| Bug #17161 | phpMyAdmin "list all databases" query often makes mysqld crash | ||
|---|---|---|---|
| Submitted: | 6 Feb 2006 15:29 | Modified: | 22 Feb 2006 14:30 |
| Reporter: | Björn Wiberg | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.18-debug | OS: | IBM AIX 5.2.0.0 ML5 |
| Assigned to: | CPU Architecture: | Any | |
[6 Feb 2006 15:33]
Björn Wiberg
The IBM AIX error log notices these coredumps and yields the following information:
---8<---
SUMMARY
=======
IDENTIFIER TIMESTAMP T C RESOURCE_NAME DESCRIPTION
B6048838 0206155506 P S SYSPROC SOFTWARE PROGRAM ABNORMALLY TERMINATED
B6048838 0206154706 P S SYSPROC SOFTWARE PROGRAM ABNORMALLY TERMINATED
B6048838 0206153106 P S SYSPROC SOFTWARE PROGRAM ABNORMALLY TERMINATED
DETAILS
=======
---------------------------------------------------------------------------
LABEL: CORE_DUMP
IDENTIFIER: B6048838
Date/Time: mån feb 6 15.55.51 2006
Sequence Number: 2518
Machine Id: 0058180C4C00
Node Id: spinus
Class: S
Type: PERM
Resource Name: SYSPROC
Description
SOFTWARE PROGRAM ABNORMALLY TERMINATED
Probable Causes
SOFTWARE PROGRAM
User Causes
USER GENERATED SIGNAL
Recommended Actions
CORRECT THEN RETRY
Failure Causes
SOFTWARE PROGRAM
Recommended Actions
RERUN THE APPLICATION PROGRAM
IF PROBLEM PERSISTS THEN DO THE FOLLOWING
CONTACT APPROPRIATE SERVICE REPRESENTATIVE
Detail Data
SIGNAL NUMBER
6
USER'S PROCESS ID:
1151004
FILE SYSTEM SERIAL NUMBER
31
INODE NUMBER
2
PROCESSOR ID
-1
CORE FILE NAME
/data/core
PROGRAM NAME
mysqld
ADDITIONAL INFORMATION
pthread_k AC
??
Symptom Data
REPORTABLE
1
INTERNAL ERROR
0
SYMPTOM CODE
PCSS/SPI2 FLDS/mysqld SIG/6 FLDS/pthread_k VALU/ac
---------------------------------------------------------------------------
/../
--->8---
[14 Feb 2006 14:55]
Valeriy Kravchuk
Is this crash repeatable for -debug version only? How many rows you usually get from that SELECT when it is not crashing server?
On 5.0.19-BK on LInux and without debug I was unable to repeat the crash, with a lot of attempts. I'll try to repeat it on AIX anyway, but want to clarify the situation.
mysql> SELECT `information_schema`.`SCHEMATA`.*, COUNT(`information_schema`.`TABLES`.`TABLE_SCHEMA`) AS `SCHEMA_TABLES`, SUM(`information_schema`.`TABLES`.`TABLE_ROWS`)
AS `SCHEMA_TABLE_ROWS`, SUM(`information_schema`.`TABLES`.`DATA_LENGTH`) AS `SCHEMA_DATA_LENGTH`, SUM(`information_schema`.`TABLES`.`MAX_DATA_LENGTH`)
AS `SCHEMA_MAX_DATA_LENGTH`, SUM(`information_schema`.`TABLES`.`INDEX_LENGTH`) AS `SCHEMA_INDEX_LENGTH`, SUM(`information_schema`.`TABLES`.`DATA_LENGTH
` + `information_schema`.`TABLES`.`INDEX_LENGTH`)
AS `SCHEMA_LENGTH`, SUM(`information_schema`.`TABLES`.`DATA_FREE`) AS `SCHEMA_DATA_FREE`
FROM `information_schema`.`SCHEMATA` LEFT JOIN `information_schema`.`TABLES` ON `information_schema`.`TABLES`.`TABLE_SCHEMA`
= `information_schema`.`SCHEMATA`.`SCHEMA_NAME` GROUP BY `information_schema`.`SCHEMATA`.`SCHEMA_NAME`\G
*************************** 1. row ***************************
CATALOG_NAME: NULL
SCHEMA_NAME: information_schema
DEFAULT_CHARACTER_SET_NAME: utf8
DEFAULT_COLLATION_NAME: utf8_general_ci
SQL_PATH: NULL
SCHEMA_TABLES: 16
SCHEMA_TABLE_ROWS: NULL
SCHEMA_DATA_LENGTH: 0
SCHEMA_MAX_DATA_LENGTH: 1125900107809399
SCHEMA_INDEX_LENGTH: 4096
SCHEMA_LENGTH: 4096
SCHEMA_DATA_FREE: 0
*************************** 2. row ***************************
CATALOG_NAME: NULL
SCHEMA_NAME: kris
DEFAULT_CHARACTER_SET_NAME: utf8
DEFAULT_COLLATION_NAME: utf8_general_ci
SQL_PATH: NULL
SCHEMA_TABLES: 1
SCHEMA_TABLE_ROWS: 1
SCHEMA_DATA_LENGTH: 20
SCHEMA_MAX_DATA_LENGTH: 281474976710655
SCHEMA_INDEX_LENGTH: 1024
SCHEMA_LENGTH: 1044
SCHEMA_DATA_FREE: 0
*************************** 3. row ***************************
CATALOG_NAME: NULL
SCHEMA_NAME: mysql
DEFAULT_CHARACTER_SET_NAME: latin1
DEFAULT_COLLATION_NAME: latin1_swedish_ci
SQL_PATH: NULL
SCHEMA_TABLES: 17
SCHEMA_TABLE_ROWS: 13
SCHEMA_DATA_LENGTH: 2882
SCHEMA_MAX_DATA_LENGTH: 1401745384019066863
SCHEMA_INDEX_LENGTH: 23552
SCHEMA_LENGTH: 26434
SCHEMA_DATA_FREE: 438
*************************** 4. row ***************************
CATALOG_NAME: NULL
SCHEMA_NAME: slis_publications
DEFAULT_CHARACTER_SET_NAME: latin1
DEFAULT_COLLATION_NAME: latin1_swedish_ci
SQL_PATH: NULL
SCHEMA_TABLES: 8
SCHEMA_TABLE_ROWS: 7
SCHEMA_DATA_LENGTH: 131072
SCHEMA_MAX_DATA_LENGTH: 0
SCHEMA_INDEX_LENGTH: 0
SCHEMA_LENGTH: 131072
SCHEMA_DATA_FREE: 0
*************************** 5. row ***************************
CATALOG_NAME: NULL
SCHEMA_NAME: test
DEFAULT_CHARACTER_SET_NAME: latin1
DEFAULT_COLLATION_NAME: latin1_swedish_ci
SQL_PATH: NULL
SCHEMA_TABLES: 25
SCHEMA_TABLE_ROWS: 208
SCHEMA_DATA_LENGTH: 233173
SCHEMA_MAX_DATA_LENGTH: 27303077035900917
SCHEMA_INDEX_LENGTH: 215040
SCHEMA_LENGTH: 448213
SCHEMA_DATA_FREE: 0
*************************** 6. row ***************************
CATALOG_NAME: NULL
SCHEMA_NAME: test_utf8
DEFAULT_CHARACTER_SET_NAME: utf8
DEFAULT_COLLATION_NAME: utf8_general_ci
SQL_PATH: NULL
SCHEMA_TABLES: 1
SCHEMA_TABLE_ROWS: 1
SCHEMA_DATA_LENGTH: 80
SCHEMA_MAX_DATA_LENGTH: 281474976710655
SCHEMA_INDEX_LENGTH: 1024
SCHEMA_LENGTH: 1104
SCHEMA_DATA_FREE: 60
6 rows in set (0.02 sec)
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.19 |
+-----------+
1 row in set (0.00 sec)
mysql> explain SELECT `information_schema`.`SCHEMATA`.*, CO
UNT(`information_schema`.`TABLES`.`TABLE_SCHEMA`) AS `S
CHEMA_TABLES`, SUM(`information_schema`.`TABLES`.`TABLE_ROW
S`) AS `SCHEMA_TABLE_ROWS`, SUM(`in
formation_schema`.`TABLES`.`DATA_LENGTH`) AS `SCHEMA_DA
TA_LENGTH`, SUM(`information_schema`.`TABLES`.`MAX_DATA_LEN
GTH`) AS `SCHEMA_MAX_DATA_LENGTH`,
SUM(`information_schema`.`TABLES`.`INDEX_LENGTH`) AS `S
CHEMA_INDEX_LENGTH`, SUM(`information_schema`.`TABLES`.`DAT
A_LENGTH` + `information_schema`.`TABLES`.`INDEX_LENGTH`)
AS `SCHEMA_LENGTH`, SUM(`informati
on_schema`.`TABLES`.`DATA_FREE`) AS `SCHEMA_DATA_FREE`
FROM `information_schema`.`SCHEMATA` LEFT JOIN `inform
ation_schema`.`TABLES` ON `information_schema`.`TABLES`.`TABLE
_SCHEMA` = `information_schema`.`SCHEMATA`.`SCHEMA_NAME`
GROUP BY `information_schema`.`SCHEMATA`.`SCHEMA_NAME`\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: SCHEMATA
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: TABLES
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
Extra:
2 rows in set (0.01 sec)
[14 Feb 2006 16:11]
Björn Wiberg
Hello Valeriy! Thank you for your reply! I haven't tried this with the non-debug version. I will however upgrade the operating system and libraries (including pthreads) to the latest available version (AIX 5.2 TL8) tomorrow, to see if I can reproduce the crash, with the debug version, using the "show databases" option in phpMyAdmin. I'll get back to you with the results as soon as I can. I'd of course appreciate it very much if you could try the query on an AIX machine of yours as well! The number of rows returned by my query is 144. However, it seems that your query lists some more fields (5.0.19 specific?). My 5.0.18 gave me: ERROR 1054 (42S22): Unknown column 'information_schema.TABLES.DATA_LENGTH ' in 'field list' ...when I tried your query. Best regards, Björn
[15 Feb 2006 10:01]
Valeriy Kravchuk
Sorry, it was your query, not mine. And that column exists in 5.0.15, for example. Look: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.15-nt Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> desc information_schema.tables; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | TABLE_TYPE | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | VERSION | bigint(21) | YES | | NULL | | | ROW_FORMAT | varchar(10) | YES | | NULL | | | TABLE_ROWS | bigint(21) | YES | | NULL | | | AVG_ROW_LENGTH | bigint(21) | YES | | NULL | | | DATA_LENGTH | bigint(21) | YES | | NULL | | | MAX_DATA_LENGTH | bigint(21) | YES | | NULL | | | INDEX_LENGTH | bigint(21) | YES | | NULL | | | DATA_FREE | bigint(21) | YES | | NULL | | | AUTO_INCREMENT | bigint(21) | YES | | NULL | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | TABLE_COLLATION | varchar(64) | YES | | NULL | | | CHECKSUM | bigint(21) | YES | | NULL | | | CREATE_OPTIONS | varchar(255) | YES | | NULL | | | TABLE_COMMENT | varchar(80) | NO | | | | +-----------------+--------------+------+-----+---------+-------+ 21 rows in set (0.66 sec) So, are you sure your INFORMATION_SCHEMA is correct? Please, read the manual: http://dev.mysql.com/doc/refman/5.0/en/tables-table.html. That column should be there...
[15 Feb 2006 10:12]
Björn Wiberg
Hi Valeriy! Thank you for your reply! It didn't like the line break before "`" in: ---8<--- SUM(`information_schema`.`TABLES`.`DATA_LENGTH ` --->8--- That's why I thought it was different. Sorry about that. ---8<--- root@localhost (none)> desc information_schema.tables; +-----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+--------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | TABLE_TYPE | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | VERSION | bigint(21) | YES | | NULL | | | ROW_FORMAT | varchar(10) | YES | | NULL | | | TABLE_ROWS | bigint(21) | YES | | NULL | | | AVG_ROW_LENGTH | bigint(21) | YES | | NULL | | | DATA_LENGTH | bigint(21) | YES | | NULL | | | MAX_DATA_LENGTH | bigint(21) | YES | | NULL | | | INDEX_LENGTH | bigint(21) | YES | | NULL | | | DATA_FREE | bigint(21) | YES | | NULL | | | AUTO_INCREMENT | bigint(21) | YES | | NULL | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | TABLE_COLLATION | varchar(64) | YES | | NULL | | | CHECKSUM | bigint(21) | YES | | NULL | | | CREATE_OPTIONS | varchar(255) | YES | | NULL | | | TABLE_COMMENT | varchar(80) | NO | | | | +-----------------+--------------+------+-----+---------+-------+ 21 rows in set (0.02 sec) --->8--- This matches your output, so the information_schema database seems OK. I have upgraded the machine to AIX 5.2 TL8 now; hoping to do some testing soon. Best regards, Björn
[21 Feb 2006 12:15]
Valeriy Kravchuk
Björn, You had written some times ago: > I will however upgrade the operating system and libraries (including pthreads) > to the latest available version (AIX 5.2 TL8) tomorrow, to see if I can > reproduce the crash, with the debug version, using the "show databases" > option in phpMyAdmin. I'll get back to you with the results as soon as I can. So, have you reproduced the crash after the upgrade?
[21 Feb 2006 12:23]
Björn Wiberg
Hello Valeriy! Thank you for your e-mail! Sorry, I haven't had time to try bogging it down with "list all databases" queries, although the ones I have performed have been working fine since the upgrade on February 15th. No crashes, no hangs, nothing. My guess is that the old pthreads library (installp package "bos.rte.libpthreads" version 5.2.0.51) was broken in one way or another. The newest one (in AIX 5.2 TL8) is of version 5.2.0.85. Along with the upgrade came a newer version of the libc library as well, also from 5.2.0.51 to 5.2.0.85. I'll let you know my findings on the "list database queries" issue soon! Best regards, Björn
[22 Feb 2006 9:14]
Valeriy Kravchuk
Thank you for the additional information. Please, inform about any results of your further tests.
[22 Feb 2006 14:05]
Björn Wiberg
Hello Valeriy! I have let a script issue the "bad" query over and over all day (3500 queries), without any crashes (on AIX 5.2 TL8). I conclude that the pthread errors were caused by the older version of libpthread in AIX 5.2 ML5. Best regards, Björn
[22 Feb 2006 14:30]
Valeriy Kravchuk
Thank you for the additional tests. So, I am closing this report now as the problem is not within MySQL code.
Description: Issuing a "Databases" listing from phpMyAdmin 2.8.0-beta1 often causes mysqld to crash on our system. The failing query and log excerpts as well as GDB excerpts can be found in the attached file. Best regards, Björn How to repeat: I have tried the query manually, and found the minimal query which (often!) repeats the problem to be: ---8<--- SELECT `information_schema`.`SCHEMATA`.*, COUNT(`information_schema`.`TABLES`.`TABLE_SCHEMA`) AS `SCHEMA_TABLES`, SUM(`information_schema`.`TABLES`.`TABLE_ROWS`) AS `SCHEMA_TABLE_ROWS`, SUM(`information_schema`.`TABLES`.`DATA_LENGTH`) AS `SCHEMA_DATA_LENGTH`, SUM(`information_schema`.`TABLES`.`MAX_DATA_LENGTH`) AS `SCHEMA_MAX_DATA_LENGTH`, SUM(`information_schema`.`TABLES`.`INDEX_LENGTH`) AS `SCHEMA_INDEX_LENGTH`, SUM(`information_schema`.`TABLES`.`DATA_LENGTH` + `information_schema`.`TABLES`.`INDEX_LENGTH`) AS `SCHEMA_LENGTH`, SUM(`information_schema`.`TABLES`.`DATA_FREE`) AS `SCHEMA_DATA_FREE` FROM `information_schema`.`SCHEMATA` LEFT JOIN `information_schema`.`TABLES` ON `information_schema`.`TABLES`.`TABLE_SCHEMA` = `information_schema`.`SCHEMATA`.`SCHEMA_NAME` GROUP BY `information_schema`.`SCHEMATA`.`SCHEMA_NAME`; --->8--- A number (5-10) repeats of this in an interactive session usually causes our server to crash, and mysqld to restart itself.