2

I have a standard simple master slave replication that usually runs fine. But sometimes, all of a sudden, I get replication errors on the "phpmyadmin" table.

I have explicitly only specified one database in replicate_do_db and in binlog_do_db. So it should only log and process that database. Why am I getting errors on another table?

This is the output of master status and slave status:

mysql> show master status \G
*************************** 1. row ***************************
            File: mysql-bin.001473
        Position: 84364707
    Binlog_Do_DB: my-database
Binlog_Ignore_DB: 
1 row in set (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: my-master-host
                  Master_User: pm-rep
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.001473
          Read_Master_Log_Pos: 82124707
               Relay_Log_File: mysqld-relay-bin.000486
                Relay_Log_Pos: 38975372
        Relay_Master_Log_File: mysql-bin.001473
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: my-database
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1146
                   Last_Error: Error 'Table 'phpmyadmin.pma_column_info' doesn't exist' on query. Default database: 'my-database'. Query: 'DELETE FROM `phpmyadmin`.`pma_column_info` WHERE `db_name` = 'my-database' AND `table_name` = 'users' AND `column_name` = 'INDEX''
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 38975226
              Relay_Log_Space: 82125053
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 1146
               Last_SQL_Error: Error 'Table 'phpmyadmin.pma_column_info' doesn't exist' on query. Default database: 'my-database'. Query: 'DELETE FROM `phpmyadmin`.`pma_column_info` WHERE `db_name` = 'my-database' AND `table_name` = 'users' AND `column_name` = 'INDEX''
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 5309510
1 row in set (0.00 sec)
2

Replication filtering isn't bulletproof. Due to how the filtering is implemented the events responsible for your errors are being generated because the default database at query runtime is the my-database schema as expected and the query being executed is fully qualified INSERT INTO phpmyadmin.pma_column_info...

Peter Zaitsev explains the scenario well in this post:

Filtered MySQL Replication

1

This problem might have occurred because of the phpmyadmin version difference in master and slave. Due to the difference one creates one table with single _ (underscore) i.e., phpmyadmin.pma_recent and the other with two __ (underscores) i.e., phpmyadmin.pma__recent.

But as we just require some main database to replicate we can ignore these conflicts by adding the following lines.

Edit in my.cnf file by the command:

sudo nano /etc/mysql/my.cnf

replicate-ignore-table=phpmyadmin.pma_recent
replicate-ignore-table=phpmyadmin.pma_table_uiprefs
replicate-ignore-table=phpmyadmin.pma_column_info
//mention whichever table is throwing error 

Then,

  • restart mysql
  • stop slave
  • start slave
  • show slave status

...and the error should be gone.

  • Welcome on the DBA SE! Why is it so? – peterh Nov 23 '18 at 10:25
  • this problem might occurred because of the phpmyadmin version difference in master and slave..and due to the difference one create table with single _ i.e., phpmyadmin.pma_recent and other with two __ phpmyadmin.pma__recent. But as we just require some main database to replicate we can ingore these conflicts by adding above lines. – RVG Nov 23 '18 at 12:04
  • Thanks! I did not ask it for myself, but because there is a requirement, that answers should be understable and useful without the comments. I tried to insert your comment into the post. Such high quality answers get upvotes. :-) – peterh Nov 23 '18 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.