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

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

          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?

          matthew.collins Matthew Collins added a comment - 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?

          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?

          bertrandop Olivier Bertrand added a comment - 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?

          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.

          matthew.collins Matthew Collins added a comment - 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.

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

          bertrandop Olivier Bertrand added a comment - Ok, I'll just comment them out. BTW this is how FEDERATEDX works.

          Update fixed version numbers

          bertrandop Olivier Bertrand added a comment - Update fixed version numbers

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.