Background

From time to time the following error occurs in DeltaShell:

SQLiteException occurred
SQL logic error or missing database
at most 64 tables in a join

This indicates that Nhibernate is attempting to fetch data from too many different tables at the same time. Note that it doesn't matter at all if there is any data in those tables, all that matters is how big the biggest class hierarchy is.

That hierarchy usually grows if you have many plugins loaded, for example many plugins have ProjectItems, so the ProjectItem hierarchy can get pretty big! Nhibernate by default attempts to load all objects at once, but as the data is spread over many tables it uses SQL JOINs to do so. This is fine, up to the point where it gets above (or equal) to 64 joins, as that is the limit of the database driver: SqlLite.

To prevent Nhibernate from joining so many tables together, you have to add instructions in the mapping files to prevent this. Unfortunately there doesn't seem to be a simple permanent fix, and instead as the relationships change and grow, this problem resurfaces every few months, sometimes hidden and sometimes as a big exception whenever you try to load any dsproj. Do note however that in production environments this issue is much less likely to occur, as usually only a few plugins are shipped in a single install.

Solution

There seem to be two options to prevent these excessive joins:

  1. Make relationships lazy (not recommended -> can introduce problems with code not compatible with laziness)
  2. Set (non-lazy) relationships to fetch=select, Example:
    <many-to-one name="CalculationSelection"
                 lazy="false"

                 fetch="select"

                 cascade="all-delete-orphan"
                 class="DeltaShell.Plugins.MorphAn.Models.CalculationSelection"
                 column="calculation_selection_id"/>

Recommended workflow:

  1. Turn 'just my code' off just prior to opening an offending dsproj.
  2. Determine the extend of the problem: copy the sql query causing the exception (part of command object) into a text editor
  3. Count the number of occurrences of the string 'JOIN' in the file (as a baseline) -> typically between 64 and 100
  4. Now the hard part: scan through that query and identify plugins / classes that seem to be occurring a lot, or combinations you wouldn't expect
  5. Apply fetch=select on one or more (non-lazy!) relationships where you think it would reduce the number of joins (see rev #21942 as an example)
  6. Reload the project; if the exception still occurs check if the number of JOIN has actually lessened (using text editor)
  7. If not, revert, if it has, find another relationship to change.
  8. Continue until no exception (preferably a bit longer: eg <40 joins!)
  • No labels