Using FEDERATED to share user information between Websites

[Jan Schotsmans]

Heya everyone.

I'm using 5.0.11 MAX-NT on Windows 2003 with PHP 4.4.1.1.

What I'm trying to do and have been quite succesfull in doing is sharing several tables that have user profiles, private messages and ban data accros several sites.

This is a 2 part questions:

A: The way I'm testing now is:

mastersitedatabase -> INNODB tables, this site runs the main site
slavesitedatabase -> INNODB tables with Federated tables for the linked tables.

I don't know if this the best way to do it.

Should I instead be using a setup like this:

storagedatabase -> INNODB tables, database that holds the tables I'll be sharing.
mastersitedatabase -> FEDERATED tables linked to storage database.
slavesitedatabase -> FEDERATED tables linked to storage database.

Or should I be using:

storagedatabase -> INNODB tables, database that holds the tables I'll be sharing.
mastersitedatabase -> FEDERATED tables linked to storage database.
slavesitedatabase -> FEDERATED tables linked to the FEDERATED tables on mastersitedatabase.

I read in the FEDERATED info pages for MySQL 5 that federated doesn't like several engines to write to the same database. So I guess I should be using the last method I said?
INNODB <-> Federated Master <-> FEDERATED Slave
Instead of
INNODB <-> Federated Master
INNODB <-> Federated Slave

Following are the sql for the tables I'll be sharing.

CREATE TABLE `discuss_ban_groups` (
`ID_BAN_GROUP` mediumint(8) unsigned NOT NULL auto_increment,
`name` varchar(20) NOT NULL default '',
`ban_time` int(10) unsigned NOT NULL default '0',
`expire_time` int(10) unsigned default NULL,
`cannot_access` tinyint(3) unsigned NOT NULL default '0',
`cannot_register` tinyint(3) unsigned NOT NULL default '0',
`cannot_post` tinyint(3) unsigned NOT NULL default '0',
`cannot_login` tinyint(3) unsigned NOT NULL default '0',
`reason` tinytext NOT NULL,
`notes` text NOT NULL,
PRIMARY KEY (`ID_BAN_GROUP`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `discuss_ban_items` (
`ID_BAN` mediumint(8) unsigned NOT NULL auto_increment,
`ID_BAN_GROUP` smallint(5) unsigned NOT NULL default '0',
`ip_low1` tinyint(3) unsigned NOT NULL default '0',
`ip_high1` tinyint(3) unsigned NOT NULL default '0',
`ip_low2` tinyint(3) unsigned NOT NULL default '0',
`ip_high2` tinyint(3) unsigned NOT NULL default '0',
`ip_low3` tinyint(3) unsigned NOT NULL default '0',
`ip_high3` tinyint(3) unsigned NOT NULL default '0',
`ip_low4` tinyint(3) unsigned NOT NULL default '0',
`ip_high4` tinyint(3) unsigned NOT NULL default '0',
`hostname` tinytext NOT NULL,
`email_address` tinytext NOT NULL,
`ID_MEMBER` mediumint(8) unsigned NOT NULL default '0',
`hits` mediumint(8) unsigned NOT NULL default '0',
PRIMARY KEY (`ID_BAN`),
KEY `ID_BAN_GROUP` (`ID_BAN_GROUP`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `discuss_membergroups` (
`ID_GROUP` smallint(5) unsigned NOT NULL auto_increment,
`groupName` varchar(80) NOT NULL default '',
`onlineColor` varchar(20) NOT NULL default '',
`minPosts` mediumint(9) NOT NULL default '-1',
`maxMessages` smallint(5) unsigned NOT NULL default '0',
`stars` tinytext NOT NULL,
PRIMARY KEY (`ID_GROUP`),
KEY `minPosts` (`minPosts`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `discuss_members` (
`ID_MEMBER` mediumint(8) unsigned NOT NULL auto_increment,
`memberName` varchar(80) NOT NULL default '',
`dateRegistered` int(10) unsigned NOT NULL default '0',
`posts` mediumint(8) unsigned NOT NULL default '0',
`ID_GROUP` smallint(5) unsigned NOT NULL default '0',
`lngfile` tinytext NOT NULL,
`lastLogin` int(10) unsigned NOT NULL default '0',
`realName` tinytext NOT NULL,
`instantMessages` smallint(5) NOT NULL default '0',
`unreadMessages` smallint(5) NOT NULL default '0',
`pm_ignore_list` tinytext NOT NULL,
`passwd` varchar(64) NOT NULL default '',
`emailAddress` tinytext NOT NULL,
`personalText` tinytext NOT NULL,
`gender` tinyint(4) unsigned NOT NULL default '0',
`birthdate` date NOT NULL default '0000-00-00',
`websiteTitle` tinytext NOT NULL,
`websiteUrl` tinytext NOT NULL,
`location` tinytext NOT NULL,
`ICQ` tinytext NOT NULL,
`AIM` varchar(16) NOT NULL default '',
`YIM` varchar(32) NOT NULL default '',
`MSN` tinytext NOT NULL,
`hideEmail` tinyint(4) NOT NULL default '0',
`showOnline` tinyint(4) NOT NULL default '1',
`timeFormat` varchar(80) NOT NULL default '',
`signature` text,
`timeOffset` float NOT NULL default '0',
`avatar` tinytext NOT NULL,
`pm_email_notify` tinyint(4) NOT NULL default '0',
`karmaBad` smallint(5) unsigned NOT NULL default '0',
`karmaGood` smallint(5) unsigned NOT NULL default '0',
`usertitle` tinytext NOT NULL,
`notifyAnnouncements` tinyint(4) NOT NULL default '1',
`notifyOnce` tinyint(4) NOT NULL default '1',
`memberIP` tinytext NOT NULL,
`secretQuestion` tinytext NOT NULL,
`secretAnswer` varchar(64) NOT NULL default '',
`ID_THEME` tinyint(4) unsigned NOT NULL default '0',
`is_activated` tinyint(3) unsigned NOT NULL default '1',
`validation_code` varchar(10) NOT NULL default '',
`ID_MSG_LAST_VISIT` int(10) unsigned NOT NULL default '0',
`additionalGroups` tinytext NOT NULL,
`smileySet` varchar(48) NOT NULL default '',
`ID_POST_GROUP` smallint(5) unsigned NOT NULL default '0',
`totalTimeLoggedIn` int(10) unsigned NOT NULL default '0',
`passwordSalt` varchar(5) NOT NULL default '',
`messageLabels` text NOT NULL,
`buddy_list` tinytext NOT NULL,
`notifySendBody` tinyint(4) NOT NULL default '0',
`notifyTypes` tinyint(4) NOT NULL default '2',
`longitude` decimal(18,15) default NULL,
`latitude` decimal(18,15) default NULL,
PRIMARY KEY (`ID_MEMBER`),
KEY `memberName` (`memberName`(30)),
KEY `dateRegistered` (`dateRegistered`),
KEY `ID_GROUP` (`ID_GROUP`),
KEY `birthdate` (`birthdate`),
KEY `posts` (`posts`),
KEY `lastLogin` (`lastLogin`),
KEY `ID_POST_GROUP` (`ID_POST_GROUP`),
KEY `lngfile` (`lngfile`(24))
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `discuss_personal_messages` (
`ID_PM` int(10) unsigned NOT NULL auto_increment,
`ID_MEMBER_FROM` mediumint(8) unsigned NOT NULL default '0',
`deletedBySender` tinyint(3) unsigned NOT NULL default '0',
`fromName` tinytext NOT NULL,
`msgtime` int(10) unsigned NOT NULL default '0',
`subject` tinytext NOT NULL,
`body` text,
PRIMARY KEY (`ID_PM`),
KEY `ID_MEMBER` (`ID_MEMBER_FROM`,`deletedBySender`),
KEY `msgtime` (`msgtime`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

CREATE TABLE `discuss_pm_recipients` (
`ID_PM` int(10) unsigned NOT NULL default '0',
`ID_MEMBER` mediumint(8) unsigned NOT NULL default '0',
`bcc` tinyint(3) unsigned NOT NULL default '0',
`is_read` tinyint(3) unsigned NOT NULL default '0',
`deleted` tinyint(3) unsigned NOT NULL default '0',
`labels` varchar(60) NOT NULL default '-1',
PRIMARY KEY (`ID_PM`,`ID_MEMBER`),
UNIQUE KEY `ID_MEMBER` (`ID_MEMBER`,`deleted`,`ID_PM`)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

I used the exact same SQL to create the FEDERATED table with the following info:

ENGINE=FEDERATED
DEFAULT CHARSET=UTF8
CONNECTION='mysql://username:password@localhost/database/tablename';

B: Now, my 2nd problem.

I can:

Create/register users on both master and slave site (eg write to the members table)
Create usergroups on both master and slave site (eg write to membergroups)
Create PM's on both master and slave sites (eg write personal_messages)
Create Bans on both master and slave sites (eg write ban_items and ban_groups)

What doesn't happen is while writing to personal_messages, the app should alse write to PM_recipients and this it only does on the mastersitedatabase. Not on the slavesitedatabase.

So sending Private messages only works on the master site.

You recieve a notice on both sites that you have a message when you send it from the slave site, this is because the message gets stored in the personal_messages table, but the information in PM_recipients which allows a user to fetch his private messages, doesn't get writen.

Is this because of the way I setup the database eg.

mastersitedatabase -> INNODB tables, this site runs the main site
slavesitedatabase -> INNODB tables with Federated tables for the linked tables.

Or is this because the way the PM_recipients tables are setup?

I'll be testing the method:

storagedatabase -> INNODB tables, database that holds the tables I'll be sharing.
mastersitedatabase -> FEDERATED tables linked to storage database.
slavesitedatabase -> FEDERATED tables linked to the FEDERATED tables on mastersitedatabase.

next and hope that fixes the problem, it seems kind of logical after reading:

-------------------------
There is no way for the FEDERATED engine to know if the remote table has changed. The reason for this is that this table must work like a data file that would never be written to by anything other than the database. The integrity of the data in the local table could be breached if there was any change to the remote database.
-------------------------

a few times more.

Hope you guys can help me, because doing it like this will make sharing users and other things, between forums and sites a whole lot easyer, because you don't need to rewrite the entire application again to be able to make it use another table prefix or address and other database all together to share user data.

Especialy these days that bugs and exploits for applications are found every day and patches and updates are released almost monthly.

Regards,

Jan