Mercurial > hg > orthanc-book
annotate Sphinx/source/plugins/mysql.rst @ 927:dfe96daba4f8
python in win installer
| author | Alain Mazy <am@osimis.io> |
|---|---|
| date | Wed, 29 Mar 2023 11:54:58 +0200 |
| parents | 2f8ee0aef0a6 |
| children | 1316bc62b5d5 |
| rev | line source |
|---|---|
| 154 | 1 .. _mysql: |
| 2 | |
| 3 | |
| 4 MySQL/MariaDB plugins | |
| 5 ===================== | |
| 6 | |
| 7 .. contents:: | |
| 8 | |
| 9 The Orthanc project provides two **official** plugins to replace the | |
| 10 default storage area (on the filesystem) and the default SQLite index | |
| 11 by a MySQL or a MariaDB database. | |
| 12 | |
| 13 For general information, check out the `official homepage of the | |
|
358
011b01ccf52d
fixing external hyperlinks
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
270
diff
changeset
|
14 plugins <https://www.orthanc-server.com/static.php?page=mysql>`__. |
| 154 | 15 |
|
643
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
16 For information about scalability, make sure to read the section about |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
17 :ref:`multiple writers in large-scale deployments <multiple-writers>`. |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
18 |
| 750 | 19 The source code of the MySQL/MariaDB plugins can be found in the |
| 20 ``orthanc-databases`` `Mercurial repository | |
| 21 <https://hg.orthanc-server.com/orthanc-databases/>`__, next to the | |
| 22 source code of the :ref:`ODBC <odbc>` and :ref:`PostgreSQL | |
| 23 <postgresql>` plugins. | |
| 24 | |
| 230 | 25 **Warning:** According to `this thread on our discussion group |
| 231 | 26 <https://groups.google.com/d/msg/orthanc-users/yV3LSTh_TjI/Fb4ShaYMBAAJ>`__, |
| 27 the MySQL/MariaDB plugins require MySQL 8.x if running on Microsoft | |
| 28 Windows. | |
| 230 | 29 |
| 154 | 30 |
| 31 | |
| 32 Compilation | |
| 33 ----------- | |
| 34 | |
| 35 Static linking | |
| 36 ^^^^^^^^^^^^^^ | |
| 37 | |
| 38 .. highlight:: text | |
| 39 | |
| 40 The procedure to compile these plugins is similar to that for the | |
| 41 :ref:`core of Orthanc <compiling>`. The following commands should work | |
| 42 for most UNIX-like distribution (including GNU/Linux):: | |
| 43 | |
| 44 $ mkdir BuildMySQL | |
| 45 $ cd BuildMySQL | |
| 46 $ cmake ../MySQL/ -DSTATIC_BUILD=ON -DCMAKE_BUILD_TYPE=Release | |
| 47 $ make | |
| 48 | |
| 49 The compilation will produce 2 shared libraries, each containing one plugin for Orthanc: | |
| 50 | |
| 51 * ``OrthancMySQLIndex`` replaces the default SQLite index of Orthanc by MySQL. | |
| 52 * ``OrthancMySQLStorage`` makes Orthanc store the DICOM files it receives into MySQL. | |
| 53 | |
| 54 | |
| 55 Microsoft Windows | |
| 56 ^^^^^^^^^^^^^^^^^ | |
| 57 | |
| 750 | 58 Pre-compiled binaries for Microsoft Windows 32bit `are also available |
|
358
011b01ccf52d
fixing external hyperlinks
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
270
diff
changeset
|
59 <https://www.orthanc-server.com/browse.php?path=/plugin-mysql>`__. |
| 154 | 60 |
| 61 | |
| 62 Dynamic linking on Ubuntu 16.04 | |
| 63 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ | |
| 64 | |
| 65 .. highlight:: text | |
| 66 | |
| 67 If static linking is not desired, here are build instructions for | |
| 68 Ubuntu 16.04 (provided build dependencies for the :ref:`core of | |
| 69 Orthanc <compiling>` have already been installed):: | |
| 70 | |
| 71 $ sudo apt-get install libmysqlclient-dev | |
| 72 $ mkdir BuildMySQL | |
| 73 $ cd BuildMySQL | |
| 74 $ cmake ../MySQL/ -DCMAKE_BUILD_TYPE=Release \ | |
| 75 -DALLOW_DOWNLOADS=ON \ | |
| 76 -DUSE_SYSTEM_GOOGLE_TEST=OFF \ | |
| 77 -DUSE_SYSTEM_ORTHANC_SDK=OFF | |
| 78 $ make | |
| 79 | |
| 80 | |
| 81 Usage | |
| 82 ----- | |
| 83 | |
| 84 You of course first have to :ref:`install Orthanc <binaries>`, with a | |
| 85 version above 0.9.5. You then have to **create a database** dedicated | |
| 86 to Orthanc on some MySQL/MariaDB server. Please refer to the `MySQL | |
| 87 documentation | |
| 88 <https://dev.mysql.com/doc/refman/8.0/en/database-use.html>`__. | |
| 89 | |
| 90 .. highlight:: json | |
| 91 | |
| 92 Once Orthanc is installed and the database is created, you must add a | |
| 93 section in the :ref:`configuration file <configuration>` that | |
| 94 specifies the address of the **MySQL/MariaDB server together with your | |
| 95 credentials**. You also have to tell Orthanc in which path it can find | |
| 96 the plugins: This is done by properly modifying the ``Plugins`` | |
| 97 option. You could for instance adapt the following configuration | |
| 98 file:: | |
| 99 | |
| 100 { | |
| 101 "Name" : "MyOrthanc", | |
| 102 "MySQL" : { | |
| 103 "EnableIndex" : true, | |
| 104 "EnableStorage" : true, | |
| 158 | 105 "Host" : "localhost", // For TCP connections (notably Windows) |
| 106 "Port" : 3306, // For TCP connections (notably Windows) | |
| 154 | 107 "UnixSocket" : "/var/run/mysqld/mysqld.sock", // For UNIX on localhost |
| 108 "Database" : "orthanc", | |
| 109 "Username" : "orthanc", | |
| 110 "Password" : "orthanc", | |
| 465 | 111 "EnableSsl" : false, // force SSL connections |
|
643
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
112 "SslVerifyServerCertificates": true, // Verify server certificates if EnableSsl is true |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
113 "SslCACertificates": "", // Path to CA certificates to validate servers |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
114 "Lock" : true, // See section about Locking |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
115 "MaximumConnectionRetries" : 10, // New in release 3.0 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
116 "ConnectionRetryInterval" : 5, // New in release 3.0 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
117 "IndexConnectionsCount" : 1 // New in release 4.0 |
| 154 | 118 }, |
| 119 "Plugins" : [ | |
| 120 "/home/user/orthanc-databases/BuildMySQL/libOrthancMySQLIndex.so", | |
| 121 "/home/user/orthanc-databases/BuildMySQL/libOrthancMySQLStorage.so" | |
| 122 ] | |
| 123 } | |
| 124 | |
| 158 | 125 **Important 1:** The ``EnableIndex`` and ``EnableStorage`` options must |
| 154 | 126 be explicitly set to ``true``, otherwise Orthanc will continue to use |
| 127 its default SQLite back-end and the filesystem storage area. | |
| 128 | |
| 158 | 129 **Important 2:** To force a TCP connection on the ``localhost`` in |
| 130 UNIX (i.e. to instruct Orthanc not to use UNIX socket), the | |
| 131 ``UnixSocket`` can be set to the empty string. | |
| 132 | |
| 465 | 133 **Remark:** To force using a TLS connection, you must set ``EnableSsl`` |
| 134 to ``true``. Once ``EnableSsl`` is ``true``, the ``SslVerifyServerCertificates`` | |
| 135 enables the check of server certificates (``true`` by default). | |
| 136 The CA certificates used to verify the server certificate can be defined | |
| 137 through ``SslCACertificates``; if not defined or empty, the value of the global | |
| 138 Orthanc configuration ``HttpsCACertificates`` is used. These options have been | |
| 139 introduced in the mainline in July 2020 and have not yet been released. | |
| 140 | |
| 154 | 141 **Remark:** When using the ``Storage`` MySQL plugin, the DICOM files |
| 158 | 142 are stored as blobs in the database. This might actually consume more |
| 154 | 143 space than the DICOM file itself. |
| 144 | |
| 145 Note that a typical usage of the MySQL plugin is to enable only the | |
| 146 ``Index``, using the default filesystem storage for DICOM files. | |
| 147 | |
| 148 | |
| 149 | |
| 150 .. highlight:: text | |
| 151 | |
| 152 Orthanc must of course be **restarted** after the modification of its | |
| 153 configuration file. The log will contain an output similar to:: | |
| 154 | |
| 155 $ ./Orthanc Configuration.json | |
| 156 W0710 14:25:35.143828 main.cpp:1298] Orthanc version: 1.3.2 | |
| 157 W0710 14:25:35.146528 OrthancInitialization.cpp:120] Reading the configuration from: "./Configuration.json" | |
| 158 [...] | |
| 159 W0710 14:25:35.173652 main.cpp:671] Loading plugin(s) from: /home/jodogne/Subversion/orthanc-databases/BuildMySQL/libOrthancMySQLIndex.so | |
| 160 W0710 14:25:35.175927 PluginsManager.cpp:269] Registering plugin 'mysql-index' (version mainline) | |
| 161 W0710 14:25:35.176213 PluginsManager.cpp:168] Performance warning: The database index plugin was compiled against an old version of the Orthanc SDK, consider upgrading | |
| 162 W0710 14:25:35.176323 main.cpp:671] Loading plugin(s) from: /home/jodogne/Subversion/orthanc-databases/BuildMySQL/libOrthancMySQLStorage.so | |
| 163 W0710 14:25:35.177172 PluginsManager.cpp:269] Registering plugin 'mysql-storage' (version mainline) | |
| 164 W0710 14:25:35.180684 PluginsManager.cpp:168] Your MySQL server cannot store DICOM files larger than 16MB | |
| 165 W0710 14:25:35.180714 PluginsManager.cpp:168] => Consider increasing "max_allowed_packet" in "my.cnf" if this limit is insufficient for your use | |
| 166 W0710 14:25:35.246150 main.cpp:1098] Using a custom database from plugins | |
| 167 W0710 14:25:35.246210 main.cpp:1109] Using a custom storage area from plugins | |
| 168 [...] | |
| 169 W0710 14:25:37.073633 main.cpp:683] Orthanc has started | |
| 170 | |
| 171 | |
| 172 | |
| 173 Advanced options | |
| 174 ---------------- | |
| 175 | |
| 176 Several advanced options are available as well to fine-tune the | |
| 177 configuration of the MySQL plugins. They are documented below. | |
| 178 | |
| 179 | |
|
643
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
180 .. _mysql-multiple-writers: |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
181 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
182 Multiple writers or connections |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
183 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
184 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
185 Starting with Orthanc 1.9.2 and MySQL 4.0, it is possible to use |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
186 :ref:`multiple writers or connections in large-scale deployments |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
187 <multiple-writers>`. Here is the list of configuration that control |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
188 this behavior: |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
189 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
190 * ``Lock`` must be set to ``false`` (cf. :ref:`below <mysql-lock>`) |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
191 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
192 * ``MaximumConnectionRetries`` governs how many times Orthanc tries to |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
193 connect to the database, as well as how many times Orthanc replays |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
194 transactions to deal with collisions between multiple writers. |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
195 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
196 * ``IndexConnectionsCount`` controls the number of connections from |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
197 the index plugin to the MySQL database. It is set to ``1`` by |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
198 default, which corresponds to the old behavior of Orthanc <= 1.9.1. |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
199 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
200 * ``ConnectionRetryInterval`` is only used when opening one database |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
201 connection to MySQL. |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
202 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
203 * As of release 4.0, the MySQL plugin does **not** support yet the |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
204 :ref:`revision mechanism <revisions>` to protect metadata and |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
205 attachments from concurrent modifications. |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
206 |
|
411e82bb3a9f
documenting revisions and multiple writers
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
465
diff
changeset
|
207 |
| 846 | 208 .. _mysql-lock: |
| 209 | |
| 154 | 210 Locking |
| 211 ^^^^^^^ | |
| 212 | |
| 213 .. highlight:: json | |
| 214 | |
| 215 By default, the plugins lock the database (using `MySQL/MariaDB | |
| 216 "GET_LOCK()" | |
|
358
011b01ccf52d
fixing external hyperlinks
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
270
diff
changeset
|
217 <https://dev.mysql.com/doc/refman/8.0/en/locking-functions.html>`__) |
| 154 | 218 to prevent other instances of Orthanc from using the same database. If |
| 219 you want several instances of Orthanc to share the same database, set | |
| 220 the ``Lock`` option to ``false`` in the configuration file. | |
| 221 | |
|
270
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
231
diff
changeset
|
222 In the absence of locking, the same limitation apply to the |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
231
diff
changeset
|
223 MySQL/MariaDB plugins than to the PostgreSQL plugins (i.e. at most one |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
231
diff
changeset
|
224 instance of Orthanc writing to the database). For more information, |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
231
diff
changeset
|
225 please check out the :ref:`documentation for PostgreSQL |
|
f29d75bc5c25
more info about database locking
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
231
diff
changeset
|
226 <postgresql-lock>`. |
| 438 | 227 |
| 228 Scalability | |
| 229 ^^^^^^^^^^^ | |
| 230 | |
| 684 | 231 When configuring your MySQL plugin, ensure you've read the |
| 232 :ref:`scalability section <scalability>` | |
| 233 | |
| 234 | |
| 235 Backup | |
| 236 ------ | |
| 237 | |
| 238 The MySQL plugin uses stored routines (i.e. functions/procedures) that | |
| 239 are not archived by default by the ``mysqldump`` tool. As a | |
| 240 consequence, make sure to add the ``--routines`` command-line flag to | |
| 241 also archive such routines in your backup. |