2

As I know from DOCs and from other questions such a query should silently drop rows with duplicated id. However MySQL throws an error:

enter image description here

This is phpmyadmin output.

Why MySQL did not dropped rows with duplicated id? Any ideas?

Community
  • 1
  • 1
Vlada Katlinskaya
  • 991
  • 1
  • 10
  • 26
  • Did you try the workaround? `ALTER IGNORE TABLE cards ADD PRIMARY KEY(id) ORDER BY id DESC;`... ? – Michael - sqlbot Jul 23 '15 at 15:28
  • @Michael-sqlbot just tried as you recommended. Unfortunately it lead to `#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY id DESC' at line 1`. Also please look at my answer as it looks like I found the reason for the error. – Vlada Katlinskaya Jul 23 '15 at 15:34
  • My mistake. I omitted a comma. `...KEY(id), ORDER BY...` should work because it confuses the server into implicitly using the "older alter table" logic. InnoDB tables can't actually be reordered like this, but the server makes the necessary preparations before realizing that, switches to a different mechanism for altering the table, then just sets a warning that the order by was ignored (but does the rest, as requested). This appears to be an indirect way of ultimately doing the same thing you're accomplishing in your answer. – Michael - sqlbot Jul 23 '15 at 23:51

1 Answers1

4

Looks like I looked in a wrong docs. I looked at 5.1 version docs and I have 5.5 version of the MySQL server.

In the right docs mentioned:

enter image description here

So I need to consider using suggested SET SESSION old_alter_table=1 or find another way to remove excess rows from table.

Vlada Katlinskaya
  • 991
  • 1
  • 10
  • 26
  • IGNORE option has been removed on MySQL 5.7.4: "As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and its use produces an error." https://dev.mysql.com/doc/refman/5.7/en/alter-table.html – Lito Aug 02 '16 at 14:09
  • I think you have an accepted answer here, and if you want a traditional method of deleting duplicates : [you can use this](https://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql#5016434). – user10089632 Jan 21 '18 at 11:02