MySQL Bugs: #15072: select * from information

Bug #15072	select * from information_schema.[table] crashes server
Submitted:	19 Nov 2005 18:31	Modified:	20 Nov 2005 0:56
Reporter:	Markus Popp	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.16-max-nt	OS:	Windows (Windows XP)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
If I perform the query

select * from information_schema.[table] (I tested it with TABLES and VIEWS), 

the server crashes.

How to repeat:
select * from information_schema.tables;
select * from information_schema.views;

A further important information: the user who performs this query does not have any privileges for the mysql database. The query seems to work, if the user has these privileges.

You are reporting for version 5.0.16 I was unable to find it in our
download page. Are you built it from source?.

Testing with 5.0.15 I was unable to repeat:

c:\mysql\bin>mysql -uuser -p
Enter password: **
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 5.0.15-nt

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

mysql> use mysql
ERROR 1044 (42000): Access denied for user 'user'@'localhost' to database 'mysql'
mysql> use vb
Database changed
mysql> select * from information_schema.tables\G
<cut>

*************************** 29. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: vb
     TABLE_NAME: verify
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 0
MAX_DATA_LENGTH: 1970324836974591
   INDEX_LENGTH: 1024
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2005-11-18 15:46:51
    UPDATE_TIME: 2005-11-18 15:46:51
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
29 rows in set (0.02 sec)

mysql> select * from information_schema.views\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: vb
     TABLE_NAME: v1
VIEW_DEFINITION: select `vb`.`orders`.`id` AS `id` from `vb`.`orders`
   CHECK_OPTION: NONE
   IS_UPDATABLE: YES
        DEFINER: root@localhost
  SECURITY_TYPE: DEFINER
1 row in set (0.05 sec)

mysql> create view v2 as select * from sales;
ERROR 1142 (42000): CREATE VIEW command denied to user 'user'@'localhost' for table 'v2'
mysql>

Thanks in advance.

It's already available here:

http://dev.mysql.com/get/Downloads/MySQL-5.0/mysql-5.0.16-win32.zip/from/pick

but not yet announced on the website.

Today I updated the server from 5.0.15 to 5.0.16 - with 5.0.15 it worked, but today it didn't. So it must be specific to 5.0.16. At the moment, I'm installing a test server with 5.0.16 from scratch and try to reproduce the bug.

I could also repeat it on the test server. Here's the error message, when the server crashed - I hope, it gives information where to find the bug:

Original error message in German:

Die Anweisung in "0x0049927a" verweist auf Speicher "0x0000012c". Der Vorgang "written" konnte nicht auf dem Speicher durchgeführt werden.

which is translated ...

The statement in "0x0049927a" refers to memory (address) "0x0000012c". The action "written" could not be performed in memory (or in this memory address).

Bad thing - also this bug might be specific to the data that I've stored in my databases, if I drop all tables (except mysql, of course), the procedures and most of the users, this bug doesn't come up (that's the same as with bug report #14829). I'm afraid, you won't be able to reproduce this bug on a completely fresh server, either.

I suggest the following - I try to reduce the data to that amount that's necessary to reproduce this bug and bug #14829 - then I'll give you the dump files and the my.ini file of my installation. With them, you could create an exact duplicate of my server and should be able to reproduce both bugs. 

I have already checked the structure of the tables in the mysql database - it's perfectly correct. Also creating and re-importing dumps of all the data (using mysqldump and mysql < source_file) work without error. But maybe these bugs relate to one of my stored procedures or functions that I use - or maybe to some very specific data.

There's one thing I forgot - I downgraded the production machine (from which I created a duplicate on my test machine with 5.0.16, where I try to isolate the required data to reproduce the bugs) back to 5.0.15. After downgrading, this bug doesn't come up anymore, so it must be new in 5.0.16.

I downloaded the 5.0.16 package and was unable to repeat. So
I need the exactly steps for to reproduce that crash on my side:

c:\mysql\bin>mysql -uuser -p
Enter password: **
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-nt-max

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

mysql> select * from information_schema.tables;
+---------------+--------------------+---------------------------------------+-------------+--------
--+---------------------+------------+-------------------+----------+----------------+--------------
| TABLE_CATALOG | TABLE_SCHEMA       | TABLE_NAME                            | TABLE_TYPE  | ENGINE
  | UPDATE_TIME         | CHECK_TIME | TABLE_COLLATION   | CHECKSUM | CREATE_OPTIONS | TABLE_COMMENT
+---------------+--------------------+---------------------------------------+-------------+--------
--+---------------------+------------+-------------------+----------+----------------+--------------
| NULL          | information_schema | CHARACTER_SETS                        | SYSTEM VIEW | MEMORY
  | NULL                | NULL       | utf8_general_ci   |     NULL | max_rows=29127 |
| NULL          | information_schema | COLLATIONS                            | SYSTEM VIEW | MEMORY
  | NULL                | NULL       | utf8_general_ci   |     NULL | max_rows=39662 |
| NULL          | information_schema | COLLATION_CHARACTER_SET_APPLICABILITY | SYSTEM VIEW | MEMORY
  | NULL                | NULL       | utf8_general_ci   |     NULL | max_rows=43351 |
| NULL          | information_schema | COLUMNS                               | SYSTEM VIEW | MyISAM
9 | 2005-11-19 20:06:39 | NULL       | utf8_general_ci   |     NULL | max_rows=8324  |
| NULL          | information_schema | COLUMN_PRIVILEGES                     | SYSTEM VIEW | MEMORY
  | NULL                | NULL       | utf8_general_ci   |     NULL | max_rows=6540  |
| NULL          | information_schema | KEY_COLUMN_USAGE                      | SYSTEM VIEW | MEMORY
  | NULL                | NULL       | utf8_general_ci   |     NULL | max_rows=3618  |
| NULL          | information_schema | ROUTINES                              | SYSTEM VIEW | MyISAM
9 | 2005-11-19 20:06:39 | NULL       | utf8_general_ci   |     NULL | max_rows=9172  |
| NULL          | information_schema | SCHEMATA                              | SYSTEM VIEW | MEMORY
  | NULL                | NULL       | utf8_general_ci   |     NULL | max_rows=4588  |
| NULL          | information_schema | SCHEMA_PRIVILEGES                     | SYSTEM VIEW | MEMORY
  | NULL                | NULL       | utf8_general_ci   |     NULL | max_rows=7699  |
| NULL          | information_schema | STATISTICS                            | SYSTEM VIEW | MEMORY
  | NULL                | NULL       | utf8_general_ci   |     NULL | max_rows=6262  |
| NULL          | information_schema | TABLES                                | SYSTEM VIEW | MEMORY
  | NULL                | NULL       | utf8_general_ci   |     NULL | max_rows=4607  |
| NULL          | information_schema | TABLE_CONSTRAINTS                     | SYSTEM VIEW | MEMORY
  | NULL                | NULL       | utf8_general_ci   |     NULL | max_rows=6700  |
| NULL          | information_schema | TABLE_PRIVILEGES                      | SYSTEM VIEW | MEMORY
  | NULL                | NULL       | utf8_general_ci   |     NULL | max_rows=7073  |
| NULL          | information_schema | TRIGGERS                              | SYSTEM VIEW | MyISAM
9 | 2005-11-19 20:06:39 | NULL       | utf8_general_ci   |     NULL | max_rows=7673  |
| NULL          | information_schema | VIEWS                                 | SYSTEM VIEW | MyISAM
9 | 2005-11-19 20:06:39 | NULL       | utf8_general_ci   |     NULL | max_rows=15073 |
| NULL          | information_schema | USER_PRIVILEGES                       | SYSTEM VIEW | MEMORY
  | NULL                | NULL       | utf8_general_ci   |     NULL | max_rows=8447  |
| NULL          | db1                | tb1                                   | BASE TABLE  | MyISAM
8 | 2005-11-19 18:32:08 | NULL       | latin1_swedish_ci |     NULL |                |
| NULL          | db1                | v1                                    | VIEW        | NULL
  | NULL                | NULL       | NULL              |     NULL | NULL           | VIEW
+---------------+--------------------+---------------------------------------+-------------+--------
--+---------------------+------------+-------------------+----------+----------------+--------------
18 rows in set (0.25 sec)

mysql> select * from information_schema.views;
+---------------+--------------+------------+--------------------------------------------------+----
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | VIEW_DEFINITION                                  | CHE
+---------------+--------------+------------+--------------------------------------------------+----
| NULL          | db1          | v1         | select `db1`.`tb1`.`id` AS `id` from `db1`.`tb1` | NON
+---------------+--------------+------------+--------------------------------------------------+----
1 row in set (0.03 sec)

mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.0.16-nt-max |
+---------------+
1 row in set (0.02 sec)

mysql> use mysql
ERROR 1044 (42000): Access denied for user 'user'@'localhost' to database 'mysql'

Thanks in advance.

First the good news: this bug has helped me to find a solution for bug #14829 - see my comment in this bug report.

To reproduce this bug, do the following (the process starts with a fresh server and a root user without password; all users that are imported with the dump files have no password):

C:\>mysql -u root mysql < dump_mysql.sql

C:\>mysqladmin -u root flush-privileges

C:\>mysql -u root < dump.sql

C:\>mysql -u mpopp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 54 to server version: 5.0.16-nt-max

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

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

Error message, when server crashes

Attachment: screenshot.jpg (image/jpeg, text), 20.58 KiB.

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:

Indeed I was able to crash the 5.0.16 debug server and -max-nt. However
applying the same procedure with today source server the crash goes away
with both servers version. Thank you for the bug report and feedback.

c:\mysql\bin>mysql -umpopp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-debug

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

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

Microsoft Windows XP [versão 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

c:\mysql\bin>mysql -uroot mysql < c:\15072\dump_mysql.sql

c:\mysql\bin>mysqladmin -uroot flush-privileges

c:\mysql\bin>mysql -uroot mysql < c:\15072\dump.sql

c:\mysql\bin>mysql -umpopp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 5.0.17-debug

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

mysql> select * from information_schema.tables;
+---------------+--------------------+-----------------------------
--+---------------------+---------------------+-----------------+--
| TABLE_CATALOG | TABLE_SCHEMA       | TABLE_NAME
  | UPDATE_TIME         | CHECK_TIME          | TABLE_COLLATION | C
+---------------+--------------------+-----------------------------
--+---------------------+---------------------+-----------------+--
| NULL          | information_schema | CHARACTER_SETS
  | NULL                | NULL                | utf8_general_ci |

<cut>

--+---------------------+---------------------+-----------------+--
35 rows in set (0.08 sec)

mysql>

c:\mysql\bin>mysqladmin -uroot shutdown

c:\mysql\bin>mysql -umpopp
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.17-nt-max

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

mysql> select * from information_schema.tables;
+---------------+--------------------+-------------------------------
--+---------------------+---------------------+-----------------+----
| TABLE_CATALOG | TABLE_SCHEMA       | TABLE_NAME
  | UPDATE_TIME         | CHECK_TIME          | TABLE_COLLATION | CHE
+---------------+--------------------+-------------------------------
--+---------------------+---------------------+-----------------+----
| NULL          | information_schema | CHARACTER_SETS
  | NULL                | NULL                | utf8_general_ci |

,cut>

--+---------------------+---------------------+-----------------+----
35 rows in set (0.33 sec)

mysql>