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)

The comments are owned by the author. We aren't responsible for their content.

Login

Username

Password

Remember me

Search

Advanced Search

Donat-O-Meter

Stats
Goal:	$100.00
Due Date:	Nov 30
Gross Amount:	$0.00
Net Balance:	$0.00
Left to go:	$100.00

Did you know ?

You can easily change passwords in the database in case you lost them. You can even let phpmyadmin convert them into the md5 format in which they are stored.

More details...

Random question

Can I use phpbb or IPB forums with xoops?

Answer here!

How can i share tables between sites with MySQL?

Login

Search

Who's Online

Donat-O-Meter

Did you know ?

Random question

Recent Q&A List