MySQL Bugs: #26793: mysqld crashes when doing specific query on information

Bug #26793	mysqld crashes when doing specific query on information_schema
Submitted:	2 Mar 2007 14:02	Modified:	15 Sep 2007 11:12
Reporter:	johnny slakva	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S2 (Serious)
Version:	5.0.37, 5.0.44	OS:	Linux (RHEL3, Mac OS X)
Assigned to:	Stewart Smith	CPU Architecture:	Any
Tags:	cluster, crash, information_schema, privileges

Description:
if user dont have privileges to access some databases, doing select on information_schema.tables that includes table from those databases crashes the server.

How to repeat:
1. create some database, no tables needed.
2. create some user, without any privileges.
3. under this user, issue

select * from information_schema.tables;

the result is 
ERROR 2006 (HY000): MySQL server has gone away
and crash is recorded in server error log.

Thank you for a problem report. Sorry, but I was bot able to repeat the behaviour described with latest 5.0.38-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.38 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create user u2@localhost identified by 'p2';
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysql -uu2 -pp2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.0.38 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select * from information_schema.tables;
...
20 rows in set (0.02 sec)

Please, check if your test case is any different from the above.

thank you for response, i was slightly wrong...
this simple test case i provided really doesnt reproduce bug, because i tested it in our specific config; when i tried on clean new server with myisam, it worked all ok.

seems like i was able to come bit closer, but unable to make a 100% reproducible test case.

our servers work as sql nodes in mysql cluster; i tested it by connecting fresh clean server to cluster, and the bug reproduced.

so what reproduces bug is

1) have a mysql cluster
2) connect mysqld node to this cluster
3) need to have database _in cluster storage_, with some tables in it. preferably these tables should be created initially on other nodes, not on current.
4) user should have neither privileges to this database, nor grant option

so... these steps seemed to show the bug:

# mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.0.33 MySQL Community Edition - Standard (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set wait_timeout=120;
Query OK, 0 rows affected (0.00 sec)

mysql> create database clustertest;     // this db already has some table
Query OK, 1 row affected (0.00 sec)

mysql> create user u2@localhost identified by 'p2';
Query OK, 0 rows affected (0.00 sec)

mysql> revoke all privileges, grant option from u2@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> Bye

after this - it wasnt reproducing each time, so i had either to repeat query few times, or use single query like this:

# mysql -uu2 -pp2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.33 MySQL Community Edition - Standard (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select count(*) from information_schema.tables union all select count(*) from information_schema.tables union all select count(*) from information_schema.tables;
ERROR 2013 (HY000): Lost connection to MySQL server during query

another thing i noticed - clustertest db was not otherwise in use; but when i repeat same accessing to our real cluster dbs which are under moderate load, it repeats each time (or maybe this is related to specific tables schema or whatever).

one bug though that is clearly seen from this: with myisam tables, select * from information_schema doesnt show me tables in dbs which i dont have access to; same is not correct with ndbcluster tables - all are visible in info_schema regardless of user privileges.

unfortunately i also didnt have chance to try reproducing this under clean cluster that is not in use otherwise.

please let me know if i can provide you with any other kind of information on this issue.

i resolved stack trace from last crash, maybe it will help somehow...

mysqld got signal 11;
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=16777216
read_buffer_size=2093056
max_used_connections=1
max_connections=500
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2062380 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0xa3d7be0
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=0xb4fce15c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8190b20
0x8394433
0x8251035
0x826fbef
0x826f4fc
0x8274df9
0x81d7b67
0x828c4d9
0x828b642
0x81d3ae7
0x81a6d79
0x81addfe
0x81a538b
0x81a4ebd
0x81a43af
0x3bfdd8
0x1edd1a
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 0xa4100c8 = select count(*) from information_schema.tables union all select count(*) from information_schema.tables union all select count(*) from information_schema.tables
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.

resolved stack is

0x8190b20 handle_segfault + 416
0x8394433 _ZN3Ndb22readAutoIncrementValueEPKN13NdbDictionary5TableERy + 35
0x8251035 _ZN13ha_ndbcluster4infoEj + 213
0x826fbef _Z24get_schema_tables_recordP3THDP13st_table_listP8st_tablebPKcS6_ + 591
0x826f4fc _Z14get_all_tablesP3THDP13st_table_listP4Item + 1692
0x8274df9 _Z24get_schema_tables_resultP4JOIN + 345
0x81d7b67 _ZN4JOIN4execEv + 5943
0x828c4d9 _ZN18st_select_lex_unit4execEv + 361
0x828b642 _Z11mysql_unionP3THDP6st_lexP13select_resultP18st_select_lex_unitm + 66
0x81d3ae7 _Z13handle_selectP3THDP6st_lexP13select_resultm + 71
0x81a6d79 _Z21mysql_execute_commandP3THD + 681
0x81addfe _Z11mysql_parseP3THDPcj + 318
0x81a538b _Z16dispatch_command19enum_server_commandP3THDPcj + 1147
0x81a4ebd _Z10do_commandP3THD + 141
0x81a43af handle_one_connection + 655
0x3bfdd8 (?)
0x1edd1a (?)

Please, try to repeat with a newer version, 5.0.37, and inform about the results.

yes, this repeats for me with 5.0.37 (we upgraded whole cluster recently to this version).

should i provide you with crash stack trace for this version?

Thank you for checking. Please, send the resolved stack trace.

error message:
----------------------------------------
mysqld got signal 11;
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=16777216
read_buffer_size=2093056
max_used_connections=2
max_connections=500
threads_connected=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 206238
0 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x9ef0008
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=0xb366003c, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x819fc00
0x18b4ec
0x8453aa1
0x843839d
0x826b925
0x828a98f
0x828a29c
0x828fbc1
0x81e8612
0x82a7dc9
0x82a6f32
0x81e4157
0x81b6769
0x81bdaf2
0x81b4ccf
0x81b4714
0x81b3b32
0xc66dd8
0x1edd1a
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 0x9f28a28 = select count(*) from information_schema.tables union a
ll select count(*)
from information_schema.tables union all select count(*) from
information_schema.tables
thd->thread_id=6995
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.

-----------------------------------

resolved stack trace:

0x819fc00 handle_segfault + 416
0x18b4ec (?)
0x8453aa1 _ZN17NdbDictionaryImpl20fetchGlobalTableImplERK10BaseString + 177
0x843839d _ZN3Ndb22readAutoIncrementValueEPKN13NdbDictionary5TableERy + 189
0x826b925 _ZN13ha_ndbcluster4infoEj + 213
0x828a98f _Z24get_schema_tables_recordP3THDP13st_table_listP8st_tablebPKcS6_ + 591
0x828a29c _Z14get_all_tablesP3THDP13st_table_listP4Item + 1692
0x828fbc1 _Z24get_schema_tables_resultP4JOIN23enum_schema_table_state + 385
0x81e8612 _ZN4JOIN4execEv + 6450
0x82a7dc9 _ZN18st_select_lex_unit4execEv + 361
0x82a6f32 _Z11mysql_unionP3THDP6st_lexP13select_resultP18st_select_lex_unitm + 66
0x81e4157 _Z13handle_selectP3THDP6st_lexP13select_resultm + 71
0x81b6769 _Z21mysql_execute_commandP3THD + 697
0x81bdaf2 _Z11mysql_parseP3THDPcj + 386
0x81b4ccf _Z16dispatch_command19enum_server_commandP3THDPcj + 1391
0x81b4714 _Z10do_commandP3THD + 164
0x81b3b32 handle_one_connection + 770
0xc66dd8 (?)
0x1edd1a (?)

How much RAM do you have? Please, send the results of:

free

Linux command and

select count(*) from information_schema.tables;

statement.

we have 2GBs of physical ram on each server in cluster. datanodes are not running on server where i did these tests, they are on other servers.

# free
             total       used       free     shared    buffers     cached
Mem:       2039284    1905608     133676          0     181428    1424396
-/+ buffers/cache:     299784    1739500
Swap:      2040244          0    2040244

# mysql -uu2 -pp2 -e"select count(*) from information_schema.tables"
+----------+
| count(*) |
+----------+
|       19 |
+----------+

# mysql -uroot -e"select count(*) from information_schema.tables"
+----------+
| count(*) |
+----------+
|       47 |
+----------+

Hi,

can you please provide the schemas you have on the cluster table when you reproduce this.  The stack trace seem to indicate an auto increment relation.

BR,

Tomas

sorry for long delay, we changed our main os to debian, and upgraded mysql to 5.0.41, so i wanted to check this in new version.

so, under 5.0.41 it does replicate, but quite rarely, with my latter test case (most times query just runs ok - although shows table in database where user shouldnt have access to.)

such table looks enough to trigger bug:
 
CREATE TABLE `test` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`t` VARCHAR( 10 ) NOT NULL 
) ENGINE = ndbcluster;

i can send newer stack traces (from 5.0.41) if this will help.

test case

Attachment: ndb_bug26793.test (application/octet-stream, text), 543 bytes.

Thank you for the report.

I can repeat bug on Mac with attached test case. But I can not on Linux.

Please indicate accurate version of package you use (file name) or, if you compiled server yourself, provide configure string.

thank you for response,

problem with this bug is that it doesnt reproduce well each time.

on 5.0.37 installed from rpms, it repeated each time, but there might be slightly different table schema (i dropped it when upgraded, and now just guessed, and latest one doesnt seem to replicate bug well).

so... now we have for ndb nodes 5.0.41 installed from debian packages (debian os), for one sql node where i tested it 5.0.41 made from rpms (redhat).

i had to run this query about 10-15 times before it crashed sql node with this setup (just tried again - again quite several times before it crashed.)

so... please give it bunch of runs on linux, hopefully it will replicate.

if i will find another table schema that will trigger this bug more often, i will let you know.

to be clear... sql node i tested with was 5.0.41-rpm.

test case for Linux

Attachment: ndb_bug26793.test (application/octet-stream, text), 557 bytes.

Thank you for the feedback.

Verified on Linux using additional test case and 5.0.41 binaries.

But bug is not repeatable all time. I had to run test several times to get it crashed. Backtrace is same as on Mac.

On Mac bug is repeatable all times with current BK sources.

no external_lock(!F_UNLCK) before ::info, causing segfault when trying to read autoinc value.

but, the incredibly amazingly fun thing is, insert

SELECT * from test;

just after the create table, and no crash.

due to cache of handler objects.

fun fun fun, in the sun sun sun!

http://lists.mysql.com/commits/30952

http://lists.mysql.com/commits/30951
http://lists.mysql.com/commits/30950

new patch:
http://lists.mysql.com/commits/31592

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31604

ChangeSet@1.2470, 2007-07-26 20:24:54+10:00, stewart@flamingspork.com +2 -0
  [PATCH] BUG#26793 test: mysqld crashes in NDB on I_S query
  
  Reduce case and formalise into something we should be
  able to use in mysql-test-run.
  
  Index: ndb-work/mysql-test/t/ndb_bug26793.test
  ===================================================================

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/31605

ChangeSet@1.2471, 2007-07-26 20:25:05+10:00, stewart@flamingspork.com +1 -0
  [PATCH] Bug#26793 I_S query crashes in NDB
  
  If ::exteral_lock hadn't been called, we'd have no NDB object,
  so need to check/get one here.
  
  It looks like sql_show.cc is the only place that does this....
  or at least the other places will be well hidden.
  
  Index: ndb-work/sql/ha_ndbcluster.cc
  ===================================================================

approved by tomas.

pushed to 5.0-ndb and 5.1-ndb

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32213

ChangeSet@1.2575, 2007-08-07 19:16:06+02:00, msvensson@pilot.(none) +2 -0
  Bug#26793 mysqld crashes when doing specific query on information_schema
   - Drop the newly created user user1@localhost
   - Cleanup testcase

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/32498

ChangeSet@1.2473, 2007-08-14 15:07:17+10:00, stewart@willster.(none) +2 -0
   Backport Magnus' fix from 5.1

Documented bugfix in mysql-5.1.22-6.2.5 changelog as

"An attempt to perform a <literal>SELECT ... FROM INFORMATION_SCHEMA.TABLES</literal> whose result included information about <literal>NDB</literal> tables for which the user had no privileges could crash the MySQL Server on which the query was performed."

Left in PQ status for 5.0/mainline 5.1 pushes.

Pushed into 5.0.50

Pushed into 5.1.23-beta

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 bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Bugfix also documented in 5.0.50 and 5.1.23 changelogs.