MySQL 5.x FEDERATED Tables CAN be used to share usertables and other info!!

Started by TheMaTrIx, December 21, 2005, 04:59:52 PM

Previous topic - Next topic

TheMaTrIx

In searching for the best solution to sharing users and their PM's, account settings and stuff like that between several forums, I came on this thread:
http://www.simplemachines.org/community/index.php?topic=16190.75

There are 2 methods shown in there, but both involve editing each and every file in SMF (and if you use MKPortal or another portal with it, most likely in them too.)

1: Changing all references to $tableprefix_tablename to a fixed value, in all the files of SMF.
2: Changing the config and all references to $tableprefix_tablename to take the value you set trough the config file.

Now, with MySQL5, I stumbled upon a new storage engine they included.

http://dev.mysql.com/doc/refman/5.1/en/federated-storage-engine.html

The Federated storage engine allows you to create a table that essentialy has nothing in it, except connection information to connect to the other database's table.
The engine mainly seems to be created to link tables between different servers.

But wouldn't it be posible to use it to create sort of a symbolic link to a table in another database on your own server?
The engine supports SELECT, INSERT, UPDATE, and DELETE.

Now, if my idea of what the FEDERATED engine is capable of, you could implement multiple sites with 1 shared user database without needing to change a single line of PHP code like this:

Site 1 database: StorageDatabase
table 1: StorageDatabase.discuss_personal_messages (INNODB or MyISAM)
table 2: StorageDatabase.discuss_members (INNODB or MyISAM)
table 3: StorageDatabase.discuss_moderators (INNODB or MyISAM)
table 4: StorageDatabase.discuss_pm_recipients (INNODB or MyIsam)
table 5: StorageDatabase.discuss_membergroups (INNODB or MyIsam)

Site 2 database: site1db
table 1: site1db.discuss_personal_messages (delete the one created by SMF install and create FEDERATED to StorageDatabase.discuss_personal_messages)
table 2: site1db.discuss_members (delete the one created by SMF install and create FEDERATED to StorageDatabase.dicsuss_members)
talbe 3: site1db.discuss_moderators (INNODB or MyISAM)
table 4: site1db.discuss_pm_recipients (delete the one created by SMF install and create FEDERATED to StorageDatabase.discuss_pm_recipients)
table 5: site1db.discuss_membergroups (delete the one created by SMF install and create FEDERATED to StorageDatabase.discuss_membergroups)

Site 3 database: site2db
table 1: site2db.discuss_personal_messages (delete the one created by SMF install and create FEDERATED to site1db.discuss_personal_messages)
table 2: site2db.discuss_members (delete the one created by SMF install and create FEDERATED to site1db.dicsuss_members)
talbe 3: site2db.discuss_moderators (INNODB or MyISAM)
table 4: site2db.discuss_pm_recipients (delete the one created by SMF install and create FEDERATED to site1db.discuss_pm_recipients)
table 5: site2db.discuss_membergroups (delete the one created by SMF install and create FEDERATED to site1db.discuss_membergroups)

Site 4 database: site3db
table 1: site3db.discuss_personal_messages (delete the one created by SMF install and create FEDERATED to site1db.discuss_personal_messages)
table 2: site3db.discuss_members (delete the one created by SMF install and create FEDERATED to site1db.dicsuss_members)
talbe 3: site3db.discuss_moderators (INNODB or MyISAM)
table 4: site3db.discuss_pm_recipients (delete the one created by SMF install and create FEDERATED to site1db.discuss_pm_recipients)
table 5: site3db.discuss_membergroups (delete the one created by SMF install and create FEDERATED to site1db.discuss_membergroups)

What ya guys think?
I'll be trying it out to see what effect it has.

EDITED AFTER SUCCESS.

Ok, you have to use the above method of sharing tables.

You CANNOT, and I can't press this hard enough CANNOT link the federated tables in all sites, directly to the original database.
You have to link them to the first database you created a federate table in.

This is because of the limitation with FEDERATE engine that it doesn't have a clue whats going on on the other side. This means that only 1 other database should be reading/writing to the storage database. That is why you have to link all the other sites to the FEDERATED table in the first sites database, so that that database is the only one thats writing to the storagedatabase.

This also has as effect that the StorageDatabase only needs to have the tables that will be federated in it. The others are just a waste of space.


TheMaTrIx

I've been able to link the following tables without problems:

ban_groups
ban_items
membergroups
personal_messages
pm_recipients

I'm currently working on the members table. Its being a lil bit of a complete ******.

The test database I'm using has 5000 users in it and when you create a FEDERATED table, you can see it read a load of data from the parent table (probably to check if the parent is valid) and I also noticed that when doing that, it executes query's reading from the sessions and other tables. My guess in that is that its checking the linked keys in the parent table seeing what data it refers to.

Now after a while it goes "data transmission interupted". I'm looking into it why that is and I'm creating a test database that has only a few users in it to test if it goes better with less data to check.

Hopefully I'll get the members table done today to.

The advantages of using FEDERATED tables will go from easy upgrades and mods of PHP files per site without the need to change anything in the files to share users.
Ability to span several sites over several databases. Now, if you share users, you have to either use the same database for all sites involved, or either rewrite even more of SMF to allow for the user tables to be checked in another database.


Now a question for the SMF staff, do I need to share the SESSIONS tables too?

TheMaTrIx

BTW, the above problem I described with the members table is only when CREATING the FEDERATED table.
Not when using it.

The queries run when creating the link pritty much read the entire parent table and the tables linked to it with keys.
When using this in a live environment it will only read whatever your script requests.

TheMaTrIx

SUCCES!

It works nearly perfectly !!

I only seem to have 1 problem at the moment.

PM_recipients dosn't get writen when sening a PM.

Now I have my test PM's nicely showing up in the parent table, but no PM_recipients seem to have been writen.

Anyone an idea why that happend?

TheMaTrIx

This is strange.
The PM's do seem to arive:
--
Hey, thematrix, you have 2 messages, 2 are new.
--

But when checking PM's the inbox apears empty.

TheMaTrIx

Complete Success now.

PM_recipients table wasn't being writen because I made a lil screwup by putting the table in latin1 codepage instead of utf8 like all the other tables.

As of this, FEDERATED table type in MySQL5 can be used to share PM's, usertables and other information between several boards without having the need to modify any PHP files.

I'm writing .sql files atm to ease the creation of new sites for our network.
I might post an example here lateron.

A big pitty that from all the time I spent on this, with all the questions there are on this forum and many others, about sharing userbase between sites, none of you lazy bums even reply'd in here 1ce!

Trekkie101

Although I don't have a need for sharing databases.

Ive been watching, you, ive got you on notification every time you post here :) and have found your posts insightful!

So thank you, from me atleast, for sharing this information with us. It definately a brilliant idea, the others will take a little longer to notice :)

Ta and Happy Christmas.

TheMaTrIx

Thanks, It wasn't really directed at the staff of SMF, but more to the many members that have questions about this.

I updated my first post to show the correct method of doing this.

Lateron, when I get the network up and running (I'm pressed in time to move this one site to a network of several), I'll add more detailed explinations and posibly some MySQL scripts the users can use to do this. Because I'm quite sure 99% of them have no clue how to manualy create tables, let alone debug problems with them XD

We can all thank MySQL for creating this storage engine, because it'll make website management and updating much much much easyer.
No more need to change any PHP code.

Grudge

Sorry TheMaTrIx, I'm sure people are interested in this. May I suggest that you write up a clean topic when you're all done telling people how to do this?

As for session info, I guess you only need share it if people will be hopping from one site to the other, and you want them to be able to retain their current session info.
I'm only a half geek really...

Omniverse

Hmm, actually, I would be very interested in this. I'm new to SMF, and am loving it so far.

I have a need to have 3 seperate forums running on my site, maybe even 4. I do want the user files and settings to be global across these different forums.

Did you ever get the scripts working to do this auto? Are the directions at top to do this complete and up to date?

Any help to accomlpish this would be much appreciated, and after browsing around looking at topics like this, seems you've got a great way to do it :)

Philco

I`m also new here and would like to say very well done, I too am looking at doing just this and your thread has switched me onto using federated tables, I`m about to install mysql 5 onto my test machine and will see if I can get it working.

Thanks for sharing a great idea..

Philco

Advertisement: