Tag Archives: 1507

MySQL master-slave synchronization error 1507

After MySQL manually deleted partons from the database, the main database was not modified. Later, problems occurred after deleting partons from the main database

Fault site

Last_Errno: 1507
                   Last_Error: Error 'Error in list of partitions to DROP' on query. Default database: ''. Query: 'ALTER TABLE DROP PARTITION part_20170209' 

 

 

Solution:

Check the partition to be deleted according to the error reported from the database to the corresponding location. It is found that there is no partition. The problem is clear here. The rest is simple repair operations

mysql> stop slave ; Query OK, 0 rows affected (0.00 sec)  mysql> set global sql_slave_skip_counter=1; Query OK, 0 rows affected (0.00 sec)  mysql> start slave; Query OK, 0 rows affected (0.01 sec)

Repeat until the problem is fixed. If there are many same errors, you can also write a script to continuously monitor the MySQL slave status. If the same errors are matched, you can skip. Let’s take a look at the situation after repair

Seconds_Behind_Master: 1265 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 

I felt it was ok, there was a little delay, but I found it in time

 

 

Summary:

1. In case of such a problem, first of all, it is certain that the drop partition operation cannot be performed manually with a large permission account, resulting in no error found in the partition when the master database is executed

      To control such problems, we can start with permissions and strictly control user permissions. Non DBA personnel are not allowed to have operation permissions directly (our permissions are very strict)