MySQL Bugs: #79217: ReverseEngineer - from PostgreSQL

Bug #79217	ReverseEngineer - from PostgreSQL - syntax error at or near "-"
Submitted:	11 Nov 2015 2:44	Modified:	3 Jun 2016 13:29
Reporter:	Thomas Khyn	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Migration	Severity:	S2 (Serious)
Version:	6.3.5. build 201 CE 64 bits	OS:	Windows (7 pro 64 bits)
Assigned to:		CPU Architecture:	Any

Description:
When migrating a PostgreSQL (9.3) database generated with tryton (http://www.tryton.org), an error is raised during the reverse engineering process.

I have not experienced this error with other pg databases on the same server (the hmailserver database was migrated fine).

Here is the log in the Reverse Engineer window:

Starting...
Connect to source DBMS...
- Connecting...
Connecting to postgresql@DRIVER=PostgreSQL ANSI(x64);SERVER=127.0.0.1;PORT=5432...
Opening ODBC connection to DRIVER=PostgreSQL ANSI(x64);SERVER=127.0.0.1;PORT=5432;DATABASE=tryton_dummy;UID=postgres;UseDeclareFetch=1;...
Connected
Connect to source DBMS done
Reverse engineer selected schemas....
Reverse engineering public from tryton_dummy
- 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 350, 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: ('42601', '[42601] ERROR: syntax error at or near "-";\nError while executing the query (7) (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("('42601', '[42601] ERROR: syntax error at or near "-";\nError while executing the query (7) (SQLExecDirectW)')"): error calling Python module function DbPostgresqlRE.reverseEngineer
ERROR: Reverse engineer selected schemas: ProgrammingError("('42601', '[42601] ERROR: syntax error at or near "-";\nError while executing the query (7) (SQLExecDirectW)')"): error calling Python module function DbPostgresqlRE.reverseEngineer
Failed

If I find out how to attach files to this ticket I'll attach the log file with debug3 log level and a dummy database.

Thanks for looking at it.

How to repeat:
Attempt to migrate a tryton database hosted on a PostgreSQL 9.3.x server.

Extended log

Attachment: wb.log (application/octet-stream, text), 30.92 KiB.

Dummy tryton database that fails to be migrated

Attachment: tryton_dummy.backup (application/octet-stream, text), 595.13 KiB.

Hi Thomas, thank you for the bug report. I can't restore db from file that you attached. Can you provide me SQL file that will create schemas?

Hi Milosz,

Thanks for replying.

I have no problem restoring the tryton_dummy.backup file I provided (this is a raw PostgreSQL database dump from v 9.3.10) from a psql console using the command:

whatever_database=# \i /path/to/download/dir/tryton_dummy.backup

Would you be able to try that? If it still does not work I can provide a tar dump from PostgreSQL.

Thomas

Thanks, I can repeat now.

Posted by developer:
 
Fixed as of the upcoming MySQL Workbench 6.3.7 release, and here's the changelog entry:

The PostgreSQL migration wizard could fail to migrate PostgreSQL databases
that were generated with tryton.

Thank you for the bug report.

And thank you very much for the fix!