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

[TheRightDoctors Developer]

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,
• // Enable the use of MySQL to store the DICOM files?
• "EnableStorage" : true,
• // Parameters of the MySLQ database
• "Host" : "localhost",
• "Port" : 3306,
• "UnixSocket" : "",
• "Database" : "orthanc",
• "Username" : "root",
• "Password" : "opmtrd123!!!",
• // Optional: Disable the locking of the MySQL database
• "Lock" : true
• }
• }

server log file below error is showing >>

W1029 20:12:59.508154 main.cpp:1305] Orthanc version: 1.4.2

W1029 20:12:59.511154 OrthancInitialization.cpp:169] Scanning folder "Configuration" for configuration files

W1029 20:12:59.511154 OrthancInitialization.cpp:121] Reading the configuration from: "Configuration\dicomweb.json"

W1029 20:12:59.511154 OrthancInitialization.cpp:121] Reading the configuration from: "Configuration\mysql.json"

W1029 20:12:59.512154 OrthancInitialization.cpp:121] Reading the configuration from: "Configuration\orthanc.json"

W1029 20:12:59.513154 OrthancInitialization.cpp:121] Reading the configuration from: "Configuration\postgresql.json"

W1029 20:12:59.514154 OrthancInitialization.cpp:121] Reading the configuration from: "Configuration\serve-folders.json"

W1029 20:12:59.514154 OrthancInitialization.cpp:121] Reading the configuration from: "Configuration\webviewer.json"

W1029 20:12:59.514154 OrthancInitialization.cpp:121] Reading the configuration from: "Configuration\worklists.json"

W1029 20:12:59.643161 main.cpp:655] Loading plugin(s) from: Configuration\../Plugins/

W1029 20:12:59.647162 PluginsManager.cpp:269] Registering plugin 'worklists' (version 1.4.2)

W1029 20:12:59.647162 PluginsManager.cpp:168] Sample worklist plugin is initializing

W1029 20:12:59.647162 PluginsManager.cpp:168] Worklist server is disabled by the configuration file

W1029 20:12:59.701165 PluginsManager.cpp:269] Registering plugin 'dicom-web' (version 0.5)

W1029 20:12:59.702165 PluginsManager.cpp:168] URI to the DICOMweb REST API: /dicom-web/

W1029 20:12:59.702165 PluginsManager.cpp:168] URI to the WADO-URI API: /wado

W1029 20:12:59.707165 PluginsManager.cpp:269] Registering plugin 'mysql-index' (version 1.1)

W1029 20:12:59.714166 PluginsManager.cpp:269] Registering plugin 'mysql-storage' (version 1.1)

W1029 20:12:59.727166 PluginsManager.cpp:168] Your MySQL server cannot store DICOM files larger than 4MB

W1029 20:12:59.727166 PluginsManager.cpp:168]   => Consider increasing "max_allowed_packet" in "my.cnf" if this limit is insufficient for your use

W1029 20:12:59.732167 PluginsManager.cpp:269] Registering plugin 'postgresql-index' (version 2.2)

W1029 20:12:59.732167 PluginsManager.cpp:168] The PostgreSQL index is currently disabled, set "EnableIndex" to "true" in the "PostgreSQL" section of the configuration file of Orthanc

W1029 20:12:59.734167 PluginsManager.cpp:269] Registering plugin 'postgresql-storage' (version 2.2)

W1029 20:12:59.734167 PluginsManager.cpp:168] The PostgreSQL storage area is currently disabled, set "EnableStorage" to "true" in the "PostgreSQL" section of the configuration file of Orthanc

W1029 20:12:59.786170 PluginsManager.cpp:269] Registering plugin 'web-viewer' (version 2.4)

W1029 20:12:59.786170 PluginsManager.cpp:168] Initializing the Web viewer

W1029 20:12:59.787170 PluginsManager.cpp:168] Web viewer using 1 threads for the decoding of the DICOM images

W1029 20:12:59.787170 PluginsManager.cpp:168] Storing the cache of the Web viewer in folder: C:\Orthanc\WebViewerCache

W1029 20:12:59.790170 PluginsManager.cpp:168] Web viewer using a cache of 100 MB

W1029 20:12:59.790170 PluginsManager.cpp:168] Using GDCM instead of the DICOM decoder that is built in Orthanc

W1029 20:12:59.791170 PluginsManager.cpp:269] Registering plugin 'wsi' (version 0.5)

W1029 20:12:59.791170 PluginsManager.cpp:168] The whole-slide imaging plugin will use at most 2 threads to transcode the tiles

W1029 20:12:59.837173 PluginsManager.cpp:269] Registering plugin 'osimis-web-viewer' (version 1.1.1.0-08acf7b2)

W1029 20:12:59.837173 PluginsManager.cpp:168] Initializing the Web viewer

W1029 20:12:59.838173 PluginsManager.cpp:168] Using GDCM instead of the DICOM decoder that is built in Orthanc

W1029 20:12:59.895176 PluginsManager.cpp:269] Registering plugin 'serve-folders' (version 1.4.2)

W1029 20:12:59.897176 PluginsManager.cpp:168] ServeFolders: Empty configuration file: No additional folder will be served!

W1029 20:12:59.897176 main.cpp:1097] Using a custom database from plugins

W1029 20:12:59.897176 main.cpp:1108] Using a custom storage area from plugins

E1029 20:12:59.914177 PluginsManager.cpp:164] PostgreSQL plugin is incompatible with database schema version: 0

W1029 20:12:59.914177 PluginsManager.cpp:168] An active MySQL transaction was dismissed

W1029 20:12:59.914177 PluginsManager.cpp:219] Unregistering plugin 'dicom-web' (version 0.5)

W1029 20:12:59.925178 PluginsManager.cpp:219] Unregistering plugin 'mysql-index' (version 1.1)

W1029 20:12:59.926178 PluginsManager.cpp:168] MySQL index is finalizing

W1029 20:12:59.926178 PluginsManager.cpp:219] Unregistering plugin 'mysql-storage' (version 1.1)

W1029 20:12:59.926178 PluginsManager.cpp:168] MySQL storage area is finalizing

W1029 20:12:59.927178 PluginsManager.cpp:219] Unregistering plugin 'osimis-web-viewer' (version 1.1.1.0-08acf7b2)

W1029 20:12:59.927178 PluginsManager.cpp:168] Finalizing the Web viewer

W1029 20:12:59.938178 PluginsManager.cpp:219] Unregistering plugin 'postgresql-index' (version 2.2)

W1029 20:12:59.938178 PluginsManager.cpp:168] PostgreSQL index is finalizing

W1029 20:12:59.939178 PluginsManager.cpp:219] Unregistering plugin 'postgresql-storage' (version 2.2)

W1029 20:12:59.939178 PluginsManager.cpp:168] PostgreSQL storage area is finalizing

W1029 20:12:59.939178 PluginsManager.cpp:219] Unregistering plugin 'serve-folders' (version 1.4.2)

W1029 20:12:59.939178 PluginsManager.cpp:219] Unregistering plugin 'web-viewer' (version 2.4)

W1029 20:12:59.939178 PluginsManager.cpp:168] Finalizing the Web viewer

W1029 20:13:00.330201 PluginsManager.cpp:219] Unregistering plugin 'worklists' (version 1.4.2)

W1029 20:13:00.330201 PluginsManager.cpp:168] Sample worklist plugin is finalizing

W1029 20:13:00.331201 PluginsManager.cpp:219] Unregistering plugin 'wsi' (version 0.5)

E1029 20:13:00.331201 main.cpp:1331] Uncaught exception, stopping now: [Error with the database engine] (code 11)

W1029 20:13:00.332201 main.cpp:1364] Orthanc has stopped

[Alain Mazy]

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

    },

--

Alain Mazy / CTO 
a...@osimis.io / +32 494 31 67 27

	OSIMIS S.A.  Quai Banning 6BE-4000 Liège  www.osimis.io

[TheRightDoctors Developer]

Ok, I'll check it out. 

[杨元]

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写道：

[TheRightDoctors Developer]

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.

[Alain Mazy]

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 ?

[Alain Mazy]

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.

[TheRightDoctors Developer]

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,
• // Enable the use of MySQL to store the DICOM files?
• "EnableStorage" : true,
• // Parameters of the MySLQ database
• "Host" : "localhost",
• "Port" : 3306,

• "Database" : "orthanc",
• "Username" : "orthanc",

• "Password" : "orthanc",

• // Optional: Disable the locking of the MySQL database
• "Lock" : true
• }}

Following the Documentation link:- http://book.orthanc-server.com/plugins/mysql.html#id1

Below error is showing Server log file :

W1208 09:18:48.296394 main.cpp:1305] Orthanc version: 1.4.2

W1208 09:18:48.299395 OrthancInitialization.cpp:169] Scanning folder "Configuration" for configuration files

W1208 09:18:48.299395 OrthancInitialization.cpp:121] Reading the configuration from: "Configuration\dicomweb.json"

W1208 09:18:48.300395 OrthancInitialization.cpp:121] Reading the configuration from: "Configuration\mysql.json"

W1208 09:18:48.300395 OrthancInitialization.cpp:121] Reading the configuration from: "Configuration\orthanc.json"

W1208 09:18:48.301395 OrthancInitialization.cpp:121] Reading the configuration from: "Configuration\postgresql.json"

W1208 09:18:48.302395 OrthancInitialization.cpp:121] Reading the configuration from: "Configuration\serve-folders.json"

W1208 09:18:48.302395 OrthancInitialization.cpp:121] Reading the configuration from: "Configuration\webviewer.json"

W1208 09:18:48.302395 OrthancInitialization.cpp:121] Reading the configuration from: "Configuration\worklists.json"

W1208 09:18:48.436402 main.cpp:655] Loading plugin(s) from: Configuration\../Plugins/

W1208 09:18:48.438402 PluginsManager.cpp:269] Registering plugin 'worklists' (version 1.4.2)

W1208 09:18:48.438402 PluginsManager.cpp:168] Sample worklist plugin is initializing

W1208 09:18:48.439403 PluginsManager.cpp:168] Worklist server is disabled by the configuration file

W1208 09:18:48.486405 PluginsManager.cpp:269] Registering plugin 'dicom-web' (version 0.5)

W1208 09:18:48.487405 PluginsManager.cpp:168] URI to the DICOMweb REST API: /dicom-web/

W1208 09:18:48.487405 PluginsManager.cpp:168] URI to the WADO-URI API: /wado

W1208 09:18:48.492406 PluginsManager.cpp:269] Registering plugin 'mysql-index' (version 1.1)

W1208 09:18:48.498406 PluginsManager.cpp:269] Registering plugin 'mysql-storage' (version 1.1)

W1208 09:18:48.507406 PluginsManager.cpp:168] Your MySQL server cannot store DICOM files larger than 4MB

W1208 09:18:48.507406 PluginsManager.cpp:168]   => Consider increasing "max_allowed_packet" in "my.cnf" if this limit is insufficient for your use

W1208 09:18:48.557409 PluginsManager.cpp:269] Registering plugin 'web-viewer' (version 2.4)

W1208 09:18:48.557409 PluginsManager.cpp:168] Initializing the Web viewer

W1208 09:18:48.558409 PluginsManager.cpp:168] Web viewer using 1 threads for the decoding of the DICOM images

W1208 09:18:48.558409 PluginsManager.cpp:168] Storing the cache of the Web viewer in folder: C:\Orthanc\WebViewerCache

W1208 09:18:48.560409 PluginsManager.cpp:168] Web viewer using a cache of 100 MB

W1208 09:18:48.560409 PluginsManager.cpp:168] Using GDCM instead of the DICOM decoder that is built in Orthanc

W1208 09:18:48.561409 PluginsManager.cpp:269] Registering plugin 'wsi' (version 0.5)

W1208 09:18:48.562410 PluginsManager.cpp:168] The whole-slide imaging plugin will use at most 2 threads to transcode the tiles

W1208 09:18:48.598412 PluginsManager.cpp:269] Registering plugin 'osimis-web-viewer' (version 1.1.1.0-08acf7b2)

W1208 09:18:48.598412 PluginsManager.cpp:168] Initializing the Web viewer

W1208 09:18:48.599412 PluginsManager.cpp:168] Using GDCM instead of the DICOM decoder that is built in Orthanc

W1208 09:18:48.601412 PluginsManager.cpp:269] Registering plugin 'serve-folders' (version 1.4.2)

W1208 09:18:48.602412 PluginsManager.cpp:168] ServeFolders: Empty configuration file: No additional folder will be served!

W1208 09:18:48.602412 main.cpp:1097] Using a custom database from plugins

W1208 09:18:48.602412 main.cpp:1108] Using a custom storage area from plugins

E1208 09:18:48.610412 PluginsManager.cpp:164] PostgreSQL plugin is incompatible with database schema version: 0

W1208 09:18:48.610412 PluginsManager.cpp:168] An active MySQL transaction was dismissed

W1208 09:18:48.611412 PluginsManager.cpp:219] Unregistering plugin 'dicom-web' (version 0.5)

W1208 09:18:48.620413 PluginsManager.cpp:219] Unregistering plugin 'mysql-index' (version 1.1)

W1208 09:18:48.621413 PluginsManager.cpp:168] MySQL index is finalizing

W1208 09:18:48.621413 PluginsManager.cpp:219] Unregistering plugin 'mysql-storage' (version 1.1)

W1208 09:18:48.621413 PluginsManager.cpp:168] MySQL storage area is finalizing

W1208 09:18:48.622413 PluginsManager.cpp:219] Unregistering plugin 'osimis-web-viewer' (version 1.1.1.0-08acf7b2)

W1208 09:18:48.622413 PluginsManager.cpp:168] Finalizing the Web viewer

W1208 09:18:48.631413 PluginsManager.cpp:219] Unregistering plugin 'serve-folders' (version 1.4.2)

W1208 09:18:48.632414 PluginsManager.cpp:219] Unregistering plugin 'web-viewer' (version 2.4)

W1208 09:18:48.632414 PluginsManager.cpp:168] Finalizing the Web viewer

W1208 09:18:49.600469 PluginsManager.cpp:219] Unregistering plugin 'worklists' (version 1.4.2)

W1208 09:18:49.600469 PluginsManager.cpp:168] Sample worklist plugin is finalizing

W1208 09:18:49.600469 PluginsManager.cpp:219] Unregistering plugin 'wsi' (version 0.5)

E1208 09:18:49.600469 main.cpp:1331] Uncaught exception, stopping now: [Error with the database engine] (code 11)

W1208 09:18:49.601469 main.cpp:1364] Orthanc has stopped

Please suggest some solution

[Sébastien Jodogne]

You always put the same log.

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

S-

[Jérémy CHAMBON]

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 ?

[Sébastien Jodogne]

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-

[Jérémy CHAMBON]

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.

[Sébastien Jodogne]

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-

[Aniruddha Atale]

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.

--

Thanks,

Aniruddha

[Sébastien Jodogne]

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-

[Sébastien Jodogne]

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.

[Sébastien Jodogne]

And here is the corresponding patch for MySQL:

https://bitbucket.org/sjodogne/orthanc-databases/commits/e26690365c2501ab54bd92f9a33acb1475436e94

Looking forward to your feedback.

Sébastien-

[Aniruddha Atale]

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.

--

Thanks,

Aniruddha

--
You received this message because you are subscribed to the Google Groups "Orthanc Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orthanc-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/orthanc-users/7ef59209-435e-451b-817d-106edbe7d5cb%40googlegroups.com.

[Sébastien Jodogne]

Thanks for your suggestion about named locks! It is implemented by the following changeset:

https://bitbucket.org/sjodogne/orthanc-databases/commits/52b3859ee0b755a7c0edd633dc8eda0301a1725c

To unsubscribe from this group and stop receiving emails from it, send an email to orthanc-users+unsubscribe@googlegroups.com.

[Aniruddha Atale]

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.

--

Thanks,

Aniruddha

To unsubscribe from this group and stop receiving emails from it, send an email to orthanc-user...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/orthanc-users/7ef59209-435e-451b-817d-106edbe7d5cb%40googlegroups.com.

--

You received this message because you are subscribed to the Google Groups "Orthanc Users" group.

To unsubscribe from this group and stop receiving emails from it, send an email to orthanc-user...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/orthanc-users/71e2ebd8-b06d-4bed-8607-06bfa93943d6%40googlegroups.com.

[Sébastien Jodogne]

Great, thanks for the feedback!

Note that no official binary release is currently planned, as I'm focused on some high-priority projects.

The release should hopefully be available by July.

Regards,

Sébastien-

On Thursday, May 9, 2019 at 6:48:08 PM UTC+2, Aniruddha Atale wrote:

Hi Sébastien,

[Aniruddha Atale]

Regarding official image, I am ok with that since I am compiling the code locally. I do have a follow up question. My eventual deployment goal is to get Orthanc (with MySQL plugin) cluster with multi-writer capabilities. 

I understand that Orthanc project official support Postgres-Plugin and Postgres-Plugin today is multi-writer capable. 

My question is -- What would it take to make MySQL plugin multi-writer capable. Is there list of issues (or some notes) from Postgres development for this activity? Any other document/discussion that can help me understand scope for this activity (what problems needs to be solved)? 

--

Thanks,

Aniruddha

--

You received this message because you are subscribed to the Google Groups "Orthanc Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orthanc-user...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/orthanc-users/c304fbc9-ad48-4e42-ae67-805b337a7b2e%40googlegroups.com.

[Alain Mazy]

Hi,

Actually, there are currently 2 issues that needs to be solved in order to handle multiple writers correctly:

- https://bitbucket.org/sjodogne/orthanc/issues/83/serverindex-shall-implement-retries-for-db

- https://bitbucket.org/sjodogne/orthanc/issues/121/crash-when-pg-transaction-fails

Additionally, we should probably clearly identify all transactions as (READ or READ/WRITE) in order to minimize the conflicts in transactions.

This requires some changes in the Core of Orthanc and in the DB plugins so it's unfortunately not a quick fix ...

Best regards,

Alain.

To view this discussion on the web visit https://groups.google.com/d/msgid/orthanc-users/CAEoTiWzcmEp6HHeFm_ReCObS4--NBzM1UV5arBGTxa2ARNho1Q%40mail.gmail.com.

--

Alain Mazy / Software Developer

a...@osimis.io / +32 494 31 67 27

[Aniruddha Atale]

Hi Alain,

Thanks for response. Since you are referring to changes in Orthanc core for multi-writer support, does this imply that even Postgres plugin doesn’t support multiple-writer capability? 

When I use MySQL plugin with Orthanc, I get following log which indicates that MySQL plugin does not support multi-writer capability. I do not see this log with Postgres Plugin. From this I inferred that Postgres Plugin is multi-writer capable. But your comment about lack of support in Orthanc core doesn’t align with this conclusion.

orthanc_1        | W0503 00:44:45.989042 OrthancPluginDatabase.cpp:330] Your database index plugin is not compatible with multiple Orthanc writers

Can you explain overall status of multi-writer capability in Orthanc-core and Orthanc-plugins? If Orthanc-core functionality is implemented (in future), would all DB-plugins inherit this or do you foresee additional plugin specific effort?

 

—

Thanks,

Aniruddha

[Sébastien Jodogne]

Hello,

We never claimed that the PostreSQL and MySQL plugins currently support multi-writer capability (NB: For future reference, as of writing, Orthanc is at release 1.5.6, PostgreSQL 3.2, and MySQL 2.0). 

The log entry you report is about, well, a work-in-progress for our internal purpose. Osimis has validated the "single writer, multiple readers" scenario, provided the "SaveJobs" configuration option is set to "false" (check out the comments in the configuration file).

That being said, nothing prevents Orthanc from supporting the "multiple writers" scenario. This can be done in a few days/weeks of work, for which we need financial support from the industry or from hospitals, as this is clearly an enterprise-related use case.

The required (non-trivial) modifications would essentially be in the Orthanc core and in the Orthanc plugin SDK. From the perspective of the plugins, the modification would essentially boils down to creating SQL transactions using either "START TRANSACTION READ ONLY" or "START TRANSACTION READ WRITE", depending on the input from the Orthanc plugin SDK.

Sébastien-

[Aniruddha Atale]

Hi Sébastien,

Thanks for clarifying the present status of multi writer support. This information is really useful. 

--

Thanks,

Aniruddha

--
You received this message because you are subscribed to the Google Groups "Orthanc Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orthanc-user...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/orthanc-users/589e53b0-c5eb-45fc-89bc-541f57cceb37%40googlegroups.com.