Errors during insert into Accounting ac_in_* tables

[arrabito]

Dear all,

After having migrated our AccountingDB and Accounting services from a DataCenter to another I have issues during the insert into ac_in tables, i.e.:

ac_in_<Setup>_DataOperation
ac_in_<Setup>_PilotSubmission

etc.

The error is the following:

2024-01-16 10:26:15 UTC Accounting/DataStore DEBUG: inserting ['DataOperation', datetime.datetime(2024, 1, 16, 10, 23, 51, 234872), datetime.datetime(2024, 1, 16, 10, 26, 15, 646226), ['se.getFile', 'ltibaldo', 'DIRAC.Client.local', 'GRIF-Disk', 'DIRAC.Client.local', 'https', 'Successful', 1581295680, 144.4113540649414, 0.0, 1, 1, 0, 0]]
2024-01-16 10:26:15 UTC Accounting/DataStore/Accounting/AccountingDB DEBUG: _update: INSERT INTO `ac_in_CTA_DataOperation` (  `id`, `taken`, `takenSince`, `OperationType`, `User`, `ExecutionSite`, `Source`, `Destination`, `Protocol`, `FinalStatus`, `TransferSize`, `TransferTime`, `RegistrationTime`, `TransferOK`, `TransferTotal`, `RegistrationOK`, `RegistrationTotal`, `startTime`, `endTime` ) VALUES (  "0", "0", UTC_TIMESTAMP(), "se.getFile", "ltibaldo", "DIRAC.Client.local", "GRIF-Disk", "DIRAC.Client.local", "https", "Successful", "1581295680", "144.4113540649414", "0.0", "1", "1", "0", "0", "1705400631", "1705400775" )
2024-01-16 10:26:15 UTC Accounting/DataStore ERROR: Uncaught exception when serving RPC Function commitRegisters
Traceback (most recent call last):
  File "/opt/dirac/versions/v2.2.29-1705316995/Linux-x86_64/lib/python3.11/site-packages/DIRAC/Core/DISET/RequestHandler.py", line 292, in __RPCCallFunction
    uReturnValue = oMethod(*args)
                   ^^^^^^^^^^^^^^
  File "/opt/dirac/versions/v2.2.29-1705316995/Linux-x86_64/lib/python3.11/site-packages/DIRAC/AccountingSystem/Service/DataStoreHandler.py", line 166, in export_commitRegisters
    return self.__acDB.insertRecordBundleThroughQueue(records)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/dirac/versions/v2.2.29-1705316995/Linux-x86_64/lib/python3.11/site-packages/DIRAC/AccountingSystem/DB/MultiAccountingDB.py", line 95, in insertRecordBundleThroughQueue
    res = self.__db(acType).insertRecordBundleThroughQueue(recByType[acType])
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/dirac/versions/v2.2.29-1705316995/Linux-x86_64/lib/python3.11/site-packages/DIRAC/AccountingSystem/DB/AccountingDB.py", line 574, in insertRecordBundleThroughQueue
    result = self.__insertInQueueTable(typeName, startTime, endTime, valuesList)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/dirac/versions/v2.2.29-1705316995/Linux-x86_64/lib/python3.11/site-packages/DIRAC/AccountingSystem/DB/AccountingDB.py", line 565, in __insertInQueueTable
    return S_OK(retVal["lastRowId"])
                ~~~~~~^^^^^^^^^^^^^
KeyError: 'lastRowId'

After some checks this is due to the returned value of cursor.lastrowid which is 0 in this case, see:

https://github.com/DIRACGrid/DIRAC/blob/integration/src/DIRAC/Core/Utilities/MySQL.py#L794

while on another mysql instance the returned value is the one of the auto_increment variable.

I've also checked the imported tables and they correspond exactly to the original ones.

This issue then prevents the successive imports since it's found that there is a Duplicated entry:

2024-01-16 07:11:55 UTC Accounting/DataStore/Accounting/AccountingDB ERROR: _update (INSERT INTO ac_in_CTA_PilotSubmission( id, taken, takenSince, HostName, SiteDirector, Site, CE, Queue, Status, NumTotal, NumSucceeded, startTime, endTime ) VALUES ( "0", "0", UTC_TIMESTAMP(), "DIRAC.dcta-servers03.pic.es", "SiteDirectorIN2P3", "LCG.IN2P3-CC.fr", "cccondorce02.in2p3.fr", "cccondorce02.in2p3.fr:share", "Succeeded", "11", "11", "1705328203", "1705328203" )): Execution failed. 1062: Duplicate entry '0' for key 'PRIMARY'

So in order to make the code working correctly I had to modify the insert command by removing the explicit set to '0' for the id column by modifying:

        sqlFields = ["id", "taken", "takenSince"] + self.dbCatalog[typeName]["typeFields"]
        sqlValues = ["0", "0", "UTC_TIMESTAMP()"] + valuesList + [startTime, endTime]

to:

        sqlFields = ["taken", "takenSince"] + self.dbCatalog[typeName]["typeFields"]
        sqlValues = ["0", "UTC_TIMESTAMP()"] + valuesList + [startTime, endTime]

here:

https://github.com/DIRACGrid/DIRAC/blob/integration/src/DIRAC/AccountingSystem/DB/AccountingDB.py#L544
https://github.com/DIRACGrid/DIRAC/blob/integration/src/DIRAC/AccountingSystem/DB/AccountingDB.py#L545

I don't know if there is a specific reason to set id=0, but I found that in our case it's causing problems.

Any thoughts?

Thank you.

[fstagni]

This is very old code, not modified for years. Make a PR so that we can evaluate better please.