Bug #55265 mysqlslap Option --auto-generate-sql-secondary-indexes doesnt work
Submitted: 14 Jul 2010 15:34 Modified: 31 Mar 2015 14:18
Reporter: Andreas Braza Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.1.48 OS:Linux (SLES11 x64)
Assigned to: CPU Architecture:Any
Tags: auto-generate-sql-secondary-indexes, mysqlslap, UUID

[14 Jul 2010 15:34] Andreas Braza 
Description:
Using Option "--auto-generate-sql-secondary-indexes=n" (n > 1) together with "--auto-generate-sql" doesnt work in mysqlslap because it wants to insert a UUID with 36-digits into a varchar(32).

The error message is ...
  Cannot run query INSERT INTO t1 VALUES (uuid(), 1, 'a') 
  ERROR : Data too long for column 'id0' at row 1

How to repeat:
Just start mysqlslap --auto-generate-sql-secondary-indexes=1 --auto-generate-sql

Suggested fix:
Increase column with for secondary index to 36 chars.
[14 Jul 2010 15:40] Valeriy Kravchuk 
I can not repeat this with current 5.1.49 on Mac OS X:

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysqlslap --auto-generate-sql-secondary-indexes=1 --auto-generate-sql
Benchmark
	Average number of seconds to run all queries: 0.005 seconds
	Minimum number of seconds to run all queries: 0.005 seconds
	Maximum number of seconds to run all queries: 0.005 seconds
	Number of clients running queries: 1
	Average number of queries per client: 0

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysqlslap --auto-generate-sql-secondary-indexes=2 --auto-generate-sql
Benchmark
	Average number of seconds to run all queries: 0.007 seconds
	Minimum number of seconds to run all queries: 0.007 seconds
	Maximum number of seconds to run all queries: 0.007 seconds
	Number of clients running queries: 1
	Average number of queries per client: 0
[14 Jul 2010 17:20] MySQL Verification Team 
I couldn't repeat on Windows/Fedora X86_64 too.
[14 Jul 2010 18:16] Andreas Braza 
I can repeat it also on Windows with 5.1.48 ... and I can solve the problem changing sql_mode from 'traditional' to ''

In this case mysqlslap is able to insert a 36 chars long string into a varchar(32) ... it will be truncated
[15 Jul 2010 7:55] Sveta Smirnova 
Thank you for the feedback.

Verified as described. This is still a bug in mysqlslap which creates invalid queries.
[17 Mar 2015 6:01] Tsubasa Tanaka 
5.1.73, 5.5.42, 5.6.23, 5.7.6 are still affected.
Not only --auto-generate-sql-secondary-indexes, --auto-generate-sql-guid-primary is affected too.

Earlier than 5.7, default sql_mode is not strict.
But 5.7's default sql_mode includes STRICT_TRANS_TABLES and default_storage_engine is InnoDB, this issue will be more apparent.
[17 Mar 2015 6:02] Tsubasa Tanaka 
patch for mysqlslap.c based on 5.7.6-m16

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bug55265.patch (application/octet-stream, text), 1.29 KiB.
[25 Mar 2015 15:00] Georgi Kodinov 
Thank you tsubasa tanaka for your contribution !
[31 Mar 2015 14:18] Paul DuBois 
Noted in 5.7.8, 5.8.0 changelogs.

For mysqlslap, the combination of
--auto-generate-sql-secondary-indexes and --auto-generate-sql failed
because it tried to insert 36-digit UUID values into a VARCHAR(32)
column. Thanks to Tsubasa Tanaka for the patch.
[7 Apr 2016 7:45] MySQL Verification Team 
Bug #80987 marked as duplicate of this