Keep getting 'file is encrypted or is not a database' error

[SwethaEnixta]

I tried the same commands as mentioned: mysqldump --no-data -u user -pxxx dbname > dbname_dump.sql ./mysql2sqlite.sh dbname_dump.sql | sqlite3 dbname.db

But get Error: near line XXX: file is encrypted or is not a database

What is the intermediate step that needs to be done which can avoid this error ??

[dumblob]

Please first run mysqldump --no-data -u user -pxxx dbname > dbname_dump.sql and check, whether the newly created file dbname_dump.sql contains SQL commands (if you're unsure, consider copying the content of the dbname_dump.sql file e.g. to https://pastebin.com and post here the link, but only if it doesn't contain any secrets like passwords or personal information). I suppose, that this is the culprit as it seems the file dbname_dump.sql doesn't contain valid SQL commands (maybe the file is even completely empty).

If the file dbname_dump.sql contains SQL commands as expected, then you can run the second command ./mysql2sqlite.sh dbname_dump.sql | sqlite3 dbname.db and in case you'll get the error again, please just post here more information about the MySQL DB you're using (version, which purpose) and which sqlite version you're using.

[SwethaEnixta]

I have checked the dump file. It has valid SQL commands, here is a small snippet of it, with table & col names renamed sqldump $ mysql --version mysql Ver 14.14 Distrib 5.7.20, for macos10.12 (x86_64) using EditLine wrapper Server version: 5.7.20 MySQL Community Server (GPL)

$ sqlite3 --version 3.19.3 2017-06-27

[dumblob]

Ok, the versions of MySQL and sqlite3 should be fine. In the snippet you provided I can see one issue which is not yet supported by mysql2sqlite - namely the construct USING BTREE (there is already an issue for that: https://github.com/dumblob/mysql2sqlite/issues/11 ). After removing it it works like a charm.

Could you please try removing everywhere USING abc and then post here whether the conversion worked afterwords?

[SwethaEnixta]

Thanks @dumblob. Yes, most of the tables were migrated after I removed the "USING BTREE" construct. Did get few issues including "Error: near line 235: near "ON": syntax error" and "Error: near line 960: no such table: main.TableXXXX"

For the 1st error, does the script support "timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" ?

[dumblob]

Yes, most of the tables were migrated ...

Well done. We're getting closer :wink:.

For the 1st error, does the script support "timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP" ?

Yes it does, but there might be circumstances when it doesn't work reliably. Could you again post a snippet (few lines before and few after) of the resulting SQL file with the line 235 and another snippet with the line 960 and also corresponding snippets of the original SQL file?

[SwethaEnixta]

The interesting thing is that, line 235 corresponds to a comment line & line 960 is part of a create table statement which got migrated fine. So, not really certain which line it is actually complaining about. The tables mentioned in the error statement have not been migrated though. I have pasted a sample sql of one of those tables here. Will I be able to migrate only 2 tables into an existing database without disturbing its contents ?

Thanks again for all the inputs :)

[dumblob]

There was indeed a mistake in the processing of the MySQL specialty ON UPDATE CURRENT_TIMESTAMP. I've updated the script a bit and pushed a new revision. Feel free to test it. It just ignores the autoupdating timestamp, but I plan to automatically generate a trigger, that will update the timestamp accordingly - something along the lines (stolen from stackoverflow):

CREATE TRIGGER [UpdateLastTime]
AFTER UPDATE
ON Package
FOR EACH ROW
BEGIN
UPDATE Package SET LastUpdate = CURRENT_TIMESTAMP WHERE ActionId = old.ActionId;
END

The interesting thing is that, line 235 corresponds to a comment line & line 960 is part of a create table statement which got migrated fine.

That's because sqlite shows sometimes just the top-most context in which the error was encountered. This time I assume it was the context of the CREATE TABLE statement.

Will I be able to migrate only 2 tables into an existing database without disturbing its contents ?

Depending on the content ;) This script is quite dumb and doesn't guarantee that, but it works quite ok for most cases - just look how many people use it and look at the issues we have. Up until now it's mostly about missing functionality than about disturbing the contents.

[SwethaEnixta]

Thats great! The script surely is the most useful one available that I've seen - Thanks for keeping it in action! Thanks a ton for all the help Cheers 👍 :-)