SQLIntegrityConstraintViolationException when adding RecordType
Created by: Quazgar
Using an automated script to add lots of RTs, I experienced this uncaught exception:
caosdb-server_1 | caosdb.server.database.exceptions.TransactionException: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '761-758-761' for key 'PRIMARY' caosdb-server_1 | at caosdb.server.database.backend.implementation.MySQL.MySQLInsertParents.execute(MySQLInsertParents.java:46)
caosdb-server_1 | at caosdb.server.database.backend.transaction.InsertParents.execute(InsertParents.java:47)
caosdb-server_1 | at caosdb.server.database.BackendTransaction.execute(BackendTransaction.java:227)
caosdb-server_1 | at caosdb.server.database.backend.transaction.InsertEntity.execute(InsertEntity.java:59)
caosdb-server_1 | at caosdb.server.database.BackendTransaction.executeTransaction(BackendTransaction.java:144)
caosdb-server_1 | at caosdb.server.transaction.TransactionInterface.execute(TransactionInterface.java:44)
caosdb-server_1 | at caosdb.server.transaction.Insert.insert(Insert.java:105)
caosdb-server_1 | at caosdb.server.transaction.Insert.transaction(Insert.java:100)
caosdb-server_1 | at caosdb.server.transaction.Transaction.execute(Transaction.java:170)
caosdb-server_1 | at caosdb.server.resource.transaction.EntityResource.httpPostInChildClass(EntityResource.java:178) caosdb-server_1 | at caosdb.server.resource.AbstractCaosDBServerResource.httpPost(AbstractCaosDBServerResource.java:271)
...
I will add a failing test later.
Imported comments:
By Timm Fitschen on 2021-02-10T12:00:00.935Z
mentioned in issue #94 (closed)
By Quazgar on 2020-04-15T14:35:26.846Z
closed
By Quazgar on 2020-04-15T14:35:26.699Z
Fixed in mysql-backend.
By Quazgar on 2020-03-10T17:23:15.929Z
mentioned in commit caosdb-pyinttest@35e40184
By Quazgar on 2020-03-10T17:21:11.159Z
These SQL calls are run, the last one fails:
call insertIsa(?,?) with (129, 131)
call insertIsa(?,?) with (130, 129)
call insertIsa(?,?) with (130, 130)
call insertIsa(?,?) with (130, 131) # fails
By Quazgar on 2020-03-10T16:09:46.841Z
mentioned in commit caosdb-pyinttest@77fcd0c1
By Quazgar on 2020-03-10T13:40:30.029Z
The server error log contains this entry:
TIME: 20-03-06 18:13:52.207
SRID: 3444aaf54dba39035df6f285757cc7d
REQUEST: POST https://localhost:10443/Entity?uniquename=true HTTPS/1.1
REQUEST HEADERS: [Cookie: SessionToken=["PAM","admin",1583518432123,600000,"d94dc83d-9670-43b3-bd94-bc3999454971","609086331d716fd68150f33480e0e8f2168313da2557a88b94e7484cb4a9a27fa809fe5b5814a191fba952d14e160585ee89\
83d6002005ffdc27bffa1df9156c"];][User-Agent: caosdb-pylib/0.2.4rc0 - _DefaultCaosDBServerConnection][Host: localhost:10443][Accept-Encoding: identity][Content-Length: 2554]
REQUEST ENTITY: <Insert>
<RecordType id="-1" cuid="-1--fcccc650-eefb-443e-9c1a-0a043565aa6d" name="RecordType_0000000000" description="RecordType_0000000000">
<Parent id="-1" name="RecordType_0000000000" description="RecordType_0000000000" flag="inheritance:OBLIGATORY,"/>
<Property id="756" cuid="-5--62f5765d-60db-49e8-bb0e-849505a0caa3" name="prop_0000000004" description="prop_0000000004" datatype="DOUBLE" unit="W" importance="RECOMMENDED" flag="inheritance:FIX">
<Warning code="0" description="Unknown unit. Values with this unit cannot be converted to other units when used in search queries."/>
</Property>
</RecordType>
<RecordType id="-2" cuid="-2--c90f52f5-0808-49c4-98e0-539b6219389c" name="RecordType_0000000001" description="RecordType_0000000001">
<Property id="753" cuid="-2--18f2a716-ee3e-4f50-a8d6-711e98e6e1e3" name="prop_0000000001" description="prop_0000000001" datatype="DOUBLE" unit="GJ" importance="RECOMMENDED" flag="inheritance:FIX">
<Warning code="0" description="Unknown unit. Values with this unit cannot be converted to other units when used in search queries."/>
</Property>
</RecordType>
<RecordType id="-3" cuid="-3--21a1e57b-14a4-4837-8546-9078609d82cf" name="RecordType_0000000002" description="RecordType_0000000002">
<Parent id="-2" name="RecordType_0000000001" description="RecordType_0000000001" flag="inheritance:OBLIGATORY,"/>
<Parent id="-1" name="RecordType_0000000000" description="RecordType_0000000000" flag="inheritance:OBLIGATORY,"/>
<Parent id="-3" name="RecordType_0000000002" description="RecordType_0000000002" flag="inheritance:OBLIGATORY,"/>
</RecordType>
<RecordType id="-4" cuid="-4--db26d2b4-dfc9-489c-aa64-83ad19125fae" name="RecordType_0000000003" description="RecordType_0000000003">
<Parent id="-1" name="RecordType_0000000000" description="RecordType_0000000000" flag="inheritance:OBLIGATORY,"/>
<Parent id="-1" name="RecordType_0000000000" description="RecordType_0000000000" flag="inheritance:OBLIGATORY,"/>
</RecordType>
<RecordType id="-5" cuid="-5--44bc7a1c-710d-4678-9049-c88c7e4e8afd" name="RecordType_0000000004" description="RecordType_0000000004">
<Parent id="-3" name="RecordType_0000000002" description="RecordType_0000000002" flag="inheritance:OBLIGATORY,"/>
<Parent id="-5" name="RecordType_0000000004" description="RecordType_0000000004" flag="inheritance:OBLIGATORY,"/>
<Parent id="-2" name="RecordType_0000000001" description="RecordType_0000000001" flag="inheritance:OBLIGATORY,"/>
</RecordType>
</Insert>
RESPONSE: HTTPS/1.1 - Internal Server Error (500) - The server encountered an unexpected condition which prevented it from fulfilling the request
RESPONSE ENTITY:<?xml version="1.0" encoding="UTF-8"?>^M
<?xml-stylesheet type="text/xsl" href="https://localhost:10443/webinterface/1583517850/webcaosdb.xsl" ?>^M
<Response username="admin" realm="PAM" srid="3444aaf54dba39035df6f285757cc7d" timestamp="1583518432142" baseuri="https://localhost:10443">^M
<UserInfo username="admin" realm="PAM">^M
<Roles>^M
<Role>administration</Role>^M
</Roles>^M
</UserInfo>^M
<Error code="500" description="An unexpected server error has occurred.">SRID = 3444aaf54dba39035df6f285757cc7d^M
^M
Please report this server error to and include the SRID into your report.</Error>^M
</Response>^M
caosdb.server.database.exceptions.TransactionException: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '761-758-761' for key 'PRIMARY'
at caosdb.server.database.backend.implementation.MySQL.MySQLInsertParents.execute(MySQLInsertParents.java:46)
at caosdb.server.database.backend.transaction.InsertParents.execute(InsertParents.java:47)
at caosdb.server.database.BackendTransaction.execute(BackendTransaction.java:227)
at caosdb.server.database.backend.transaction.InsertEntity.execute(InsertEntity.java:59)
at caosdb.server.database.BackendTransaction.executeTransaction(BackendTransaction.java:144)
at caosdb.server.transaction.TransactionInterface.execute(TransactionInterface.java:44)
at caosdb.server.transaction.Insert.insert(Insert.java:105)
at caosdb.server.transaction.Insert.transaction(Insert.java:100)
at caosdb.server.transaction.Transaction.execute(Transaction.java:170)
at caosdb.server.resource.transaction.EntityResource.httpPostInChildClass(EntityResource.java:178)
at caosdb.server.resource.AbstractCaosDBServerResource.httpPost(AbstractCaosDBServerResource.java:271)
at jdk.internal.reflect.GeneratedMethodAccessor7.invoke(Unknown Source)
...
Caused by: java.sql.SQLIntegrityConstraintViolationException: Duplicate entry '761-758-761' for key 'PRIMARY'
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:533)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:513)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:115)
at com.mysql.cj.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:1983)
at com.mysql.cj.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1826)
at com.mysql.cj.jdbc.PreparedStatement.execute(PreparedStatement.java:1153)
at snaq.db.CachedPreparedStatement.execute(CachedPreparedStatement.java:328)
at caosdb.server.database.backend.implementation.MySQL.MySQLInsertParents.execute(MySQLInsertParents.java:44)
... 72 more