V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
lixuda
V2EX  ›  MySQL

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

  •  
  •   lixuda · 2020-08-08 11:04:20 +08:00 · 3071 次点击
    这是一个创建于 1328 天前的主题,其中的信息可能已经有所发展或是发生改变。

    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.')

    第 1 条附言  ·  2020-08-08 12:44:55 +08:00

    我把配置文件改 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

    9 条回复    2020-08-08 15:54:21 +08:00
    lixuda
        1
    lixuda  
    OP
       2020-08-08 11:20:05 +08:00
    我把配置文件改 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
        2
    vindurriel  
       2020-08-08 11:54:50 +08:00 via iPhone
    我翻译下 warning 是 insert 语句触发的 primary key 是个复合字段 其中非起始字段 (也就是 position) 是 auto increment 这在 binlog format 为 statement 的时候不安全
    taogen
        3
    taogen  
       2020-08-08 12:49:06 +08:00 via Android
    1 )首先,警告不是错误,只是提醒你可能出现非预期结果。
    2 )你这个 SQL 语句不是按主键删除一行,而是按字段批量删除,按主键删除需要指定复合主键的全部字段。MySQL 警告你,怕你以为这个 SQL 语句是按主键删除。
    JasonLaw
        4
    JasonLaw  
       2020-08-08 14:27:23 +08:00
    MySQL Bugs: #45670: Secondary auto-incremented primary fields on replicas end up with wrong value - https://bugs.mysql.com/bug.php?id=45670 说明了这个问题,也给出了解决方法,但是并没有说明为什么。

    如果有人知道为什么的话,分享一下,提前谢谢了。
    lixuda
        5
    lixuda  
    OP
       2020-08-08 15:03:07 +08:00
    @taogen 但是我的确需要 authorid 字段来删除,如何避免?
    zhangysh1995
        6
    zhangysh1995  
       2020-08-08 15:20:52 +08:00
    @lixuda 先用 `authorid`选出来合适的行,然后根据主键删除?
    zhangysh1995
        7
    zhangysh1995  
       2020-08-08 15:25:35 +08:00
    doublestart
        8
    doublestart  
       2020-08-08 15:52:29 +08:00
    看了这个问题忍不住搜了下资料, 主要问题是还是 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
        9
    doublestart  
       2020-08-08 15:54:21 +08:00
    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 前面试试
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   971 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 20:23 · PVG 04:23 · LAX 13:23 · JFK 16:23
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.