| 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: | |
| 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 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.
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));