On 9/12/06, Joshua Penix <[EMAIL PROTECTED]> wrote:
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.
Slick! Thanks for the tip.
My own little tip is to make use of mysqldiff to alter one db's schema
(say production) to match another's (say development). It's a command
line tool where you give it the 2 db's and it issues the ALTERs,
CREATE TABLEs and ?DROP TABLE?s needed to do the job.
The dbs can be real MySQL dbs or just SQL text files with the CREATEs
that define the db.
It's located here:
http://www.adamspiers.org/computing/mysqldiff/
I see from Google that another such tool has appeared from another
source. I haven't tried it before:
http://www.mysqldiff.org/
-Chuck
--
[email protected]
http://www.kernel-panic.org/cgi-bin/mailman/listinfo/kplug-lpsg
