omatic
3-12-03, 02:10 AM
Hello all,
I have been experimenting with making backups of my mySQL data from Powweb's mySQL servers, and then restoring that data to my copy of mySQL running locally, here in my 'lab'.
What I'm having a problem with, is that many of the INSERT statements contain semicolons within their string data. Unfortunately, the INSERT statement seems to ignore the fact that the ; character is in single quotes (part of a quoted literal string) and mySQL treats that semicolon as the end of the INSERT statement.
This kinda SUCKS, because it turns a single valid INSERT statement into two completely broken SQL statements by breaking it in half! Here's an example:INSERT INTO mysql_tablename (stringcolumn1, column2) VALUES ('a string literal; with a semicolon in it!', 'someothervalue');This gets interpreted, rather ungracefully, as two separate (and completely invalid) statements:INSERT INTO mysql_tablename (stringcolumn1, column2) VALUES ('a string literal;
--- and ---
with a semicolon in it!', 'someothervalue');Of course, that's completely wrong.
I have tried escaping these semicolons with a backslash character, as one would with normal special characters that need escaping (like "\n" for a newline character). Unfortunately, it appears that "\;" is not a valid escape sequence, and it still fails.
Anybody have an idea how I can get it to recognize the semicolons as part of a literal string?
Good Gawd!! Frustrating.
omatic
I have been experimenting with making backups of my mySQL data from Powweb's mySQL servers, and then restoring that data to my copy of mySQL running locally, here in my 'lab'.
What I'm having a problem with, is that many of the INSERT statements contain semicolons within their string data. Unfortunately, the INSERT statement seems to ignore the fact that the ; character is in single quotes (part of a quoted literal string) and mySQL treats that semicolon as the end of the INSERT statement.
This kinda SUCKS, because it turns a single valid INSERT statement into two completely broken SQL statements by breaking it in half! Here's an example:INSERT INTO mysql_tablename (stringcolumn1, column2) VALUES ('a string literal; with a semicolon in it!', 'someothervalue');This gets interpreted, rather ungracefully, as two separate (and completely invalid) statements:INSERT INTO mysql_tablename (stringcolumn1, column2) VALUES ('a string literal;
--- and ---
with a semicolon in it!', 'someothervalue');Of course, that's completely wrong.
I have tried escaping these semicolons with a backslash character, as one would with normal special characters that need escaping (like "\n" for a newline character). Unfortunately, it appears that "\;" is not a valid escape sequence, and it still fails.
Anybody have an idea how I can get it to recognize the semicolons as part of a literal string?
Good Gawd!! Frustrating.
omatic