MySQL Bugs: #48506: crash in CREATE TABLE <existing_view> IF NOT EXISTS LIKE <tmp

Bug #48506	crash in CREATE TABLE <existing_view> IF NOT EXISTS LIKE <tmp_tbl> with RBL
Submitted:	3 Nov 2009 14:10	Modified:	15 Mar 2010 4:55
Reporter:	Sven Sandberg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S1 (Critical)
Version:	5.1, 5.1-bugteam	OS:	Any (Ubuntu Jaunty)
Assigned to:	Libing Song	CPU Architecture:	Any
Tags:	crash, regression

Description:
A small test case fails with the following stack trace:

#0  0xb80d7430 in __kernel_vsyscall ()
#1  0xb80b21c8 in pthread_kill () from /lib/tls/i686/cmov/libpthread.so.0
#2  0x08741d79 in my_write_core (sig=11) at stacktrace.c:310
#3  0x082c6de4 in handle_segfault (sig=11) at mysqld.cc:2570
#4  <signal handler called>
#5  0x0844a1c2 in tmp_use_all_columns (table=0x93e72c8, bitmap=0x0) at table.h:1680
#6  0x0845d4d8 in store_create_info (thd=0x93b7098, table_list=0x9408d80, packet=0xb75a5198, create_info_arg=0xb75a6368, show_database=false) at sql_show.cc:1272
#7  0x0843c0c4 in mysql_create_like_table (thd=0x93b7098, table=0x9408d80, src_table=0x9408f88, create_info=0xb75a6368) at sql_table.cc:5424
#8  0x082db188 in mysql_execute_command (thd=0x93b7098) at sql_parse.cc:2722
#9  0x082e3791 in mysql_parse (thd=0x93b7098, inBuf=0x9408cc0 "CREATE TABLE IF NOT EXISTS t LIKE t3", length=36, found_semicolon=0xb75a7080) at sql_parse.cc:5963
#10 0x082e45b5 in dispatch_command (command=COM_QUERY, thd=0x93b7098, packet=0x93dbd29 "CREATE TABLE IF NOT EXISTS t LIKE t3", packet_length=36) at sql_parse.cc:1224
#11 0x082e5b30 in do_command (thd=0x93b7098) at sql_parse.cc:865
#12 0x082d0b33 in handle_one_connection (arg=0x93b7098) at sql_connect.cc:1127
#13 0xb80ad4ff in start_thread () from /lib/tls/i686/cmov/libpthread.so.0
#14 0xb7fb249e in clone () from /lib/tls/i686/cmov/libc.so.6

How to repeat:
==== TEST FILE mysql-test/suite/rpl/rpl_bug.test ====
CREATE TABLE t1 SELECT 1 a;
CREATE TEMPORARY TABLE t3 SELECT 3 a;
CREATE VIEW t AS SELECT * FROM t1;
CREATE TABLE IF NOT EXISTS t LIKE t3;
--exit
--source include/have_log_bin.inc
==== END OF TEST FILE ====

The test does not crash if I take away '--source include/have_log_bin.inc'. This is funny since the line appears after 'exit'. That suggests the failure has something to do with mysqltest.

On the other hand, the test does not fail if I move it away from the rpl suite and into the main suite. That suggests it either has something to do with mysqltest or with replication.

The test also does not fail if I change the CREATE commands slightly. That suggests it has something to do with CREATE TABLE.

full log from mtr

Attachment: ttt (application/octet-stream, text), 8.21 KiB.

Verified just as described with recent 5.4.41 from bzr on Mac OS X:

valeriy-kravchuks-macbook-pro:mysql-test openxs$ ./mysql-test-run.pl --suite=rpl bug48506
Logging: ./mysql-test-run.pl  --suite=rpl bug48506
091103 16:16:44 [Warning] Setting lower_case_table_names=2 because file system for /var/folders/dX/dXCzvuSlHX4Op1g-o1jIWk+++TI/-Tmp-/IG5c4E8zBK/ is case insensitive
091103 16:16:45 [Note] Plugin 'FEDERATED' is disabled.
091103 16:16:45 [Note] Plugin 'ndbcluster' is disabled.
MySQL Version 5.1.41
Checking supported features...
 - using ndbcluster when necessary, mysqld supports it
 - SSL connections supported
 - binaries are debug compiled
Collecting tests...
 - adding combinations for rpl
vardir: /Users/openxs/dbs/5.1/mysql-test/var
Checking leftover processes...
 - found old pid 20104 in 'mysqld.1.pid', killing it...
   process did not exist!
 - found old pid 20106 in 'mysqld.2.pid', killing it...
   process did not exist!
Removing old var directory...
Creating var directory '/Users/openxs/dbs/5.1/mysql-test/var'...
Installing system database...
Using server port 50867

==============================================================================

TEST                                      RESULT   TIME (ms)
------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
rpl.bug48506 'row'                       [ fail ]
        Test ended at 2009-11-03 16:17:00

CURRENT_TEST: rpl.bug48506
mysqltest: At line 4: query 'CREATE TABLE IF NOT EXISTS t LIKE t3' failed: 2013: Lost connection to MySQL server during query

The result from queries just before the failure was:
CREATE TABLE t1 SELECT 1 a;
CREATE TEMPORARY TABLE t3 SELECT 3 a;
CREATE VIEW t AS SELECT * FROM t1;

Server [mysqld.1 - pid: 1440, winpid: 1440, exit: 256] failed during test run
Server log from this test:
091103 17:16:49 [Warning] Setting lower_case_table_names=2 because file system for /Users/openxs/dbs/5.1/mysql-test/var/mysqld.1/data/ is case insensitive
091103 17:16:49 [Note] Plugin 'FEDERATED' is disabled.
091103 17:16:49 [Note] Plugin 'InnoDB' is disabled.
091103 17:16:49 [Note] Plugin 'ndbcluster' is disabled.
091103 17:16:50 [Note] Event Scheduler: Loaded 0 events
091103 17:16:50 [Note] /Users/openxs/dbs/5.1/libexec/mysqld: ready for connections.
Version: '5.1.41-debug-log'  socket: '/Users/openxs/dbs/5.1/mysql-test/var/tmp/mysqld.1.sock'  port: 13000  Source distribution
091103 17:16:51 - mysqld got signal 10 ;
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=1048576
read_buffer_size=131072
max_used_connections=1
max_threads=151
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 59964 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x1029e18
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 = 0xb0062f64 thread_stack 0x30000
0   mysqld                              0x0058919a my_print_stacktrace + 45
1   mysqld                              0x000fcfc6 handle_segfault + 882
2   libSystem.B.dylib                   0x940472bb _sigtramp + 43
3   ???                                 0xffffffff 0x0 + 4294967295
4   mysqld                              0x00280805 _Z23mysql_create_like_tableP3THDP10TABLE_LISTS2_P24st_ha_create_information + 2077
5   mysqld                              0x001177d3 _Z21mysql_execute_commandP3THD + 8393
6   mysqld                              0x001203c6 _Z11mysql_parseP3THDPKcjPS2_ + 592
7   mysqld                              0x0012117f _Z16dispatch_command19enum_server_commandP3THDPcj + 3053
8   mysqld                              0x0012258a _Z10do_commandP3THD + 666
9   mysqld                              0x0010cd8f handle_one_connection + 385
10  libSystem.B.dylib                   0x9400c095 _pthread_start + 321
11  libSystem.B.dylib                   0x9400bf52 thread_start + 34
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x1069228 = CREATE TABLE IF NOT EXISTS t LIKE t3
thd->thread_id=2
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.
Writing a core file

 - saving '/Users/openxs/dbs/5.1/mysql-test/var/log/rpl.bug48506-row/' to '/Users/openxs/dbs/5.1/mysql-test/var/log/rpl.bug48506-row/'

Only  1  of 3 completed.
mysql-test-run: *** ERROR: Not all tests completed

A slightly smaller test case:

SET BINLOG_FORMAT=ROW;
CREATE TEMPORARY TABLE t1 SELECT 1 a;
CREATE VIEW t AS SELECT 1;
CREATE TABLE IF NOT EXISTS t LIKE t1;
SELECT * FROM t;

It seems crucial that:

 (1) The test does a CREATE TABLE <t1> IF NOT EXISTS LIKE <t2>, where <t1> is an existing view and <t2> is a temporary table. When I try any other combination, it does not crash.

 (2) BINLOG_FORMAT=ROW. When I change to some other binlog_format, it does not crash. Note that it does not matter if log_bin is set.

This test fails even if I put it in the main suite.

So it seems that this has something to do with binlogging of CREATE TABLE IF NOT EXISTS LIKE when BINLOG_FORMAT=ROW. Changing category to replication.

Not repeatable with mysql-5.1.37-osx10.5-x86_64 (non-debug binaries), so it is a regression bug.

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

3191 Li-Bing.Song@sun.com	2009-11-27
      BUG# 48506 crash in CREATE TABLE IF NOT EXISTS <existing_view> LIKE <tmp_tbl> with RBL
      
      When binlogging the statement, server always handle the 
      existing object as a table, even though it is a view. 
      However view is most different with table in code level.
      Thus, it is crashed.
      
      Now, we just binlog nothing if the existing object is a view in RBR.
      
      However we are still discussing following problems:
        whether it should be binlogged or not ?
        if we decide to binlogged it, what should be binlogged ? the
        existing view or like table ?
      Bug#47442 is a same bug on 'CREATE TABLE IF NOT EXISTS
      <existing_view> SELECT' statement. all problems will have be decided
      when the patch for bug#47442 is commited. if we decides to binlog
      something, 'CREATE TABLE ... LIKE' will have the same behavior.

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

3205 Alfranio Correia	2009-11-27
      BUG#48506 crash in CREATE TABLE IF NOT EXISTS <existing_view> LIKE
      <tmp_tbl> with RBL
      
      When binlogging the statement, the server always handle the existing
      object as a table, even though it is a view. However a view is
      handled differently in other parts of the code thus leading the
      statement to crash in RBL if the view exists.
      
      This happens because the underlying tables for the view are not opened
      when we try to call store_create_info() on the view in order to build
      a CREATE TABLE statement.
      
      This patch will only address the crash problem, other binlogging
      problems related to CREATE TABLE IF NOT EXISTS LIKE when the existing
      object is a view will be solved by BUG 47442.

Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:alfranio.correia@sun.com-20091127150116-gt221t8u39pacyft) (merge vers: 5.1.42) (pib:13)

Noted in 5.1.42 changelog.

With row-based binary logging, the server crashed for statements of
the form CREATE TABLE IF NOT EXISTS existing_view LIKE
temporary_table. This occurred because the server handled the 
existing view as a table when logging the statement.

Setting report to NDI pending push to 5.5.x+.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091201145844-39gy4wmejbisbxac) (merge vers: 5.5.0-beta) (pib:14)

Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)

Also documented in the 5.5.1, 5.6.0, and 6.0.14 changelogs. Closed.

Setting back to Verified; please review and remove Private flag if/when possible.

5.6.0 changelog entry unneeded.

Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)

No additional changelog entries needed. Setting back to Closed state.