"not an error" on SQL import #1519
Description
Details for the issue
What did you do?
I exported multiple tables from a database into a .sql file, and tried to import it into an existing database.
What did you expect to see?
A message indicating that the import had been completed, or an indication of why the import failed.
What did you see instead?
Upon importing, I received "Error importing data: Error in statement #3: not an error. Aborting execution and rolling back." I copied the content of the .sql file into the "Execute SQL" tab and executed it there, where I was given a better indication of where the issue lay. The file includes references to other tables which were also being imported, and as the referenced tables were created later in the file, this seems to have caused the issue. An example of the SQL that caused the issue is "CONSTRAINT FK_1332519100 FOREIGN KEY(ownerId) REFERENCES libk_player(id) ON DELETE CASCADE ON UPDATE RESTRICT", where the "REFERENCES libk_player" caused issue as libk_player was created later in the file. I had to move the "CREATE TABLE" query for the referenced tables above the queries that referenced them, and that resolved the issue.
Useful extra information
The info below often helps, please fill it out if you're able to. :)
What operating system are you using?
- Windows: (version: 1803 Build 17134)
- Linux: ( distro: ___ )
- Mac OS: ( version: ___ )
- Other: ___
What is your DB4S version?
- 3.10.1
- 3.10.0
- 3.9.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
Activity
[-]"no error" on SQL import[/-][+]"not an error" on SQL import[/+]justinclift commentedon Aug 26, 2018
As a thought, try with the latest nightly build instead:
https://nightlies.sqlitebrowser.org/latest/
We've rewritten large parts of the importing code, so there's a decent chance you'll have a better outcome from that. 😄
VortixDev commentedon Aug 26, 2018
Thanks for the tip! I gave it a try, and for the original file that I was using that seems to work better: the import still produces an error due to the fact that items are inserted into a table which references a non-existent table (as my version completes the INSERTs directly after the table is created, instead of running all INSERTs after all CREATEs).
I tried to export the same data and import it using the nightly build for both operations, however that seems to still have some issues. I got a "FOREIGN KEY constraint failed" error, however I only got that information from running the query in execute: the actual import feature only gave me the "not an error" message in this case. It seems to me that the issue is having a foreign key referencing another table, but when the data is inserted into the referencing table before it is inserted into the referenced table this causes the error. After re-ordering the INSERTs such that referenced tables are first, it worked.
It would seem that to have this work smoothly in these cases, the INSERTs need to be re-arranged at either import or export so that any tables which are deepest in the reference chain are inserted first.
justinclift commentedon Aug 26, 2018
Yeah. Thinking about it, that definitely makes sense. I suspect we'll probably not add any kind of "rearrange the table before import" stuff, at least not in the near future.
But, we should catch the error better/properly instead, then display an appropriate guidance message/thing. eg something so the person doing the import knows what the problem is and how to fix it.
Show better error messages when executing SQL scripts
MKleusberg commentedon Aug 27, 2018
Thanks for reporting this issue, @VortixDev! I have already pushed a fix for the error message thing. So in tomorrow's nightly build you should get a proper error message.
Tomorrow or so I will also take a look at the actual problem here which is the order of inserts when there are foreign key constraints. This should actually be fairly easy to fix because we can just turn off foreign keys during the import and then turn them back on when we're done. This way we only get an error message if there is an actual foreign key problem but not if there is only a transient one during import.
Defer foreign keys during SQL import
MKleusberg commentedon Aug 28, 2018
I believe both issues, the error message and the failing import itself, are fixed now. Can you download tomorrow's nightly build and see if it's working for you as well, @VortixDev? 😄
MKleusberg commentedon Sep 28, 2018
Did you have a chance to test this yet, @VortixDev?
VortixDev commentedon Oct 1, 2018
@MKleusberg Sorry for the delay in getting back to you! I've done some more testing, and it seems to work much better: I get a "no such table" error when the INSERT runs for a referencing table instead of the "not an error" message.
As a side note, when I get the error by running the code in the "execute SQL" tab, the error states the line number where the closing bracket is for the CREATE TABLE statement, whilst actually refering to the statement after that, which is the INSERT.
MKleusberg commentedon Jun 3, 2020
Closing this because it looks like this issue is resolved and we haven't heard back anything for a long time. If there's any remaining problem with this, please just re-open this issue any time 😄