Bug #28125 ERROR 2013 when adding index.
Submitted: 26 Apr 2007 18:38 Modified: 3 Aug 2007 16:34
Reporter: Daniel Parry Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S2 (Serious)
Version:5.0, 5.1 OS:Any
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: assertion, Contribution, crash

[26 Apr 2007 18:38] Daniel Parry
Description:
Ran query:

alter table rwikihistory add index irwikihistory_ref (referenced(1024));

and encountered:

ERROR 2013 (HY000) at line 1096: Lost connection to MySQL server during query

mysql error log had:

070426 18:26:02InnoDB: Assertion failure in thread 2180815792 in file data0type.c line 69
InnoDB: Failing assertion: !(prefix_len % dtype->mbmaxlen)

Resolved the associated stack trace as:

0x8195b92 handle_segfault + 430
0xffffe420 _end + -141677224
0x82cfa36 row_ins_index_entry_step + 34
0x82cfa36 row_ins_index_entry_step + 34
0x82cfc70 row_ins + 540
0x82cfdac row_ins_step + 252
0x82d1416 row_insert_for_mysql + 430
0x823c68c write_row__11ha_innobasePc + 900
0x8259723 copy_data_between_tables__FP8st_tableT0Rt4List1Z12create_fieldbUiP8st_orderPUxT622enum_enable_or_disable + 1231
0x8258923 mysql_alter_table__FP3THDPcT1P24st_ha_create_informationP13st_table_listP10Alter_infoUiP8st_orderb + 7283
0x81a94ec mysql_execute_command__FP3THD + 5948
0x81ae98d mysql_parse__FP3THDPcUi + 281
0x81a65f4 dispatch_command__F19enum_server_commandP3THDPcUi + 1888
0x81a5e88 do_command__FP3THD + 216
0x81a53e4 handle_one_connection + 1104
0x40029b63 _end + 932242587
0x4019018a _end + 933710530

innodb config is:

innodb_data_home_dir = /usr/local/mysql5/data/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql5/data/
innodb_log_arch_dir = /usr/local/mysql5/data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_lock_wait_timeout = 50

How to repeat:
mysql> show create table rwikihistory\G
*************************** 1. row ***************************
       Table: rwikihistory
Create Table: CREATE TABLE `rwikihistory` (
  `id` varchar(36) NOT NULL,
  `version` datetime default NULL,
  `name` varchar(255) NOT NULL,
  `realm` varchar(255) default NULL,
  `referenced` text,
  `userid` varchar(64) default NULL,
  `owner` varchar(64) default NULL,
  `ownerRead` bit(1) default NULL,
  `ownerWrite` bit(1) default NULL,
  `ownerAdmin` bit(1) default NULL,
  `groupRead` bit(1) default NULL,
  `groupWrite` bit(1) default NULL,
  `groupAdmin` bit(1) default NULL,
  `publicRead` bit(1) default NULL,
  `publicWrite` bit(1) default NULL,
  `sha1` varchar(64) default NULL,
  `revision` int(11) default NULL,
  `rwikiobjectid` varchar(36) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `name_idx` (`name`),
  KEY `rwikiobjectid_idx` (`rwikiobjectid`),
  KEY `irwikihistory_name` (`name`),
  KEY `irwikihistory_realm` (`realm`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

alter table rwikihistory add index irwikihistory_ref (referenced(1024));
[26 Apr 2007 19:17] MySQL Verification Team
Thank you for the bug report. I was unable to repeat with current
source server, could you please provide more details about your
Linux box (hardware, distribution, 32-bit/64-bit and so on) and
are you using self-compiled server or our distro package:

[miguel@light 5.0]$ bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.42-debug Source distribution

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

mysql> CREATE TABLE `rwikihistory` (
    ->   `id` varchar(36) NOT NULL,
    ->   `version` datetime default NULL,
    ->   `name` varchar(255) NOT NULL,
    ->   `realm` varchar(255) default NULL,
    ->   `referenced` text,
    ->   `userid` varchar(64) default NULL,
    ->   `owner` varchar(64) default NULL,
    ->   `ownerRead` bit(1) default NULL,
    ->   `ownerWrite` bit(1) default NULL,
    ->   `ownerAdmin` bit(1) default NULL,
    ->   `groupRead` bit(1) default NULL,
    ->   `groupWrite` bit(1) default NULL,
    ->   `groupAdmin` bit(1) default NULL,
    ->   `publicRead` bit(1) default NULL,
    ->   `publicWrite` bit(1) default NULL,
    ->   `sha1` varchar(64) default NULL,
    ->   `revision` int(11) default NULL,
    ->   `rwikiobjectid` varchar(36) NOT NULL,
    ->   PRIMARY KEY  (`id`),
    ->   KEY `name_idx` (`name`),
    ->   KEY `rwikiobjectid_idx` (`rwikiobjectid`),
    ->   KEY `irwikihistory_name` (`name`),
    ->   KEY `irwikihistory_realm` (`realm`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.07 sec)

mysql> alter table rwikihistory add index irwikihistory_ref (referenced(1024));
Query OK, 0 rows affected, 1 warning (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> 

Thanks in advance.
[26 Apr 2007 19:41] Daniel Parry
Server info:

32 bit Linux 2.6.8-2-686-smp #1 SMP Tue Aug 16 12:08:30 UTC 2005 i686 GNU/Linux Debian.

Dual Intel(R) Xeon(TM) CPU 2.80GHz

Memory: 4GB RAM
[26 Apr 2007 19:42] Daniel Parry
Oh, and using distro package - not compiling myself. i.e. this one:

http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-5.0.37-linux-i686.tar.gz/from/http://ww...
[29 Apr 2007 4:54] Valeriy Kravchuk
I was also unable to repeat with 5.0.37. Please, send/upload your entire my.cnf.
[30 Apr 2007 4:46] Daniel Parry
The my.cnf I am using - hope this helps, and thanks for investigating!

[client]
port            = 3306
socket          = /tmp/mysql5.sock
max_allowed_packet = 64M

[mysqld]
port            = 3306
socket          = /tmp/mysql5.sock
skip-locking
key_buffer = 384M
max_connections = 192
max_allowed_packet = 64M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
thread_concurrency = 8

log-bin=/usr/local/mysql5-log-bin/servername-bin
server-id       = 5

innodb_data_home_dir = /usr/local/mysql5/data/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql5/data/
innodb_log_arch_dir = /usr/local/mysql5/data/
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 128M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 512M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout
[2 May 2007 17:24] Heikki Tuuri
If you are using UTF-8, then a prefix of 1024 characters is 3072 bytes, that is more than MySQL can handle. Maybe MySQL truncates the prefix to some length that is not divisible by 3?

Assigning this for Vasil to study. Maybe this is a MySQL server bug.
[22 May 2007 19:07] Vasil Dimov
Daniel,

I am also unable to repeat this. Can you confirm that you can trigger this on an empty table? Or you need some data stored in the table to get this error?
[22 May 2007 19:54] MySQL Verification Team
i can repeat this easily.  will upload a testcase later.

Version: '5.0.40-enterprise-gpl-nt'  socket: ''  port: 3306  MySQL Enterprise Server (GPL)
070522 21:54:01InnoDB: Assertion failure in thread 7656 in file .\data\data0type.c line 69
InnoDB: Failing assertion: !(prefix_len % dtype->mbmaxlen)
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html
InnoDB: about forcing recovery.
InnoDB: Thread 8980 stopped in file .\os\os0sync.c line 255
InnoDB: Thread 10940 stopped in file .\os\os0sync.c line 532
InnoDB: Thread 10004 stopped in file .\sync\sync0arr.c line 315
[22 May 2007 20:01] MySQL Verification Team
testcase for 5.0.44

Attachment: thread_11732_init.sql (application/octet-stream, text), 2.43 KiB.

[26 May 2007 8:03] Vasil Dimov
Shane, thank you for this test case. I am now able to repeat the bug.
I created a sample patch and after being discussed with the InnoDB team it will probably get committed.

This also happens on 5.1 and is not OS specific.
[30 May 2007 13:43] Vasil Dimov
After studying MySQL code carefully I spotted that this is a bug in MySQL, not InnoDB. Here is the problematic code:

sql/sql_table.cc (in 5.1):
2890       length= sql_field->key_length;
2891 
2892       if (column->length)
2893       {
2894         if (f_is_blob(sql_field->pack_flag))
2895         {
2896           if ((length=column->length) > max_key_length ||
2897               length > file->max_key_part_length())
2898           {
2899             length=min(max_key_length, file->max_key_part_length());
2900             if (key->type == Key::MULTIPLE)
2901             {
2902               /* not a critical problem */
2903               char warn_buff[MYSQL_ERRMSG_SIZE];
2904               my_snprintf(warn_buff, sizeof(warn_buff), ER(ER_TOO_LONG_KEY),
2905                           length);
2906               push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
2907                            ER_TOO_LONG_KEY, warn_buff);
2908             }
2909             else
2910             {
2911               my_error(ER_TOO_LONG_KEY,MYF(0),length);
2912               DBUG_RETURN(-1);
2913             }
2914           }
2915         }
2916         else if (!f_is_geom(sql_field->pack_flag) &&
2917                   (column->length > length ||
2918                    !Field::type_can_have_key_part (sql_field->sql_type) ||
2919                    ((f_is_packed(sql_field->pack_flag) ||
2920                      ((file->ha_table_flags() & HA_NO_PREFIX_CHAR_KEYS) &&
2921                       (key_info->flags & HA_NOSAME))) &&
2922                     column->length != length)))
2923         {
2924           my_message(ER_WRONG_SUB_KEY, ER(ER_WRONG_SUB_KEY), MYF(0));
2925           DBUG_RETURN(-1);
2926         }
2927         else if (!(file->ha_table_flags() & HA_NO_PREFIX_CHAR_KEYS))
2928           length=column->length;
2929       }
2930       else if (length == 0)
2931       {
2932         my_error(ER_WRONG_KEY_COLUMN, MYF(0), column->field_name);
2933           DBUG_RETURN(-1);
2934       }
2935       if (length > file->max_key_part_length() && key->type != Key::FULLTEXT)
2936       {
2937         length= file->max_key_part_length();
2938         /* Align key length to multibyte char boundary */
2939         length-= length % sql_field->charset->mbmaxlen;
2940         if (key->type == Key::MULTIPLE)
2941         {
2942           /* not a critical problem */
2943           char warn_buff[MYSQL_ERRMSG_SIZE];
2944           my_snprintf(warn_buff, sizeof(warn_buff), ER(ER_TOO_LONG_KEY),
2945                       length);
2946           push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
2947                        ER_TOO_LONG_KEY, warn_buff);
2948         }
2949         else
2950         {
2951           my_error(ER_TOO_LONG_KEY,MYF(0),length);
2952           DBUG_RETURN(-1);
2953         }
2954       }
2955       key_part_info->length=(uint16) length;

Notice that this code is aware that length must be a multiple of mbmaxlen but just fails to do it in some cases. The execution path that does this and that triggers InnoDB's assertion is the following:

1. length gets its value from line 2899 (which is file->max_key_part_length(), max_key_length is 3076 there).
2. Then the condition at line 2935 evaluates for false (obviously).
3. length never gets adjusted to multibyte char boundary as would be done in line 2939.

I have created a simple patch which moves the adjustment from line 2939 to before line 2955, so that it is always done. This patch fixes the assertion, which can be triggered with Shane's test submitted in this bug follow-up.
[30 May 2007 13:44] Vasil Dimov
Patch for fix the problem by always adjusting length to a multiple of mbmaxlen

Attachment: mysqlfix.diff (application/octet-stream, text), 1.33 KiB.

[7 Jun 2007 1:35] Timothy Smith
Vasil and Shane,

Thanks for the patches.  I'll move this through the review process.
[18 Jul 2007 7:13] 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/31057

ChangeSet@1.2533, 2007-07-18 12:13:45+05:00, ramil@mysql.com +3 -0
  Fix for bug #28125: ERROR 2013 when adding index.
  
  Problem: we may break a multibyte char sequence using a key 
  reduced to maximum allowed length for a storage engine
  (that leads to failed assertion in the innodb code, 
  see also #17530). 
  
  Fix: align truncated key length to multibyte char boundary.
[18 Jul 2007 8:49] Alexander Barkov
http://lists.mysql.com/commits/31057  is ok to push.
[2 Aug 2007 19:12] Bugs System
Pushed into 5.1.21-beta
[2 Aug 2007 19:15] Bugs System
Pushed into 5.0.48
[3 Aug 2007 16:34] Paul DuBois
Noted in 5.0.48, 5.1.21 changelogs.

Index creation could fail due to truncation of key values to the
maximum key length rather than to a mulitiple of the maximum
character length.