-
Notifications
You must be signed in to change notification settings - Fork 36
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
Autoincrementing formatter not locking all required tree tables #4148
Comments
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 |
Developer NotesFollowing 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
It makes sense we are receiving a database error here: Django QuerySets are lazy. Due to the nature of the error: +----------+--------------------------+--------+-------------+
| 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: 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
In the case for Taxon, Where the specify7/specifyweb/specify/filter_by_col.py Lines 30 to 31 in 2e5ca79
Furthermore, the locking of the tables is occuring in the following lines: specify7/specifyweb/specify/autonumbering.py Lines 41 to 43 in 2e5ca79
In this case, The fix here would be to additionally lock the tables needed to determine the scoping for tree records.
|
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. Screen.Recording.2024-02-13.at.19.54.12.movKU 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 |
You may be experiencing #4430, which was fixed in #4431. 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 docker exec -it specify7-specify7-1 bash -c "ve/bin/python3 manage.py migrate" |
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 |
Can recreate in edge (7.9.6) |
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 thediscipline
andtaxontreedef
tables while the Taxon (and related) tables are locked.From MariaDB Lock Tables Documentation
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 patternTAX-###
, where#
will be any digit which is supposed to be automatically set.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:
Notes
sanitized_Specify_7_Crash_Report.txt
Reported By
Institut de Recherche pour le Développement
The text was updated successfully, but these errors were encountered: