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 e597d46609341b3c4a35fa7230552d8b2e49f926..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
 
 /* -----------------------------------------------------------------------
  *
@@ -285,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 */
@@ -626,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);
@@ -1090,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 ("
@@ -1111,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"
 };
 
 /* 
@@ -1236,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  "
@@ -1255,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 */