Percona Bug Tracker

Summary

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) heshan@localhost [(none)] 10:46:20>show grants for heshan; ----------------------------------------------------------------------------------+ | Grants for heshan@% | +---------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'heshan'@'%' WITH GRANT OPTION | +----------------------------------------------------------------------------------+ 1 row in set (0.00 sec) heshan@localhost [(none)] 10:40:27>show variables like '%limit%'; +------------------------------+----------------------+ | Variable_name | Value | +------------------------------+----------------------+ | delayed_insert_limit | 100 | | eq_range_index_dive_limit | 200 | | ft_query_expansion_limit | 20 | | innodb_ft_result_cache_limit | 2000000000 | | key_cache_division_limit | 100 | | log_slow_rate_limit | 1 | | min_examined_row_limit | 0 | | open_files_limit | 65535 | | optimizer_trace_limit | 1 | | query_cache_limit | 1048576 | | relay_log_space_limit | 0 | | sql_select_limit | 18446744073709551615 | | thread_pool_stall_limit | 500 | | updatable_views_with_limit | YES | +------------------------------+----------------------+ 14 rows in set (0.00 sec) heshan@localhost [(none)] 10:45:30>show variables like '%commit%'; +-------------------------------------------+-------+ | Variable_name | Value | +-------------------------------------------+-------+ | autocommit | ON | | binlog_group_commit_sync_delay | 0 | | binlog_group_commit_sync_no_delay_count | 0 | | binlog_order_commits | OFF | | innodb_api_bk_commit_interval | 5 | | innodb_commit_concurrency | 0 | | innodb_flush_log_at_trx_commit | 1 | | innodb_use_global_flush_log_at_trx_commit | ON | | slave_preserve_commit_order | OFF | +-------------------------------------------+-------+ heshan@localhost [(none)] 10:59:12>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.00 sec) heshan@localhost [(none)] 10:46:27>show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 172.xx.xxx.xxx Master_User: replication Master_Port: 35972 Connect_Retry: 60 Master_Log_File: bin-log.000435 Read_Master_Log_Pos: 5063432 Relay_Log_File: relay-log.001300 Relay_Log_Pos: 5024820 Relay_Master_Log_File: bin-log.000435 Slave_IO_Running: Yes Slave_SQL_Running: Yes