MySQL Bugs: #111909: While using federate tables in mysql 8.0.28 getting communication packet error

Bug #111909	While using federate tables in mysql 8.0.28 getting communication packet error
Submitted:	28 Jul 2023 7:02	Modified:	18 Oct 2023 10:16
Reporter:	Azar T	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Websites: bugs.mysql.com	Severity:	S5 (Performance)
Version:	8.0.28	OS:	CentOS
Assigned to:		CPU Architecture:	x86

Description:
While using federate tables in mysql 8.0.28 getting communication packet error randomly

when a query is run from php , getting this error randomly

ERROR 1160 (08S01): Got an error writing communication packets

How to repeat:
SELECT sum(sbr.TOTAL_WIN_AMOUNT+sbr.refund) as multi_bet FROM `sports_bet` as `sb_s` RIGHT JOIN `sports_bet_rollback` `sbr` ON `sb_s`.`SPORTS_BET_ID` = `sbr`.`SPORTS_BET_ID` JOIN `player` `p` ON `p`.`PLAYER_ID` = `sb_s`.`PLAYER_ID` WHERE `sb_s`.`ROLLBACK_STATUS` = 3 AND `sb_s`.`STATUS` NOT IN(8, 11) AND `sb_s`.`CASH_OUT_STATUS` != 3 AND sb_s.CREATED_ON BETWEEN '2023-06-26 00:00:00' AND '2023-06-26 23:59:59' AND `sb_s`.`BET_TYPE` = 2 AND `sb_s`.`REFERENCE_NO` = '12456ghf' AND `sbr`.`BET_STATUS` > 1 AND `sbr`.`STARTING_ORDER` = 1 GROUP BY `sbr`.`STARTING_ORDER`;
ERROR 1160 (08S01): Got an error writing communication packets

Suggested fix:
Fix for federate tables , should not get this error and the data in select query from federate table should fetch properly

ERROR 1160 (08S01): Got an error writing communication packets

HI Mr. T,

Thank you for your bug report.

However, this is not MySQL bug.

What you have got is a typical TCP/IP problem in the communications. Your Linux administrative logs should show you what was the problem with the network.

Also, FEDERATED tables are no longer maintained, so there is no more bug fixing for them. Although, your report is not a bug in that storage engine.

Unsupported.

what is alternative for federate engine, if you meant it is not supported and server logs doesn't have any error logs and only the mysql is reporting this communication packet error

Hi,

The alternative is to use replication. A simple one-to-one replication.

Regarding this report, it has nothing to do with Federated engine. It is a network problem. Could be a problem in cabling, hubs, switches or in the OS (TCP/IP).

Hi, 

For replication we need 2 db servers, iam asking solution for single server 2 Databases where the datas from one db to replicate in another like federate tables and further this federate tables were working fine in the same server having mysql 5.7 only after upgrade to 8.0.28 we are facing this communication packet issue randomly

Hi,

Let us remind you that this is not a site for free support, but a forum for the reports with fully repeatable test cases. You have not provided a test case, that would be repeatable on every run. This is a pre-requisite for us to consider a report, at all.

You have reported the error that OS returns to MySQL server. Hence, this is not our problem.

Regarding the replacement, there are couple, but the easiest one is to run the DMLs for each of the schemas.

Unsupported.

Hi we are facing this error only on mysql 8.0.28. This issue is happening in repeated test case where whenever we use query to select records from federate tables. And if it is related to OS error, why the same server in mysql 5.7 doesn't occur. can you please check the bug in federate tables in Mysql 8.0 version and make a fix

Hi,

In order to verify a bug report we need a test case that will ALWAYS result in the error reported.

We can not process this report with a fully repeatable test case.

You have not provided one in this report.

Hi,

SELECT sum(sbr.TOTAL_WIN_AMOUNT+sbr.refund) as multi_bet FROM `sports_bet` as `sb_s` RIGHT JOIN `sports_bet_rollback` `sbr` ON `sb_s`.`SPORTS_BET_ID` = `sbr`.`SPORTS_BET_ID` JOIN `player` `p` ON `p`.`PLAYER_ID` = `sb_s`.`PLAYER_ID` WHERE `sb_s`.`ROLLBACK_STATUS` = 3 AND `sb_s`.`STATUS` NOT IN(8, 11) AND `sb_s`.`CASH_OUT_STATUS` != 3 AND sb_s.CREATED_ON BETWEEN '2023-06-26 00:00:00' AND '2023-06-26 23:59:59' AND `sb_s`.`BET_TYPE` = 2 AND `sb_s`.`REFERENCE_NO` = '12456ghf' AND `sbr`.`BET_STATUS` > 1 AND `sbr`.`STARTING_ORDER` = 1 GROUP BY `sbr`.`STARTING_ORDER`;

ERROR 1160 (08S01): Got an error writing communication packets

Just see the above query it is just a select from federate table, whenever we use select queries on federate table it throws the above error in random manner.  By enabling federate in my.cnf of mysql and if you access a data from federate table it will throw this error. i hope This test case is proper and clear. furthermore what else you expect to be a testcase

Hi Mr. T,

We have run a similar query like yours on a big federated table and got no errors what so ever.

We have had some bug reports where a query took long time on that storage engine, when this error was returned, but we were never able to repeat it.

This is possibly just a query that took too long time to execute and returned a wrong error message.

Can't repeat.

Hi,

But in my case i was having only 100 records in the table and the query was alo not running for a long time. And as i have already informed it is ocurring on random manner and not everytime. so when this queries are used in PHP they are getting this in error log and due to this datas are not fetched properly and shown in frontend

Hi,

In order to verify a bug report we need a test case that will ALWAYS
result in the error reported.

We can not process this report with a fully repeatable test case.

You have not provided one in this report.

Hi the error occurring in random manner , but for sure it is occurring in the mysql 8 servers  and atleast give an alternate functionality for the concept of federate in Mysql 8

Error number: 1156; Symbol: ER_NET_PACKETS_OUT_OF_ORDER; SQLSTATE: 08S01
Message: Got packets out of order

How to resolve this issue

Any update on it?

Hi,

Resolution of the problem is easy.

Just find the error in your networking hardware, software and OS tuning.