Reverse engineering error during migration of SqLite to MySql using MySql workbench

Question

I am migrating an SQLite database to MySql using the MySQL workbench migration wizard (Community Edition). I have downloaded the ODBC drivers for SQLite. I am able to connect to SQLite and retrieve the schema (See Figure One).

However, an error is produced during the reverse engineering phase of migration (See Figure Two). In this example the error reads "ERROR: Line 1: syntax error, unexpected TEXT_STRING. Statement skipped." Because reverse engineering terminated with errors the schema is not reverse engineered.

Does anyone know how to resolve the issue?

Thank you, Camille

Figure One.

---

Figure Two.

Answer 1

In my experience, Workbench used to be quite buggy, and probably this is a bug too. In case no-one answers with a real solution, I suggest you look at this SO question: Quick easy way to migrate SQLite3 to MySQL?

I'm only afraid it might not be that quick and easy. Most methods revolve around dumping your SQLite database to an SQL script, converting the script to MySQL syntax and importing/executing it in MySQL.

Answer 2

From what I just experienced, that kind of error will happen if the syntax from the database file in entry (here SQLite) isn't compatible with MySQL. In my case, it was reporting "syntax error, unexpected IDENT_QUOTED, expecting ')'" when facing the word "autoincrement" (which doesn't exist in MySQL syntax).

A way to do your migration would be to dump your SQLite DB in a .sql script, modify it to respect MySQL's syntax and import it in MySQL.

See Shalmanese's answer here https://stackoverflow.com/a/87531/1324798 for a list of differences between syntaxes and a script to convert it.

Sadly, I don't know if there is still a way to use Workbench's migration wizard with this kind of problem.

Good luck.