Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.1.17
-
None
-
docker mariadb:lastest image
Description
Problem
When adding the first partition with ALTER TABLE, two partitions are created - only one should be created.
Reproduce
1. Create the table
create database if not exists test;
|
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);
|
2. Confirm table has been created and can be described
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) */ |
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set, 2 warnings (0.04 sec)
|
|
|
MariaDB [(none)]> desc test.votes;
|
+----------+------------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+----------+------------+------+-----+---------+-------+
|
| voter_id | int(11) | NO | PRI | NULL | |
|
| vote | varchar(1) | YES | | NULL | |
|
+----------+------------+------+-----+---------+-------+
|
2 rows in set (0.00 sec)
|
3. Create the server and add the first partition
CREATE SERVER shard1 FOREIGN DATA WRAPPER mysql
|
OPTIONS(
|
HOST 'spider_shard1_1',
|
DATABASE 'test',
|
USER 'sharded',
|
PASSWORD 'sharding',
|
PORT 3306);
|
|
|
alter table test.votes add partition (PARTITION part1 comment = 'srv "shard1"');
|
4. Describe the modified table
MariaDB [(none)]> desc test.votes;
|
+----------+------------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+----------+------------+------+-----+---------+-------+
|
| voter_id | int(11) | NO | PRI | NULL | |
|
| vote | varchar(1) | YES | | NULL | |
|
+----------+------------+------+-----+---------+-------+
|
2 rows in set (0.00 sec)
|
|
|
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 p0 ENGINE = SPIDER,
|
PARTITION part1 COMMENT = 'srv "shard1"' ENGINE = SPIDER) */ |
|
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set, 6 warnings (15.10 sec)
|
As can be seen, it appears two partitions are created
- part1 - this comes from the ALTER TABLE
- p0 - seems to have been created automatically
Solution
Do not create the additional and unrequested partition (in this case "p0").
Rationale
To automate adding a spider node, after the node starts we will want to script (Docker, Salt, Ansible etc.) adding the partition for the new node. Having this extra Partition breaks queries and other operations because they timeout.
Workaround
After each ALTER TABLE ADD PARTITION, execute a ALTER TABLE ... DROP PARTITION p0;