Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-20845

Connect with MySQL table type times out when results don't start within 40 seconds

    XMLWordPrintable

    Details

      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?

        Attachments

          Activity

            People

            Assignee:
            bertrandop Olivier Bertrand
            Reporter:
            matthew.collins Matthew Collins
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: