This patch fixes several problems: it fixes incorrect or incomplete error messages; it fixes a problem opening the SQLite3 database when multiple simultaneous jobs were running; it fixes a bug with certain versions of MySQL where batch inserts failed because of table name character case (upper/lower) differences. It can be applied to version 2.2.4 (and possibly earlier 2.2.x versions) with: cd patch -p0 <2.2.4-sql.patch ./configure (your options) make ... make install Index: src/cats/sql.c =================================================================== --- src/cats/sql.c (revision 5687) +++ src/cats/sql.c (working copy) @@ -115,7 +115,6 @@ bacula_db_version = 0; if (!db_sql_query(mdb, query, int_handler, (void *)&bacula_db_version)) { - Mmsg(mdb->errmsg, "Database not created or server not running.\n"); Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg); return false; } Index: src/cats/sqlite.c =================================================================== --- src/cats/sqlite.c (revision 5687) +++ src/cats/sqlite.c (working copy) @@ -148,6 +148,7 @@ int len; struct stat statbuf; int errstat; + int retry = 0; P(mutex); if (mdb->connected) { @@ -157,8 +158,9 @@ mdb->connected = FALSE; if ((errstat=rwl_init(&mdb->lock)) != 0) { + berrno be; Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"), - strerror(errstat)); + be.bstrerror(errstat)); V(mutex); return 0; } @@ -178,28 +180,28 @@ return 0; } + for (mdb->db=NULL; !mdb->db && retry++ < 10; ) { #ifdef HAVE_SQLITE3 - int stat = sqlite3_open(db_name, &mdb->db); - if (stat != SQLITE_OK) { - mdb->sqlite_errmsg = (char *)sqlite3_errmsg(mdb->db); - sqlite3_close(mdb->db); - mdb->db = NULL; - } else { - mdb->sqlite_errmsg = NULL; - } -#ifdef SQLITE3_INIT_QUERY - db_sql_query(mdb, SQLITE3_INIT_QUERY, NULL, NULL); -#endif - + int stat = sqlite3_open(db_name, &mdb->db); + if (stat != SQLITE_OK) { + mdb->sqlite_errmsg = (char *)sqlite3_errmsg(mdb->db); + sqlite3_close(mdb->db); + mdb->db = NULL; + } else { + mdb->sqlite_errmsg = NULL; + } #else - mdb->db = sqlite_open( - db_name, /* database name */ - 644, /* mode */ - &mdb->sqlite_errmsg); /* error message */ + mdb->db = sqlite_open( + db_name, /* database name */ + 644, /* mode */ + &mdb->sqlite_errmsg); /* error message */ #endif - Dmsg0(300, "sqlite_open\n"); - + Dmsg0(300, "sqlite_open\n"); + if (!mdb->db) { + bmicrosleep(1, 0); + } + } if (mdb->db == NULL) { Mmsg2(&mdb->errmsg, _("Unable to open Database=%s. ERR=%s\n"), db_name, mdb->sqlite_errmsg ? mdb->sqlite_errmsg : _("unknown")); @@ -209,10 +211,6 @@ } mdb->connected = true; free(db_name); - if (!check_tables_version(jcr, mdb)) { - V(mutex); - return 0; - } /* set busy handler to wait when we use mult_db_connections = 1 */ #ifdef HAVE_SQLITE3 @@ -221,6 +219,16 @@ sqlite_busy_handler(mdb->db, my_busy_handler, NULL); #endif +#if defined(HAVE_SQLITE3) && defined(SQLITE3_INIT_QUERY) + db_sql_query(mdb, SQLITE3_INIT_QUERY, NULL, NULL); +#endif + + if (!check_tables_version(jcr, mdb)) { + V(mutex); + return 0; + } + + V(mutex); return 1; } @@ -448,16 +456,20 @@ return mdb->fields[mdb->field++]; } -char *my_sqlite_batch_lock_query = "BEGIN"; -char *my_sqlite_batch_unlock_query = "COMMIT"; -char *my_sqlite_batch_fill_path_query = "INSERT INTO Path (Path) " - " SELECT DISTINCT Path FROM batch " - " EXCEPT SELECT Path FROM Path "; +#ifdef HAVE_BATCH_FILE_INSERT +const char *my_sqlite_batch_lock_query = "BEGIN"; +const char *my_sqlite_batch_unlock_query = "COMMIT"; -char *my_sqlite_batch_fill_filename_query = "INSERT INTO Filename (Name) " - " SELECT DISTINCT Name FROM batch " - " EXCEPT SELECT Name FROM Filename "; +const char *my_sqlite_batch_fill_path_query = + "INSERT INTO Path (Path)" + " SELECT DISTINCT Path FROM batch" + " EXCEPT SELECT Path FROM Path"; +const char *my_sqlite_batch_fill_filename_query = + "INSERT INTO Filename (Name)" + " SELECT DISTINCT Name FROM batch " + " EXCEPT SELECT Name FROM Filename"; +#endif /* HAVE_BATCH_FILE_INSERT */ #endif /* HAVE_SQLITE */ Index: src/cats/cats.h =================================================================== --- src/cats/cats.h (revision 5687) +++ src/cats/cats.h (working copy) @@ -187,10 +187,10 @@ int my_sqlite_query(B_DB *mdb, const char *cmd); void my_sqlite_field_seek(B_DB *mdb, int field); SQL_FIELD *my_sqlite_fetch_field(B_DB *mdb); -extern char* my_sqlite_batch_lock_query; -extern char* my_sqlite_batch_unlock_query; -extern char* my_sqlite_batch_fill_filename_query; -extern char* my_sqlite_batch_fill_path_query; +extern const char* my_sqlite_batch_lock_query; +extern const char* my_sqlite_batch_unlock_query; +extern const char* my_sqlite_batch_fill_filename_query; +extern const char* my_sqlite_batch_fill_path_query; #else @@ -317,10 +317,10 @@ int my_sqlite_query(B_DB *mdb, const char *cmd); void my_sqlite_field_seek(B_DB *mdb, int field); SQL_FIELD *my_sqlite_fetch_field(B_DB *mdb); -extern char* my_sqlite_batch_lock_query; -extern char* my_sqlite_batch_unlock_query; -extern char* my_sqlite_batch_fill_filename_query; -extern char* my_sqlite_batch_fill_path_query; +extern const char* my_sqlite_batch_lock_query; +extern const char* my_sqlite_batch_unlock_query; +extern const char* my_sqlite_batch_fill_filename_query; +extern const char* my_sqlite_batch_fill_path_query; #else @@ -398,11 +398,11 @@ #define sql_batch_fill_path_query my_mysql_batch_fill_path_query -extern char* my_mysql_batch_lock_path_query; -extern char* my_mysql_batch_lock_filename_query; -extern char* my_mysql_batch_unlock_tables_query; -extern char* my_mysql_batch_fill_filename_query; -extern char* my_mysql_batch_fill_path_query; +extern const char* my_mysql_batch_lock_path_query; +extern const char* my_mysql_batch_lock_filename_query; +extern const char* my_mysql_batch_unlock_tables_query; +extern const char* my_mysql_batch_fill_filename_query; +extern const char* my_mysql_batch_fill_path_query; extern void my_mysql_free_result(B_DB *mdb); #else @@ -486,11 +486,11 @@ int my_postgresql_batch_insert(JCR *jcr, B_DB *mdb, ATTR_DBR *ar); char *my_postgresql_copy_escape(char *dest, char *src, size_t len); -extern char* my_pg_batch_lock_path_query; -extern char* my_pg_batch_lock_filename_query; -extern char* my_pg_batch_unlock_tables_query; -extern char* my_pg_batch_fill_filename_query; -extern char* my_pg_batch_fill_path_query; +extern const char* my_pg_batch_lock_path_query; +extern const char* my_pg_batch_lock_filename_query; +extern const char* my_pg_batch_unlock_tables_query; +extern const char* my_pg_batch_fill_filename_query; +extern const char* my_pg_batch_fill_path_query; /* "Generic" names for easier conversion */ #define sql_store_result(x) ((x)->result) Index: src/cats/mysql.c =================================================================== --- src/cats/mysql.c (revision 5687) +++ src/cats/mysql.c (working copy) @@ -149,8 +149,9 @@ } if ((errstat=rwl_init(&mdb->lock)) != 0) { + berrno be; Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"), - strerror(errstat)); + be.bstrerror(errstat)); V(mutex); return 0; } @@ -403,33 +404,27 @@ db_unlock(mdb); } -char *my_mysql_batch_lock_path_query = "LOCK TABLES Path write, " - " batch write, " - " Path as p write "; +#ifdef HAVE_BATCH_FILE_INSERT +const char *my_mysql_batch_lock_path_query = + "LOCK TABLES Path write, batch write, Path as p write"; -char *my_mysql_batch_lock_filename_query = "LOCK TABLES Filename write, " - " batch write, " - " Filename as f write "; +const char *my_mysql_batch_lock_filename_query = + "LOCK TABLES Filename write, batch write, Filename as f write"; -char *my_mysql_batch_unlock_tables_query = "UNLOCK TABLES"; +const char *my_mysql_batch_unlock_tables_query = "UNLOCK TABLES"; -char *my_mysql_batch_fill_path_query = "INSERT INTO Path (Path) " - " SELECT a.Path FROM " - " (SELECT DISTINCT Path " - " FROM batch) AS a " - " WHERE NOT EXISTS " - " (SELECT Path " - " FROM Path AS p " - " WHERE p.Path = a.Path) "; +const char *my_mysql_batch_fill_path_query = + "INSERT INTO Path (Path) " + "SELECT a.Path FROM " + "(SELECT DISTINCT Path FROM batch) AS a WHERE NOT EXISTS " + "(SELECT Path FROM Path AS p WHERE p.Path = a.Path)"; -char *my_mysql_batch_fill_filename_query = "INSERT INTO Filename (Name) " - " SELECT a.Name FROM " - " (SELECT DISTINCT Name " - " FROM batch) AS a " - " WHERE NOT EXISTS " - " (SELECT Name " - " FROM Filename AS f " - " WHERE f.Name = a.Name) "; +const char *my_mysql_batch_fill_filename_query = + "INSERT INTO Filename (Name) " + "SELECT a.Name FROM " + "(SELECT DISTINCT Name FROM batch) AS a WHERE NOT EXISTS " + "(SELECT Name FROM Filename AS f WHERE f.Name = a.Name)"; +#endif /* HAVE_BATCH_FILE_INSERT */ #endif /* HAVE_MYSQL */ Index: src/cats/sql_create.c =================================================================== --- src/cats/sql_create.c (revision 5687) +++ src/cats/sql_create.c (working copy) @@ -668,6 +668,8 @@ * }; */ +#ifdef HAVE_BATCH_FILE_INSERT + /* All sql_batch_* functions are used to do bulk batch insert in File/Filename/Path * tables. This code can be activated by adding "#define HAVE_BATCH_FILE_INSERT 1" * in baconfig.h @@ -690,13 +692,13 @@ db_lock(mdb); ok = db_sql_query(mdb, - " CREATE TEMPORARY TABLE batch " - " (fileindex integer, " - " jobid integer, " - " path blob, " - " name blob, " - " lstat tinyblob, " - " md5 tinyblob) ",NULL, NULL); + "CREATE TEMPORARY TABLE batch (" + "FileIndex integer," + "JobId integer," + "Path blob," + "Name blob," + "LStat tinyblob," + "MD5 tinyblob)",NULL, NULL); db_unlock(mdb); return ok; } @@ -746,7 +748,6 @@ return true; } -#ifdef HAVE_BATCH_FILE_INSERT /* * Returns 1 if OK * 0 if failed @@ -794,7 +795,7 @@ if (!db_sql_query(jcr->db_batch,sql_batch_fill_filename_query, NULL,NULL)) { Jmsg(jcr,M_FATAL,0,"Can't fill Filename table %s\n",jcr->db_batch->errmsg); - QUERY_DB(jcr, jcr->db_batch, sql_batch_unlock_tables_query); + db_sql_query(jcr->db_batch, sql_batch_unlock_tables_query, NULL, NULL); return false; } @@ -804,12 +805,12 @@ } if (!db_sql_query(jcr->db_batch, - " INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5)" - " SELECT batch.FileIndex, batch.JobId, Path.PathId, " - " Filename.FilenameId,batch.LStat, batch.MD5 " - " FROM batch " - " JOIN Path ON (batch.Path = Path.Path) " - " JOIN Filename ON (batch.Name = Filename.Name) ", + "INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5)" + "SELECT batch.FileIndex, batch.JobId, Path.PathId, " + "Filename.FilenameId,batch.LStat, batch.MD5 " + "FROM batch " + "JOIN Path ON (batch.Path = Path.Path) " + "JOIN Filename ON (batch.Name = Filename.Name)", NULL,NULL)) { Jmsg(jcr, M_FATAL, 0, "Can't fill File table %s\n", jcr->db_batch->errmsg); @@ -845,19 +846,24 @@ mdb->db_port, mdb->db_socket, 1 /* multi_db = true */); + if (!jcr->db_batch) { + Mmsg1(&mdb->errmsg, _("Could not init batch database: \"%s\".\n"), + jcr->db->db_name); + Jmsg1(jcr, M_FATAL, 0, "%s", mdb->errmsg); + return false; + } - if (!jcr->db_batch || !db_open_database(jcr, jcr->db_batch)) { - Jmsg(jcr, M_FATAL, 0, _("Could not open database \"%s\".\n"), - jcr->db->db_name); - if (jcr->db_batch) { - Jmsg(jcr, M_FATAL, 0, "%s", db_strerror(jcr->db_batch)); - } + if (!db_open_database(jcr, jcr->db_batch)) { + Mmsg2(&mdb->errmsg, _("Could not open database \"%s\": ERR=%s\n"), + jcr->db->db_name, db_strerror(jcr->db_batch)); + Jmsg1(jcr, M_FATAL, 0, "%s", mdb->errmsg); return false; } if (!sql_batch_start(jcr, jcr->db_batch)) { - Jmsg(jcr, M_FATAL, 0, - "Can't start batch mode %s", db_strerror(jcr->db_batch)); + Mmsg1(&mdb->errmsg, + "Can't start batch mode: ERR=%s", db_strerror(jcr->db_batch)); + Jmsg1(jcr, M_FATAL, 0, "%s", mdb->errmsg); return false; } Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", jcr->db_batch->ref_count, @@ -870,10 +876,10 @@ */ if (!(ar->Stream == STREAM_UNIX_ATTRIBUTES || ar->Stream == STREAM_UNIX_ATTRIBUTES_EX)) { - Mmsg1(&bdb->errmsg, _("Attempt to put non-attributes into catalog. Stream=%d\n"), + Mmsg1(&mdb->errmsg, _("Attempt to put non-attributes into catalog. Stream=%d\n"), ar->Stream); - Jmsg(jcr, M_ERROR, 0, "%s", bdb->errmsg); - return 0; + Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg); + return false; } split_path_and_file(jcr, bdb, ar->fname); Index: src/cats/postgresql.c =================================================================== --- src/cats/postgresql.c (revision 5687) +++ src/cats/postgresql.c (working copy) @@ -605,13 +605,13 @@ Dmsg0(500, "my_postgresql_batch_start started\n"); if (my_postgresql_query(mdb, - " CREATE TEMPORARY TABLE batch " - " (fileindex int, " - " jobid int, " - " path varchar, " - " name varchar, " - " lstat varchar, " - " md5 varchar)") == 1) + "CREATE TEMPORARY TABLE batch (" + "fileindex int," + "jobid int," + "path varchar," + "name varchar," + "lstat varchar," + "md5 varchar)") == 1) { Dmsg0(500, "my_postgresql_batch_start failed\n"); return 1; @@ -785,22 +785,29 @@ return dest; } -char *my_pg_batch_lock_path_query = "BEGIN; LOCK TABLE Path IN SHARE ROW EXCLUSIVE MODE"; +#ifdef HAVE_BATCH_FILE_INSERT +const char *my_pg_batch_lock_path_query = + "BEGIN; LOCK TABLE Path IN SHARE ROW EXCLUSIVE MODE"; -char *my_pg_batch_lock_filename_query = "BEGIN; LOCK TABLE Filename IN SHARE ROW EXCLUSIVE MODE"; +const char *my_pg_batch_lock_filename_query = + "BEGIN; LOCK TABLE Filename IN SHARE ROW EXCLUSIVE MODE"; -char *my_pg_batch_unlock_tables_query = "COMMIT"; +const char *my_pg_batch_unlock_tables_query = "COMMIT"; -char *my_pg_batch_fill_path_query = "INSERT INTO Path (Path) " - " SELECT a.Path FROM " - " (SELECT DISTINCT Path FROM batch) AS a " - " WHERE NOT EXISTS (SELECT Path FROM Path WHERE Path = a.Path) "; +const char *my_pg_batch_fill_path_query = + "INSERT INTO Path (Path) " + "SELECT a.Path FROM " + "(SELECT DISTINCT Path FROM batch) AS a " + "WHERE NOT EXISTS (SELECT Path FROM Path WHERE Path = a.Path) "; -char *my_pg_batch_fill_filename_query = "INSERT INTO Filename (Name) " - " SELECT a.Name FROM " - " (SELECT DISTINCT Name FROM batch) as a " - " WHERE NOT EXISTS " - " (SELECT Name FROM Filename WHERE Name = a.Name)"; +const char *my_pg_batch_fill_filename_query = + "INSERT INTO Filename (Name) " + "SELECT a.Name FROM " + "(SELECT DISTINCT Name FROM batch) as a " + "WHERE NOT EXISTS " + "(SELECT Name FROM Filename WHERE Name = a.Name)"; +#endif /* HAVE_BATCH_FILE_INSERT */ + #endif /* HAVE_POSTGRESQL */