mysql duplicated my rows over night

Question

Ok I know this might sound screwed up, but mysql sometime yesturday through an unexpected curball my way, which i have never come across before.

SO we have a database table called employees in that table you have records like

Russell
Smith
Sam

but all of a sudden i have

Russell
Russell
Smith
Smith
Smith
Sam
Sam
Sam

If anyone knows how to fix this without, downloading the full table and manually fixing it and re uploading that would be great

Not sure why that would happen, weird! Maybe talk to your webhost about it! — pattyd, May 28 '13 at 00:23
Cartesian join involving the employee table introduced sometime yesterday into the code? — Dallas, May 28 '13 at 00:32

juergen d · Answer 1 · 2013-05-28T00:43:13.063

1

If your table has an id column and you want to keep only the records with lowest id you can do

delete from employees 
where id not in 
(
   select * from 
   (
     select min(id) 
     from employees 
     group by name
   ) x
)

edited May 28 '13 at 00:43

answered May 28 '13 at 00:28

juergen d

201,996
37
293
362

. . I thought about this. But if the rows are being duplicated, wouldn't the id also be duplicated? If so, this isn't going to work. – Gordon Linoff May 28 '13 at 01:05

score 0 · Answer 2 · answered May 28 '13 at 00:26

My recommendation:

(1) Create a new temporary table as:

create temporary table xxx as
    select distinct * from employees

(2) Truncate the original table

truncate table employees

(3) Insert the records back in

insert into employees
    select * from xxx

By truncating the table, you will be keeping almost all information associated with it -- security, triggers, constraints. The one issue may be an auto-incremented primary key. This may not work if you have one of those (but then again, you couldn't duplicate the records if there is a primary key).

You can also manually correct an auto-increment counter (`ALTER TABLE mytable AUTO_INCREMENT = 500`). — Niels Keurentjes, May 28 '13 at 00:46

mysql duplicated my rows over night

2 Answers2