[MDEV-25592] CREATE TEMPORARY TABLE ... SELECT syntax fails on read-only replica Created: 2021-05-04  Updated: 2023-03-19  Resolved: 2023-03-19

Status: Closed
Project: MariaDB Server
Component/s: Replication
Affects Version/s: 10.5.8
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Ross Motley Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MDEV-20692 When a slave server is running in rea... Confirmed

 Description   

We have recently undergone a migration of our app from MySQL 5.6 to MariaDB 10.5.8, and encountered the following change in behaviour between the two.

On MariaDB 10.5.8, the following fails:

Create a table on primary:

CREATE TABLE test (
 
  id int(10),
 
  stock_level int(10),
 
  PRIMARY KEY (id)
 
);
 
 
 
INSERT INTO test (id, stock_level) VALUES (1, 2);

On read-only replica, the following fails:

CREATE TEMPORARY TABLE temp_test_fails (
 
  id int(10),
 
  stock_level int(10),
 
  PRIMARY KEY (id)
 
)
 
SELECT
 
  id,
 
  stock_level
 
FROM
 
  test
 
;

The error message is "The MariaDB server is running with the --read-only option so it cannot execute this statement". It does still seem to create the temporary table, but doesn't insert any data.

However, those statements will run if they are split up:

CREATE TEMPORARY TABLE temp_test_ok (
 
  id int(10),
 
  stock_level int(10),
 
  PRIMARY KEY (id)
 
);
 
 
 
INSERT INTO temp_test_ok SELECT id, stock_level FROM test;



 Comments   
Comment by Alice Sherepa [ 2021-05-04 ]

I could not repeat it, please add your .cnf file(s) and describe how to reproduce the problem again, how replication was setted up, etc
I tried on 10.5.8->10.5.8, replica with --read-only

MariaDB [test]> create table t (i int);
 
ERROR 1290 (HY000): The MariaDB server is running with the --read-only option so it cannot execute this statement
 
MariaDB [test]> CREATE TEMPORARY TABLE temp_test_fails (
 
    ->   id int(10),
 
    ->   stock_level int(10),
 
    ->   PRIMARY KEY (id)
 
    -> )
 
    -> SELECT
 
    ->   id,
 
    ->   stock_level
 
    -> FROM
 
    ->   test
 
    -> ;
 
Query OK, 1 row affected (0.002 sec)
 
Records: 1  Duplicates: 0  Warnings: 0
 
 
 
MariaDB [test]> select * from temp_test_fails;
 
+----+-------------+
 
| id | stock_level |
 
+----+-------------+
 
|  1 |           2 |
 
+----+-------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select version();
 
+--------------------+
 
| version()          |
 
+--------------------+
 
| 10.5.8-MariaDB-log |
 
+--------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> show slave status\G
 
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER, REPLICATION SLAVE ADMIN privilege(s) for this operation

Comment by Ross Motley [ 2021-05-04 ]

Hi Alice,

I have tried again with specifying the Engine. It fails with InnoDb, and works with Aria and MyISAM.

Failing: 

CREATE TEMPORARY TABLE temp_test_fails (
 
  id int(10),
 
  stock_level int(10),
 
  PRIMARY KEY (id)
 
) ENGINE=InnoDb
 
SELECT
 
  id,
 
  stock_level
 
FROM
 
  test
 
;

The servers have been created at AWS RDS, I can try and dig out what parameters we have altered from the default ones if that's useful - if the above isn't reproducible for you.

Thanks,

Ross

Comment by Alice Sherepa [ 2021-05-05 ]

Hi Ross,
I've already tried with InnoDB, but it worked for me. Could you please add the output of 

SELECT CONCAT(variable_name, '=', global_value) from information_schema.system_variables where global_value != default_value; 
SELECT CONCAT(variable_name, '=', session_value) from information_schema.system_variables where session_value != default_value;

from slave+master. And SHOW GRANTS.

Comment by Ross Motley [ 2021-05-05 ]

Hi,

It doesn't seem possible to access the `information_schema.system_variables` table on RDS. Here are the RDS user params we have set, and the RDS system params:

User params:

"join_buffer_size" = "1048576"
 
"log_bin_trust_function_creators" = "1"
 
"net_read_timeout" = "180"
 
"net_write_timeout" = "180"
 
"query_cache_size" = "134217728"
 
"query_cache_type" = "1" 
"sql_mode" = "NO_ENGINE_SUBSTITUTION"

System params:

"basedir" = "/rdsdbbin/mysql"
 
"binlog_cache_size" = "32768"
 
"binlog_format" = "MIXED"
 
"datadir" = "/rdsdbdata/db/"
 
"default_password_lifetime" = "0"
 
"default_storage_engine" = "InnoDB"
 
"general_log_file" = "/rdsdbdata/log/general/mysql-general.log"
 
"innodb_buffer_pool_size" = "{DBInstanceClassMemory*3/4}"
 
"innodb_data_home_dir" = "/rdsdbdata/db/innodb"
 
"innodb_file_per_table" = "1"
 
"innodb_flush_method" = "O_DIRECT"
 
"innodb_log_buffer_size" = "8388608"
 
"innodb_log_file_size" = "2147483648"
 
"innodb_log_group_home_dir" = "/rdsdbdata/log/innodb"
 
"key_buffer_size" = "16777216"
 
"local_infile" = "1"
 
"log-bin" = "/rdsdbdata/log/binlog/mysql-bin-changelog"
 
"log_error" = "/rdsdbdata/log/error/mysql-error.log"
 
"log_output" = "TABLE"
 
"log_slave_updates" = "1"
 
"max_binlog_size" = "134217728"
 
"max_connections" = "LEAST({DBInstanceClassMemory/25165760},12000)"
 
"performance_schema" = "0"
 
"pid_file" = "/rdsdbdata/log/mysql-{EndPointPort}.pid"
 
"plugin_dir" = null
 
"port" = "{EndPointPort}"
 
"read_buffer_size" = "262144"
 
"read_only" = "{TrueIfReplica}"
 
"read_rnd_buffer_size" = "524288"
 
"relay-log" = "/rdsdbdata/log/relaylog/relaylog"
 
"relay_log_recovery" = "1"
 
"secure_file_priv" = "/tmp"
 
"server_id" = "{ServerId}"
 
"skip-slave-start" = "1"
 
"slow_query_log_file" = "/rdsdbdata/log/slowquery/mysql-slowquery.log"
 
"socket" = "/tmp/mysql.sock"
 
"sync_binlog" = "1"
 
"tmpdir" = "/rdsdbdata/tmp"

The output of `SHOW GRANTS` is the same on both primary/replica:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, PROCESS, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, BINLOG MONITOR, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER ON *.* TO `master`@`%` IDENTIFIED BY PASSWORD '...' WITH GRANT OPTION

Comment by Elena Stepanova [ 2023-03-19 ]

This is likely to be a duplicate of MDEV-20692.

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