The BIGGEST problem I had with importing data from MySQL to SQLite is the
table definitions.
If you do two dumps, one specifically for table definitions, the other for
the actual data to be imported, you could get a script to handle the table
definition file to make it conform to what SQLite can use, and the raw data
is sitting there ready to be imported. Most of the work is going to be
against the table definitions as MySQL dumps information about what MySQL
engine needs to be used, while SQLite has no such necessity and breaks.
The work to be done would be to do a multi-pass "string replacement".
Remove the text that is superficial to SQLite that MySQL requires, like the
engine used by MySQL. Remove anything that's MySQL language specific in
regards to table definitions, and wipe them or swap them for a generic TEXT
or NUMERIC definition. Etc. If you run into problems with the import,
it'd be easy enough modify the script and rerun the job.
I learned in my venture that the #! 000000 numbers represent to the MySQL
engine that the 000000 is a version number that must be met by the
importing engine for the command to be executed. So (off the cuff) if the
export was done on MySQL 1.2, and the importer is MySQL 1.1, any line that
has #! 010200 {some command} would not execute on the 1.1 version. Some of
these statements still must be executed for SQLite to behave as expected,
so you can't just blindly remove all #! lines.
On Wed, Aug 7, 2019 at 12:13 PM Thomas Kurz <sqlite.2...@t-net.ruhr> wrote:
> Dear SQLite team,
>
> I suppose I am not the only one having to convert between MySQL/MariaDB
> and SQLite databases every now and then. I know there are converters for
> MySQL dumps but none of any I have ever tried did work nearly reliable.
>
> So my suggestion would be to add an import feature to the CLI that allows
> to directly import MySQL/MariaDB dumps into an SQLite database keeping as
> many information as possible. As SQLite already has a complete SQL parser I
> expect much better results than with existing converters.
>
> Kind regards,
> Thomas
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
