Unable to export table partitions from mysql using ora2pg

[sanyamsinghal]

It detects 4 partitions but could not export DDLs for them and continues

I am using the export_schema.sh script which comes with the standard ora2pg project.

[root@ip-10-9-80-202 sakila]# ./export_schema.sh
[========================>] 33/33 tables (100.0%) end of scanning.
[========================>] 11/11 objects types (100.0%) end of objects auditing.
Running: ora2pg -p -m -t TABLE -o table.sql -b ./schema/tables -c ./config/ora2pg.conf
[========================>] 33/33 tables (100.0%) end of scanning.
[========================>] 40/40 tables (100.0%) end of table export.
Running: ora2pg -p -m -t VIEW -o view.sql -b ./schema/views -c ./config/ora2pg.conf
[========================>] 7/7 views (100.0%) end of output.
Running: ora2pg -p -m -t GRANT -o grant.sql -b ./schema/grants -c ./config/ora2pg.conf
WARNING: Exporting privilege as non DBA user is not allowed, see USER_GRANT
Running: ora2pg -p -m -t TRIGGER -o trigger.sql -b ./schema/triggers -c ./config/ora2pg.conf
[========================>] 6/6 triggers (100.0%) end of output.
Running: ora2pg -p -m -t FUNCTION -o function.sql -b ./schema/functions -c ./config/ora2pg.conf
[========================>] 3/3 functions (100.0%) end of functions export.
Running: ora2pg -p -m -t PROCEDURE -o procedure.sql -b ./schema/procedures -c ./config/ora2pg.conf
[========================>] 4/4 procedures (100.0%) end of procedures export.
Running: ora2pg -p -m -t PARTITION -o partition.sql -b ./schema/partitions -c ./config/ora2pg.conf
[>                        ] 0/4 partitions (0.0%) end of output.
Running: ora2pg -p -m -t DBLINK -o dblink.sql -b ./schema/dblinks -c ./config/ora2pg.conf
[========================>] 0/0 dblink (100.0%) end of output.
Running: ora2pg -t VIEW -o view.sql -b ./sources/views -c ./config/ora2pg.conf
[========================>] 7/7 views (100.0%) end of output.
Running: ora2pg -t TRIGGER -o trigger.sql -b ./sources/triggers -c ./config/ora2pg.conf
[========================>] 6/6 triggers (100.0%) end of output.
Running: ora2pg -t FUNCTION -o function.sql -b ./sources/functions -c ./config/ora2pg.conf
[========================>] 3/3 functions (100.0%) end of functions export.
Running: ora2pg -t PROCEDURE -o procedure.sql -b ./sources/procedures -c ./config/ora2pg.conf
[========================>] 4/4 procedures (100.0%) end of procedures export.
Running: ora2pg -t PARTITION -o partition.sql -b ./sources/partitions -c ./config/ora2pg.conf
[>                        ] 0/4 partitions (0.0%) end of output.
Running: ora2pg -t MVIEW -o mview.sql -b ./sources/mviews -c ./config/ora2pg.conf
[========================>] 0/0 materialized views (100.0%) end of output.


To extract data use the following command:

ora2pg -t COPY -o data.sql -b ./data -c ./config/ora2pg.conf

@darold please have a look and let me know if something is wrong with my setup

[darold]

Can you post the output of the following command:

ora2pg -p -m -t PARTITION -o partition.sql -b ./schema/partitions -c ./config/ora2pg.conf -v

Also what is your setting of PG_VERSION in ora2pg.conf?

[sanyamsinghal]

@darold output is this:

$ ora2pg -p -m -t PARTITION -o partition.sql -b ./schema/partitions -c ./config/ora2pg.conf -v
Ora2Pg v23.1

PG_VERSION is 12 in my config file

Please let me know if you need any other details

[sanyamsinghal]

@darold any update on this?

[sanyamsinghal]

@darold is it only me who is having issues with Table Partitions in MySQL or it is true for others also?

[gilles-migops]

It might be broken for everyone, this is just that I have not found the time to work on it until now.

[sanyamsinghal]

ok thanks, just wanted to confirm that.

[gilles-migops]

Please give a try to latest development code, commit 7a01b2d might solve this issue. I have no MySQL database with partitioning so I have just done a simple test with RANGE partitioning so let me know if there is any other issues.

[sanyamsinghal]

thanks a lot @darold, sure i will let you know once i test this.

[sanyamsinghal]

Hi @darold i have been testing these changes for a MySQL schema. Few irregularities I observed. Let me know if i am missing something?

There are DDLs in partition.sql but exported CREATE TABLE statement doesn't have PARTITIONING defined on it.

Example: for a table named "customers"

> table.sql
CREATE TABLE customers (
        customerid double precision NOT NULL,
        firstname varchar(50) NOT NULL,
        lastname varchar(50) NOT NULL,
        address1 varchar(50) NOT NULL,
        address2 varchar(50),
        city varchar(50) NOT NULL,
        state varchar(50),
        zip double precision,
        country varchar(50) NOT NULL,
        region integer NOT NULL,
        email varchar(50),
        phone varchar(50),
        creditcardtype double precision NOT NULL,
        creditcard varchar(50),
        creditcardexpiration varchar(50) NOT NULL,
        username varchar(50) NOT NULL,
        password varchar(50) NOT NULL,
        age double precision,
        income double precision,
        gender varchar(6)
) ;

> partition.sql
CREATE TABLE us_part PARTITION OF customers
FOR VALUES FROM (MINVALUE) TO (2);
CREATE TABLE row_part PARTITION OF customers
FOR VALUES FROM (2) TO (3);
CREATE TABLE maxval PARTITION OF customers DEFAULT;

So during import database postgres throws this error: "customers" is not partitioned

[sanyamsinghal]

I am not sure if this has come to notice since this is a closed issue, please acknowledge. Will wait for acknowledgment for sometime before creating a new issue. cc @darold

[darold]

Actually the work on exporting partitions for MySQL was never ended, it don't works at all with latest change in partitioning export. I will fix that.

[darold]

Commit eb5eb2b might fix MySQL partition export. I still have to work on sub partition but if you don't have such object you can test latest development code.

[sanyamsinghal]

Yeah, I don't have a subpartition as of now in my database. I will test this latest commit and let you know.

[sanyamsinghal]

Thanks, @darold it looks good for my use case(that has some tables with RANGE PARTITIONING) Will like to try sub-partitioning also once those changes land.