Given bad mysqldump
that causes error on import:
namtar backups # mysql -p < 2010-12-01.sql
Enter password:
ERROR 1062 (23000) at line 8020: Duplicate entry 'l�he?' for key 'wrd_txt'
Is there an easy way to tell import to just skip given row and continue?
(Yes, I know I can manually edit the file or parse output, but it's not very convinient)
INTO DUMPFILE is a SELECT clause which writes the resultset into a single unformatted row, without any separators, in a file. The results will not be returned to the client. file_path can be an absolute path, or a relative path starting from the data directory.
If you can make the dump again you could add --insert-ignore
to the command-line when dumping.
Or you can try using the mysqlimport command with --force
,which will continue even if it encounters MySQL Errors.
mysql -f -p < 2010-12-01.sql
the -f
(force) being the operative option here, worked for me.
Following the advice from jmlsteele's answer and comment, here's how to turn the inserts into INSERT IGNORE
on the fly.
If you're importing from an sql
file:
sed -e "s/^INSERT INTO/INSERT IGNORE INTO/" < 2010-12-01.sql | mysql -p
If you're importing from a gz
file, just pipe the output from gunzip into sed instead of using the file input:
gunzip < 2010-12-01.sql.gz | sed -e "s/^INSERT INTO/INSERT IGNORE INTO/" | mysql -p
The other options certainly are viable options, but another solution would be to simply edit the .sql
file obtained from the mysqldump
.
Change:
INSERT INTO table_name ...
TO
INSERT IGNORE INTO table_name ...
Just a thought did you delete the MySQL directives at the top of dump? (I unintentionally did when I restarted a restore after deleting all the records/tables I'd already inserted with a sed command). These directives tell MySQL ,among other things, not to do unique tests, foreign key tests etc)
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
Great tip. I did it a little different but same result.
perl -pi -e 's/INSERT INTO/INSERT IGNORE INTO/g' filename.sql
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With