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)
244 db_end_transaction(jcr);
247 Dmsg3(100, "closedb ref=%d connected=%d db=%p\n", m_ref_count, m_connected, m_db_handle);
248 if (m_ref_count == 0) {
250 db_list->remove(this);
252 Dmsg1(100, "close db=%p\n", m_db_handle);
253 mysql_close(&m_instance);
255 #ifdef xHAVE_EMBEDDED_MYSQL
256 // mysql_server_end();
259 rwl_destroy(&m_lock);
260 free_pool_memory(errmsg);
261 free_pool_memory(cmd);
262 free_pool_memory(cached_path);
263 free_pool_memory(fname);
264 free_pool_memory(path);
265 free_pool_memory(esc_name);
266 free_pool_memory(esc_path);
267 free_pool_memory(esc_obj);
287 if (db_list->size() == 0) {
296 * This call is needed because the message channel thread
297 * opens a database on behalf of a jcr that was created in
298 * a different thread. MySQL then allocates thread specific
299 * data, which is NOT freed when the original jcr thread
300 * closes the database. Thus the msgchan must call here
301 * to cleanup any thread specific data that it created.
303 void B_DB_MYSQL::db_thread_cleanup(void)
311 * Escape strings so that MySQL is happy
313 * NOTE! len is the length of the old string. Your new
314 * string must be long enough (max 2*old+1) to hold
315 * the escaped output.
317 void B_DB_MYSQL::db_escape_string(JCR *jcr, char *snew, char *old, int len)
319 mysql_real_escape_string(m_db_handle, snew, old, len);
323 * Escape binary object so that MySQL is happy
324 * Memory is stored in B_DB struct, no need to free it
326 char *B_DB_MYSQL::db_escape_object(JCR *jcr, char *old, int len)
328 esc_obj = check_pool_memory_size(esc_obj, len*2+1);
329 mysql_real_escape_string(m_db_handle, esc_obj, old, len);
334 * Unescape binary object so that MySQL is happy
336 void B_DB_MYSQL::db_unescape_object(JCR *jcr, char *from, int32_t expected_len,
337 POOLMEM **dest, int32_t *dest_len)
344 *dest = check_pool_memory_size(*dest, expected_len+1);
345 *dest_len = expected_len;
346 memcpy(*dest, from, expected_len);
347 (*dest)[expected_len]=0;
350 void B_DB_MYSQL::db_start_transaction(JCR *jcr)
353 jcr->attr = get_pool_memory(PM_FNAME);
356 jcr->ar = (ATTR_DBR *)malloc(sizeof(ATTR_DBR));
360 void B_DB_MYSQL::db_end_transaction(JCR *jcr)
362 if (jcr && jcr->cached_attribute) {
363 Dmsg0(400, "Flush last cached attribute.\n");
364 if (!db_create_attributes_record(jcr, this, jcr->ar)) {
365 Jmsg1(jcr, M_FATAL, 0, _("Attribute create error. %s"), db_strerror(jcr->db));
367 jcr->cached_attribute = false;
372 * Submit a general SQL command (cmd), and for each row returned,
373 * the result_handler is called with the ctx.
375 bool B_DB_MYSQL::db_sql_query(const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
382 Dmsg1(500, "db_sql_query starts with %s\n", query);
385 ret = mysql_query(m_db_handle, query);
387 Mmsg(errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror());
388 Dmsg0(500, "db_sql_query failed\n");
392 Dmsg0(500, "db_sql_query succeeded. checking handler\n");
394 if (result_handler != NULL) {
395 if ((m_result = mysql_use_result(m_db_handle)) != NULL) {
396 m_num_fields = mysql_num_fields(m_result);
399 * We *must* fetch all rows
401 while ((row = mysql_fetch_row(m_result)) != NULL) {
403 /* the result handler returns 1 when it has
404 * seen all the data it wants. However, we
405 * loop to the end of the data.
407 if (result_handler(ctx, m_num_fields, row)) {
416 Dmsg0(500, "db_sql_query finished\n");
424 bool B_DB_MYSQL::sql_query(const char *query, int flags)
429 Dmsg1(500, "sql_query starts with '%s'\n", query);
431 * We are starting a new query. reset everything.
438 mysql_free_result(m_result);
442 ret = mysql_query(m_db_handle, query);
444 Dmsg0(500, "we have a result\n");
445 if (flags & QF_STORE_RESULT) {
446 m_result = mysql_store_result(m_db_handle);
447 if (m_result != NULL) {
448 m_num_fields = mysql_num_fields(m_result);
449 Dmsg1(500, "we have %d fields\n", m_num_fields);
450 m_num_rows = mysql_num_rows(m_result);
451 Dmsg1(500, "we have %d rows\n", m_num_rows);
454 m_num_rows = mysql_affected_rows(m_db_handle);
455 Dmsg1(500, "we have %d rows\n", m_num_rows);
459 m_num_rows = mysql_affected_rows(m_db_handle);
460 Dmsg1(500, "we have %d rows\n", m_num_rows);
463 Dmsg0(500, "we failed\n");
464 m_status = 1; /* failed */
470 void B_DB_MYSQL::sql_free_result(void)
474 mysql_free_result(m_result);
481 m_num_rows = m_num_fields = 0;
485 SQL_ROW B_DB_MYSQL::sql_fetch_row(void)
490 return mysql_fetch_row(m_result);
494 const char *B_DB_MYSQL::sql_strerror(void)
496 return mysql_error(m_db_handle);
499 void B_DB_MYSQL::sql_data_seek(int row)
501 return mysql_data_seek(m_result, row);
504 int B_DB_MYSQL::sql_affected_rows(void)
506 return mysql_affected_rows(m_db_handle);
509 uint64_t B_DB_MYSQL::sql_insert_autokey_record(const char *query, const char *table_name)
512 * First execute the insert query and then retrieve the currval.
514 if (mysql_query(m_db_handle, query) != 0) {
518 m_num_rows = mysql_affected_rows(m_db_handle);
519 if (m_num_rows != 1) {
525 return mysql_insert_id(m_db_handle);
528 SQL_FIELD *B_DB_MYSQL::sql_fetch_field(void)
533 if (!m_fields || m_fields_size < m_num_fields) {
538 Dmsg1(500, "allocating space for %d fields\n", m_num_fields);
539 m_fields = (SQL_FIELD *)malloc(sizeof(SQL_FIELD) * m_num_fields);
540 m_fields_size = m_num_fields;
542 for (i = 0; i < m_num_fields; i++) {
543 Dmsg1(500, "filling field %d\n", i);
544 if ((field = mysql_fetch_field(m_result)) != NULL) {
545 m_fields[i].name = field->name;
546 m_fields[i].max_length = field->max_length;
547 m_fields[i].type = field->type;
548 m_fields[i].flags = field->flags;
550 Dmsg4(500, "sql_fetch_field finds field '%s' has length='%d' type='%d' and IsNull=%d\n",
551 m_fields[i].name, m_fields[i].max_length, m_fields[i].type, m_fields[i].flags);
557 * Increment field number for the next time around
559 return &m_fields[m_field_number++];
562 bool B_DB_MYSQL::sql_field_is_not_null(int field_type)
564 return IS_NOT_NULL(field_type);
567 bool B_DB_MYSQL::sql_field_is_numeric(int field_type)
569 return IS_NUM(field_type);
576 bool B_DB_MYSQL::sql_batch_start(JCR *jcr)
581 retval = sql_query("CREATE TEMPORARY TABLE batch ("
588 "DeltaSeq integer)");
594 /* set error to something to abort operation */
599 bool B_DB_MYSQL::sql_batch_end(JCR *jcr, const char *error)
610 bool B_DB_MYSQL::sql_batch_insert(JCR *jcr, ATTR_DBR *ar)
615 esc_name = check_pool_memory_size(esc_name, fnl*2+1);
616 db_escape_string(jcr, esc_name, fname, fnl);
618 esc_path = check_pool_memory_size(esc_path, pnl*2+1);
619 db_escape_string(jcr, esc_path, path, pnl);
621 if (ar->Digest == NULL || ar->Digest[0] == 0) {
627 Mmsg(cmd, "INSERT INTO batch VALUES "
628 "(%u,%s,'%s','%s','%s','%s',%u)",
629 ar->FileIndex, edit_int64(ar->JobId,ed1), esc_path,
630 esc_name, ar->attr, digest, ar->DeltaSeq);
632 return sql_query(cmd);
636 * Initialize database data structure. In principal this should
637 * never have errors, or it is really fatal.
639 B_DB *db_init_database(JCR *jcr, const char *db_driver, const char *db_name, const char *db_user,
640 const char *db_password, const char *db_address, int db_port, const char *db_socket,
641 bool mult_db_connections, bool disable_batch_insert)
643 B_DB_MYSQL *mdb = NULL;
646 Jmsg(jcr, M_FATAL, 0, _("A user name for MySQL must be supplied.\n"));
649 P(mutex); /* lock DB queue */
652 * Look to see if DB already open
654 if (db_list && !mult_db_connections) {
655 foreach_dlist(mdb, db_list) {
656 if (mdb->db_match_database(db_driver, db_name, db_address, db_port)) {
657 Dmsg1(100, "DB REopen %s\n", db_name);
658 mdb->increment_refcount();
663 Dmsg0(100, "db_init_database first time\n");
664 mdb = New(B_DB_MYSQL(jcr, db_driver, db_name, db_user, db_password, db_address,
665 db_port, db_socket, mult_db_connections, disable_batch_insert));
672 #endif /* HAVE_MYSQL */