Bug #37626 mysql-server crash

• Submitted: 25 Jun 2008 11:29
• Modified: 15 Sep 2008 21:24
• Reporter: Levente Farkas
• Status: Can't repeat
• Category: MySQL Server: MyISAM storage engine
• Severity: S1 (Critical)
• Version: 5.0.45
• OS: Linux (rhel 5.2)
• CPU Architecture: Any

[25 Jun 2008 11:29] Levente Farkas

Description:
after we upgrade ot centos-5.2 which contains mysql-5.0.45 we permanently (every minutes) got this error in the log file:
----------------------------------------------
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=268435456
read_buffer_size=1044480
max_used_connections=1
max_connections=100
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 466543 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x9c1c4a0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xa0161f98, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8187393
(nil)
0x828e2b3
0x828f920
0x828f7ea
0x81c8ec3
0x81a06b9
0x81a3e90
0x81a4473
0x81a58b7
0x81a6487
0x88246b
0x413dbe
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x9c53b20 = select ID, NAME, TYPE, asText(POLYLINE) from geo_all_road r where MBRIntersects(GeomFromText('Polygon((19.029111 47.472561,19.029561 47.472561,19.029561 47.472111,19.029111 47.47211
1,19.029111 47.472561))'), r.POLYLINE)
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
----------------------------------------------
after using http://dev.mysql.com/doc/mysql/en/using-stack-trace.html i've got
----------------------------------------------
0x8187393 handle_segfault + 755
(nil)
0x828e2b3 st_select_lex_unit::prepare(THD*, select_result*, unsigned long) + 2787
0x828f920 mysql_derived_prepare(THD*, st_lex*, st_table_list*) + 224
0x828f7ea mysql_handle_derived(st_lex*, bool (*)(THD*, st_lex*, st_table_list*)) + 90
0x81c8ec3 open_and_lock_tables(THD*, st_table_list*) + 131
0x81a06b9 mysql_execute_command(THD*) + 15017
0x81a3e90 mysql_parse(THD*, char const*, unsigned int, char const**) + 368
0x81a4473 dispatch_command(enum_server_command, THD*, char*, unsigned int) + 1251
0x81a58b7 do_command(THD*) + 167
0x81a6487 handle_one_connection + 2695
0x15546b (?)
0x2e3dbe (?)
----------------------------------------------
what can be the reason?

How to repeat:
just start mysql. we use Django and httpd.

[25 Jun 2008 12:20] Sveta Smirnova

Thank you for the report.

Version 5.0.45 is a bit old. Please upgrade to current version 5.0.51b, try with it and if you still get same error provide output of SHOW CREATE TABLE geo_all_road  and SHOW TABLE STATUS LIKE 'geo_all_road'

[25 Jun 2008 12:32] Levente Farkas

this's the latest default rhel version and i don't really like to diverge from it.

[25 Jun 2008 12:36] Sveta Smirnova

Thank you for the feedback.

You don't need to install new version system-wide. Either use *tar.gz package or RPM and rpm2cpio to unpack it to temporary directory.

And in any case we need output of SHOW CREATE TABLE geo_all_road and SHOW TABLE
STATUS LIKE 'geo_all_road' to be able to repeat the problem.

[25 Jun 2008 12:59] Levente Farkas

mysql> SHOW CREATE TABLE geo_all_road;
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| View         | Create View                                                                                                                                                                                                                                                                                                                                                          |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| geo_all_road | CREATE ALGORITHM=UNDEFINED DEFINER=`fregatt`@`localhost` SQL SECURITY DEFINER VIEW `geo_all_road` AS select `rc`.`ID` AS `ID`,`rc`.`NAME` AS `NAME`,`rc`.`TYPE` AS `TYPE`,`rc`.`POLYLINE` AS `POLYLINE` from `geo_road_custom_ordered` `rc` union select `r`.`ID` AS `ID`,`r`.`NAME` AS `NAME`,`r`.`TYPE` AS `TYPE`,`r`.`POLYLINE` AS `POLYLINE` from `geo_road` `r` | 
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

mysql> SHOW TABLE
    -> STATUS LIKE 'geo_all_road';
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| Name         | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| geo_all_road | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL      |     NULL | NULL           | VIEW    | 
+--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
1 row in set (0.00 sec)

[25 Jun 2008 13:06] Sveta Smirnova

Thank you for the feedback.

Please also provide output of SHOW CREATE TABLE and SHOW TABLE STATUS of tables geo_road_custom_ordered and geo_road.

[25 Jun 2008 13:23] Levente Farkas

mysql> SHOW CREATE TABLE geo_road_custom_ordered;
+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| View                    | Create View                                                                                                                                                                                                                                                                            |
+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| geo_road_custom_ordered | CREATE ALGORITHM=UNDEFINED DEFINER=`fregatt`@`localhost` SQL SECURITY DEFINER VIEW `geo_road_custom_ordered` AS select `r`.`ID` AS `ID`,`r`.`NAME` AS `NAME`,`r`.`TYPE` AS `TYPE`,`r`.`POLYLINE` AS `POLYLINE` from `geo_road_custom` `r` order by `r`.`TYPE` desc,`r`.`POLYLINE` desc | 
+-------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> SHOW CREATE TABLE geo_road;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| View     | Create View                                                                                                                                                                                                                                                                                                                                  |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| geo_road | CREATE ALGORITHM=UNDEFINED DEFINER=`fregatt`@`localhost` SQL SECURITY DEFINER VIEW `geo_road` AS select `r`.`ID` AS `ID`,`r`.`NAME` AS `NAME`,`r`.`TYPE` AS `TYPE`,`r`.`POLYLINE` AS `POLYLINE` from `geo_road_base` `r` where ((`r`.`TYPE` = _utf8'Highway') or (`r`.`TYPE` = _utf8'Freeway')) order by `r`.`TYPE` desc,`r`.`POLYLINE` desc | 
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'geo_road_custom_ordered';
+-------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| Name                    | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| geo_road_custom_ordered | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL      |     NULL | NULL           | VIEW    | 
+-------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'geo_road';
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| Name     | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
| geo_road | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |            NULL |         NULL |      NULL |           NULL | NULL        | NULL        | NULL       | NULL      |     NULL | NULL           | VIEW    | 
+----------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+
1 row in set (0.00 sec)

[25 Jun 2008 13:35] Sveta Smirnova

Thank you for the feedback.

We really need SHOW CREATE TABLE of all underlying tables and views from the original view and SHOW TABLE STATUS for all underlying base tables. Now this is geo_road_custom and geo_road_base. Please provide its definition too.

[25 Jun 2008 13:57] Levente Farkas

mysql> SHOW CREATE TABLE geo_road_custom;
+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                                                                                                                                                    |
+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| geo_road_custom | CREATE TABLE `geo_road_custom` (
  `ID` int(11) NOT NULL,
  `NAME` varchar(50) default NULL,
  `TYPE` varchar(20) default NULL,
  `POLYLINE` geometry NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `ID` (`ID`),
  SPATIAL KEY `POLYLINE` (`POLYLINE`(32))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 
+-----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE geo_road_base;
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                                                                                                                  |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| geo_road_base | CREATE TABLE `geo_road_base` (
  `ID` int(11) NOT NULL,
  `NAME` varchar(50) default NULL,
  `TYPE` varchar(20) default NULL,
  `POLYLINE` geometry NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `ID` (`ID`),
  SPATIAL KEY `POLYLINE` (`POLYLINE`(32))
) ENGINE=MyISAM DEFAULT CHARSET=utf8 | 
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'geo_road_base';
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name          | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       | Checksum | Create_options | Comment |
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| geo_road_base | MyISAM |      10 | Dynamic    | 2840 |            166 |      472832 | 281474976710655 |       245760 |         0 |           NULL | 2008-06-06 01:02:30 | 2008-06-25 11:17:57 | 2008-06-25 11:17:57 | utf8_general_ci |     NULL | NULL           |         | 
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'geo_road_custom';
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| Name            | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation       | Checksum | Create_options | Comment |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
| geo_road_custom | MyISAM |      10 | Dynamic    |    0 |              0 |           0 | 281474976710655 |         1024 |         0 |           NULL | 2008-06-06 01:02:30 | 2008-06-25 11:17:57 | 2008-06-25 11:17:57 | utf8_general_ci |     NULL | NULL           |         | 
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+
1 row in set (0.00 sec)

[25 Jun 2008 14:16] Levente Farkas

one more thing. this is our development server which is i386, the same database exists on the production server which is x86_64. i'm not sure that the two database exactly the same (but the tables should have to be), but this bug is not happened on the other server.

[25 Jun 2008 14:28] Levente Farkas

and one more thing, when i downgrade to rhel 5.1's mysql-server-5.0.22-2.2.el5_1.1 it's working again:-(

[25 Jun 2008 20:13] Sveta Smirnova

Thank you for the feedback.

Bug is not repeatable since version 5.0.52, although it is repeatable in 5.0.51a. Please upgrade to Enterprise server or wait next Community release.

[25 Jun 2008 20:38] Levente Farkas

did you find the reason?
do you know which changes cause this bug? since as i wrote with mysql-server-5.0.22-2.2.el5_1.1 it's working.
do you have any kind of patch for this bug?
imho redhat won't upgrade to 52, but can add a patch if it's possible. 
it's clear there is a bug, but how can fix this?

[31 Jul 2008 15:14] Levente Farkas

any change for a new release in the near future which fix this bug?

[15 Sep 2008 21:24] Levente Farkas

is this fixed in the latest version?