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

Spider: specify connection to data node by engine-defined attributes

Details

    Description

      The Spider storage engine provides two ways to specify remote data nodes. One is by the COMMENT clause and the other is by the CONNECTION clause (see below links for existing options). However, writing every specification in a single connection string is not very user-friendly.

      We introduce new engine-defined attributes to specify remote data nodes. The engine attributes do not cover all the existing DSN parameters because most of the parameters need not be specified at the table level. So, we first introduce engine-defined attributes that correspond to essential parameters. If users complain that a parameter they need is missing, we can introduce it later.

      I believe that the following attributes are enough:

      engine-defined attribute corresponding DNS parameter
      REMOTE_SERVER srv
      REMOTE_DATABASE database
      REMOTE_TABLE tbl

      Writing a raw password in the COMMENT or engine-defined option is not preferable from the viewpoint of security because the password might be logged in slow log or general log. Therefore, we should encourage the use of the CREATE SERVER statement, and not introduce attributes such as REMOTE_USER and REMOTE_PASSWORD.

      SSL-related parameters might be essential but I believe that they should be covered by CREATE SERVER statement rather than per-table or per-partition attributes.

      If, for example, both COMMENT='table "t1"' and REMOTE_TABLE="t2" are specified for a single table or a single partition, the Spider returns an error.

      Example:

      CREATE TABLE `users` (
      		 
          `id` INT,
      		 
          `name` VARCHAR(255)
      		 
      ) ENGINE=SPIDER REMOTE_TABLE="users"
      		 
      PARTITION BY HASH(id)
      		 
      (
      		 
          PARTITION `pt1` REMOTE_SERVER="s1" REMOTE_DATABASE="db1",
      		 
          PARTITION `pt2` REMOTE_SERVER="s1" REMOTE_DATABASE="db2",
      		 
          PARTITION `pt3` REMOTE_SERVER="s2" REMOTE_DATABASE="db1"
      		 
      );

      TODO:

      • Introduce engine-defined attributes described above;
      • Make Spider to use the attributes;
      • Write comprehensive test cases;
      • Refactor entire patch.

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27521 SIGSEGV in spider_parse_connect_info in MDEV-27106 branch

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27676 Assertion `str.alloced_length() >= str.length() + data_len' failed in spider_string::q_append

          • Major - Major loss of function.
          • Closed
          is part of

          Task - A task that needs to be done. MDEV-27373 Q1 2022 release merge

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Task - A task that needs to be done. MDEV-27169 Change default values of Spider plugin variables to default values of table variables

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-27211 Spider: deprecate table option specification by COMMENT

          • Major - Major loss of function.
          • Closed

          Task - A task that needs to be done. MDEV-28856 Spider: Implement more engine-defined options

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-28861 Spider: Deprecate table options by COMMENT

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Task - A task that needs to be done. MDEV-5271 Support engine-defined attributes per partition

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-22541 CREATE SERVER does not treat SOCKET as mutually exclusive from HOST and PORT

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27400 Spider attempts localhost TCP/IP connection, even when none is specified.

          • Major - Major loss of function.
          • Confirmed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-27564 Clearly specify/indicate in the partitioning manual which per-partition options are available

          • Critical - Crashes, loss of data, severe memory leak.
          • Confirmed

          Task - A task that needs to be done. MDEV-31146 Spider: Remove table options by COMMENT

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-33925 Add TLS-related options for CREATE SERVER

          • Major - Major loss of function.
          • Open

          Activity

            Ascending order - Click to sort in descending order
            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment -

            Please review. The patch depends on one for MDEV-5271.
            https://github.com/MariaDB/server/commit/ae9e72ca06d95947e44ef0801c94fbac6882b38e

            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment - Please review. The patch depends on one for MDEV-5271 . https://github.com/MariaDB/server/commit/ae9e72ca06d95947e44ef0801c94fbac6882b38e
            holyfoot Alexey Botchkov added a comment -

            see minor comments to the patch.

            holyfoot Alexey Botchkov added a comment - see minor comments to the patch.
            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment -

            holyfoot Thank you for your review. I fixed the patch according to your comments. Please check it again. https://github.com/MariaDB/server/commit/384f9d34ff29fe81de234c48516ccf257b9b10f5

            nayuta-yanagisawa Nayuta Yanagisawa (Inactive) added a comment - holyfoot Thank you for your review. I fixed the patch according to your comments. Please check it again. https://github.com/MariaDB/server/commit/384f9d34ff29fe81de234c48516ccf257b9b10f5
            holyfoot Alexey Botchkov added a comment -

            Ok to push.

            holyfoot Alexey Botchkov added a comment - Ok to push.
            Roel Roel Van de Paar added a comment - - edited

            Logged MDEV-27400 Spider attempts localhost TCP/IP connection, even when none is specified.

            Roel Roel Van de Paar added a comment - - edited Logged MDEV-27400 Spider attempts localhost TCP/IP connection, even when none is specified.
            Roel Roel Van de Paar added a comment -

            Logged MDEV-27521 SIGSEGV in spider_parse_connect_info in MDEV-27106 branch

            Roel Roel Van de Paar added a comment - Logged MDEV-27521 SIGSEGV in spider_parse_connect_info in MDEV-27106 branch
            Roel Roel Van de Paar added a comment -

            Confirming this comment, it is interesting to note that Spider already supported MDEV-5271-like functionality (i.e. per partition engine-defined attributes) using the comment field. iow, in the comment field one could specify per-partition options even before MDEV-5271 was implemented.

            Roel Roel Van de Paar added a comment - Confirming this comment , it is interesting to note that Spider already supported MDEV-5271 -like functionality (i.e. per partition engine-defined attributes) using the comment field. iow, in the comment field one could specify per-partition options even before MDEV-5271 was implemented.
            Roel Roel Van de Paar added a comment -

            As the COMMENT field functionality was not changed in any way, and as any new SQL syntax is 10.8-only, I did not do any upgrade testing for this issue. nayuta-yanagisawa and serg If you think any should be done, please let me know your thoughts.

            Roel Roel Van de Paar added a comment - As the COMMENT field functionality was not changed in any way, and as any new SQL syntax is 10.8-only, I did not do any upgrade testing for this issue. nayuta-yanagisawa and serg If you think any should be done, please let me know your thoughts.
            Roel Roel Van de Paar added a comment -

            OK to push. However, I am still running a generic crash test against the feature branch (including the MDEV-27521 patch). Results from that will be available around next week Wednesday.

            Roel Roel Van de Paar added a comment - OK to push. However, I am still running a generic crash test against the feature branch (including the MDEV-27521 patch). Results from that will be available around next week Wednesday.
            Roel Roel Van de Paar added a comment - - edited

            From the same run, logged MDEV-27575 SIGSEGV in intern_plugin_lock on SHUTDOWN when setting Spider as default storage engine (temporary or global)

            Roel Roel Van de Paar added a comment - - edited From the same run, logged MDEV-27575 SIGSEGV in intern_plugin_lock on SHUTDOWN when setting Spider as default storage engine (temporary or global)
            Roel Roel Van de Paar added a comment - - edited

            Logged MDEV-27676 Assertion `str.alloced_length() >= str.length() + data_len' failed in spider_string::q_append

            Roel Roel Van de Paar added a comment - - edited Logged MDEV-27676 Assertion `str.alloced_length() >= str.length() + data_len' failed in spider_string::q_append
            ycp Yuchen Pei added a comment - - edited

            Hi greenman, how much work do you think is needed to document the nice feature introduced in this ticket in the KB?

            ycp Yuchen Pei added a comment - - edited Hi greenman , how much work do you think is needed to document the nice feature introduced in this ticket in the KB?
            ycp Yuchen Pei added a comment -

            > Hi Ian Gilfillan, how much work do you think is needed to document the nice feature introduced in this ticket in the KB?

            I added to the kb two weeks ago: https://mariadb.com/kb/en/spider-storage-engine-overview/#basic-usage

            ycp Yuchen Pei added a comment - > Hi Ian Gilfillan, how much work do you think is needed to document the nice feature introduced in this ticket in the KB? I added to the kb two weeks ago: https://mariadb.com/kb/en/spider-storage-engine-overview/#basic-usage

            People

              nayuta-yanagisawa Nayuta Yanagisawa (Inactive)
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              10 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.