XOOPS FAQ - The Database and Site Migration. - How can i share tables between sites with MySQL?

To sharing tables you need to have installed in you server MySQL 5 or higher.

The trick is that you can use the MySQL Engine MERGE and MySQL will see the table just like the other.

Here's the idea:

1) You install xoops normally, using the db prefix "site1"
2) You install another time, using the db prefix "site2"
3) Export the tables from the site1 database with the hole structure and data. (*)
4) Drop the table to be shared and import it again. (*)
5) Now, drop the site2 table which should be replaced.
6) Copy from the export the structure from the table (only teh structure, not the data) and put the next line istead of the last line.


) TYPE= MyISAM ENGINE=MERGE UNION=(xoops_users) INSERT_METHOD=FIRST;

Your code should look like the next in the case of the users table.

CREATE TABLE `xoops02_users` (
  `uid` mediumint(8) unsigned NOT NULL auto_increment,
  `name` varchar(60) NOT NULL default '',
  `uname` varchar(25) NOT NULL default '',
  `email` varchar(60) NOT NULL default '',
  `url` varchar(100) NOT NULL default '',
  `user_avatar` varchar(30) NOT NULL default 'blank.gif',
  `user_regdate` int(10) unsigned NOT NULL default '0',
  `user_icq` varchar(15) NOT NULL default '',
  `user_from` varchar(100) NOT NULL default '',
  `user_sig` tinytext NOT NULL,
  `user_viewemail` tinyint(1) unsigned NOT NULL default '0',
  `actkey` varchar(8) NOT NULL default '',
  `user_aim` varchar(18) NOT NULL default '',
  `user_yim` varchar(25) NOT NULL default '',
  `user_msnm` varchar(100) NOT NULL default '',
  `pass` varchar(32) NOT NULL default '',
  `posts` mediumint(8) unsigned NOT NULL default '0',
  `attachsig` tinyint(1) unsigned NOT NULL default '0',
  `rank` smallint(5) unsigned NOT NULL default '0',
  `level` tinyint(3) unsigned NOT NULL default '1',
  `theme` varchar(100) NOT NULL default '',
  `timezone_offset` float(3,1) NOT NULL default '0.0',
  `last_login` int(10) unsigned NOT NULL default '0',
  `umode` varchar(10) NOT NULL default '',
  `uorder` tinyint(1) unsigned NOT NULL default '0',
  `notify_method` tinyint(1) NOT NULL default '1',
  `notify_mode` tinyint(1) NOT NULL default '0',
  `user_occ` varchar(100) NOT NULL default '',
  `bio` tinytext NOT NULL,
  `user_intrest` varchar(150) NOT NULL default '',
  `user_mailok` tinyint(1) unsigned NOT NULL default '1',
  PRIMARY KEY  (`uid`),
  KEY `uname` (`uname`),
  KEY `email` (`email`),
  KEY `uiduname` (`uid`,`uname`),
  KEY `unamepass` (`uname`,`pass`)
) TYPE= MyISAM ENGINE=MERGE UNION=(xoops_users) INSERT_METHOD=FIRST;

NOTE: This "trick" is very sencible to the structure of the table. If the the structure changes, you are going to have to redo the trick.

(*) As the NOTE said the trick is too sencible to the structure, and testing it we realised that an efective way to make it work is exporting the tables and then importing it manually.

Initial trick send by Skalpa
Final solutions an testing by krewl

0 Comment(s)