DIcom Server with mysql-error-with-the-database-engine-code

Google Groups archive
1

Hi Developers,

I want to connect the Dicom server with the MySQL database, but the error is coming …please help.suggest some solution,

I am using Dicom server 1.4.2 in windows OS

there is by default configuration folder having two files. mysql.json and postgresql.json

I changed in mysql.json, file to connect with local MySQL database below is my file

  • {
  • /**
    • Configuration to use MySQL or MariaDB instead of the default
    • SQLite back-end of Orthanc. You will have to install the
    • “orthanc-mysql” package to take advantage of this feature.
  • **/
  • “MySQL” : {
  • // Enable the use of MySQL to store the Orthanc index?
  • “EnableIndex” : true,

Orthanc.log.20181029-201259.2468 (5.88 KB)

2

Hi,

I’ve just tried it with a MariaDb 10.3.10 on Windows too and had no issue. Are you sure you’ve just created an empty database named “orthanc” and have not created anything manually in it ?

Here are the commands I used to create the DB (note that I’m not using the root user):
in command prompt 1, start mysql.
mysqld --console

in command prompt 2, create the orthanc user and DB:
mysql.exe -u root -p
MariaDB [(none)]> GRANT ALL PRIVILEGES ON . TO ‘orthanc’@‘localhost’ IDENTIFIED BY ‘password’;

MariaDB [(none)]> \q

mysql.exe -u orthanc -p

MariaDB [(none)]> CREATE DATABASE orthanc;

MariaDB [(none)]> \q

Then, start Orthanc with this config section:
“MySQL” : {
“EnableIndex” : true,

“EnableStorage” : true,

“Host” : “localhost”,

“Port” : 3306,
“Database” : “orthanc”,
“Username” : “orthanc”,
“Password” : “password”,
“Lock” : false

},

3

Ok, I’ll check it out.

4

hi, when i use OrthancMySQL-1.1 on orthanc server 1.4.2, mysql version is 5.6, i got an error ‘MySQL error (1709,HY000): Index column size too large. The maximum column size is 767 bytes’, i had try to set mysql innodb_large_prefix=on and innodb_file_format=BARRACUDA, but it’s need add ROW_FORMAT=DYNAMIC to create table statement, how can i solve the problem? can i create table myself?

在 2018年10月30日星期二 UTC+8上午12:01:08，Alain Mazy写道：

5

Alain
Thanks
for response below is my stack trace ;

I want to connect with my remote cloud SQL instance (google)

W1106 16:25:44.024273 main.cpp:1305] Orthanc version: 1.4.2

W1106 16:25:44.026273 OrthancInitialization.cpp:169] Scanning folder “Configuration” for configuration files

W1106 16:25:44.026273 OrthancInitialization.cpp:121] Reading the configuration from: “Configuration\dicomweb.json”

W1106 16:25:44.027273 OrthancInitialization.cpp:121] Reading the configuration from: “Configuration\mysql.json”

W1106 16:25:44.027273 OrthancInitialization.cpp:121] Reading the configuration from: “Configuration\orthanc.json”

W1106 16:25:44.028273 OrthancInitialization.cpp:121] Reading the configuration from: “Configuration\postgresql.json”

W1106 16:25:44.029273 OrthancInitialization.cpp:121] Reading the configuration from: “Configuration\serve-folders.json”

W1106 16:25:44.029273 OrthancInitialization.cpp:121] Reading the configuration from: “Configuration\webviewer.json”

W1106 16:25:44.029273 OrthancInitialization.cpp:121] Reading the configuration from: “Configuration\worklists.json”

W1106 16:25:44.157280 main.cpp:655] Loading plugin(s) from: Configuration../Plugins/

W1106 16:25:44.158281 PluginsManager.cpp:269] Registering plugin ‘worklists’ (version 1.4.2)

W1106 16:25:44.159281 PluginsManager.cpp:168] Sample worklist plugin is initializing

W1106 16:25:44.159281 PluginsManager.cpp:168] Worklist server is disabled by the configuration file

W1106 16:25:44.206283 PluginsManager.cpp:269] Registering plugin ‘dicom-web’ (version 0.5)

W1106 16:25:44.207283 PluginsManager.cpp:168] URI to the DICOMweb REST API: /dicom-web/

W1106 16:25:44.208283 PluginsManager.cpp:168] URI to the WADO-URI API: /wado

W1106 16:25:44.212284 PluginsManager.cpp:269] Registering plugin ‘mysql-index’ (version 1.1)

W1106 16:25:44.218284 PluginsManager.cpp:269] Registering plugin ‘mysql-storage’ (version 1.1)

E1106 16:25:44.356292 PluginsManager.cpp:164] MySQL error (1045,28000): Access denied for user ‘orthanc’@‘122.169.192.207’ (using password: YES)

W1106 16:25:44.357292 PluginsManager.cpp:168] Database is currently unavailable, retrying…

E1106 16:25:45.517358 PluginsManager.cpp:164] MySQL error (1045,28000): Access denied for user ‘orthanc’@‘122.169.192.207’ (using password: YES)

W1106 16:25:45.517358 PluginsManager.cpp:168] Database is currently unavailable, retrying…

E1106 16:25:46.679425 PluginsManager.cpp:164] MySQL error (1045,28000): Access denied for user ‘orthanc’@‘122.169.192.207’ (using password: YES)

W1106 16:25:46.680425 PluginsManager.cpp:168] Database is currently unavailable, retrying…

E1106 16:25:47.829490 PluginsManager.cpp:164] MySQL error (1045,28000): Access denied for user ‘orthanc’@‘122.169.192.207’ (using password: YES)

W1106 16:25:47.830491 PluginsManager.cpp:168] Database is currently unavailable, retrying…

E1106 16:25:48.975556 PluginsManager.cpp:164] MySQL error (1045,28000): Access denied for user ‘orthanc’@‘122.169.192.207’ (using password: YES)

W1106 16:25:48.975556 PluginsManager.cpp:168] Database is currently unavailable, retrying…

E1106 16:25:50.105621 PluginsManager.cpp:164] MySQL error (1045,28000): Access denied for user ‘orthanc’@‘122.169.192.207’ (using password: YES)

W1106 16:25:50.105621 PluginsManager.cpp:168] Database is currently unavailable, retrying…

E1106 16:25:51.270687 PluginsManager.cpp:164] MySQL error (1045,28000): Access denied for user ‘orthanc’@‘122.169.192.207’ (using password: YES)

W1106 16:25:51.270687 PluginsManager.cpp:168] Database is currently unavailable, retrying…

E1106 16:25:52.399752 PluginsManager.cpp:164] MySQL error (1045,28000): Access denied for user ‘orthanc’@‘122.169.192.207’ (using password: YES)

W1106 16:25:52.399752 PluginsManager.cpp:168] Database is currently unavailable, retrying…

E1106 16:25:53.581820 PluginsManager.cpp:164] MySQL error (1045,28000): Access denied for user ‘orthanc’@‘122.169.192.207’ (using password: YES)

W1106 16:25:53.581820 PluginsManager.cpp:168] Database is currently unavailable, retrying…

E1106 16:25:54.710884 PluginsManager.cpp:164] MySQL error (1045,28000): Access denied for user ‘orthanc’@‘122.169.192.207’ (using password: YES)

W1106 16:25:54.710884 PluginsManager.cpp:168] Database is currently unavailable, retrying…

E1106 16:25:55.861950 PluginsManager.cpp:164] MySQL error (1045,28000): Access denied for user ‘orthanc’@‘122.169.192.207’ (using password: YES)

E1106 16:25:55.861950 PluginsManager.cpp:164] Timeout when connecting to the database, giving up

E1106 16:25:55.861950 PluginsManager.cpp:164] The database is currently not available (probably a transient situation)

E1106 16:25:55.861950 PluginsManager.cpp:102] Error while initializing plugin Configuration../Plugins\OrthancMySQLStorage.dll (code -1)

W1106 16:25:55.862950 PluginsManager.cpp:219] Unregistering plugin ‘dicom-web’ (version 0.5)

W1106 16:25:55.872951 PluginsManager.cpp:219] Unregistering plugin ‘mysql-index’ (version 1.1)

W1106 16:25:55.872951 PluginsManager.cpp:168] MySQL index is finalizing

W1106 16:25:55.873951 PluginsManager.cpp:219] Unregistering plugin ‘worklists’ (version 1.4.2)

W1106 16:25:55.873951 PluginsManager.cpp:168] Sample worklist plugin is finalizing

E1106 16:25:55.874951 main.cpp:1331] Uncaught exception, stopping now: [Error while using a shared library (plugin)] (code 25)

W1106 16:25:55.874951 main.cpp:1364] Orthanc has stopped.

I am attaching Global properties table dump.

orthancdb_GlobalProperties.sql (2.22 KB)

6

Are the sql dump and logs from the same test session ?

In the logs, you just seems not have access to the DB so I can’t imagine how Orthanc could have created a DB.

For the access problems, have you first tried to access your DB from mysql command line ? By default, cloud SQL servers are not accessible from outside the cloud. Have you configured your cloud firewall accordingly ?

7

We have tested the plugin only with MySQL version 7 and 8.

Yes you can try to create the table yourself as long as its schema is compatible with what Orthanc expects.

8

Hi, Alain Mazy,

Thanks for the reply.

I’m trying to Connect Orthanc Mysql server in localhost

I changed in the mysql.json configuration file

Below is my file

  • {
  • “MySQL” : {
  • // Enable the use of MySQL to store the Orthanc index?
  • “EnableIndex” : true,
9

You always put the same log.

Drop the MySQL database, that is visibly corrupted, then start again.

S-

10

Hello,

I have the same problem on a fresh installation of Orthanc 19.2.2 for win64.

When I enable MySQL, I have 2 errors on the log file :

E0306 15:46:39.936572 PluginsManager.cpp:164] MySQL plugin is incompatible with database schema version: 0

and finally …

E0306 15:46:43.429604 main.cpp:1462] Uncaught exception, stopping now: [Error with the database engine] (code 11)

I am using MySQL Server 5.7.23 for Win64.

Could you help me please ?

11

Hello,

This is visibly a problem related to binaries for Microsoft Windows.

As I don’t use Windows, so I won’t personally dig into this issue, so don’t expect any further answer from myself.

I hope someone from the Orthanc community will be able to contribute by systematically reproducing this issue on GNU/Linux and/or by debugging it.

Remember that the source code of the plugin is available at the following location:
https://bitbucket.org/sjodogne/orthanc-databases/src/default/MySQL/

Sébastien-

12

Hello,

Thanks for your answer. I know why !! MySQL branch 5.x is incompatible. I works like a charm with Mysql 8.x.

Have a nice day.

13

Thanks for the feedback!

However, my Ubuntu 16.04 computer runs MySQL 5.7.25, and Orthanc runs fine with this configuration. As a consequence, this is not an incompatibility with MySQL branch 5.x, but really a Windows-specific issue.

Anyway, you pointed out a quick fix: Upgrade to MySQL 8.x if using Microsoft Windows. I have just added a warning in the documentation:
http://book.orthanc-server.com/plugins/mysql.html

Regards,
Sébastien-

14

Hi Sébastien,
I am new to Orthanc and have been experimenting with orchestrating Orthanc cluster (3 nodes) with MySQL backend. From documentation, I know that multi-writer configuration is not supported for Orthanc with MySQL plugin. My plan is to have single orthanc-writer and multiple orthanc-reader configuration. For image-store, I am using S3-plugin.

This is a docker setup with docker-compose bringing up 3 Orthanc instances and 1 MySQL instance and 1 Minio instance (for S3 backend) and a HAProxy instance for load balancing dicom-web traffic.

While bringing up this cluster, I get crash is two out of three Orthanc instances with following signature.

orthanc_3 | E0508 13:29:13.595744 main.cpp:1462] Uncaught exception, stopping now: [Error with the database engine] (code 11)
orthanc_3 | W0508 13:29:13.596041 main.cpp:1495] Orthanc has stopped

This is seems similar to problem that was discussed in this thread, hence I am posting my findings here.

I looked into MySQL plugin source code and found the potential problem in function IDatabase* MySQLIndex::OpenInternal() (File: MySQLIndex.cpp)

This function does following

  1. Establishes connection to database and sets session transaction isolation level to serialize.

  2. Starts a transaction => MySQLTransaction t(*db);

  3. Checks if “Resource” table exists => if (!db->DoesTableExist(t, “Resources”))

  4. If it doesn’t exist then run PrepareIndex.sql

  5. If it exists then there are further checks about consistency of the database and one of these check reads the database schema version. This check is failing.

if (!LookupGlobalIntegerProperty(version, *db, t, Orthanc::GlobalProperty_DatabaseSchemaVersion) ||

version != 6)

{

LOG(ERROR) << "MySQL plugin is incompatible with database schema version: " << version;

db->ReleaseDBSetupLock();

throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);

}

Thinking about this bit more – I think this function is not safe of concurrent execution by multiple instances of Orthanc. While almost all the DB transactions have been wrapped inside a transaction, table creation doesn’t honor transaction boundaries. You can easily test this MySQL server and 2 mysql command line clients (Don’t forget to set autocommit=0; before experimenting).

Because of this, following sequence of events can occur.

  1. Orthanc-1 boots up

  2. Orthanc-1 connects to DB and starts a transaction

  3. Orthanc-1 checks if Orthanc Resource table exists and finds that its not present.

  4. Orthanc-1 initiates execution of PrepareIndex.sql script

  5. Orthanc-2 boots up concurrently

  6. Orthanc-2 connects to DB and starts a transaction

  7. Orthanc-2 checks if Orthanc Resource table exists – It will find it present since PrepareIndex.sql script run by Orthanc-1 and table creation is outside SQL transactional boundaries.

  8. Orthanc-2 will find table, but it will not find any entries in the table. This is because Orthanc-1 hasn’t yet committed the transaction – all the table updates are part of ongoing transaction.

  9. Orthanc-2 will get wrong data (or no data) and will assume that database is corrupt, while actually database is being created.

  10. Orthanc-2 will crash.

Note that the problem will manifest only when you are starting with freshly created database without any Orthanc schema. In docker-setup, if you are using persistent volumes for MySQL container then this problem will be seen only for first time with fresh volume and all subsequent run will not encounter this problem since Orthanc schema would persist between restarts. Also, simply restarting the failed Orthanc container will help situation to bring up the cluster successfully.

Finally, I switched over to Postgres DB and could reproduce this problem with Postgres plugin as well. The function IDatabase* PostgreSQLIndex::OpenInternal() has similar structure as MySQL and is prone to same problem.

To get around the problem, I modified the function to acquire an advisory_lock (and wait/block if not lock is not available) before starting the transaction and release it at the end of transaction (and also during other exit condition from within transaction). This solved the problem since this lock serializes the function execution among concurrent Orthanc instances.

15

Dear Aniruddha,

Many thanks for this detailed, highly helpful analysis!

Summarizing, please could confirm that:

  • The bug in the plugin is that, both for PostgreSQL and MySQL, “CREATE TABLE” instructions are not protected by the “START TRANSACTION” / “COMMIT” instructions?
  • The solution consists in protecting the initialization of the Orthanc database by another mechanism than transactions (i.e. advisory locks)?

Kind Regards,

Sébastien-

16

I have just implemented a patch for PostgreSQL:
https://bitbucket.org/sjodogne/orthanc-databases/commits/cc3dc759c989dd9084c1f5fa7d57fd95a854a9b6

Aniruddha, would you have the possibility to test and validate this fix, by compiling from sources?

I’ll now work on the same fix for MySQL.

17

And here is the corresponding patch for MySQL:
https://bitbucket.org/sjodogne/orthanc-databases/commits/e26690365c2501ab54bd92f9a33acb1475436e94

Looking forward to your feedback.

Sébastien-

18

Thanks Sébastien for quickly getting the patches for Postgres and MySQL plugins. I will gladly test both patches and let you know the result.

I looked at the code and have one small suggestion. Instead of using random number (e.g. 44 or 42), can you use string like “<database_name>.advisory_lock” and “<database_name>.transient_advisory_lock” or something like that.

If there are multiple independent Orthanc instances (e.g. one each for different projects or prod-vs-dev version etc) then they should not compete for same lock namespace in db. In such scenarios, each Orthanc will point to its own database namespace in SQL Server. So it would be great if we can leverage this namespace for locks as well. Let me know what you think.

19

Thanks for your suggestion about named locks! It is implemented by the following changeset:
https://bitbucket.org/sjodogne/orthanc-databases/commits/52b3859ee0b755a7c0edd633dc8eda0301a1725c

20

Hi Sébastien,
I was able to test both MySQL and Postgres plugins for this change and I was able to concurrently boot multiple Orthanc instances in each case. I have attached info logs (annotated with comments) for MySQL. The logs clearly shows that Orthanc access to critical region is now serialized due to advisory lock thus solving the problem.

Thanks for quickly fixing this issue.

mysql_logs.txt (7.37 KB)