Details
-
Technical task
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Won't Fix
-
None
-
None
-
None
-
None
Description
It is most likely an upstream problem, but I cannot positively prove it since it requires an engine which only supports STATEMENT binlog format.
Statements like
INSERT INTO t1 SELECT * FROM t1 |
cannot be executed at all when binlog is enabled.
The statement is considered unsafe for statement-based replication, and normally it would be processed the following way:
- with binlog_format=ROW it is executed all right;
- with binlog_format=MIXED it is written in the ROW format;
- with binlog_format=STATEMENT it is executed, and a warning is written into the error log.
However, with LevelDB (and possibly with any engine limited to STATEMENT format), it cannot be executed with ROW or MIXED format because of the limitation, and with STATEMENT format it causes an unexpected error:
mysql> select @@binlog_format; |
+-----------------+ |
| @@binlog_format |
|
+-----------------+ |
| STATEMENT |
|
+-----------------+ |
1 row in set (0.00 sec) |
|
|
mysql> CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=LevelDB; |
Query OK, 0 rows affected (0.13 sec) |
|
|
mysql> INSERT INTO t1 VALUES (1),(2); |
Query OK, 2 rows affected (0.00 sec) |
Records: 2 Duplicates: 0 Warnings: 0
|
|
|
mysql> INSERT INTO t1 SELECT NULL FROM t1; |
ERROR 1663 (HY000): Cannot execute statement: impossible to write to binary log since statement is unsafe, storage engine is limited to statement-based logging, and BINLOG_FORMAT = MIXED. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave. |
That is, on some reason server behaves as if the format was MIXED, while it's actually STATEMENT.
Test case:
--source include/have_binlog_format_statement.inc
|
|
|
SELECT @@binlog_format; |
CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY) ENGINE=LevelDB; |
INSERT INTO t1 VALUES (1),(2); |
INSERT INTO t1 SELECT NULL FROM t1; |
revision-id: psergey@askmonty.org-20130406172740-fid7u9tkz5ahlc4c
|
revno: 4823
|
branch-nick: mysql-5.6-leveldb
|