When a slave server is running in read_only mode, and when connected as a user that does not have the SUPER privilege, it is not possible to create temporary tables using data from innodb real tables.

######################################

# master

######################################

MariaDB [mysql]> CREATE DATABASE bug_test;

Query OK, 1 row affected (0.01 sec)

MariaDB [mysql]> GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW ON bug_test.* TO 'user_test'@'localhost';

Query OK, 0 rows affected (0.02 sec)

MariaDB [bug_test]> CREATE TABLE foo (id INT) ENGINE=InnoDB;

Query OK, 0 rows affected (0.02 sec)

MariaDB [bug_test]> INSERT INTO foo VALUES (1),(2),(3);

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [bug_test]> SELECT * FROM foo;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

+------+

3 rows in set (0.00 sec)

MariaDB [bug_test]> CREATE TABLE foo2 (id INT) ENGINE=MyISAM;

Query OK, 0 rows affected (0.02 sec)

MariaDB [bug_test]> INSERT INTO foo2 VALUES (1),(2),(3);

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [bug_test]> SELECT * FROM foo2;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

+------+

3 rows in set (0.00 sec)

######################################

# slave

######################################

MariaDB [(none)]> show grants;

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for user_test@localhost                                                                                                                                                     |

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| GRANT PROCESS ON *.* TO 'user_test'@'localhost' IDENTIFIED BY PASSWORD '*HASHFORPASSHWORD'                                                                 |

| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, CREATE VIEW, SHOW VIEW ON `bug_test`.* TO 'user_test'@'localhost'          |

+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

MariaDB [bug_test]> show tables;

+--------------------+

| Tables_in_bug_test |

+--------------------+

| foo                |

| foo2               |

+--------------------+

2 rows in set (0.001 sec)

MariaDB [bug_test]> CREATE TEMPORARY TABLE bar SELECT * FROM foo;

ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement

MariaDB [bug_test]> desc bar;

+-------+---------+------+-----+---------+-------+

| Field | Type    | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| id    | int(11) | YES  |     | NULL    |       |

+-------+---------+------+-----+---------+-------+

MariaDB [bug_test]> INSERT INTO bar SELECT * FROM foo;

Query OK, 3 rows affected (0.001 sec)

Records: 3  Duplicates: 0  Warnings

MariaDB [bug_test]> SELECT * FROM bar;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

+------+

3 rows in set (0.001 sec)

MariaDB [bug_test]> CREATE TEMPORARY TABLE bar2 SELECT * FROM foo2;

Query OK, 3 rows affected (0.002 sec)

Records: 3  Duplicates: 0  Warnings: 0

MariaDB [bug_test]> SELECT * FROM bar2;

+------+

| id   |

+------+

|    1 |

|    2 |

|    3 |

+------+

3 rows in set (0.001 se