[MDEV-13045] Strange behavior when adding partitions to Json type table (Connect engine) Created: 2017-06-09  Updated: 2017-06-27  Resolved: 2017-06-27

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Connect
Affects Version/s: 10.2.6
Fix Version/s: 10.1.25, 10.0.32

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

Ubuntu 16.04


 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"}



 Comments   
Comment by Olivier Bertrand [ 2017-06-26 ]

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.

Comment by Ian Gilfillan [ 2017-06-27 ]

This text has been added to the documentation:
https://mariadb.com/kb/en/mariadb/using-connect-partitioning-and-sharding/

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