2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2011 Free Software Foundation Europe e.V.
6 The main author of Bacula is Kern Sibbald, with contributions from
7 many others, a complete list can be found in the file AUTHORS.
8 This program is Free Software; you can redistribute it and/or
9 modify it under the terms of version three of the GNU Affero General Public
10 License as published by the Free Software Foundation and included
13 This program is distributed in the hope that it will be useful, but
14 WITHOUT ANY WARRANTY; without even the implied warranty of
15 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 General Public License for more details.
18 You should have received a copy of the GNU Affero General Public License
19 along with this program; if not, write to the Free Software
20 Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
23 Bacula® is a registered trademark of Kern Sibbald.
24 The licensor of Bacula is the Free Software Foundation Europe
25 (FSFE), Fiduciary Program, Sumatrastrasse 25, 8006 Zürich,
26 Switzerland, email:ftf@fsfeurope.org.
29 * Bacula Catalog Database routines specific to MySQL
30 * These are MySQL specific routines -- hopefully all
31 * other files are generic.
33 * Kern Sibbald, March 2000
35 * Major rewrite by Marco van Wieringen, January 2010 for catalog refactoring.
45 #include <bdb_mysql.h>
47 /* -----------------------------------------------------------------------
49 * MySQL dependent defines and subroutines
51 * -----------------------------------------------------------------------
55 * List of open databases
57 static dlist *db_list = NULL;
59 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
61 B_DB_MYSQL::B_DB_MYSQL(JCR *jcr,
62 const char *db_driver,
65 const char *db_password,
66 const char *db_address,
68 const char *db_socket,
69 bool mult_db_connections,
70 bool disable_batch_insert)
73 * Initialize the parent class members.
75 m_db_interface_type = SQL_INTERFACE_TYPE_MYSQL;
76 m_db_type = SQL_TYPE_MYSQL;
77 m_db_driver = bstrdup("MySQL");
78 m_db_name = bstrdup(db_name);
79 m_db_user = bstrdup(db_user);
81 m_db_password = bstrdup(db_password);
84 m_db_address = bstrdup(db_address);
87 m_db_socket = bstrdup(db_socket);
91 if (disable_batch_insert) {
92 m_disabled_batch_insert = true;
93 m_have_batch_insert = false;
95 m_disabled_batch_insert = false;
96 #if defined(USE_BATCH_FILE_INSERT)
97 # if defined(HAVE_MYSQL_THREAD_SAFE)
98 m_have_batch_insert = mysql_thread_safe();
100 m_have_batch_insert = false;
101 # endif /* HAVE_MYSQL_THREAD_SAFE */
103 m_have_batch_insert = false;
104 #endif /* USE_BATCH_FILE_INSERT */
106 errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
108 cmd = get_pool_memory(PM_EMSG); /* get command buffer */
109 cached_path = get_pool_memory(PM_FNAME);
112 fname = get_pool_memory(PM_FNAME);
113 path = get_pool_memory(PM_FNAME);
114 esc_name = get_pool_memory(PM_FNAME);
115 esc_path = get_pool_memory(PM_FNAME);
116 esc_obj = get_pool_memory(PM_FNAME);
117 m_allow_transactions = mult_db_connections;
119 /* At this time, when mult_db_connections == true, this is for
120 * specific console command such as bvfs or batch mode, and we don't
121 * want to share a batch mode or bvfs. In the future, we can change
122 * the creation function to add this parameter.
124 m_dedicated = mult_db_connections;
127 * Initialize the private members.
133 * Put the db in the list.
135 if (db_list == NULL) {
136 db_list = New(dlist(this, &this->m_link));
138 db_list->append(this);
141 B_DB_MYSQL::~B_DB_MYSQL()
146 * Now actually open the database. This can generate errors,
147 * which are returned in the errmsg
149 * DO NOT close the database or delete mdb here !!!!
151 bool B_DB_MYSQL::db_open_database(JCR *jcr)
162 if ((errstat=rwl_init(&m_lock)) != 0) {
164 Mmsg1(&errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
165 be.bstrerror(errstat));
170 * Connect to the database
172 #ifdef xHAVE_EMBEDDED_MYSQL
173 // mysql_server_init(0, NULL, NULL);
175 mysql_init(&m_instance);
177 Dmsg0(50, "mysql_init done\n");
179 * If connection fails, try at 5 sec intervals for 30 seconds.
181 for (int retry=0; retry < 6; retry++) {
182 m_db_handle = mysql_real_connect(
183 &(m_instance), /* db */
184 m_db_address, /* default = localhost */
185 m_db_user, /* login name */
186 m_db_password, /* password */
187 m_db_name, /* database name */
188 m_db_port, /* default port */
189 m_db_socket, /* default = socket */
190 CLIENT_FOUND_ROWS); /* flags */
193 * If no connect, try once more in case it is a timing problem
195 if (m_db_handle != NULL) {
201 m_instance.reconnect = 1; /* so connection does not timeout */
202 Dmsg0(50, "mysql_real_connect done\n");
203 Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n", m_db_user, m_db_name,
204 (m_db_password == NULL) ? "(NULL)" : m_db_password);
206 if (m_db_handle == NULL) {
207 Mmsg2(&errmsg, _("Unable to connect to MySQL server.\n"
208 "Database=%s User=%s\n"
209 "MySQL connect failed either server not running or your authorization is incorrect.\n"),
210 m_db_name, m_db_user);
211 #if MYSQL_VERSION_ID >= 40101
212 Dmsg3(50, "Error %u (%s): %s\n",
213 mysql_errno(&(m_instance)), mysql_sqlstate(&(m_instance)),
214 mysql_error(&(m_instance)));
216 Dmsg2(50, "Error %u: %s\n",
217 mysql_errno(&(m_instance)), mysql_error(&(m_instance)));
223 if (!check_tables_version(jcr, this)) {
227 Dmsg3(100, "opendb ref=%d connected=%d db=%p\n", m_ref_count, m_connected, m_db_handle);
230 * Set connection timeout to 8 days specialy for batch mode
232 sql_query("SET wait_timeout=691200");
233 sql_query("SET interactive_timeout=691200");
242 void B_DB_MYSQL::db_close_database(JCR *jcr)
245 db_end_transaction(jcr);
249 Dmsg3(100, "closedb ref=%d connected=%d db=%p\n", m_ref_count, m_connected, m_db_handle);
250 if (m_ref_count == 0) {
252 db_list->remove(this);
254 Dmsg1(100, "close db=%p\n", m_db_handle);
255 mysql_close(&m_instance);
257 #ifdef xHAVE_EMBEDDED_MYSQL
258 // mysql_server_end();
261 if (rwl_is_init(&m_lock)) {
262 rwl_destroy(&m_lock);
264 free_pool_memory(errmsg);
265 free_pool_memory(cmd);
266 free_pool_memory(cached_path);
267 free_pool_memory(fname);
268 free_pool_memory(path);
269 free_pool_memory(esc_name);
270 free_pool_memory(esc_path);
271 free_pool_memory(esc_obj);
291 if (db_list->size() == 0) {
300 * This call is needed because the message channel thread
301 * opens a database on behalf of a jcr that was created in
302 * a different thread. MySQL then allocates thread specific
303 * data, which is NOT freed when the original jcr thread
304 * closes the database. Thus the msgchan must call here
305 * to cleanup any thread specific data that it created.
307 void B_DB_MYSQL::db_thread_cleanup(void)
315 * Escape strings so that MySQL is happy
317 * NOTE! len is the length of the old string. Your new
318 * string must be long enough (max 2*old+1) to hold
319 * the escaped output.
321 void B_DB_MYSQL::db_escape_string(JCR *jcr, char *snew, char *old, int len)
323 mysql_real_escape_string(m_db_handle, snew, old, len);
327 * Escape binary object so that MySQL is happy
328 * Memory is stored in B_DB struct, no need to free it
330 char *B_DB_MYSQL::db_escape_object(JCR *jcr, char *old, int len)
332 esc_obj = check_pool_memory_size(esc_obj, len*2+1);
333 mysql_real_escape_string(m_db_handle, esc_obj, old, len);
338 * Unescape binary object so that MySQL is happy
340 void B_DB_MYSQL::db_unescape_object(JCR *jcr, char *from, int32_t expected_len,
341 POOLMEM **dest, int32_t *dest_len)
348 *dest = check_pool_memory_size(*dest, expected_len+1);
349 *dest_len = expected_len;
350 memcpy(*dest, from, expected_len);
351 (*dest)[expected_len]=0;
354 void B_DB_MYSQL::db_start_transaction(JCR *jcr)
357 jcr->attr = get_pool_memory(PM_FNAME);
360 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
364 void B_DB_MYSQL::db_end_transaction(JCR *jcr)
366 if (jcr && jcr->cached_attribute) {
367 Dmsg0(400, "Flush last cached attribute.\n");
368 if (!db_create_attributes_record(jcr, this, jcr->ar)) {
369 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), db_strerror(jcr->db));
371 jcr->cached_attribute = false;
376 * Submit a general SQL command (cmd), and for each row returned,
377 * the result_handler is called with the ctx.
379 bool B_DB_MYSQL::db_sql_query(const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
386 Dmsg1(500, "db_sql_query starts with %s\n", query);
389 ret = mysql_query(m_db_handle, query);
391 Mmsg(errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror());
392 Dmsg0(500, "db_sql_query failed\n");
396 Dmsg0(500, "db_sql_query succeeded. checking handler\n");
398 if (result_handler != NULL) {
399 if ((m_result = mysql_use_result(m_db_handle)) != NULL) {
400 m_num_fields = mysql_num_fields(m_result);
403 * We *must* fetch all rows
405 while ((row = mysql_fetch_row(m_result)) != NULL) {
407 /* the result handler returns 1 when it has
408 * seen all the data it wants. However, we
409 * loop to the end of the data.
411 if (result_handler(ctx, m_num_fields, row)) {
420 Dmsg0(500, "db_sql_query finished\n");
428 bool B_DB_MYSQL::sql_query(const char *query, int flags)
433 Dmsg1(500, "sql_query starts with '%s'\n", query);
435 * We are starting a new query. reset everything.
442 mysql_free_result(m_result);
446 ret = mysql_query(m_db_handle, query);
448 Dmsg0(500, "we have a result\n");
449 if (flags & QF_STORE_RESULT) {
450 m_result = mysql_store_result(m_db_handle);
451 if (m_result != NULL) {
452 m_num_fields = mysql_num_fields(m_result);
453 Dmsg1(500, "we have %d fields\n", m_num_fields);
454 m_num_rows = mysql_num_rows(m_result);
455 Dmsg1(500, "we have %d rows\n", m_num_rows);
458 m_num_rows = mysql_affected_rows(m_db_handle);
459 Dmsg1(500, "we have %d rows\n", m_num_rows);
463 m_num_rows = mysql_affected_rows(m_db_handle);
464 Dmsg1(500, "we have %d rows\n", m_num_rows);
467 Dmsg0(500, "we failed\n");
468 m_status = 1; /* failed */
474 void B_DB_MYSQL::sql_free_result(void)
478 mysql_free_result(m_result);
485 m_num_rows = m_num_fields = 0;
489 SQL_ROW B_DB_MYSQL::sql_fetch_row(void)
494 return mysql_fetch_row(m_result);
498 const char *B_DB_MYSQL::sql_strerror(void)
500 return mysql_error(m_db_handle);
503 void B_DB_MYSQL::sql_data_seek(int row)
505 return mysql_data_seek(m_result, row);
508 int B_DB_MYSQL::sql_affected_rows(void)
510 return mysql_affected_rows(m_db_handle);
513 uint64_t B_DB_MYSQL::sql_insert_autokey_record(const char *query, const char *table_name)
516 * First execute the insert query and then retrieve the currval.
518 if (mysql_query(m_db_handle, query) != 0) {
522 m_num_rows = mysql_affected_rows(m_db_handle);
523 if (m_num_rows != 1) {
529 return mysql_insert_id(m_db_handle);
532 SQL_FIELD *B_DB_MYSQL::sql_fetch_field(void)
537 if (!m_fields || m_fields_size < m_num_fields) {
542 Dmsg1(500, "allocating space for %d fields\n", m_num_fields);
543 m_fields = (SQL_FIELD *)malloc(sizeof(SQL_FIELD) * m_num_fields);
544 m_fields_size = m_num_fields;
546 for (i = 0; i < m_num_fields; i++) {
547 Dmsg1(500, "filling field %d\n", i);
548 if ((field = mysql_fetch_field(m_result)) != NULL) {
549 m_fields[i].name = field->name;
550 m_fields[i].max_length = field->max_length;
551 m_fields[i].type = field->type;
552 m_fields[i].flags = field->flags;
554 Dmsg4(500, "sql_fetch_field finds field '%s' has length='%d' type='%d' and IsNull=%d\n",
555 m_fields[i].name, m_fields[i].max_length, m_fields[i].type, m_fields[i].flags);
561 * Increment field number for the next time around
563 return &m_fields[m_field_number++];
566 bool B_DB_MYSQL::sql_field_is_not_null(int field_type)
568 return IS_NOT_NULL(field_type);
571 bool B_DB_MYSQL::sql_field_is_numeric(int field_type)
573 return IS_NUM(field_type);
580 bool B_DB_MYSQL::sql_batch_start(JCR *jcr)
585 retval = sql_query("CREATE TEMPORARY TABLE batch ("
592 "DeltaSeq integer)");
596 * Keep track of the number of changes in batch mode.
603 /* set error to something to abort operation */
608 bool B_DB_MYSQL::sql_batch_end(JCR *jcr, const char *error)
613 * Flush any pending inserts.
616 return sql_query(cmd);
626 bool B_DB_MYSQL::sql_batch_insert(JCR *jcr, ATTR_DBR *ar)
631 esc_name = check_pool_memory_size(esc_name, fnl*2+1);
632 db_escape_string(jcr, esc_name, fname, fnl);
634 esc_path = check_pool_memory_size(esc_path, pnl*2+1);
635 db_escape_string(jcr, esc_path, path, pnl);
637 if (ar->Digest == NULL || ar->Digest[0] == 0) {
644 * Try to batch up multiple inserts using multi-row inserts.
647 Mmsg(cmd, "INSERT INTO batch VALUES "
648 "(%u,%s,'%s','%s','%s','%s',%u)",
649 ar->FileIndex, edit_int64(ar->JobId,ed1), esc_path,
650 esc_name, ar->attr, digest, ar->DeltaSeq);
654 * We use the esc_obj for temporary storage otherwise
655 * we keep on copying data.
657 Mmsg(esc_obj, ",(%u,%s,'%s','%s','%s','%s',%u)",
658 ar->FileIndex, edit_int64(ar->JobId,ed1), esc_path,
659 esc_name, ar->attr, digest, ar->DeltaSeq);
660 pm_strcat(cmd, esc_obj);
665 * See if we need to flush the query buffer filled
666 * with multi-row inserts.
668 if ((changes % MYSQL_CHANGES_PER_BATCH_INSERT) == 0) {
669 if (!sql_query(cmd)) {
680 * Initialize database data structure. In principal this should
681 * never have errors, or it is really fatal.
683 B_DB *db_init_database(JCR *jcr, const char *db_driver, const char *db_name, const char *db_user,
684 const char *db_password, const char *db_address, int db_port, const char *db_socket,
685 bool mult_db_connections, bool disable_batch_insert)
687 B_DB_MYSQL *mdb = NULL;
690 Jmsg(jcr, M_FATAL, 0, _("A user name for MySQL must be supplied.\n"));
693 P(mutex); /* lock DB queue */
696 * Look to see if DB already open
698 if (db_list && !mult_db_connections) {
699 foreach_dlist(mdb, db_list) {
700 if (mdb->db_match_database(db_driver, db_name, db_address, db_port)) {
701 Dmsg1(100, "DB REopen %s\n", db_name);
702 mdb->increment_refcount();
707 Dmsg0(100, "db_init_database first time\n");
708 mdb = New(B_DB_MYSQL(jcr, db_driver, db_name, db_user, db_password, db_address,
709 db_port, db_socket, mult_db_connections, disable_batch_insert));
716 #endif /* HAVE_MYSQL */