PostgreSQL to MySQL MySQL Workbench migration error

Question

I am trying to migrate a PostgreSQL database to MySQL using MySQL Workbench. I have been able to connect to both databases but when I get to the Reverse Engineer Source step I get the error below.

The system is using PostgreSQL 8.1.23. Old I know which is why we a are migrating the system to the latest version of MySQL.

This post has a similar issue but not exactly the same PostgreSQL to MySQL data migration

I am guessing it is an issue with a sequence but not sure how to track it down.

Thanks!

Starting... Connect to source DBMS... - Connecting... Connecting to postgresql@DRIVER=PostgreSQL ANSI(x64);SERVER=162.242.171.94;PORT=5432... Opening ODBC connection to DRIVER=PostgreSQL ANSI(x64);SERVER=123.456.123.456;PORT=5432;DATABASE=xxxxxxx;UID=xxxx;UseDeclareFetch=1;... Connected Connect to source DBMS done Reverse engineer selected schemas.... Reverse engineering public from xxxxxxx - Reverse engineering catalog information Traceback (most recent call last): File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\db_postgresql_re_grt.py", line 352, in reverseEngineer return PostgresqlReverseEngineering.reverseEngineer(connection, catalog_name, schemata_list, context) File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\db_generic_re_grt.py", line 237, in reverseEngineer catalog = cls.reverseEngineerCatalog(connection, catalog_name) File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\db_generic_re_grt.py", line 397, in reverseEngineerCatalog cls.reverseEngineerSequences(connection, schema) File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\db_postgresql_re_grt.py", line 76, 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 "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\db_generic_re_grt.py", line 76, in execute_query return cls.get_connection(connection_object).cursor().execute(query, *args, **kwargs) pyodbc.ProgrammingError: ('42703', '[42703] ERROR: column "start_value" does not exist;\nError while executing the query (1) (SQLExecDirectW)')

Traceback (most recent call last): File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\workbench\wizard_progress_page_widget.py", line 192, in thread_work self.func() File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\migration_schema_selection.py", line 175, in task_reveng self.main.plan.migrationSource.reverseEngineer() File "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\migration.py", line 369, 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 "start_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 "start_value" does not exist;\nError while executing the query (1) (SQLExecDirectW)')"): error calling Python module function DbPostgresqlRE.reverseEngineer Failed

In MySQL Workbench db_postgresql_migration_grt.py has start_value in the SQL statement, line #70. Since there is no start_value column I assume this is the source of the issue. Any suggestions on how this should be replaced? — ZeroGravity
– ZeroGravity, Commented Mar 27, 2017 at 20:01
I am having the same problem too and I wonder if our postgres version is higher than the workbench supports since where I read the workbench tutorial is actually from 2012 and lots of changed during these years. — Tsuna
– Tsuna, Commented Dec 13, 2017 at 23:12

Ahsan Ehtesham · Accepted Answer · 2022-12-05 06:36:46Z

0

Try adding this code in this location WORKBENCH_INSTALLATION_PATH/modules/db_postgresql_re_grt.py

Replace:

    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' """

Then restart MySQL Workbench and retry migrating, the issue will be solved

answered Dec 5, 2022 at 6:36

Ahsan Ehtesham

1561 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL to MySQL MySQL Workbench migration error

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related