mysql 删除一条数据后显示这个，大佬们这个怎么解决？

[lixuda]

Warning: (1592, 'Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. INSERT into autoincrement field which is not the first part in the composed primary key is unsafe.')

我把配置文件改 binlog_format = MIXED 错误就消失了。

数据表：

DROP TABLE IF EXISTS pre_forum_post;
CREATE TABLE pre_forum_post (
pid int(10) unsigned NOT NULL,
fid mediumint(8) unsigned NOT NULL DEFAULT '0',
tid mediumint(8) unsigned NOT NULL DEFAULT '0',
`first` tinyint(1) NOT NULL DEFAULT '0',
author varchar(15) NOT NULL DEFAULT '',
authorid mediumint(8) unsigned NOT NULL DEFAULT '0',
`subject` varchar(80) NOT NULL DEFAULT '',
dateline int(10) unsigned NOT NULL DEFAULT '0',
message mediumtext NOT NULL,
useip varchar(15) NOT NULL DEFAULT '',
`port` smallint(6) unsigned NOT NULL DEFAULT '0',
invisible tinyint(1) NOT NULL DEFAULT '0',
anonymous tinyint(1) NOT NULL DEFAULT '0',
usesig tinyint(1) NOT NULL DEFAULT '0',
htmlon tinyint(1) NOT NULL DEFAULT '0',
bbcodeoff tinyint(1) NOT NULL DEFAULT '0',
smileyoff tinyint(1) NOT NULL DEFAULT '0',
parseurloff tinyint(1) NOT NULL DEFAULT '0',
attachment tinyint(1) NOT NULL DEFAULT '0',
rate smallint(6) NOT NULL DEFAULT '0',
ratetimes tinyint(3) unsigned NOT NULL DEFAULT '0',
`status` int(10) NOT NULL DEFAULT '0',
tags varchar(255) NOT NULL DEFAULT '0',
`comment` tinyint(1) NOT NULL DEFAULT '0',
replycredit int(10) NOT NULL DEFAULT '0',
position int(8) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (tid,position),
UNIQUE KEY pid (pid),
KEY fid (fid),
KEY authorid (authorid,invisible),
KEY dateline (dateline),
KEY invisible (invisible),
KEY displayorder (tid,invisible,dateline),
KEY `first` (tid,`first`)
) TYPE=MyISAM;

执行的SQL 语句：

DELETE FROM pre_forum_post WHERE `authorid`=1111118

是不是要修改 SQL

[lixuda]

我把配置文件改 binlog_format = MIXED
错误就消失了。

数据表：

```
DROP TABLE IF EXISTS pre_forum_post;
CREATE TABLE pre_forum_post (
pid int(10) unsigned NOT NULL,
fid mediumint(8) unsigned NOT NULL DEFAULT '0',
tid mediumint(8) unsigned NOT NULL DEFAULT '0',
`first` tinyint(1) NOT NULL DEFAULT '0',
author varchar(15) NOT NULL DEFAULT '',
authorid mediumint(8) unsigned NOT NULL DEFAULT '0',
`subject` varchar(80) NOT NULL DEFAULT '',
dateline int(10) unsigned NOT NULL DEFAULT '0',
message mediumtext NOT NULL,
useip varchar(15) NOT NULL DEFAULT '',
`port` smallint(6) unsigned NOT NULL DEFAULT '0',
invisible tinyint(1) NOT NULL DEFAULT '0',
anonymous tinyint(1) NOT NULL DEFAULT '0',
usesig tinyint(1) NOT NULL DEFAULT '0',
htmlon tinyint(1) NOT NULL DEFAULT '0',
bbcodeoff tinyint(1) NOT NULL DEFAULT '0',
smileyoff tinyint(1) NOT NULL DEFAULT '0',
parseurloff tinyint(1) NOT NULL DEFAULT '0',
attachment tinyint(1) NOT NULL DEFAULT '0',
rate smallint(6) NOT NULL DEFAULT '0',
ratetimes tinyint(3) unsigned NOT NULL DEFAULT '0',
`status` int(10) NOT NULL DEFAULT '0',
tags varchar(255) NOT NULL DEFAULT '0',
`comment` tinyint(1) NOT NULL DEFAULT '0',
replycredit int(10) NOT NULL DEFAULT '0',
position int(8) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (tid,position),
UNIQUE KEY pid (pid),
KEY fid (fid),
KEY authorid (authorid,invisible),
KEY dateline (dateline),
KEY invisible (invisible),
KEY displayorder (tid,invisible,dateline),
KEY `first` (tid,`first`)
) TYPE=MyISAM;

```

SQL 语句：

DELETE FROM pre_forum_post WHERE `authorid`=1111118

是不是要修改 SQL

[vindurriel]

我翻译下 warning 是 insert 语句触发的 primary key 是个复合字段 其中非起始字段 (也就是 position) 是 auto increment 这在 binlog format 为 statement 的时候不安全

[taogen]

1 ）首先，警告不是错误，只是提醒你可能出现非预期结果。
2 ）你这个 SQL 语句不是按主键删除一行，而是按字段批量删除，按主键删除需要指定复合主键的全部字段。MySQL 警告你，怕你以为这个 SQL 语句是按主键删除。

[JasonLaw]

MySQL Bugs: #45670: Secondary auto-incremented primary fields on replicas end up with wrong value - https://bugs.mysql.com/bug.php?id=45670 说明了这个问题，也给出了解决方法，但是并没有说明为什么。

如果有人知道为什么的话，分享一下，提前谢谢了。

[lixuda]

@taogen 但是我的确需要 authorid 字段来删除，如何避免？

[zhangysh1995]

@lixuda 先用 `authorid`选出来合适的行，然后根据主键删除？

[zhangysh1995]

参考一下这个写法？ https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=3ad7b75ae42aad281f9adec23dcbee4f
@lixuda

[doublestart]

看了这个问题忍不住搜了下资料, 主要问题是还是 BINLOG_FORMAT= STATEMENT 格式可能导致 slave 数据问题, 提示也很明显 position AUTO_INCREMENT 不是 the first part composed primary key, 猜想可能导致同样的数据在 master 和 slave 的 postion 值不一样. 从官方文档查询到 https://dev.mysql.com/doc/refman/5.6/en/replication-features-auto-increment.html , 里面解释了这个问题

[doublestart]

An INSERT into a table that has a composite primary key that includes an AUTO_INCREMENT column that is not the first column of this composite key is not safe for statement-based logging or replication. Beginning with MySQL 5.6.6, such statements are marked as unsafe. (Bug #11754117, Bug #45670)

This issue does not affect tables using the InnoDB storage engine, since an InnoDB table with an AUTO_INCREMENT column requires at least one key where the auto-increment column is the only or leftmost column.

解决办法
binlog_format=mixed 或者 改为 innodb, 或者更改 position 到 tid 前面试试