Are you trying to figure out MYSQL replication error 1146?
This is a generic error when we set up the MYSQL replication.
Also, the main reasons for the MYSQL replication error 1146 due to invalid MySQL queries or non-existing SQL queries.
At Bobcares, we often get requests to solve such MYSQL replication errors as part of our Server Management Services.
Today, let’s analyze the cause and see how our Support Team fix it for our customers.
What is MYSQL replication?
Normally, it is a process that enables data from one MySQL database server (the master) to copied automatically to one or more MySQL database servers (the slaves). However, general principles of setting up the MySQL master-slave replication on the same machine are the same for all operating systems.
It is simply copying data from one server to another server. So all the users can share the same data without any inconsistency.
The main Advantages of MySQL Replication includes,
- High Performance
- Backup and Security
- Remote Access
Reasons for MYSQL replication error 1146.
This is a generic error when we discover the slave MySQL server is having a problem replicating data from the master. The main reason for this error is due to invalid MySQL queries or non-existing mysql queries.
Recently one of our customers contacted us with this MYSQL error when he tried to set-up the MYSQL replication. Then he checked the slave status and returned the error code like this.
MYSQL replication error 1146
The above error message shows that the table “db3.table3” does not exist in the slave database. To fix this error, we just simply ignore this error and resume the replication.
How we fix the MYSQL replication error 1146.
So far, we discuss the MYSQL replication and reasons for the replication error 1146. Now let’s see how our Support Engineers fix this error for our customers.
Solution 1
To fix the replication error we follow the below steps.
1. First, we log into the MYSQL.
mysql -u root -p
2. On the MySQL shell, we check the slave status.
mysql> SHOW SLAVE STATUSThe sample result as follows.
mysql> SHOW SLAVE STATUS
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 1.2.3.4
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001089
Read_Master_Log_Pos: 269214467
Relay_Log_File: slave-relay.000234
Relay_Log_Pos: 100125935
Relay_Master_Log_File: mysql-bin.001079
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: mydb
Last_Errno: 1146
If anyone of the Slave_IO_Running or Slave_SQL_Running is set as NO, it means the replication is broken.
So, we start to repair the MYSQL replication.
3. For that, we stop the slave from replication, using the below command.
mysql> STOP SLAVE;
4. Next, we tell the slave to simply skip the invalid SQL query. So we use the below command.
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;This query tells the slave to skip one query (which is the invalid one that caused the replication to stop). If we like to skip two queries, we use the following code instead.
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 2;That’s it.
5. Again, we start the slave.
mysql> START SLAVE;6. After that, we check if replication is working again.
mysql> SHOW SLAVE STATUS
mysql> SHOW SLAVE STATUS
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 1.2.3.4
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001089
Read_Master_Log_Pos: 269214467
Relay_Log_File: slave-relay.000234
Relay_Log_Pos: 100125935
Relay_Master_Log_File: mysql-bin.001079
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: mydb
Last_Errno: 1146 Both Slave_IO_Running and Slave_SQL_Running are set to Yes now. And the replication is running without any error.
Then we leave the MySQL shell.
mysql> quit;
Solution 2
Recently another customer contacted us with the MYSQL replication error. He also tried to set up the MYSQL replication and returns the error as follows.
[ERROR] Slave I/O: Unable to load replication GTID slave state from mysql.gtid_slave_pos: Table 'mysql.gtid_slave_pos' doesn't exist, Internal MariaDB error code: 1146
Next, he tried mysql_upgrade` and it looked already up to date, so he tried –force, which gave:
mysql.gtid_slave_pos
Error : Table 'mysql.gtid_slave_pos' doesn't exist in engine
status: Operation failedThen he checked the dB/mysql folder and found that the .frm and .ibd is already existing.
gtid_slave_pos.frm
gtid_slave_pos.ibd
Then we just removed the files and recreated the table, which solved the error.
1. So we go to the mysql folder and drop the following files using the command.
rm gtid_slave_pos.frmrm gtid_slave_pos.ibdAlso, to create the table we use the command:
CREATE TABLE table_name (column_name column_type);2. Then, we stop and start the slave
mysql]> stop slave;
mysql]> start slave;This fixes the error.
[Need more assistance for MYSQL replication error 1146? We’ll help you]
Conclusion
In short, the main reasons for the MYSQL replication error 1146 is invalid MySQL queries or non-existing SQL queries. Today, we saw how our Support Engineers fix this error for our customers.
I too had this 1146 error regarding creating tables in the DB.
Then I manually created the DB and the tables related to the error.
After that, I executed `STOP SLAVE` and `START SLAVE`.
Finally, it auto-synced all the data from the other instance.