MySQL Bugs: #18224: VIEW on information

Bug #18224	VIEW on information_schema crashes the server
Submitted:	14 Mar 2006 14:58	Modified:	20 Mar 2006 19:58
Reporter:	Beat Vontobel (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S1 (Critical)
Version:	5.0.19/5.0.20BK/5.1.8BK	OS:	Linux (Linux 2.4.21 (SuSE Professional))
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
After the update from 5.0.18 to 5.0.19 some VIEWs that use information_schema now crash the server. The crash happens even if the VIEW is recreated in 5.0.19. This bug didn't exist in 5.0.18 and was probably the result of another bugfix.

Resolved stack trace:

060314 20:52:12 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.0.19-standard'  socket: '/tmp/mysql.sock'  port: 3306  MySQL Community Edition - Standard (GPL)
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=8388600
read_buffer_size=131072
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 = 225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x8a13b80
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=0x425b5ca8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8154ef0 handle_segfault + 356
0x4003c96c _end + 934253948
0x81edbd5 _Z8filesortP3THDP8st_tableP13st_sort_fieldjP10SQL_SELECTyPy + 821
0x81a27ea _Z17create_sort_indexP3THDP4JOINP8st_orderyy + 342
0x8196729 _ZN4JOIN4execEv + 3881
0x8197359 _Z12mysql_selectP3THDPPP4ItemP13st_table_listjR4ListIS1_ES2_jP8st_orderSB_S2_SB_mP13select_resultP18st_select_lex_unitP13st_sel + 305
0x8231a0b _Z21mysql_derived_fillingP3THDP6st_lexP13st_table_list + 291
0x82315a4 _Z20mysql_handle_derivedP6st_lexPFbP3THDS0_P13st_table_listE + 80
0x818998d _Z20open_and_lock_tablesP3THDP13st_table_list + 305
0x8168d6a _Z21mysql_execute_commandP3THD + 478
0x816fcfe _Z11mysql_parseP3THDPcj + 306
0x8167706 _Z16dispatch_command19enum_server_commandP3THDPcj + 1178
0x8167231 _Z10do_commandP3THD + 129
0x8166739 handle_one_connection + 621
0x40036f60 _end + 934230896
0x401cc327 _end + 935890743
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 0x8a2d0c0 = SELECT * FROM routines2
thd->thread_id=2
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.

Number of processes running now: 0

How to repeat:
On a default install do:

mysql5:/usr/local/mysql/data # mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19-standard

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

mysql> CREATE SQL SECURITY INVOKER VIEW routines2
    -> AS SELECT
    ->                 ROUTINE_SCHEMA AS `schema`,
    ->                 ROUTINE_NAME AS `name`,
    ->                 ROUTINE_TYPE AS `type`,
    ->                 SECURITY_TYPE AS `security`,
    ->                 CREATED AS `created`,
    ->                 ROUTINE_COMMENT AS `comment`,
    ->                 DEFINER AS `definer`
    -> FROM            information_schema.ROUTINES
    -> ORDER BY        ROUTINE_SCHEMA, ROUTINE_NAME;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM routines2;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Suggested fix:
Double check your bugfixes to not introduce new bugs when resolving old ones.

Thank you for the bug report. I was able to repeat:

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.20-debug

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

mysql> CREATE SQL SECURITY INVOKER VIEW routines2
    -> AS SELECT
    ->  ROUTINE_SCHEMA AS `schema`,
    ->  ROUTINE_NAME AS `name`,
    ->  ROUTINE_TYPE AS `type`,
    ->  SECURITY_TYPE AS `security`,
    ->  CREATED AS `created`,
    ->  ROUTINE_COMMENT AS `comment`,
    ->  DEFINER AS `definer`
    ->  FROM            information_schema.ROUTINES
    ->  ORDER BY        ROUTINE_SCHEMA, ROUTINE_NAME;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM routines2;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 

[New Thread 1099623344 (LWP 6321)]
060314 12:33:59 [Note] /home/miguel/dbs/5.0/libexec/mysqld: ready for connections.
Version: '5.0.20-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1131862960 (LWP 6349)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1131862960 (LWP 6349)]
0x082bea7c in filesort (thd=0x8e619f8, table=0x8e88268, sortorder=0x8e92d38, s_length=2, select=0x8e80f60, max_rows=18446744073709551615, 
    examined_rows=0x4376c4c0) at filesort.cc:276
276       if (my_b_inited(outfile))
(gdb) 

miguel@hegel:~/dbs/5.1> 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 1 to server version: 5.1.8-beta-debug

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

mysql> CREATE SQL SECURITY INVOKER VIEW routines2 <cut>

mysql> SELECT * FROM routines2;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>

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/3893

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/3965

Fixed in 5.0.20

Documented in 5.0.20 changelog:

      <listitem>
        <para>
          Views that incorporate tables from the INFORMATION_SCHEMA
          resulted in a server crash when queried. (Bug #18224)
        </para>
      </listitem>