Mercurial > hg > orthanc-databases

comparison MySQL/Plugins/MySQLIndex.cpp @ 144:740d9829f52e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
handling of errors if MySQL user cannot CREATE TRIGGER
author Sebastien Jodogne <s.jodogne@gmail.com>
date Thu, 12 Mar 2020 12:10:52 +0100
parents 4cd7e45b671e
children 063aa53b5917
comparison
equal deleted inserted replaced
142:bbd57f5672fa 144:740d9829f52e
33 33
34 #include <ctype.h> 34 #include <ctype.h>
35 35
36 namespace OrthancDatabases 36 namespace OrthancDatabases
37 { 37 {
38 static void ThrowCannotCreateTrigger()
39 {
40 LOG(ERROR) << "The MySQL user is not allowed to create triggers => 2 possible solutions:";
41 LOG(ERROR) << " 1- Give the SUPER privilege to the MySQL database user, or";
42 LOG(ERROR) << " 2- Run \"set global log_bin_trust_function_creators=1;\" as MySQL root user.";
43 LOG(ERROR) << "Once you are done, drop and recreate the MySQL database";
44 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database,
45 "Need to fix the MySQL permissions for \"CREATE TRIGGER\"");
46 }
47
38 IDatabase* MySQLIndex::OpenInternal() 48 IDatabase* MySQLIndex::OpenInternal()
39 { 49 {
40 uint32_t expectedVersion = 6; 50 uint32_t expectedVersion = 6;
41 if (context_) 51 if (context_)
42 { 52 {
72 db->Open(); 82 db->Open();
73 db->Execute("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE", false); 83 db->Execute("SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE", false);
74 84
75 { 85 {
76 MySQLDatabase::TransientAdvisoryLock lock(*db, MYSQL_LOCK_DATABASE_SETUP); 86 MySQLDatabase::TransientAdvisoryLock lock(*db, MYSQL_LOCK_DATABASE_SETUP);
77 MySQLTransaction t(*db); 87
78 88 /**
79 db->Execute("ALTER DATABASE " + parameters_.GetDatabase() + 89 * In a first transaction, we create the tables. Such a
80 " CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci", false); 90 * transaction cannot be rollback: "The CREATE TABLE statement
81 91 * in InnoDB is processed as a single transaction. This means
82 if (!db->DoesTableExist(t, "Resources")) 92 * that a ROLLBACK from the user does not undo CREATE TABLE
83 { 93 * statements the user made during that transaction."
84 std::string query; 94 * https://dev.mysql.com/doc/refman/8.0/en/implicit-commit.html
85 95 *
86 Orthanc::EmbeddedResources::GetFileResource 96 * As a consequence, we delay the initial population of the
87 (query, Orthanc::EmbeddedResources::MYSQL_PREPARE_INDEX); 97 * tables in a sequence of transactions below. This solves the
88 db->Execute(query, true); 98 * error message "MySQL plugin is incompatible with database
89 99 * schema version: 0" that was reported in the forum:
90 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabaseSchemaVersion, expectedVersion); 100 * https://groups.google.com/d/msg/orthanc-users/OCFFkm1qm0k/Mbroy8VWAQAJ
91 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabasePatchLevel, 1); 101 **/
92 } 102 {
93 103 MySQLTransaction t(*db);
94 if (!db->DoesTableExist(t, "Resources")) 104
95 { 105 db->Execute("ALTER DATABASE " + parameters_.GetDatabase() +
96 LOG(ERROR) << "Corrupted MySQL database"; 106 " CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci", false);
97 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); 107
98 } 108 // This is the first table to be created
109 if (!db->DoesTableExist(t, "GlobalProperties"))
110 {
111 std::string query;
112
113 Orthanc::EmbeddedResources::GetFileResource
114 (query, Orthanc::EmbeddedResources::MYSQL_PREPARE_INDEX);
115 db->Execute(query, true);
116 }
117
118 t.Commit();
119 }
120
121 /**
122 * This is the sequence of transactions that initially populate
123 * the database. WARNING - As table creation cannot be rollback,
124 * don't forget to add "IF NOT EXISTS" if some table must be
125 * created below this point (in order to recover from failed
126 * transaction).
127 **/
99 128
100 int version = 0; 129 int version = 0;
101 if (!LookupGlobalIntegerProperty(version, *db, t, Orthanc::GlobalProperty_DatabaseSchemaVersion) || 130
102 version != 6) 131 {
103 { 132 MySQLTransaction t(*db);
104 LOG(ERROR) << "MySQL plugin is incompatible with database schema version: " << version; 133
105 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); 134 // This is the last table to be created
106 } 135 if (!db->DoesTableExist(t, "PatientRecyclingOrder"))
107 136 {
108 int revision; 137 LOG(ERROR) << "Corrupted MySQL database";
109 if (!LookupGlobalIntegerProperty(revision, *db, t, Orthanc::GlobalProperty_DatabasePatchLevel)) 138 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
110 { 139 }
111 revision = 1; 140
112 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabasePatchLevel, revision); 141 // This is the last item to be created
142 if (!db->DoesTriggerExist(t, "PatientAdded"))
143 {
144 ThrowCannotCreateTrigger();
145 }
146
147 if (!LookupGlobalIntegerProperty(version, *db, t, Orthanc::GlobalProperty_DatabaseSchemaVersion))
148 {
149 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabaseSchemaVersion, expectedVersion);
150 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabasePatchLevel, 1);
151 version = expectedVersion;
152 }
153
154 if (version != 6)
155 {
156 LOG(ERROR) << "MySQL plugin is incompatible with database schema version: " << version;
157 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);
158 }
159
160 t.Commit();
161 }
162
163 int revision = 0;
164
165 {
166 MySQLTransaction t(*db);
167
168 if (!LookupGlobalIntegerProperty(revision, *db, t, Orthanc::GlobalProperty_DatabasePatchLevel))
169 {
170 revision = 1;
171 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabasePatchLevel, revision);
172 }
173
174 t.Commit();
113 } 175 }
114 176
115 if (revision == 1) 177 if (revision == 1)
116 { 178 {
179 MySQLTransaction t(*db);
180
117 // The serialization of jobs as a global property can lead to 181 // The serialization of jobs as a global property can lead to
118 // very long values => switch to the LONGTEXT type that can 182 // very long values => switch to the LONGTEXT type that can
119 // store up to 4GB: 183 // store up to 4GB:
120 // https://stackoverflow.com/a/13932834/881731 184 // https://stackoverflow.com/a/13932834/881731
121 db->Execute("ALTER TABLE GlobalProperties MODIFY value LONGTEXT", false); 185 db->Execute("ALTER TABLE GlobalProperties MODIFY value LONGTEXT", false);
122 186
123 revision = 2; 187 revision = 2;
124 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabasePatchLevel, revision); 188 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabasePatchLevel, revision);
189
190 t.Commit();
125 } 191 }
126 192
127 if (revision == 2) 193 if (revision == 2)
128 { 194 {
195 MySQLTransaction t(*db);
196
129 // Install the "GetLastChangeIndex" extension 197 // Install the "GetLastChangeIndex" extension
130 std::string query; 198 std::string query;
131 199
132 Orthanc::EmbeddedResources::GetFileResource 200 Orthanc::EmbeddedResources::GetFileResource
133 (query, Orthanc::EmbeddedResources::MYSQL_GET_LAST_CHANGE_INDEX); 201 (query, Orthanc::EmbeddedResources::MYSQL_GET_LAST_CHANGE_INDEX);
134 db->Execute(query, true); 202 db->Execute(query, true);
203
204 if (!db->DoesTriggerExist(t, "ChangeAdded"))
205 {
206 ThrowCannotCreateTrigger();
207 }
135 208
136 revision = 3; 209 revision = 3;
137 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabasePatchLevel, revision); 210 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabasePatchLevel, revision);
138 } 211
139 212 t.Commit();
213 }
214
140 if (revision == 3) 215 if (revision == 3)
141 { 216 {
217 MySQLTransaction t(*db);
218
142 // Reconfiguration of "Metadata" from TEXT type (up to 64KB) 219 // Reconfiguration of "Metadata" from TEXT type (up to 64KB)
143 // to the LONGTEXT type (up to 4GB). This might be important 220 // to the LONGTEXT type (up to 4GB). This might be important
144 // for applications such as the Osimis Web viewer that stores 221 // for applications such as the Osimis Web viewer that stores
145 // large amount of metadata. 222 // large amount of metadata.
146 // http://book.orthanc-server.com/faq/features.html#central-registry-of-metadata-and-attachments 223 // http://book.orthanc-server.com/faq/features.html#central-registry-of-metadata-and-attachments
147 db->Execute("ALTER TABLE Metadata MODIFY value LONGTEXT", false); 224 db->Execute("ALTER TABLE Metadata MODIFY value LONGTEXT", false);
148 225
149 revision = 4; 226 revision = 4;
150 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabasePatchLevel, revision); 227 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabasePatchLevel, revision);
151 } 228
152 229 t.Commit();
230 }
231
153 if (revision == 4) 232 if (revision == 4)
154 { 233 {
234 MySQLTransaction t(*db);
235
155 // Install the "CreateInstance" extension 236 // Install the "CreateInstance" extension
156 std::string query; 237 std::string query;
157 238
158 Orthanc::EmbeddedResources::GetFileResource 239 Orthanc::EmbeddedResources::GetFileResource
159 (query, Orthanc::EmbeddedResources::MYSQL_CREATE_INSTANCE); 240 (query, Orthanc::EmbeddedResources::MYSQL_CREATE_INSTANCE);
160 db->Execute(query, true); 241 db->Execute(query, true);
161 242
162 revision = 5; 243 revision = 5;
163 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabasePatchLevel, revision); 244 SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_DatabasePatchLevel, revision);
164 } 245
165 246 t.Commit();
247 }
248
166 if (revision != 5) 249 if (revision != 5)
167 { 250 {
168 LOG(ERROR) << "MySQL plugin is incompatible with database schema revision: " << revision; 251 LOG(ERROR) << "MySQL plugin is incompatible with database schema revision: " << revision;
169 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); 252 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);
170 } 253 }
171
172 t.Commit();
173 } 254 }
174 255
175 /** 256 /**
176 * WARNING: This lock must be acquired after 257 * WARNING: This lock must be acquired after
177 * "MYSQL_LOCK_DATABASE_SETUP" is released. Indeed, in MySQL < 258 * "MYSQL_LOCK_DATABASE_SETUP" is released. Indeed, in MySQL <