search

yugabyte / yb-voyager

Data migration Engine for YugabyteDB database
38 stars 10 forks source link

[ORACLE] Issues in exporting PM schema : Nested tables are exported as part of export schema command #679

Open chetank-yb opened 1 year ago

chetank-yb commented 1 year ago

Jira Link: DB-13487 Download schemas here: https://github.com/oracle/db-sample-schemas/releases/latest Schema used for test : PM (product media)

Total tables in schema : 2 Table migrated : print_media only

NOTE : The print_media table has 4 rows which are not exported as part of export data.

FYR, the table.sql contents of this schema is

CREATE TABLE print_media (
    product_id integer NOT NULL,
    ad_id integer NOT NULL,
    ad_composite bytea,
    ad_sourcetext text,
    ad_finaltext text,
    ad_fltextn text,
    ad_textdocs_ntab TEXTDOC_TAB,
    ad_photo bytea,
    ad_graphic bytea,
    ad_header ADHEADER_TYP,
    PRIMARY KEY (product_id,ad_id)
) ;
ALTER TABLE print_media ADD CONSTRAINT printmedia_fk FOREIGN KEY (product_id) REFERENCES oe.product_information(product_id) ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;
shubham-yb commented 1 year ago

Export is not going through due to the presence of the BFILE column. Support for which will be tracked in https://github.com/yugabyte/yb-voyager/issues/712

After altering the table to drop the column, the export goes through but is showing incorrect/garbage values. For example instead of 4 it's showing 123 rows. The import also fails and the log shows

2022-12-26 10:33:16 INFO utils.go:310 string s="ERROR: invalid byte sequence for encoding \"UTF8\": 0x00 (SQLSTATE 22021)" did not match with any string in [Sending too long RPC message invalid input syntax violates unique constraint]

This might be due to the source encoding which is different from UTF-8 i.e. WE8MSWIN1252 Exploring it further.