Bug #24601 MySQLd crashes upon creation of an index
Submitted: 26 Nov 2006 17:04 Modified: 27 Dec 2006 13:12
Reporter: Graeme Mathieson Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S2 (Serious)
Version:5.0.26 OS:MacOS (Mac OS X 10.4.8)
Assigned to: Assigned Account CPU Architecture:Any

[26 Nov 2006 17:04] Graeme Mathieson 
Description:
While trying to create an index on a full text field in a table, MySQLd is crashing.  The query I entered was:

    CREATE INDEX `index_comatose_pages_on_full_path` ON comatose_pages(full_path(1024));

which I would have expected to successfully create the index (though I am quickly trying to learn how indexes work, so I might be wrong!).  Instead it causes MySQLd to segfault.  I'll attach the crashreporter log.  The full_path field is a 'mediumtext' type.

Hmm, having tried it out on a fresh table along the lines of:

mysql> CREATE TABLE cheese (
    ->     id INT PRIMARY KEY,
    ->     text MEDIUMTEXT);
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE INDEX `index_cheese_on_text` ON cheese(text(1024)); 
Query OK, 0 rows affected, 1 warning (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

it works fine.  The warning is to indicate that my key length is too long.  However, if I reduce the key length to 950 in my original index creation, it still crashes.  Since it appears to be related to my particular schema, here is that table in all its glorious detail:

mysql> show full columns from comatose_pages;
+-------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field       | Type         | Collation       | Null | Key | Default | Extra          | Privileges                      | Comment |
+-------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id          | int(11)      | NULL            | NO   | PRI | NULL    | auto_increment | select,insert,update,references |         | 
| parent_id   | int(11)      | NULL            | YES  |     | NULL    |                | select,insert,update,references |         | 
| full_path   | mediumtext   | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         | 
| title       | varchar(255) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         | 
| slug        | varchar(255) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         | 
| keywords    | varchar(255) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         | 
| body        | mediumtext   | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         | 
| author      | varchar(255) | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         | 
| position    | int(11)      | NULL            | YES  |     | 0       |                | select,insert,update,references |         | 
| version     | int(11)      | NULL            | YES  |     | NULL    |                | select,insert,update,references |         | 
| updated_on  | datetime     | NULL            | YES  |     | NULL    |                | select,insert,update,references |         | 
| created_on  | datetime     | NULL            | YES  |     | NULL    |                | select,insert,update,references |         | 
| filter_type | varchar(25)  | utf8_general_ci | YES  |     | NULL    |                | select,insert,update,references |         | 
+-------------+--------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+

Hopefully that won't look too ugly to parse your end. :)

Ah, bingo.  If I remove all the data from the table, the index creation works.  So it's obviously something in `full_path` that's causing the problem?  Lucky for you I only had 6 records in there.  So let's add them back.  Aha, it crashes when I have this one particular record in there with the empty string in `full_path`.  Let's try changing it to something else.  No, that's still crashing, no matter what the content.

Pass.  Any ideas?

Cheers,

Graeme

How to repeat:
mysql> CREATE TABLE comatose_pages (
    ->   `id` int(11) DEFAULT NULL auto_increment PRIMARY KEY,
    ->   `parent_id` int(11),
    ->   `full_path` text,
    ->   `title` varchar(255),
    ->   `slug` varchar(255),
    ->   `keywords` varchar(255),
    ->   `body` text,
    ->   `filter_type` varchar(25) DEFAULT 'Textile',
    ->   `author` varchar(255),
    ->   `position` int(11) DEFAULT 0,
    ->   `version` int(11),
    ->   `updated_on` datetime,
    ->   `created_on` datetime) ENGINE=InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> INSERT INTO comatose_pages 
    ->   (
    ->     `created_on`, `slug`, `filter_type`, `body`, `title`,
    ->     `updated_on`, `author`, `version`, `parent_id`, `position`,
    ->     `full_path`, `keywords`
    ->   )
    ->   VALUES
    ->   (
    ->     '2006-11-26 16:49:54', 'home-page', 'Textile',
    ->     'h1. Welcome\n\nYour content goes here...', 'Home Page',
    ->     '2006-11-26 16:49:54', 'System', 1, NULL, 1, '', NULL
    ->   );
Query OK, 1 row affected (0.00 sec)

mysql> CREATE INDEX `index_comatose_pages_on_full_path`
    ->   ON comatose_pages(full_path(1024));
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

Suggested fix:
Not so much a fix, but a crash dump.  I figured this would do here.  Note that it's thread 9 it's crashing in, at `dtype_get_at_most_n_mbchars`:

Host Name:      lagavulin
Date/Time:      2006-11-26 16:51:24.455 +0000
OS Version:     10.4.8 (Build 8L127)
Report Version: 4

Command: mysqld
Path:    /opt/local/libexec/mysqld
Parent:  sh [124]

Version: ??? (???)

PID:    25585
Thread: 9

Exception:  EXC_BAD_ACCESS (0x0001)
Codes:      KERN_PROTECTION_FAILURE (0x0002) at 0x00000000

Thread 0:
0   libSystem.B.dylib 	0x9001f08c select + 12
1   mysqld            	0x0009f64c handle_connections_sockets + 348
2   mysqld            	0x000a099c main + 3324
3   mysqld            	0x00002a5c _start + 760
4   mysqld            	0x00002760 start + 48

Thread 1:
0   libSystem.B.dylib 	0x9002bbc8 semaphore_wait_signal_trap + 8
1   libSystem.B.dylib 	0x900306ac pthread_cond_wait + 480
2   mysqld            	0x002c71a0 os_event_wait + 68
3   mysqld            	0x002c6114 os_aio_simulated_handle + 1756
4   mysqld            	0x00290c78 fil_aio_wait + 172
5   mysqld            	0x001d84f8 io_handler_thread + 84
6   libSystem.B.dylib 	0x9002b508 _pthread_body + 96

Thread 2:
0   libSystem.B.dylib 	0x9002bbc8 semaphore_wait_signal_trap + 8
1   libSystem.B.dylib 	0x900306ac pthread_cond_wait + 480
2   mysqld            	0x002c71a0 os_event_wait + 68
3   mysqld            	0x002c6114 os_aio_simulated_handle + 1756
4   mysqld            	0x00290c78 fil_aio_wait + 172
5   mysqld            	0x001d84f8 io_handler_thread + 84
6   libSystem.B.dylib 	0x9002b508 _pthread_body + 96

Thread 3:
0   libSystem.B.dylib 	0x9002bbc8 semaphore_wait_signal_trap + 8
1   libSystem.B.dylib 	0x900306ac pthread_cond_wait + 480
2   mysqld            	0x002c71a0 os_event_wait + 68
3   mysqld            	0x002c6114 os_aio_simulated_handle + 1756
4   mysqld            	0x00290c78 fil_aio_wait + 172
5   mysqld            	0x001d84f8 io_handler_thread + 84
6   libSystem.B.dylib 	0x9002b508 _pthread_body + 96

Thread 4:
0   libSystem.B.dylib 	0x9002bbc8 semaphore_wait_signal_trap + 8
1   libSystem.B.dylib 	0x900306ac pthread_cond_wait + 480
2   mysqld            	0x002c71a0 os_event_wait + 68
3   mysqld            	0x002c6114 os_aio_simulated_handle + 1756
4   mysqld            	0x00290c78 fil_aio_wait + 172
5   mysqld            	0x001d84f8 io_handler_thread + 84
6   libSystem.B.dylib 	0x9002b508 _pthread_body + 96

Thread 5:
0   libSystem.B.dylib 	0x9001f08c select + 12
1   mysqld            	0x002c7b4c os_thread_sleep + 76
2   mysqld            	0x001d67d0 srv_lock_timeout_and_monitor_thread + 156
3   libSystem.B.dylib 	0x9002b508 _pthread_body + 96

Thread 6:
0   libSystem.B.dylib 	0x9001f08c select + 12
1   mysqld            	0x002c7b4c os_thread_sleep + 76
2   mysqld            	0x001d6ebc srv_error_monitor_thread + 524
3   libSystem.B.dylib 	0x9002b508 _pthread_body + 96

Thread 7:
0   libSystem.B.dylib 	0x9001f08c select + 12
1   mysqld            	0x002c7b4c os_thread_sleep + 76
2   mysqld            	0x001d7464 srv_master_thread + 772
3   libSystem.B.dylib 	0x9002b508 _pthread_body + 96

Thread 8:
0   libSystem.B.dylib 	0x900c6cc0 sigwait + 12
1   mysqld            	0x00099ccc signal_hand + 412
2   libSystem.B.dylib 	0x9002b508 _pthread_body + 96

Thread 9 Crashed:
0   mysqld            	0x002bb570 dtype_get_at_most_n_mbchars + 208
1   mysqld            	0x0021095c row_ins_index_entry_set_vals + 124
2   mysqld            	0x002109bc row_ins_index_entry_step + 40
3   mysqld            	0x00210c94 row_ins + 684
4   mysqld            	0x00210e0c row_ins_step + 304
5   mysqld            	0x001fe298 row_insert_for_mysql + 516
6   mysqld            	0x0017367c ha_innobase::write_row(char*) + 1180
7   mysqld            	0x0017c444 mysql_alter_table(THD*, char*, char*, st_ha_create_information*, st_table_list*, List<create_field>&, List<Key>&, unsigned, st_order*, bool, st_alter_info*, bool) + 7044
8   mysqld            	0x000abccc mysql_create_index(THD*, st_table_list*, List<Key>&) + 204
9   mysqld            	0x000b18e4 mysql_execute_command(THD*) + 5716
10  mysqld            	0x000b8040 mysql_parse(THD*, char*, unsigned) + 656
11  mysqld            	0x000b8968 dispatch_command(enum_server_command, THD*, char*, unsigned) + 2168
12  mysqld            	0x000ba624 handle_one_connection + 2628
13  libSystem.B.dylib 	0x9002b508 _pthread_body + 96

Thread 9 crashed with PPC Thread State 64:
  srr0: 0x00000000002bb570 srr1: 0x100000000200f030                        vrsave: 0x0000000000000000
    cr: 0x24444404          xer: 0x0000000000000000   lr: 0x00000000002bb564  ctr: 0x0000000090000dc8
    r0: 0x00000000002bb564   r1: 0x00000000f0467f70   r2: 0x0000000000000000   r3: 0x000000000000018d
    r4: 0x0000000000000000   r5: 0x00000000000003e8   r6: 0xffffffffffffffff   r7: 0x000000000000018d
    r8: 0x000000000000000a   r9: 0x00000000003fc538  r10: 0x0000000090015568  r11: 0x00000000a0006208
   r12: 0x0000000090000dc8  r13: 0x0000000000000000  r14: 0x0000000000000000  r15: 0x0000000000000000
   r16: 0x0000000001872800  r17: 0x000000000185d1a4  r18: 0x000000000185bf10  r19: 0x0000000000000000
   r20: 0x000000000185bed8  r21: 0x000000000185bed8  r22: 0x000000000185c0d0  r23: 0x0000000000000001
   r24: 0x000000000143fb60  r25: 0x0000000001443ce0  r26: 0x000000000143f060  r27: 0x000000000143be60
   r28: 0x0000000000000006  r29: 0x000000000143e0dc  r30: 0x00000000000002ff  r31: 0x00000000002bb4b4

Binary Images Description:
    0x1000 -   0x3e3fff mysqld 	/opt/local/libexec/mysqld
  0x4e9000 -   0x4fbfff libz.1.dylib 	/opt/local/lib/libz.1.dylib
  0x605000 -   0x63ffff libssl.0.9.8.dylib 	/opt/local/lib/libssl.0.9.8.dylib
  0x655000 -   0x756fff libcrypto.0.9.8.dylib 	/opt/local/lib/libcrypto.0.9.8.dylib
0x8fe00000 - 0x8fe51fff dyld 45.3	/usr/lib/dyld
0x90000000 - 0x901bcfff libSystem.B.dylib 	/usr/lib/libSystem.B.dylib
0x90214000 - 0x90219fff libmathCommon.A.dylib 	/usr/lib/system/libmathCommon.A.dylib
0x91422000 - 0x9142dfff libgcc_s.1.dylib 	/usr/lib/libgcc_s.1.dylib
0x94b07000 - 0x94b78fff libstdc++.6.dylib 	/usr/lib/libstdc++.6.dylib
[27 Nov 2006 9:36] Sveta Smirnova 
Thank you for the report.

I can not repeat it using last BK sources.
[27 Nov 2006 13:12] Heikki Tuuri 
Graeme,

please post your my.cnf.

Have you built 5.0.26 yourself?

Is it a 64-bit computer?

/*************************************************************************
Determine how many bytes the first n characters of the given string occupy.
If the string is shorter than n characters, returns the number of bytes
the characters in the string occupy. */

ulint
dtype_get_at_most_n_mbchars(
/*========================*/
                                        /* out: length of the prefix,
                                        in bytes */
        const dtype_t*  dtype,          /* in: data type */
        ulint           prefix_len,     /* in: length of the requested
                                        prefix, in characters, multiplied by
                                        dtype_get_mbmaxlen(dtype) */
        ulint           data_len,       /* in: length of str (in bytes) */
        const char*     str)            /* in: the string whose prefix
                                        length is being determined */
{
#ifndef UNIV_HOTBACKUP
        ut_a(data_len != UNIV_SQL_NULL);
        ut_ad(!dtype->mbmaxlen || !(prefix_len % dtype->mbmaxlen));

        if (dtype->mbminlen != dtype->mbmaxlen) {
                ut_a(!(prefix_len % dtype->mbmaxlen));
                return(innobase_get_at_most_n_mbchars(
                                dtype_get_charset_coll(dtype->prtype),
                                prefix_len, data_len, str));
        }

        if (prefix_len < data_len) {

                return(prefix_len);

        }

        return(data_len);
#else /* UNIV_HOTBACKUP */
        /* This function depends on MySQL code that is not included in
        InnoDB Hot Backup builds.  Besides, this function should never
        be called in InnoDB Hot Backup. */
        ut_error;
#endif /* UNIV_HOTBACKUP */
}
[28 Dec 2006 0:00] Bugs System 
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".