Bug #45998 database crashes when running "create as select" (innodb table)
Submitted: 7 Jul 2009 11:45 Modified: 19 Dec 2009 1:11
Reporter: zohar aharoni
Status: Closed
Category:Server: General Severity:S1 (Critical)
Version:5.1.33, 5.1.37 OS:Linux (CentOS release 5.2)
Assigned to: Ramil Kalimullin Target Version:5.1+
Tags: 5.1.33, crash, create as select
Triage: Triaged: D1 (Critical)

[7 Jul 2009 11:45] zohar aharoni 
Description:
Hi,
The database is crashing after running "create as select" statement.
I tried it on two servers with this version.
on the first - The database was upgraded from 5.1.24 rc version (I ran mysql_upgrade)
On the second the database was upgraded from 5.1.28 (also ran mysql_upgrade)

Here is the relevant part from the log file:
==============================================================================
090707 12:16:23 - 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=67108864
read_buffer_size=4194304
max_used_connections=26
max_threads=512
threads_connected=14
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 4265044 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x1d1d87e0
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...
stack_bottom = 0x45786f20 thread_stack 0x40000
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x84417e]
/usr/sbin/mysqld(handle_segfault+0x322)[0x5bc292]
/lib64/libpthread.so.0[0x39e200de80]
/lib64/libc.so.6(strcmp+0x2)[0x39e1877da2]
/usr/sbin/mysqld(_Z17store_create_infoP3THDP10TABLE_LISTP6StringP24st_ha_create_informationb+0x1a5)[0x6bd915]
/usr/sbin/mysqld(_ZN13select_create24binlog_show_create_tableEPP8st_tablej+0xa3)[0x635003]
/usr/sbin/mysqld[0x6351fc]
/usr/sbin/mysqld(_ZN13select_create7prepareER4ListI4ItemEP18st_select_lex_unit+0x6a1)[0x639911]
/usr/sbin/mysqld(_ZN4JOIN7prepareEPPP4ItemP10TABLE_LISTjS1_jP8st_orderS7_S1_S7_P13st_select_lexP18st_select_lex_unit+0x95e)[0x62497e]
/usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x6e4)[0x
633d64]
/usr/sbin/mysqld(_Z13handle_selectP3THDP6st_lexP13select_resultm+0x169)[0x634109]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x53d4)[0x5ce344]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x1f1)[0x5ce6a1]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xfd0)[0x5cf690]
/usr/sbin/mysqld(_Z10do_commandP3THD+0xe4)[0x5cfc24]
/usr/sbin/mysqld(handle_one_connection+0x6ef)[0x5c30cf]
/lib64/libpthread.so.0[0x39e2006307]
/lib64/libc.so.6(clone+0x6d)[0x39e18d1ded]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x1d25b0c0 = create table test.publishers  select * from trc.publishers
thd->thread_id=13
thd->killed=NOT_KILLED
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
090707 12:16:24 mysqld_safe Number of processes running now: 0
090707 12:16:24 mysqld_safe mysqld restarted
090707 12:16:24 [Warning] The syntax 'for replication startup options' is deprecated and
will be removed in MySQL 6.0. Please use 'CHANGE MASTER' instead.
InnoDB: Log scan progressed past the checkpoint lsn 493 1731167147
090707 12:16:25  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 493 1731174053
090707 12:16:25  InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 4
7 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76
77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
InnoDB: Last MySQL binlog file position 0 985674264, file name ./mysql-bin.000954
090707 12:16:28  InnoDB: Started; log sequence number 493 1731174053
090707 12:16:28 [Note] Recovering after a crash using mysql-bin
090707 12:16:28 [Note] Starting crash recovery...
090707 12:16:28 [Note] Crash recovery finished.
090707 12:16:28 [Note] Event Scheduler: Loaded 1 event
090707 12:16:28 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.33-community-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL
Community Server (GPL)
090707 12:16:28 [Note] Event Scheduler: scheduler thread started with id 1

How to repeat:
This is was caused it:

[root]# mysql -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 159
Server version: 5.1.33-community-log MySQL Community Server (GPL)

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

mysql> create table test.publishers as select * from trc.publishers;
ERROR 2013 (HY000): Lost connection to MySQL server during query
[7 Jul 2009 12:14] Shane Bester 
testcase:

start server with --log-bin --binlog-format=row

login without selecting a database: 

mysql -uroot

drop table if exists test.t1,test.t2;
create table test.t1(id int)engine=innodb;
insert into test.t1 values (1),(2);
create table test.t2 select * from test.t1;
[7 Jul 2009 12:16] Shane Bester 
stack trace from valgrind for 5.1.37.

Attachment: bug45998_5.1.37_stack_trace.txt (text/plain), 1.63 KiB.
[7 Jul 2009 12:32] Valeriy Kravchuk 
Verified just as described by Shane with recent 5.1.37 from bzr on Linux. No crash without
row-based logging.
[8 Jul 2009 11:09] zohar aharoni 
We are working in mixed mode.
when transaction isolation level is repeatable read it is working fine, no crash (I guess
it's because the statement it is logged as statement and not raw) when it is read
committed than we get the crash...
[14 Jul 2009 9:44] Bugs System 
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/78612

3022 Ramil Kalimullin	2009-07-14
      Fix for bug#45998: database crashes when running
      "create as select" (innodb table)
      
      Problem: code constructing "CREATE TABLE..." statement
      doesn't take into account that current database is not set
      in some cases. That may lead to a server crash.
      
      Fix: check if current database is set.
     @ mysql-test/extra/binlog_tests/binlog.test
        Fix for bug#45998: database crashes when running
        "create as select" (innodb table)
          - test case.
     @ mysql-test/suite/binlog/r/binlog_row_binlog.result
        Fix for bug#45998: database crashes when running
        "create as select" (innodb table)
          - test result.
     @ sql/sql_show.cc
        Fix for bug#45998: database crashes when running
        "create as select" (innodb table)
          - added check if there's current database set.
[14 Jul 2009 17:17] Bugs System 
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/78653

3022 Ramil Kalimullin	2009-07-14
      Fix for bug#45998: database crashes when running
      "create as select" (innodb table)
      
      Problem: code constructing "CREATE TABLE..." statement
      doesn't take into account that current database is not set
      in some cases. That may lead to a server crash.
      
      Fix: check if current database is set.
     @ mysql-test/extra/binlog_tests/binlog.test
        Fix for bug#45998: database crashes when running
        "create as select" (innodb table)
          - test case.
     @ mysql-test/suite/binlog/r/binlog_row_binlog.result
        Fix for bug#45998: database crashes when running
        "create as select" (innodb table)
          - test result.
     @ sql/sql_show.cc
        Fix for bug#45998: database crashes when running
        "create as select" (innodb table)
          - added check if there's current database set.
[4 Aug 2009 21:49] Bugs System 
Pushed into 5.4.4-alpha (revid:alik@sun.com-20090804194615-h40sa098mx4z49qg) (version
source revid:ramil@mysql.com-20090715034451-rrv902ejf0keyzyk) (merge vers: 5.4.4-alpha)
(pib:11)
[4 Aug 2009 22:45] Bugs System 
Pushed into 5.1.38 (revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (version
source revid:davi.arnaut@sun.com-20090804204317-ggodqkik7de6nfpz) (merge vers: 5.1.38)
(pib:11)
[1 Sep 2009 20:10] Paul DuBois 
Noted in 5.1.38, 5.4.4 changelogs.

CREATE TABLE ... SELECT could cause a server crash if no default
database was selected.
[1 Oct 2009 7:58] Bugs System 
Pushed into 5.1.39-ndb-6.3.28 (revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv)
(version source revid:jonas@mysql.com-20091001055605-ap2kiaarr7p40mmv) (merge vers:
5.1.39-ndb-6.3.28) (pib:11)
[1 Oct 2009 9:25] Bugs System 
Pushed into 5.1.39-ndb-7.0.9 (revid:jonas@mysql.com-20091001072547-kv17uu06hfjhgjay)
(version source revid:jonas@mysql.com-20091001071652-irejtnumzbpsbgk2) (merge vers:
5.1.39-ndb-7.0.9) (pib:11)
[1 Oct 2009 15:25] Bugs System 
Pushed into 5.1.39-ndb-7.1.0 (revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0)
(version source revid:jonas@mysql.com-20091001123013-g9ob2tsyctpw6zs0) (merge vers:
5.1.39-ndb-7.1.0) (pib:11)
[5 Oct 2009 12:50] Bugs System 
Pushed into 5.1.39-ndb-6.2.19 (revid:jonas@mysql.com-20091005103850-dwij2dojwpvf5hi6)
(version source revid:jonas@mysql.com-20090930185117-bhud4ek1y0hsj1nv) (merge vers:
5.1.39-ndb-6.2.19) (pib:11)
[7 Oct 2009 18:24] Paul DuBois 
The 5.4 fix has been pushed to 5.4.2.
[13 Oct 2009 20:30] Bugs System 
Pushed into 5.1.41 (revid:build@mysql.com-20091013182142-vgsjky3t1v4btufv) (version source
revid:build@mysql.com-20091013182142-vgsjky3t1v4btufv) (merge vers: 5.1.41) (pib:13)
[14 Oct 2009 1:30] Paul DuBois 
Already fixed in 5.1.38.
[22 Oct 2009 8:34] Bugs System 
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091022063126-l0qzirh9xyhp0bpc) (version
source revid:alik@sun.com-20091019135554-s1pvptt6i750lfhv) (merge vers: 6.0.14-alpha)
(pib:13)
[22 Oct 2009 9:07] Bugs System 
Pushed into 5.5.0-beta (revid:alik@sun.com-20091022060553-znkmxm0g0gm6ckvw) (version
source revid:alik@sun.com-20091019131937-nchb8tjk88jpfjav) (merge vers: 5.5.0-beta)
(pib:13)
[22 Oct 2009 21:53] Paul DuBois 
Noted in 5.5.0, 6.0.14 changelogs.
[18 Dec 2009 11:29] Bugs System 
Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6)
(version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers:
5.1.41-ndb-7.1.0) (pib:15)
[18 Dec 2009 11:44] Bugs System 
Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt)
(version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers:
5.1.41-ndb-6.2.19) (pib:15)
[18 Dec 2009 12:00] Bugs System 
Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0)
(version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers:
5.1.41-ndb-6.3.31) (pib:15)
[18 Dec 2009 12:14] Bugs System 
Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606)
(version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers:
5.1.41-ndb-7.0.11) (pib:15)