从PostgreSQL迁移到MySQL出现错误[42703]

Question

我有一个Postgres数据库，想要将其迁移到MySQL，但是每次到了MySQL Workbench Migration Wizard的反向工程阶段时，就会出现错误。报错信息如下:

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

完整的错误日志如下：

    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 "min_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 "min_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 "min_value" does not exist;\nError while executing the query (1) (SQLExecDirectW)')"): error calling Python module function DbPostgresqlRE.reverseEngineer
Failed

我尝试按照这里置顶评论所述修改Python脚本，但是仍然出现错误。有人知道我该如何解决吗？

- Ryan G

---

你的Postgre版本号是否在9.0和9.1之间？如果是，那么这个错误报告中的说明可能会对你有所帮助。 - Obsidian Age

我看到了，但是我正在运行 10.1.3。不过还是谢谢。 - Ryan G

在PostgreSQL 10中，min_value从序列关系移动到了pg_sequence目录表中作为seqmin。但是我不太确定如何在Workbench中解决这个问题。 - Łukasz Kamiński

好的提示@ŁukaszKamiński！我在两个PostgreSQL版本之间做出了选择，所以回退到9.6版本对我很有帮助。 - featherbelly

Answer 1

修复文件 "C:\Program Files\MySQL\MySQL Workbench 6.3 CE\modules\db_postgresql_re_grt.py" 中的第70行更改为

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

- Phung Duy Phong

---

在我的 Mac 上适用。同一个文件，位于 /Applications/MySQLWorkbench.app/Contents/Resources/plugins/db_postgresql_re_grt.py。 - Jason Schock

在我的Ubuntu 16上，使用Mysql Workbench 6.3也可以正常工作（并连接到mysql 5.7）。非常感谢。文件位置为/usr/lib/mysql-workbench/modules/db_postgresql_re_grt.py。 - danipenaperez

你是如何想出这样的解决方案的？你真是个天才。 - druskacik

这个问题似乎在 Workbench 8.0.20 和 PostgreSQL 13.1.1 (Windows) 中仍然存在，而这个修复方法有效。 - Adam C

如果这个方法一开始似乎不起作用，请确保在更改.py文件后重新启动Workbench。如果我没有忘记这样做，我本可以避免一些头痛的问题。一旦我这样做了，它就像魔法般地运行了。 - Doug Johnson

PG 15，Workbench 8.0 - 仍然运行良好，感谢！ 还从这里得到了其他领域的帮助：https://bugs.mysql.com/bug.php?id=93594 cycle as is_cycled, cache_size as cache_value - glebsts

Answer 2

我有 PostgreSQL 10.5

在 db_postgresql_re_grt.py 的第80行进行替换

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()

使用：

last_value, log_cnt, is_called = cls.execute_query(connection, """SELECT last_value, log_cnt, is_called FROM "%s"."%s" """ % (schema.name, seq_name)).fetchone()
seqrelid, seqtypid, seqstart, seqincrement, seqmax, seqmin, seqcache, seqcycle = cls.execute_query(connection, "select seqrelid, seqtypid, seqstart, seqincrement, seqmax, seqmin, seqcache, seqcycle from pg_sequence where seqrelid = '%s'::regclass" % seq_name).fetchone()
min_value = seqmin
max_value = seqmax
start_value = seqstart
increment_by = seqincrement
is_cycled = seqcycle
ncache = seqcache

- ycuk