Repository navigation
Import from SQL-Dump files is broken since v3.11 #2323
Description
Details for the issue
What did you do?
I tried to import data from a SQL-Dump.
What did you expect to see?
Approx. 20,000 records imported into a table
What did you see instead?
A syntax error near "DELETE FROM table". Removing the DELETE statement resulted in a syntax error near "INSERT INTO ..."
The error was already present in v3.11, upgrading to 3.12 did not fix it.
However, after downgrading to 3.10 the import worked, as it did years before. Hence the bug was most likely introduced in 3.11
Useful extra information
DB4S v3.12.0 [built for x86_64-little_endian-llp64] on Windows 8.1 (6.3) (winnt/6.3.9600) [x86_64]
using SQLCipher Version 4.4.0 community (based on SQLite 3.31.0)
and Qt 5.12.8
Activity
chrisjlocke commentedon Jul 7, 2020
Is there any chance you could email me the dump (or zip of it). If needed, you could sanitize the data... I'd just need the lump where the error occurs.
What 'possibly' is happening, is that there is no 'standard' for creating a SQL dump file. Depending on what dumped it, it might have different quotes or formatting. Additionally, the biggest gotcha is inserting records into a table that hasn't been created yet, or due to foreign keys, a record can't be inserted, as the corresponding record hasn't been inserted yet. Its a little bit messy...
You can email it to db4s [at] chrisjlocke [dot] co [dot] uk (a poor attempt to obfuscate an email address that probably isn't worth it anymore...)
Improvements (or not ... depending on your viewpoint) were made to importing back in 3.11, and tweaks have been made.
Thanks for reporting it, and we'll have a look to see what can be fixed.
HarryDe commentedon Jul 7, 2020
@chrisjlocke : I just sent you an email
chrisjlocke commentedon Jul 8, 2020
This appears to be because the .sql file being imported is encoded as UTF-8 (BOM).
If converted to UTF-8 (Notepad++ does this in a simple menu-click) then it imports fine. 👍
Maybe DB4S could detect this file format when importing and do a pre-import cough? Slightly over my head for these sort of conversions and what DB4S can detect. I don't know if DB4S is reading the whole file as one long string and therefore choking, or just isn't expecting BOM encoded files...
Fix SQL import of files with BOM
MKleusberg commentedon Jul 8, 2020
Should be fixed in the next nightly build. Can you give that a try, @HarryDe? 😄
HarryDe commentedon Jul 8, 2020
HarryDe commentedon Jul 9, 2020
Can you make me a 64-msi? The latest 64 nightly build is of 6. July.
MKleusberg commentedon Jul 9, 2020
Oh you're right. @justinclift Can you check if there was any build failure?
justinclift commentedon Jul 9, 2020
Oops. I nuked the built win .msi's and logs about an hour ago from the build server Win VM, to free up disk space. 😦
As a workaround, I've just kicked off the Win64 build process again manually. If that it completes ok then there should be updated Win64 binaries on the nightlies server in about 30-40 mins. 😄
justinclift commentedon Jul 9, 2020
The Win64 build process just finished without error. 😄
@HarryDe New .msi's are here:
https://nightlies.sqlitebrowser.org/latest/
HarryDe commentedon Jul 9, 2020
Works now with UTF-8-BOM
Thanks a lot!
HarryDe commentedon Jul 9, 2020
MKleusberg commentedon Jul 9, 2020
Awesome 😄 Thanks again for reporting this!
Fix SQL import of files with BOM