CREATE table .. SELECT * from <table> fails if destination table is from other SE that is not aware of fake-changes

[Roel Van de Paar]

Thread 1 (Thread 0x7f46c46ff700 (LWP 1967)):
+bt
#0 0x00007f46e2ad2771 in pthread_kill () from /lib64/libpthread.so.0
#1 0x0000000000ab6f6a in my_write_core (sig=6) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.21-69.0/mysys/stacktrace.c:422
#2 0x000000000072f89f in handle_fatal_signal (sig=6) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.21-69.0/sql/signal_handler.cc:236
#3 <signal handler called>
#4 0x00007f46e16d65c9 in raise () from /lib64/libc.so.6
#5 0x00007f46e16d7cd8 in abort () from /lib64/libc.so.6
#6 0x00007f46e16cf536 in __assert_fail_base () from /lib64/libc.so.6
#7 0x00007f46e16cf5e2 in __assert_fail () from /lib64/libc.so.6
#8 0x00000000007c9569 in select_insert::abort_result_set (this=0x7f4684005fb8) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.21-69.0/sql/sql_insert.cc:3942
#9 0x00000000007ca9f7 in select_create::abort_result_set (this=0x7f4684005fb8) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.21-69.0/sql/sql_insert.cc:4448
#10 0x00000000008122d9 in handle_select (thd=0x25bd210, result=0x7f4684005fb8, setup_tables_done_option=0) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.21-69.0/sql/sql_select.cc:116
#11 0x00000000007e3fd7 in mysql_execute_command (thd=0x25bd210) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.21-69.0/sql/sql_parse.cc:3449
#12 0x00000000007ed033 in mysql_parse (thd=0x25bd210, rawbuf=0x7f46840050a0 "create table t2_myisam as select * from t2", length=42, parser_state=0x7f46c46fde30) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.21-69.0/sql/sql_parse.cc:6773
#13 0x00000000007df49c in dispatch_command (command=COM_QUERY, thd=0x25bd210, packet=0x2dd1661 "create table t2_myisam as select * from t2;", packet_length=43) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.21-69.0/sql/sql_parse.cc:1432
#14 0x00000000007de3c8 in do_command (thd=0x25bd210) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.21-69.0/sql/sql_parse.cc:1049
#15 0x00000000007a6385 in do_handle_one_connection (thd_arg=0x25bd210) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.21-69.0/sql/sql_connect.cc:1532
#16 0x00000000007a5e8d in handle_one_connection (arg=0x25bd210) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.21-69.0/sql/sql_connect.cc:1443
#17 0x0000000000dc5590 in pfs_spawn_thread (arg=0x2d88ad0) at /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.21-69.0/storage/perfschema/pfs.cc:1860
#18 0x00007f46e2acddf3 in start_thread () from /lib64/libpthread.so.0
#19 0x00007f46e179701d in clone () from /lib64/libc.so.6

2014-10-06 21:46:19 1913 [Note] /sda/Percona-Server-5.6.21-rel69.0-670.Linux.x86_64-debug/bin/mysqld: ready for connections.
Version: '5.6.21-69.0-debug' socket: '/dev/shm/814189/1049/socket.sock' port: 59027 Percona Server (GPL), Release 69.0, Revision 670, DEBUG BINARY
2014-10-06 21:46:19 7f46917fd700 InnoDB: FTS Optimize Removing table test/t1
2014-10-06 21:46:20 7f46917fd700 InnoDB: FTS Optimize Removing table test/t1
2014-10-06 21:46:20 1913 [Note] InnoDB: Saving InnoDB page number: 1
2014-10-06 21:46:20 1913 [Warning] Did not write failed 'GRANT ALL PRIVILEGES ON test_user_db.* TO plug IDENTIFIED WITH test_plugin_server AS 'plug_dest'' into binary log while granting/revoking privileges in databases.
2014-10-06 21:46:20 1913 [ERROR] InnoDB: The table data/#sql779_4_d doesn't have a corresponding tablespace, it was discarded.
2014-10-06 21:46:20 1913 [ERROR] InnoDB: The table data/#sql779_4_d doesn't have a corresponding tablespace, it was discarded.
2014-10-06 21:46:20 1913 [ERROR] InnoDB: The table data/#sql779_4_d doesn't have a corresponding tablespace, it was discarded.
2014-10-06 21:46:20 1913 [ERROR] InnoDB: The table data/#sql779_4_d doesn't have a corresponding tablespace, it was discarded.
2014-10-06 21:46:20 1913 [ERROR] InnoDB: The table data/#sql779_4_d doesn't have a corresponding tablespace, it was discarded.
2014-10-06 21:46:20 1913 [ERROR] InnoDB: The table data/#sql779_4_d doesn't have a corresponding tablespace, it was discarded.
2014-10-06 21:46:20 1913 [ERROR] InnoDB: The table data/#sql779_4_d doesn't have a corresponding tablespace, it was discarded.
2014-10-06 21:46:20 1913 [ERROR] InnoDB: The table data/#sql779_4_d doesn't have a corresponding tablespace, it was discarded.
2014-10-06 21:46:20 1913 [ERROR] InnoDB: The table data/#sql779_4_d doesn't have a corresponding tablespace, it was discarded.
2014-10-06 21:46:20 1913 [ERROR] InnoDB: The table data/#sql779_4_d doesn't have a corresponding tablespace, it was discarded.
2014-10-06 21:46:20 1913 [ERROR] InnoDB: The table data/#sql779_4_d doesn't have a corresponding tablespace, it was discarded.
2014-10-06 21:46:20 1913 [ERROR] InnoDB: The table data/#sql779_4_d doesn't have a corresponding tablespace, it was discarded.
2014-10-06 21:46:20 1913 [Warning] Could not remove temporary table: '/dev/shm/814189/1049/data/#sql779_4_17', error: 137
2014-10-06 21:46:20 1913 [Note] InnoDB: Saving InnoDB page number: 10
2014-10-06 21:46:20 1913 [Warning] Did not write failed 'GRANT SELECT ON *.* TO mysqltest6@localhost' into binary log while granting/revoking privileges in databases.
2014-10-06 21:46:20 1913 [Note] Event Scheduler: scheduler thread started with id 16
mysqld: /mnt/workspace/percona-server-5.6-binaries-debug-yassl/label_exp/centos6-64/percona-server-5.6.21-69.0/sql/sql_insert.cc:3943: virtual void select_insert::abort_result_set(): Assertion `transactional_table || !changed || thd->transaction.stmt.cannot_safely_rollback()' failed.
10:46:20 UTC - mysqld got signal 6 ;
[...]
Query (7f46840050a0): create table t2_myisam as select * from t2

Note "myisam" is just text here, not an actual table storage engine selection.

[Roel Van de Paar (roel11)]

Issue is single threaded (original run was single threaded). Testcase creation with reducer failed, but all files are available. Upload below.

[Ramesh Sivaraman (rameshvs02)]

***** Testcase

DROP DATABASE test;CREATE DATABASE test;USE test;
create table t1(a int key,b CHAR (1)unique);
INSERT INTO t1 VALUES(0,0);
SET innodb_fake_changes=1;
SET @@global.default_storage_engine=MYISAM;
SET @@session.default_storage_engine=DEFAULT;
create table t1_myisam as select * from t1;

[Krunal Bauskar (krunal-bauskar)]

Interesting use-case:

- destination table is myisam and source table is innodb. myisam is not aware of fake-changes and so it allows table creation (which otherwise blocked in innodb if fake-change mode is set to true) but select from source table is resulting in error during innobase_commit (as fake-changes = on).

- Also, this means normal flow by mysql server never assumes that select can failure. Well that's true in general.

[Krunal Bauskar (krunal-bauskar)]

commit a91d09ada88abb0d20a5d13c78962eb54e46011f
Author: Krunal Bauskar <email address hidden>
Date: Thu Sep 17 11:46:53 2015 +0530

    - bug#1382745 : CREATE table .. SELECT * from <table> fails if destination table
      is from other SE that is not aware of fake-changes

      Creating table in InnoDB with fake-change mode enable is semantically blocked
      and accordingly ha_innobase::create would return an error.

      What if such table exist in other SE that is not aware of fake-changes and
      table is being created and loaded using CREATE TABLE .... SELECT * FROM;

      Due to restrictive/blocked SELECT execution (in autocommit mode) it would
      fail which eventually would result in catastrophic error as SELECT statement
      will never fail at commit stage (especially being readonly workload).

      SELECT is readonly so it could be allowed to execute even if fake-changes are
      enabled. Especially SELECT with auto-commit that use to fail before.
      SELECT inside a transaction is already allowed but commit will fail.
      Infact, a transaction without any other modifying DML statement has nothing
      to commit. Also, SELECT being executed as part of CREATE TABLE .... SELECT
      should be allowed too.

PR#178 - 5.5
PR#181 - 5.6

[Roel Van de Paar (roel11)]

Bug 1647608?

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1551