Bug #79856 mysqlslap adds '\0' symbol in the end of autogenerated CREATE TABLE statement
Submitted: 5 Jan 2016 21:03 Modified: 6 Jan 2016 7:48
Reporter: Sveta Smirnova (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.7.10, 5.6.28 OS:Any
Assigned to: CPU Architecture:Any

[5 Jan 2016 21:03] Sveta Smirnova
Description:
mysqlslap adds extra '\0' symbol in the end of the autogenerated CREATE TABLE statement

How to repeat:
1. Start server with binary log enabled, for example, from mysql-test:

./mtr --start alias --mysqld=--plugin-dir=$HOME/build/mysql-5.7/lib/plugin --mysqld=--character-set-server=utf8 --mysqld=--log-bin &

2. Run mysqlslap with --auto-generate-sql option:

../bin/mysqlslap -h127.0.0.1 -P13000 -uroot  --concurrency=50 --iterations=200 --number-int-cols=2 --number-char-cols=3   --auto-generate-sql

3. Check binary log content:

$../bin/mysqlbinlog var/mysqld.1/data/0.000005 
...
# at 510
#160105 22:54:37 server id 1  end_log_pos 575 CRC32 0x53f2e1e5  Anonymous_GTID  last_committed=2        sequence_number=3
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 575
#160105 22:54:37 server id 1  end_log_pos 779 CRC32 0x3cecd4a0  Query   thread_id=33812 exec_time=0     error_code=0
use `mysqlslap`/*!*/;
SET TIMESTAMP=1452027277/*!*/;
CREATE TABLE `t1` (intcol1 INT(32) ,intcol2 INT(32) ,charcol1 VARCHAR(128),charcol2 VARCHAR(128),charcol3 VARCHAR(128))^@
/*!*/;

4. Try to import data into test database:

$../bin/mysqlbinlog var/mysqld.1/data/0.000005 | mysqlmtr test
ERROR at line 45: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: 'CREATE TABLE `t1` (intcol1 INT(32) ,intcol2 INT(32) ,charcol1 VARCHAR(128),charcol2 VARCHAR(128),charcol3 VARCHAR(128))'.

Suggested fix:
Don't add extra character in the end of auto-generated CREATE TABLE statement.
[6 Jan 2016 7:48] MySQL Verification Team
Hello Sveta,

Thank you for the report.
Verified as described on 5.7.10 build.

Thanks,
Umesh
[6 Jan 2016 7:49] MySQL Verification Team
// 5.7.10

[root@cluster-repo ~]# mysqlslap -uroot  -p --concurrency=50 --iterations=200 --number-int-cols=2 --number-char-cols=3   --auto-generate-sql
Enter password:
Benchmark
        Average number of seconds to run all queries: 0.207 seconds
        Minimum number of seconds to run all queries: 0.165 seconds
        Maximum number of seconds to run all queries: 0.379 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0

[root@cluster-repo ~]# mysqlbinlog /var/lib/mysql/cluster-repo-bin.000004 > 1.sql

# at 563
#160108 20:13:29 server id 1  end_log_pos 767 CRC32 0x0f0c1888  Query   thread_id=1019  exec_time=0     error_code=0
use `mysqlslap`/*!*/;
SET TIMESTAMP=1452264209/*!*/;
CREATE TABLE `t1` (intcol1 INT(32) ,intcol2 INT(32) ,charcol1 VARCHAR(128),charcol2 VARCHAR(128),charcol3 VARCHAR(128))^@
/*!*/;

-- import fails

[root@cluster-repo ~]# mysqlbinlog /var/lib/mysql/cluster-repo-bin.000004 |mysql -uroot -p
Enter password:
ERROR at line 45: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: 'CREATE TABLE `t1` (intcol1 INT(32) ,intcol2 INT(32) ,charcol1 VARCHAR(128),charcol2 VARCHAR(128),charcol3 VARCHAR(128))'.
[root@cluster-repo ~]#