Bug #92311 slave report a error:--read-only option so it cannot execute this statement
Submitted: 6 Sep 2018 3:25 Modified: 6 Sep 2018 21:23
Reporter: 何善 heshan Email Updates:
Status: Unsupported Impact on me:
None 
Category:MySQL Server: Command-line Clients Severity:S3 (Non-critical)
Version:5.7.22 OS:Linux (Linux version 3.10.0-862.el7.x86_64)
Assigned to: MySQL Verification Team CPU Architecture:Any
Tags: error, read-only, slave

[6 Sep 2018 3:25] 何善 heshan
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
[6 Sep 2018 21:23] MySQL Verification Team
Hi,

> My mysql version is percona-server-5.7.22-22-linux86_64

Don't you think you should report this to Percona?

kind regards
Bogdan