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
Triage: Triaged: D4 (Minor)

[22 Sep 2007 15:24] Hakan Küçükyılmaz
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.
[22 Sep 2007 16:50] Miguel Solorzano
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)
[23 Sep 2007 3:07] Hakan Küçükyılmaz
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)
[28 Sep 2007 16:48] Hakan Küçükyılmaz
falcon_bug_22161.test and falcon_bug_23189.test regressed because of this bug.
[1 Oct 2007 16:53] Hakan Küçükyılmaz
This bug keeps mysql-6.0-main from getting green.
[5 Oct 2007 16:02] Bugs System
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.
[8 Oct 2008 19:38] Konstantin Osipov
Bug#32475 ALTER TABLE: too many warnings on index creation was marked a duplicate of this bug.
[7 Jul 2009 16:17] Tomas Ulin
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)
[7 Jun 2012 16:13] Paul Dubois
Noted in 5.6.6 changelog.

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