Importing Records With Existing Data Creates 'Invalid File Format' Error And Hangs DB4S

[chrisjlocke]

Details for the issue

This semi-relates to an issue raised in #1585. Upon further testing, a reproducible set of steps was identified.

When importing data into a table, if an imported record fails a constraint check (eg, primary key violation, eg, importing record #1 again) DB4S throws up a 'invalid file format' error, then a further 'the system is busy - do you want to cancel?' dialog. If the user presses No, DB4S locks up*.

*DB4S didn't come out of the 'not responding state'

Tried the alpha, nightly, and the recent 'special build' for #1575.
See screencast: https://screencast-o-matic.com/watch/cF60IJYAsP

If record #1 is removed from the table and the import retried, then DB4S reports the same error, but this time on record #2. This confirms something. Not sure what. But something.

What did you do?

Imported records from a CSV file, which was from an exported table. The import was into the same table, so records were known to violate the primary key.

What did you expect to see?

The records known to violate the primary key would be ignored.

What did you see instead?

DB4S got kicked in the nuts, fell down on its knees and didn't get back up again.

Useful extra information

Please see the below screencast:
https://screencast-o-matic.com/watch/cF60IJYAsP

Note clicking Yes or No both locks up DB4S. So you're stuffed either way. 😆

The info below often helps, please fill it out if you're able to. :)

What operating system are you using?

• Windows: ( version: 10_ )
• Linux: ( distro: ___ )
• Mac OS: ( version: ___ )
• Other: ___

What is your DB4S version?

• 3.11.0-alpha*
• 3.10.1
• Other: ___

Did you also

• Try out the latest nightly version: https://github.com/sqlitebrowser/sqlitebrowser#nightly-builds
• Search for an existing similar issue: https://github.com/sqlitebrowser/sqlitebrowser/issues?utf8=%E2%9C%93&q=is%3Aissue%20

[MKleusberg]

Thanks Chris! That's a good find and a very helpful error description 👍 I think I have fixed both issues, wrong error message and crash. Can you check tomorrow's nightly?

I have also already cherry-picked this commit to the v3.11.x branch as it fixes an important regression which was introduced after 3.10.1 - it also doesn't change any user-visible strings, so no translation update needed 😄

[chrisjlocke]

Credit goes to @antonybits who mentioned the error in #1585, but in his case it raised different errors.... so we'll see what you've corrected and how far it now gets! 😉

[chrisjlocke]

HAve just tried the latest nightly, and while there is a more 'valid' error:

the import terminates.

Would it be possible to continue, or because we've received an error, and potentially that error could be anything, its 'safer' to terminate?
If the former, then would it be also possible to ignore certain errors?

Theoretically, if I've deleted some records but have a table/database backup, I could import just the missing records. I can't do that currently, as the import terminates at an error. In an ideal world, DB4S could throw up a 'Retry, Ignore, Cancel' dialog...

This could give the user a chance to open the database in another instance to correct the error, and retry.

[chrisjlocke]

Would still like an answer to the above, but I'll close this issue, as the main problem has been resolved. The above question is more in line with answering #1585.

[MKleusberg]

Thanks for testing, Chris! You're quite right about the other point. I think your dialog would also need a 'Remember this action' checkbox or some similar option for larger CSV files. Maybe we also need to distinguish between different sorts of errors for that - but that is quite tricky because we only get a very vague error code from SQLite and the error string as seen in the message box. The first probably doesn't contain enough information on the error to be of any help here and the second is subject to change and hard to parse. I guess the easiest solution is to just end the import as we do now but add a dropdown box to allow changing the ON CONFLICT strategy, just as @antonybits suggested in #1585. The only downside is that you might have to start the import a second time if you didn't get the settings right on the first run. But since the import is so much faster now... 😝