Orthanc not working with MySQL 8

[Ian Smith]

I'm trying to get the MySQL backend working with Orthanc on Windows Server 2019 but I get this startup error

message with MySQL 8.19:

MySQL plugin is incompatible with database schema version:

Same thing on RHEL 8. It works fine with MySQL 5.7 on Windows so I am wondering why this is. Is Orthanc

compatible with MySQL 8 ???

any help much appreciated,

regards,

-ian.

--------------------------------------------

Dr Ian C. Smith

Advanced Research Computing

University of Liverpool

UK.

[Sébastien Jodogne]

Hello,

The MySQL plugin for Orthanc is compatible with MySQL 8.x since its version 1.1 (released on 2018-07-18), as can be seen in the release notes at the bottom of this page:

https://www.orthanc-server.com/browse.php?path=/plugin-mysql

Check out the version of the MySQL plugin by inspecting your logs.

If you expect further help from this forum, check out the following page, provide logs in "--verbose" mode, as well as a minimal working example on GNU/Linux for us to reproduce your issue:

https://book.orthanc-server.com/users/support.html

HTH,

Sébastien-

[Ian Smith]

Hi Sebastien,

Thanks for the quick reply. I'm running on RHEL version 8.0.0 with MySQL version 8.0.17 and version

2.0 of the MySQL plugin built from source. If I put --verbose on I can see this in the log:

--

I0309 10:47:03.881863 PluginsManager.cpp:172] Successful connection to MySQL database
I0309 10:47:03.882168 PluginsManager.cpp:172] Preparing MySQL statement: SELECT GET_LOCK('Lock42', 0);
I0309 10:47:03.886435 PluginsManager.cpp:172] Preparing MySQL statement: SELECT COUNT(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = ?) AND (TABLE_NAME = ?)
I0309 10:47:03.888037 PluginsManager.cpp:172] Preparing MySQL statement: SELECT COUNT(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = ?) AND (TABLE_NAME = ?)
I0309 10:47:03.889083 PluginsManager.cpp:172] Preparing MySQL statement: SELECT value FROM GlobalProperties WHERE property=?
E0309 10:47:03.889547 PluginsManager.cpp:164] MySQL plugin is incompatible with database schema version: 0
W0309 10:47:03.889606 PluginsManager.cpp:168] An active MySQL transaction was dismissed
I0309 10:47:03.889690 PluginsManager.cpp:172] Closing connection to MySQL database
W0309 10:47:03.889788 PluginsManager.cpp:219] Unregistering plugin 'mysql-index' (version 2.0)
W0309 10:47:03.889801 PluginsManager.cpp:168] MySQL index is finalizing
W0309 10:47:03.890064 PluginsManager.cpp:219] Unregistering plugin 'osimis-web-viewer' (version 1.3.1.0-ea114768)
W0309 10:47:03.890076 PluginsManager.cpp:168] Finalizing the Web viewer
W0309 10:47:03.894221 PluginsManager.cpp:219] Unregistering plugin 'web-viewer' (version 2.5)
W0309 10:47:03.894239 PluginsManager.cpp:168] Finalizing the Web viewer
E0309 10:47:04.868146 main.cpp:1550] Uncaught exception, stopping now: [Error with the database engine] (code 11)
W0309 10:47:04.868407 main.cpp:1583] Orthanc has stopped
--

If I try running the SQL command by hand,I get this:

--

mysql> SELECT COUNT(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = ?) AND (TABLE_NAME = ?);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?) AND (TABLE_NAME = ?)' at line 1

--

I noticed this when I ran 'strings' on libOrthancMySQLIndex.so - not sure if it's relevant ???

--

This database plugin is incompatible with your version of Orthanc
expecting the DB schema version
, but this plugin is only compatible with version 6

MySQL plugin is incompatible with database schema version:

_client_version
--

thanks again,

-ian.

[Sébastien Jodogne]

Hello,

The following message:

"MySQL plugin is incompatible with database schema version: 0"

has already been discussed in the past on this forum:

https://groups.google.com/d/msg/orthanc-users/yV3LSTh_TjI/WSGPCp3QAwAJ

It occurs in the scenario where multiple Orthanc servers access the same database. WARNING: As of release 1.5.8, only one Orthanc server can write to the same MySQL/PostgreSQL database, as explained in the Orthanc Book:

https://book.orthanc-server.com/plugins/mysql.html#locking

https://book.orthanc-server.com/plugins/postgresql.html#postgresql-lock

It looks like a fix was committed to enable the "single-writer, multi-reader" scenario, but this fix is still pending in the mainline of the MySQL plugin (no official release yet):

https://groups.google.com/d/msg/orthanc-users/yV3LSTh_TjI/h3PRApJFBAAJ

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

Please could you make sure that only one Orthanc server is using the MySQL database, and give a try dropping your database, then using the mainline version of the MySQL plugin?

Regards,

Sébastien-

[Ian Smith]

Hi Sebastien,

I've tried re-installing MySQL and dropping/recreating the Orthanc database but I get exactly the same thing.

Pretty sure I've not had multiple writers messing things up but I'll try starting again from scratch and will let

you know how I get on.

thanks,

-ian.

[Ian Smith]

I've now started with a fresh AWS cloud instance, installed Orthanc from source, installed the MySQL plugin version 2.0 (mainline)

and installed MySQL again but I get *exactly * the same error message:

--

I0310 12:46:23.461055 PluginsManager.cpp:172] Successful connection to MySQL database
I0310 12:46:23.461440 PluginsManager.cpp:172] Preparing MySQL statement: SELECT GET_LOCK(?, 0)
I0310 12:46:23.465817 PluginsManager.cpp:172] Preparing MySQL statement: SELECT COUNT(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = ?) AND (TABLE_NAME = ?)
I0310 12:46:23.467434 PluginsManager.cpp:172] Preparing MySQL statement: SELECT COUNT(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = ?) AND (TABLE_NAME = ?)
I0310 12:46:23.468444 PluginsManager.cpp:172] Preparing MySQL statement: SELECT value FROM GlobalProperties WHERE property=?
E0310 12:46:23.468969 PluginsManager.cpp:164] MySQL plugin is incompatible with database schema version: 0
W0310 12:46:23.469026 PluginsManager.cpp:168] An active MySQL transaction was dismissed
I0310 12:46:23.469137 PluginsManager.cpp:172] Preparing MySQL statement: SELECT RELEASE_LOCK(?)
I0310 12:46:23.469552 PluginsManager.cpp:172] Closing connection to MySQL database
W0310 12:46:23.469633 PluginsManager.cpp:219] Unregistering plugin 'mysql-index' (version mainline)
W0310 12:46:23.469654 PluginsManager.cpp:168] MySQL index is finalizing
W0310 12:46:23.469933 PluginsManager.cpp:219] Unregistering plugin 'mysql-storage' (version mainline)
W0310 12:46:23.469964 PluginsManager.cpp:168] MySQL storage area is finalizing
E0310 12:46:23.470180 main.cpp:1550] Uncaught exception, stopping now: [Error with the database engine] (code 11)
W0310 12:46:23.470391 main.cpp:1583] Orthanc has stopped
--

If I quote the wildcard characters in the SQL command it works:

mysql> SELECT COUNT(*) FROM information_schema.TABLES WHERE (TABLE_SCHEMA = '?') AND (TABLE_NAME = '?');
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

although maybe these just went missing in the log file message ?

Any ideas ?

thanks,

-ian.

[Sébastien Jodogne]

The "?" corresponds to variables mapped within SQL queries when MySQL is called from a programming language. This is perfectly legal and fine.

As far as I'm concerned, I'm unable to reproduce your issue on my computer, and I don't have time to investigate very specific MySQL issues in the following weeks. If you want a quick answer, please provide a way to replicate your issue in a Docker environment (i.e. provide a command-line to start a Docker container running MySQL, against which the Orthanc MySQL plugin does not work).

[Ian Smith]

Just for the record I've tried version 1.1 of the plugin and get exactly the same thing as well. I'm not quite sure how this could

be a problem with multiple writers as I've not managed to actually start a *single* Orthanc  instance yet. Will have to give up

on this for now as I don't have time to investigate further. Incidentally I was already aware of how the ? wildcard worked - the

point I was trying to make was a possible quoting problem if the SQL queries are passed via a standard UNIX shell rather than

an API.

regards,

-ian.

On Friday, March 6, 2020 at 2:44:37 PM UTC, Ian Smith wrote:

[Sébastien Jodogne]

Hello again Ian,

 

I'm not quite sure how this could be a problem with multiple writers as I've not managed to actually start a *single* Orthanc  instance yet. Will have to give up on this for now as I don't have time to investigate further.

As written before, please explain how to independently reproduce your issue using a MySQL running in Docker, and I'll happily have a look at your problem.

Sébastien- 

[Sébastien Jodogne]

For reference, here are the procedures to run MySQL 8.0.19 from Docker, then start Orthanc against it:

(1) Start the MySQL container in one terminal:

$ docker run -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root --rm -t -i mysql:8.0.19

(2) Create a database for Orthanc within this MySQL container (in another terminal):

$ echo "CREATE DATABASE orthanc;" | mysql --protocol=tcp --host=localhost --port=3306 --user=root -p

NB: Password is "root", cf. the "MYSQL_ROOT_PASSWORD" environment variable.

(3) Compile MySQL plugin 2.0 for Orthanc, and create the following configuration file:

{

  "Name" : "MyOrthanc",

  "MySQL" : {

    "EnableIndex" : true,

    "EnableStorage" : true,

    "Host" : "localhost",

    "Port" : 3306,

    "UnixSocket" : "",

    "Database" : "orthanc",

    "Username" : "root",

    "Password" : "root",

    "Lock" : true

  },

  "Plugins" : [ "." ]

}

This configuration is essentially the one from the Orthanc Book:

https://book.orthanc-server.com/plugins/mysql.html#usage

Using this setup, I cannot reproduce your issue, and Orthanc just runs fine.

As a consequence, if you cannot provide additional information, I cannot provide further support by myself.

Kind Regards,

Sébastien-

[Ian Smith]

I think I've spotted the problem. In your example you have used the "root" account in the configuration file

whereas I have used a no-privileged "orthanc" account. To get thing working I did this:

1. Dropped the orthanc database

2. Created the orthanc database as root

3. Started the orthanc server using the root credentials

All now works. If I subsequently restart the server but using the "orthanc" credentials then

it still works.  It seems that something requires root permission on first start up but not

subsequently. 

If I do this it doesn't work:

1. Drop the orthanc database

2. Create the orthanc database as root

3. Grant all privileges on the orthanc database to user "orthanc"

4. Start the Orthanc server with non-root "orthanc" user credentials.

thanks,

-ian.

On Friday, March 6, 2020 at 2:44:37 PM UTC, Ian Smith wrote:

[Sébastien Jodogne]

Hi Ian,

Many thanks for your investigation! Thanks to it, I was able to reproduce your issue on my computer. For further reference, here are the instructions:

(1) Start the MySQL container in one terminal:

$ docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=root --rm -t -i mysql:8.0.19

(2) Create a database for Orthanc within this MySQL container (in another terminal):

$ cat <<EOF | mysql --protocol=tcp --host=localhost --port=3306 --user=root -p

CREATE USER 'orthanc'@'%' IDENTIFIED BY 'orthanc';

GRANT ALL PRIVILEGES ON orthanc.* TO 'orthanc'@'%';

CREATE DATABASE orthanc;

EOF

NB: Password is "root", cf. the "MYSQL_ROOT_PASSWORD" environment variable.

(3) Start Orthanc using the following configuration file:

{

  "Name" : "MyOrthanc",

  "MySQL" : {

    "EnableIndex" : true,

    "EnableStorage" : true,

    "Host" : "localhost",

    "Port" : 3306,

    "UnixSocket" : "",

    "Database" : "orthanc",

    "Username" : "orthanc",

    "Password" : "orthanc",

    "Lock" : true

  },

  "Plugins" : [ "." ]

}

In such a situation, MySQL plugin 2.0 stops and reports the following error (which indicates that the Orthanc database has not fully been initialized):

E0312 12:22:15.924770 PluginsManager.cpp:164] MySQL error (1419,HY000): You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

If Orthanc is then started a second time, we get your initial error message:

E0312 12:22:30.514954 PluginsManager.cpp:164] MySQL plugin is incompatible with database schema version: 0

The error results from the fact that, by default, a regular MySQL user has not the possibility to run the "CREATE TRIGGER" command. This corrupts the initialization process.

I have just pushed a fix in the mainline of the MySQL plugin, that adds safeguards to avoid this corruption and that displays a solution for the Orthanc user:

https://bitbucket.org/sjodogne/orthanc-databases/commits/740d9829f52e59719eb5770f56a537e820f61ec0

I would love to have your feedback about this modification. If this patch fixes your issue, I'll happily make a fresh release 2.1 of the MySQL plugin.

Regards,

Sébastien-

[Salim Kanoun]

Hi,

I reactivate this awaiting feedback thread because i just hit the exact same issue,

however by changing the log_bin_trust_function_creators to true solves the problem,

Best regards,

Salim

[Sébastien Jodogne]

Hi Salim,

Thanks for your feedback.

The following changeset that is pending in the mainline will explicitly display the instruction about "log_bin_trust_function_creators", and the plugin will refuse to start as long as this instruction is not fulfilled:

https://hg.orthanc-server.com/orthanc-databases/rev/740d9829f52e59719eb5770f56a537e820f61ec0

However, we're still waiting from the feedback of the original poster (Ian) about these bug fixes to start the official release process of the version 2.1 of the MySQL plugin.

Regards,

Sébastien-

[pacur...@gmail.com]

getting the same error. Hopefully version 2.1 will come soon and fix that. Trying to use Orthanc on Windows with latest MySQL server administered in MySQL Workbench
Thank you all,

Emil

[Sébastien Jodogne]

Hello,

As written above, we're still waiting for user feedback before we can release the MySQL 2.1 plugin.

Please try and compile the plugin from the sources of the mainline, then provide feedback if you want a quick official release:

https://book.orthanc-server.com/plugins/mysql.html

Sébastien-

--
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/964e8e14-2fb6-4bc1-85dc-0dafc2828d9cn%40googlegroups.com.