mysql file import with multiple lines of string
Question: 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!


Answer: 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`](https://dev.mysql.com/doc/refman/5.0/en/sql-
mode.html#sqlmode_no_backslash_escapes) sql mode.

Add the following to the start of your file:

    
    
    SET sql_mode = 'NO_BACKSLASH_ESCAPES';
    

