git.sur5r.net Git - bacula/bacula/blob - bacula/patches/2.2.4-sql.patch

   1
   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.
   7
   8  It can be applied to version 2.2.4 (and possibly earlier 2.2.x versions)
   9  with:
  10
  11    cd <bacula-source>
  12    patch -p0 <2.2.4-sql.patch
  13    ./configure (your options)
  14    make
  15    ...
  16    make install
  17
  18
  19
  20 Index: src/cats/sql.c
  21 ===================================================================
  22 --- src/cats/sql.c      (revision 5687)
  23 +++ src/cats/sql.c      (working copy)
  24 @@ -115,7 +115,6 @@
  25
  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);
  30        return false;
  31     }
  32 Index: src/cats/sqlite.c
  33 ===================================================================
  34 --- src/cats/sqlite.c   (revision 5687)
  35 +++ src/cats/sqlite.c   (working copy)
  36 @@ -148,6 +148,7 @@
  37     int len;
  38     struct stat statbuf;
  39     int errstat;
  40 +   int retry = 0;
  41
  42     P(mutex);
  43     if (mdb->connected) {
  44 @@ -157,8 +158,9 @@
  45     mdb->connected = FALSE;
  46
  47     if ((errstat=rwl_init(&mdb->lock)) != 0) {
  48 +      berrno be;
  49        Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
  50 -            strerror(errstat));
  51 +            be.bstrerror(errstat));
  52        V(mutex);
  53        return 0;
  54     }
  55 @@ -178,28 +180,28 @@
  56        return 0;
  57     }
  58
  59 +   for (mdb->db=NULL; !mdb->db && retry++ < 10; ) {
  60  #ifdef HAVE_SQLITE3
  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);
  65 -      mdb->db = NULL;
  66 -   } else {
  67 -      mdb->sqlite_errmsg = NULL;
  68 -   }
  69 -#ifdef SQLITE3_INIT_QUERY
  70 -   db_sql_query(mdb, SQLITE3_INIT_QUERY, NULL, NULL);
  71 -#endif
  72 -
  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);
  77 +         mdb->db = NULL;
  78 +      } else {
  79 +         mdb->sqlite_errmsg = NULL;
  80 +      }
  81  #else
  82 -   mdb->db = sqlite_open(
  83 -        db_name,                      /* database name */
  84 -        644,                          /* mode */
  85 -        &mdb->sqlite_errmsg);         /* error message */
  86 +      mdb->db = sqlite_open(
  87 +           db_name,                      /* database name */
  88 +           644,                          /* mode */
  89 +           &mdb->sqlite_errmsg);         /* error message */
  90  #endif
  91
  92 -   Dmsg0(300, "sqlite_open\n");
  93 -
  94 +      Dmsg0(300, "sqlite_open\n");
  95 +      if (!mdb->db) {
  96 +         bmicrosleep(1, 0);
  97 +      }
  98 +   }
  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"));
 102 @@ -209,10 +211,6 @@
 103     }
 104     mdb->connected = true;
 105     free(db_name);
 106 -   if (!check_tables_version(jcr, mdb)) {
 107 -      V(mutex);
 108 -      return 0;
 109 -   }
 110
 111     /* set busy handler to wait when we use mult_db_connections = 1 */
 112  #ifdef HAVE_SQLITE3
 113 @@ -221,6 +219,16 @@
 114     sqlite_busy_handler(mdb->db, my_busy_handler, NULL);
 115  #endif
 116
 117 +#if  defined(HAVE_SQLITE3) && defined(SQLITE3_INIT_QUERY)
 118 +   db_sql_query(mdb, SQLITE3_INIT_QUERY, NULL, NULL);
 119 +#endif
 120 +
 121 +   if (!check_tables_version(jcr, mdb)) {
 122 +      V(mutex);
 123 +      return 0;
 124 +   }
 125 +
 126 +
 127     V(mutex);
 128     return 1;
 129  }
 130 @@ -448,16 +456,20 @@
 131     return mdb->fields[mdb->field++];
 132  }
 133
 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";
 142
 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";
 150
 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 */
 156
 157
 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;
 175
 176
 177  #else
 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;
 190
 191
 192  #else
 193 @@ -398,11 +398,11 @@
 194  #define sql_batch_fill_path_query       my_mysql_batch_fill_path_query
 195
 196
 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);
 208
 209  #else
 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);
 213
 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;
 224
 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)
 231 @@ -149,8 +149,9 @@
 232     }
 233
 234     if ((errstat=rwl_init(&mdb->lock)) != 0) {
 235 +      berrno be;
 236        Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
 237 -            strerror(errstat));
 238 +            be.bstrerror(errstat));
 239        V(mutex);
 240        return 0;
 241     }
 242 @@ -403,33 +404,27 @@
 243     db_unlock(mdb);
 244  }
 245
 246 -char *my_mysql_batch_lock_path_query = "LOCK TABLES Path write,     "
 247 -                                       "            batch 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";
 252
 253
 254 -char *my_mysql_batch_lock_filename_query = "LOCK TABLES Filename write,     "
 255 -                                           "            batch 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";
 259
 260 -char *my_mysql_batch_unlock_tables_query = "UNLOCK TABLES";
 261 +const char *my_mysql_batch_unlock_tables_query = "UNLOCK TABLES";
 262
 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              "
 268 -                                       "  (SELECT Path                 "
 269 -                                       "     FROM Path AS p            "
 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)";
 276
 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                "
 282 -                                           "   (SELECT Name                   "
 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 */
 291
 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)
 297 @@ -668,6 +668,8 @@
 298   *  };
 299   */
 300
 301 +#ifdef HAVE_BATCH_FILE_INSERT
 302 +
 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"
 305   *  in baconfig.h
 306 @@ -690,13 +692,13 @@
 307
 308     db_lock(mdb);
 309     ok =  db_sql_query(mdb,
 310 -             " CREATE TEMPORARY TABLE batch "
 311 -             "        (fileindex integer,   "
 312 -             "        jobid integer,        "
 313 -             "        path blob,            "
 314 -             "        name blob,            "
 315 -             "        lstat tinyblob,       "
 316 -             "        md5 tinyblob)         ",NULL, NULL);
 317 +             "CREATE TEMPORARY TABLE batch ("
 318 +                "FileIndex integer,"
 319 +                "JobId integer,"
 320 +                "Path blob,"
 321 +                "Name blob,"
 322 +                "LStat tinyblob,"
 323 +                "MD5 tinyblob)",NULL, NULL);
 324     db_unlock(mdb);
 325     return ok;
 326  }
 327 @@ -746,7 +748,6 @@
 328     return true;
 329  }
 330
 331 -#ifdef HAVE_BATCH_FILE_INSERT
 332  /*
 333   * Returns 1 if OK
 334   *         0 if failed
 335 @@ -794,7 +795,7 @@
 336
 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);
 341        return false;
 342     }
 343
 344 @@ -804,12 +805,12 @@
 345     }
 346
 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               "
 351 -       "  FROM batch                                                      "
 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 "
 357 +           "FROM batch "
 358 +           "JOIN Path ON (batch.Path = Path.Path) "
 359 +           "JOIN Filename ON (batch.Name = Filename.Name)",
 360                       NULL,NULL))
 361     {
 362        Jmsg(jcr, M_FATAL, 0, "Can't fill File table %s\n", jcr->db_batch->errmsg);
 363 @@ -845,19 +846,24 @@
 364                                        mdb->db_port,
 365                                        mdb->db_socket,
 366                                        1 /* multi_db = true */);
 367 +      if (!jcr->db_batch) {
 368 +         Mmsg1(&mdb->errmsg, _("Could not init batch database: \"%s\".\n"),
 369 +                        jcr->db->db_name);
 370 +         Jmsg1(jcr, M_FATAL, 0, "%s", mdb->errmsg);
 371 +         return false;
 372 +      }
 373
 374 -      if (!jcr->db_batch || !db_open_database(jcr, jcr->db_batch)) {
 375 -         Jmsg(jcr, M_FATAL, 0, _("Could not open database \"%s\".\n"),
 376 -              jcr->db->db_name);
 377 -         if (jcr->db_batch) {
 378 -            Jmsg(jcr, M_FATAL, 0, "%s", db_strerror(jcr->db_batch));
 379 -         }
 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);
 384           return false;
 385        }
 386
 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);
 393           return false;
 394        }
 395        Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", jcr->db_batch->ref_count,
 396 @@ -870,10 +876,10 @@
 397      */
 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"),
 402           ar->Stream);
 403 -      Jmsg(jcr, M_ERROR, 0, "%s", bdb->errmsg);
 404 -      return 0;
 405 +      Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
 406 +      return false;
 407     }
 408
 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");
 416
 417     if (my_postgresql_query(mdb,
 418 -                           " CREATE TEMPORARY TABLE batch "
 419 -                           "        (fileindex int,       "
 420 -                           "        jobid int,            "
 421 -                           "        path varchar,         "
 422 -                           "        name varchar,         "
 423 -                           "        lstat varchar,        "
 424 -                           "        md5 varchar)") == 1)
 425 +                           "CREATE TEMPORARY TABLE batch ("
 426 +                               "fileindex int,"
 427 +                               "jobid int,"
 428 +                               "path varchar,"
 429 +                               "name varchar,"
 430 +                               "lstat varchar,"
 431 +                               "md5 varchar)") == 1)
 432     {
 433        Dmsg0(500, "my_postgresql_batch_start failed\n");
 434        return 1;
 435 @@ -785,22 +785,29 @@
 436     return dest;
 437  }
 438
 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";
 443
 444
 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";
 448
 449 -char *my_pg_batch_unlock_tables_query = "COMMIT";
 450 +const char *my_pg_batch_unlock_tables_query = "COMMIT";
 451
 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) ";
 461
 462
 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 */
 475 +
 476  #endif /* HAVE_POSTGRESQL */