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

Strange behavior when adding partitions to Json type table (Connect engine)

Details

    Description

      Can not create a table, when adding partition on JSON type table (Connect engine), got en error "Unknown option 'FIELD_FORMAT' ".

      --------------
      create table t1 (
        `address_street` varchar(150) NOT NULL `FIELD_FORMAT`='address:street',
        `address_zipcode` varchar(150) NOT NULL `FIELD_FORMAT`='address:zipcode',
        `cuisine` varchar(150) NOT NULL,
        `name` varchar(150) NOT NULL,
        `restaurant_id` bigint NOT NULL)
      engine=CONNECT HUGE=1 table_type=JSON file_name='/t/qq.json' 
      lrecl=128 option_list='pretty=0' ending=1
      PARTITION BY RANGE (restaurant_id) (
      PARTITION p0 VALUES LESS THAN (3),
      PARTITION p1 VALUES LESS THAN MAXVALUE)
      --------------
       
      ERROR 1911 (HY000) at line 4 in file: '/t/a.sql': Unknown option 'FIELD_FORMAT'
      --------------
      

      While the same, but with alter table works, but data is presented 2 times (if I add 3 partitions, then 3 times) :

      --------------
      create table t1 (
        `address_street` varchar(150) NOT NULL `FIELD_FORMAT`='address:street',
        `address_zipcode` varchar(150) NOT NULL `FIELD_FORMAT`='address:zipcode',
        `cuisine` varchar(150) NOT NULL,
        `name` varchar(150) NOT NULL,
        `restaurant_id` bigint NOT NULL)
      engine=CONNECT HUGE=1 table_type=JSON file_name='/t/qq.json' 
      lrecl=128 option_list='pretty=0' ending=1
      --------------
      Query OK, 0 rows affected (0.06 sec)
       
      --------------
      select * from t1
      --------------
      +-----------------+-----------------+---------+-------------+---------------+
      | address_street  | address_zipcode | cuisine | name        | restaurant_id |
      +-----------------+-----------------+---------+-------------+---------------+
      | m               | 10462           |         | Bake Shop 1 |             1 |
      | e               | 10463           |         | Bake Shop 2 |             2 |
      | t               | 10464           |         | Bake Shop 3 |             3 |
      | w               | 10465           |         | Bake Shop 4 |             4 |
      | m               | 10466           |         | Bake Shop 5 |             5 |
      | t               | 10467           |         | Bake Shop 6 |             6 |
      +-----------------+-----------------+---------+-------------+---------------+
      6 rows in set (0.00 sec)
      --------------
      ALTER TABLE t1
      PARTITION BY RANGE (restaurant_id) (
      PARTITION p0 VALUES LESS THAN (3),
      PARTITION p1 VALUES LESS THAN MAXVALUE)
      --------------
       
      Query OK, 0 rows affected, 2 warnings (0.02 sec)
      Records: 0  Duplicates: 0  Warnings: 2
      Warning (Code 1105): Data repartition in p0 is unchecked
      Warning (Code 1105): Data repartition in p1 is unchecked
      --------------
      select * from t1
      --------------
      +-----------------+-----------------+---------+-------------+---------------+
      | address_street  | address_zipcode | cuisine | name        | restaurant_id |
      +-----------------+-----------------+---------+-------------+---------------+
      | m               | 10462           |         | Bake Shop 1 |             1 |
      | e               | 10463           |         | Bake Shop 2 |             2 |
      | t               | 10464           |         | Bake Shop 3 |             3 |
      | w               | 10465           |         | Bake Shop 4 |             4 |
      | m               | 10466           |         | Bake Shop 5 |             5 |
      | t               | 10467           |         | Bake Shop 6 |             6 |
      | m               | 10462           |         | Bake Shop 1 |             1 |
      | e               | 10463           |         | Bake Shop 2 |             2 |
      | t               | 10464           |         | Bake Shop 3 |             3 |
      | w               | 10465           |         | Bake Shop 4 |             4 |
      | m               | 10466           |         | Bake Shop 5 |             5 |
      | t               | 10467           |         | Bake Shop 6 |             6 |
      +-----------------+-----------------+---------+-------------+---------------+
      12 rows in set (0.00 sec)
      

      json file

      {"address": {"street": "m", "zipcode": "10462"}, "name": "Bake Shop 1", "restaurant_id": "1"}
      {"address": {"street": "e", "zipcode": "10463"}, "name": "Bake Shop 2", "restaurant_id": "2"}
      {"address": {"street": "t", "zipcode": "10464"}, "name": "Bake Shop 3", "restaurant_id": "3"}
      {"address": {"street": "w", "zipcode": "10465"}, "name": "Bake Shop 4", "restaurant_id": "4"}
      {"address": {"street": "m", "zipcode": "10466"}, "name": "Bake Shop 5", "restaurant_id": "5"}
      {"address": {"street": "t", "zipcode": "10467"}, "name": "Bake Shop 6", "restaurant_id": "6"}
      

      Attachments

        Activity

          Here again there is a documentation problem. The original doc I gave to MariaDB contains a paragraph saying:

          Partition engine issues
          Using partitions sometimes requires creating the tables in an unnatural way to avoid some error due to several partition engine bugs:

          1. Engine specific column and index options are not recognized and cause a syntax error when the table is created. The workaround is to create the table in two steps, a CREATE TABLE statement followed by an ALTER TABLE statement.
          2. The connection string, when specified for the table, is lost by the partition engine. The workaround is to specify the connection string in the OPTION_LIST.
          3. MySQL upstream bug #71095. In case of list columns partitioning it sometimes causes a false “impossible where” clause to be raised. This makes a wrong void result returned when it should not be void. There is no workaround but this bug should be hopefully fixed.

          However this seems to be missing on https://mariadb.com/kb/en/mariadb/using-connect-partitioning-and-sharding/

          Your other problem is apparently comming from a wrong table definition. The FILE_NAME should contain a wildcard (%s) entry enabling to create or use different files for the partitions. With no wildcard, all partitions use the same file.

          bertrandop Olivier Bertrand added a comment - Here again there is a documentation problem. The original doc I gave to MariaDB contains a paragraph saying: Partition engine issues Using partitions sometimes requires creating the tables in an unnatural way to avoid some error due to several partition engine bugs: 1. Engine specific column and index options are not recognized and cause a syntax error when the table is created. The workaround is to create the table in two steps, a CREATE TABLE statement followed by an ALTER TABLE statement. 2. The connection string, when specified for the table, is lost by the partition engine. The workaround is to specify the connection string in the OPTION_LIST. 3. MySQL upstream bug #71095. In case of list columns partitioning it sometimes causes a false “impossible where” clause to be raised. This makes a wrong void result returned when it should not be void. There is no workaround but this bug should be hopefully fixed. However this seems to be missing on https://mariadb.com/kb/en/mariadb/using-connect-partitioning-and-sharding/ Your other problem is apparently comming from a wrong table definition. The FILE_NAME should contain a wildcard (%s) entry enabling to create or use different files for the partitions. With no wildcard, all partitions use the same file.
          greenman Ian Gilfillan added a comment - This text has been added to the documentation: https://mariadb.com/kb/en/mariadb/using-connect-partitioning-and-sharding/

          People

            bertrandop Olivier Bertrand
            alice Alice Sherepa
            Votes:
            0 Vote for this issue
            Watchers:
            4 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.