Grant
October 24, 2023, 12:23am
5
Hi @sfiat ,
Apologies for the delay– we have been investigating this and have finally discovered what issue is causing this problem!
It is going to get a bit technical, so bear with me!
The problem is with the commonName
field’s formatter in the Taxon table.
<format system="false" name="VIROT_idtax" class="edu.ku.brc.specify.datamodel.Taxon" fieldname="commonName">
<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="5" inc="true"/>
</format>
Before saving an object, Specify locks the tables and fields associated with the record and it’s (auto-incrementing) formatters
https://github.com/specify/specify7/blob/8983e264b0887c46464a9a6e9dff5cecb069b6dd/specifyweb/specify/autonumbering.py#L41-L43
You can remove the formatter or set inc
to false on the final line that defines the formatter:
<field type="numeric" size="5" inc="false"/>
and then you will be able to create a new taxon successfully!
If you’d like to do this using Specify 6, you can follow these steps:
Go to the Schema Configuration tool.
From here, go to the table taxon
and the field commonName
.
Click on the … button next to the field format name.
Click on the format VIROT_idtax
and click on the pencil to edit.
Click on the numeric
section of the catalog number and disable auto-incrementing.
Now in Specify 7, clear your browser’s cache (you can log in and out) and edit an item in the tree!
Unfortunately, this change means that you cannot auto-increment the field. Instead, you will need to enter a new number manually when creating a new Taxon record if you wish to continue using it as an identifier.
We’ve opened a GitHub issue to track this problem! Our developers are now aware of the issue. Thank you so much for your report!
opened 12:19AM - 24 Oct 23 UTC
1 - Bug
> Hi again,
>
> I have another problem, when I try to edit the taxon tree (no… t 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](https://mariadb.com/kb/en/lock-tables/#limitations)
> 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.
```xml
<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](https://github.com/specify/specify7/files/13090128/sanitized_Specify_7_Crash_Report.txt)
**Reported By**
**Institut de Recherche pour le Développement**