[MDEV-5514] MariaDB issues unsafe binary log warning due to "LIMIT" even when determinstic "ORDER BY" specified Created: 2014-01-10 Updated: 2023-04-27 |
|
| Status: | Open |
| Project: | MariaDB Server |
| Component/s: | None |
| Affects Version/s: | 5.5.34 |
| Fix Version/s: | 10.4 |
| Type: | Bug | Priority: | Major |
| Reporter: | Tim Gokcen | Assignee: | Andrei Elkin |
| Resolution: | Unresolved | Votes: | 0 |
| Labels: | upstream-fixed | ||
| Environment: |
Binary logging enabled |
||
| Description |
|
For a table with a PRIMARY KEY: CREATE TABLE `t1` ( When running an "INSERT ... SELECT" statement with a LIMIT clause, albeit while also using ORDER BY: CREATE TABLE `t2` LIKE `t1`; MariaDB generates the following warning: 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: |
| Comments |
| Comment by Daniel Bartholomew [ 2014-01-29 ] |
|
http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/2502.567.181 |
| Comment by Daniel Black [ 2017-12-30 ] |
|
Corrected in MySQL with a less defining "unsafe" message MariaDB still affected as our error is "Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. %s" leaving no ambiguity about the unsafeness. Removing the error message would be this patch https://lists.mysql.com/commits/113293 which didn't make it to a release it seems. |