super_read_only mode breaks replication with some queries

I did tried to reproduce it with no luck so far. Can you please provide my.cnf file from both servers i.e. master and slave.
Further, is the slave stops only on join query with update statement ? Also, provide slave server error log to check further.

I'll try to repeat on actual data set, I had to resync slaves and I didn't have time to save all logs and data. It will take me several days to prepare everything, I though it will be easy to repeat issue, but yes - only update with joins failed, everything else worked fine.

Ok, I'm able to reproduce slave error immediately, trying to carve smaller test case.

Muhammad - I was able to reproduce issue with my provided examples in bug description. I created tbl and tbl_postfix tables on master server, set super_read_only mode on on its' slave and executed:
'UPDATE tbl INNER JOIN tbl_postfix on tbl.id_ = tbl_postfix.tbl_id SET is_visible = 1 WHERE hex(tbl_postfix.postfix_id) LIKE 'yadada%' AND tbl.is_visible = 0'

Which immediately broke replication on slave:
               Last_SQL_Errno: 1290
               Last_SQL_Error: Error 'The MySQL server is running with the --read-only (super) option so it cannot execute this statement' on query. Default database: 'test'. Query: 'UPDATE tbl INNER JOIN tbl_postfix on tbl.id_ = tbl_postfix.tbl_id SET is_visible = 1 WHERE hex(tbl_postfix.postfix_id) LIKE 'yadada%' AND tbl.is_visible = 0'

Mysql version which we use: 5.6.21-70.1-698.precise

Now I am able to reproduce the same with below settings.

super_read_only=1
read_only=1

slave1 [localhost] {msandbox} ((none)) > SHOW SLAVE STATUS\G
*************************** 1. row ***************************
                   Last_Errno: 1290
                   Last_Error: Error 'The MySQL server is running with the --read-only (super) option so it cannot execute this statement' on query. Default database: 'test'. Query: 'UPDATE tbl INNER JOIN tbl_postfix on tbl.id_ = tbl_postfix.tbl_id SET is_visible = 1 WHERE hex(tbl_postfix.postfix_id) LIKE 'yadada%' AND tbl.is_visible = 0'

master [localhost] {msandbox} ((none)) > SHOW GLOBAL VARIABLES LIKE "%version%";
+-------------------------+--------------------------------------------------+
| Variable_name | Value |
+-------------------------+--------------------------------------------------+
| innodb_version | 5.6.21-rel70.1 |
| protocol_version | 10 |
| slave_type_conversions | |
| version | 5.6.21-70.1-log |
| version_comment | Percona Server (GPL), Release 70.1, Revision 698 |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
+-------------------------+--------------------------------------------------+

Muhammad, is it upstream? (i.e. WebScaleSQL)

Any news on this bug?

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.

This is still present in latest version - 5.6.25-73.1-log

The failure reported in bug 1500638 happened again

               Last_SQL_Error: Error 'The MySQL server is running with the --read-only (super) option so it cannot execute this statement' on query. Default database: 'BroadcastApi'. Query: 'UPDATE channels c, accounts a SET c.active = false WHERE c.active = true and a.deleted = true and c.accountGuid = a.accountGuid'

     Last_SQL_Error_Timestamp: 150929 10:10:25

The problem was introduced in commit 78e1f5d "Cherry picking patch for BUG#37051" (https://github.com/percona/percona-server/commit/78e1f5d5232a471e21234efb55a683161b72d40a)
Original BUG#37051 "Replication rules not evaluated correctly"
(https://bugs.mysql.com/bug.php?id=37051).

In "sql_parse.cc" in "mysql_execute_command()", "case SQLCOM_UPDATE_MULTI:" branch
*****************************************
#ifdef HAVE_REPLICATION
    /* Check slave filtering rules */
    if (unlikely(thd->slave_thread && !have_table_map_for_update))
    {
      if (all_tables_not_ok(thd, all_tables))
      {
        if (res!= 0)
        {
          res= 0; /* don't care of prev failure */
          thd->clear_error(); /* filters are of highest prior */
        }
        /* we warn the slave SQL thread */
        my_error(ER_SLAVE_IGNORED_TABLE, MYF(0));
        break;
      }
      if (res)
        break;
    }
    else
    {
#endif /* HAVE_REPLICATION */
      if (res)
        break;
      bool enforce_ro = true;
      if (!opt_super_readonly)
        enforce_ro = !(thd->security_ctx->master_access & SUPER_ACL);
      if (opt_readonly &&
   enforce_ro &&
   some_non_temp_table_to_be_updated(thd, all_tables))
      {
        my_error(ER_OPTION_PREVENTS_STATEMENT, MYF(0),
                 opt_super_readonly ? "--read-only (super)" : "--read-only");
      }
#ifdef HAVE_REPLICATION
    } /* unlikely */
#endif
*****************************************

"!have_table_map_for_update" additional condition was put in the wrong place. It should have been added inside the "slave" ("if (unlikely(thd->slave_thread))") branch.

Although the original bug was fixed in 5.5 (or even 5.1) and therefore a new bug was introduced in the same version, this problem did not reveal itself until "super_read_only" functionality was introduced (in 5.6).

Despite the fact that the execution was going through the wrong path ("else" block of the "if (unlikely(thd->slave_thread))"), the error was never reported.

Here is this "else" block from 5.5
*****************************************
    else
    {
#endif /* HAVE_REPLICATION */
      if (res)
        break;
      if (opt_readonly &&
   !(thd->security_ctx->master_access & SUPER_ACL) &&
   some_non_temp_table_to_be_updated(thd, all_tables))
      {
 my_error(ER_OPTION_PREVENTS_STATEMENT, MYF(0), "--read-only");
 break;
      }
#ifdef HAVE_REPLICATION
    } /* unlikely */
#endif

*****************************************
Because for slave threads "thd->security_ctx->master_access" was always set in "Security_context::skip_grants()" and therefore always included "SUPER_ACL" flag, "my_error()" was never called.

Original MySQL Server 5.5 and 5.6 still go through the wrong branch, but for the same reason (no "super_read_only" mode) it's not a problem there.

In MySQL Server 5.7 "case SQLCOM_UPDATE_MULTI:" code branch was seriously reworked and the problem does not appear this this version.

Percona Server 5.7 does not have this problem either.

https://github.com/percona/percona-server/pull/280
https://github.com/percona/percona-server/pull/283

Removing 5.7 milestone and "Fix Released" status as 5.7 was not affected by the bug and the patch only upmerged the testcase.

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-891