MySQL Bugs: #68997: Error occured when "reverse engineer selected schemata" for PostgreSQL 9.0.4

Bug #68997	Error occured when "reverse engineer selected schemata" for PostgreSQL 9.0.4
Submitted:	18 Apr 2013 13:15	Modified:	27 Jul 2013 20:16
Reporter:	Thales Pereira	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Migration	Severity:	S2 (Serious)
Version:	5.2.47	OS:	Windows (64 bits)
Assigned to:		CPU Architecture:	Any
Tags:	migration, Postgresql, workbench

Description:
Hi,

When I tried to migrate data stored on PostgreSQL with the version 9.0.4 to MySQL 5.5 , I got the following error at the step of "Reverse engineer selected schemata" on Windows 7

Starting...

Connect to source DBMS...

- Connecting...

Connect to source DBMS done

Reverse engineer selected schemata....

Reverse engineering public from cmdb

- Reverse engineering catalog information

Traceback (most recent call last):

  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.47\modules\db_postgresql_re_grt.py", line 335, in reverseEngineer

    return PostgresqlReverseEngineering.reverseEngineer(connection, catalog_name, schemata_list, context)

  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.47\modules\db_generic_re_grt.py", line 207, in reverseEngineer

    catalog = cls.reverseEngineerCatalog(connection, catalog_name)

  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.47\modules\db_generic_re_grt.py", line 358, in reverseEngineerCatalog

    cls.reverseEngineerUserDatatypes(connection, catalog)

  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.47\modules\db_postgresql_re_grt.py", line 236, in reverseEngineerUserDatatypes

    enum_types = cls.execute_query(connection, query_enums if version.majorNumber >= 9 else query_enums_80)

  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.47\modules\db_generic_re_grt.py", line 56, in execute_query

    return cls.get_connection(connection_object).cursor().execute(query, *args, **kwargs)

pyodbc.ProgrammingError: ('42703', '[42703] ERROR: column e.enumsortorder does not exist;\nError while executing the query (7) (SQLExecDirectW)')

Traceback (most recent call last):

  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.47\workbench\wizard_progress_page_widget.py", line 192, in thread_work

    self.func()

  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.47\modules\migration_schema_selection.py", line 160, in task_reveng

    self.main.plan.migrationSource.reverseEngineer()

  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.47\modules\migration.py", line 332, 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 e.enumsortorder does not exist;\nError while executing the query (7) (SQLExecDirectW)')"): error calling Python module function DbPostgresqlRE.reverseEngineer

ERROR: Reverse engineer selected schemata: ProgrammingError("('42703', '[42703] ERROR: column e.enumsortorder does not exist;\nError while executing the query (7) (SQLExecDirectW)')"): error calling Python module function DbPostgresqlRE.reverseEngineer

Failed

How to repeat:
How to repeat:
Use MySQL Workbench 5.2.47 to migrate data from a PostgreSQL server of version 9.0.4 or 8.4 on Windows 7 64bits

Description:
Hi,

When I tried to migrate data stored on PostgreSQL with the version 9.0.4 to MySQL 5.5 , I got the following error at the step of "Reverse engineer selected schemata" on Windows 7:

------------LOG START------------------

Starting...
Connect to source DBMS...
- Connecting...
Connect to source DBMS done
Reverse engineer selected schemata....
Reverse engineering public from cmdb
- Reverse engineering catalog information
Traceback (most recent call last):
  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.47\modules\db_postgresql_re_grt.py", line 335, in reverseEngineer
    return PostgresqlReverseEngineering.reverseEngineer(connection, catalog_name, schemata_list, context)
  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.47\modules\db_generic_re_grt.py", line 207, in reverseEngineer
    catalog = cls.reverseEngineerCatalog(connection, catalog_name)
  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.47\modules\db_generic_re_grt.py", line 358, in reverseEngineerCatalog
    cls.reverseEngineerUserDatatypes(connection, catalog)
  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.47\modules\db_postgresql_re_grt.py", line 236, in reverseEngineerUserDatatypes
    enum_types = cls.execute_query(connection, query_enums if version.majorNumber >= 9 else query_enums_80)
  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.47\modules\db_generic_re_grt.py", line 56, in execute_query
    return cls.get_connection(connection_object).cursor().execute(query, *args, **kwargs)
pyodbc.ProgrammingError: ('42703', '[42703] ERROR: column e.enumsortorder does not exist;\nError while executing the query (7) (SQLExecDirectW)')
Traceback (most recent call last):
  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.47\workbench\wizard_progress_page_widget.py", line 192, in thread_work
    self.func()
  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.47\modules\migration_schema_selection.py", line 160, in task_reveng
    self.main.plan.migrationSource.reverseEngineer()
  File "C:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.47\modules\migration.py", line 332, 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 e.enumsortorder does not exist;\nError while executing the query (7) (SQLExecDirectW)')"): error calling Python module function DbPostgresqlRE.reverseEngineer
ERROR: Reverse engineer selected schemata: ProgrammingError("('42703', '[42703] ERROR: column e.enumsortorder does not exist;\nError while executing the query (7) (SQLExecDirectW)')"): error calling Python module function DbPostgresqlRE.reverseEngineer
Failed

-------------LOG END------------

How to repeat:
Use MySQL Workbench 5.2.47 to migrate data from a PostgreSQL server of version 9.0.4 or 8.4 on Windows 7 64bits

Hello.
Can you please attach (as private)a dump of a DB where you can reproduce this issue.
Thanks.

Hi,

Since its a client database with sensitive information, i will create a new db with same db/table setup with random data as soon i got some time to create it.

This bug only happens for PostgreSQL server from version 9.0 up to 9.1. It turns out that we were mistakenly assuming that the pg_catalog.pg_enum table had the enumsortorder column for versions >= 9.0 when it was in fact added in 9.1.

The fix is really simple: Edit the file db_postgresql_re_grt.py (inside the modules dir within Workbench install dir) and locate, around line 236, the line that reads:

enum_types = cls.execute_query(connection, query_enums if version.majorNumber >= 9 else query_enums_80)

and change it to:

enum_types = cls.execute_query(connection, query_enums if version.majorNumber >= 9 and version.minorNumber >= 1 else query_enums_80)

(You have to add " and version.minorNumber >= 1" after the ">= 9"). Please don't change its indentation as this is important in Python.

Save the changes and restart MySQL Workbench and it should work.

Otherwise wait for the next release.

Hi,

should i close this submission? or can i close only after the fix is avaliable?

Fixed as of MySQL Workbench 6.0.4, and here's the changelog entry:

"Forward Engineer SQL Script" would not create View definitions in the
generated SQL script when "Omit Schema Qualifier in Object Names" was set,
the resulting SQL script only contained definitions for the table. 

A workaround is to edit "db_postgresql_re_grt.py", as suggested in the
bug report.

Thank you for the bug report.

The actual changelog entry:

"Reverse Engineer Selected Schemata" could fail while migrating PostgreSQL
9.0.x, because MySQL Workbench assumed the "pg_catalog.pg_enum" table had the
"enumsortorder" column in version 9.0.x, when it was added in 9.1. 

A workaround is to edit "db_postgresql_re_grt.py", as suggested in the bug
report.