MySQL duplicate entry error opon import to new server

backupgaleraMySQL

I do have a DB on MySQL Galera 5.6.34 with a table that contains an index on ID,date. The table is fully functional and I have used repair and check to verify the table. However, upon backup/restore the importing DB-Server (MySQL 5.7.17, not Galera) tells me there is a duplicate entry and stops importing:

gunzip < DB_170206_040001.sql.gz | mysql -u root -p staging

ERROR 1062 (23000) at line 3800: Duplicate entry '329610-2011-03-27 03:00:00' for key 'user_id'

If I empty that table prior to the backup, everything works fine.

Is there a command to "ignore" upon import, or even better to resolve the problem at the live server?

Best Answer

Per this response: mysqldump with INSERT ... ON DUPLICATE

It would be best to take a new backup with the --replace option, if you are certain that this will not destroy relational integrity of your dataset. This will drop the row that is creating conflicts and instead insert the row that is signalling the error and stopping the restore.

The issue is that you, somehow, have ended up with multiple rows with the same value in a column that enforces uniqueness. How this happened in the Galera server is unclear.

It may be useful to post the results of the following: 

mysql> select max(t.count) from (select count(user_id) as count from <db>.<table> group by user) t;

If any row returns a value other than 1, then there's a relational problem on the originating server. If the value is equal to 1, there's more to explore on the restore-server in why it believes there is an issue. It could be that the backup was taken hot, and there are missing transactions which do not yield a consistent dataset.

Related Question