X-Git-Url: https://git.sur5r.net/?a=blobdiff_plain;f=bacula%2Fsrc%2Fcats%2Fmysql.c;h=a7dfe465a5fe35575af74ef9be146fcc15ea8f10;hb=cd7acc68ac7b79a612e3c3ce64fa191e303e2c72;hp=2a860263400016201376272457a53d85528f5c2c;hpb=e5f5d52c7ca02da4a5264a6fa2967d4a7958143f;p=bacula%2Fbacula diff --git a/bacula/src/cats/mysql.c b/bacula/src/cats/mysql.c index 2a86026340..a7dfe465a5 100644 --- a/bacula/src/cats/mysql.c +++ b/bacula/src/cats/mysql.c @@ -1,38 +1,44 @@ /* - * Bacula Catalog Database routines specific to MySQL - * These are MySQL specific routines -- hopefully all - * other files are generic. - * - * Kern Sibbald, March 2000 - * - * Version $Id$ - */ + Bacula® - The Network Backup Solution -/* - Copyright (C) 2000-2003 Kern Sibbald and John Walker + Copyright (C) 2000-2010 Free Software Foundation Europe e.V. - This program is free software; you can redistribute it and/or - modify it under the terms of the GNU General Public License as - published by the Free Software Foundation; either version 2 of - the License, or (at your option) any later version. + 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 three of the GNU Affero General Public + License as published by the Free Software Foundation and included + in the file LICENSE. - This program is distributed in the hope that it will be useful, - but WITHOUT ANY WARRANTY; without even the implied warranty of + This program is distributed in the hope that it will be useful, but + WITHOUT ANY WARRANTY; without even the implied warranty of 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 along with this program; if not, write to the Free - Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, - MA 02111-1307, USA. + 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. + Bacula® is a registered trademark of Kern Sibbald. + The licensor of Bacula is the Free Software Foundation Europe + (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich, + Switzerland, email:ftf@fsfeurope.org. +*/ +/* + * Bacula Catalog Database routines specific to MySQL + * These are MySQL specific routines -- hopefully all + * other files are generic. + * + * Kern Sibbald, March 2000 + * */ /* The following is necessary so that we do not include * the dummy external definition of DB. */ -#define __SQL_C /* indicate that this is sql.c */ +#define __SQL_C /* indicate that this is sql.c */ #include "bacula.h" #include "cats.h" @@ -47,36 +53,61 @@ */ /* List of open databases */ -static BQUEUE db_list = {&db_list, &db_list}; +static dlist *db_list = NULL; static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER; +/* + * Retrieve database type + */ +const char * +db_get_type(void) +{ + return "MySQL"; +} + /* * Initialize database data structure. In principal this should * never have errors, or it is really fatal. */ B_DB * -db_init_database(JCR *jcr, char *db_name, char *db_user, char *db_password, - char *db_address, int db_port, char *db_socket) +db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char *db_password, + const char *db_address, int db_port, const char *db_socket, + int mult_db_connections) { - B_DB *mdb; + B_DB *mdb = NULL; - P(mutex); /* lock DB queue */ + if (!db_user) { + Jmsg(jcr, M_FATAL, 0, _("A user name for MySQL must be supplied.\n")); + return NULL; + } + P(mutex); /* lock DB queue */ + if (db_list == NULL) { + db_list = New(dlist(mdb, &mdb->link)); + } /* Look to see if DB already open */ - for (mdb=NULL; (mdb=(B_DB *)qnext(&db_list, &mdb->bq)); ) { - if (strcmp(mdb->db_name, db_name) == 0) { - Dmsg2(100, "DB REopen %d %s\n", mdb->ref_count, db_name); - mdb->ref_count++; - V(mutex); - return mdb; /* already open */ + if (!mult_db_connections) { + foreach_dlist(mdb, db_list) { + if (bstrcmp(mdb->db_name, db_name) && + bstrcmp(mdb->db_address, db_address) && + mdb->db_port == db_port) { + Dmsg2(100, "DB REopen %d %s\n", mdb->ref_count, db_name); + mdb->ref_count++; + V(mutex); + Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", mdb->ref_count, + mdb->connected, mdb->db); + return mdb; /* already open */ + } } } Dmsg0(100, "db_open first time\n"); - mdb = (B_DB *) malloc(sizeof(B_DB)); + mdb = (B_DB *)malloc(sizeof(B_DB)); memset(mdb, 0, sizeof(B_DB)); mdb->db_name = bstrdup(db_name); mdb->db_user = bstrdup(db_user); - mdb->db_password = bstrdup(db_password); + if (db_password) { + mdb->db_password = bstrdup(db_password); + } if (db_address) { mdb->db_address = bstrdup(db_address); } @@ -84,7 +115,6 @@ db_init_database(JCR *jcr, char *db_name, char *db_user, char *db_password, mdb->db_socket = bstrdup(db_socket); } mdb->db_port = db_port; - mdb->have_insert_id = TRUE; mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */ *mdb->errmsg = 0; mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */ @@ -94,14 +124,21 @@ db_init_database(JCR *jcr, char *db_name, char *db_user, char *db_password, mdb->fname = get_pool_memory(PM_FNAME); mdb->path = get_pool_memory(PM_FNAME); mdb->esc_name = get_pool_memory(PM_FNAME); - qinsert(&db_list, &mdb->bq); /* put db in list */ + mdb->esc_path = get_pool_memory(PM_FNAME); + mdb->esc_obj = get_pool_memory(PM_FNAME); + mdb->allow_transactions = mult_db_connections; + db_list->append(mdb); /* put db in list */ + Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", mdb->ref_count, + mdb->connected, mdb->db); V(mutex); return mdb; } /* * Now actually open the database. This can generate errors, - * which are returned in the errmsg + * which are returned in the errmsg + * + * DO NOT close the database or free(mdb) here !!!! */ int db_open_database(JCR *jcr, B_DB *mdb) @@ -113,66 +150,76 @@ db_open_database(JCR *jcr, B_DB *mdb) V(mutex); return 1; } - mdb->connected = FALSE; if ((errstat=rwl_init(&mdb->lock)) != 0) { - Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"), - strerror(errstat)); + berrno be; + Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"), + be.bstrerror(errstat)); V(mutex); return 0; } /* connect to the database */ -#ifdef HAVE_EMBEDDED_MYSQL - mysql_server_init(0, NULL, NULL); +#ifdef xHAVE_EMBEDDED_MYSQL +// mysql_server_init(0, NULL, NULL); #endif - mysql_init(&(mdb->mysql)); + mysql_init(&mdb->mysql); + Dmsg0(50, "mysql_init done\n"); - mdb->db = mysql_real_connect( - &(mdb->mysql), /* db */ - mdb->db_address, /* default = localhost */ - mdb->db_user, /* login name */ - mdb->db_password, /* password */ - mdb->db_name, /* database name */ - mdb->db_port, /* default port */ - mdb->db_socket, /* default = socket */ - CLIENT_FOUND_ROWS); /* flags */ - - /* If no connect, try once more incase it is a timing problem */ - if (mdb->db == NULL) { + /* If connection fails, try at 5 sec intervals for 30 seconds. */ + for (int retry=0; retry < 6; retry++) { mdb->db = mysql_real_connect( - &(mdb->mysql), /* db */ - mdb->db_address, /* default = localhost */ - mdb->db_user, /* login name */ - mdb->db_password, /* password */ - mdb->db_name, /* database name */ - mdb->db_port, /* default port */ - mdb->db_socket, /* default = socket */ - 0); /* flags = none */ + &(mdb->mysql), /* db */ + mdb->db_address, /* default = localhost */ + mdb->db_user, /* login name */ + mdb->db_password, /* password */ + mdb->db_name, /* database name */ + mdb->db_port, /* default port */ + mdb->db_socket, /* default = socket */ + CLIENT_FOUND_ROWS); /* flags */ + + /* If no connect, try once more in case it is a timing problem */ + if (mdb->db != NULL) { + break; + } + bmicrosleep(5,0); } - + + mdb->mysql.reconnect = 1; /* so connection does not timeout */ Dmsg0(50, "mysql_real_connect done\n"); - Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n", mdb->db_user, mdb->db_name, + Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n", mdb->db_user, mdb->db_name, mdb->db_password==NULL?"(NULL)":mdb->db_password); - + if (mdb->db == NULL) { - Mmsg2(&mdb->errmsg, _("Unable to connect to MySQL server. \n\ -Database=%s User=%s\n\ -It is probably not running or your password is incorrect.\n"), - mdb->db_name, mdb->db_user); + Mmsg2(&mdb->errmsg, _("Unable to connect to MySQL server.\n" +"Database=%s User=%s\n" +"MySQL connect failed either server not running or your authorization is incorrect.\n"), + mdb->db_name, mdb->db_user); +#if MYSQL_VERSION_ID >= 40101 + Dmsg3(50, "Error %u (%s): %s\n", + mysql_errno(&(mdb->mysql)), mysql_sqlstate(&(mdb->mysql)), + mysql_error(&(mdb->mysql))); +#else + Dmsg2(50, "Error %u: %s\n", + mysql_errno(&(mdb->mysql)), mysql_error(&(mdb->mysql))); +#endif V(mutex); return 0; } + mdb->connected = true; if (!check_tables_version(jcr, mdb)) { V(mutex); - db_close_database(jcr, mdb); return 0; } - my_thread_init(); + Dmsg3(100, "opendb ref=%d connected=%d db=%p\n", mdb->ref_count, + mdb->connected, mdb->db); + + /* Set connection timeout to 8 days specialy for batch mode */ + sql_query(mdb, "SET wait_timeout=691200"); + sql_query(mdb, "SET interactive_timeout=691200"); - mdb->connected = TRUE; V(mutex); return 1; } @@ -180,48 +227,87 @@ It is probably not running or your password is incorrect.\n"), void db_close_database(JCR *jcr, B_DB *mdb) { + if (!mdb) { + return; + } + db_end_transaction(jcr, mdb); P(mutex); + sql_free_result(mdb); mdb->ref_count--; - my_thread_end(); + Dmsg3(100, "closedb ref=%d connected=%d db=%p\n", mdb->ref_count, + mdb->connected, mdb->db); if (mdb->ref_count == 0) { - qdchain(&mdb->bq); - if (mdb->connected && mdb->db) { - sql_close(mdb); -#ifdef HAVE_EMBEDDED_MYSQL - mysql_server_end(); + db_list->remove(mdb); + if (mdb->connected) { + Dmsg1(100, "close db=%p\n", mdb->db); + mysql_close(&mdb->mysql); + +#ifdef xHAVE_EMBEDDED_MYSQL +// mysql_server_end(); #endif } - rwl_destroy(&mdb->lock); + rwl_destroy(&mdb->lock); free_pool_memory(mdb->errmsg); free_pool_memory(mdb->cmd); free_pool_memory(mdb->cached_path); free_pool_memory(mdb->fname); free_pool_memory(mdb->path); free_pool_memory(mdb->esc_name); + free_pool_memory(mdb->esc_path); + free_pool_memory(mdb->esc_obj); if (mdb->db_name) { - free(mdb->db_name); + free(mdb->db_name); } if (mdb->db_user) { - free(mdb->db_user); + free(mdb->db_user); } if (mdb->db_password) { - free(mdb->db_password); + free(mdb->db_password); } if (mdb->db_address) { - free(mdb->db_address); + free(mdb->db_address); } if (mdb->db_socket) { - free(mdb->db_socket); + free(mdb->db_socket); } free(mdb); + if (db_list->size() == 0) { + delete db_list; + db_list = NULL; + } } V(mutex); } +void db_check_backend_thread_safe() +{ +#ifdef HAVE_BATCH_FILE_INSERT + if (!mysql_thread_safe()) { + Emsg0(M_ABORT, 0, _("MySQL client library must be thread-safe " + "when using BatchMode.\n")); + } +#endif +} + +/* + * This call is needed because the message channel thread + * opens a database on behalf of a jcr that was created in + * a different thread. MySQL then allocates thread specific + * data, which is NOT freed when the original jcr thread + * closes the database. Thus the msgchan must call here + * to cleanup any thread specific data that it created. + */ +void db_thread_cleanup() +{ +#ifndef HAVE_WIN32 + my_thread_end(); +#endif +} + /* * Return the next unique index (auto-increment) for * the given table. Return NULL on error. - * + * * For MySQL, NULL causes the auto-increment value * to be updated. */ @@ -229,218 +315,143 @@ int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index) { strcpy(index, "NULL"); return 1; -} +} + +/* + * Escape binary object so that MySQL is happy + * Memory is stored in B_DB struct, no need to free it + */ +char * +db_escape_object(JCR *jcr, B_DB *mdb, char *old, int len) +{ + mdb->esc_obj = check_pool_memory_size(mdb->esc_obj, len*2+1); + mysql_real_escape_string(mdb->db, mdb->esc_obj, old, len); + return mdb->esc_obj; +} +/* + * Unescape binary object so that MySQL is happy + */ +void +db_unescape_object(JCR *jcr, B_DB *db, + char *from, int32_t expected_len, + POOLMEM **dest, int32_t *dest_len) +{ + if (!from) { + *dest[0] = 0; + *dest_len = 0; + return; + } + *dest = check_pool_memory_size(*dest, expected_len+1); + *dest_len = expected_len; + memcpy(*dest, from, expected_len); + (*dest)[expected_len]=0; +} /* * Escape strings so that MySQL is happy * * NOTE! len is the length of the old string. Your new - * string must be long enough (max 2*old) to hold - * the escaped output. + * string must be long enough (max 2*old+1) to hold + * the escaped output. */ void -db_escape_string(char *snew, char *old, int len) +db_escape_string(JCR *jcr, B_DB *mdb, char *snew, char *old, int len) { - mysql_escape_string(snew, old, len); - -#ifdef DO_IT_MYSELF - -/* Should use mysql_real_escape_string ! */ -unsigned long mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned long length); - - char *n, *o; - - n = snew; - o = old; - while (len--) { - switch (*o) { - case 0: - *n++= '\\'; - *n++= '0'; - o++; - break; - case '\n': - *n++= '\\'; - *n++= 'n'; - o++; - break; - case '\r': - *n++= '\\'; - *n++= 'r'; - o++; - break; - case '\\': - *n++= '\\'; - *n++= '\\'; - o++; - break; - case '\'': - *n++= '\\'; - *n++= '\''; - o++; - break; - case '"': - *n++= '\\'; - *n++= '"'; - o++; - break; - case '\032': - *n++= '\\'; - *n++= 'Z'; - o++; - break; - default: - *n++= *o++; - } - } - *n = 0; -#endif + mysql_real_escape_string(mdb->db, snew, old, len); } /* * Submit a general SQL command (cmd), and for each row returned, * the sqlite_handler is called with the ctx. */ -int db_sql_query(B_DB *mdb, char *query, DB_RESULT_HANDLER *result_handler, void *ctx) +bool db_sql_query(B_DB *mdb, const char *query, DB_RESULT_HANDLER *result_handler, void *ctx) { SQL_ROW row; - + bool send = true; + db_lock(mdb); if (sql_query(mdb, query) != 0) { - Mmsg(&mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb)); + Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb)); db_unlock(mdb); - return 0; + return false; } if (result_handler != NULL) { - if ((mdb->result = sql_store_result(mdb)) != NULL) { - int num_fields = sql_num_fields(mdb); - - while ((row = sql_fetch_row(mdb)) != NULL) { - if (result_handler(ctx, num_fields, row)) - break; - } - - sql_free_result(mdb); + if ((mdb->result = sql_use_result(mdb)) != NULL) { + int num_fields = sql_num_fields(mdb); + + /* We *must* fetch all rows */ + while ((row = sql_fetch_row(mdb)) != NULL) { + if (send) { + /* the result handler returns 1 when it has + * seen all the data it wants. However, we + * loop to the end of the data. + */ + if (result_handler(ctx, num_fields, row)) { + send = false; + } + } + } + + sql_free_result(mdb); } } db_unlock(mdb); - return 1; + return true; } - -static void -list_dashes(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx) +void my_mysql_free_result(B_DB *mdb) { - SQL_FIELD *field; - unsigned int i, j; - - sql_field_seek(mdb, 0); - send(ctx, "+"); - for (i = 0; i < sql_num_fields(mdb); i++) { - field = sql_fetch_field(mdb); - for (j = 0; j < field->max_length + 2; j++) - send(ctx, "-"); - send(ctx, "+"); + db_lock(mdb); + if (mdb->result) { + mysql_free_result(mdb->result); + mdb->result = NULL; } - send(ctx, "\n"); + db_unlock(mdb); } -/* - * If full_list is set, we list vertically, otherwise, we - * list on one line horizontally. - */ -void -list_result(B_DB *mdb, DB_LIST_HANDLER *send, void *ctx, int full_list) +uint64_t my_mysql_insert_autokey_record(B_DB *mdb, const char *query, const char *table_name) { - SQL_FIELD *field; - SQL_ROW row; - unsigned int i, col_len, max_len = 0; - char buf[2000], ewc[30]; - - if (mdb->result == NULL) { - send(ctx, _("No results to list.\n")); - return; - } - /* determine column display widths */ - sql_field_seek(mdb, 0); - for (i = 0; i < sql_num_fields(mdb); i++) { - field = sql_fetch_field(mdb); - col_len = strlen(field->name); - if (full_list) { - if (col_len > max_len) { - max_len = col_len; - } - } else { - if (IS_NUM(field->type) && field->max_length > 0) { /* fixup for commas */ - field->max_length += (field->max_length - 1) / 3; - } - if (col_len < field->max_length) { - col_len = field->max_length; - } - if (col_len < 4 && !IS_NOT_NULL(field->flags)) { - col_len = 4; /* 4 = length of the word "NULL" */ - } - field->max_length = col_len; /* reset column info */ - } + /* + * First execute the insert query and then retrieve the currval. + */ + if (mysql_query(mdb->db, query)) { + return 0; } - if (full_list) { - goto horizontal_list; + mdb->num_rows = sql_affected_rows(mdb); + if (mdb->num_rows != 1) { + return 0; } - list_dashes(mdb, send, ctx); - send(ctx, "|"); - sql_field_seek(mdb, 0); - for (i = 0; i < sql_num_fields(mdb); i++) { - field = sql_fetch_field(mdb); - bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, field->name); - send(ctx, buf); - } - send(ctx, "\n"); - list_dashes(mdb, send, ctx); - - while ((row = sql_fetch_row(mdb)) != NULL) { - sql_field_seek(mdb, 0); - send(ctx, "|"); - for (i = 0; i < sql_num_fields(mdb); i++) { - field = sql_fetch_field(mdb); - if (row[i] == NULL) { - bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, "NULL"); - } else if (IS_NUM(field->type)) { - bsnprintf(buf, sizeof(buf), " %*s |", (int)field->max_length, - add_commas(row[i], ewc)); - } else { - bsnprintf(buf, sizeof(buf), " %-*s |", (int)field->max_length, row[i]); - } - send(ctx, buf); - } - send(ctx, "\n"); - } - list_dashes(mdb, send, ctx); - return; - -horizontal_list: - - while ((row = sql_fetch_row(mdb)) != NULL) { - sql_field_seek(mdb, 0); - for (i = 0; i < sql_num_fields(mdb); i++) { - field = sql_fetch_field(mdb); - if (row[i] == NULL) { - bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, "NULL"); - } else if (IS_NUM(field->type)) { - bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, - add_commas(row[i], ewc)); - } else { - bsnprintf(buf, sizeof(buf), " %*s: %s\n", max_len, field->name, row[i]); - } - send(ctx, buf); - } - send(ctx, "\n"); - } - return; + mdb->changes++; + + return mysql_insert_id(mdb->db); } +#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 */