Replication failed for drop user if exists

Hi

we have recently setup replication between Mariadb 10.2.32 to Percona xtradb cluster 8.0.34 by adding below filters
Replicate_Ignore_DB: mysql,sys,performance_schema on 1st node of pxc.

Replication ran without any issues for sometime, after that it was broken with below error,
Worker 1 failed executing transaction ‘NOT_YET_DETERMINED’ at source log bin.000010, end_log_pos 18374821; Error ‘String ‘xxxxxxxxx’ is too long for user name (should be no longer than 32)’ on query. Default database: ‘’. Query: ‘DROP USER IF EXISTS ‘xxxxxxxxx’@‘10.%’’ | 2024-02-12 13:54:22.811109

though we have added filter to not replicate mysql database, drop user if exists still replicated to pxc from mariadb and failed

Hello @anjaneyarajus

Error ‘String ‘xxxxxxxxx’ is too long for user name (should be no longer than 32)’ on query.

Error is for username length/char size.

Mariadb mysql.user table has User char(80), which allows 80 char length username , but in MySQL/PS/PXC mysql.user` table on allow max 20 char length user name,

MariaDB: mysql.user Table - MariaDB Knowledge Base

MySQL/PS/PXC :
desc mysql.user;
±-------------------------±----------------------------------±-----±----±----------------------±------+
| Field | Type | Null | Key | Default | Extra |
±-------------------------±----------------------------------±-----±----±----------------------±------+
| Host | char(255) | NO | PRI | | |
| User | char(32) | NO | PRI | | |

Because of this, you are seeing reported error.

though we have added filter to not replicate mysql database, drop user if exists still replicated to pxc from mariadb and failed

Can you add here what exact repication filter option is used for this and how?

Hi Lalit,

below are the replication filters added in my.cnf

Replication filters

replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=performance_schema

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: xxxxxxxxx
Master_User: xxxxxxx
Master_Port: 3306
Connect_Retry: 60
Master_Log_File:bin.000010
Read_Master_Log_Pos: 410466437
Relay_Log_File: xxxxxxx
Relay_Log_Pos: 49495305
Relay_Master_Log_File: bin.000010
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,sys,performance_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:

can we modify char(32) to char(100) for user table?

Hello @anjaneyarajus

with Replicate_Ignore_DB: mysql it will only ignore SQL when it executed under mysql database using use mysql; command.

Example:

slave1 [localhost] {msandbox} ((none)) > show replica status\G             
*************************** 1. row ***************************

           Replica_IO_Running: Yes
          Replica_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: mysql,sys,performance_schema



master [localhost] {root} ((none)) > **use test;**
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
master [localhost] {root} (test) > create user u1_testdb@'%' identified by 'msan>
Query OK, 0 rows affected (0.02 sec)

slave1 [localhost] {msandbox} ((none)) > select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| u1_testdb        | %         |
| msandbox         | 127.%     |
| msandbox_ro      | 127.%     |


master [localhost] {root} (test) > **use mysql**
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
master [localhost] {root} (mysql) > create user u1_mysqldb@'%' identified by 'ms>
Query OK, 0 rows affected (0.02 sec)


slave1 [localhost] {msandbox} ((none)) > select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| u1_testdb        | %         |
| msandbox         | 127.%     |
| msandbox_ro      | 127.%     |

with use mysql; create user not replicated.

Regarding,

can we modify char(32) to char(100) for user table?

It is not recommended as it will break the logic + corrupt user table as we can see in below example:

slave1 [localhost] {msandbox} ((none)) > alter table mysql.user modify user char(100); 
Query OK, 12 rows affected (0.09 sec)
Records: 12  Duplicates: 0  Warnings: 0

slave1 [localhost] {msandbox} ((none)) > desc mysql.user;                                                                                                            
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                    | Type                              | Null | Key | Default               | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                     | char(255)                         | NO   | PRI |                       |       |
| user                     | char(100)                         | NO   | PRI | NULL                  |       |


slave1 [localhost] {msandbox} (mysql) > create user replicawdofeowfwfibflwjwbefbwobwbwe@'%';               
ERROR 1470 (HY000): String 'replicawdofeowfwfibflwjwbefbwobwbwe' is too long for user name (should be no longer than 32)
slave1 [localhost] {msandbox} (mysql) > create user replica@'%';                 
**ERROR 1728 (HY000): Cannot load from mysql.user. The table is probably corrupted**
slave1 [localhost] {msandbox} (mysql) > 


mysql log:
2024-02-12T17:38:06.164623Z 14 [Warning] [MY-013139] [Server] Cannot load from mysql.user. The table is probably corrupted!

To fix this issue, you can try the following,

  • Skip the replication error to continue replication and drop the user manually on Replica if it exists.

Hi Lalit,

I have even retried with Replicate_Wild_Ignore_Table,

replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=sys.%
replicate-wild-ignore-table=performance_schema.%

Even after that, I have faced similar issues. When a replication filter is added to avoid a specific database, it shouldn’t allow the drop user command to replicate on slaves.

is there a way we can avoid statements like grants,create user,drop user and revoke to replicate to slave?

Yes, keep the replication filter for mysql database, and when you execute statements like grants,create user,drop user and revoke, make sure you select mysql database as current database by running use mysql; before executing the above user related commands.

grants,create user,drop user and revoke requests comes from application.

below filters shouldn’t not allow irrespective of which db we are running the mentioned commands
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=sys.%
replicate-wild-ignore-table=performance_schema.%

as far as i know above filters were working in mysql 5.7 version.
are there any changes in mysql 8.X versions?

is there a way we can add filters even if you don’t mention use db?

This could be due to the difference in binlog_format between the 5.7 and 8.0 version setup.