2 Bacula(R) - The Network Backup Solution
4 Copyright (C) 2000-2015 Kern Sibbald
5 Copyright (C) 2000-2014 Free Software Foundation Europe e.V.
7 The original author of Bacula is Kern Sibbald, with contributions
8 from many others, a complete list can be found in the file AUTHORS.
10 You may use this file and others of this release according to the
11 license defined in the LICENSE file, which includes the Affero General
12 Public License, v3.0 ("AGPLv3") and some additional permissions and
13 terms pursuant to its AGPLv3 Section 7.
15 This notice must be preserved when any source code is
16 conveyed and/or propagated.
18 Bacula(R) is a registered trademark of Kern Sibbald.
21 * Bacula Catalog Database routines specific to MySQL
22 * These are MySQL specific routines -- hopefully all
23 * other files are generic.
25 * Written by Kern Sibbald, March 2000
27 * Note: at one point, this file was changed to class based by a certain
28 * programmer, and other than "wrapping" in a class, which is a trivial
29 * change for a C++ programmer, nothing substantial was done, yet all the
30 * code was recommitted under this programmer's name. Consequently, we
31 * undo those changes here.
41 #define __BDB_MYSQL_H_ 1
42 #include "bdb_mysql.h"
44 /* -----------------------------------------------------------------------
46 * MySQL dependent defines and subroutines
48 * -----------------------------------------------------------------------
51 /* List of open databases */
52 static dlist *db_list = NULL;
54 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
56 BDB_MYSQL::BDB_MYSQL()
58 BDB_MYSQL *mdb = this;
60 if (db_list == NULL) {
61 db_list = New(dlist(this, &this->m_link));
63 mdb->m_db_driver_type = SQL_DRIVER_TYPE_MYSQL;
64 mdb->m_db_type = SQL_TYPE_MYSQL;
65 mdb->m_db_driver = bstrdup("MySQL");
66 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
68 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
69 mdb->cached_path = get_pool_memory(PM_FNAME);
70 mdb->cached_path_id = 0;
72 mdb->fname = get_pool_memory(PM_FNAME);
73 mdb->path = get_pool_memory(PM_FNAME);
74 mdb->esc_name = get_pool_memory(PM_FNAME);
75 mdb->esc_path = get_pool_memory(PM_FNAME);
76 mdb->esc_obj = get_pool_memory(PM_FNAME);
77 mdb->m_use_fatal_jmsg = true;
79 /* Initialize the private members. */
80 mdb->m_db_handle = NULL;
83 db_list->append(this);
86 BDB_MYSQL::~BDB_MYSQL()
91 * Initialize database data structure. In principal this should
92 * never have errors, or it is really fatal.
94 BDB *db_init_database(JCR *jcr, const char *db_driver, const char *db_name, const char *db_user,
95 const char *db_password, const char *db_address, int db_port, const char *db_socket,
96 bool mult_db_connections, bool disable_batch_insert)
98 BDB_MYSQL *mdb = NULL;
101 Jmsg(jcr, M_FATAL, 0, _("A user name for MySQL must be supplied.\n"));
104 P(mutex); /* lock DB queue */
107 * Look to see if DB already open
109 if (db_list && !mult_db_connections) {
110 foreach_dlist(mdb, db_list) {
111 if (mdb->bdb_match_database(db_driver, db_name, db_address, db_port)) {
112 Dmsg1(100, "DB REopen %s\n", db_name);
113 mdb->increment_refcount();
118 Dmsg0(100, "db_init_database first time\n");
119 mdb = New(BDB_MYSQL());
120 if (!mdb) goto get_out;
123 * Initialize the parent class members.
125 mdb->m_db_name = bstrdup(db_name);
126 mdb->m_db_user = bstrdup(db_user);
128 mdb->m_db_password = bstrdup(db_password);
131 mdb->m_db_address = bstrdup(db_address);
134 mdb->m_db_socket = bstrdup(db_socket);
136 mdb->m_db_port = db_port;
138 if (disable_batch_insert) {
139 mdb->m_disabled_batch_insert = true;
140 mdb->m_have_batch_insert = false;
142 mdb->m_disabled_batch_insert = false;
143 #ifdef USE_BATCH_FILE_INSERT
144 #ifdef HAVE_MYSQL_THREAD_SAFE
145 mdb->m_have_batch_insert = mysql_thread_safe();
147 mdb->m_have_batch_insert = false;
148 #endif /* HAVE_MYSQL_THREAD_SAFE */
150 mdb->m_have_batch_insert = false;
151 #endif /* USE_BATCH_FILE_INSERT */
154 mdb->m_allow_transactions = mult_db_connections;
156 /* At this time, when mult_db_connections == true, this is for
157 * specific console command such as bvfs or batch mode, and we don't
158 * want to share a batch mode or bvfs. In the future, we can change
159 * the creation function to add this parameter.
161 mdb->m_dedicated = mult_db_connections;
170 * Now actually open the database. This can generate errors,
171 * which are returned in the errmsg
173 * DO NOT close the database or delete mdb here !!!!
175 bool BDB_MYSQL::bdb_open_database(JCR *jcr)
177 BDB_MYSQL *mdb = this;
182 if (mdb->m_connected) {
187 if ((errstat=rwl_init(&mdb->m_lock)) != 0) {
189 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
190 be.bstrerror(errstat));
195 * Connect to the database
197 #ifdef xHAVE_EMBEDDED_MYSQL
198 // mysql_server_init(0, NULL, NULL);
200 mysql_init(&mdb->m_instance);
202 Dmsg0(50, "mysql_init done\n");
204 * If connection fails, try at 5 sec intervals for 30 seconds.
206 for (int retry=0; retry < 6; retry++) {
207 mdb->m_db_handle = mysql_real_connect(
208 &(mdb->m_instance), /* db */
209 mdb->m_db_address, /* default = localhost */
210 mdb->m_db_user, /* login name */
211 mdb->m_db_password, /* password */
212 mdb->m_db_name, /* database name */
213 mdb->m_db_port, /* default port */
214 mdb->m_db_socket, /* default = socket */
215 CLIENT_FOUND_ROWS); /* flags */
218 * If no connect, try once more in case it is a timing problem
220 if (mdb->m_db_handle != NULL) {
226 mdb->m_instance.reconnect = 1; /* so connection does not timeout */
227 Dmsg0(50, "mysql_real_connect done\n");
228 Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n", mdb->m_db_user, mdb->m_db_name,
229 (mdb->m_db_password == NULL) ? "(NULL)" : mdb->m_db_password);
231 if (mdb->m_db_handle == NULL) {
232 Mmsg2(&mdb->errmsg, _("Unable to connect to MySQL server.\n"
233 "Database=%s User=%s\n"
234 "MySQL connect failed either server not running or your authorization is incorrect.\n"),
235 mdb->m_db_name, mdb->m_db_user);
236 #if MYSQL_VERSION_ID >= 40101
237 Dmsg3(50, "Error %u (%s): %s\n",
238 mysql_errno(&(mdb->m_instance)), mysql_sqlstate(&(mdb->m_instance)),
239 mysql_error(&(mdb->m_instance)));
241 Dmsg2(50, "Error %u: %s\n",
242 mysql_errno(&(mdb->m_instance)), mysql_error(&(mdb->m_instance)));
247 mdb->m_connected = true;
248 if (!bdb_check_version(jcr)) {
252 Dmsg3(100, "opendb ref=%d connected=%d db=%p\n", mdb->m_ref_count, mdb->m_connected, mdb->m_db_handle);
255 * Set connection timeout to 8 days specialy for batch mode
257 sql_query("SET wait_timeout=691200");
258 sql_query("SET interactive_timeout=691200");
267 void BDB_MYSQL::bdb_close_database(JCR *jcr)
269 BDB_MYSQL *mdb = this;
271 if (mdb->m_connected) {
272 bdb_end_transaction(jcr);
276 Dmsg3(100, "closedb ref=%d connected=%d db=%p\n", mdb->m_ref_count, mdb->m_connected, mdb->m_db_handle);
277 if (mdb->m_ref_count == 0) {
278 if (mdb->m_connected) {
281 db_list->remove(mdb);
282 if (mdb->m_connected) {
283 Dmsg1(100, "close db=%p\n", mdb->m_db_handle);
284 mysql_close(&mdb->m_instance);
286 if (is_rwl_valid(&mdb->m_lock)) {
287 rwl_destroy(&mdb->m_lock);
289 free_pool_memory(mdb->errmsg);
290 free_pool_memory(mdb->cmd);
291 free_pool_memory(mdb->cached_path);
292 free_pool_memory(mdb->fname);
293 free_pool_memory(mdb->path);
294 free_pool_memory(mdb->esc_name);
295 free_pool_memory(mdb->esc_path);
296 free_pool_memory(mdb->esc_obj);
297 if (mdb->m_db_driver) {
298 free(mdb->m_db_driver);
300 if (mdb->m_db_name) {
301 free(mdb->m_db_name);
303 if (mdb->m_db_user) {
304 free(mdb->m_db_user);
306 if (mdb->m_db_password) {
307 free(mdb->m_db_password);
309 if (mdb->m_db_address) {
310 free(mdb->m_db_address);
312 if (mdb->m_db_socket) {
313 free(mdb->m_db_socket);
316 if (db_list->size() == 0) {
325 * This call is needed because the message channel thread
326 * opens a database on behalf of a jcr that was created in
327 * a different thread. MySQL then allocates thread specific
328 * data, which is NOT freed when the original jcr thread
329 * closes the database. Thus the msgchan must call here
330 * to cleanup any thread specific data that it created.
332 void BDB_MYSQL::bdb_thread_cleanup(void)
335 mysql_thread_end(); /* Cleanup thread specific data */
340 * Escape strings so MySQL is happy
342 * len is the length of the old string. Your new
343 * string must be long enough (max 2*old+1) to hold
344 * the escaped output.
346 void BDB_MYSQL::bdb_escape_string(JCR *jcr, char *snew, char *old, int len)
348 BDB_MYSQL *mdb = this;
349 mysql_real_escape_string(mdb->m_db_handle, snew, old, len);
353 * Escape binary object so that MySQL is happy
354 * Memory is stored in BDB struct, no need to free it
356 char *BDB_MYSQL::bdb_escape_object(JCR *jcr, char *old, int len)
358 BDB_MYSQL *mdb = this;
359 mdb->esc_obj = check_pool_memory_size(mdb->esc_obj, len*2+1);
360 mysql_real_escape_string(mdb->m_db_handle, mdb->esc_obj, old, len);
365 * Unescape binary object so that MySQL is happy
367 void BDB_MYSQL::bdb_unescape_object(JCR *jcr, char *from, int32_t expected_len,
368 POOLMEM **dest, int32_t *dest_len)
375 *dest = check_pool_memory_size(*dest, expected_len+1);
376 *dest_len = expected_len;
377 memcpy(*dest, from, expected_len);
378 (*dest)[expected_len]=0;
381 void BDB_MYSQL::bdb_start_transaction(JCR *jcr)
384 jcr->attr = get_pool_memory(PM_FNAME);
387 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
391 void BDB_MYSQL::bdb_end_transaction(JCR *jcr)
393 if (jcr && jcr->cached_attribute) {
394 Dmsg0(400, "Flush last cached attribute.\n");
395 if (!bdb_create_attributes_record(jcr, jcr->ar)) {
396 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), jcr->db->bdb_strerror());
398 jcr->cached_attribute = false;
403 * Submit a general SQL command (cmd), and for each row returned,
404 * the result_handler is called with the ctx.
406 bool BDB_MYSQL::bdb_sql_query(const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
412 BDB_MYSQL *mdb = this;
414 Dmsg1(500, "db_sql_query starts with %s\n", query);
418 ret = mysql_query(m_db_handle, query);
420 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror());
421 Dmsg0(500, "db_sql_query failed\n");
425 Dmsg0(500, "db_sql_query succeeded. checking handler\n");
427 if (result_handler) {
428 if ((mdb->m_result = mysql_use_result(mdb->m_db_handle)) != NULL) {
429 mdb->m_num_fields = mysql_num_fields(mdb->m_result);
432 * We *must* fetch all rows
434 while ((row = mysql_fetch_row(m_result))) {
436 /* the result handler returns 1 when it has
437 * seen all the data it wants. However, we
438 * loop to the end of the data.
440 if (result_handler(ctx, mdb->m_num_fields, row)) {
449 Dmsg0(500, "db_sql_query finished\n");
457 bool BDB_MYSQL::sql_query(const char *query, int flags)
461 BDB_MYSQL *mdb = this;
463 Dmsg1(500, "sql_query starts with '%s'\n", query);
465 * We are starting a new query. reset everything.
467 mdb->m_num_rows = -1;
468 mdb->m_row_number = -1;
469 mdb->m_field_number = -1;
472 mysql_free_result(mdb->m_result);
473 mdb->m_result = NULL;
476 ret = mysql_query(mdb->m_db_handle, query);
478 Dmsg0(500, "we have a result\n");
479 if (flags & QF_STORE_RESULT) {
480 mdb->m_result = mysql_store_result(mdb->m_db_handle);
481 if (mdb->m_result != NULL) {
482 mdb->m_num_fields = mysql_num_fields(mdb->m_result);
483 Dmsg1(500, "we have %d fields\n", mdb->m_num_fields);
484 mdb->m_num_rows = mysql_num_rows(mdb->m_result);
485 Dmsg1(500, "we have %d rows\n", mdb->m_num_rows);
487 mdb->m_num_fields = 0;
488 mdb->m_num_rows = mysql_affected_rows(mdb->m_db_handle);
489 Dmsg1(500, "we have %d rows\n", mdb->m_num_rows);
492 mdb->m_num_fields = 0;
493 mdb->m_num_rows = mysql_affected_rows(mdb->m_db_handle);
494 Dmsg1(500, "we have %d rows\n", mdb->m_num_rows);
497 Dmsg0(500, "we failed\n");
498 mdb->m_status = 1; /* failed */
504 void BDB_MYSQL::sql_free_result(void)
506 BDB_MYSQL *mdb = this;
509 mysql_free_result(mdb->m_result);
510 mdb->m_result = NULL;
514 mdb->m_fields = NULL;
516 mdb->m_num_rows = mdb->m_num_fields = 0;
520 SQL_ROW BDB_MYSQL::sql_fetch_row(void)
522 BDB_MYSQL *mdb = this;
523 if (!mdb->m_result) {
526 return mysql_fetch_row(mdb->m_result);
530 const char *BDB_MYSQL::sql_strerror(void)
532 BDB_MYSQL *mdb = this;
533 return mysql_error(mdb->m_db_handle);
536 void BDB_MYSQL::sql_data_seek(int row)
538 BDB_MYSQL *mdb = this;
539 return mysql_data_seek(mdb->m_result, row);
542 int BDB_MYSQL::sql_affected_rows(void)
544 BDB_MYSQL *mdb = this;
545 return mysql_affected_rows(mdb->m_db_handle);
548 uint64_t BDB_MYSQL::sql_insert_autokey_record(const char *query, const char *table_name)
550 BDB_MYSQL *mdb = this;
552 * First execute the insert query and then retrieve the currval.
554 if (mysql_query(mdb->m_db_handle, query) != 0) {
558 mdb->m_num_rows = mysql_affected_rows(mdb->m_db_handle);
559 if (mdb->m_num_rows != 1) {
565 return mysql_insert_id(mdb->m_db_handle);
568 SQL_FIELD *BDB_MYSQL::sql_fetch_field(void)
572 BDB_MYSQL *mdb = this;
574 if (!mdb->m_fields || mdb->m_fields_size < mdb->m_num_fields) {
577 mdb->m_fields = NULL;
579 Dmsg1(500, "allocating space for %d fields\n", mdb->m_num_fields);
580 mdb->m_fields = (SQL_FIELD *)malloc(sizeof(SQL_FIELD) * mdb->m_num_fields);
581 mdb->m_fields_size = mdb->m_num_fields;
583 for (i = 0; i < mdb->m_num_fields; i++) {
584 Dmsg1(500, "filling field %d\n", i);
585 if ((field = mysql_fetch_field(mdb->m_result)) != NULL) {
586 mdb->m_fields[i].name = field->name;
587 mdb->m_fields[i].max_length = field->max_length;
588 mdb->m_fields[i].type = field->type;
589 mdb->m_fields[i].flags = field->flags;
591 Dmsg4(500, "sql_fetch_field finds field '%s' has length='%d' type='%d' and IsNull=%d\n",
592 mdb->m_fields[i].name, mdb->m_fields[i].max_length, mdb->m_fields[i].type, mdb->m_fields[i].flags);
598 * Increment field number for the next time around
600 return &mdb->m_fields[mdb->m_field_number++];
603 bool BDB_MYSQL::sql_field_is_not_null(int field_type)
605 return IS_NOT_NULL(field_type);
608 bool BDB_MYSQL::sql_field_is_numeric(int field_type)
610 return IS_NUM(field_type);
617 bool BDB_MYSQL::sql_batch_start(JCR *jcr)
619 BDB_MYSQL *mdb = this;
623 retval = sql_query("CREATE TEMPORARY TABLE batch ("
630 "DeltaSeq integer)");
634 * Keep track of the number of changes in batch mode.
641 /* set error to something to abort operation */
646 bool BDB_MYSQL::sql_batch_end(JCR *jcr, const char *error)
648 BDB_MYSQL *mdb = this;
653 * Flush any pending inserts.
656 return sql_query(mdb->cmd);
666 bool BDB_MYSQL::sql_batch_insert(JCR *jcr, ATTR_DBR *ar)
668 BDB_MYSQL *mdb = this;
672 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
673 bdb_escape_string(jcr, mdb->esc_name, mdb->fname, mdb->fnl);
675 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
676 bdb_escape_string(jcr, mdb->esc_path, mdb->path, mdb->pnl);
678 if (ar->Digest == NULL || ar->Digest[0] == 0) {
685 * Try to batch up multiple inserts using multi-row inserts.
687 if (mdb->changes == 0) {
688 Mmsg(cmd, "INSERT INTO batch VALUES "
689 "(%u,%s,'%s','%s','%s','%s',%u)",
690 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
691 mdb->esc_name, ar->attr, digest, ar->DeltaSeq);
695 * We use the esc_obj for temporary storage otherwise
696 * we keep on copying data.
698 Mmsg(mdb->esc_obj, ",(%u,%s,'%s','%s','%s','%s',%u)",
699 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
700 mdb->esc_name, ar->attr, digest, ar->DeltaSeq);
701 pm_strcat(mdb->cmd, mdb->esc_obj);
706 * See if we need to flush the query buffer filled
707 * with multi-row inserts.
709 if ((mdb->changes % MYSQL_CHANGES_PER_BATCH_INSERT) == 0) {
710 if (!sql_query(mdb->cmd)) {
721 #endif /* HAVE_MYSQL */