CREATE TABLE .. SELECT and TOI

[Raghavendra D Prabhu]

CTAS is not replicated currently under TOI. This bug is a placeholder for that.

[Raghavendra D Prabhu (raghavendra-prabhu)]

Created a separate branch bzr+ssh://bazaar.launchpad.net/~percona-core/percona-xtradb-cluster/bug-1391948/ for this.

[Nilnandan Joshi (nilnandan-joshi)]

Unable to reproduce this with PXC 5.6.19. Please let me know if I miss anything.

On node1:

mysql> show global variables like 'wsrep_OSU_method';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| wsrep_OSU_method | TOI |
+------------------+-------+
1 row in set (0.00 sec)
mysql> use nil
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from test1;
+------+-----------+
| id | name |
+------+-----------+
| 1 | nilnandan |
+------+-----------+
1 row in set (0.00 sec)

mysql> create table test2 as select * from test1;
Query OK, 1 row affected (0.14 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> show tables;
+---------------+
| Tables_in_nil |
+---------------+
| test1 |
| test2 |
+---------------+
2 rows in set (0.00 sec)

mysql> quit

in binlog (node1) :

BEGIN
/*!*/;
# at 196
#141126 13:24:34 server id 1 end_log_pos 354 CRC32 0x9ba52700 Query thread_id=4 exec_time=0 error_code=0
use `nil`/*!*/;
SET TIMESTAMP=1416988474/*!*/;
CREATE TABLE `test2` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
)
/*!*/;

On node2:

mysql> show tables;
+---------------+
| Tables_in_nil |
+---------------+
| test1 |
+---------------+
1 row in set (0.00 sec)

mysql> show tables;
+---------------+
| Tables_in_nil |
+---------------+
| test1 |
| test2 |
+---------------+
2 rows in set (0.00 sec)

mysql> select * from test2;
+------+-----------+
| id | name |
+------+-----------+
| 1 | nilnandan |
+------+-----------+
1 row in set (0.00 sec)

[Krunal Bauskar (krunal-bauskar)]

CTAS by design is not suppose to work under TOI due to involvement of SELECT.
SELECT evaluation define whether the table is created on not.

If there is need for any enhancement then it can be part of WishList (probably upstream).

[Shahriyar Rzayev (rzayev-sehriyar)]

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