DBD::mysql::st execute failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(p1sp0) a' at line 1 at /usr/local/share/perl5/Ora2Pg.pm line 14662

[sanyamsinghal]

I have a MySQL database that has a variety of table partitioning defined(including subpartitions). When I am trying to export data it is failing because of subpartitions present in the schema. If I remove tables with subpartitions the export of data goes through.

Is data export for subpartitions in MySQL supported yet?

I am pasting below the complete schema i used for this:

drop table if exists range_partition_test;

CREATE TABLE range_partition_test (bill_no INT , bill_date TIMESTAMP unique, 
cust_code VARCHAR(15) NOT NULL, amount DECIMAL(8,2), primary key(bill_no,bill_date))  
PARTITION BY RANGE (UNIX_TIMESTAMP(bill_date))(
PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2013-04-01')), 
PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2013-07-01')), 
PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2013-10-01')), 
PARTITION p3 VALUES LESS THAN MAXVALUE);

INSERT INTO range_partition_test VALUES (1, '2013-01-02', 'C001', 125.56), 
(2, '2013-01-25', 'C003', 456.50), 
(3, '2014-02-15', 'C012', 365.00), 
(4, '2013-03-26', 'C345', 785.00), 
(5, '2013-04-19', 'C234', 656.00), 
(6, '2013-05-31', 'C743', 854.00), 
(7, '2013-06-11', 'C234', 542.00), 
(8, '2013-07-24', 'C003', 300.00), 
(9, '2013-08-02', 'C456', 475.20);

SELECT * FROM range_partition_test;

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='range_partition_test';


drop table if exists list_partition_test;

CREATE TABLE list_partition_test (bill_no INT, bill_date TIMESTAMP, 
cust_code VARCHAR(15) NOT NULL, amount DECIMAL(8,2), primary key(bill_no,bill_date)) 
PARTITION BY LIST(bill_no) (   
PARTITION pEast VALUES IN (1, 3),   
PARTITION pWest VALUES IN (2, 4),   
PARTITION pNorth VALUES IN (5, 6),   
PARTITION pSouth VALUES IN (7,8,9));   

INSERT INTO list_partition_test VALUES (1, '2013-01-02', 'C001', 125.56), 
(2, '2013-01-25', 'C003', 456.50), 
(3, '2014-02-15', 'C012', 365.00), 
(4, '2013-03-26', 'C345', 785.00), 
(5, '2013-04-19', 'C234', 656.00), 
(6, '2013-05-31', 'C743', 854.00), 
(7, '2013-06-11', 'C234', 542.00), 
(8, '2013-07-24', 'C003', 300.00), 
(9, '2013-08-02', 'C456', 475.20);

SELECT * FROM list_partition_test;

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='list_partition_test';


drop table if exists range_columns_partition_test;

CREATE TABLE range_columns_partition_test (
    a INT,
    b INT
)
PARTITION BY RANGE COLUMNS(a, b) (
    PARTITION p0 VALUES LESS THAN (5, 5),
    PARTITION p1 VALUES LESS THAN (MAXVALUE, MAXVALUE)
);

INSERT INTO range_columns_partition_test VALUES (5,5),(3,4), (5,11), (5,12),(4,3);

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='range_columns_partition_test';


drop table if exists list_columns_partition_test;

CREATE TABLE list_columns_partition_test (
    a INT,
    b INT,
    c INT
)
PARTITION BY LIST COLUMNS(a, b) (
    PARTITION p0 VALUES in ((3,4),(5,11)),
    PARTITION p1 VALUES in ((5,5))
);

INSERT INTO list_columns_partition_test VALUES (5,5,1),(3,4,6), (5,11,1), (5,11,2),(5,5,5);

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='list_columns_partition_test';


drop table if exists key_partition_test;

CREATE TABLE key_partition_test (
    a INT,
    b INT primary key,
    c INT
)
PARTITION BY key()
partitions 2;

INSERT INTO key_partition_test VALUES (5,6,1),(3,4,6), (5,11,1), (5,1,2),(5,5,5);

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='key_partition_test';

select * from key_partition_test partition(p0);
select * from key_partition_test partition(p1);


drop table if exists hash_partition_test;

CREATE TABLE hash_partition_test (
    a INT,
    b INT primary key,
    c INT
)
PARTITION BY hash(b)
partitions 2;

INSERT INTO hash_partition_test VALUES (5,6,1),(3,4,6), (5,11,1), (5,1,2),(5,5,5);

SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='hash_partition_test';

select * from hash_partition_test partition(p0);


drop table if exists subpartitioning_test;

CREATE TABLE subpartitioning_test (BILL_NO INT, sale_date DATE, cust_code VARCHAR(15), 
AMOUNT DECIMAL(8,2))
PARTITION BY RANGE(MONTH(sale_date) )
SUBPARTITION BY HASH(TO_DAYS(sale_date))
SUBPARTITIONS 4 (
PARTITION p0 VALUES LESS THAN (2),
PARTITION p1 VALUES LESS THAN (4),
PARTITION p2 VALUES LESS THAN (6),
PARTITION p3 VALUES LESS THAN MAXVALUE
);

INSERT INTO subpartitioning_test VALUES (1, '2013-01-02', 'C001', 125.56), 
(2, '2013-01-25', 'C003', 456.50), 
(3, '2014-02-15', 'C012', 365.00), 
(4, '2013-03-26', 'C345', 785.00), 
(5, '2013-04-19', 'C234', 656.00), 
(6, '2013-05-31', 'C743', 854.00), 
(7, '2013-06-11', 'C234', 542.00), 
(8, '2013-07-24', 'C003', 300.00), 
(9, '2013-08-02', 'C456', 475.20);


SELECT PARTITION_NAME, TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='subpartitioning_test';

[sanyamsinghal]

cc @darold

[darold]

MySQL sub partitioning is not supported yet, I will look at this asap.

[sanyamsinghal]

Sure, thanks

[darold]

Commit a642de4 add support for sub-partitioning data export.