Duplicate primary key values while updating

While you're updating an old installation of PHPDevShell (using the built-in updater), you may encouter a strange error: they are duplicates keys in the primary key index.

This is because at some point a table was slightly redesigned: a primary key has been added to optimize access. Unfortunatly your table already contain duplicates. It's not a problem for PHPDevShell MySQL chokes on them.

In this case, you have to "purge" the table from the useless entries. You can do it manually but it can be tedious. Fortunatly there's a quick and clean way to do that.

Let's the upgrader tells us the offending table is "pds_core_user_role_permissions". We'll create a temporary clean table and swap it with the old one:

# create the temp table
CREATE TABLE `pds_core_user_role_permissions_clean` (`user_role_id` int(10) NOT NULL DEFAULT '0', `menu_id` varchar(64) NOT NULL,PRIMARY KEY (`user_role_id`,`menu_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# fill it with the purged data
insert into `pds_core_user_role_permissions_clean` select DISTINCT * from `pds_core_user_role_permissions`;

# swap the tables
rename table `pds_core_user_role_permissions` to `pds_core_user_role_permissions_dirty`;
rename table `pds_core_user_role_permissions_clean` to `pds_core_user_role_permissions`;

After the upgrade has been done, we can delete the dirty table:

drop table `pds_core_user_role_permissions_dirty`;