MySQL Bugs: #31149: "Specified key was too long" is printed three times

Bug #31149	"Specified key was too long" is printed three times
Submitted:	22 Sep 2007 15:24	Modified:	7 Jun 2012 16:13
Reporter:	Hakan Küçükyılmaz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.2, 6.0-falcon,5.1	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The warning "Specified key was too long" is printed three times.

How to repeat:
[17:17] root@test>CREATE TABLE t2 (a varchar(30000)) Engine MyISAM;
Query OK, 0 rows affected (0.01 sec)

[17:17] root@test>CREATE INDEX i1 ON t2 (a);
Query OK, 0 rows affected, 3 warnings (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

[17:18] root@test>show warnings;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 1000 bytes |
| Warning | 1071 | Specified key was too long; max key length is 1000 bytes |
| Warning | 1071 | Specified key was too long; max key length is 1000 bytes |
+---------+------+----------------------------------------------------------+
3 rows in set (0.00 sec)

Suggested fix:
Print the warning once.

Thank you for the bug report. I got 2 warnings:

[miguel@skybr 5.2]$ bin/mysql -uroot db6
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.2.6-alpha-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE t2 (a varchar(30000)) Engine MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE INDEX i1 ON t2 (a);
Query OK, 0 rows affected, 2 warnings (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 1000 bytes | 
| Warning | 1071 | Specified key was too long; max key length is 1000 bytes | 
+---------+------+----------------------------------------------------------+
2 rows in set (0.00 sec)

Just tried with 5.2.6-alpha from bk and it gives three warnings:

[04:38] root@test>CREATE TABLE t2 (a varchar(30000)) Engine MyISAM;
Query OK, 0 rows affected (0.01 sec)

[05:04] root@test>CREATE INDEX i1 ON t2 (a);
Query OK, 0 rows affected, 3 warnings (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

[05:05] root@test>show warnings;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 1000 bytes |
| Warning | 1071 | Specified key was too long; max key length is 1000 bytes |
| Warning | 1071 | Specified key was too long; max key length is 1000 bytes |
+---------+------+----------------------------------------------------------+
3 rows in set (0.00 sec)

falcon_bug_22161.test and falcon_bug_23189.test regressed because of this bug.

This bug keeps mysql-6.0-main from getting green.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/34989

ChangeSet@1.2595, 2007-10-05 09:01:44-07:00, acurtis@xiphis.org +35 -0
  Bug#31149
    "Warnings and Error messages printed multiple times"
    Many things may result in duplicate error messages which would give user no additional
    information. These messages may be result of different phases of the same action
    (prepare to do action vs executing action) or as consequence of similar but not
    identical tests.
    If a message is pushed and later another identical message but with a higher level
    is pushed, the existing message has its level upgraded. For all duplicates, the
    warn_count property is incremented so no information is lost but we do not do anything
    with this property yet.

Bug#32475 ALTER TABLE: too many warnings on index creation was marked a duplicate of this bug.

verified in 5.1 as well... albeit only 2 times...

mysql> show warnings;
+---------+------+----------------------------------------------------------+
| Level   | Code | Message                                                  |
+---------+------+----------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 1000 bytes | 
| Warning | 1071 | Specified key was too long; max key length is 1000 bytes | 
+---------+------+----------------------------------------------------------+
2 rows in set (0.00 sec)

Noted in 5.6.6 changelog.

Redundant "Specified key was too long" messages could be produced by
index-creation operations.