[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` (
`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



 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
https://github.com/mysql/mysql-server/commit/984d0841c80919a206c561cb35e71e409cf85c7d (and post fix 022a8e1270b1b8230f5863ed770499b53ee541f4 (test results)

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.

Generated at Thu Feb 08 07:04:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.