Description:
Hi,
I've just experienced a annoying problem with MySQL 4.1.2.
I've used ALTER TABLE on some of my table on my table, and those tables were in use during the manipulation.
Sometimes during the ALTER TABLE, MySQL could crash, with always the same stack trace :
0x80c2307 handle_segfault + 647
0x82f5878 pthread_sighandler + 176
0x831c09f strlen + 15
0x80bbd2b Protocol::send_fields(List<Item>*, unsigned) + 523
0x80b4c28 select_send::send_fields(List<Item>&, unsigned) + 40
0x81047c2 do_select(JOIN*, List<Item>*, st_table*, Procedure*) + 754
0x80fc616 JOIN::exec() + 3606
0x80fcf0d _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 173
0x80f9927 handle_select(THD*, st_lex*, select_result*) + 295
0x80d49dd mysql_execute_command(THD*) + 717
0x80da0f0 mysql_parse(THD*, char*, unsigned) + 240
0x80d35c0 dispatch_command(enum_server_command, THD*, char*, unsigned) + 1008
0x80d3181 do_command(THD*) + 145
0x80d2923 handle_one_connection + 851
0x82f329a pthread_start_thread + 218
0x832361a thread_start + 4
Unfortunately I'm still unable to repeat the problem on a simple testcase, but it's perhaps possible to exhibit the problem in creating a script which do insert/select/delete on the table while doing the alter command.
Is this possible that the list of Item sent to Protocol::send_fields became invalid because of the alter table command (missing mutex ?), and thus later in the function item->make_field(&field); stores an invalid address in field (which could explain the segfault in the strlen function) ?
If there is no evident bug in the code, I could try to run MySQL in debug mode and provide you a trace of the problem.
Regards,
Jocelyn
How to repeat:
An example of commands which causes a crash :
CREATE TABLE `prive` (
`titre` char(80) NOT NULL default '',
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`auteur` char(35) NOT NULL default '',
`icone` tinyint(2) unsigned NOT NULL default '0',
`lastauteur` char(35) NOT NULL default '',
`nbrep` smallint(6) unsigned NOT NULL default '0',
`dest` char(35) NOT NULL default '',
`lu` tinyint(1) unsigned NOT NULL default '0',
`vue` mediumint(8) unsigned NOT NULL default '0',
`ludest` tinyint(1) unsigned NOT NULL default '0',
`ouvert` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`numeropost`),
KEY `date` (`date`),
KEY `dest` (`dest`,`ludest`),
KEY `auteur` (`auteur`,`lu`),
KEY `auteur_2` (`auteur`,`date`),
KEY `dest_2` (`dest`,`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 DELAY_KEY_WRITE=1;
ALTER TABLE prive DROP KEY auteur_2;
Query OK, 183841 rows affected (6.51 sec)
Records: 183841 Duplicates: 0 Warnings: 0
ALTER TABLE prive DROP KEY dest_2;
Query OK, 183841 rows affected (5.56 sec)
Records: 183841 Duplicates: 0 Warnings: 0
ALTER TABLE prive ADD KEY (dest,auteur,date);
Query OK, 183841 rows affected (6.95 sec)
Records: 183841 Duplicates: 0 Warnings: 0
EXPLAIN SELECT LOWER(auteur),titre,date,numeropost FROM prive WHERE auteur='joce' AND dest='marc' ORDER BY date DESC LIMIT 3;
+----+-------------+-------+------+--------------------+--------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+--------------------+--------+---------+-------------+------+-------------+
| 1 | SIMPLE | prive | ref | dest,auteur,dest_2 | dest_2 | 70 | const,const | 1 | Using where |
+----+-------------+-------+------+--------------------+--------+---------+-------------+------+-------------+
ALTER TABLE prive DROP KEY dest_2;
Query OK, 183841 rows affected (5.41 sec)
Records: 183841 Duplicates: 0 Warnings: 0
ALTER TABLE prive ADD KEY (dest,auteur);
Query OK, 183841 rows affected (6.86 sec)
Records: 183841 Duplicates: 0 Warnings: 0
EXPLAIN SELECT LOWER(auteur),titre,date,numeropost FROM prive WHERE auteur='joce' AND dest='marc' ORDER BY date DESC LIMIT 3;
ERROR 2006 (HY000): MySQL server has gone away
=> first crash
ALTER TABLE prive DROP KEY dest_2;
Query OK, 183841 rows affected (5.88 sec)
Records: 183841 Duplicates: 0 Warnings: 0
ALTER TABLE prive ADD KEY (dest,auteur,date);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 26
Current database: Hardwarefr
Query OK, 183841 rows affected (6.76 sec)
Records: 183841 Duplicates: 0 Warnings: 0
=> once again a crash
EXPLAIN SELECT LOWER(auteur),titre,date,numeropost FROM prive WHERE auteur='joce' AND dest='marc' ORDER BY date DESC LIMIT 3;
ERROR 2013 (HY000): Lost connection to MySQL server during query
