2 Bacula(R) - The Network Backup Solution
4 Copyright (C) 2000-2016 Kern Sibbald
6 The original author of Bacula is Kern Sibbald, with contributions
7 from many others, a complete list can be found in the file AUTHORS.
9 You may use this file and others of this release according to the
10 license defined in the LICENSE file, which includes the Affero General
11 Public License, v3.0 ("AGPLv3") and some additional permissions and
12 terms pursuant to its AGPLv3 Section 7.
14 This notice must be preserved when any source code is
15 conveyed and/or propagated.
17 Bacula(R) is a registered trademark of Kern Sibbald.
20 * Bacula Catalog Database routines specific to MySQL
21 * These are MySQL specific routines -- hopefully all
22 * other files are generic.
24 * Written by Kern Sibbald, March 2000
26 * Note: at one point, this file was changed to class based by a certain
27 * programmer, and other than "wrapping" in a class, which is a trivial
28 * change for a C++ programmer, nothing substantial was done, yet all the
29 * code was recommitted under this programmer's name. Consequently, we
30 * undo those changes here.
40 #define BDB_MYSQL_H_ 1
41 #include "bdb_mysql.h"
43 /* -----------------------------------------------------------------------
45 * MySQL dependent defines and subroutines
47 * -----------------------------------------------------------------------
50 /* List of open databases */
51 static dlist *db_list = NULL;
53 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
55 BDB_MYSQL::BDB_MYSQL()
57 BDB_MYSQL *mdb = this;
59 if (db_list == NULL) {
60 db_list = New(dlist(this, &this->m_link));
62 mdb->m_db_driver_type = SQL_DRIVER_TYPE_MYSQL;
63 mdb->m_db_type = SQL_TYPE_MYSQL;
64 mdb->m_db_driver = bstrdup("MySQL");
65 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
67 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
68 mdb->cached_path = get_pool_memory(PM_FNAME);
69 mdb->cached_path_id = 0;
71 mdb->fname = get_pool_memory(PM_FNAME);
72 mdb->path = get_pool_memory(PM_FNAME);
73 mdb->esc_name = get_pool_memory(PM_FNAME);
74 mdb->esc_path = get_pool_memory(PM_FNAME);
75 mdb->esc_obj = get_pool_memory(PM_FNAME);
76 mdb->m_use_fatal_jmsg = true;
78 /* Initialize the private members. */
79 mdb->m_db_handle = NULL;
82 db_list->append(this);
85 BDB_MYSQL::~BDB_MYSQL()
90 * Initialize database data structure. In principal this should
91 * never have errors, or it is really fatal.
93 BDB *db_init_database(JCR *jcr, const char *db_driver, const char *db_name, const char *db_user,
94 const char *db_password, const char *db_address, int db_port, const char *db_socket,
95 const char *db_ssl_key, const char *db_ssl_cert, const char *db_ssl_ca,
96 const char *db_ssl_capath, const char *db_ssl_cipher,
97 bool mult_db_connections, bool disable_batch_insert)
99 BDB_MYSQL *mdb = NULL;
102 Jmsg(jcr, M_FATAL, 0, _("A user name for MySQL must be supplied.\n"));
105 P(mutex); /* lock DB queue */
108 * Look to see if DB already open
110 if (db_list && !mult_db_connections) {
111 foreach_dlist(mdb, db_list) {
112 if (mdb->bdb_match_database(db_driver, db_name, db_address, db_port)) {
113 Dmsg1(100, "DB REopen %s\n", db_name);
114 mdb->increment_refcount();
119 Dmsg0(100, "db_init_database first time\n");
120 mdb = New(BDB_MYSQL());
121 if (!mdb) goto get_out;
124 * Initialize the parent class members.
126 mdb->m_db_name = bstrdup(db_name);
127 mdb->m_db_user = bstrdup(db_user);
129 mdb->m_db_password = bstrdup(db_password);
132 mdb->m_db_address = bstrdup(db_address);
135 mdb->m_db_socket = bstrdup(db_socket);
138 mdb->m_db_ssl_key = bstrdup(db_ssl_key);
141 mdb->m_db_ssl_cert = bstrdup(db_ssl_cert);
144 mdb->m_db_ssl_ca = bstrdup(db_ssl_ca);
147 mdb->m_db_ssl_capath = bstrdup(db_ssl_capath);
150 mdb->m_db_ssl_cipher = bstrdup(db_ssl_cipher);
152 mdb->m_db_port = db_port;
154 if (disable_batch_insert) {
155 mdb->m_disabled_batch_insert = true;
156 mdb->m_have_batch_insert = false;
158 mdb->m_disabled_batch_insert = false;
159 #ifdef USE_BATCH_FILE_INSERT
160 #ifdef HAVE_MYSQL_THREAD_SAFE
161 mdb->m_have_batch_insert = mysql_thread_safe();
163 mdb->m_have_batch_insert = false;
164 #endif /* HAVE_MYSQL_THREAD_SAFE */
166 mdb->m_have_batch_insert = false;
167 #endif /* USE_BATCH_FILE_INSERT */
170 mdb->m_allow_transactions = mult_db_connections;
172 /* At this time, when mult_db_connections == true, this is for
173 * specific console command such as bvfs or batch mode, and we don't
174 * want to share a batch mode or bvfs. In the future, we can change
175 * the creation function to add this parameter.
177 mdb->m_dedicated = mult_db_connections;
186 * Now actually open the database. This can generate errors,
187 * which are returned in the errmsg
189 * DO NOT close the database or delete mdb here !!!!
191 bool BDB_MYSQL::bdb_open_database(JCR *jcr)
193 BDB_MYSQL *mdb = this;
198 if (mdb->m_connected) {
203 if ((errstat=rwl_init(&mdb->m_lock)) != 0) {
205 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
206 be.bstrerror(errstat));
211 * Connect to the database
213 #ifdef xHAVE_EMBEDDED_MYSQL
214 // mysql_server_init(0, NULL, NULL);
216 mysql_init(&mdb->m_instance);
218 Dmsg0(50, "mysql_init done\n");
221 * Sets the appropriate certificate options for
222 * establishing secure connection using SSL to the database.
224 if (mdb->m_db_ssl_key) {
225 mysql_ssl_set(&(mdb->m_instance),
229 mdb->m_db_ssl_capath,
230 mdb->m_db_ssl_cipher);
234 * If connection fails, try at 5 sec intervals for 30 seconds.
236 for (int retry=0; retry < 6; retry++) {
237 mdb->m_db_handle = mysql_real_connect(
238 &(mdb->m_instance), /* db */
239 mdb->m_db_address, /* default = localhost */
240 mdb->m_db_user, /* login name */
241 mdb->m_db_password, /* password */
242 mdb->m_db_name, /* database name */
243 mdb->m_db_port, /* default port */
244 mdb->m_db_socket, /* default = socket */
245 CLIENT_FOUND_ROWS); /* flags */
248 * If no connect, try once more in case it is a timing problem
250 if (mdb->m_db_handle != NULL) {
256 mdb->m_instance.reconnect = 1; /* so connection does not timeout */
257 Dmsg0(50, "mysql_real_connect done\n");
258 Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n", mdb->m_db_user, mdb->m_db_name,
259 (mdb->m_db_password == NULL) ? "(NULL)" : mdb->m_db_password);
261 if (mdb->m_db_handle == NULL) {
262 Mmsg2(&mdb->errmsg, _("Unable to connect to MySQL server.\n"
263 "Database=%s User=%s\n"
264 "MySQL connect failed either server not running or your authorization is incorrect.\n"),
265 mdb->m_db_name, mdb->m_db_user);
266 #if MYSQL_VERSION_ID >= 40101
267 Dmsg3(50, "Error %u (%s): %s\n",
268 mysql_errno(&(mdb->m_instance)), mysql_sqlstate(&(mdb->m_instance)),
269 mysql_error(&(mdb->m_instance)));
271 Dmsg2(50, "Error %u: %s\n",
272 mysql_errno(&(mdb->m_instance)), mysql_error(&(mdb->m_instance)));
277 /* get the current cipher used for SSL connection */
278 if (mdb->m_db_ssl_key) {
280 if (mdb->m_db_ssl_cipher) {
281 free(mdb->m_db_ssl_cipher);
283 cipher = (const char *)mysql_get_ssl_cipher(&(mdb->m_instance));
285 mdb->m_db_ssl_cipher = bstrdup(cipher);
287 Dmsg1(50, "db_ssl_ciper=%s\n", (mdb->m_db_ssl_cipher == NULL) ? "(NULL)" : mdb->m_db_ssl_cipher);
290 mdb->m_connected = true;
291 if (!bdb_check_version(jcr)) {
295 Dmsg3(100, "opendb ref=%d connected=%d db=%p\n", mdb->m_ref_count, mdb->m_connected, mdb->m_db_handle);
298 * Set connection timeout to 8 days specialy for batch mode
300 sql_query("SET wait_timeout=691200");
301 sql_query("SET interactive_timeout=691200");
310 void BDB_MYSQL::bdb_close_database(JCR *jcr)
312 BDB_MYSQL *mdb = this;
314 if (mdb->m_connected) {
315 bdb_end_transaction(jcr);
319 Dmsg3(100, "closedb ref=%d connected=%d db=%p\n", mdb->m_ref_count, mdb->m_connected, mdb->m_db_handle);
320 if (mdb->m_ref_count == 0) {
321 if (mdb->m_connected) {
324 db_list->remove(mdb);
325 if (mdb->m_connected) {
326 Dmsg1(100, "close db=%p\n", mdb->m_db_handle);
327 mysql_close(&mdb->m_instance);
329 if (is_rwl_valid(&mdb->m_lock)) {
330 rwl_destroy(&mdb->m_lock);
332 free_pool_memory(mdb->errmsg);
333 free_pool_memory(mdb->cmd);
334 free_pool_memory(mdb->cached_path);
335 free_pool_memory(mdb->fname);
336 free_pool_memory(mdb->path);
337 free_pool_memory(mdb->esc_name);
338 free_pool_memory(mdb->esc_path);
339 free_pool_memory(mdb->esc_obj);
340 if (mdb->m_db_driver) {
341 free(mdb->m_db_driver);
343 if (mdb->m_db_name) {
344 free(mdb->m_db_name);
346 if (mdb->m_db_user) {
347 free(mdb->m_db_user);
349 if (mdb->m_db_password) {
350 free(mdb->m_db_password);
352 if (mdb->m_db_address) {
353 free(mdb->m_db_address);
355 if (mdb->m_db_socket) {
356 free(mdb->m_db_socket);
358 if (mdb->m_db_ssl_key) {
359 free(mdb->m_db_ssl_key);
361 if (mdb->m_db_ssl_cert) {
362 free(mdb->m_db_ssl_cert);
364 if (mdb->m_db_ssl_ca) {
365 free(mdb->m_db_ssl_ca);
367 if (mdb->m_db_ssl_capath) {
368 free(mdb->m_db_ssl_capath);
370 if (mdb->m_db_ssl_cipher) {
371 free(mdb->m_db_ssl_cipher);
374 if (db_list->size() == 0) {
383 * This call is needed because the message channel thread
384 * opens a database on behalf of a jcr that was created in
385 * a different thread. MySQL then allocates thread specific
386 * data, which is NOT freed when the original jcr thread
387 * closes the database. Thus the msgchan must call here
388 * to cleanup any thread specific data that it created.
390 void BDB_MYSQL::bdb_thread_cleanup(void)
393 mysql_thread_end(); /* Cleanup thread specific data */
398 * Escape strings so MySQL is happy
400 * len is the length of the old string. Your new
401 * string must be long enough (max 2*old+1) to hold
402 * the escaped output.
404 void BDB_MYSQL::bdb_escape_string(JCR *jcr, char *snew, char *old, int len)
406 BDB_MYSQL *mdb = this;
407 mysql_real_escape_string(mdb->m_db_handle, snew, old, len);
411 * Escape binary object so that MySQL is happy
412 * Memory is stored in BDB struct, no need to free it
414 char *BDB_MYSQL::bdb_escape_object(JCR *jcr, char *old, int len)
416 BDB_MYSQL *mdb = this;
417 mdb->esc_obj = check_pool_memory_size(mdb->esc_obj, len*2+1);
418 mysql_real_escape_string(mdb->m_db_handle, mdb->esc_obj, old, len);
423 * Unescape binary object so that MySQL is happy
425 void BDB_MYSQL::bdb_unescape_object(JCR *jcr, char *from, int32_t expected_len,
426 POOLMEM **dest, int32_t *dest_len)
433 *dest = check_pool_memory_size(*dest, expected_len+1);
434 *dest_len = expected_len;
435 memcpy(*dest, from, expected_len);
436 (*dest)[expected_len]=0;
439 void BDB_MYSQL::bdb_start_transaction(JCR *jcr)
442 jcr->attr = get_pool_memory(PM_FNAME);
445 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
446 memset(jcr->ar, 0, sizeof(ATTR_DBR));
450 void BDB_MYSQL::bdb_end_transaction(JCR *jcr)
452 if (jcr && jcr->cached_attribute) {
453 Dmsg0(400, "Flush last cached attribute.\n");
454 if (!bdb_create_attributes_record(jcr, jcr->ar)) {
455 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), jcr->db->bdb_strerror());
457 jcr->cached_attribute = false;
462 * Submit a general SQL command (cmd), and for each row returned,
463 * the result_handler is called with the ctx.
465 bool BDB_MYSQL::bdb_sql_query(const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
471 BDB_MYSQL *mdb = this;
473 Dmsg1(500, "db_sql_query starts with %s\n", query);
477 ret = mysql_query(m_db_handle, query);
479 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror());
480 Dmsg0(500, "db_sql_query failed\n");
484 Dmsg0(500, "db_sql_query succeeded. checking handler\n");
486 if (result_handler) {
487 if ((mdb->m_result = mysql_use_result(mdb->m_db_handle)) != NULL) {
488 mdb->m_num_fields = mysql_num_fields(mdb->m_result);
491 * We *must* fetch all rows
493 while ((row = mysql_fetch_row(m_result))) {
495 /* the result handler returns 1 when it has
496 * seen all the data it wants. However, we
497 * loop to the end of the data.
499 if (result_handler(ctx, mdb->m_num_fields, row)) {
508 Dmsg0(500, "db_sql_query finished\n");
516 bool BDB_MYSQL::sql_query(const char *query, int flags)
520 BDB_MYSQL *mdb = this;
522 Dmsg1(500, "sql_query starts with '%s'\n", query);
524 * We are starting a new query. reset everything.
526 mdb->m_num_rows = -1;
527 mdb->m_row_number = -1;
528 mdb->m_field_number = -1;
531 mysql_free_result(mdb->m_result);
532 mdb->m_result = NULL;
535 ret = mysql_query(mdb->m_db_handle, query);
537 Dmsg0(500, "we have a result\n");
538 if (flags & QF_STORE_RESULT) {
539 mdb->m_result = mysql_store_result(mdb->m_db_handle);
540 if (mdb->m_result != NULL) {
541 mdb->m_num_fields = mysql_num_fields(mdb->m_result);
542 Dmsg1(500, "we have %d fields\n", mdb->m_num_fields);
543 mdb->m_num_rows = mysql_num_rows(mdb->m_result);
544 Dmsg1(500, "we have %d rows\n", mdb->m_num_rows);
546 mdb->m_num_fields = 0;
547 mdb->m_num_rows = mysql_affected_rows(mdb->m_db_handle);
548 Dmsg1(500, "we have %d rows\n", mdb->m_num_rows);
551 mdb->m_num_fields = 0;
552 mdb->m_num_rows = mysql_affected_rows(mdb->m_db_handle);
553 Dmsg1(500, "we have %d rows\n", mdb->m_num_rows);
556 Dmsg0(500, "we failed\n");
557 mdb->m_status = 1; /* failed */
563 void BDB_MYSQL::sql_free_result(void)
565 BDB_MYSQL *mdb = this;
568 mysql_free_result(mdb->m_result);
569 mdb->m_result = NULL;
573 mdb->m_fields = NULL;
575 mdb->m_num_rows = mdb->m_num_fields = 0;
579 SQL_ROW BDB_MYSQL::sql_fetch_row(void)
581 BDB_MYSQL *mdb = this;
582 if (!mdb->m_result) {
585 return mysql_fetch_row(mdb->m_result);
589 const char *BDB_MYSQL::sql_strerror(void)
591 BDB_MYSQL *mdb = this;
592 return mysql_error(mdb->m_db_handle);
595 void BDB_MYSQL::sql_data_seek(int row)
597 BDB_MYSQL *mdb = this;
598 return mysql_data_seek(mdb->m_result, row);
601 int BDB_MYSQL::sql_affected_rows(void)
603 BDB_MYSQL *mdb = this;
604 return mysql_affected_rows(mdb->m_db_handle);
607 uint64_t BDB_MYSQL::sql_insert_autokey_record(const char *query, const char *table_name)
609 BDB_MYSQL *mdb = this;
611 * First execute the insert query and then retrieve the currval.
613 if (mysql_query(mdb->m_db_handle, query) != 0) {
617 mdb->m_num_rows = mysql_affected_rows(mdb->m_db_handle);
618 if (mdb->m_num_rows != 1) {
624 return mysql_insert_id(mdb->m_db_handle);
627 SQL_FIELD *BDB_MYSQL::sql_fetch_field(void)
631 BDB_MYSQL *mdb = this;
633 if (!mdb->m_fields || mdb->m_fields_size < mdb->m_num_fields) {
636 mdb->m_fields = NULL;
638 Dmsg1(500, "allocating space for %d fields\n", mdb->m_num_fields);
639 mdb->m_fields = (SQL_FIELD *)malloc(sizeof(SQL_FIELD) * mdb->m_num_fields);
640 mdb->m_fields_size = mdb->m_num_fields;
642 for (i = 0; i < mdb->m_num_fields; i++) {
643 Dmsg1(500, "filling field %d\n", i);
644 if ((field = mysql_fetch_field(mdb->m_result)) != NULL) {
645 mdb->m_fields[i].name = field->name;
646 mdb->m_fields[i].max_length = field->max_length;
647 mdb->m_fields[i].type = field->type;
648 mdb->m_fields[i].flags = field->flags;
650 Dmsg4(500, "sql_fetch_field finds field '%s' has length='%d' type='%d' and IsNull=%d\n",
651 mdb->m_fields[i].name, mdb->m_fields[i].max_length, mdb->m_fields[i].type, mdb->m_fields[i].flags);
657 * Increment field number for the next time around
659 return &mdb->m_fields[mdb->m_field_number++];
662 bool BDB_MYSQL::sql_field_is_not_null(int field_type)
664 return IS_NOT_NULL(field_type);
667 bool BDB_MYSQL::sql_field_is_numeric(int field_type)
669 return IS_NUM(field_type);
676 bool BDB_MYSQL::sql_batch_start(JCR *jcr)
678 BDB_MYSQL *mdb = this;
682 retval = sql_query("CREATE TEMPORARY TABLE batch ("
689 "DeltaSeq integer)");
693 * Keep track of the number of changes in batch mode.
700 /* set error to something to abort operation */
705 bool BDB_MYSQL::sql_batch_end(JCR *jcr, const char *error)
707 BDB_MYSQL *mdb = this;
712 * Flush any pending inserts.
715 return sql_query(mdb->cmd);
725 bool BDB_MYSQL::sql_batch_insert(JCR *jcr, ATTR_DBR *ar)
727 BDB_MYSQL *mdb = this;
731 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
732 bdb_escape_string(jcr, mdb->esc_name, mdb->fname, mdb->fnl);
734 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
735 bdb_escape_string(jcr, mdb->esc_path, mdb->path, mdb->pnl);
737 if (ar->Digest == NULL || ar->Digest[0] == 0) {
744 * Try to batch up multiple inserts using multi-row inserts.
746 if (mdb->changes == 0) {
747 Mmsg(cmd, "INSERT INTO batch VALUES "
748 "(%u,%s,'%s','%s','%s','%s',%u)",
749 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
750 mdb->esc_name, ar->attr, digest, ar->DeltaSeq);
754 * We use the esc_obj for temporary storage otherwise
755 * we keep on copying data.
757 Mmsg(mdb->esc_obj, ",(%u,%s,'%s','%s','%s','%s',%u)",
758 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
759 mdb->esc_name, ar->attr, digest, ar->DeltaSeq);
760 pm_strcat(mdb->cmd, mdb->esc_obj);
765 * See if we need to flush the query buffer filled
766 * with multi-row inserts.
768 if ((mdb->changes % MYSQL_CHANGES_PER_BATCH_INSERT) == 0) {
769 if (!sql_query(mdb->cmd)) {
780 #endif /* HAVE_MYSQL */