[MDEV-20692] 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. Created: 2019-09-27  Updated: 2023-12-11

Status: Confirmed
Project: MariaDB Server
Component/s: Data Definition - Temporary
Affects Version/s: 10.1, 10.3.18, 10.3, 10.4
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0

Type: Bug Priority: Major
Reporter: Breno Brand Fernandes Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 1
Labels: None
Environment:

CentOS Linux release 7.6.1810 (Core)
Linux 3.10.0-957.27.2.el7.x86_64 #1 SMP Mon Jul 29 17:46:05 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

Issue Links:
Duplicate
duplicates MDEV-25592 CREATE TEMPORARY TABLE ... SELECT syn... Closed

 Description   

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.

However, when the data is from MyISAM real tables, it works.

######################################
 
# 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

As you can see, MariaDB returned an error when it was tried to create a temporary table using data from a InnoDB real table.

 Comments   
Comment by Alice Sherepa [ 2019-10-02 ]

Thank you for the report! Reproducible on current 10.1-10.4:

--source include/have_innodb.inc
 
 
 
create table t1 (id int) engine=innodb;
 
insert into t1 values (1),(2),(3);
 
 
 
create user  'u1'@'localhost';
 
grant select, insert, create temporary tables on test.* to 'u1'@'localhost';
 
 
 
set global read_only=ON;
 
 
 
connect (conn1,localhost,u1,,test);
 
connection conn1;
 
 
 
set default_storage_engine=myisam;
 
create temporary table bar select * from t1;
 
 
 
set default_storage_engine=innodb;
 
# fails with 1290: The MariaDB server is running with the --read-only option so it cannot execute this statement
 
create temporary table bar1 select * from t1;

Comment by Marko Mäkelä [ 2023-12-11 ]

The bug is outside the InnoDB storage engine. For CREATE TEMPORARY TABLE bar1, the transaction is being marked read-write here:

10.4 5ca63b2b8b4fafd82314f6d6595128b3b12a2311

 
#0  0x000055f1fe3f84d9 in Ha_trx_info::set_trx_read_write (this=this@entry=0x7fa16400d458) at /mariadb/10.4/sql/handler.h:1825
 
#1  0x000055f1fe3f1b1c in handler::mark_trx_read_write_internal (this=this@entry=0x7fa16401ef80) at /mariadb/10.4/sql/handler.cc:4453
 
#2  0x000055f1fe3f95a6 in handler::mark_trx_read_write (this=this@entry=0x7fa16401ef80) at /mariadb/10.4/sql/handler.h:4545
 
#3  0x000055f1fe3f28d5 in handler::ha_create_partitioning_metadata (this=this@entry=0x7fa16401ef80, name=name@entry=0x7fa14c465070 "/dev/shm/10.4/mysql-test/var/tmp/mysqld.1/#sql52be7_a_1", 
    old_name=old_name@entry=0x0, action_flag=action_flag@entry=0) at /mariadb/10.4/sql/handler.cc:4881
 
#4  0x000055f1fe62257f in create_table_impl (thd=thd@entry=0x7fa16400ae28, orig_db=@0x7fa16401c750: {str = 0x7fa16401ce28 "test", length = 4}, 
    orig_table_name=@0x7fa16401c760: {str = 0x7fa16401c700 "bar1", length = 4}, db=@0x7fa16401c750: {str = 0x7fa16401ce28 "test", length = 4}, 
    table_name=@0x7fa16401c760: {str = 0x7fa16401c700 "bar1", length = 4}, path=path@entry=0x7fa14c465070 "/dev/shm/10.4/mysql-test/var/tmp/mysqld.1/#sql52be7_a_1", 
    options={m_options = DDL_options_st::OPT_NONE}, create_info=0x7fa14c466ad0, alter_info=0x7fa14c466a10, create_table_mode=1, is_trans=0x0, key_info=0x7fa14c465058, key_count=0x7fa14c465054, 
    frm=0x7fa14c465060) at /mariadb/10.4/sql/sql_table.cc:5209

The reason is that ha_info->is_started() holds in handler::mark_trx_read_write_internal(). For the MyISAM table bar, we would have ht->slot=0 pointing to the following:

(rr) p thd.ha_data[0].ha_info[0]
 
$11 = {m_next = 0x0, m_ht = 0x0, m_flags = 0 '\000'}

I am not familiar with the logic of "not started" transactions, so I can’t dig deeper to the root cause.

Generated at Thu Feb 08 09:01:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.