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_key, const char *db_ssl_cert, const char *db_ssl_ca,
95 const char *db_ssl_capath, const char *db_ssl_cipher,
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);
137 mdb->m_db_ssl_key = bstrdup(db_ssl_key);
140 mdb->m_db_ssl_cert = bstrdup(db_ssl_cert);
143 mdb->m_db_ssl_ca = bstrdup(db_ssl_ca);
146 mdb->m_db_ssl_capath = bstrdup(db_ssl_capath);
149 mdb->m_db_ssl_cipher = bstrdup(db_ssl_cipher);
151 mdb->m_db_port = db_port;
153 if (disable_batch_insert) {
154 mdb->m_disabled_batch_insert = true;
155 mdb->m_have_batch_insert = false;
157 mdb->m_disabled_batch_insert = false;
158 #ifdef USE_BATCH_FILE_INSERT
159 #ifdef HAVE_MYSQL_THREAD_SAFE
160 mdb->m_have_batch_insert = mysql_thread_safe();
162 mdb->m_have_batch_insert = false;
163 #endif /* HAVE_MYSQL_THREAD_SAFE */
165 mdb->m_have_batch_insert = false;
166 #endif /* USE_BATCH_FILE_INSERT */
169 mdb->m_allow_transactions = mult_db_connections;
171 /* At this time, when mult_db_connections == true, this is for
172 * specific console command such as bvfs or batch mode, and we don't
173 * want to share a batch mode or bvfs. In the future, we can change
174 * the creation function to add this parameter.
176 mdb->m_dedicated = mult_db_connections;
185 * Now actually open the database. This can generate errors,
186 * which are returned in the errmsg
188 * DO NOT close the database or delete mdb here !!!!
190 bool BDB_MYSQL::bdb_open_database(JCR *jcr)
192 BDB_MYSQL *mdb = this;
197 if (mdb->m_connected) {
202 if ((errstat=rwl_init(&mdb->m_lock)) != 0) {
204 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
205 be.bstrerror(errstat));
210 * Connect to the database
212 #ifdef xHAVE_EMBEDDED_MYSQL
213 // mysql_server_init(0, NULL, NULL);
215 mysql_init(&mdb->m_instance);
217 Dmsg0(50, "mysql_init done\n");
220 * Sets the appropriate certificate options for
221 * establishing secure connection using SSL to the database.
223 if (mdb->m_db_ssl_key) {
224 mysql_ssl_set(&(mdb->m_instance),
228 mdb->m_db_ssl_capath,
229 mdb->m_db_ssl_cipher);
233 * If connection fails, try at 5 sec intervals for 30 seconds.
235 for (int retry=0; retry < 6; retry++) {
236 mdb->m_db_handle = mysql_real_connect(
237 &(mdb->m_instance), /* db */
238 mdb->m_db_address, /* default = localhost */
239 mdb->m_db_user, /* login name */
240 mdb->m_db_password, /* password */
241 mdb->m_db_name, /* database name */
242 mdb->m_db_port, /* default port */
243 mdb->m_db_socket, /* default = socket */
244 CLIENT_FOUND_ROWS); /* flags */
247 * If no connect, try once more in case it is a timing problem
249 if (mdb->m_db_handle != NULL) {
255 mdb->m_instance.reconnect = 1; /* so connection does not timeout */
256 Dmsg0(50, "mysql_real_connect done\n");
257 Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n", mdb->m_db_user, mdb->m_db_name,
258 (mdb->m_db_password == NULL) ? "(NULL)" : mdb->m_db_password);
260 if (mdb->m_db_handle == NULL) {
261 Mmsg2(&mdb->errmsg, _("Unable to connect to MySQL server.\n"
262 "Database=%s User=%s\n"
263 "MySQL connect failed either server not running or your authorization is incorrect.\n"),
264 mdb->m_db_name, mdb->m_db_user);
265 #if MYSQL_VERSION_ID >= 40101
266 Dmsg3(50, "Error %u (%s): %s\n",
267 mysql_errno(&(mdb->m_instance)), mysql_sqlstate(&(mdb->m_instance)),
268 mysql_error(&(mdb->m_instance)));
270 Dmsg2(50, "Error %u: %s\n",
271 mysql_errno(&(mdb->m_instance)), mysql_error(&(mdb->m_instance)));
276 /* get the current cipher used for SSL connection */
277 if (mdb->m_db_ssl_key) {
279 if (mdb->m_db_ssl_cipher) {
280 free(mdb->m_db_ssl_cipher);
282 cipher = (const char *)mysql_get_ssl_cipher(&(mdb->m_instance));
284 mdb->m_db_ssl_cipher = bstrdup(cipher);
286 Dmsg1(50, "db_ssl_ciper=%s\n", (mdb->m_db_ssl_cipher == NULL) ? "(NULL)" : mdb->m_db_ssl_cipher);
289 mdb->m_connected = true;
290 if (!bdb_check_version(jcr)) {
294 Dmsg3(100, "opendb ref=%d connected=%d db=%p\n", mdb->m_ref_count, mdb->m_connected, mdb->m_db_handle);
297 * Set connection timeout to 8 days specialy for batch mode
299 sql_query("SET wait_timeout=691200");
300 sql_query("SET interactive_timeout=691200");
309 void BDB_MYSQL::bdb_close_database(JCR *jcr)
311 BDB_MYSQL *mdb = this;
313 if (mdb->m_connected) {
314 bdb_end_transaction(jcr);
318 Dmsg3(100, "closedb ref=%d connected=%d db=%p\n", mdb->m_ref_count, mdb->m_connected, mdb->m_db_handle);
319 if (mdb->m_ref_count == 0) {
320 if (mdb->m_connected) {
323 db_list->remove(mdb);
324 if (mdb->m_connected) {
325 Dmsg1(100, "close db=%p\n", mdb->m_db_handle);
326 mysql_close(&mdb->m_instance);
328 if (is_rwl_valid(&mdb->m_lock)) {
329 rwl_destroy(&mdb->m_lock);
331 free_pool_memory(mdb->errmsg);
332 free_pool_memory(mdb->cmd);
333 free_pool_memory(mdb->cached_path);
334 free_pool_memory(mdb->fname);
335 free_pool_memory(mdb->path);
336 free_pool_memory(mdb->esc_name);
337 free_pool_memory(mdb->esc_path);
338 free_pool_memory(mdb->esc_obj);
339 if (mdb->m_db_driver) {
340 free(mdb->m_db_driver);
342 if (mdb->m_db_name) {
343 free(mdb->m_db_name);
345 if (mdb->m_db_user) {
346 free(mdb->m_db_user);
348 if (mdb->m_db_password) {
349 free(mdb->m_db_password);
351 if (mdb->m_db_address) {
352 free(mdb->m_db_address);
354 if (mdb->m_db_socket) {
355 free(mdb->m_db_socket);
357 if (mdb->m_db_ssl_key) {
358 free(mdb->m_db_ssl_key);
360 if (mdb->m_db_ssl_cert) {
361 free(mdb->m_db_ssl_cert);
363 if (mdb->m_db_ssl_ca) {
364 free(mdb->m_db_ssl_ca);
366 if (mdb->m_db_ssl_capath) {
367 free(mdb->m_db_ssl_capath);
369 if (mdb->m_db_ssl_cipher) {
370 free(mdb->m_db_ssl_cipher);
373 if (db_list->size() == 0) {
382 * This call is needed because the message channel thread
383 * opens a database on behalf of a jcr that was created in
384 * a different thread. MySQL then allocates thread specific
385 * data, which is NOT freed when the original jcr thread
386 * closes the database. Thus the msgchan must call here
387 * to cleanup any thread specific data that it created.
389 void BDB_MYSQL::bdb_thread_cleanup(void)
392 mysql_thread_end(); /* Cleanup thread specific data */
397 * Escape strings so MySQL is happy
399 * len is the length of the old string. Your new
400 * string must be long enough (max 2*old+1) to hold
401 * the escaped output.
403 void BDB_MYSQL::bdb_escape_string(JCR *jcr, char *snew, char *old, int len)
405 BDB_MYSQL *mdb = this;
406 mysql_real_escape_string(mdb->m_db_handle, snew, old, len);
410 * Escape binary object so that MySQL is happy
411 * Memory is stored in BDB struct, no need to free it
413 char *BDB_MYSQL::bdb_escape_object(JCR *jcr, char *old, int len)
415 BDB_MYSQL *mdb = this;
416 mdb->esc_obj = check_pool_memory_size(mdb->esc_obj, len*2+1);
417 mysql_real_escape_string(mdb->m_db_handle, mdb->esc_obj, old, len);
422 * Unescape binary object so that MySQL is happy
424 void BDB_MYSQL::bdb_unescape_object(JCR *jcr, char *from, int32_t expected_len,
425 POOLMEM **dest, int32_t *dest_len)
432 *dest = check_pool_memory_size(*dest, expected_len+1);
433 *dest_len = expected_len;
434 memcpy(*dest, from, expected_len);
435 (*dest)[expected_len]=0;
438 void BDB_MYSQL::bdb_start_transaction(JCR *jcr)
441 jcr->attr = get_pool_memory(PM_FNAME);
444 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
445 memset(jcr->ar, 0, 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 */