Fix bug #1504 -- Error when creating tables in MySQL 5.5

[bacula/bacula] / bacula / src / cats / sql_create.c

diff --git a/bacula/src/cats/sql_create.c b/bacula/src/cats/sql_create.c
index 834ebdf89d8b9a25c349bd5e9f8d3b8563ea4a5a..ad84e678f2fe2d718486bf35103ff4f06441705e 100644 (file)
--- a/bacula/src/cats/sql_create.c
+++ b/bacula/src/cats/sql_create.c
@@ -6,7 +6,7 @@
    The main author of Bacula is Kern Sibbald, with contributions from
    many others, a complete list can be found in the file AUTHORS.
    This program is Free Software; you can redistribute it and/or
-   modify it under the terms of version two of the GNU General Public
+   modify it under the terms of version three of the GNU Affero General Public
    License as published by the Free Software Foundation and included
    in the file LICENSE.
 
@@ -15,7 +15,7 @@
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
    General Public License for more details.
 
-   You should have received a copy of the GNU General Public License
+   You should have received a copy of the GNU Affero General Public License
    along with this program; if not, write to the Free Software
    Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
    02110-1301, USA.
@@ -30,7 +30,7 @@
  *
  *    Kern Sibbald, March 2000
  *
- *    Version $Id$
+ *    Version $Id: sql_create.c 8407 2009-01-28 10:47:21Z ricozz $
  */
 
 /* The following is necessary so that we do not include
@@ -43,7 +43,7 @@
 
 static const int dbglevel = 100;
 
-#if    HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_DBI
+#if    HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI
 
 /* -----------------------------------------------------------------------
  *
@@ -132,13 +132,12 @@ db_create_jobmedia_record(JCR *jcr, B_DB *mdb, JOBMEDIA_DBR *jm)
     */
    Mmsg(mdb->cmd,
         "INSERT INTO JobMedia (JobId,MediaId,FirstIndex,LastIndex,"
-        "StartFile,EndFile,StartBlock,EndBlock,VolIndex,Copy) "
-        "VALUES (%s,%s,%u,%u,%u,%u,%u,%u,%u,%u)",
+        "StartFile,EndFile,StartBlock,EndBlock,VolIndex) "
+        "VALUES (%s,%s,%u,%u,%u,%u,%u,%u,%u)",
         edit_int64(jm->JobId, ed1),
         edit_int64(jm->MediaId, ed2),
         jm->FirstIndex, jm->LastIndex,
-        jm->StartFile, jm->EndFile, jm->StartBlock, jm->EndBlock,count,
-        jm->Copy);
+        jm->StartFile, jm->EndFile, jm->StartBlock, jm->EndBlock,count);
 
    Dmsg0(300, mdb->cmd);
    if (!INSERT_DB(jcr, mdb, mdb->cmd)) {
@@ -286,6 +285,7 @@ bool db_create_storage_record(JCR *jcr, B_DB *mdb, STORAGE_DBR *sr)
 
    sr->StorageId = 0;
    sr->created = false;
+   /* Check if it already exists */
    if (QUERY_DB(jcr, mdb, mdb->cmd)) {
       mdb->num_rows = sql_num_rows(mdb);
       /* If more than one, report error, but return first row */
@@ -627,7 +627,7 @@ int db_create_counter_record(JCR *jcr, B_DB *mdb, COUNTER_DBR *cr)
    }
 
    /* Must create it */
-   Mmsg(mdb->cmd, "INSERT INTO Counters (Counter,MinValue,MaxValue,CurrentValue,"
+   Mmsg(mdb->cmd, "INSERT INTO Counters (Counter,\"MinValue\",\"MaxValue\",CurrentValue,"
       "WrapCounter) VALUES ('%s','%d','%d','%d','%s')",
       cr->Counter, cr->MinValue, cr->MaxValue, cr->CurrentValue,
       cr->WrapCounter);
@@ -909,6 +909,10 @@ bool db_write_batch_file_records(JCR *jcr)
  *  is a single FileName record and a single Path record, no matter
  *  how many times it occurs.  This is this subroutine, we separate
  *  the file and the path and fill temporary tables with this three records.
+ *
+ *  Note: all routines that call this expect to be able to call
+ *    db_strerror(mdb) to get the error message, so the error message
+ *    MUST be edited into mdb->errmsg before returning an error status.
  */
 bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
 {
@@ -919,14 +923,13 @@ bool db_create_file_attributes_record(JCR *jcr, B_DB *mdb, ATTR_DBR *ar)
 
    /* Open the dedicated connexion */
    if (!jcr->batch_started) {
-
       if (!db_open_batch_connexion(jcr, mdb)) {
-         return false;
+         return false;     /* error already printed */
       }
       if (!sql_batch_start(jcr, 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);
+         Jmsg(jcr, M_FATAL, 0, "%s", mdb->errmsg);
          return false;
       }
       jcr->batch_started = true;
@@ -1088,7 +1091,7 @@ bool db_write_batch_file_records(JCR *jcr)
 
 
 /* List of SQL commands to create temp table and indicies  */
-const char *create_temp_basefile[4] = {
+const char *create_temp_basefile[5] = {
    /* MySQL */
    "CREATE TEMPORARY TABLE basefile%lld ("
 //   "CREATE TABLE basefile%lld ("
@@ -1109,7 +1112,13 @@ const char *create_temp_basefile[4] = {
    /* SQLite3 */
    "CREATE TEMPORARY TABLE basefile%lld (" 
    "Path TEXT,"
-   "Name TEXT)"
+   "Name TEXT)",
+
+   /* Ingres */
+   "DECLARE GLOBAL TEMPORARY TABLE basefile%lld (" 
+   "Path TEXT NOT NULL,"
+   "Name TEXT NOT NULL)"
+   "ON COMMIT PRESERVE ROWS WITH NORECOVERY"
 };
 
 /* 
@@ -1203,7 +1212,7 @@ bool db_commit_base_file_attributes_record(JCR *jcr, B_DB *mdb)
       "AND A.Name = B.Name "
     "ORDER BY B.FileId", 
         edit_uint64(jcr->JobId, ed1), ed1, ed1);
-   ret = QUERY_DB(jcr, mdb, mdb->cmd);
+   ret = db_sql_query(mdb, mdb->cmd, NULL, NULL);
    jcr->nb_base_files_used = sql_affected_rows(mdb);
    db_cleanup_base_file(jcr, mdb);
 
@@ -1234,6 +1243,15 @@ bool db_create_base_file_list(JCR *jcr, B_DB *mdb, char *jobids)
    if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
       goto bail_out;
    }
+   /* Quick and dirty fix for MySQL and Bacula 5.0.1 */
+#ifdef HAVE_MYSQL
+   Mmsg(mdb->cmd, 
+        "CREATE INDEX basefile%lld_idx ON basefile%lld (Path(255), Name(255))", 
+        (uint64_t) jcr->JobId, (uint64_t) jcr->JobId);
+   if (!db_sql_query(mdb, mdb->cmd, NULL, NULL)) {
+      goto bail_out;
+   }
+#endif
    Mmsg(buf, select_recent_version[db_type], jobids, jobids);
    Mmsg(mdb->cmd,
 "CREATE TEMPORARY TABLE new_basefile%lld AS  "
@@ -1253,4 +1271,4 @@ bail_out:
    return ret;
 }
 
-#endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_DBI */
+#endif /* HAVE_SQLITE3 || HAVE_MYSQL || HAVE_SQLITE || HAVE_POSTGRESQL || HAVE_INGRES || HAVE_DBI */