Description:
My mysql version is percona-server-5.7.22-22-linux86_64, OS version 3.10.0-862.el7.x86_64, configured with GTID master slave semi synchronization.However, ordinary users are used to operate SELECT or SHOW operation times on SLAVE. The ERROR is: ERROR 1290 (HY000): The MySQL server is running with The --read-only option so it cannot execute this statement.I don't know the reason, please help check it, thank you very much. Here are some of my configuration information:
cat /etc/my.cnf
[mysqld]
# ---------------------- General ----------------------
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
log_bin_trust_function_creators = 1
lower_case_table_names = 1
socket = /tmp/35972.sock
port = 35972
basedir = /app/mysql/dist
datadir = /app/mysql/data
pid-file = /app/mysql/pid/35972.pid
transaction_isolation = READ-COMMITTED
event_scheduler = ON
large_pages = ON
skip_name_resolve = ON
open_files_limit = 65535
read_buffer_size = 524288
read_rnd_buffer_size = 524288
sort_buffer_size = 2M
join_buffer_size = 2M
tmp_table_size = 64M
max_heap_table_size = 64M
back_log = 1000
userstat = 1
# ---------------------- Character ----------------------
character-set-server = utf8mb4
# ----------- Connections & Network & thread ------------
max_connections = 3000
max_allowed_packet = 1024M
thread_cache_size = 100
# ----------------------- Innodb ------------------------
innodb_buffer_pool_size = 15G
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_flush_method = O_DIRECT
innodb_undo_tablespaces = 8
innodb_undo_log_truncate = 1
innodb_max_undo_log_size = 10G
innodb_data_file_path = ibdata1:1000M:autoextend
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_compressed_pages = OFF
innodb_sort_buffer_size = 128M
innodb_spin_wait_delay = 30
innodb_sync_spin_loops = 100
innodb_disable_sort_file_cache = ON
innodb_open_files = 5000
innodb_io_capacity = 2000
innodb_monitor_enable = all
# ------------------------ Log -------------------------
general_log = OFF
general_log_file = /app/mysql/log/general/sql.log
slow-query-log-file = /app/mysql/log/slow/slow.log
slow_query_log = ON
long_query_time = 1
log-error = /app/mysql/log/error/error.log
log_output = TABLE
log_timestamps = SYSTEM
log_slow_slave_statements = ON
log_slow_admin_statements = ON
slow_query_log_always_write_time = 1
slow_query_log_use_global_control = all
log_slow_rate_type = query
log_slow_rate_limit = 1
log_slow_verbosity = full
query_response_time_stats = 1
# ----------------------- binlog -----------------------
log-bin = /app/mysql/log/binlog/bin-log
log-bin-index = /app/mysql/log/binlog/bin-log.index
expire_logs_days = 5
sync_binlog = 1
max_binlog_size = 100M
binlog_cache_size = 2M
binlog_order_commits = OFF
binlog-format = ROW
# --------------------- Replication --------------------
server-id = 1533807165
skip_slave_start = 1
master-info-repository = table
relay-log-info_repository = table
gtid-mode = on
enforce-gtid-consistency = true
log-slave-updates = true
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 10000
rpl_semi_sync_slave_enabled = 1
# ---------------------- relaylog ----------------------
relay-log = /app/mysql/log/relay/relay-log
relay-log-index = /app/mysql/log/relay/relay-log.index
relay-log-info-file = /app/mysql/log/relay/relay-log.info
relay_log_purge = 0
# ----------------------- Parallel -----------------------
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 5
[mysql]
prompt="\\u@\\h [\\d] \\R:\\m:\\s>"
------ERROR BEGIN-------------------------------------------------------
heshan@localhost [supermarket] 11:04:51>select COUNT(1) from bak_t_point_event_log_20170901;
+----------+
| COUNT(1) |
+----------+
| 66186292 |
+----------+
1 row in set (8.58 sec)
heshan@localhost [supermarket] 11:05:08>select COUNT(1) from bak_t_point_event_log_20170901;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
heshan@localhost [supermarket] 11:05:19>select COUNT(1) from bak_t_point_event_log_20170901;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
heshan@localhost [supermarket] 11:05:29>select COUNT(1) from bak_t_point_event_log_20170901;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
heshan@localhost [supermarket] 11:05:40>SHOW variables like '%read_only%';
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
heshan@localhost [supermarket] 11:05:43>SHOW variables like '%read_only%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| innodb_read_only | OFF |
| read_only | ON |
| super_read_only | OFF |
| transaction_read_only | OFF |
| tx_read_only | OFF |
+-----------------------+-------+
5 rows in set (0.01 sec)
How to repeat:
Copy my.cnf to your mysql server and set the above important parameters. Start executing the SQL statement on slave, sometimes without error, and sometimes with read-only error