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));
449 void BDB_MYSQL::bdb_end_transaction(JCR *jcr)
451 if (jcr && jcr->cached_attribute) {
452 Dmsg0(400, "Flush last cached attribute.\n");
453 if (!bdb_create_attributes_record(jcr, jcr->ar)) {
454 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), jcr->db->bdb_strerror());
456 jcr->cached_attribute = false;
461 * Submit a general SQL command (cmd), and for each row returned,
462 * the result_handler is called with the ctx.
464 bool BDB_MYSQL::bdb_sql_query(const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
470 BDB_MYSQL *mdb = this;
472 Dmsg1(500, "db_sql_query starts with %s\n", query);
476 ret = mysql_query(m_db_handle, query);
478 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror());
479 Dmsg0(500, "db_sql_query failed\n");
483 Dmsg0(500, "db_sql_query succeeded. checking handler\n");
485 if (result_handler) {
486 if ((mdb->m_result = mysql_use_result(mdb->m_db_handle)) != NULL) {
487 mdb->m_num_fields = mysql_num_fields(mdb->m_result);
490 * We *must* fetch all rows
492 while ((row = mysql_fetch_row(m_result))) {
494 /* the result handler returns 1 when it has
495 * seen all the data it wants. However, we
496 * loop to the end of the data.
498 if (result_handler(ctx, mdb->m_num_fields, row)) {
507 Dmsg0(500, "db_sql_query finished\n");
515 bool BDB_MYSQL::sql_query(const char *query, int flags)
519 BDB_MYSQL *mdb = this;
521 Dmsg1(500, "sql_query starts with '%s'\n", query);
523 * We are starting a new query. reset everything.
525 mdb->m_num_rows = -1;
526 mdb->m_row_number = -1;
527 mdb->m_field_number = -1;
530 mysql_free_result(mdb->m_result);
531 mdb->m_result = NULL;
534 ret = mysql_query(mdb->m_db_handle, query);
536 Dmsg0(500, "we have a result\n");
537 if (flags & QF_STORE_RESULT) {
538 mdb->m_result = mysql_store_result(mdb->m_db_handle);
539 if (mdb->m_result != NULL) {
540 mdb->m_num_fields = mysql_num_fields(mdb->m_result);
541 Dmsg1(500, "we have %d fields\n", mdb->m_num_fields);
542 mdb->m_num_rows = mysql_num_rows(mdb->m_result);
543 Dmsg1(500, "we have %d rows\n", mdb->m_num_rows);
545 mdb->m_num_fields = 0;
546 mdb->m_num_rows = mysql_affected_rows(mdb->m_db_handle);
547 Dmsg1(500, "we have %d rows\n", mdb->m_num_rows);
550 mdb->m_num_fields = 0;
551 mdb->m_num_rows = mysql_affected_rows(mdb->m_db_handle);
552 Dmsg1(500, "we have %d rows\n", mdb->m_num_rows);
555 Dmsg0(500, "we failed\n");
556 mdb->m_status = 1; /* failed */
562 void BDB_MYSQL::sql_free_result(void)
564 BDB_MYSQL *mdb = this;
567 mysql_free_result(mdb->m_result);
568 mdb->m_result = NULL;
572 mdb->m_fields = NULL;
574 mdb->m_num_rows = mdb->m_num_fields = 0;
578 SQL_ROW BDB_MYSQL::sql_fetch_row(void)
580 BDB_MYSQL *mdb = this;
581 if (!mdb->m_result) {
584 return mysql_fetch_row(mdb->m_result);
588 const char *BDB_MYSQL::sql_strerror(void)
590 BDB_MYSQL *mdb = this;
591 return mysql_error(mdb->m_db_handle);
594 void BDB_MYSQL::sql_data_seek(int row)
596 BDB_MYSQL *mdb = this;
597 return mysql_data_seek(mdb->m_result, row);
600 int BDB_MYSQL::sql_affected_rows(void)
602 BDB_MYSQL *mdb = this;
603 return mysql_affected_rows(mdb->m_db_handle);
606 uint64_t BDB_MYSQL::sql_insert_autokey_record(const char *query, const char *table_name)
608 BDB_MYSQL *mdb = this;
610 * First execute the insert query and then retrieve the currval.
612 if (mysql_query(mdb->m_db_handle, query) != 0) {
616 mdb->m_num_rows = mysql_affected_rows(mdb->m_db_handle);
617 if (mdb->m_num_rows != 1) {
623 return mysql_insert_id(mdb->m_db_handle);
626 SQL_FIELD *BDB_MYSQL::sql_fetch_field(void)
630 BDB_MYSQL *mdb = this;
632 if (!mdb->m_fields || mdb->m_fields_size < mdb->m_num_fields) {
635 mdb->m_fields = NULL;
637 Dmsg1(500, "allocating space for %d fields\n", mdb->m_num_fields);
638 mdb->m_fields = (SQL_FIELD *)malloc(sizeof(SQL_FIELD) * mdb->m_num_fields);
639 mdb->m_fields_size = mdb->m_num_fields;
641 for (i = 0; i < mdb->m_num_fields; i++) {
642 Dmsg1(500, "filling field %d\n", i);
643 if ((field = mysql_fetch_field(mdb->m_result)) != NULL) {
644 mdb->m_fields[i].name = field->name;
645 mdb->m_fields[i].max_length = field->max_length;
646 mdb->m_fields[i].type = field->type;
647 mdb->m_fields[i].flags = field->flags;
649 Dmsg4(500, "sql_fetch_field finds field '%s' has length='%d' type='%d' and IsNull=%d\n",
650 mdb->m_fields[i].name, mdb->m_fields[i].max_length, mdb->m_fields[i].type, mdb->m_fields[i].flags);
656 * Increment field number for the next time around
658 return &mdb->m_fields[mdb->m_field_number++];
661 bool BDB_MYSQL::sql_field_is_not_null(int field_type)
663 return IS_NOT_NULL(field_type);
666 bool BDB_MYSQL::sql_field_is_numeric(int field_type)
668 return IS_NUM(field_type);
675 bool BDB_MYSQL::sql_batch_start(JCR *jcr)
677 BDB_MYSQL *mdb = this;
681 retval = sql_query("CREATE TEMPORARY TABLE batch ("
688 "DeltaSeq integer)");
692 * Keep track of the number of changes in batch mode.
699 /* set error to something to abort operation */
704 bool BDB_MYSQL::sql_batch_end(JCR *jcr, const char *error)
706 BDB_MYSQL *mdb = this;
711 * Flush any pending inserts.
714 return sql_query(mdb->cmd);
724 bool BDB_MYSQL::sql_batch_insert(JCR *jcr, ATTR_DBR *ar)
726 BDB_MYSQL *mdb = this;
730 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
731 bdb_escape_string(jcr, mdb->esc_name, mdb->fname, mdb->fnl);
733 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
734 bdb_escape_string(jcr, mdb->esc_path, mdb->path, mdb->pnl);
736 if (ar->Digest == NULL || ar->Digest[0] == 0) {
743 * Try to batch up multiple inserts using multi-row inserts.
745 if (mdb->changes == 0) {
746 Mmsg(cmd, "INSERT INTO batch VALUES "
747 "(%u,%s,'%s','%s','%s','%s',%u)",
748 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
749 mdb->esc_name, ar->attr, digest, ar->DeltaSeq);
753 * We use the esc_obj for temporary storage otherwise
754 * we keep on copying data.
756 Mmsg(mdb->esc_obj, ",(%u,%s,'%s','%s','%s','%s',%u)",
757 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
758 mdb->esc_name, ar->attr, digest, ar->DeltaSeq);
759 pm_strcat(mdb->cmd, mdb->esc_obj);
764 * See if we need to flush the query buffer filled
765 * with multi-row inserts.
767 if ((mdb->changes % MYSQL_CHANGES_PER_BATCH_INSERT) == 0) {
768 if (!sql_query(mdb->cmd)) {
779 #endif /* HAVE_MYSQL */