Description:
I tried to turn off "NO_ENGINE_SUBSTITUTION" in NDB to InnoDB replication environment; however,
it doesn't work in replication environment.
If I executed DDL directly on a slave, it seems working fine.
【Directly Execute a DDL on Slave】
root@localhost [TEST_DB]> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| ndbcluster | NO | Clustered, fault-tolerant tables | NULL | NULL | NULL |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| ndbinfo | YES | MySQL Cluster system information storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
11 rows in set (0.00 sec)
root@localhost [TEST_DB]> CREATE TABLE `T_NDB_ON_SLAVE` (
-> `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
-> `comment` varchar(100) DEFAULT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=ndbcluster AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected, 2 warnings (0.15 sec)
root@localhost [TEST_DB]> show warnings;
+---------+------+--------------------------------------------------------+
| Level | Code | Message |
+---------+------+--------------------------------------------------------+
| Warning | 1286 | Unknown storage engine 'ndbcluster' |
| Warning | 1266 | Using storage engine InnoDB for table 'T_NDB_ON_SLAVE' |
+---------+------+--------------------------------------------------------+
2 rows in set (0.00 sec)
root@localhost [TEST_DB]>
root@localhost [TEST_DB]> show create table T_NDB_ON_SLAVE\G
*************************** 1. row ***************************
Table: T_NDB_ON_SLAVE
Create Table: CREATE TABLE `T_NDB_ON_SLAVE` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
root@localhost [TEST_DB]>
【Send binlog from master to slave】
I turned off "NO_ENGINE_SUBSTITUTION"; however, it doesn't work in replication environtment.
root@localhost [TEST_DB]> select @@version;
+--------------------------------------------------+
| @@version |
+--------------------------------------------------+
| 5.6.25-ndb-7.4.7-cluster-commercial-advanced-log |
+--------------------------------------------------+
1 row in set (0.00 sec)
root@localhost [TEST_DB]> show variables like '%storage%';
+----------------------------+--------+
| Variable_name | Value |
+----------------------------+--------+
| default_storage_engine | InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine | InnoDB |
+----------------------------+--------+
3 rows in set (0.00 sec)
root@localhost [TEST_DB]> show variables like 'sql_mode';
+---------------+--------------------+
| Variable_name | Value |
+---------------+--------------------+
| sql_mode | ONLY_FULL_GROUP_BY |
+---------------+--------------------+
1 row in set (0.00 sec)
root@localhost [TEST_DB]>
root@localhost [TEST_DB]> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.114
Master_User: cluster_slave
<SNIP>
Last_SQL_Errno: 1286
Last_SQL_Error: Error 'Unknown storage engine 'ndbcluster'' on query. Default database: 'TEST_DB'. Query: 'CREATE TABLE `T_NDB02` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`comment` varchar(100) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=ndbcluster AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 50
How to repeat:
1) Binary 5.6.25-ndb-7.4.7-cluster-commercial-advanced-log
2) I tried binary log format mixed and row based replication.
3) NDB is used for scale our write and PK based read.
Slave is used for long transactions and large query.
4) Since we use NDB for storage engine on master and InnoDB is used for
slave storage Engine. We would like to trun off "NO_ENGINE_SUBSTITUTION".
Set SQL Mode on Slave: sql_mode = ONLY_FULL_GROUP_BY
5) If we execute DDL on a Slave directly, default storage engine is used.
If we execute DDL on a Master, ndb is not replaced by InnoDB.
Suggested fix:
Turn off "NO_ENGINE_SUBSTITUTION" should be function even through replication.
Best Regard
Shinya
Description: I tried to turn off "NO_ENGINE_SUBSTITUTION" in NDB to InnoDB replication environment; however, it doesn't work in replication environment. If I executed DDL directly on a slave, it seems working fine. 【Directly Execute a DDL on Slave】 root@localhost [TEST_DB]> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | ndbcluster | NO | Clustered, fault-tolerant tables | NULL | NULL | NULL | | CSV | YES | CSV storage engine | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | ndbinfo | YES | MySQL Cluster system information storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 11 rows in set (0.00 sec) root@localhost [TEST_DB]> CREATE TABLE `T_NDB_ON_SLAVE` ( -> `id` int(11) unsigned NOT NULL AUTO_INCREMENT, -> `comment` varchar(100) DEFAULT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=ndbcluster AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4; Query OK, 0 rows affected, 2 warnings (0.15 sec) root@localhost [TEST_DB]> show warnings; +---------+------+--------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------+ | Warning | 1286 | Unknown storage engine 'ndbcluster' | | Warning | 1266 | Using storage engine InnoDB for table 'T_NDB_ON_SLAVE' | +---------+------+--------------------------------------------------------+ 2 rows in set (0.00 sec) root@localhost [TEST_DB]> root@localhost [TEST_DB]> show create table T_NDB_ON_SLAVE\G *************************** 1. row *************************** Table: T_NDB_ON_SLAVE Create Table: CREATE TABLE `T_NDB_ON_SLAVE` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `comment` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) root@localhost [TEST_DB]> 【Send binlog from master to slave】 I turned off "NO_ENGINE_SUBSTITUTION"; however, it doesn't work in replication environtment. root@localhost [TEST_DB]> select @@version; +--------------------------------------------------+ | @@version | +--------------------------------------------------+ | 5.6.25-ndb-7.4.7-cluster-commercial-advanced-log | +--------------------------------------------------+ 1 row in set (0.00 sec) root@localhost [TEST_DB]> show variables like '%storage%'; +----------------------------+--------+ | Variable_name | Value | +----------------------------+--------+ | default_storage_engine | InnoDB | | default_tmp_storage_engine | InnoDB | | storage_engine | InnoDB | +----------------------------+--------+ 3 rows in set (0.00 sec) root@localhost [TEST_DB]> show variables like 'sql_mode'; +---------------+--------------------+ | Variable_name | Value | +---------------+--------------------+ | sql_mode | ONLY_FULL_GROUP_BY | +---------------+--------------------+ 1 row in set (0.00 sec) root@localhost [TEST_DB]> root@localhost [TEST_DB]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.56.114 Master_User: cluster_slave <SNIP> Last_SQL_Errno: 1286 Last_SQL_Error: Error 'Unknown storage engine 'ndbcluster'' on query. Default database: 'TEST_DB'. Query: 'CREATE TABLE `T_NDB02` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `comment` varchar(100) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=ndbcluster AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4' Replicate_Ignore_Server_Ids: Master_Server_Id: 50 How to repeat: 1) Binary 5.6.25-ndb-7.4.7-cluster-commercial-advanced-log 2) I tried binary log format mixed and row based replication. 3) NDB is used for scale our write and PK based read. Slave is used for long transactions and large query. 4) Since we use NDB for storage engine on master and InnoDB is used for slave storage Engine. We would like to trun off "NO_ENGINE_SUBSTITUTION". Set SQL Mode on Slave: sql_mode = ONLY_FULL_GROUP_BY 5) If we execute DDL on a Slave directly, default storage engine is used. If we execute DDL on a Master, ndb is not replaced by InnoDB. Suggested fix: Turn off "NO_ENGINE_SUBSTITUTION" should be function even through replication. Best Regard Shinya