I found this useful today so thought I'd share.
I had a ~35k record table and was trying to ALTER the structure. I
wanted to make an index out of three columns (user_id, msg_id,
folder_name), but MySQL refused with a 1062 error because my request
would cause duplicate index entries -- that trio of columns had the
same set of values in more than one row of the table.
Any duplicate records would be logically invalid to the application,
but somehow they got in there from a bug in a previous version of the
app. All I needed to do was find them and clear them out and then
create the index to prevent it from happening again.
But how do you find the rows with the duped triplets? One SQL
statement can do the trick:
SELECT *, count(*) cnt FROM tableName GROUP BY user_id, msg_id,
folder_name HAVING cnt > 1;
That statement returned a nice report showing the troublesome
triplets and through some subsequent DELETE queries I was able to add
my index.
--
Joshua Penix http://www.binarytribe.com
Binary Tribe Linux Integration Services & Network Consulting
--
[email protected]
http://www.kernel-panic.org/cgi-bin/mailman/listinfo/kplug-lpsg
