MySQL Bugs: #80987: mysqlslap auto-generate-sql-guid-primary

Bug #80987	mysqlslap auto-generate-sql-guid-primary
Submitted:	7 Apr 2016 7:03	Modified:	7 Apr 2016 7:44
Reporter:	Ted Wennmark	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Running mysqlslap using "--auto-generate-sql-guid-primary" will fail on mysql 5.7 with stricter settings for SQL_MODE.

Runing with default values you get error:
ted@ted-PORTEGE-Z30-A:~/lek$ mysqlslap -S/home/ted/lek/my.sock -uroot -proot --auto-generate-sql          --auto-generate-sql-guid-primary --auto-generate-sql-secondary-indexes=2          --auto-generate-sql-write-number=10000 --auto-generate-sql-execute-number=300000 -i10
mysqlslap: Cannot run query INSERT INTO t1 VALUES (uuid(),uuid(),uuid(),1804289383,'mxvtvmC9127qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkdekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxBL') ERROR : Data too long for column 'id' at row 1

Reason is column id is not large enough for uuid() value:
mysql> show create table t1;                                                                                                                                                                                  
| t1    | CREATE TABLE `t1` (
  `id` varchar(32) NOT NULL,
  `id0` varchar(32) DEFAULT NULL,
  `id1` varchar(32) DEFAULT NULL,

mysql> SELECT LENGTH(uuid());                                                                                                                                                                                    +----------------+
| LENGTH(uuid()) |
+----------------+
|             36 |
+----------------+
1 row in set (0,00 sec)

Setting SQL_MODE to '' we silently ignore this truncation. 

How to repeat:
Follow steps above.

Suggested fix:
Increase size of column in mysqlslap.t1 table to at least varchar(36)

Hi Ted,

Thank you for the report.
Could you please confirm the exact 5.7 build you are using for tests? Imho, this issue was fixed in 5.7.8 post Bug #55265. Also, this can be confirmed from schema inspection in 5.7.11:

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.11: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.7.11 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show variables like 'sql_mode';
+---------------+---------------------------------------+
| Variable_name | Value                                 |
+---------------+---------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,STRICT_ALL_TABLES |
+---------------+---------------------------------------+
1 row in set (0.00 sec)

mysql> desc mysqlslap.t1;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | varchar(36)  | NO   | PRI | NULL    |       |
| id0      | varchar(36)  | YES  | UNI | NULL    |       |
| id1      | varchar(36)  | YES  | UNI | NULL    |       |
| intcol1  | int(32)      | YES  |     | NULL    |       |
| charcol1 | varchar(128) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql>

For 5.6, issue still exists as noted in Bug #80329

Thanks,
Umesh

Sorry, did not see that bug report.
Worked good with 5.7.11

Thanks!