CODEWITHSUNDEEP
mysqlsqlite
leoce
leoce

Reputation: 735

mysql file import with multiple lines of string

I have 3 sql files migrated from sqlite3 dump. Unfortunately they have string values of multiple lines. So when I mysql -p dbname <- dbname.sql it returns syntax errors at specific lines:

ERROR 1064 (42000) at line 87194: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '15052','53663423259319','21:35:45','FALSE','536625' at line 1

These lines may look like this (lines have been edited for masking):

87193 INSERT INTO `tbl` VALUES('15052','53663968185392','21:57:25','FALSE','53634933924616','79886','','','','','79886','rado','simple','');
87194 INSERT INTO `tbl` VALUES('15052','53663466948450','21:37:30','FALSE','53653949005223','62487','','','','','62487','Nopa','oh, i dunno:
87195
87196 😭    ☺
87197 /|\   /(👶)\
87198 /\      / \','');
87199 INSERT INTO `tbl` VALUES('15052','53663423259319','21:35:45','FALSE','53662542442479','28086','','','','','28086','ESOS','AHAHAHAHAHAHAHA 😂 ','');

How can I import these sql files and let mysql understand that if a string doesn't end with single quote there's another line below? I searched for some args for mysql but didn't find any.

I also tried to use http://www.redmine.org/attachments/8273/sqlite3-to-mysql.py and redo the migration, as it says the python script handles this multiple line trouble. However every time the console says "Killed", I guess because of the temp file operation (the sqlite3 db files are over 100Gb). I changed tempfile.tempdir = "/home/username/sqlite_to_mysql/, hoping it can provide enough space for the temp file (6Tb free) but still the console returned "Killed". I don't know other ways to solve it before the sql file is created.

Thanks a lot for any help!

Upvotes: 0

Views: 857

Answers (1)

Burhan Khalid
Burhan Khalid

Reputation: 174708

The problem is not the spacing of the lines, but rather the quoting of the content.

On the line mentioned in the error message, the text content has \' as the last character; which ends up escaping the ' and causing the parser to consider the rest of the line as one single string.

The end effect is when it encounters the next ', it has already chopped off the start of the query statement and thus your error.

To prevent this, you need to disable \ as an escape character; which can you can do by setting the NO_BACKSLASH_ESCAPES sql mode.

Add the following to the start of your file:

SET sql_mode = 'NO_BACKSLASH_ESCAPES';

Upvotes: 1

Related Questions