Autoincrementing formatter not locking all required tree tables

[melton-jason]

Hi again,

I have another problem, when I try to edit the taxon tree (not the other trees), I have an error:

"Table 'taxontreedef' was not locked with LOCK TABLES"

In the logs I found:
field "_tableName" does not exist in <class 'specifyweb.specify.models.Taxon'>

I cannot reproduce this error in my test database, it seems that the taxon tree is not locked correctly ? I checked using specify6 and it doesn’t look locked by another user (don’t know how to get this info in the database)

https://discourse.specifysoftware.org/t/error-when-editing-taxon-tree-in-specify7/1359

Describe the bug

The problem is occurring as a result of how Specify 7 handles auto-incrementing fields in a UIFormatter while creating a new tree record which has a field formatted as auto-incrementing.
More specifically, it is occurring because during the time that the new record is being created, Specify is locking the table (Taxon in this case), and all tables which have foreign keys which reference the main table.

However, to determine which tree to insert the Taxon record into, Specify does a SQL Inner Join on the discipline and taxontreedef tables while the Taxon (and related) tables are locked.

From MariaDB Lock Tables Documentation

While a connection holds an explicit lock on a table, it cannot access a non-locked table. If you try, the following error will be produced:
ERROR 1100 (HY000): Table 'tab_name' was not locked with LOCK TABLES

To Reproduce

To reproduce the error, a UIFormatter on the Taxon table must be established with a numeric, autoincrementing/autonumbering field.

For example, the following is a formatter for the text1 field which has the pattern TAX-###, where # will be any digit which is supposed to be automatically set.

  <format system="false" name="TEST_TAXON_INCR" class="edu.ku.brc.specify.datamodel.Taxon" fieldname="text1">
  <autonumber>edu.ku.brc.af.core.db.AutoNumberGeneric</autonumber>
    <field type="constant" size="3" value="TAX"/>
    <field type="separator" size="1" value="-"/>
    <field type="numeric" size="3" inc="true"/>
  </format>

Once the format has been established, attempting to create any Taxon record will result in the OperationalError: Table 'taxontreedef' was not locked with LOCK TABLES.

*Editing Taxon records did not raise the error.

Desktop:

• OS: Ubuntu 22.04.3
• Browser: Chrome
• Specify 7 Version: v7.9.1

Notes

sanitized_Specify_7_Crash_Report.txt

Reported By

Institut de Recherche pour le Développement

[specifysoftware]

This issue has been mentioned on Specify Community Forum. There might be relevant details there:

https://discourse.specifysoftware.org/t/error-when-editing-taxon-tree-in-specify7/1359/5

[melton-jason]

Developer Notes

Following the Traceback provided in the crash report, the error is being raised as the direct result of the following line:

specify7/specifyweb/specify/uiformatters.py

Line 145 in 2e5ca79

biggest = filtered_objs[0] # actual lookup occurs here

It makes sense we are receiving a database error here: Django QuerySets are lazy.

Due to the nature of the error: Table 'taxontreedef' was not locked with LOCK TABLES, I investigated which tables were locked at the time the error is being thrown.

+----------+--------------------------+--------+-------------+
| Database | Table                    | In_use | Name_locked |
+----------+--------------------------+--------+-------------+
| specify  | taxonattachment          |      1 |           0 |
| specify  | taxon                    |      2 |           0 |
| specify  | determination            |      1 |           0 |
| specify  | taxoncitation            |      1 |           0 |
| specify  | collectingeventattribute |      1 |           0 |
| specify  | commonnametx             |      1 |           0 |
+----------+--------------------------+--------+-------------+

To get a better idea of the SQL being performed, the query can be extracted from the QuerySet. Which equates to the following (truncated for brevity)

SELECT ... FROM `taxon` INNER JOIN `taxontreedef` ON (`taxon`.`TaxonTreeDefID` = `taxontreedef`.`taxontreedefid`) INNER JOIN `discipline` ON (`taxontreedef`.`taxontreedefid` = `discipline`.`TaxonTreeDefID`) WHERE (`taxon`.`text1` REGEXP BINARY ^(TAX)(\\-)([0-9]{3})$ AND `discipline`.`usergroupscopeid` = 3) ORDER BY `taxon`.`text1` DESC;

Thus the problem becomes apparent:
We are locking taxon and all tables which reference taxon as a Foreign Key, but trying to perform an INNER JOIN on Discipline and TaxonTreeDef.

Following the Stack Trace, we can see the autonumber QuerySet is being determined by the following:

specify7/specifyweb/specify/uiformatters.py

Lines 32 to 45 in 2e5ca79

	default = lambda objs: filter_by_collection(objs, collection)
	scope_info = (
	ScopeInfo('collectionid', collection.id, 'coll', 'collectionmemberid') if hasattr(model, 'collectionmemberid') else
	ScopeInfo('disciplineid', collection.discipline.id, 'dsp', 'discipline_id') if hasattr(model, 'discipline_id') else
	ScopeInfo('divisionid', collection.discipline.division.id, 'div', 'division_id') if hasattr(model, 'division_id') else
	None
	)
	logger.info("autonumbering with %s and format_name = %r", scope_info, format_name)
	if scope_info is None or scope_info.db_id_field is None or scope_info.id is None:
	logger.debug("using default collection based autonumbering b/c of missing scope_info fields")
	return default

In the case for Taxon, scope_info is None so it is using the default Collection QuerySet determined by the filter_by_collection(...) function.

Where the INNER JOINs is being inserted becomes apparent:

specify7/specifyweb/specify/filter_by_col.py

Lines 30 to 31 in 2e5ca79

	if queryset.model is Taxon:
	return queryset.filter(definition__discipline=collection.discipline)

Furthermore, the locking of the tables is occuring in the following lines:

specify7/specifyweb/specify/autonumbering.py

Lines 41 to 43 in 2e5ca79

	with lock_tables(obj._meta.db_table):
	for apply_autonumbering_to in thunks:
	apply_autonumbering_to(obj)

In this case, obj._meta.db_table is taxon, so we are running the following SQL to perform the table locks:
lock tables taxon write, which does exactly lock the tables as first mentioned.

The fix here would be to additionally lock the tables needed to determine the scoping for tree records.

⚠️
This is yet to be confirmed, but the same problem likely occurs with the other trees in Specify 7:

• Storage
• Geography
• Geologictimeperiod
• Lithostrat

[maxpatiiuk]

The above comments seem to be all about tree tables, but I am having this bug with CollectionObject:

When trying to create a blank Collection Object, Specify crashes with "Table 'institution' was not locked with LOCK TABLES")" error.
Seems to happen during autonumbering according to stack trace

Screen.Recording.2024-02-13.at.19.54.12.mov

KU Fish Voucher Database

Not able to replicate in that database on the test panel - thus, maybe not a high priority issue, but opening this issue just in case
Able to replicate after restarting the local docker database container

Specify 7 Crash Report - 2024-02-14T03_35_21.881Z.txt

[melton-jason]

You may be experiencing #4430, which was fixed in #4431.
The cause of the issue was was a bug in running the second business rule migration (which applied the default uniqueness rules).
The erroneous migration (before #4431) was likely already applied when #4431 was merged and because the migration was already stored in the django_migrations table, the updated migration was never run.
#4431 was merged before v7.9.3 was released, so this issue should only occur for those use production in the time frame #4430 was introduced and #4431 was merged.

To resolve the issue locally, you can do the following in the database:

delete from django_migrations where app like "%business%";
drop table uniquenessrule_fields;
drop table uniquenessrule;

And then re-run migrations, either by restarting the backend container or manually via the following command (assuming your backend container is titled specify7-specify7-1):

docker exec -it specify7-specify7-1 bash -c "ve/bin/python3 manage.py migrate"

[maxpatiiuk]

thanks for the detailed instructions - I will go ahead and nuke the database entirely as there have been several bugs recently that are not reproducible on the test panel

[emenslin]

Can recreate in edge (7.9.6)

[pashiav]

v7.10.2 Reproduced #4148 (comment):

When trying to create a blank Collection Object, Specify crashes with "Table 'institution' was not locked with LOCK TABLES")" error.

https://chadrontest220250318-production.test.specifysystems.org/specify/view/collectionobject/new/

Screen.Recording.2025-04-02.at.9.36.10.AM.mov

Specify 7 Crash Report - 2025-04-02T14_36_15.520Z.txt

Specify 7 System Information - 2025-04-02T14_36_34.304Z.txt