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.
