Federated table issue

Question

I linked a table called timesheet from a remote server by using the FEDERATED engine.

The table has these fields:

• ts_id
• ts_userid
• ts_weekend

If I execute this query, the result is correct, including a record for weekend 2022年11月27日:

SELECT * FROM timesheet WHERE ts_userid=123;

But if I added one more criteria into the WHERE clause, the result was empty.

SELECT * FROM timesheet WHERE ts_userid=123 AND ts_weekend='2022-11-27';

I tried to transfer the timesheet table from the remote server to the local server, and all the queries are fine.

Is there is a way to fix the issue with the federated table?

Answer 1

This matches the scenario of this bug, reported in 2016: https://bugs.mysql.com/bug.php?id=83851

I wouldn't hold your breath waiting for a fix. The federated engine of MySQL gets very little attention.

The original purpose of the federated engine was to support high traffic writes, not reads. It was developed for cases where an application was connected to a MySQL Server, but a single server could not handle the rate of INSERTs the application was sending it. The bottleneck was the I/O throughput rate of slow disks.

These days, with high-performance storage like NVMe and RAID, the role of the federated engine is doubtful. Storage can easily keep up with a greater rate of INSERTs. It's more likely that the InnoDB software cannot send rows to the storage fast enough to saturate the I/O.

If your application needs to read data from a secondary MySQL instance, it's easier to just open a second connection to that instance. No need for a federated table.