Bug #3643 MySQL could crash when a TABLE is altered and used at the same time
Submitted: 4 May 2004 1:48 Modified: 5 May 2004 0:13
Reporter: jocelyn fournier (Silver Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.2 latest bktree OS:Linux (Linux)
Assigned to: Michael Widenius CPU Architecture:Any

[4 May 2004 1:48] jocelyn fournier
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
[4 May 2004 19:23] jocelyn fournier
Hi,

To reproduce the bug :

Run tests/fork_big.pl

I get the following result :

[root@forum] /usr/local/mysql-4.1/tests> ./fork_big.pl                                                                    <19:19:51
Test of multiple connections that test the following things:
insert, select, delete, update, alter, check, repair and flush
Creating table bench_f31 in database test
Creating table bench_f32 in database test
Creating table bench_f33 in database test
Creating table bench_f34 in database test
Creating table bench_f35 in database test
Started 14 threads
MySQL server has gone away at ./fork_big.pl line 519.
thread 'alter' finished with exit code 32

Regards,
  Jocelyn
[5 May 2004 0:13] Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will be in 4.1.2

Regards,
Monty
[5 May 2004 0:13] Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Fix will be in 4.1.2

Regards,
Monty