Differences depending on mysql version

[jaehanchoi]

Hello, I am a student studying db in Korea.

I tried using ora2pg v23.2 and wanted to extract data to MySQL v5.5.62, but the following error occurred and it was not executed.

mysqld -V

mysqld Ver 5.5.62 for Linux on x86_64 (MySQL Community Server (GPL))

$ ora2pg -v Ora2Pg v23.2

---

$ ora2pg -c ora2pg.conf --debug [2023-03-04 00:52:06] Ora2Pg version: 23.2 [2023-03-04 00:52:06] Export type: TABLE [2023-03-04 00:52:06] Geometry export type: INTERNAL [2023-03-04 00:52:06] Trying to connect to database: dbi:mysql:host=192.168.1.12;database=test;port=3306 [2023-03-04 00:52:06] Isolation level: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE [2023-03-04 00:52:06] Set default encoding to 'utf8' and collate to 'utf8_general_ci' [2023-03-04 00:52:06] Retrieving table information... [2023-03-04 00:52:06] Retrieving index information... [2023-03-04 00:52:06] Retrieving columns information... [2023-03-04 00:52:06] Collecting column information for table ... DBD::mysql::st execute failed: Unknown column 'GENERATION_EXPRESSION' in 'field list' at /usr/local/share/perl5/Ora2Pg/MySQL.pm line 398. DBD::mysql::st execute failed: Unknown column 'GENERATION_EXPRESSION' in 'field list' at /usr/local/share/perl5/Ora2Pg/MySQL.pm line 398.

Looking at the error, ora2pg connects to Mysql through the dbd:mysql module, reads information such as tables, indexes, and columns, and executes them.

However, since there is no 'GENERATION_EXPRESSION' column, it seems to generate an error and not be executed.

I checked that it seems to be querying the system catalog called information_schema.columns in mysql.

Mysql 5.5.62 version is as below.

mysql> desc information_schema.columns; +--------------------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+---------------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | NO | | | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | COLUMN_NAME | varchar(64) | NO | | | | | ORDINAL_POSITION | bigint(21) unsigned | NO | | 0 | | | COLUMN_DEFAULT | longtext | YES | | NULL | | | IS_NULLABLE | varchar(3) | NO | | | | | DATA_TYPE | varchar(64) | NO | | | | | CHARACTER_MAXIMUM_LENGTH | bigint(21) unsigned | YES | | NULL | | | CHARACTER_OCTET_LENGTH | bigint(21) unsigned | YES | | NULL | | | NUMERIC_PRECISION | bigint(21) unsigned | YES | | NULL | | | NUMERIC_SCALE | bigint(21) unsigned | YES | | NULL | | | CHARACTER_SET_NAME | varchar(32) | YES | | NULL | | | COLLATION_NAME | varchar(32) | YES | | NULL | | | COLUMN_TYPE | longtext | NO | | NULL | | | COLUMN_KEY | varchar(3) | NO | | | | | EXTRA | varchar(27) | NO | | | | | PRIVILEGES | varchar(80) | NO | | | | | COLUMN_COMMENT | varchar(1024) | NO | | | | +--------------------------+---------------------+------+-----+---------+-------+ 19 rows in set (0.00 sec) - 5.5.62

Mysql 8.0.32 version is as below.

mysql> desc information_schema.columns; +--------------------------+----------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------------------+----------------------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(64) | NO | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | NULL | | | TABLE_NAME | varchar(64) | NO | | NULL | | | COLUMN_NAME | varchar(64) | YES | | NULL | | | ORDINAL_POSITION | int unsigned | NO | | NULL | | | COLUMN_DEFAULT | text | YES | | NULL | | | IS_NULLABLE | varchar(3) | NO | | | | | DATA_TYPE | longtext | YES | | NULL | | | CHARACTER_MAXIMUM_LENGTH | bigint | YES | | NULL | | | CHARACTER_OCTET_LENGTH | bigint | YES | | NULL | | | NUMERIC_PRECISION | bigint unsigned | YES | | NULL | | | NUMERIC_SCALE | bigint unsigned | YES | | NULL | | | DATETIME_PRECISION | int unsigned | YES | | NULL | | | CHARACTER_SET_NAME | varchar(64) | YES | | NULL | | | COLLATION_NAME | varchar(64) | YES | | NULL | | | COLUMN_TYPE | mediumtext | NO | | NULL | | | COLUMN_KEY | enum('','PRI','UNI','MUL') | NO | | NULL | | | EXTRA | varchar(256) | YES | | NULL | | | PRIVILEGES | varchar(154) | YES | | NULL | | | COLUMN_COMMENT | text | NO | | NULL | | | GENERATION_EXPRESSION | longtext | NO | | NULL | | | SRS_ID | int unsigned | YES | | NULL | | +--------------------------+----------------------------+------+-----+---------+-------+ 22 rows in set (0.30 sec) - 8.0

In the same configuration mysql 8.0.32 version succeeded in extracting the schema.

If my analysis above is correct, what is the range of support for ora2pg according to mysql version?

If not, is there any way to extract data from mysql 5 version?

thanks.

[jaehanchoi]

If there is any more information you need, please let me know. Thank you.

[darold]

Commit 76d7983 might fix this issue, thanks for the report.