Avoiding Key Errors | Mastering MySQL 4

This example shows what happens when data is updated onto the slave, which causes a key error. This error is commonly caused when you have the master and slave replicating in a circular manner or you make updates directly to a slave.

For this, you're going to add a primary key to the replication_table table. You can modify the existing table, as follows:

mysql> ALTER TABLE replication_table MODIFY f1 INT NOT NULL,ADD PRIMARY KEY(f1); Query OK, 0 rows affected (0.36 sec)

Or you can create the table anew, as follows:

mysql> CREATE TABLE replication_table(f1 INT NOT NULL,f2 VARCHAR(20),  PRIMARY KEY(f1)); Query OK, 0 rows affected (0.03 sec)

Add a few records onto the master and reset the master, so you do not repeat the error of the previous example:

mysql> INSERT INTO replication_table (f1,f2) VALUES(1,'first'); mysql> INSERT INTO replication_table (f1,f2) VALUES(2,'second'); mysql> RESET MASTER;

Copy the data to the slave and start the server replicating. Add a new record on the master:

mysql> INSERT INTO replication_table (f1,f2) VALUES(3,'third'); Query OK, 1 row affected (0.01 sec)

The data on the slave should now appear as follows:

mysql> SELECT * FROM replication_table; +----+--------+ | f1 | f2     | +----+--------+ |  1 | first  | |  2 | second | |  3 | third  | +----+--------+ 3 rows in set (0.00 sec)

So far everything is running smoothly. The problem comes if you add a record on the slave and then add the same record on the master. In this example, you purposely set the key to be the same, but it's most likely to happen when using AUTO_INCREMENT fields. Insert the following record first on the slave, then the master:

mysql> INSERT INTO replication_table (f1,f2) VALUES(4,'fourth'); Query OK, 1 row affected (0.01 sec)

Although if you checked the data on both servers they'd be identical, there's actually an error on the slave, as it has attempted to INSERT the record twice. Unless you used the risky slave-skip-errors option in the configuration file, replication would now stop, and the slave will report the erroneous query, as follows:

mysql> SHOW SLAVE STATUS; +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+---------------- ----------------------------------------------------------------------- -------------------+--------------+---------------------+-------------- ---+ | Master_Host   | Master_User      | Master_Port | Connect_retry |  Master_Log_File    | Read_Master_Log_Pos | Relay_Log_File           |  Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running |  Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | Last_errno  | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space | +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+---------------- ----------------------------------------------------------------------- -------------------+--------------+---------------------+-------------- ---+ | 192.168.4.100 | replication_user | 3306        | 60            |  g-bin.001 | 279                 | s-bin.002 |  224           | g-bin.001    | Yes              | No                 | replication_db  |                     | 1062       | error 'Duplicate  entry '4' for key 1' on query 'INSERT INTO replication_table(f1,f2)  values(4,'fourth')' | 0            | 179                 | 332              | +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------ -------+-----------------+---------------------+------------+---------- ----------------------------------------------------------------------- -------------------------+--------------+---------------------+-------- ---------+ 1 row in set (0.00 sec)

The error is clearly reported so that you can investigate the cause of the error and take action. In this case, the error was that the statement was repeated on the slave when it shouldn't have been. You can get the slave replicating correctly once more by telling it to skip the next command in the master binary log and continue from there. You use the SET SQL_SLAVE_SKIP_COUNTER command for this. Once this is run, you can start the slave (you can only tell a slave to skip when replication is stopped), and it will continue as before, as follows:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; Query OK, 0 rows affected (0.00 sec) mysql> SLAVE START; Query OK, 0 rows affected (0.00 sec) mysql> SHOW SLAVE STATUS; +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+------------+--- -----------+---------------------+-----------------+ | Master_Host   | Master_User      | Master_Port | Connect_retry |  Master_Log_File    | Read_Master_Log_Pos | Relay_Log_File           |  Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running |  Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | Last_errno  | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space | +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+------------+--- -----------+---------------------+-----------------+ | 192.168.4.100 | replication_user | 3306        | 60            |  g-bin.001 | 378                 | s-bin.002 |  423           | g-bin.001    | Yes              | Yes                | replication_db  |                     | 0          |            | 0             | 378                 | 431             | +---------------+------------------+-------------+---------------+----- ---------------+---------------------+--------------------------+------ ---------+-----------------------+------------------+------------------ -+-----------------+---------------------+------------+------------+--- -----------+---------------------+-----------------+ 1 row in set (0.00 sec)

Insert a record on the master:

mysql> INSERT INTO replication_table(f1,f2) values(5,'fifth'); Query OK, 1 row affected (0.00 sec)

Once again it will be replicated on the slave:

mysql> SELECT * FROM replication_table; +----+--------+ | f1 | f2     | +----+--------+ |  1 | first  | |  2 | second | |  3 | third  | |  4 | fourth | |  5 | fifth  | +----+--------+ 5 rows in set (0.00 sec)