MySQL: DECLARE CONTINUE HANDLER to continue on errors, printing a warning

Question

I am reviewing / redesigning / refactoring a database and want to create a new database that stores pretty much the same data in a smarter fashion. One of the problems in the 'legacy' database is that it does not make proper use of keys and indices, so there is duplicate entries where there should be none.

I have written a python script that reads data from the legacy database and generates a SQL script which in turn inserts these values from the legacy database into the new database using a lot of very similar SQL INSERT statements. The SQL script stops every time it encounters a duplicate entry with an error message

ERROR 1062 (23000) at line 3086: Duplicate entry '56.450000000--3.366670000-121' for key 'lat_lon_height'

which AFAICT is exactly what it should do. However, for the moment, I just want the script to keep going, not insert the duplicate entries, but print a warning about them. I tried installing a continue handler at the beginning of the script in several ways following the MySQL docs and some other online resources, but all of them just create a syntax error:

DECLARE CONTINUE HANDLER FOR 1062 
SELECT 'Duplicate key in unique index';

or

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
SELECT 'Duplicate key in unique index';

or

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
BEGIN
SELECT 'Duplicate key in unique index';
END;

What am I doing wrong ?

Answer 1

OK, one way to make the SQL script simply print all the errors that occur is to use the --force command line option as in

mysql --force < my_insertion_script.sql

Still don't know what to actually do about the duplicate entries, but once I've figured that out (not a SQL problem), this is probably going to help.

Would love to get the continue handler to work, though...

Answer 2

I haven't used handlers except inside of stored procedures, where my use of them has been limited but uneventful. Perhaps you can make your code into a stored procedure and then run the stored procedure? You might find MySQL accepts what you are trying to do then.