|
Additionally, if the table is created with at least one partition to begin with, adding a second results in just two partitions (correctly). This appears to be an edge case for when the table is created with zero partitions.
CREATE SERVER shard1 FOREIGN DATA WRAPPER mysql
|
OPTIONS(
|
HOST 'spider_shard1_1',
|
DATABASE 'test',
|
USER 'sharded',
|
PASSWORD 'sharding',
|
PORT 3306);
|
|
create or replace
|
table test.votes(
|
voter_id int,
|
vote varchar(1),
|
primary key(voter_id))
|
ENGINE=spider
|
COMMENT='wrapper "mysql", table "votes"'
|
PARTITION BY HASH (voter_id)
|
(PARTITION part1 comment = 'srv "shard1"');
|
MariaDB [(none)]> show create table test.votes;
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| votes | CREATE TABLE `votes` (
|
`voter_id` int(11) NOT NULL,
|
`vote` varchar(1) DEFAULT NULL,
|
PRIMARY KEY (`voter_id`)
|
) ENGINE=SPIDER DEFAULT CHARSET=latin1 COMMENT='wrapper "mysql", table "votes"'
|
/*!50100 PARTITION BY HASH (voter_id)
|
(PARTITION part1 COMMENT = 'srv "shard1"' ENGINE = SPIDER) */ |
|
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set, 2 warnings (10.04 sec)
|
CREATE SERVER shard2 FOREIGN DATA WRAPPER mysql
|
OPTIONS(
|
HOST 'spider_shard2_1',
|
DATABASE 'test',
|
USER 'sharded',
|
PASSWORD 'sharding',
|
PORT 3306);
|
|
alter table test.votes add partition (PARTITION part2 comment = 'srv "shard2"');
|
show create table test.votes;
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| votes | CREATE TABLE `votes` (
|
`voter_id` int(11) NOT NULL,
|
`vote` varchar(1) DEFAULT NULL,
|
PRIMARY KEY (`voter_id`)
|
) ENGINE=SPIDER DEFAULT CHARSET=latin1 COMMENT='wrapper "mysql", table "votes"'
|
/*!50100 PARTITION BY HASH (voter_id)
|
(PARTITION part1 COMMENT = 'srv "shard1"' ENGINE = SPIDER,
|
PARTITION part2 COMMENT = 'srv "shard2"' ENGINE = SPIDER) */ |
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set, 6 warnings (20.18 sec)
|
|
|
Partition p0 is not added by the ALTER statement.
When you create a new table with PARTITION clause, it is already partitioned, which means it has at least one partition, p0 if you didn't specify the partition name (it could have had more – p0, p1, ... pN if you specified the number of partitions upon creation).
What you might be trying to do is
create or replace
|
table test.votes(
|
voter_id int,
|
vote varchar(1),
|
primary key(voter_id))
|
ENGINE=spider
|
COMMENT='wrapper "mysql", table "votes"'
|
PARTITION BY HASH (voter_id)
|
(PARTITION part1 comment = 'srv "shard1"');
|
Or, if the workflow is such that you don't want to create this shard1 partition right away, you can create a non-partitioned table first, and then add partitioning via the ALTER statement:
create or replace
|
table test.votes(
|
voter_id int,
|
vote varchar(1),
|
primary key(voter_id))
|
ENGINE=spider
|
COMMENT='wrapper "mysql", table "votes"';
|
|
-- do whatever you need to do here ..
|
|
alter table test.votes
|
PARTITION BY HASH (voter_id)
|
(PARTITION part1 comment = 'srv "shard1"');
|
|
|
So it looks like "p0" is always being created, just not shown by "show create table test.votes" in the case where no partitions were initially specified. The spec of this default partition looks like:
PARTITION p0 ENGINE = SPIDER
Because of the default definition of this default partition, I cannot query the information schema to confirm:
select * from INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='votes' AND TABLE_SCHEMA='test';
|
ERROR 1429 (HY000): Unable to connect to foreign data source: localhost
|
|
|
Note: we are talking about PARTITION BY HASH now, it's different for other partition types.
Default partition p0 is not always created, it's created if you don't specify the partition:
MariaDB [test]> create or replace table t1 (i int) partition by hash(i);
|
Query OK, 0 rows affected (0.58 sec)
|
|
MariaDB [test]> select partition_name, partition_expression, partition_method from information_schema.partitions where table_name = 't1';
|
+----------------+----------------------+------------------+
|
| partition_name | partition_expression | partition_method |
|
+----------------+----------------------+------------------+
|
| p0 | i | HASH |
|
+----------------+----------------------+------------------+
|
1 row in set (0.00 sec)
|
In a more general case, partitions p0 ... pN are created if you don't specify the list of partitions:
MariaDB [test]> create or replace table t1 (i int) partition by hash(i) partitions 5;
|
Query OK, 0 rows affected (1.79 sec)
|
|
MariaDB [test]> select partition_name, partition_expression, partition_method from information_schema.partitions where table_name = 't1';
|
+----------------+----------------------+------------------+
|
| partition_name | partition_expression | partition_method |
|
+----------------+----------------------+------------------+
|
| p0 | i | HASH |
|
| p1 | i | HASH |
|
| p2 | i | HASH |
|
| p3 | i | HASH |
|
| p4 | i | HASH |
|
+----------------+----------------------+------------------+
|
5 rows in set (0.00 sec)
|
But if you specify the list of partitions, default ones are not created:
MariaDB [test]> create or replace table t1 (i int) partition by hash(i) (partition my_part1, partition my_part2);
|
Query OK, 0 rows affected (1.59 sec)
|
|
MariaDB [test]> select partition_name, partition_expression, partition_method from information_schema.partitions where table_name = 't1';
|
+----------------+----------------------+------------------+
|
| partition_name | partition_expression | partition_method |
|
+----------------+----------------------+------------------+
|
| my_part1 | i | HASH |
|
| my_part2 | i | HASH |
|
+----------------+----------------------+------------------+
|
2 rows in set (0.01 sec)
|
In MariaDB/MySQL there is no such thing as "partitioned table without partitions". There is always at least one.
For the error that you're getting, it's just Spider specific. That's why I thought that you might want to create a non-partitioned table first, and then partition it later.
For SHOW CREATE TABLE output, with numerous obscure exceptions it shows the statement which was used to create the table. So, if you specified the list of partitions, it's shown there, if you didn't specify it, it's not. Adding ALTER TABLE to the equation makes it confusing beyond common sense, I wouldn't dare formulate any rules for that.
|