1

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?

Jeremy Wadhams
  • 958
  • 1
  • 9
  • 13

2 Answers2

3

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-characterbyte delimiters aren't really even supported.

if (user_supplied_query)
    actual_queries= parse_delimiter(tmp_string, &query_statements, delimiter[0]);
Michael - sqlbot
  • 22,715
  • 2
  • 49
  • 76
1

Give it up. Because (as Michael pointed out) mysqlslap is using naive string finding instead of a parser for the delimiter, if the delimiter you pick is anywhere in your --query or --create datasets, you're hosed.

Instead, open up the mysqldump file, and merge any multiline statements into one line and let mysqlslap continue to expect one-statement-per-line.

If you use Vim it's dead easy, just search for known multiline patterns (in mine it was only CREATE TABLE, I guess it might include stored procedures or triggers), then use J to join up all the lines in each statement.

Jeremy Wadhams
  • 958
  • 1
  • 9
  • 13