MySQL Bugs: #26174: Server Crash: INSERT ... SELECT ... FROM I_S.GLOBAL

Bug #26174	Server Crash: INSERT ... SELECT ... FROM I_S.GLOBAL_STATUS in Event
Submitted:	8 Feb 2007 1:23	Modified:	4 Apr 2007 3:47
Reporter:	Kai Voigt	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S1 (Critical)
Version:	5.1.15/5.1BK,5.1.14	OS:	MacOS (MacOSX 10.4.8/Linux)
Assigned to:	Kristofer Pettersson	CPU Architecture:	Any

Description:
Using an event with a "INSERT ... SELECT ... FROM I_S.GLOBAL_STATUS" statement crashes the server. It seems to be caused by using I_S.global_status. When using I_S.processlist (the commented INSERT ... SELECT statement), the event works.

How to repeat:
DROP TABLE IF EXISTS thread_status;
CREATE TABLE thread_status (dt DATETIME, variable_name VARCHAR(64), variable_value DECIMAL(22,7));

DROP EVENT IF EXISTS log_status;

DELIMITER $$

CREATE EVENT log_status
 ON SCHEDULE EVERY 5 SECOND
 DO
 BEGIN
  -- INSERT INTO thread_status SELECT NOW(), user, time FROM information_schema.processlist;
  INSERT INTO thread_status SELECT NOW(), variable_name, variable_value FROM information_schema.global_status;
 END
$$

DELIMITER ;

SET GLOBAL event_scheduler=1;

Thank you for the bug report. Verified on Ubuntu 6.10:

070207 23:55:21 [Note] /home/miguel/dbs/5.1/libexec/mysqld: ready for connections.
Version: '5.1.16-beta-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
070207 23:55:21 [Note] SCHEDULER: Loaded 0 events
[New Thread -1257477216 (LWP 13350)]
[New Thread -1257677920 (LWP 13354)]
070207 23:56:05 [Note] SCHEDULER: Manager thread started with id 2
[New Thread -1257878624 (LWP 13357)]
070207 23:56:08 [Note] SCHEDULER: [test.log_status of root@localhost] executing in thread 3. Execution 1

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread -1257878624 (LWP 13357)]
0x082266b2 in show_ssl_get_cipher (thd=0x8ed0130, var=0xb5062ce4, buff=0xb5062d08 "") at mysqld.cc:6626
6626                  SSL_get_cipher((SSL*) thd->net.vio->ssl_arg) : "");
(gdb) bt full
#0  0x082266b2 in show_ssl_get_cipher (thd=0x8ed0130, var=0xb5062ce4, buff=0xb5062d08 "") at mysqld.cc:6626
No locals.
#1  0x0837468a in fill_schema_status (thd=0x8ed0130, variables=<value optimized out>, status_var=0xb5063188, prefix=0x8697bd0 "", table=0x8ee2368)
    at sql_show.cc:5171
        tmp = {name = 0x8697bd0 "", value = 0xb5062d08 "", type = SHOW_CHAR}
        var = (SHOW_VAR *) 0x0
        show_type = SHOW_FUNC
        null_lex_str = {str = 0x0, length = 0}
<cut>

Linux backtrace, mysqld 5.1.17, gcc4.1

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread -1257911408 (LWP 15436)]
0x0821a7e2 in show_ssl_get_cipher (thd=0x8e984f0, var=0xb505b298, buff=0xb505ae58 "") at mysqld.cc:6651
6651                  SSL_get_cipher((SSL*) thd->net.vio->ssl_arg) : "");
(gdb) bt
#0  0x0821a7e2 in show_ssl_get_cipher (thd=0x8e984f0, var=0xb505b298, buff=0xb505ae58 "") at mysqld.cc:6651
#1  0x0835d653 in fill_schema_status (thd=0x8e984f0, variables=<value optimized out>, status_var=0xb505b2f8, prefix=0x866fed0 "", table=0x8ea31c0)
    at sql_show.cc:5183
#2  0x0835ef3c in fill_schema_global_status (thd=0x8e984f0, tables=0x8ea2760, cond=0x0) at sql_show.cc:5278
#3  0x0835d434 in get_schema_tables_result (join=0x8ea45c0, executed_place=PROCESSED_BY_JOIN_EXEC) at sql_show.cc:5110
#4  0x08294ed8 in JOIN::exec (this=0x8ea45c0) at sql_select.cc:1508
#5  0x082975a3 in mysql_select (thd=0x8e984f0, rref_pointer_array=0x8ea1ff4, tables=0x8ea2760, wild_num=0, fields=@0x8ea1f48, conds=0x0, og_num=0,
    order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=4026812416, result=0x8ea4540, unit=0x8ea1c70, select_lex=0x8ea1eb8)
    at sql_select.cc:2095
#6  0x08297a92 in handle_select (thd=0x8e984f0, lex=0x8ea1c08, result=0x8ea4540, setup_tables_done_option=1073741824) at sql_select.cc:255
#7  0x0823ae82 in mysql_execute_command (thd=0x8e984f0) at sql_parse.cc:3530
#8  0x083b124d in sp_instr_stmt::exec_core (this=0x8ea2940, thd=0x8e984f0, nextp=0xb505c230) at sp_head.cc:2573
#9  0x083b1527 in sp_lex_keeper::reset_lex_and_exec_core (this=0x8ea296c, thd=0x8e984f0, nextp=0xb505c230, open_tables=false, instr=0x8ea2940)
    at sp_head.cc:2442
#10 0x083b75b0 in sp_instr_stmt::execute (this=0x8ea2940, thd=0x8e984f0, nextp=0xb505c230) at sp_head.cc:2524
#11 0x083b5c2d in sp_head::execute (this=0x8e89dc0, thd=0x8e984f0) at sp_head.cc:1076
#12 0x083b661f in sp_head::execute_procedure (this=0x8e89dc0, thd=0x8e984f0, args=0xb505c34c) at sp_head.cc:1708
#13 0x083c9812 in Event_job_data::execute (this=0x8e894d0, thd=0x8e984f0) at event_data_objects.cc:1689
#14 0x083c490f in event_worker_thread (arg=0x8e894d0) at event_scheduler.cc:278
#15 0xb7eb7112 in start_thread () from /lib/libpthread.so.0
#16 0xb7dd32ee in clone () from /lib/libc.so.6

Latest changeset
http://lists.mysql.com/commits/21921

Hi! I don't agree that the comments are overwhelming. I think they belong with the design of this particular code. But if you have a suggestion on how you think it should be done I will reconsider this approach. Cheers!

Pushed into 5.1.18-beta

Noted in 5.1.18 changelog.

Executing an INSERT ... SELECT ... FROM
INFORMATION_SCHEMA.GLOBAL_STATUS
statement from within an event
caused a server crash.