|
There is old and very questionable logic that when a table is converted from one engine to another, existing table flags are preserved, even if they are unsupported by the new engine. Like so:
MariaDB [test]> create table t (a int) engine=Aria transactional=1;
|
Query OK, 0 rows affected (0.034 sec)
|
|
MariaDB [test]> alter table t engine=MyISAM;
|
Query OK, 0 rows affected, 1 warning (0.067 sec)
|
Records: 0 Duplicates: 0 Warnings: 1
|
|
MariaDB [test]> show warnings;
|
+---------+------+------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+---------+------+------------------------------------------------------------------------------------+
|
| Warning | 1478 | Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1' |
|
+---------+------+------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
|
MariaDB [test]> show create table t;
|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t | CREATE TABLE `t` (
|
`a` int(11) DEFAULT NULL
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci PAGE_CHECKSUM=1 TRANSACTIONAL=1 |
|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
When the statement returned by SHOW CREATE is executed, it throws an error (in the strict mode which is default).
Complaints have been raised more than once, also by users (e.g. MySQL#67727, MDEV-8733) and support (e.g. MDEV-21387), but they are always rejected on the grounds that the behavior is intentional. I think it's wrong, but for now we have to assume it stays this way, and side-effects have to be dealt with on case by case basis.
Alternatively, somebody can try to raise the root cause issue again.
One of such side-effects affects replication, for example in this simple scenario
--source include/have_binlog_format_row.inc
|
--source include/master-slave.inc
|
|
create temporary table t1 (a int) engine=Aria transactional=1;
|
alter table t1 engine=MyISAM;
|
create table t like t1;
|
|
--sync_slave_with_master
|
|
# Cleanup
|
--connection master
|
drop table t;
|
--source include/rpl_end.inc
|
replication fails with
|
10.4 30f3db3c
|
2023-07-29 0:47:12 13 [ERROR] Slave SQL: Error 'Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1'' on query. Default database: 'test'. Query: 'CREATE TABLE `t` (
|
`a` int(11) DEFAULT NULL
|
) ENGINE=MyISAM TRANSACTIONAL=1', Gtid 0-1-1, Internal MariaDB error code: 1478
|
The reason is that CREATE .. LIKE works on the master side, the strict mode allows it; but since it involves a temporary table, and RBR is in use, for the binary log CREATE .. LIKE is converted into a plain CREATE TABLE statement, with all flags preserved, and fails.
|