2 This patch fixes several problems: it fixes incorrect or incomplete error
3 messages; it fixes a problem opening the SQLite3 database when multiple
4 simultaneous jobs were running; it fixes a bug with certain versions of
5 MySQL where batch inserts failed because of table name character case
6 (upper/lower) differences.
8 It can be applied to version 2.2.4 (and possibly earlier 2.2.x versions)
12 patch -p0 <2.2.4-sql.patch
13 ./configure (your options)
21 ===================================================================
22 --- src/cats/sql.c (revision 5687)
23 +++ src/cats/sql.c (working copy)
26 bacula_db_version = 0;
27 if (!db_sql_query(mdb, query, int_handler, (void *)&bacula_db_version)) {
28 - Mmsg(mdb->errmsg, "Database not created or server not running.\n");
29 Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
32 Index: src/cats/sqlite.c
33 ===================================================================
34 --- src/cats/sqlite.c (revision 5687)
35 +++ src/cats/sqlite.c (working copy)
45 mdb->connected = FALSE;
47 if ((errstat=rwl_init(&mdb->lock)) != 0) {
49 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
51 + be.bstrerror(errstat));
59 + for (mdb->db=NULL; !mdb->db && retry++ < 10; ) {
61 - int stat = sqlite3_open(db_name, &mdb->db);
62 - if (stat != SQLITE_OK) {
63 - mdb->sqlite_errmsg = (char *)sqlite3_errmsg(mdb->db);
64 - sqlite3_close(mdb->db);
67 - mdb->sqlite_errmsg = NULL;
69 -#ifdef SQLITE3_INIT_QUERY
70 - db_sql_query(mdb, SQLITE3_INIT_QUERY, NULL, NULL);
73 + int stat = sqlite3_open(db_name, &mdb->db);
74 + if (stat != SQLITE_OK) {
75 + mdb->sqlite_errmsg = (char *)sqlite3_errmsg(mdb->db);
76 + sqlite3_close(mdb->db);
79 + mdb->sqlite_errmsg = NULL;
82 - mdb->db = sqlite_open(
83 - db_name, /* database name */
85 - &mdb->sqlite_errmsg); /* error message */
86 + mdb->db = sqlite_open(
87 + db_name, /* database name */
89 + &mdb->sqlite_errmsg); /* error message */
92 - Dmsg0(300, "sqlite_open\n");
94 + Dmsg0(300, "sqlite_open\n");
99 if (mdb->db == NULL) {
100 Mmsg2(&mdb->errmsg, _("Unable to open Database=%s. ERR=%s\n"),
101 db_name, mdb->sqlite_errmsg ? mdb->sqlite_errmsg : _("unknown"));
104 mdb->connected = true;
106 - if (!check_tables_version(jcr, mdb)) {
111 /* set busy handler to wait when we use mult_db_connections = 1 */
114 sqlite_busy_handler(mdb->db, my_busy_handler, NULL);
117 +#if defined(HAVE_SQLITE3) && defined(SQLITE3_INIT_QUERY)
118 + db_sql_query(mdb, SQLITE3_INIT_QUERY, NULL, NULL);
121 + if (!check_tables_version(jcr, mdb)) {
130 @@ -448,16 +456,20 @@
131 return mdb->fields[mdb->field++];
134 -char *my_sqlite_batch_lock_query = "BEGIN";
135 -char *my_sqlite_batch_unlock_query = "COMMIT";
136 -char *my_sqlite_batch_fill_path_query = "INSERT INTO Path (Path) "
137 - " SELECT DISTINCT Path FROM batch "
138 - " EXCEPT SELECT Path FROM Path ";
139 +#ifdef HAVE_BATCH_FILE_INSERT
140 +const char *my_sqlite_batch_lock_query = "BEGIN";
141 +const char *my_sqlite_batch_unlock_query = "COMMIT";
143 -char *my_sqlite_batch_fill_filename_query = "INSERT INTO Filename (Name) "
144 - " SELECT DISTINCT Name FROM batch "
145 - " EXCEPT SELECT Name FROM Filename ";
146 +const char *my_sqlite_batch_fill_path_query =
147 + "INSERT INTO Path (Path)"
148 + " SELECT DISTINCT Path FROM batch"
149 + " EXCEPT SELECT Path FROM Path";
151 +const char *my_sqlite_batch_fill_filename_query =
152 + "INSERT INTO Filename (Name)"
153 + " SELECT DISTINCT Name FROM batch "
154 + " EXCEPT SELECT Name FROM Filename";
155 +#endif /* HAVE_BATCH_FILE_INSERT */
158 #endif /* HAVE_SQLITE */
159 Index: src/cats/cats.h
160 ===================================================================
161 --- src/cats/cats.h (revision 5687)
162 +++ src/cats/cats.h (working copy)
163 @@ -187,10 +187,10 @@
164 int my_sqlite_query(B_DB *mdb, const char *cmd);
165 void my_sqlite_field_seek(B_DB *mdb, int field);
166 SQL_FIELD *my_sqlite_fetch_field(B_DB *mdb);
167 -extern char* my_sqlite_batch_lock_query;
168 -extern char* my_sqlite_batch_unlock_query;
169 -extern char* my_sqlite_batch_fill_filename_query;
170 -extern char* my_sqlite_batch_fill_path_query;
171 +extern const char* my_sqlite_batch_lock_query;
172 +extern const char* my_sqlite_batch_unlock_query;
173 +extern const char* my_sqlite_batch_fill_filename_query;
174 +extern const char* my_sqlite_batch_fill_path_query;
178 @@ -317,10 +317,10 @@
179 int my_sqlite_query(B_DB *mdb, const char *cmd);
180 void my_sqlite_field_seek(B_DB *mdb, int field);
181 SQL_FIELD *my_sqlite_fetch_field(B_DB *mdb);
182 -extern char* my_sqlite_batch_lock_query;
183 -extern char* my_sqlite_batch_unlock_query;
184 -extern char* my_sqlite_batch_fill_filename_query;
185 -extern char* my_sqlite_batch_fill_path_query;
186 +extern const char* my_sqlite_batch_lock_query;
187 +extern const char* my_sqlite_batch_unlock_query;
188 +extern const char* my_sqlite_batch_fill_filename_query;
189 +extern const char* my_sqlite_batch_fill_path_query;
193 @@ -398,11 +398,11 @@
194 #define sql_batch_fill_path_query my_mysql_batch_fill_path_query
197 -extern char* my_mysql_batch_lock_path_query;
198 -extern char* my_mysql_batch_lock_filename_query;
199 -extern char* my_mysql_batch_unlock_tables_query;
200 -extern char* my_mysql_batch_fill_filename_query;
201 -extern char* my_mysql_batch_fill_path_query;
202 +extern const char* my_mysql_batch_lock_path_query;
203 +extern const char* my_mysql_batch_lock_filename_query;
204 +extern const char* my_mysql_batch_unlock_tables_query;
205 +extern const char* my_mysql_batch_fill_filename_query;
206 +extern const char* my_mysql_batch_fill_path_query;
207 extern void my_mysql_free_result(B_DB *mdb);
210 @@ -486,11 +486,11 @@
211 int my_postgresql_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar);
212 char *my_postgresql_copy_escape(char *dest, char *src, size_t len);
214 -extern char* my_pg_batch_lock_path_query;
215 -extern char* my_pg_batch_lock_filename_query;
216 -extern char* my_pg_batch_unlock_tables_query;
217 -extern char* my_pg_batch_fill_filename_query;
218 -extern char* my_pg_batch_fill_path_query;
219 +extern const char* my_pg_batch_lock_path_query;
220 +extern const char* my_pg_batch_lock_filename_query;
221 +extern const char* my_pg_batch_unlock_tables_query;
222 +extern const char* my_pg_batch_fill_filename_query;
223 +extern const char* my_pg_batch_fill_path_query;
225 /* "Generic" names for easier conversion */
226 #define sql_store_result(x) ((x)->result)
227 Index: src/cats/mysql.c
228 ===================================================================
229 --- src/cats/mysql.c (revision 5687)
230 +++ src/cats/mysql.c (working copy)
234 if ((errstat=rwl_init(&mdb->lock)) != 0) {
236 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
237 - strerror(errstat));
238 + be.bstrerror(errstat));
242 @@ -403,33 +404,27 @@
246 -char *my_mysql_batch_lock_path_query = "LOCK TABLES Path write, "
248 - " Path as p write ";
249 +#ifdef HAVE_BATCH_FILE_INSERT
250 +const char *my_mysql_batch_lock_path_query =
251 + "LOCK TABLES Path write, batch write, Path as p write";
254 -char *my_mysql_batch_lock_filename_query = "LOCK TABLES Filename write, "
256 - " Filename as f write ";
257 +const char *my_mysql_batch_lock_filename_query =
258 + "LOCK TABLES Filename write, batch write, Filename as f write";
260 -char *my_mysql_batch_unlock_tables_query = "UNLOCK TABLES";
261 +const char *my_mysql_batch_unlock_tables_query = "UNLOCK TABLES";
263 -char *my_mysql_batch_fill_path_query = "INSERT INTO Path (Path) "
264 - " SELECT a.Path FROM "
265 - " (SELECT DISTINCT Path "
266 - " FROM batch) AS a "
267 - " WHERE NOT EXISTS "
270 - " WHERE p.Path = a.Path) ";
271 +const char *my_mysql_batch_fill_path_query =
272 + "INSERT INTO Path (Path) "
273 + "SELECT a.Path FROM "
274 + "(SELECT DISTINCT Path FROM batch) AS a WHERE NOT EXISTS "
275 + "(SELECT Path FROM Path AS p WHERE p.Path = a.Path)";
277 -char *my_mysql_batch_fill_filename_query = "INSERT INTO Filename (Name) "
278 - " SELECT a.Name FROM "
279 - " (SELECT DISTINCT Name "
280 - " FROM batch) AS a "
281 - " WHERE NOT EXISTS "
283 - " FROM Filename AS f "
284 - " WHERE f.Name = a.Name) ";
285 +const char *my_mysql_batch_fill_filename_query =
286 + "INSERT INTO Filename (Name) "
287 + "SELECT a.Name FROM "
288 + "(SELECT DISTINCT Name FROM batch) AS a WHERE NOT EXISTS "
289 + "(SELECT Name FROM Filename AS f WHERE f.Name = a.Name)";
290 +#endif /* HAVE_BATCH_FILE_INSERT */
292 #endif /* HAVE_MYSQL */
293 Index: src/cats/sql_create.c
294 ===================================================================
295 --- src/cats/sql_create.c (revision 5687)
296 +++ src/cats/sql_create.c (working copy)
301 +#ifdef HAVE_BATCH_FILE_INSERT
303 /* All sql_batch_* functions are used to do bulk batch insert in File/Filename/Path
304 * tables. This code can be activated by adding "#define HAVE_BATCH_FILE_INSERT 1"
306 @@ -690,13 +692,13 @@
309 ok = db_sql_query(mdb,
310 - " CREATE TEMPORARY TABLE batch "
311 - " (fileindex integer, "
315 - " lstat tinyblob, "
316 - " md5 tinyblob) ",NULL, NULL);
317 + "CREATE TEMPORARY TABLE batch ("
318 + "FileIndex integer,"
323 + "MD5 tinyblob)",NULL, NULL);
331 -#ifdef HAVE_BATCH_FILE_INSERT
337 if (!db_sql_query(jcr->db_batch,sql_batch_fill_filename_query, NULL,NULL)) {
338 Jmsg(jcr,M_FATAL,0,"Can't fill Filename table %s\n",jcr->db_batch->errmsg);
339 - QUERY_DB(jcr, jcr->db_batch, sql_batch_unlock_tables_query);
340 + db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL);
344 @@ -804,12 +805,12 @@
347 if (!db_sql_query(jcr->db_batch,
348 - " INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5)"
349 - " SELECT batch.FileIndex, batch.JobId, Path.PathId, "
350 - " Filename.FilenameId,batch.LStat, batch.MD5 "
352 - " JOIN Path ON (batch.Path = Path.Path) "
353 - " JOIN Filename ON (batch.Name = Filename.Name) ",
354 + "INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5)"
355 + "SELECT batch.FileIndex, batch.JobId, Path.PathId, "
356 + "Filename.FilenameId,batch.LStat, batch.MD5 "
358 + "JOIN Path ON (batch.Path = Path.Path) "
359 + "JOIN Filename ON (batch.Name = Filename.Name)",
362 Jmsg(jcr, M_FATAL, 0, "Can't fill File table %s\n", jcr->db_batch->errmsg);
363 @@ -845,19 +846,24 @@
366 1 /* multi_db = true */);
367 + if (!jcr->db_batch) {
368 + Mmsg1(&mdb->errmsg, _("Could not init batch database: \"%s\".\n"),
370 + Jmsg1(jcr, M_FATAL, 0, "%s", mdb->errmsg);
374 - if (!jcr->db_batch || !db_open_database(jcr, jcr->db_batch)) {
375 - Jmsg(jcr, M_FATAL, 0, _("Could not open database \"%s\".\n"),
377 - if (jcr->db_batch) {
378 - Jmsg(jcr, M_FATAL, 0, "%s", db_strerror(jcr->db_batch));
380 + if (!db_open_database(jcr, jcr->db_batch)) {
381 + Mmsg2(&mdb->errmsg, _("Could not open database \"%s\": ERR=%s\n"),
382 + jcr->db->db_name, db_strerror(jcr->db_batch));
383 + Jmsg1(jcr, M_FATAL, 0, "%s", mdb->errmsg);
387 if (!sql_batch_start(jcr, jcr->db_batch)) {
388 - Jmsg(jcr, M_FATAL, 0,
389 - "Can't start batch mode %s", db_strerror(jcr->db_batch));
390 + Mmsg1(&mdb->errmsg,
391 + "Can't start batch mode: ERR=%s", db_strerror(jcr->db_batch));
392 + Jmsg1(jcr, M_FATAL, 0, "%s", mdb->errmsg);
395 Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", jcr->db_batch->ref_count,
396 @@ -870,10 +876,10 @@
398 if (!(ar->Stream == STREAM_UNIX_ATTRIBUTES ||
399 ar->Stream == STREAM_UNIX_ATTRIBUTES_EX)) {
400 - Mmsg1(&bdb->errmsg, _("Attempt to put non-attributes into catalog. Stream=%d\n"),
401 + Mmsg1(&mdb->errmsg, _("Attempt to put non-attributes into catalog. Stream=%d\n"),
403 - Jmsg(jcr, M_ERROR, 0, "%s", bdb->errmsg);
405 + Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
409 split_path_and_file(jcr, bdb, ar->fname);
410 Index: src/cats/postgresql.c
411 ===================================================================
412 --- src/cats/postgresql.c (revision 5687)
413 +++ src/cats/postgresql.c (working copy)
414 @@ -605,13 +605,13 @@
415 Dmsg0(500, "my_postgresql_batch_start started\n");
417 if (my_postgresql_query(mdb,
418 - " CREATE TEMPORARY TABLE batch "
419 - " (fileindex int, "
424 - " md5 varchar)") == 1)
425 + "CREATE TEMPORARY TABLE batch ("
431 + "md5 varchar)") == 1)
433 Dmsg0(500, "my_postgresql_batch_start failed\n");
435 @@ -785,22 +785,29 @@
439 -char *my_pg_batch_lock_path_query = "BEGIN; LOCK TABLE Path IN SHARE ROW EXCLUSIVE MODE";
440 +#ifdef HAVE_BATCH_FILE_INSERT
441 +const char *my_pg_batch_lock_path_query =
442 + "BEGIN; LOCK TABLE Path IN SHARE ROW EXCLUSIVE MODE";
445 -char *my_pg_batch_lock_filename_query = "BEGIN; LOCK TABLE Filename IN SHARE ROW EXCLUSIVE MODE";
446 +const char *my_pg_batch_lock_filename_query =
447 + "BEGIN; LOCK TABLE Filename IN SHARE ROW EXCLUSIVE MODE";
449 -char *my_pg_batch_unlock_tables_query = "COMMIT";
450 +const char *my_pg_batch_unlock_tables_query = "COMMIT";
452 -char *my_pg_batch_fill_path_query = "INSERT INTO Path (Path) "
453 - " SELECT a.Path FROM "
454 - " (SELECT DISTINCT Path FROM batch) AS a "
455 - " WHERE NOT EXISTS (SELECT Path FROM Path WHERE Path = a.Path) ";
456 +const char *my_pg_batch_fill_path_query =
457 + "INSERT INTO Path (Path) "
458 + "SELECT a.Path FROM "
459 + "(SELECT DISTINCT Path FROM batch) AS a "
460 + "WHERE NOT EXISTS (SELECT Path FROM Path WHERE Path = a.Path) ";
463 -char *my_pg_batch_fill_filename_query = "INSERT INTO Filename (Name) "
464 - " SELECT a.Name FROM "
465 - " (SELECT DISTINCT Name FROM batch) as a "
466 - " WHERE NOT EXISTS "
467 - " (SELECT Name FROM Filename WHERE Name = a.Name)";
468 +const char *my_pg_batch_fill_filename_query =
469 + "INSERT INTO Filename (Name) "
470 + "SELECT a.Name FROM "
471 + "(SELECT DISTINCT Name FROM batch) as a "
472 + "WHERE NOT EXISTS "
473 + "(SELECT Name FROM Filename WHERE Name = a.Name)";
474 +#endif /* HAVE_BATCH_FILE_INSERT */
476 #endif /* HAVE_POSTGRESQL */