Error when migrating MySQL database to SQLite

Question

I have access to a MySQL database hosted on a remote server. I am attempting to migrate this to a local SQLite database. To do this, I am using this script, as suggested by this question. The usage is

./mysql2sqlite mysqldump-opts db-name | sqlite3 database.sqlite

I tried doing exactly that (with no dump options) and sqlite3 returned an error:

Error: near line 4: near "SET": syntax error

So far, I have found that when I only specify one of my tables in the dump options like so

./mysql2sqlite db-name table-B | sqlite3 database.sqlite

It appears to work fine, but when I specify the first table (let's call it table-A) it returns this error. I'm pretty sure it's returning this error because of the output of mysql2sqlite. The 4th line (I guess the 4th logical line, or the command that starts on the 4th actual line) of the dump file looks like this:

CREATE TABLE "Association_data_interaction" (
  "id" int(10)  DEFAULT NULL,
  ...
  "Comments" text CHARACTER SET latin1,
  ...
  "Experiment" text CHARACTER SET latin1,
  "Methods" text CHARACTER SET latin1,
  ...
);

With many other rows removed. I don't really know SQL that well, but as far as I can tell, the migration script is trying to output a dump file with commands that can create a new database, but the script has to translate between MySQL's output commands and the commands sqlite3 wants to create a database, and is failing to properly handle the text fields. I know that when I run SHOW COLUMNS; in the MySQL database the Comments, Experiment, and Methods columns are of the "text" type. What can I do make sqlite3 accept the database?

Note: I have editing access to the database, but I would much prefer to avoid that if at all possible. I do not believe I have administrative access to the database. Also, if it's relevant, the database has about 1000 tables, most of which have about 10,000 rows and 10-50 columns. I'm not too interested in the performance characteristics of the database; they're currently good enough for me.

CL. · Accepted Answer · 2014-08-14 13:56:40Z

2

That script is buggy; one of the bugs is that it expects a space before the final comma:

gsub( /(CHARACTER SET|character set) [^ ]+ /, "" )

Replace that line with:

gsub( /(CHARACTER SET|character set) [^ ]+/, "" )

answered Aug 14, 2014 at 13:56

CL.

182k18 gold badges241 silver badges282 bronze badges

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

Collectives™ on Stack Overflow

Error when migrating MySQL database to SQLite

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related