New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

"not an error" on SQL import #1519

Open
VortixDev opened this Issue Aug 26, 2018 · 7 comments

Comments

Projects
None yet
4 participants
@VortixDev
Copy link

VortixDev commented Aug 26, 2018

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

@VortixDev VortixDev changed the title "no error" on SQL import "not an error" on SQL import Aug 26, 2018

@justinclift

This comment has been minimized.

Copy link
Member

justinclift commented 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

This comment has been minimized.

Copy link
Author

VortixDev commented 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

This comment has been minimized.

Copy link
Member

justinclift commented Aug 26, 2018

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.

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.

@justinclift justinclift added the bug label Aug 26, 2018

MKleusberg added a commit that referenced this issue Aug 27, 2018

Show better error messages when executing SQL scripts
This commit fixed wrong error messages in the code we use whenever
multiple SQL statements are executed at once programmatically, most
noticeably in the SQL import. In the case of an error we would first
clean up and rollback and only then recover the error message which
usually would be lost by then. With the changes in this commit the first
thing we do is to recover the error message and only then clean up.

See issue #1519.
@MKleusberg

This comment has been minimized.

Copy link
Member

MKleusberg commented 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.

@MKleusberg MKleusberg self-assigned this Aug 27, 2018

MKleusberg added a commit that referenced this issue Aug 28, 2018

Defer foreign keys during SQL import
When importing an SQL file, defer foreign keys until the import is
finished. This is necessary if there are foreign key constraints in the
imported table and the order of the INSERT statements doesn't match the
hierarchy of the constraints.

Also perform an integrity check at the end of the import.

See issue #1519.
@MKleusberg

This comment has been minimized.

Copy link
Member

MKleusberg commented 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

This comment has been minimized.

Copy link
Member

MKleusberg commented Sep 28, 2018

Did you have a chance to test this yet, @VortixDev?

@VortixDev

This comment has been minimized.

Copy link
Author

VortixDev commented 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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment