Comment 8 for bug 1441259

Rick Pizzi (pizzi) wrote :

This bug is nasty.

It hits when the following cases are met:

- UPDATE with JOIN
- BINLOG_FORMAT = STATEMENT

Very easy to reproduce:

use rick;

CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
Query OK, 1 row affected (0.00 sec)

CREATE TABLE `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `val` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
Query OK, 1 row affected (0.00 sec)

mysql> insert into t1 values (1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t2 values (1, 2);
Query OK, 1 row affected (0.00 sec)

mysql> SET SESSION binlog_format = 'STATEMENT';
Query OK, 0 rows affected (0.00 sec)

mysql> update rick.t1 left join rick.t2 using(id) set t1.val = 19;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

=> replication breaks on slave with the following error:

Error 'The MySQL server is running with the --read-only (super) option so it cannot execute this statement' on query. Default database: 'rick'. Query: 'update rick.t1 left join rick.t2 using(id) set t1.val = 19'

This makes the option "super_read_only" unusable in production.