[MDEV-20845] Connect with MySQL table type times out when results don't start within 40 seconds Created: 2019-10-16  Updated: 2019-11-16  Resolved: 2019-10-31

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.3.18
Fix Version/s: 10.2.28, 10.1.42, 10.3.19, 10.4.9

Type: Bug Priority: Major
Reporter: Matthew Collins Assignee: Olivier Bertrand
Resolution: Fixed Votes: 0
Labels: None
Environment:

CentOS7


 Description   

I've defined a Connect table as the mysql type with a srcdef that is a group by statement that takes about 2 minutes to start returning rows. Here is an abbreviated example:

CREATE TABLE `thingsGroupedByDay` (
 
  `ownerID` bigint(20) DEFAULT NULL,
 
  `createDate` date DEFAULT NULL,
 
  `count` decimal(23,0) DEFAULT NULL
 
) ENGINE=CONNECT DEFAULT CHARSET=utf8mb4 
CONNECTION='mysql://user:pass@my.host/my_schema' 
`TABLE_TYPE`=MYSQL `HUGE`=1 `SRCDEF`='
 
SELECT
 
  ownerID,
 
  DATE(createTimestamp) AS createDate,
 
  count(*) AS count
 
FROM things
 
GROUP BY
 
  ownerID,
 
  createDate
 
';

When I try selecting anything from the connect table, after 40 second in my client I get:

`MySQL said: Got timeout reading communication packets`

I've set the net_read_timeout system variable in my.cnf to 3600 with no effect. I've also set OS and my.cnf level TCP keepalive values.

Tables with srcdefs that take a long time to complete but start returning rows immediately work great.

I found in the source myconn.cpp file in the MYSQLC::Open function a line that appears to fix the connection read timeout to 20 seconds:

uint        cto = 10, nrt = 20;
 
...
 
mysql_options(m_DB, MYSQL_OPT_READ_TIMEOUT, &nrt);

(https://github.com/MariaDB/server/blob/22b645ef5292387871b12b26fd550eed6e57aa2d/storage/connect/myconn.cpp#L492)

Is this read timeout in the Connect mysql client what is causing this issue? Is there a reason it was hard coded to 20 seconds instead of, if I'm following the code, being allow to use the net_read_timeout system variable?

 Comments   
Comment by Matthew Collins [ 2019-10-21 ]

I rebuilt MariaDB with the `mysql_options(m_DB, MYSQL_OPT_READ_TIMEOUT, &nrt);` line commented out and it now respects the net_read_timeout variable. So far it's happy to wait 15-20 minutes for the first rows to come back.

Is there a reason why this was originally set to a fixed value?

Comment by Olivier Bertrand [ 2019-10-28 ]

Hmmm... that was probably copied from some legacy code. Besides, all three lines at this address could probably be commented out.

Alternatively, the cto and nrt values could be made parameters of the CREATE TABLE statement. What do you think would be the best solution?

Comment by Matthew Collins [ 2019-10-30 ]

I personally don't have a use case for the Connect connection timeouts to be different from the rest of the places that net_read_timeout etc are used. I think it is definitely best to remove the fixed nrt and cto values so the behavior here is consistent with everything else.

If you wanted to add new flags to allow overriding the net_*_timeout system variables in the CREATE TABLE, I could see how that might be useful. However, it looks like no one's had my issue with the current code's timeout in the last few years so I don't know how much use it would get.

Comment by Olivier Bertrand [ 2019-10-30 ]

Ok, I'll just comment them out. BTW this is how FEDERATEDX works.

Comment by Olivier Bertrand [ 2019-10-31 ]

Update fixed version numbers

Generated at Thu Feb 08 09:02:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.