patternsqlMinor
MySQL Got timeout reading communication packets when reading federated table
Viewed 0 times
gotreadingpacketsfederatedcommunicationtimeoutmysqlwhentable
Problem
I have two servers (solomon and serenity).
Both servers are Windows Server (2012 & 2016), IIS, PHP (5.6.31), MySQL (5.5).
On solomon I have several federated tables set up that point to tables on serenity. And I have a user set up on solomon specifically for connecting to these federated tables.
Accounts are split over the two servers. Half my user accounts are on solomon, half on serenity. However, the login page for all users is on serenity. This is so that the serenity server can check to see which server this user should be directed to, it then redirects to the password page on either solomon or serenity. So in this way, all users go through serenity, but not all users stay there.
However, there are a few other scripts running on solomon that do check these federated tables on serenity, so even though the user has passed through to solomon, the site is still querying these federated tables and thereby hitting serenity.
As far as I know that shouldn't be a problem, but on serenity I'm getting lots of warnings in mysqlerror.log like this: (username, dbname and ip address obfuscated)
On solomon I don't get these warnings. Also 95% of these warnings mention the federated user, even though 99% of my queries are done using a different user. So that's why I suspect it's an issue with the federated tables / user.
I wouldn't be too worried about it except that I've been having lots of problems on serenity and I suspect this might be related. Today my database just crashed and I had to reboot the server. Lots of angry customers.
Other Symptoms
For the last few months I've been running into what appears to be concurrent connection limits, as I have several scripts that loop through many rows of data (typically between 50 and 200) and use ajax to call a send email script for eac
Both servers are Windows Server (2012 & 2016), IIS, PHP (5.6.31), MySQL (5.5).
On solomon I have several federated tables set up that point to tables on serenity. And I have a user set up on solomon specifically for connecting to these federated tables.
Accounts are split over the two servers. Half my user accounts are on solomon, half on serenity. However, the login page for all users is on serenity. This is so that the serenity server can check to see which server this user should be directed to, it then redirects to the password page on either solomon or serenity. So in this way, all users go through serenity, but not all users stay there.
However, there are a few other scripts running on solomon that do check these federated tables on serenity, so even though the user has passed through to solomon, the site is still querying these federated tables and thereby hitting serenity.
As far as I know that shouldn't be a problem, but on serenity I'm getting lots of warnings in mysqlerror.log like this: (username, dbname and ip address obfuscated)
180430 16:21:00 [Warning] Aborted connection 24784 to db: 'dbname_blah' user: 'federated_user_blah' host: 'xxx.xxx.xx.xx' (Got timeout reading communication packets)On solomon I don't get these warnings. Also 95% of these warnings mention the federated user, even though 99% of my queries are done using a different user. So that's why I suspect it's an issue with the federated tables / user.
I wouldn't be too worried about it except that I've been having lots of problems on serenity and I suspect this might be related. Today my database just crashed and I had to reboot the server. Lots of angry customers.
Other Symptoms
For the last few months I've been running into what appears to be concurrent connection limits, as I have several scripts that loop through many rows of data (typically between 50 and 200) and use ajax to call a send email script for eac
Solution
I realize now that this error occurs when idle threads are killed by wait_timeout. In other words, if wait_timeout is set to 40 and idle threads stick around up to 40 seconds, as soon as they hit 40 seconds and get killed off the error is logged: “got timeout reading communication packets”.
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. So when a query is executed on a federated table, the connection it creates on the other server does not get killed by mysql_close(). (Which is why I have so many idle connections, and why they're only getting 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. So when a query is executed on a federated table, the connection it creates on the other server does not get killed by mysql_close(). (Which is why I have so many idle connections, and why they're only getting killed by wait_timeout)
Context
StackExchange Database Administrators Q#205398, answer score: 6
Revisions (0)
No revisions yet.