dbupgrade.py: IntegrityError: FOREIGN KEY constraint failed in db_v37

My situation will be a weird one, since my lazylibrarian updates got stalled without my noticing it, and once I addressed the issue that was preventing them, the dbupdate script was faced with the task of updating the db from 21 to 44.

It proceeded gamely, but failed in the updates for v37:

Sun Nov 18 03:03:23 2018 v0: Updating database to version 44, current version is 21
Sun Nov 18 03:03:23 2018 v22: complete
Sun Nov 18 03:03:23 2018 v23: complete
Sun Nov 18 03:03:23 2018 v24: complete
Sun Nov 18 03:03:23 2018 v25: complete
Sun Nov 18 03:03:23 2018 v26: complete
Sun Nov 18 03:03:23 2018 v27: complete
Sun Nov 18 03:03:23 2018 v28: complete
Sun Nov 18 03:03:23 2018 v29: Upgrading 818 authors, 129189 books
Sun Nov 18 19:21:00 2018 v29: Upgrading 25169 series members
Sun Nov 18 19:21:34 2018 v29: complete
Sun Nov 18 19:21:34 2018 v30: complete
Sun Nov 18 19:21:34 2018 v31: complete
Sun Nov 18 19:21:34 2018 v32: Upgrading 5593 series counters
Sun Nov 18 19:21:34 2018 v32: complete
Sun Nov 18 19:21:34 2018 v33: complete
Sun Nov 18 19:21:34 2018 v34: complete
Sun Nov 18 19:21:34 2018 v35: complete
Sun Nov 18 19:21:34 2018 v36: complete
Sun Nov 18 19:21:34 2018 v37: Adding delete cascade constraint to existing tables
Sun Nov 18 19:21:41 2018: Unhandled exception in database upgrade: Traceback (most recent call last):
  File "/opt/lazylibrarian/lazylibrarian/dbupgrade.py", line 209, in dbupgrade
    upgrade_function(myDB, upgradelog)
  File "/opt/lazylibrarian/lazylibrarian/dbupgrade.py", line 1112, in db_v37
    myDB.action('ALTER TABLE seriesauthors RENAME TO temp_table')
  File "/opt/lazylibrarian/lazylibrarian/database.py", line 46, in action
    return self._action(query, args, suppress)
  File "/opt/lazylibrarian/lazylibrarian/database.py", line 56, in _action
    sqlResult = self.connection.execute(query)
IntegrityError: FOREIGN KEY constraint failed

matt@plugh:~$

To help with identifying and fixing issues, please include as much information as possible, including:

LazyLibrarian version number (at the bottom of config page)

ddeeb9faedff7c9b93d1c50fb98a13ca1e15e1b3 (the version it had long been stalled at was 8c53a90f8f6d13053a89d9d6a072ee120188a69e)

Operating system used (windows, mac, linux, NAS type)

linux

Interface in use (default, bookstrap)

legacy

Which api (Goodreads, GoogleBooks, both)

Goodreads

Source of your LazyLibrarian installation (git, zip file, 3rd party package)

git

Relevant debug log with api keys and any passwords redacted

I have to restart the server and wait through hours of some kind of update before it gets to the dbupgrade part. I can try to get a debug log with that but, in the meantime, there is the dbupgrade.log I quoted above, and here is the lazylibrarian.log from the end where the dbupgrade.py error occurred: lazylibrarian.log.zip

Please note - usually a single line of log is not sufficient. The lines just before the error occurs can give useful context and greatly assist with debugging.

There is a built-in debug log creator on the logs page which makes it easy to provide this information

To use it, first go to the log page and make sure debug logging is OFF
Now press the "Start debug log" button
Go and do whatever you need to recreate the error
Go back to the log page and press "Save debug log"
You can now turn debug logging off again if you want
The saved log file will be found in your lazylibrarian log directory called debug.zip
It will contain all lines logged since you pressed "Start debug log" with any passwords and api keys removed
Attach the zip file to your bug report.

As your database is sooo old I would suggest just starting with a new one rather than rebuilding it. It can be fixed manually but it may not be a trivial task. The current error you are getting is that we expect entries in the "seriesauthors" table to be unique, and in your database they are not. In addition some of the later updates are quite time consuming as you have seen.

You will lose your "wanted" books doing this, but if you want to keep them, export them from your old database first. You can start lazylibrarian ignoring the upgrade to do this, then go to the manage page and export csv.

If you stop lazylibrarian and delete your database, then restart lazylibrarian you will have a new clean database at level 44 (better still, switch to the new source over on gitlab and get a new level 46 database). Once you have a new database, run libraryscan on ebooks, magazines, audiobooks as appropriate to repopulate. If your cache is fairly up-to-date we will read most of the info from there, so it should be fairly quick, depending on how many authors/books. Once the database is rebuilt, go back to manage page and import csv to mark your wanted books as wanted again. Hope this helps.

DobyTang / LazyLibrarian