2 Bacula(R) - The Network Backup Solution
4 Copyright (C) 2000-2017 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.
39 #define __BDB_MYSQL_H_ 1
40 #include "bdb_mysql.h"
42 /* -----------------------------------------------------------------------
44 * MySQL dependent defines and subroutines
46 * -----------------------------------------------------------------------
49 /* List of open databases */
50 static dlist *db_list = NULL;
52 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
54 BDB_MYSQL::BDB_MYSQL(): BDB()
56 BDB_MYSQL *mdb = this;
58 if (db_list == NULL) {
59 db_list = New(dlist(this, &this->m_link));
61 mdb->m_db_driver_type = SQL_DRIVER_TYPE_MYSQL;
62 mdb->m_db_type = SQL_TYPE_MYSQL;
63 mdb->m_db_driver = bstrdup("MySQL");
64 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
66 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
67 mdb->cached_path = get_pool_memory(PM_FNAME);
68 mdb->cached_path_id = 0;
70 mdb->fname = get_pool_memory(PM_FNAME);
71 mdb->path = get_pool_memory(PM_FNAME);
72 mdb->esc_name = get_pool_memory(PM_FNAME);
73 mdb->esc_path = get_pool_memory(PM_FNAME);
74 mdb->esc_obj = get_pool_memory(PM_FNAME);
75 mdb->m_use_fatal_jmsg = true;
77 /* Initialize the private members. */
78 mdb->m_db_handle = NULL;
81 db_list->append(this);
84 BDB_MYSQL::~BDB_MYSQL()
89 * Initialize database data structure. In principal this should
90 * never have errors, or it is really fatal.
92 BDB *db_init_database(JCR *jcr, const char *db_driver, const char *db_name, const char *db_user,
93 const char *db_password, const char *db_address, int db_port, const char *db_socket,
94 const char *db_ssl_mode, const char *db_ssl_key,
95 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_mode = bstrdup(db_ssl_mode);
140 mdb->m_db_ssl_mode = bstrdup("preferred");
143 mdb->m_db_ssl_key = bstrdup(db_ssl_key);
146 mdb->m_db_ssl_cert = bstrdup(db_ssl_cert);
149 mdb->m_db_ssl_ca = bstrdup(db_ssl_ca);
152 mdb->m_db_ssl_capath = bstrdup(db_ssl_capath);
155 mdb->m_db_ssl_cipher = bstrdup(db_ssl_cipher);
157 mdb->m_db_port = db_port;
159 if (disable_batch_insert) {
160 mdb->m_disabled_batch_insert = true;
161 mdb->m_have_batch_insert = false;
163 mdb->m_disabled_batch_insert = false;
164 #ifdef USE_BATCH_FILE_INSERT
165 #ifdef HAVE_MYSQL_THREAD_SAFE
166 mdb->m_have_batch_insert = mysql_thread_safe();
168 mdb->m_have_batch_insert = false;
169 #endif /* HAVE_MYSQL_THREAD_SAFE */
171 mdb->m_have_batch_insert = false;
172 #endif /* USE_BATCH_FILE_INSERT */
175 mdb->m_allow_transactions = mult_db_connections;
177 /* At this time, when mult_db_connections == true, this is for
178 * specific console command such as bvfs or batch mode, and we don't
179 * want to share a batch mode or bvfs. In the future, we can change
180 * the creation function to add this parameter.
182 mdb->m_dedicated = mult_db_connections;
191 * Now actually open the database. This can generate errors,
192 * which are returned in the errmsg
194 * DO NOT close the database or delete mdb here !!!!
196 bool BDB_MYSQL::bdb_open_database(JCR *jcr)
198 BDB_MYSQL *mdb = this;
203 if (mdb->m_connected) {
208 if ((errstat=rwl_init(&mdb->m_lock)) != 0) {
210 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
211 be.bstrerror(errstat));
216 * Connect to the database
218 #ifdef xHAVE_EMBEDDED_MYSQL
219 // mysql_server_init(0, NULL, NULL);
221 mysql_init(&mdb->m_instance);
223 Dmsg0(50, "mysql_init done\n");
226 * Sets the appropriate certificate options for
227 * establishing secure connection using SSL to the database.
229 if (mdb->m_db_ssl_key) {
230 mysql_ssl_set(&(mdb->m_instance),
234 mdb->m_db_ssl_capath,
235 mdb->m_db_ssl_cipher);
239 * If connection fails, try at 5 sec intervals for 30 seconds.
241 for (int retry=0; retry < 6; retry++) {
242 mdb->m_db_handle = mysql_real_connect(
243 &(mdb->m_instance), /* db */
244 mdb->m_db_address, /* default = localhost */
245 mdb->m_db_user, /* login name */
246 mdb->m_db_password, /* password */
247 mdb->m_db_name, /* database name */
248 mdb->m_db_port, /* default port */
249 mdb->m_db_socket, /* default = socket */
250 CLIENT_FOUND_ROWS); /* flags */
253 * If no connect, try once more in case it is a timing problem
255 if (mdb->m_db_handle != NULL) {
261 mdb->m_instance.reconnect = 1; /* so connection does not timeout */
262 Dmsg0(50, "mysql_real_connect done\n");
263 Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n", mdb->m_db_user, mdb->m_db_name,
264 (mdb->m_db_password == NULL) ? "(NULL)" : mdb->m_db_password);
266 if (mdb->m_db_handle == NULL) {
267 Mmsg2(&mdb->errmsg, _("Unable to connect to MySQL server.\n"
268 "Database=%s User=%s\n"
269 "MySQL connect failed either server not running or your authorization is incorrect.\n"),
270 mdb->m_db_name, mdb->m_db_user);
271 #if MYSQL_VERSION_ID >= 40101
272 Dmsg3(50, "Error %u (%s): %s\n",
273 mysql_errno(&(mdb->m_instance)), mysql_sqlstate(&(mdb->m_instance)),
274 mysql_error(&(mdb->m_instance)));
276 Dmsg2(50, "Error %u: %s\n",
277 mysql_errno(&(mdb->m_instance)), mysql_error(&(mdb->m_instance)));
282 /* get the current cipher used for SSL connection */
283 if (mdb->m_db_ssl_key) {
285 if (mdb->m_db_ssl_cipher) {
286 free(mdb->m_db_ssl_cipher);
288 cipher = (const char *)mysql_get_ssl_cipher(&(mdb->m_instance));
290 mdb->m_db_ssl_cipher = bstrdup(cipher);
292 Dmsg1(50, "db_ssl_ciper=%s\n", (mdb->m_db_ssl_cipher == NULL) ? "(NULL)" : mdb->m_db_ssl_cipher);
295 mdb->m_connected = true;
296 if (!bdb_check_version(jcr)) {
300 Dmsg3(100, "opendb ref=%d connected=%d db=%p\n", mdb->m_ref_count, mdb->m_connected, mdb->m_db_handle);
303 * Set connection timeout to 8 days specialy for batch mode
305 sql_query("SET wait_timeout=691200");
306 sql_query("SET interactive_timeout=691200");
315 void BDB_MYSQL::bdb_close_database(JCR *jcr)
317 BDB_MYSQL *mdb = this;
319 if (mdb->m_connected) {
320 bdb_end_transaction(jcr);
324 Dmsg3(100, "closedb ref=%d connected=%d db=%p\n", mdb->m_ref_count, mdb->m_connected, mdb->m_db_handle);
325 if (mdb->m_ref_count == 0) {
326 if (mdb->m_connected) {
329 db_list->remove(mdb);
330 if (mdb->m_connected) {
331 Dmsg1(100, "close db=%p\n", mdb->m_db_handle);
332 mysql_close(&mdb->m_instance);
334 if (is_rwl_valid(&mdb->m_lock)) {
335 rwl_destroy(&mdb->m_lock);
337 free_pool_memory(mdb->errmsg);
338 free_pool_memory(mdb->cmd);
339 free_pool_memory(mdb->cached_path);
340 free_pool_memory(mdb->fname);
341 free_pool_memory(mdb->path);
342 free_pool_memory(mdb->esc_name);
343 free_pool_memory(mdb->esc_path);
344 free_pool_memory(mdb->esc_obj);
345 if (mdb->m_db_driver) {
346 free(mdb->m_db_driver);
348 if (mdb->m_db_name) {
349 free(mdb->m_db_name);
351 if (mdb->m_db_user) {
352 free(mdb->m_db_user);
354 if (mdb->m_db_password) {
355 free(mdb->m_db_password);
357 if (mdb->m_db_address) {
358 free(mdb->m_db_address);
360 if (mdb->m_db_socket) {
361 free(mdb->m_db_socket);
363 if (mdb->m_db_ssl_mode) {
364 free(mdb->m_db_ssl_mode);
366 if (mdb->m_db_ssl_key) {
367 free(mdb->m_db_ssl_key);
369 if (mdb->m_db_ssl_cert) {
370 free(mdb->m_db_ssl_cert);
372 if (mdb->m_db_ssl_ca) {
373 free(mdb->m_db_ssl_ca);
375 if (mdb->m_db_ssl_capath) {
376 free(mdb->m_db_ssl_capath);
378 if (mdb->m_db_ssl_cipher) {
379 free(mdb->m_db_ssl_cipher);
382 if (db_list->size() == 0) {
391 * This call is needed because the message channel thread
392 * opens a database on behalf of a jcr that was created in
393 * a different thread. MySQL then allocates thread specific
394 * data, which is NOT freed when the original jcr thread
395 * closes the database. Thus the msgchan must call here
396 * to cleanup any thread specific data that it created.
398 void BDB_MYSQL::bdb_thread_cleanup(void)
401 mysql_thread_end(); /* Cleanup thread specific data */
406 * Escape strings so MySQL is happy
408 * len is the length of the old string. Your new
409 * string must be long enough (max 2*old+1) to hold
410 * the escaped output.
412 void BDB_MYSQL::bdb_escape_string(JCR *jcr, char *snew, char *old, int len)
414 BDB_MYSQL *mdb = this;
415 mysql_real_escape_string(mdb->m_db_handle, snew, old, len);
419 * Escape binary object so that MySQL is happy
420 * Memory is stored in BDB struct, no need to free it
422 char *BDB_MYSQL::bdb_escape_object(JCR *jcr, char *old, int len)
424 BDB_MYSQL *mdb = this;
425 mdb->esc_obj = check_pool_memory_size(mdb->esc_obj, len*2+1);
426 mysql_real_escape_string(mdb->m_db_handle, mdb->esc_obj, old, len);
431 * Unescape binary object so that MySQL is happy
433 void BDB_MYSQL::bdb_unescape_object(JCR *jcr, char *from, int32_t expected_len,
434 POOLMEM **dest, int32_t *dest_len)
441 *dest = check_pool_memory_size(*dest, expected_len+1);
442 *dest_len = expected_len;
443 memcpy(*dest, from, expected_len);
444 (*dest)[expected_len]=0;
447 void BDB_MYSQL::bdb_start_transaction(JCR *jcr)
450 jcr->attr = get_pool_memory(PM_FNAME);
453 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
454 memset(jcr->ar, 0, sizeof(ATTR_DBR));
458 void BDB_MYSQL::bdb_end_transaction(JCR *jcr)
460 if (jcr && jcr->cached_attribute) {
461 Dmsg0(400, "Flush last cached attribute.\n");
462 if (!bdb_create_attributes_record(jcr, jcr->ar)) {
463 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), jcr->db->bdb_strerror());
465 jcr->cached_attribute = false;
470 * Submit a general SQL command (cmd), and for each row returned,
471 * the result_handler is called with the ctx.
473 bool BDB_MYSQL::bdb_sql_query(const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
479 BDB_MYSQL *mdb = this;
481 Dmsg1(500, "db_sql_query starts with %s\n", query);
485 ret = mysql_query(m_db_handle, query);
487 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror());
488 Dmsg0(500, "db_sql_query failed\n");
492 Dmsg0(500, "db_sql_query succeeded. checking handler\n");
494 if (result_handler) {
495 if ((mdb->m_result = mysql_use_result(mdb->m_db_handle)) != NULL) {
496 mdb->m_num_fields = mysql_num_fields(mdb->m_result);
499 * We *must* fetch all rows
501 while ((row = mysql_fetch_row(m_result))) {
503 /* the result handler returns 1 when it has
504 * seen all the data it wants. However, we
505 * loop to the end of the data.
507 if (result_handler(ctx, mdb->m_num_fields, row)) {
516 Dmsg0(500, "db_sql_query finished\n");
524 bool BDB_MYSQL::sql_query(const char *query, int flags)
528 BDB_MYSQL *mdb = this;
530 Dmsg1(500, "sql_query starts with '%s'\n", query);
532 * We are starting a new query. reset everything.
534 mdb->m_num_rows = -1;
535 mdb->m_row_number = -1;
536 mdb->m_field_number = -1;
539 mysql_free_result(mdb->m_result);
540 mdb->m_result = NULL;
543 ret = mysql_query(mdb->m_db_handle, query);
545 Dmsg0(500, "we have a result\n");
546 if (flags & QF_STORE_RESULT) {
547 mdb->m_result = mysql_store_result(mdb->m_db_handle);
548 if (mdb->m_result != NULL) {
549 mdb->m_num_fields = mysql_num_fields(mdb->m_result);
550 Dmsg1(500, "we have %d fields\n", mdb->m_num_fields);
551 mdb->m_num_rows = mysql_num_rows(mdb->m_result);
552 Dmsg1(500, "we have %d rows\n", mdb->m_num_rows);
554 mdb->m_num_fields = 0;
555 mdb->m_num_rows = mysql_affected_rows(mdb->m_db_handle);
556 Dmsg1(500, "we have %d rows\n", mdb->m_num_rows);
559 mdb->m_num_fields = 0;
560 mdb->m_num_rows = mysql_affected_rows(mdb->m_db_handle);
561 Dmsg1(500, "we have %d rows\n", mdb->m_num_rows);
564 Dmsg0(500, "we failed\n");
565 mdb->m_status = 1; /* failed */
571 void BDB_MYSQL::sql_free_result(void)
573 BDB_MYSQL *mdb = this;
576 mysql_free_result(mdb->m_result);
577 mdb->m_result = NULL;
581 mdb->m_fields = NULL;
583 mdb->m_num_rows = mdb->m_num_fields = 0;
587 SQL_ROW BDB_MYSQL::sql_fetch_row(void)
589 BDB_MYSQL *mdb = this;
590 if (!mdb->m_result) {
593 return mysql_fetch_row(mdb->m_result);
597 const char *BDB_MYSQL::sql_strerror(void)
599 BDB_MYSQL *mdb = this;
600 return mysql_error(mdb->m_db_handle);
603 void BDB_MYSQL::sql_data_seek(int row)
605 BDB_MYSQL *mdb = this;
606 return mysql_data_seek(mdb->m_result, row);
609 int BDB_MYSQL::sql_affected_rows(void)
611 BDB_MYSQL *mdb = this;
612 return mysql_affected_rows(mdb->m_db_handle);
615 uint64_t BDB_MYSQL::sql_insert_autokey_record(const char *query, const char *table_name)
617 BDB_MYSQL *mdb = this;
619 * First execute the insert query and then retrieve the currval.
621 if (mysql_query(mdb->m_db_handle, query) != 0) {
625 mdb->m_num_rows = mysql_affected_rows(mdb->m_db_handle);
626 if (mdb->m_num_rows != 1) {
632 return mysql_insert_id(mdb->m_db_handle);
635 SQL_FIELD *BDB_MYSQL::sql_fetch_field(void)
639 BDB_MYSQL *mdb = this;
641 if (!mdb->m_fields || mdb->m_fields_size < mdb->m_num_fields) {
644 mdb->m_fields = NULL;
646 Dmsg1(500, "allocating space for %d fields\n", mdb->m_num_fields);
647 mdb->m_fields = (SQL_FIELD *)malloc(sizeof(SQL_FIELD) * mdb->m_num_fields);
648 mdb->m_fields_size = mdb->m_num_fields;
650 for (i = 0; i < mdb->m_num_fields; i++) {
651 Dmsg1(500, "filling field %d\n", i);
652 if ((field = mysql_fetch_field(mdb->m_result)) != NULL) {
653 mdb->m_fields[i].name = field->name;
654 mdb->m_fields[i].max_length = field->max_length;
655 mdb->m_fields[i].type = field->type;
656 mdb->m_fields[i].flags = field->flags;
658 Dmsg4(500, "sql_fetch_field finds field '%s' has length='%d' type='%d' and IsNull=%d\n",
659 mdb->m_fields[i].name, mdb->m_fields[i].max_length, mdb->m_fields[i].type, mdb->m_fields[i].flags);
665 * Increment field number for the next time around
667 return &mdb->m_fields[mdb->m_field_number++];
670 bool BDB_MYSQL::sql_field_is_not_null(int field_type)
672 return IS_NOT_NULL(field_type);
675 bool BDB_MYSQL::sql_field_is_numeric(int field_type)
677 return IS_NUM(field_type);
684 bool BDB_MYSQL::sql_batch_start(JCR *jcr)
686 BDB_MYSQL *mdb = this;
690 retval = sql_query("CREATE TEMPORARY TABLE batch ("
697 "DeltaSeq integer)");
701 * Keep track of the number of changes in batch mode.
708 /* set error to something to abort operation */
713 bool BDB_MYSQL::sql_batch_end(JCR *jcr, const char *error)
715 BDB_MYSQL *mdb = this;
720 * Flush any pending inserts.
723 return sql_query(mdb->cmd);
733 bool BDB_MYSQL::sql_batch_insert(JCR *jcr, ATTR_DBR *ar)
735 BDB_MYSQL *mdb = this;
739 mdb->esc_name = check_pool_memory_size(mdb->esc_name, mdb->fnl*2+1);
740 bdb_escape_string(jcr, mdb->esc_name, mdb->fname, mdb->fnl);
742 mdb->esc_path = check_pool_memory_size(mdb->esc_path, mdb->pnl*2+1);
743 bdb_escape_string(jcr, mdb->esc_path, mdb->path, mdb->pnl);
745 if (ar->Digest == NULL || ar->Digest[0] == 0) {
752 * Try to batch up multiple inserts using multi-row inserts.
754 if (mdb->changes == 0) {
755 Mmsg(cmd, "INSERT INTO batch VALUES "
756 "(%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);
762 * We use the esc_obj for temporary storage otherwise
763 * we keep on copying data.
765 Mmsg(mdb->esc_obj, ",(%u,%s,'%s','%s','%s','%s',%u)",
766 ar->FileIndex, edit_int64(ar->JobId,ed1), mdb->esc_path,
767 mdb->esc_name, ar->attr, digest, ar->DeltaSeq);
768 pm_strcat(mdb->cmd, mdb->esc_obj);
773 * See if we need to flush the query buffer filled
774 * with multi-row inserts.
776 if ((mdb->changes % MYSQL_CHANGES_PER_BATCH_INSERT) == 0) {
777 if (!sql_query(mdb->cmd)) {
788 #endif /* HAVE_MYSQL */