Thesqlslap chokes on strings that contain “;” delimiter

MySQLmysqlslap

I'm having a problem passing preexisting files full of SQL statements into mysqlslap.

For example, I have a file named create.sql that contains my table structure (as dumped by mysqldump), with normal ; delimiting.

I also have a file called slap.sql (actually a slightly munged general-log-file, but this is a tiny example that reproduces the error) that contains

INSERT INTO comments VALUES ("I like winks ;) and frowns :(");

And I run:

mysqlslap --delimiter=";" --create create.sql --query thing.sql

I get the error:

mysqlslap: Cannot run query INSERT INTO comments VALUES ("I like winks  
ERROR : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"I like winks' at line 1

Which is consistent with MySQL terminating the statement at the ; that's in the middle of a string.

What can I do to make mysqlslap take --create data from mysqldump and not hork on semicolons embedded in strings in the --query data? Is it possible that mysqlslap does not follow normal parsing rules when you pass a file in to the –query parameter?

Best Answer

Looking briefly at the source code, it looks like mysqlslap doesn't do any sophisticated parsing for delimiters... it's just splitting strings.

If you write each of your queries on a single line, you don't have to terminate them -- the default statement delimiter is a newline.

const char *delimiter= "\n";

With one query per line, it splits the line and executes each statement.

The ; delimiter isn't actually needed at the end of each query to make the --query file valid and the server has no problem executing the example query you provided with the semicolon removed.

You could modify the output file from mysqldump to use a different delimiter, like $$, using sed or perl and then use that delimiter in your query file also.

mysqldump [args] | perl -pe 's/;$/\n\$\$\n/g' > create.sql

edit: oops, yeah, this is really primitive... multi-~~character~~byte delimiters aren't really even supported.

if (user_supplied_query)
    actual_queries= parse_delimiter(tmp_string, &query_statements, delimiter[0]);

Related Solutions

MySQL – LOAD DATA INFILE – Handling unescaped NULL in INFILE or creating INFILE with escaped NULL

Have you looked into the SELECT INTO OUTFILE syntax?

Here is a quote on the NULL string issue:

If the FIELDS ESCAPED BY character is empty, no characters are escaped and NULL is output as NULL, not \N. It is probably not a good idea to specify an empty escape character, particularly if field values in your data contain any of the characters in the list just given.

So make sure you indicate a FIELDS ESCAPED BY clause to get NULLs written as \N

Hope this helps

Thesqldump: flush-privileges option

The documentation is misleading. I read it exactly the same way you do, which isn't what the utility does.

Adding --flush-privileges causes mysqldump to include the following in the backup file, after dumping the mysql schema...

--
-- Flush Grant Tables
--

/*! FLUSH PRIVILEGES */;

...which of course causes the server where the dump is being restored to re-read the potentially-changed grant tables.

And, that's all it does.

Confirmation of this can be found in the source code for mysqldump "10.13" (the version bundled with MySQL Server 5.5.30):

if (flush_privileges && using_mysql_db == 0)
{
  fprintf(md_result_file,"\n--\n-- Flush Grant Tables \n--\n");
  fprintf(md_result_file,"\n/*! FLUSH PRIVILEGES */;\n");
}

The using_mysql_db == 0 comparison was confusing at first, but it turns out to be the return value from a string comparison function where "0" means "identical."

Best Answer

Related Solutions

MySQL – LOAD DATA INFILE – Handling unescaped NULL in INFILE or creating INFILE with escaped NULL

Thesqldump: flush-privileges option

Related Question