Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

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]);
share|improve this answer
    
I really like the idea of changing the delimiter, but the crux of the problem seems to be that it has to be one character (2 char delimiters behaved incomprehensibly) and if that one char is in your data, you're boned. (I also couldn't get exotic unicode that I knew wasn't in my dataset to work.) –  Jeremy Wadhams May 10 '13 at 2:33
up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.