dumblob
Error: near line 72: too many terms in compound SELECT
This error comes from sqlite, not from this conversion script.. It seems you hit the limitations of SQLite3 (refer to Maximum Number Of Terms In A Compound SELECT Statement section on http://www.sqlite.org/limits.html ). Unfortunately this can't be influenced in this conversion script.
If this issue is not the above case, please provide your source .sql file, so that I can take a look what's going on there.
ok it is the problem what do you post thanks 👍 i splitted the inserted query in the *.sql file in 3 parts and now it work
can you add limitation in your script that automatically split the query?
@tom it's about selects and I can't see any connection to inserts.
@androidseb25 I'm not sure what you mean by splitting a query, but I'm not aware of any existing algorithm for splitting a textual form of query without knowing anything about the semantics and metadata about the data which are queried. I can't prove it right now mathematically, but my sense tells me it's simply totally impossible, so I hope you didn't mean this proposal as a joke, but you had something specific in your mind. In that case I'd like to hear about it.
I can though add a detection of the SQLite3 limitation and write out a better error message than SQLite3 does, but I don't want to do that, because it has literally nothing to do with this conversion script and in the name of the UNIX philosophy "do one thing, but do it perfectly" (separation of concerns) I'm rather reluctant to add any such detection (it's undoubtedly a responsibility of SQLite3 as you can see from the error message).
I would suggest contacting the SQLite3 development team and asking them for improving of the error message and maybe also increasing the limit for the size of queries. Or, of course, I would change the queries as such extremely long queries mean only one thing - a totally dumb SQL schema and even worse DB specialists writing such queries (or programmers using nasty ORMs without being very cautious and without enough knowledge about ORM internals).
@dumblob I think it is in fact about inserts and sqlite3's error message is misleading.
Afaict, sqlite3 is reusing the SQLITE_LIMIT_COMPOUND_SELECT limit and error message for extended inserts. It accepts 500 rows of data in the VALUES cause and quits with this error on 501 or more.
--skip-extended-insert resolves this issue for me.
In fact, with this option, I was quite impressed to produce a 26 GiB sqlite3 database file from a similarly sized MySQL database with about 10 tables by piping mysqld through your script and into sqlite3. I haven't verified all the data is correct (yikes!) but it looks good so far.
Thanks for your work on it :>
@tom thank you for your explanation and testing. It's impressive! I'm really curious whether you'll come across some issue with the SQLite3 DB converted from MySQL (be careful, SQLite3 doesn't support multiple users and thus parallel access is tricky).
Anyway, in that case we should really tell the SQLite3 team about a misleading error message.
actually, it turns out that i cannot recreate this bug with a simple test sql query. i need to investigate some more.
in any case, --skip-extended-insert is worth a try
Having the same problem here. Does anyone have some automated solution to deal with this error?
@apsg did you create the mysql dump with mysqldump --skip-extended-insert ... as mentioned in README.md?