MySQL Bugs: #93594: Error migrating PostgreSQL 11 database using Wokbench Migration Tool

Bug #93594	Error migrating PostgreSQL 11 database using Wokbench Migration Tool
Submitted:	13 Dec 2018 14:36	Modified:	24 Sep 2019 11:53
Reporter:	Victor Nunes	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Workbench: Migration	Severity:	S3 (Non-critical)
Version:	8.0.17, 8.0.19, 8.0.22, 8.0.24, 8.0.26, 8.0.41	OS:	Ubuntu (19.04)
Assigned to:		CPU Architecture:	x86 (64 bits)

Description:
MySQL Workbench 8.0 Migration Tool presents an error on reverse engeneering processes of selected schemas from a PostgreSQL 11 database.

Trying to migrate the postgres schema to a fresh MySQL schema of a MySQL 8.0 server I got the following message log:

Starting...
Connect to source DBMS...
- Connecting...
Connecting to postgresql@DRIVER=psqlodbc;SERVER=10.60.1.54;PORT=5434...
Opening ODBC connection to DRIVER=psqlodbc;SERVER=10.60.1.54;PORT=5434;DATABASE=eventos;UID=postgres;UseDeclareFetch=1;...
Connected
Connect to source DBMS done
Reverse engineer selected schemas....
Reverse engineering public from eventos
- Reverse engineering catalog information
Traceback (most recent call last):
  File "/usr/lib/mysql-workbench/modules/db_postgresql_re_grt.py", line 356, in reverseEngineer
    return PostgresqlReverseEngineering.reverseEngineer(connection, catalog_name, schemata_list, context)
  File "/usr/lib/mysql-workbench/modules/db_generic_re_grt.py", line 241, in reverseEngineer
    catalog = cls.reverseEngineerCatalog(connection, catalog_name)
  File "/usr/lib/mysql-workbench/modules/db_generic_re_grt.py", line 401, in reverseEngineerCatalog
    cls.reverseEngineerSequences(connection, schema)
  File "/usr/lib/mysql-workbench/modules/db_postgresql_re_grt.py", line 80, in reverseEngineerSequences
    min_value, max_value, start_value, increment_by, last_value, is_cycled, ncache = cls.execute_query(connection, seq_details_query % (schema.name, seq_name)).fetchone()
  File "/usr/lib/mysql-workbench/modules/db_generic_re_grt.py", line 80, in execute_query
    return cls.get_connection(connection_object).cursor().execute(query, *args, **kwargs)
pyodbc.ProgrammingError: ('42703', '[42703] ERROR: column "min_value" does not exist;\nError while executing the query (1) (SQLExecDirectW)')

Traceback (most recent call last):
  File "/usr/share/mysql-workbench/libraries/workbench/wizard_progress_page_widget.py", line 196, in thread_work
    self.func()
  File "/usr/lib/mysql-workbench/modules/migration_schema_selection.py", line 179, in task_reveng
    self.main.plan.migrationSource.reverseEngineer()
  File "/usr/lib/mysql-workbench/modules/migration.py", line 364, in reverseEngineer
    self.state.sourceCatalog = self._rev_eng_module.reverseEngineer(self.connection, self.selectedCatalogName, self.selectedSchemataNames, self.state.applicationData)
SystemError: ProgrammingError("('42703', '[42703] ERROR: column "min_value" does not exist;\nError while executing the query (1) (SQLExecDirectW)')"): error calling Python module function DbPostgresqlRE.reverseEngineer
ERROR: Reverse engineer selected schemas: ProgrammingError("('42703', '[42703] ERROR: column "min_value" does not exist;\nError while executing the query (1) (SQLExecDirectW)')"): error calling Python module function DbPostgresqlRE.reverseEngineer
Failed

Apparently there is an error on the Python script that make the reverse engeneering proccess, maybe related to some change in the schema skeleton on PostgreSQL 11 update.

How to repeat:
Try to migrate a PostgreSQL 11 schema to MySQL 8.0 via Workbench Migration Tool, using psqlodbc-11.00.0000 driver.

https://bugs.mysql.com/bug.php?id=93785 marked as duplicate of this one.

Thank you for the bug report. Please provide a SQL script to create the PostgreSQL stuff so we could try to migrate it. Thanks.

https://bugs.mysql.com/bug.php?id=94399 marked as duplicate of this one.

https://bugs.mysql.com/bug.php?id=94471 marked as duplicate of this one.

How is the duplicate of this https://bugs.mysql.com/bug.php?id=94471 marked as duplicate of this one. 

The 94471 bug is regarding the "is_cycled" column doesn't exist. The error in this thread is "min_value" column missing? Please recheck the error in the 94471 bug and help me.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

PostgreSQL Affected DB

Attachment: helpdesk_2019-09-23_1133.sql (application/sql, text), 53.38 KiB.

The issue still persists on Workbench 8.0.17 and Ubuntu 19.04, the solicited file was provided on a previous comment.

Sorry about the delay but because we couldn't properly made the migration, we just gave up and keeped using PostgreSQL.

Browsing on the Internet trying to fix the issue, I found this patch to WORKBENCH_INSTALLATION_PATH/modules/db_postgresql_re_grt.py

on line 74 I replaced:

    seq_details_query = """SELECT min_value, max_value, start_value, 
increment_by, last_value, is_cycled, cache_value
FROM "%s"."%s" """

for:

    seq_details_query = """SELECT min_value, max_value, start_value, 
increment_by, last_value, cycle as is_cycled, cache_size as cache_value
FROM pg_catalog.pg_sequences
WHERE schemaname = '%s' AND sequencename = '%s' """

and the error reported didn't happen anymore, but when the migration process is actually running workbench throws a segmentation fault and crashes.

No hope over here.

Thank you for the sql dump and feedback.
Observed when tried to migrate from Postgres 11 to MySQL 8.0.17.

Regards,
Ashwini Patil

8.0.17 test results

Attachment: 93594.results.txt (text/plain), 8.31 KiB.

Bug #98749 marked as duplicate of this one.

According to Victor Nunes, in file: WORKBENCH_INSTALLATION_PATH/modules/db_postgresql_re_grt.py I have changed this (line 74): 

seq_details_query = """SELECT min_value, max_value, start_value, 
increment_by, last_value, is_cycled, cache_value
FROM "%s"."%s" """

To this (see some additional changes): 

seq_details_query = """SELECT ps.min_value, ps.max_value, ps.start_value, 
ps.increment_by, "%s".last_value, ps.cycle as is_cycled, ps.cache_size as cache_value
FROM "%s"."%s" JOIN pg_sequences ps ON ps.sequencename = '%s' """

Also do not forget to add additional parameters to query execution (line 80):

... seq_details_query % (seq_name, schema.name, seq_name, seq_name) ...

And the process went smoothly. The case is, that Postgres from version 10 has some data about sequences stored in different table.

Bug #99783 marked as duplicate of this one.

For version 8.0.20, the fix proposed causes MySQL Workbench to segfault on launch. See attached file.

Version string = "PostgreSQL 11.7 (Debian 11.7-0+deb10u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit"

Error, version 8.0.20

Attachment: mysql-wb-error.txt (text/plain), 76.76 KiB.

Bug #101741 marked as duplicate of this one.

Bug #104759 marked as duplicate of this one.

Bug #104762 marked as duplicate of this one.

Answer by Victor Nunes of replacing the specific code in db_postgresql_re_grt.py fixed my issue.
I was able to successfully do the migration but it only migrated my tables from PostgreSQL to MySQL whereas I had tables, views, procedures as well as functions.
But it didn't anything else than tables. Can anyone guide here?

Tools Used:
PostgreSQL 14.6
MySQL 8.0

Bug #117946 marked as duplicate of this one.