Details
Description
For a table with a PRIMARY KEY:
CREATE TABLE `t1` (
`c1` INTEGER,
PRIMARY KEY (`c1`)
);
When running an "INSERT ... SELECT" statement with a LIMIT clause, albeit while also using ORDER BY:
CREATE TABLE `t2` LIKE `t1`;
INSERT INTO `t2` SELECT * FROM `t1` ORDER BY `c1` LIMIT 500;
MariaDB generates the following warning:
Jan 9 16:58:37 localhost mysqld: 140109 16:58:37 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: insert into t2 select * from t1 order by c1 limit 500
The warning also occurs when doing a similar "CREATE TABLE ... SELECT" statement:
CREATE TABLE `t2` SELECT `c1` FROM `t1` ORDER BY `c1` LIMIT 500;
Jan 9 16:57:25 localhost mysqld: 140109 16:57:25 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted. Statement: CREATE TABLE `t2` SELECT `c1` FROM `t1` ORDER BY `c1` LIMIT 500
Unless I'm mistaken, these statements are actually safe because the ORDER BY clause (in this case) provides a deterministic order.
This may be related to MySQL bug #42415, which has sat in verified-but-unresolved state for more than a year:
http://bugs.mysql.com/bug.php?id=42415
Gliffy Diagrams
Attachments
Issue Links
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/2502.567.181