That's right. Consider the following example (it's more or less the same as the test in MDEV-4452 does, but I'm intentionally convert it into a manual test with two separate servers, to make it more obvious.
On server 1 (remote server): create a remote table
create table t_remote (a int primary key, b varchar(8));
|
On server 2 (local server): create a federated table:
create table t_local (a int primary key, b varchar(8))
|
engine=federated connection='mysql://root@127.0.0.1:3307/test/t_remote';
|
So far you've just created tables, but did not use them, so there is no connection between the local and remote server. Check it by running show processlist on the server 1 (remote):
MariaDB [test]> show processlist;
|
+----+------+-----------------+------+---------+------+-------+------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+------+-----------------+------+---------+------+-------+------------------+----------+
|
| 3 | root | localhost:44325 | test | Query | 0 | init | show processlist | 0.000 |
|
+----+------+-----------------+------+---------+------+-------+------------------+----------+
|
1 row in set (0.00 sec)
|
Now, set the timeout to a small value on the server 1 (the remote server):
set global wait_timeout = 20;
|
Select from the federated table on the local server:
MariaDB [test]> select * from t_local;
|
Empty set (0.00 sec)
|
While 20 seconds have not passed, check the processlist on the remote server:
MariaDB [test]> show processlist;
|
+----+------+-----------------+------+---------+------+-------+------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+------+-----------------+------+---------+------+-------+------------------+----------+
|
| 3 | root | localhost:44325 | test | Query | 0 | init | show processlist | 0.000 |
|
| 7 | root | localhost:55683 | test | Sleep | 3 | | NULL | 0.000 |
|
+----+------+-----------------+------+---------+------+-------+------------------+----------+
|
2 rows in set (0.00 sec)
|
As you can see, we now have another connection, it's the connection from Federated.
If you keep running SHOW PROCESSLIST, you'll see how the Time value grows:
MariaDB [test]> show processlist;
|
+----+------+-----------------+------+---------+------+-------+------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+------+-----------------+------+---------+------+-------+------------------+----------+
|
| 3 | root | localhost:44325 | test | Query | 0 | init | show processlist | 0.000 |
|
| 7 | root | localhost:55683 | test | Sleep | 15 | | NULL | 0.000 |
|
+----+------+-----------------+------+---------+------+-------+------------------+----------+
|
2 rows in set (0.00 sec)
|
But as soon as it exceeds the configured 20 seconds, the connection will disappear:
MariaDB [test]> show processlist;
|
+----+------+-----------------+------+---------+------+-------+------------------+----------+
|
| Id | User | Host | db | Command | Time | State | Info | Progress |
|
+----+------+-----------------+------+---------+------+-------+------------------+----------+
|
| 3 | root | localhost:44325 | test | Query | 0 | init | show processlist | 0.000 |
|
+----+------+-----------------+------+---------+------+-------+------------------+----------+
|
1 row in set (0.00 sec)
|
And now, if you run the query on the local server again, you'll get an error. It can be either "server has gone away" or "error reading communication packets" – all the same, it will fail:
MariaDB [test]> select * from t_local;
|
ERROR 1158 (08S01): Got an error reading communication packets
|
That's what bug MDEV-4452 is about.
If you re-run the query, it will re-establish the connection, and the timeout will start count-down again.
Absolute values does not matter. Even if you have the timeout 28800, but you only run the query once a day, you'll get the same problem.
Try to set the timeout to more than a day and see if the problem goes away. Remember to set the GLOBAL value of wait_timeout.
Bug
Major
That's right. Consider the following example (it's more or less the same as the test in
MDEV-4452does, but I'm intentionally convert it into a manual test with two separate servers, to make it more obvious.On server 1 (remote server): create a remote table
On server 2 (local server): create a federated table:
So far you've just created tables, but did not use them, so there is no connection between the local and remote server. Check it by running show processlist on the server 1 (remote):
MariaDB [test]> show processlist;| 3 | root | localhost:44325 | test | Query | 0 | init | show processlist | 0.000 |Now, set the timeout to a small value on the server 1 (the remote server):
Select from the federated table on the local server:
While 20 seconds have not passed, check the processlist on the remote server:
MariaDB [test]> show processlist;| 3 | root | localhost:44325 | test | Query | 0 | init | show processlist | 0.000 |As you can see, we now have another connection, it's the connection from Federated.
If you keep running SHOW PROCESSLIST, you'll see how the Time value grows:
MariaDB [test]> show processlist;| 3 | root | localhost:44325 | test | Query | 0 | init | show processlist | 0.000 |But as soon as it exceeds the configured 20 seconds, the connection will disappear:
MariaDB [test]> show processlist;| 3 | root | localhost:44325 | test | Query | 0 | init | show processlist | 0.000 |And now, if you run the query on the local server again, you'll get an error. It can be either "server has gone away" or "error reading communication packets" – all the same, it will fail:
ERROR 1158 (08S01): Got an error reading communication packetsThat's what bug
MDEV-4452is about.If you re-run the query, it will re-establish the connection, and the timeout will start count-down again.
Absolute values does not matter. Even if you have the timeout 28800, but you only run the query once a day, you'll get the same problem.
Try to set the timeout to more than a day and see if the problem goes away. Remember to set the GLOBAL value of wait_timeout.