Ignore mysql error while executing bulk statements

Sometimes when running MySQL in batch mode, it requires to ignore some MySQL errors which pops in between.

As an example, we can consider a scenario where thousands of inserts required to be done and the statements are saved in a file, suppose in indata.sql. But it may be the case that some records are already there in database which you’re trying to insert or you are importing large data for development/test purpose and you won’t bother much if some rows can be skipped. In normal case, MySQL will show error something like below and exit:

ERROR 1062 (23000) at line 6: Duplicate entry 'blablabla' for key 2

So, to ignore the error and keep the insertion process running, you need to supply extra command line argument “-f” while execution:

$ mysql -f -u root -p dbname < indata.sql

MySQL will certainly show the errors but not halt, instead keep execution of next statements.

This entry was posted on Friday, October 31st, 2008 at 4:55 pm and is filed under database. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

7 Responses to “Ignore mysql error while executing bulk statements”

Mereset username dan password untuk MySQL dan PostgreSQL « eX-GeodetaNews on July 29th, 2010 at 11:30 am

[...] Related posts in this blog on MySQL: 1. Setup MySQL Cluster in RedHat based Linux 2. Setup Multiple MySQL server instances in a single Linux Server. 3. Optimize a large installation of MySQL. 4. Fix MySQL running slow without any known reason. 5. Ignore MySQL error coming while executing bulk statements. [...]
Amulraj on May 21st, 2011 at 2:55 pm

i try this command $ mysql -f -u root -p dbname < indata.sql but its not working,but its working wheni use MySql Admin IDE
jagbir on May 21st, 2011 at 4:38 pm

Amulraj, this command is just for example, if should run if you have indata.sql file in your current directory and if its not working, let me know the error text its showing.
Zachary Nin on July 26th, 2011 at 4:52 pm

Hi! I just would like to give you a huge thumbs up for this wonderful tips you have right here on this particular post. I’ll be coming back again to your blog for more soon.
jagbir on July 26th, 2011 at 5:01 pm

Hey Zachary Nin, Thanks for the comment. Glad to see you got help from the post.
Fatcow Coupon on August 3rd, 2011 at 12:26 am

hello there and thank you for your information – I have definitely picked up something new from right here. I did however expertise some technical issues using this site, as I experienced to reload the website lots of times previous to I could get it to load correctly. I had been wondering if your web hosting is OK? Not that I’m complaining, but slow loading instances times will often affect your placement in google and can damage your quality score if advertising and marketing with Adwords. Well I am adding this RSS to my email and could look out for much more of your respective exciting content. Ensure that you update this again very soon..
jagbir on August 3rd, 2011 at 2:37 pm

Thanks Fatcow for your comment and information. Site is loading fine and looks ok in other aspects as well but definitely I will keep an eye on it.

Linux Admin Zone