How to fix MySQL federated table error

This blog is about a very odd case of a Federated table error causing unable reach the remote MySQL server and causing to error. I’ll share the approach I tool to diagnose and resolve the issue.

The Percona Server instance reported a replication error 1296 that was related to a Federated table, indicating a connection problem.
Here’s the error message:

Last_SQL_Errno: 1296
Last_SQL_Error: Could not execute Write_rows event on table mysql_federated_db.federated_table_test; Got error 10000 'Error on remote system: 0: ' from FEDERATED, Error_code: 1296; handler error No Error!; the event's master log mysql-bin.000012, end_log_pos 26421

Error on remote system: 0

This error is replication error noting it failing to write “Could not execute Write_rows event” though the underlying error says “Got error 10000 ‘Error on remote system: 0: ‘ from FEDERATED”. The error denotes an error on Federated table suggesting that it is not able to reach!

Investigation

I manually tried connecting and it worked:

Reviewing mysql.servers table to check the configuration.
mysql> select * from mysql.servers;
+-------------------+--------------+---------------------+----------+----------+------+--------+---------+-------+
| Server_name | Host | Db | Username | Password | Port | Socket | Wrapper | Owner |
+-------------------+--------------+---------------------+----------+----------+------+--------+---------+-------+
| ServerB | 192.168.0.10 | mysql_federated_db | usr | X | 3306 | | mysql | |
+-------------------+--------------+---------------------+----------+----------+------+--------+---------+-------+

Trying to connect remote server using above configured credentials:

kedar@centos: ~ $ mysql -h192.168.0.10 -P3301 mysql_federated_db -p -uusr
…
mysql> \u mysql_federated_db
Database changed
mysql> select * from federated_table_test limit 1;
+---------+-------------+-----------------------------------+
| id | error | val |
+---------+-------------+-----------------------------------+
| 8582 | 1296 | MySQL Federated Table Error |
+---------+-------------+-----------------------------------+
1 row in set (0.00 sec)

Tried reviewing connectivity information, grants and max_connect_errors though that wasn’t an issue as and there were no hints regard such things in error log!
Also noted there are two tables in the database and querying second database works but first doesn’t.

If you see below the exact error “ERROR 1430 (HY000): : 0 :” noted in replication shows up when manually executing:

mysql> use mysql_federated_db
...
mysql mysql_federated_db> show create table federated_table_test_OK\G
*************************** 1. row ***************************
       Table: federated_table_test_OK
Create Table: CREATE TABLE `federated_table_test_OK` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `error` int(11) NOT NULL DEFAULT '0',
  `val` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=FEDERATED DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci CONNECTION='ServerB/federated_table_test_OK'
1 row in set (0.00 sec)

mysql mysql_federated_db> select * from federated_table_test_OK limit 1;
+---------+--------+---------------------------+
| id      | error  | val                       |
+---------+--------+---------------------------+
| 1       | 1      | No Database Errors        |
+---------+--------+---------------------------+
1 row in set (0.00 sec)

centos mysql_federated_db> select * from mysql_federated_db.federated_table_test limit 1;
ERROR 1430 (HY000): : 0 :

I decided to try creating a new table with same definition to see if it can fetch the data as

1. connection to the remote server works
2. table is present on remote server

centos mysql_federated_db> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)

centos mysql_federated_db> CREATE TABLE if not exists `_fed_test` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `error` int(11) NOT NULL DEFAULT '0',
    ->   `val` int(11) NOT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=FEDERATED DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci CONNECTION='ServerB/federated_table_test';
Query OK, 0 rows affected (0.00 sec)

centos mysql_federated_db> select * from _fed_test limit 1;
+---------+-------------+-----------------------------------+
| id      | error       | val                               |
+---------+-------------+-----------------------------------+
| 8582    | 1296        | MySQL Federated Table Error       |
+---------+-------------+-----------------------------------+
1 row in set (0.22 sec)

Wow!! The new table worked but the old didn’t! Something’s wrong with the table in the memory and hence I decided to “flush table”.

centos mysql_federated_db> FLUSH TABLE federated_table_test;
...
centos mysql_federated_db> select * from federated_table_test limit 1;
+---------+-------------+-----------------------------------+
| id      | error       | val                               |
+---------+-------------+-----------------------------------+
| 8582    | 1296        | MySQL Federated Table Error       |
+---------+-------------+-----------------------------------+

Well, so I hit “START REPLICA” and replication caught-up in few mins. This looks like a bug but I couldn’t reproduce this yet and I welcome you to share with me if you already know the history.

TL;DR

When “Error on remote system: 0:” for federated table, try FLUSH TABLE.