From 5070f2db85165c72ff72837785a04b6fd131d7c7 Mon Sep 17 00:00:00 2001 From: Kern Sibbald Date: Wed, 3 Oct 2007 05:59:42 +0000 Subject: [PATCH] Backport 2.3 code that fixes: 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. git-svn-id: https://bacula.svn.sourceforge.net/svnroot/bacula/branches/Branch-2.2@5710 91ce42f0-d328-0410-95d8-f526ca767f89 --- bacula/patches/2.2.4-sql.patch | 476 +++++++++++++++++++++++++++++++++ bacula/src/cats/cats.h | 36 +-- bacula/src/cats/mysql.c | 53 ++-- bacula/src/cats/postgresql.c | 45 ++-- bacula/src/cats/sql.c | 1 - bacula/src/cats/sql_create.c | 58 ++-- bacula/src/cats/sqlite.c | 74 ++--- bacula/technotes-2.1 | 5 + 8 files changed, 624 insertions(+), 124 deletions(-) create mode 100644 bacula/patches/2.2.4-sql.patch diff --git a/bacula/patches/2.2.4-sql.patch b/bacula/patches/2.2.4-sql.patch new file mode 100644 index 0000000000..24de09994c --- /dev/null +++ b/bacula/patches/2.2.4-sql.patch @@ -0,0 +1,476 @@ + + 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 */ diff --git a/bacula/src/cats/cats.h b/bacula/src/cats/cats.h index 1971b8681a..f774612a23 100644 --- a/bacula/src/cats/cats.h +++ b/bacula/src/cats/cats.h @@ -187,10 +187,10 @@ SQL_ROW my_sqlite_fetch_row(B_DB *mdb); 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 @@ SQL_ROW my_sqlite_fetch_row(B_DB *mdb); 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 @@ struct B_DB { #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 @@ typedef struct ATTR_DBR ATTR_DBR; 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) diff --git a/bacula/src/cats/mysql.c b/bacula/src/cats/mysql.c index 6242fc3753..cb5b76783a 100644 --- a/bacula/src/cats/mysql.c +++ b/bacula/src/cats/mysql.c @@ -149,8 +149,9 @@ db_open_database(JCR *jcr, B_DB *mdb) } 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 @@ void my_mysql_free_result(B_DB *mdb) db_unlock(mdb); } -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 "; - -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) "; - -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) "; +#ifdef HAVE_BATCH_FILE_INSERT +const char *my_mysql_batch_lock_path_query = + "LOCK TABLES Path write, batch write, Path as p write"; + + +const char *my_mysql_batch_lock_filename_query = + "LOCK TABLES Filename write, batch write, Filename as f write"; + +const char *my_mysql_batch_unlock_tables_query = "UNLOCK TABLES"; + +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)"; + +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 */ diff --git a/bacula/src/cats/postgresql.c b/bacula/src/cats/postgresql.c index ff73a9f9c5..6f901403e7 100644 --- a/bacula/src/cats/postgresql.c +++ b/bacula/src/cats/postgresql.c @@ -605,13 +605,13 @@ int my_postgresql_batch_start(JCR *jcr, B_DB *mdb) 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 @@ char *my_postgresql_copy_escape(char *dest, char *src, size_t len) 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"; + +const char *my_pg_batch_lock_filename_query = + "BEGIN; LOCK TABLE Filename 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_unlock_tables_query = "COMMIT"; -char *my_pg_batch_unlock_tables_query = "COMMIT"; +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_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_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 */ -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_POSTGRESQL */ diff --git a/bacula/src/cats/sql.c b/bacula/src/cats/sql.c index 10bfaf3085..8a61c449d9 100644 --- a/bacula/src/cats/sql.c +++ b/bacula/src/cats/sql.c @@ -115,7 +115,6 @@ bool check_tables_version(JCR *jcr, B_DB *mdb) 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; } diff --git a/bacula/src/cats/sql_create.c b/bacula/src/cats/sql_create.c index 95d4afdf85..be89406b81 100644 --- a/bacula/src/cats/sql_create.c +++ b/bacula/src/cats/sql_create.c @@ -668,6 +668,8 @@ bool db_create_fileset_record(JCR *jcr, B_DB *mdb, FILESET_DBR *fsr) * }; */ +#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 @@ bool my_batch_start(JCR *jcr, B_DB *mdb) 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 @@ bool my_batch_end(JCR *jcr, B_DB *mdb, const char *error) return true; } -#ifdef HAVE_BATCH_FILE_INSERT /* * Returns 1 if OK * 0 if failed @@ -794,7 +795,7 @@ bool db_write_batch_file_records(JCR *jcr) 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 @@ bool db_write_batch_file_records(JCR *jcr) } 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 @@ bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar) 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 @@ bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar) */ 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); diff --git a/bacula/src/cats/sqlite.c b/bacula/src/cats/sqlite.c index 5f129f4f75..66cc74c47b 100644 --- a/bacula/src/cats/sqlite.c +++ b/bacula/src/cats/sqlite.c @@ -148,6 +148,7 @@ db_open_database(JCR *jcr, B_DB *mdb) int len; struct stat statbuf; int errstat; + int retry = 0; P(mutex); if (mdb->connected) { @@ -157,8 +158,9 @@ db_open_database(JCR *jcr, B_DB *mdb) 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 @@ db_open_database(JCR *jcr, B_DB *mdb) 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 @@ db_open_database(JCR *jcr, B_DB *mdb) } 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 @@ db_open_database(JCR *jcr, B_DB *mdb) 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 @@ SQL_FIELD *my_sqlite_fetch_field(B_DB *mdb) 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 */ diff --git a/bacula/technotes-2.1 b/bacula/technotes-2.1 index f2c4e9b78d..85794f8407 100644 --- a/bacula/technotes-2.1 +++ b/bacula/technotes-2.1 @@ -2,6 +2,11 @@ General: 28Sep07 +kes Backport 2.3 code that fixes: 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. kes Fix race condition that drops final block written to volume. This happens in rare cases with multiple simultaneous jobs when the Volume fills. This fixes bug #964. May be related -- 2.39.5