1

I have one problem with FEDERATED table in MySQL. I have one server (MySQL version 5.0.51a), who serve to store client data and actually nothing more. The logic database are stored in another server (version 5.1.56), sometimes it should handle that data from first server. So the second server have one FEDERATED table, which connect to the first server.

Actually, it has worked without any problems, but recently I got strange errors with this solution. Some kind of queries on second server cannot be performed correctly.

For example SELECT * FROM table - doesn't work. It hangs exactly 3 minutes and then gives:

Error Code: 1159 Got timeout reading communication packets

Ok, I checked table on the first server and it's Ok. Then I tried some another queries to FEDERATED table and they work...

For example, query like SELECT * FROM table WHERE id=x returns the result. Probably it could have problem with size of result, so I tried query with dummy WHERE-clause like SELECT * FROM table WHERE id > 0 - and it also works...

Finally I found a "solution", which helped only for two days - on the first server I made a copy of table, and on second server I re-declared a new FEDERATED table with new connection string to this copy. And it works, but after two days the same problem with new copied table.

I've already talk with both server providers, they see no problems, everything seems to work and other hosting provider is the causer of problems.

I've checked all variables in MySQL and there is no timeout parameter with 3 minutes etc. So how can I deal so kind of problems? It seems to be something automatic on network or database side, but I don't know, how to detect the reason of problems.

Do You have any ideas?

cheesemacfly
  • 11,622
  • 11
  • 53
  • 72
eurocups
  • 51
  • 1
  • 4
  • I'm having a similar problem. I know this was a long time ago now, but did you ever discover the cause of this? – Vincent Apr 27 '18 at 23:47

2 Answers2

0

You may try checking MTU size settings for network interfaces on both servers.

offlaner
  • 1
0

This warning is logged when idle threads are killed by wait_timeout.

Normally, the way to avoid threads getting killed by wait_timeout is to call mysql_close() in scripts when the connection is no longer needed. Unfortunately that doesn't work for queries made through federated tables because the query and the connection are not on the same server.

For example, when a query is executed on server A of a federated table (pointing to data on server B), it creates a connection on server B. Then when you run mysql_close() on server A it obviously can not close the connection that was created on server B.

Eventually the connection gets killed by mysql after the number of seconds specified in "wait_timeout" have passed (the default is 8 hours). This generates the warning in your mysqlerror.log "Got timeout reading communication packets"

Vincent
  • 1,741
  • 23
  • 35