1

I am trying to move my PostgreSQL database with all the data inside it to a MySQL database so I am using MySQL Workbench > Data migration tool.

On the "Reverse Engineer Source" step I got a strange error:

ERROR: Reverse engineer selected schemata: ProgrammingError("('42P01', '[42P01] ERROR: relation "public.psqlcfg_lid_seq" does not exist;\nError while executing the query (7) (SQLExecDirectW)')"): error calling Python module function DbPostgresqlRE.reverseEngineer Failed

The complete error log where this error message appears at its end is:

Starting...
Connect to source DBMS...
- Connecting...
Connecting to ...
Opening ODBC connection to DSN=InventoryDBDS...
Connected
Connect to source DBMS done
Reverse engineer selected schemata....
Reverse engineering public from InventoryDB
- Reverse engineering catalog information
Traceback (most recent call last):
  File "C:\Program Files\MySQL\MySQL Workbench CE 6.0.6\modules\db_postgresql_re_grt.py", line 335, in reverseEngineer
return PostgresqlReverseEngineering.reverseEngineer(connection, catalog_name, schemata_list, context)
File "C:\Program Files\MySQL\MySQL Workbench CE 6.0.6\modules\db_generic_re_grt.py", line 228, in reverseEngineer
catalog = cls.reverseEngineerCatalog(connection, catalog_name)
File "C:\Program Files\MySQL\MySQL Workbench CE 6.0.6\modules\db_generic_re_grt.py", line 388, in reverseEngineerCatalog
cls.reverseEngineerSequences(connection, schema)
File "C:\Program Files\MySQL\MySQL Workbench CE 6.0.6\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 CE 6.0.6\modules\db_generic_re_grt.py", line 76, in execute_query
return cls.get_connection(connection_object).cursor().execute(query, *args, **kwargs)
pyodbc.ProgrammingError: ('42P01', '[42P01] ERROR: relation "public.psqlcfg_lid_seq" does not exist;\nError while executing the query (7) (SQLExecDirectW)')

Traceback (most recent call last):
File "C:\Program Files\MySQL\MySQL Workbench CE 6.0.6\workbench\wizard_progress_page_widget.py", line 192, in thread_work
self.func()
File "C:\Program Files\MySQL\MySQL Workbench CE 6.0.6\modules\migration_schema_selection.py", line 160, in task_reveng
self.main.plan.migrationSource.reverseEngineer()
File "C:\Program Files\MySQL\MySQL Workbench CE 6.0.6\modules\migration.py", line 335, in reverseEngineer
self.state.sourceCatalog = self._rev_eng_module.reverseEngineer(self.connection,     self.selectedCatalogName, self.selectedSchemataNames, self.state.applicationData) SystemError: ProgrammingError("('42P01', '[42P01] ERROR: relation "public.psqlcfg_lid_seq" does not exist;\nError while executing the query (7) (SQLExecDirectW)')"): error calling      
Python module function DbPostgresqlRE.reverseEngineer
ERROR: Reverse engineer selected schemata: ProgrammingError("('42P01', '[42P01] ERROR: relation "public.psqlcfg_lid_seq" does not exist;\nError while executing the query (7) (SQLExecDirectW)')"): error calling Python module function DbPostgresqlRE.reverseEngineer  Failed

I've searched the web for anything related to (error 42P01) appearing in the log, but couldn't find any reference. So if someone can please tell me what exactly I am doing wrong here that will be really great.

Thanks

Improve this question

2 Answers 2

Reset to default
4

This error bring me here.

If your "psqlcfg_lid_seq" actually including both uppercase and lowercase character(s), remember that PostgreSQL will convert the name into ALL lowercase for query.

A basic knowledge is: In order to perform a case matched query, the name must be wrapped by double quotation marks ("), so the convertion will be avoided.

However, in MySQL Workbench, they forget to do that when try to fetch sequences.

In db_postgresql_re_grt.py. Located in %Program Files%\MySQL\MySQL Workbench (Your version, for example "6.1 CE")\modules on Windows.

Line around 70, you will found the SQL query in the variable seq_details_query, it will be something like:

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

Change that to:

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

So the sequences can be fetched, and so whole flow can be proceed.

Notice that: You may need to restart MySQL Workbench to use modified scripts.

I'm surprised MySQL guys still not fix this problem. Maybe i need report the bug somehow? :P

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

Comments

1

42P01 is a generic error meaning the object doesn't exist.

In this case it's the sequence public.psqlcfg_lid_seq that does not exist. Based on the series of error messages, the error happens when the tool tries to query the attributes of that sequence (with SELECT ... FROM schema_name.sequence_name)

Presumably this sequence is still referenced somewhere in the database even if ot no longer exists. In theory there are safeguards in PostgreSQL against this situation (dependency tracking) but I believe they depend on your server version and maybe on the specifics of the dependency.

To find the references to this sequence, one approach would be to dump the database schema to an SQL text file (with pg_dump -s) and search for the text psqlcfg_lid_seq within it.

Once found, presumably some ALTER statements may be able to remove the references.

Improve this answer

3 Comments

What shall I ALTER exactly? Can you please provide me a small example on what shall be altered?
I have found many entries for psqlcfg_lid_seq...for example: CREATE SEQUENCE "PSQLCFG_LID_seq" START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE public."PSQLCFG_LID_seq" OWNER TO postgres;
@MChan: based on the latest comment, it appears now as a case sensitivity problem. PSQLCFG_LID_seq must be enclosed in double quotes since it has a mix of upper and lower case, whereas in the error message it appears in all lower case: ERROR: relation "public.psqlcfg_lid_seq" does not exist

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.