Details
-
Task
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Done
-
None
Description
Overview
MariaDB allows storage engines to define additional attributes per index, field, or table. However, per-table attributes cannot be specified per partition. We should support per-partition attributes for more flexible configurations. Also, this functionality is necessary for other important features (see the issue links below). https://mariadb.com/kb/en/engine-defined-new-tablefieldindex-attributes/
Syntax
partition_definition:
|
PARTITION partition_name
|
[VALUES {LESS THAN {(expr) | MAXVALUE} | IN (value_list)}]
|
[[STORAGE] ENGINE [=] engine_name]
|
[engine_defined_options ]
|
...
|
[(subpartition_definition [, subpartition_definition] ...)]
|
https://mariadb.com/kb/en/create-table/#partitions
Example
Here is a possible use case of the per-partition attributes support. By the use of per-partition attributes, the Spider storage engine (and other federated engines) can provide users with a cleaner way to specify where to connect.
CREATE TABLE tbl_a ( |
a INT, |
b VARCHAR(255), |
PRIMARY KEY(a) |
) ENGINE=Spider PARTITION BY RANGE(a) ( |
PARTITION pt1 VALUES LESS THAN (100) REMOTE_SERVER="s_2_1" REMOTE_TABLE="ta_r1", |
PARTITION pt2 VALUES LESS THAN MAXVALUE REMOTE_SERVER="s_2_1", REMOTE_TABLE="ta_r2" |
)
|
Note that the example above doesn't work with the current server implementation.
Description
- If an engine-defined attribute is specified at table-level, it applies to all the partitions in the table (backward compatible behavior).
- Specifying an engine-defined attribute at table-level is only possible when all the storage engines composing a partitioned table support this attribute. Currently, it is not possible to use multiple storage engines to create a partitioned table, but this will be possible in MDEV-22168.
- One can use both per-table attributes and per-partition attributes for a single table. If the same attribute is specified both at table-level and partition-level, the per-partition attribute takes precedence. So, we can consider per-table attributes as default values.
- We do not support, at least in the present issue, engine-defined attributes per subpartitions.
TODO
Modify the parser to accept per-partition attributes;- Keep per-partition attributes some in-memory data structure (class partition_element ?);
- Pass per-partition attributes to ha_partition::create() as per-table attributes;
- This is to keep the handler mostly oblivious whether it's a partition or a table.
- The partition storage engine has a hook point to modify TABLE_SHARE. We can also set up per-table attributes there. https://github.com/MariaDB/server/blob/mariadb-10.7.1/sql/ha_partition.cc#L2722
- Per-table attributes and per-partition attributes should be merged before table creation.
- Persist per-partition attributes in FRM files;
-
Modify the SHOW function to handle per-partition attributes.(unnecessary) -
Make it work with subpartitions - Add test cases (subpartitioning, alter table)
- Fix CONNECT tests failures: https://buildbot.mariadb.org/#/builders/236/builds/3094
- Refactor the entire patch.
Attachments
Issue Links
- causes
-
MDEV-27604 Values of partition-level options are not validated
-
- Open
-
-
-
- Closed
-
-
-
- Open
-
- duplicates
-
-
- Closed
-
-
-
- Closed
-
- is part of
-
MDEV-22168 Supporting multiple engines with table partitioning
-
- Stalled
-
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Open
-
-
-
- Closed
-
-
-
- Open
-
-
-
- Confirmed
-
-
-
- Open
-
Activity
frm does not store attributes per partition and in memory data structures have only one list of attributes per table.
it's expected to be a pretty intrusive internal refactoring. If it'll turn out to be something smaller and safer we can backport it to earlier versions.
ralf.gebhardt@mariadb.com and serg. In MariaDB, there seem to be two types of table options, one is engine-specific and the other is non-engine-specific. And, they seemed to be treated differently. For example, PAGE_COMPRESSED is an InnoDB specific option and STATS_AUTO_RECALC is a non-engine specific option (while STATS_AUTO_RECALC seems to be used only by InnoDB).
Can I limit the scope of the issue to the engine-specific options? Of course, we may need to make similar enhancements to the non-engine-specific options, but I would like to make it by a separate task.
Yes, this is only about engine-defined attributes, those that are not hard-coded in the server, but declared by the engine using the dedicated API
Just note that we also want to implement MDEV-22168 and that whatever is done in this MDEV should of course support what will be done in that MDEV.
serg, ralf.gebhardt@mariadb.com I wrote out some details of the per-partition attribute. Please comment if you have any concerns or if there is anything that needs to be fixed.
holyfoot Please review: https://github.com/MariaDB/server/commit/473ece969dbba474d2aa146466e66238bb373d28
holyfoot I slightly updated my patch: https://github.com/MariaDB/server/commit/53fb89c94154ff8879af7a0c1dc8e41bb98164a3
holyfoot Thank you for your review. I update my patch according to your comments. Please review the updated version: https://github.com/MariaDB/server/commit/88e445850b90db963704076b750bea0b26e555c8
Summary of the changes:
- Duplications in sql_yacc.yy are removed;
- The patch is formated by clang-format-diff;
- The call of link() is removed from the engine_option_value constructions;
- The error handling for the new operator on engine_option_value is added;
- Some functions are renamed for consistency;
- opt_ prefix of some expressions in sql_yacc.yy are revoed for consistency.
What has not yet been fixed:
- ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ts1, |
+ ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' |
The diff above seems to be due to the extension of the syntax.
An engine-defined option name can be an arbitrary identity and thus the parser is not able to determine that it is a syntax error until it reads a later token. The function parse_error() just print the last token parsed. So, "t1" is dropped from the error message.
IMHO, the error message looks still informative.
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' |
partition x2 tablespace ts2,
|
partition x3 tablespace ts3)' at line 8 |
According to the private communication with holyfoot, I slightly fix my patch (redundant err variable in add_engine_part_options() is removed): https://github.com/MariaDB/server/commit/afb81948ac34f37469d43154892cd3b8753bace6
Note that mixing of SE's is not implemented yet (planned in MDEV-22168):
|
10.8.0 959a6a723f6b6fb6d9630380c2317b8d51f9e4d8 (Debug) |
10.8.0-dbg>CREATE TABLE t1 (a INT,b VARCHAR(255),PRIMARY KEY(a)) ENGINE=MyISAM PARTITION BY RANGE(a) (
|
-> PARTITION pt1 VALUES LESS THAN (100) ENGINE=InnoDB,
|
-> PARTITION pt2 VALUES LESS THAN MAXVALUE ENGINE=InnoDB);
|
ERROR 1497 (HY000): The mix of handlers in the partitions is not allowed in this version of MariaDB
|
Logged MDEV-27545 Feature request: migrate server attributes applicable to engines to the storage engine(s)
Logged MDEV-27564 Clearly specify/indicate in the partitioning manual which per-partition options are available
OK to push. However, I am still running a generic crash test against the feature branch. Results from that will be available around next week Wednesday.
There are more problems withe the PARTITION engine, for instances:
If the original table has a CONNECTION string, it is lost when calling the partition tables
Query pruning can loose existing data (
MDEV-6322)These issues need to be handled.