How to execute SQL queries skipping error?

Question

I try to import to table data from sql files using command line. This data contents duplicates in filed url.

But field url in table is unique. So when I try to insert data I get error "Dublicate entry"

How to inmport all data skipping this error?

You should probably import to a new table, and insert only unique records. — HoneyBadger, Dec 06 '16 at 13:15
How to do this? Create one table and how insert only unique rows? — MisterPi, Dec 06 '16 at 13:16
You should create query from the table you have created with DISTINCT Or GROUP BY if is possible — Weenesta - Mathieu Dormeval, Dec 06 '16 at 13:19
Possible duplicate of [How to skip row when importing bad MySQL dump](http://stackoverflow.com/questions/7622253/how-to-skip-row-when-importing-bad-mysql-dump) — Shadow, Dec 06 '16 at 13:20
When `url` is a duplicate, Is the whole row is a duplicate? how do you want to choose between duplicate rows? — David דודו Markovitz, Dec 06 '16 at 13:21

score 4 · Accepted Answer · answered Dec 06 '16 at 13:18

4

You can to use the --force (-f) flag.

mysql -u userName -p -f -D dbName < script.sql

From man mysql:

· --force, -f

Continue even if an SQL error occurs.

answered Dec 06 '16 at 13:18

vp_arth

14,461
4
37
66

score 1 · Answer 2 · answered Dec 06 '16 at 13:24

Create a staging table with the same structure as your destination table but without the constraints (unique index included).
Manually check the duplicates and decide on the way you want to choose between duplicates rows / merge rows.
Write the appropriate query and use "insert into ... select ...".

score 0 · Answer 3 · answered Dec 06 '16 at 13:16

0

How to inmport all data skipping this error?

Drop the index for time being -> run your batch insert -> recreate the index back

answered Dec 06 '16 at 13:16

Rahul

76,197
13
71
125

`recreate the index back` will be failed – vp_arth Dec 06 '16 at 13:17

score 0 · Answer 4 · answered Dec 06 '16 at 13:19

If you are using insert, the you can ignore errors using ignore error or on duplicate key update (preferable because it only ignores duplicate key errors).

If you are using load data infile, then you can use the ignore key word. As described in the documentation:

If you specify IGNORE, rows that duplicate an existing row on a unique key value are discarded. For more information, see Comparison of the IGNORE Keyword and Strict SQL Mode.

Or, do as I would normally do:

Load the data into a staging table.
Validate the staging table and only load the appropriate data into the final table.

How to execute SQL queries skipping error?

4 Answers4