2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2010 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from
7 many others, a complete list can be found in the file AUTHORS.
8 This program is Free Software; you can redistribute it and/or
9 modify it under the terms of version three of the GNU Affero General Public
10 License as published by the Free Software Foundation and included
13 This program is distributed in the hope that it will be useful, but
14 WITHOUT ANY WARRANTY; without even the implied warranty of
15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 General Public License for more details.
18 You should have received a copy of the GNU Affero General Public License
19 along with this program; if not, write to the Free Software
20 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
23 Bacula® is a registered trademark of Kern Sibbald.
24 The licensor of Bacula is the Free Software Foundation Europe
25 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
26 Switzerland, email:ftf@fsfeurope.org.
29 * Bacula Catalog Database routines specific to MySQL
30 * These are MySQL specific routines -- hopefully all
31 * other files are generic.
33 * Kern Sibbald, March 2000
38 /* The following is necessary so that we do not include
39 * the dummy external definition of DB.
41 #define __SQL_C /* indicate that this is sql.c */
48 /* -----------------------------------------------------------------------
50 * MySQL dependent defines and subroutines
52 * -----------------------------------------------------------------------
55 /* List of open databases */
56 static dlist *db_list = NULL;
58 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
61 * Retrieve database type
70 * Initialize database data structure. In principal this should
71 * never have errors, or it is really fatal.
74 db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char *db_password,
75 const char *db_address, int db_port, const char *db_socket,
76 int mult_db_connections)
81 Jmsg(jcr, M_FATAL, 0, _("A user name for MySQL must be supplied.\n"));
84 P(mutex); /* lock DB queue */
85 if (db_list == NULL) {
86 db_list = New(dlist(mdb, &mdb->link));
88 /* Look to see if DB already open */
89 if (!mult_db_connections) {
90 foreach_dlist(mdb, db_list) {
91 if (bstrcmp(mdb->db_name, db_name) &&
92 bstrcmp(mdb->db_address, db_address) &&
93 mdb->db_port == db_port) {
94 Dmsg2(100, "DB REopen %d %s\n", mdb->ref_count, db_name);
97 Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", mdb->ref_count,
98 mdb->connected, mdb->db);
99 return mdb; /* already open */
103 Dmsg0(100, "db_open first time\n");
104 mdb = (B_DB *)malloc(sizeof(B_DB));
105 memset(mdb, 0, sizeof(B_DB));
106 mdb->db_name = bstrdup(db_name);
107 mdb->db_user = bstrdup(db_user);
109 mdb->db_password = bstrdup(db_password);
112 mdb->db_address = bstrdup(db_address);
115 mdb->db_socket = bstrdup(db_socket);
117 mdb->db_port = db_port;
118 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
120 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
121 mdb->cached_path = get_pool_memory(PM_FNAME);
122 mdb->cached_path_id = 0;
124 mdb->fname = get_pool_memory(PM_FNAME);
125 mdb->path = get_pool_memory(PM_FNAME);
126 mdb->esc_name = get_pool_memory(PM_FNAME);
127 mdb->esc_path = get_pool_memory(PM_FNAME);
128 mdb->esc_obj = get_pool_memory(PM_FNAME);
129 mdb->allow_transactions = mult_db_connections;
130 db_list->append(mdb); /* put db in list */
131 Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", mdb->ref_count,
132 mdb->connected, mdb->db);
138 * Now actually open the database. This can generate errors,
139 * which are returned in the errmsg
141 * DO NOT close the database or free(mdb) here !!!!
144 db_open_database(JCR *jcr, B_DB *mdb)
149 if (mdb->connected) {
154 if ((errstat=rwl_init(&mdb->lock)) != 0) {
156 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
157 be.bstrerror(errstat));
162 /* connect to the database */
163 #ifdef xHAVE_EMBEDDED_MYSQL
164 // mysql_server_init(0, NULL, NULL);
166 mysql_init(&mdb->mysql);
168 Dmsg0(50, "mysql_init done\n");
169 /* If connection fails, try at 5 sec intervals for 30 seconds. */
170 for (int retry=0; retry < 6; retry++) {
171 mdb->db = mysql_real_connect(
172 &(mdb->mysql), /* db */
173 mdb->db_address, /* default = localhost */
174 mdb->db_user, /* login name */
175 mdb->db_password, /* password */
176 mdb->db_name, /* database name */
177 mdb->db_port, /* default port */
178 mdb->db_socket, /* default = socket */
179 CLIENT_FOUND_ROWS); /* flags */
181 /* If no connect, try once more in case it is a timing problem */
182 if (mdb->db != NULL) {
188 mdb->mysql.reconnect = 1; /* so connection does not timeout */
189 Dmsg0(50, "mysql_real_connect done\n");
190 Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n", mdb->db_user, mdb->db_name,
191 mdb->db_password==NULL?"(NULL)":mdb->db_password);
193 if (mdb->db == NULL) {
194 Mmsg2(&mdb->errmsg, _("Unable to connect to MySQL server.\n"
195 "Database=%s User=%s\n"
196 "MySQL connect failed either server not running or your authorization is incorrect.\n"),
197 mdb->db_name, mdb->db_user);
198 #if MYSQL_VERSION_ID >= 40101
199 Dmsg3(50, "Error %u (%s): %s\n",
200 mysql_errno(&(mdb->mysql)), mysql_sqlstate(&(mdb->mysql)),
201 mysql_error(&(mdb->mysql)));
203 Dmsg2(50, "Error %u: %s\n",
204 mysql_errno(&(mdb->mysql)), mysql_error(&(mdb->mysql)));
210 mdb->connected = true;
211 if (!check_tables_version(jcr, mdb)) {
216 Dmsg3(100, "opendb ref=%d connected=%d db=%p\n", mdb->ref_count,
217 mdb->connected, mdb->db);
219 /* Set connection timeout to 8 days specialy for batch mode */
220 sql_query(mdb, "SET wait_timeout=691200");
221 sql_query(mdb, "SET interactive_timeout=691200");
228 db_close_database(JCR *jcr, B_DB *mdb)
233 db_end_transaction(jcr, mdb);
235 sql_free_result(mdb);
237 Dmsg3(100, "closedb ref=%d connected=%d db=%p\n", mdb->ref_count,
238 mdb->connected, mdb->db);
239 if (mdb->ref_count == 0) {
240 db_list->remove(mdb);
241 if (mdb->connected) {
242 Dmsg1(100, "close db=%p\n", mdb->db);
243 mysql_close(&mdb->mysql);
245 #ifdef xHAVE_EMBEDDED_MYSQL
246 // mysql_server_end();
249 rwl_destroy(&mdb->lock);
250 free_pool_memory(mdb->errmsg);
251 free_pool_memory(mdb->cmd);
252 free_pool_memory(mdb->cached_path);
253 free_pool_memory(mdb->fname);
254 free_pool_memory(mdb->path);
255 free_pool_memory(mdb->esc_name);
256 free_pool_memory(mdb->esc_path);
257 free_pool_memory(mdb->esc_obj);
264 if (mdb->db_password) {
265 free(mdb->db_password);
267 if (mdb->db_address) {
268 free(mdb->db_address);
270 if (mdb->db_socket) {
271 free(mdb->db_socket);
274 if (db_list->size() == 0) {
282 void db_check_backend_thread_safe()
284 #ifdef HAVE_BATCH_FILE_INSERT
285 if (!mysql_thread_safe()) {
286 Emsg0(M_ABORT, 0, _("MySQL client library must be thread-safe "
287 "when using BatchMode.\n"));
293 * This call is needed because the message channel thread
294 * opens a database on behalf of a jcr that was created in
295 * a different thread. MySQL then allocates thread specific
296 * data, which is NOT freed when the original jcr thread
297 * closes the database. Thus the msgchan must call here
298 * to cleanup any thread specific data that it created.
300 void db_thread_cleanup()
308 * Return the next unique index (auto-increment) for
309 * the given table. Return NULL on error.
311 * For MySQL, NULL causes the auto-increment value
314 int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index)
316 strcpy(index, "NULL");
321 * Escape binary object so that MySQL is happy
322 * Memory is stored in B_DB struct, no need to free it
325 db_escape_object(JCR *jcr, B_DB *mdb, char *old, int len)
327 mdb->esc_obj = check_pool_memory_size(mdb->esc_obj, len*2+1);
328 mysql_real_escape_string(mdb->db, mdb->esc_obj, old, len);
333 * Unescape binary object so that MySQL is happy
336 db_unescape_object(JCR *jcr, B_DB *db,
337 char *from, int32_t expected_len,
338 POOLMEM **dest, int32_t *dest_len)
345 *dest = check_pool_memory_size(*dest, expected_len+1);
346 *dest_len = expected_len;
347 memcpy(*dest, from, expected_len);
348 (*dest)[expected_len]=0;
352 * Escape strings so that MySQL is happy
354 * NOTE! len is the length of the old string. Your new
355 * string must be long enough (max 2*old+1) to hold
356 * the escaped output.
359 db_escape_string(JCR *jcr, B_DB *mdb, char *snew, char *old, int len)
361 mysql_real_escape_string(mdb->db, snew, old, len);
365 * Submit a general SQL command (cmd), and for each row returned,
366 * the sqlite_handler is called with the ctx.
368 bool db_sql_query(B_DB *mdb, const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
374 if (sql_query(mdb, query) != 0) {
375 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb));
379 if (result_handler != NULL) {
380 if ((mdb->result = sql_use_result(mdb)) != NULL) {
381 int num_fields = sql_num_fields(mdb);
383 /* We *must* fetch all rows */
384 while ((row = sql_fetch_row(mdb)) != NULL) {
386 /* the result handler returns 1 when it has
387 * seen all the data it wants. However, we
388 * loop to the end of the data.
390 if (result_handler(ctx, num_fields, row)) {
396 sql_free_result(mdb);
404 void my_mysql_free_result(B_DB *mdb)
408 mysql_free_result(mdb->result);
414 int my_mysql_insert_autokey_record(B_DB *mdb, const char *query, const char *table_name)
417 * First execute the insert query and then retrieve the currval.
419 if (mysql_query(mdb->db, query)) {
423 mdb->num_rows = sql_affected_rows(mdb);
424 if (mdb->num_rows != 1) {
430 return mysql_insert_id(mdb->db);
434 #ifdef HAVE_BATCH_FILE_INSERT
435 const char *my_mysql_batch_lock_path_query =
436 "LOCK TABLES Path write, batch write, Path as p write";
439 const char *my_mysql_batch_lock_filename_query =
440 "LOCK TABLES Filename write, batch write, Filename as f write";
442 const char *my_mysql_batch_unlock_tables_query = "UNLOCK TABLES";
444 const char *my_mysql_batch_fill_path_query =
445 "INSERT INTO Path (Path) "
446 "SELECT a.Path FROM "
447 "(SELECT DISTINCT Path FROM batch) AS a WHERE NOT EXISTS "
448 "(SELECT Path FROM Path AS p WHERE p.Path = a.Path)";
450 const char *my_mysql_batch_fill_filename_query =
451 "INSERT INTO Filename (Name) "
452 "SELECT a.Name FROM "
453 "(SELECT DISTINCT Name FROM batch) AS a WHERE NOT EXISTS "
454 "(SELECT Name FROM Filename AS f WHERE f.Name = a.Name)";
455 #endif /* HAVE_BATCH_FILE_INSERT */
457 #endif /* HAVE_MYSQL */