Mysql to postgresql migration

[mgole001]

Hi,

Attempting a mysql to postgresql migration and running the import-all.sh script

I have following custom parameter values set in ora2pg conf file SKIP keys keys keys indexes checks Fkey_deferable 1 Drop_key 1

I am trying to import directly to the postgresql database It gives me following error and aborts import Fatal: error : null value in column ----- violates not null constraint

How to resolve this issue, my understanding is that by setting above non custom values (1) for certain parameter in ora2pg conf file I had assumed that constraints would be skipped/not checked during import?

Thanks

[darold]

Don't import constraint and indexes before importing data is the best solution.

[mgole001]

Yes agree, so while doing import for the below option I choose N, still it gave me above null constraint error, is there another way to not import constraint prior to data import

Would you like to process indexes and constraints before loading data? [y/N/q] N

[mgole001]

I tried the following but still gives not null constraint violation error, using ora2pg version V24.1

1. Import only schema by using Import_all.sh - I - s
2. Copy data (this step gives not null constraint violation error)
3. Import constraint/indexes/trigger Import_all.sh - i

How do I resolve this error ? Thanks

[darold]

Fix the data, if you have a column with a NOT NULL constraint, whether you create the constraint before or after this is the same.

[mgole001]

Thanks for the input

Another issue While importing using Import_all.sh - I - s I get below error

Psql:./schema/tables/table.sql:45:error: invalid command \'$.clid ErROR: An error occurs when importing file. /schema/tables/table.sql

When I look into table.sql here is the line which is erroring out? Can you help why the importing is giving error for this line

Clid varchar(255) Generated always as (json_unquote(json_extract(cust, _utf8mb4\'$.clid\'))) STORED

[darold]

Please post de MySQL DDL of this table.

[mgole001]

CREATE TABLE test.activation (id bigint (16) NOT NULL AUTO_INCREMENT, hours int (10) NOT NULL DEFAULT 4, details json NULL, cust json NULL, updated_at datetime(0) NOT NULL, clid varchar (255) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci GENERATED ALWAYS AS (json_unquote(json_extract(cust, _utf8mb4\'$.clid\'))) VIRTUAL NULL, PRIMARY KEY (id)) ENGINE InnoDB COLLATE 'utf8mb3_general_ci' ROW_FORMAT DEFAULT;

[mgole001]

Hi, any update on how to resolve this issue? Thx

[darold]

Commit e35a82c might fix this particular case but more complex call to json_extract() might fail. Also here the call to json_unquote can not be replaced so you still have to edit the output DDL file.