Hi, Henrik! On May 11, Henrik Ingo wrote:

On Tue, May 11, 2010 at 12:05 PM, Sergei Golubchik wrote:

...

we've talked about engine attributes in the CREATE TABLE, and that one should be able to specify them per partition as well.

Now, thinking about it, I'm not quite sure what the semantics shuld be.

Thinking as an end user only, I would vote for a scheme where the following holds true:

i) Anything that can be specified table level, could also be specified partition level. ii) anything that can be defined for all partitions, can be instead defined on the table level part. iii) For the problem with different engines per partition, and only one engine supports XXX=1 I could argue that it is not allowed to specify it on table level. If all specified engines support XXX=1, then it is allowed to specify it on table level.

Is this helpful?

Yes, thanks. You're basically saying that there should be no attributes that are valid only on the table or only on the partition level, everything that can be specified per table should work per partition, and vice versa. It is certainly possible and logical. Regards, Sergei

Hi, Sergei! My use case is 1. ----- my use case start ----- Currently, Spider and Vertical Partitioning engine can use table level "connection", table level "comment", partition level "comment" and sub-partition level "comment" for engine options. This options have following priority. 1. sub-partition level "comment" 2. partition level "comment" 3. table level "comment" 4. table level "connection" If you create a table using following SQL, CREATE TABLE tbl_a (.....) ENGINE=spider COMMENT='database "auto_test_remote2", table "ta_r3"' CONNECTION='socket "mysql_1.sock", host "localhost"' PARTITION BY KEY(a) ( PARTITION pt1 COMMENT='database "auto_test_remote", table "ta_r2"', PARTITION pt2 COMMENT='socket "mysql_2.sock"' ); pt1 options are database "auto_test_remote" table "ta_r2" socket "mysql_1.sock" host "localhost" and pt2 options are database "auto_test_remote2" table "ta_r3" socket "mysql_2.sock" host "localhost" ----- my use case end ----- I think per-partition options should better to work like this. If per-partition options works like this, every engine don't need the special code for partitioned case. Additionally, for supporting different engines in different partitions, table options should better to be added engine names like global/session parameters for avoiding conflicting option names. ----- example start ----- If you create a table using following SQL, CREATE TABLE tbl_a (.....) innodb_XXX=1 pbxt_XXX=2 PARTITION BY KEY(a) ( PARTITION pt1 ENGINE=innodb innodb_YYY=3, PARTITION pt2 ENGINE=pbxt pbxt_ZZZ=4 ); pt1 options are innodb_XXX=1 innodb_YYY=3 and pt2 options are pbxt_XXX=2 pbxt_ZZZ=4 ----- example end ----- What do you think about my opinion? Thanks, Kentoku 2010/5/12 Michael Widenius :

Hi!

...

...

...

...

...

"Sergei" == Sergei Golubchik writes:

Sergei> Hi, Sergei> we've talked about engine attributes in the CREATE TABLE, Sergei> and that one should be able to specify them per partition as well.

Sergei> Now, thinking about it, I'm not quite sure what the semantics shuld be.

Sergei> What is your use case ? How do you want them to work ?

Sergei> I see different possibilities. Say, there is

Sergei>   create table ... (.....) XXX=1 Sergei>     partition by list (a) Sergei>     ( Sergei>        partition p0 values in (1) YYY=2, Sergei>        partition p1 values in (2) Sergei>     );

Sergei> 1. We can say that XXX should be listed in the engine's hton-> table_options, and YYY - in the hton->partition_options.

Sergei> this works fine because the engine can use table_share->option_struct Sergei> and it will contain correct values, independent from whether a table is Sergei> partitioned or not.

Sergei> but it will break when partitioning will support different engines Sergei> in different partitions.

Sergei> 2. We can say that XXX is partition engine options, and a pluggable Sergei> engine can only see YYY. YYY should come from hton->partition_options, Sergei> and the engine's table level attributes are not applicable.

Sergei> The drawback - every engine needs to have special code to take care of Sergei> the partitioned case, and to duplicate all table-level options in the Sergei> partition-level options.

Sergei> 3. Same as 2, but YYY can come from either table level or partition Sergei> level arrays. Every engine still needs the special code for partitioned Sergei> case, but does not need to duplicate the table_options array.

For now I would suggest we go with 1), as this required least code from the engine sides.

Reagards, Monty

Hi, kentoku! On May 12, kentoku wrote:

Hi, Sergei!

My use case is 1.

----- my use case start ----- If you create a table using following SQL, CREATE TABLE tbl_a (.....) ENGINE=spider COMMENT='database "auto_test_remote2", table "ta_r3"' CONNECTION='socket "mysql_1.sock", host "localhost"' PARTITION BY KEY(a) ( PARTITION pt1 COMMENT='database "auto_test_remote", table "ta_r2"', PARTITION pt2 COMMENT='socket "mysql_2.sock"' ); ----- my use case end ----- I think per-partition options should better to work like this. If per-partition options works like this, every engine don't need the special code for partitioned case.

Unfortunately, this change - per partition options - seems to be too big. I have it working (almost, there're still few bugs), but it is already quite an intrusive patch :( I'm hesitant to add it to 5.2 now, as 5.2 was supposed to contain only few safe changes, as compared to 5.1, so that it could stabilize quickly. I am going to do per partition options in 5.3

Additionally, for supporting different engines in different partitions, table options should better to be added engine names like global/session parameters for avoiding conflicting option names.

Right, we thought about it too. I don't remember why it was dropped, but luckily if it will be needed, it can be implemented any time in a completely backward compatible manner. Regards, Sergei