December 26, 2024

User Info Update Triggers MySQL Replication Anomaly

Check user status by querying tables. Verify permissions and correctness of renaming.

Introduction

The customer inadvertently entered incorrect host value when creating a MySQL user. Subsequently, they carried out a data update operation on mysql.user of the primary database (adjusted the host value of this user). Immediately afterward, the replication status of the replica database in the cluster abruptly turned abnormal. This article will reproduce the fault and provide a solution.

Fault Reproduction

Environment

Create a test user

create user test@'10.186.60.63' identified by 'test';
grant all privileges on test.* to test@'10.186.60.63';

Simulate the fault

Execute the UPDATE operation.

mysql> update mysql.user set host='101.86.60.64' where user='test' and host='10.186.60.63';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Check the user information in the mysql.user table on 10.186.60.64 (the primary node), and it is found that the update was successful.

mysql> select user , host from mysql.user where user='test';
+------+--------------+
| user | host         |
+------+--------------+
| test | 101.86.60.64 |
+------+--------------+
1 row in set (0.00 sec)

Check the user information in the mysql.user table on 10.186.60.63 (the replica node), and it is found that it is still the original host address.

mysql> select user , host from mysql.user where user='test';
+------+--------------+
| user | host         |
+------+--------------+
| test | 10.186.60.63 |
+------+--------------+
1 row in set (0.01 sec)

Problem Analysis

Obtain the replication status of 10.186.60.63 (the replica node), and it is found that Replica_SQL_Running is No, and the following error message appears:

Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '6c77afd9-6387-11ef-a8ad-02000aba3c40:413' at master log mysql-bin.000003, end_log_pos 294043. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

Based on the error message, we analyzed mysql-bin.000003 on 10.186.60.64 (the primary node) and found that the SQL statement that caused the error was the UPDATE statement executed just now.

SET @@SESSION.GTID_NEXT= '6c77afd9-6387-11ef-a8ad-02000aba3c40:413'/*!*/;
# at 293326
#240826 16:51:15 server id 259286858  end_log_pos 293402        Query   thread_id=59    exec_time=0     error_code=0
SET TIMESTAMP=1724662275/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=255/*!*/;
BEGIN;93402
#240826 16:51:15 server id 259286858  end_log_pos 293505        Rows_query
# update mysql.user set host='101.86.60.64' where user='test' and host='10.186.60.63'
# at 293505
#240826 16:51:15 server id 259286858  end_log_pos 293699        Table_map: `mysql`.`user` mapped to number 62

Check the mysql-error.log on 10.186.60.63 (the replica node) and find the following error message:

2024-08-26T16:51:15.781430+08:00 117 [ERROR] [MY-013146] [Repl] Replica SQL for channel '': Worker 1 failed executing transaction '6c77afd9-6387-11ef-a8ad-02000aba3c40:413' at master log mysql-bin.000003, end_log_pos 294043; Column 0 of table 'mysql.user' cannot be converted from type 'char(255(bytes))' to type 'char(180(bytes) utf8)', Error_code: MY-013146

‍

According to the error message of the replica database, the replication anomaly is caused by the difference in the definition of a certain field type in the mysql.user table. So we checked the mysql.user tables of the two databases and found that:

There is indeed a difference in the definition of the host field in the mysql.user table between MySQL 8.0.34 and MySQL 8.0.13!

The following is the specific definition of the host field in MySQL:

MySQL 8.0.34

mysql>  show create table mysql.user \G
*************************** 1. row ***************************
    Table: user
Create Table: CREATE TABLE `user` (
`Host` char(255) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL DEFAULT '',
....................

‍

MySQL 8.0.13

mysql> show create table mysql.user \G
*************************** 1. row ***************************
    Table: user
Create Table: CREATE TABLE `user` (
`Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
....................

Solution

After confirming that there were indeed differences in the host field, we adjusted the replica_type_conversions= 'ALL_LOSSY' on the replica node and then restarted the replication. We observed that the replication returned to normal and the user update was successful. After that, we adjusted the replica_type_conversions parameter back to an empty value.

mysql> set global replica_type_conversions='ALL_LOSSY';
Query OK, 0 rows affected (0.00 sec)

mysql> start replica;
Query OK, 0 rows affected (0.12 sec)

mysql> select user , host from mysql.user where user='test';
+------+--------------+
| user | host         |
+------+--------------+
| test | 101.86.60.64 |
+------+--------------+
1 row in set (0.00 sec)

mysql> set global replica_type_conversions='';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@replica_type_conversions;
+--------------------------+
| @@replica_type_conversions |
+--------------------------+
|                          |
+--------------------------+
1 row in set (0.00 sec)

Conclusion

Through the experiment, it can be seen that there are indeed differences in the definition of the mysql.user table between these two versions of MySQL. Moreover, the operation of updating the host value of MySQL users in this case did not comply with the operation and maintenance specifications, so the replication status of the replica node became abnormal.

When adding replica nodes to a MySQL cluster, try to choose the same version as the primary node as much as possible to avoid anomalies in the replay of data on the replica node caused by version issues.
For the scenario of adjusting the user's host, it can be done through the RENAME method. Try to avoid performing DML operations directly on MySQL system tables as much as possible. For specific operations, please refer to the following content.

Suggestions

In this scenario where the user's host needs to be modified, you can create a new user with the same permissions and password to replace the original one, and then delete the original user.
Users can also be renamed by using the RENAME USER method.

Supplement

The role of the replica_type_conversions parameter

It is used to adjust the type conversion in primary-replica replication. Besides the default empty value, the other optional values for this parameter are: ALL_LOSSY, ALL_NON_LOSSY, ALL_SIGNED, ALL_UNSIGNED. The following is an explanation of MySQL under each of these values:

ALL_LOSSY: In this mode, type conversions that may lead to information loss are allowed.
ALL_NON_LOSSY: This mode allows conversions that do not require truncation or other special handling of the source value. That is to say, it allows conversions when the range of the target type is larger than that of the source type.
ALL_LOSSY, ALL_NON_LOSSY: When this mode is set, all supported type conversions are allowed, regardless of whether they are lossy conversions or not.
ALL_SIGNED: The promoted integer types are treated as signed values (the default behavior).
ALL_UNSIGNED: The promoted integer types are treated as unsigned values.
ALL_SIGNED, ALL_UNSIGNED: If possible, the promoted integer types are treated as signed; otherwise, they are treated as unsigned.
Default value (empty): When it is not set, the promotion or demotion of attributes is not allowed. This means that all columns in the source table and the target table must have the same type.

Notes for Modifying User Information with RENAME

When we use the DROP or RENAME methods to operate on users, the information related to the user's custom objects will not be deleted or updated. This will lead to problems such as the triggering of "user does not exist" or "insufficient permissions" when we call custom objects.

Methods for Querying Custom Objects

We can obtain the relevant information about (procedure, function, event, trigger, view) by querying the following six tables.

information_schema.EVENTS
mysql.event
information_schema.ROUTINES
mysql.proc
information_schema.TRIGGERS
information_schema.VIEWS

The SECURITY_TYPE attribute of custom objects

When we create custom objects, there is an attribute called SECURITY_TYPE that can be defined. From the literal meaning, we can guess that its purpose is related to security, that is, it is used for authentication processing. This attribute has two optional values, namely DEFINER and INVOKER. Now, let's introduce the functions of each of these values respectively:

DEFINER means that when a user has the EXECUTE permission for a procedure, when it is called, we will check whether the creator of the procedure has the EXECUTE permission and the access permissions for the relevant databases and tables. If any of these permissions is lacking, an error will be reported. None of them can be missing. That is to say, when the SECURITY_TYPE is set to DEFINER, if the user to whom the procedure belongs is deleted, then this procedure will be marked as an invalid/orphan stored object (also known as Orphan Stored Objects in the database).

INVOKER means that when a user has the EXECUTE permission for a procedure, when it is called, we will check whether the caller has the access permissions for the relevant databases and tables involved in the procedure. If any of these permissions is lacking, an error will be reported. None of them can be missing. That is to say, when the SECURITY_TYPE is set to INVOKER, even if the user to whom the procedure belongs is deleted, as long as the caller has the execution permission for the procedure and the permissions for the corresponding databases and tables, it can still be used normally.

How to Ensure the Validity of Objects After Modifying User Definitions

For the three types of objects, including procedures, functions, and events, since their definitions exist in the MySQL database:

For the DEFINER attribute, we can directly update the value of the DEFINER field to make modifications.
For the SECURITY_TYPE attribute, we can directly use the ALTER PROCEDURE syntax to make modifications.

For Trigger and View, since they are not stored in the MySQL database, and the INFORMATION_SCHEMA database is a special in-memory temporary database where DDL operations cannot be performed. Although MySQL provides the ALTER VIEW syntax (which actually means rebuilding the view), we can use the CONCAT function to concatenate the definition of the view that needs to be modified, modify its DEFINER, and then re-execute it. As for Trigger, since their definitions involve more elements, we can consider concatenating them, or we can directly query the trigger definitions and then execute them manually.

select concat(
  "alter DEFINER=`user_a`@`%` SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";"
  ) from information_schema.VIEWS 
  where definer='root@localhost';

Best Practices for Database Custom Objects

Avoid using them as much as possible unless it is necessary.
During the creation process, it is necessary to clearly define the DEFINER attribute and try your best to ensure that the user defined as the DEFINER will not be modified or deleted.
When creating, it is advisable to preferentially set SQL SECURITY to INVOKER. In this way, even if the user defined as the DEFINER is deleted, it will not affect the invocation of the object (there may be a prompt indicating insufficient permissions, but there will be no prompt saying that the user does not exist), and it can more clearly manage and control whether the invoker has the permission to operate on the tables in the database.

Verification of the correctness of user renaming

## Check user status.
select user,host,plugin from mysql.user;

## Query these tables one by one to see if there are relevant user object definitions.
select EVENT_SCHEMA,EVENT_NAME,DEFINER from information_schema.EVENTS;
select ROUTINE_SCHEMA,ROUTINE_NAME,ROUTINE_TYPE,DEFINER from information_schema.ROUTINES;
select TABLE_SCHEMA,TABLE_NAME,DEFINER,SECURITY_TYPE from information_schema.views;
select TRIGGER_SCHEMA,TRIGGER_NAME,DEFINER from information_schema.TRIGGERS;

## User permission verification.
select user,host,db from mysql.db;
select user,host from mysql.tables_priv;
select user,host from mysql.columns_priv;
select user,host,db from mysql.procs_priv;
select user,host from mysql.proxies_priv;