I was trying to sync some tables between master and slave on MySQL/InnoDB using pt-table-sync, when I noticed something odd. There are several tables that relate users to some other entity, that don't have any primary key defined. This means that pt-table-sync has real trouble identifying rows distinctly, because there can be duplicates.
For example phpbb_user_group is defined as:
Code: Select all
CREATE TABLE phpbb_user_group (
group_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
user_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL,
group_leader tinyint(1) UNSIGNED DEFAULT '0' NOT NULL,
user_pending tinyint(1) UNSIGNED DEFAULT '1' NOT NULL,
KEY group_id (group_id),
KEY user_id (user_id),
KEY group_leader (group_leader)
) CHARACTER SET `utf8` COLLATE `utf8_bin`;
It seems to me that, since a user shouldn't be able to be in a group twice, (user_id, group_id) should be the primary key of this table. If I run this query to check for duplicate rows:
Code: Select all
SELECT group_id, user_id, COUNT(*)
FROM phpbb_user_group
GROUP BY group_id, user_id
HAVING COUNT(*) > 1;
Code: Select all
SELECT group_id, user_id, COUNT(*)
FROM phpbb_user_group
GROUP BY group_id, user_id
HAVING COUNT(DISTINCT group_leader) > 1 OR COUNT(DISTINCT user_pending) > 1;