| Bug #98069 | No error on slave while trigger for binlog_format="statement" and read_only is 1 | ||
|---|---|---|---|
| Submitted: | 25 Dec 2019 10:12 | Modified: | 31 Dec 2019 8:47 |
| Reporter: | Aayushi Mangal | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
| Version: | 5.7 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | replica | ||
[25 Dec 2019 10:14]
Aayushi Mangal
that will create schema
Attachment: all_schema.sql (application/octet-stream, text), 14.22 KiB.
[25 Dec 2019 10:15]
Aayushi Mangal
call trigger
Attachment: insert_error.sql (application/octet-stream, text), 202 bytes.
[25 Dec 2019 10:15]
Aayushi Mangal
this is not giving error
Attachment: insert_success.sql (application/octet-stream, text), 160 bytes.
[31 Dec 2019 8:47]
MySQL Verification Team
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_read_only and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php As you already noted, this is an expected and documented behavior i.e Quoting from our manual https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_read_only Even with read_only enabled, the server permits these operations: Updates performed by slave threads, if the server is a replication slave. In replication setups, it can be useful to enable read_only on slave servers to ensure that slaves accept updates only from the master server and not from clients.
[9 Jan 2020 4:53]
Kazuya Notsu
Hi Umesh, Excuse me for cutting in. I understand its error happen because TRIGGER definer (= general user) don't have SUPER privilege nevertheless read_only=ON". > Updates performed by slave threads, if the server is a replication slave. In replication setups, it can be useful to enable read_only on slave servers to ensure that slaves accept updates only from the master server and not from clients. Does above documentation mean "Updates of TRIGGER performed by slave threads" ? In this case, it seems that TRIGGER was executed by another SUPER user other than definer (= general user).
Description: Test Case: 1) master/slave setup 2) read_only enabled only on the slave with binlog_format="STATEMENT" 3) there is a "general user" for TRIGGER DEFINER execute "insert_error.sql" which call TRIGGER 4) "general user" don't have SUPER privilege 5) with read_only =1, the slave can accept write only from the master, and when we execute insert on the master, the slave should observe "TRIGGER" error, but no error found. Expected Results: 1) Master could execute "insert_error.sql" 2) Slave receives its replication event from Master's binary log, and 3) Slave tried to execute the same INSERT, but TRIGGER error should happen, For example: when I set read_only to 1 for master also, then it will prompt an error: master [localhost:19529] {root} ((none)) > SET GLOBAL read_only = ON; Query OK, 0 rows affected (0.01 sec) aayushi.mangal@bm-support01:~/sandboxes/rsandbox_5_7_28$ ./m -u admin_user -p < /home/aashushi.mangal/sandboxes/insert_error.sql Enter password: ERROR 1290 (HY000) at line 1: The MySQL server is running with the --read-only option so it cannot execute this statement In documents I found that: "When the read_only system variable is enabled, the server permits no client updates except from users who have the CONNECTION_ADMIN or SUPER privilege." I have also tested those steps while checking the slave status with read_only=ON only on the slave and checked, I did not found any error in both the "ROW" and "STATEMENT" based slave when it is replicated by slave threads, not directly from the client. Here is the statement from the same document: "Even with read_only enabled, the server permits these operations: Updates performed by slave threads, if the server is a replication slave. In replication setups, it can be useful to enable read_only on slave servers to ensure that slaves accept updates only from the master server and not from clients" But no documentation for how "STATEMENT" based work in this case How to repeat: 1) Create User as: CREATE USER 'admin_user'@'localhost'; GRANT ALL PRIVILEGES ON *.* TO 'admin_user'@'localhost' WITH GRANT OPTION; CREATE USER 'general_user'@'%'; GRANT PROCESS ON *.* TO 'general_user'@'%'; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `test_db2`.* TO 'general_user'@'%'; 2) Set read_only=1 and binlog_format="STATEMENT" in slave 3) create tables using all_schema.sql and then execute insert_success.sql and insert_error.sql that will call "TRIGGER" aayushi.mangal@bm-support01:~/sandboxes/rsandbox_5_7_28$ ./m -u admin_user -p < /home/aashushi.mangal/sandboxes/insert_error.sql Enter password: aayushi.mangal@bm-support01:~/sandboxes/rsandbox_5_7_28$ ./n2 -u admin_user -p -e "show slave status\G" Enter password: *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: rsandbox Master_Port: 19529 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 21886 Relay_Log_File: mysql-relay.000002 Relay_Log_Pos: 22099 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 21886 Relay_Log_Space: 22302 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 100 Master_UUID: 00019529-1111-1111-1111-111111111111 Master_Info_File: /home/aashushi.mangal/sandboxes/rsandbox_5_7_28/node1/data/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: