database - PostgreSQL 到 MySQL 数据迁移

Question

我正在尝试将我的 PostgreSQL 数据库及其中的所有数据移动到 MySQL 数据库，因此我正在使用 MySQL Workbench > 数据迁移工具。

在“逆向工程源代码”这一步我遇到了一个奇怪的错误:

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

此错误消息最后出现的完整错误日志是:

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

我在网上搜索了与日志中出现的(错误 42P01)相关的任何内容，但找不到任何引用。因此，如果有人可以告诉我我到底做错了什么，那将非常棒。

谢谢

Answer 1

这个错误把我带到这里。

如果你的“psqlcfg_lid_seq”实际上包括大写和小写字符，请记住 PostgreSQL 会将名称转换为所有小写以供查询。

一个基本知识是:为了进行大小写匹配查询，名称必须用双引号(")括起来，这样可以避免转换。

但是，在 MySQL Workbench 中，他们在尝试获取序列时忘记这样做。

在 db_postgresql_re_grt.py 中。在 Windows 上位于 %Program Files%\MySQL\MySQL Workbench(您的版本，例如“6.1 CE”)\modules。

在第 70 行左右，您会在变量 seq_details_query 中找到 SQL 查询，它类似于:

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

将其更改为:

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

因此可以获取序列，然后可以继续整个流程。

请注意:您可能需要重新启动 MySQL Workbench 才能使用修改后的脚本。

我很惊讶 MySQL 的家伙们仍然没有解决这个问题。也许我需要以某种方式报告错误？ :P