Best practice for importing a .sql file with error on MySQL

Question

I am trying to import a .sql file to MySQL using this command line

mysql -u username -p databasename < path/myfile.sql

Here is a sample of myfile.sql

INSERT INTO mytable (‘id’,’isDeleted’,’amount’,’date’,’description’,’description_clean’,’isDebit’,’account_id’,’user_uuid’) VALUES (3798,'0',35,'0000-00-00','test','test','1',108,'021752f8-7194-4a4b-8919-fb8216ecba07');
INSERT INTO mytable (‘id’,’isDeleted’,’amount’,’date’,’description’,’description_clean’,’isDebit’,’account_id’,’user_uuid’) VALUES (3799,'0',9.4,'2018-05-02','CB Frichti','CB Frichti','1',108,'316a3da6-821b-42c8-80ea-3c1ac437484e');
INSERT INTO mytable (‘id’,’isDeleted’,’amount’,’date’,’description’,’description_clean’,’isDebit’,’account_id’,’user_uuid’) VALUES (3800,'0',50,'0000-00-00','test','test','1',108,'316a3da6-821b-42c8-80ea-3c1ac437484e');

My file contains some date but some of those are wrong date 0000-00-00. Therefore the command above returns an error.

What is the best way to import ? Should I remove the line where date is wrong ? Is there a way to import only import lines that are correct and ignore those with error ?

Thanks for your help

Replace '0000-00-00' in a text editor with NULL (if the column is nullable) or a default date '1900-01-01' then you can either use the data as is or delete it one you have imported. (INSERT INTO ‘‘ doesn't look correct either) — Alex K.
– Alex K., Commented Apr 5, 2019 at 16:05
If you want to clean up your data first, keep in mind it's often a lot easier to edit as CSV and then import that with LOAD DATA INFILE. Most any spreadsheet tool can read and write CSV, making edits pretty effortless and bulk search/replace isn't even that hard. — tadman
– tadman, Commented Apr 5, 2019 at 16:52
Or load it into a temporary table where the columns are just character, and then write some SQL with case statements etc. to clean it up and populate the production table - that way you (effectively) have a record of what you changed, and you can potentially re-use it next time you get a batch of nasty data to load. — MandyShaw
– MandyShaw, Commented Apr 5, 2019 at 17:03

w4bo · Accepted Answer · 2019-04-05 16:33:39Z

6

As stated here

You can use the --force (-f) flag on your mysql import. Rather than stopping, MySQL will continue and just log the errors to the console.

For example:

mysql -u username -p -f -D path/myfile.sql < path/myfile.sql

Replacing invalid values with a default value (e.g., 1900-01-01) is not a good practice, you should prefer NULL instead

answered Apr 5, 2019 at 16:33

w4bo

9157 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yohn Over a year ago

For a minute I though it would work, but it still failed because of an sql error at line 178704

Collectives™ on Stack Overflow

Best practice for importing a .sql file with error on MySQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related