MySQL error in log after Anonymization

Hi Everyone,

When anonymizing a patient stored in fresh Orthanc instance I have noticed that, although everything appears to work fine (anonymized patient is created) there is a regular error message starting to appear in the logs:

T0628 10:00:47.150741 ServerContext.cpp:254] Serializing the content of the jobs engine
E0628 10:00:47.158729 PluginsManager.cpp:164] MySQL error (1406,22001): Data too long for column ‘value’ at row 1
I0628 10:00:47.158796 PluginsManager.cpp:172] (plugins) An active MySQL transaction was dismissed
I0628 10:00:47.172269 PluginsManager.cpp:172] (plugins) Cannot rollback a non-existing transaction
I0628 10:00:47.172328 StatelessDatabaseOperations.cpp:554] Cannot rollback transaction: Bad sequence of calls
E0628 10:00:47.172586 ServerContext.cpp:268] Cannot serialize the jobs engine: Error with the database engine

The same message is then repeated every 10 seconds. I would assume that this is related to the MySQL plugin and the question is how severe this is. After restarting Orthanc the message goes away (until anonymizing another patient).

A couple of notes:

  • running Orthan 1.9.4 on CentOS 8
  • tried with MySQL plugin 4.0 and the latest mainline binary from 27 June 2021
  • it does not matter if the anonymization is triggered via REST API or Orthanc Explorer
  • is not limited to the anonymization of patients but also happens for series
    here is the config of the MySQL plugin used:

{
“MySQL” : {
“EnableIndex” : true,
“EnableStorage” : false,
“Host” : “xxx”,
“Port” : xxx,
“UnixSocket” : “”,
“Database” : “xxx”,
“Username” : “xxx”,
“Password” : “xxx”,
“EnableSsl” : false,
“SslVerifyServerCertificates”: true,
“SslCACertificates”: “”,
“Lock” : true,
“MaximumConnectionRetries” : 10,
“ConnectionRetryInterval” : 5,
“IndexConnectionsCount” : 1
}
}

Hello,

What is your version of MySQL?

I am unable to reproduce this “data too long” error using MySQL 5.7 and MySQL 8.0.

This error corresponds to the fact that it is not possible for Orthanc to write a large JSON (that contains a serialized version of all its jobs) into the “GlobalProperties” table of the MySQL database.

For reference, here is the file that initializes the MySQL database, where one can see that the “value” column of the “GlobalProperties” table is set to the “TEXT” type, which should allow for long texts:

https://hg.orthanc-server.com/orthanc-databases/file/tip/MySQL/Plugins/PrepareIndex.sql

Sébastien-

Hi Sébastien,

sorry for missing out on this information, completely forgot to mention this.

I’m actually using MariaDB version 10.3.28.
According to the MariaDB documentation TEXT supports a maximum length of 65,535 (216 - 1) characters.

I checked the database and it’s actually set to LONGTEXT which is supposed to hold 4GB of information,

You’re right: The “TEXT” from SQL is automatically patched as a “LONGTEXT” by the C++ code of the plugin:
https://hg.orthanc-server.com/orthanc-databases/file/OrthancMySQL-4.0/MySQL/Plugins/MySQLIndex.cpp#l188

So, this shouldn’t be the issue. There is visibly something specific with MariaDB that doesn’t appear with MySQL.

I cannot reproduce using MariaDB 10.3.30 running in Docker.

Could you make sure that the value of the “max_allowed_packet” option is large enough in your configuration?
https://mariadb.com/kb/en/server-system-variables/#max_allowed_packet

If this this not the cause of the problem, please provide us with a systematic way to reproduce your issue:
https://book.orthanc-server.com/users/support.html#discussing-a-minimal-working-example

Sébastien-

I have tried the following things:

  • increase max_allowed_packet to 256 MB → still the same problem
  • update MariaDB to version 10.5.11 → still the same problem

So I indeed would now also assume that it’s a problem with my setup.

I will try to set-up a fresh Orthanc installation on the same CentOS 8 system using latest stable linux standard base with a new DB and minimal plugins (e.g. only the MySQL plugin).

Is there anything else I could try to on the existing system, like purging any particular (temporary?) database table?
Could it be related to the fact that I’m only using the MySQL Index Plugin but not the MySQL Storage Plugin?

Martin

Yes, I think that trying with a fresh installation would be the best.

To fully reset the MySQL/MariaDB database, the command “DROP DATABASE [orthanc database]” should be sufficient. You could also try running MariaDB from a Docker container in order to make sure that no problematic configuration is in use, for instance:

$ docker run -e MARIADB_ROOT_PASSWORD=root -e MARIADB_DATABASE=orthanctest --rm -t -i -p 3306:3306 --name mariadb mariadb:10.3 --max-allowed-packet=67108864

In either case, this has nothing to do with the fact that you don’t use the MySQL storage plugin.

Sébastien-

Unfortunately even after setting up a new Instance the problem remains.
Below is what I did to set it up as a shell and MySQL history (I hope I did nothing utterly stupid).

I did also give it several different tries (while dropping the database in between runs) and what I have observed is that interestingly it does not appear to happen for a single DICOM instance or even a few DICOM instances. So I tried with a dataset that had 50 Instances and did simply run the anonymization multiple times. The first couple of times it appeared to work but then suddenly the error came up again. So I tried to narrow it down and it appears to be triggered at around when 200 instances are anonymized (or have been anonymized if done multiple times).

hope that helps
Martin

Thanks for your instructions: I managed to reproduce your issue thanks to them.

I think I found the culprit code:

https://hg.orthanc-server.com/orthanc-databases/file/OrthancMySQL-4.0/MySQL/Plugins/MySQLIndex.cpp#l272

The column “value” should be declared “LONGTEXT” in the table “ServerProperties” that was introduced in release 4.0 of the MySQL plugin.

I will soon have a look at the best way to solve this issue while preserving backward compatibility.

Sébastien-

Hello,

This issue has been fixed by the following changeset:
https://hg.orthanc-server.com/orthanc-databases/rev/0868500060f3

The just-realesed version 4.1 of the MySQL plugin includes this patch:
https://groups.google.com/g/orthanc-users/c/LBil4zk8ZiY/m/UIGpAmOvAAAJ

Regards,
Sébastien-

I have tested the new 4.1 version and everything seems to be working now.
Thank you very much!

Best wishes
Martin