2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2010 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 two of the GNU 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 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
38 /* The following is necessary so that we do not include
39 * the dummy external definition of DB.
41 #define __SQL_C /* indicate that this is sql.c */
48 /* -----------------------------------------------------------------------
50 * MySQL dependent defines and subroutines
52 * -----------------------------------------------------------------------
55 /* List of open databases */
56 static BQUEUE db_list = {&db_list, &db_list};
58 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
61 * Retrieve database type
70 * Initialize database data structure. In principal this should
71 * never have errors, or it is really fatal.
74 db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char *db_password,
75 const char *db_address, int db_port, const char *db_socket,
76 int mult_db_connections)
81 Jmsg(jcr, M_FATAL, 0, _("A user name for MySQL must be supplied.\n"));
84 P(mutex); /* lock DB queue */
85 /* Look to see if DB already open */
86 if (!mult_db_connections) {
87 for (mdb=NULL; (mdb=(B_DB *)qnext(&db_list, &mdb->bq)); ) {
88 if (bstrcmp(mdb->db_name, db_name) &&
89 bstrcmp(mdb->db_address, db_address) &&
90 mdb->db_port == db_port) {
91 Dmsg2(100, "DB REopen %d %s\n", mdb->ref_count, db_name);
94 Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", mdb->ref_count,
95 mdb->connected, mdb->db);
96 return mdb; /* already open */
100 Dmsg0(100, "db_open first time\n");
101 mdb = (B_DB *)malloc(sizeof(B_DB));
102 memset(mdb, 0, sizeof(B_DB));
103 mdb->db_name = bstrdup(db_name);
104 mdb->db_user = bstrdup(db_user);
106 mdb->db_password = bstrdup(db_password);
109 mdb->db_address = bstrdup(db_address);
112 mdb->db_socket = bstrdup(db_socket);
114 mdb->db_port = db_port;
115 mdb->have_insert_id = true;
116 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
118 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
119 mdb->cached_path = get_pool_memory(PM_FNAME);
120 mdb->cached_path_id = 0;
122 mdb->fname = get_pool_memory(PM_FNAME);
123 mdb->path = get_pool_memory(PM_FNAME);
124 mdb->esc_name = get_pool_memory(PM_FNAME);
125 mdb->esc_path = get_pool_memory(PM_FNAME);
126 mdb->allow_transactions = mult_db_connections;
127 qinsert(&db_list, &mdb->bq); /* put db in list */
128 Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", mdb->ref_count,
129 mdb->connected, mdb->db);
135 * Now actually open the database. This can generate errors,
136 * which are returned in the errmsg
138 * DO NOT close the database or free(mdb) here !!!!
141 db_open_database(JCR *jcr, B_DB *mdb)
146 if (mdb->connected) {
151 if ((errstat=rwl_init(&mdb->lock)) != 0) {
153 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
154 be.bstrerror(errstat));
159 /* connect to the database */
160 #ifdef xHAVE_EMBEDDED_MYSQL
161 // mysql_server_init(0, NULL, NULL);
163 mysql_init(&mdb->mysql);
165 Dmsg0(50, "mysql_init done\n");
166 /* If connection fails, try at 5 sec intervals for 30 seconds. */
167 for (int retry=0; retry < 6; retry++) {
168 mdb->db = mysql_real_connect(
169 &(mdb->mysql), /* db */
170 mdb->db_address, /* default = localhost */
171 mdb->db_user, /* login name */
172 mdb->db_password, /* password */
173 mdb->db_name, /* database name */
174 mdb->db_port, /* default port */
175 mdb->db_socket, /* default = socket */
176 CLIENT_FOUND_ROWS); /* flags */
178 /* If no connect, try once more in case it is a timing problem */
179 if (mdb->db != NULL) {
185 mdb->mysql.reconnect = 1; /* so connection does not timeout */
186 Dmsg0(50, "mysql_real_connect done\n");
187 Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n", mdb->db_user, mdb->db_name,
188 mdb->db_password==NULL?"(NULL)":mdb->db_password);
190 if (mdb->db == NULL) {
191 Mmsg2(&mdb->errmsg, _("Unable to connect to MySQL server.\n"
192 "Database=%s User=%s\n"
193 "MySQL connect failed either server not running or your authorization is incorrect.\n"),
194 mdb->db_name, mdb->db_user);
195 #if MYSQL_VERSION_ID >= 40101
196 Dmsg3(50, "Error %u (%s): %s\n",
197 mysql_errno(&(mdb->mysql)), mysql_sqlstate(&(mdb->mysql)),
198 mysql_error(&(mdb->mysql)));
200 Dmsg2(50, "Error %u: %s\n",
201 mysql_errno(&(mdb->mysql)), mysql_error(&(mdb->mysql)));
207 mdb->connected = true;
208 if (!check_tables_version(jcr, mdb)) {
213 Dmsg3(100, "opendb ref=%d connected=%d db=%p\n", mdb->ref_count,
214 mdb->connected, mdb->db);
216 /* Set connection timeout to 8 days specialy for batch mode */
217 sql_query(mdb, "SET wait_timeout=691200");
218 sql_query(mdb, "SET interactive_timeout=691200");
225 db_close_database(JCR *jcr, B_DB *mdb)
230 db_end_transaction(jcr, mdb);
232 sql_free_result(mdb);
234 Dmsg3(100, "closedb ref=%d connected=%d db=%p\n", mdb->ref_count,
235 mdb->connected, mdb->db);
236 if (mdb->ref_count == 0) {
238 if (mdb->connected) {
239 Dmsg1(100, "close db=%p\n", mdb->db);
240 mysql_close(&mdb->mysql);
242 #ifdef xHAVE_EMBEDDED_MYSQL
243 // mysql_server_end();
246 rwl_destroy(&mdb->lock);
247 free_pool_memory(mdb->errmsg);
248 free_pool_memory(mdb->cmd);
249 free_pool_memory(mdb->cached_path);
250 free_pool_memory(mdb->fname);
251 free_pool_memory(mdb->path);
252 free_pool_memory(mdb->esc_name);
253 free_pool_memory(mdb->esc_path);
260 if (mdb->db_password) {
261 free(mdb->db_password);
263 if (mdb->db_address) {
264 free(mdb->db_address);
266 if (mdb->db_socket) {
267 free(mdb->db_socket);
274 void db_check_backend_thread_safe()
276 #ifdef HAVE_BATCH_FILE_INSERT
277 if (!mysql_thread_safe()) {
278 Emsg0(M_ABORT, 0, _("MySQL client library must be thread-safe "
279 "when using BatchMode.\n"));
285 * This call is needed because the message channel thread
286 * opens a database on behalf of a jcr that was created in
287 * a different thread. MySQL then allocates thread specific
288 * data, which is NOT freed when the original jcr thread
289 * closes the database. Thus the msgchan must call here
290 * to cleanup any thread specific data that it created.
292 void db_thread_cleanup()
300 * Return the next unique index (auto-increment) for
301 * the given table. Return NULL on error.
303 * For MySQL, NULL causes the auto-increment value
306 int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index)
308 strcpy(index, "NULL");
314 * Escape strings so that MySQL is happy
316 * NOTE! len is the length of the old string. Your new
317 * string must be long enough (max 2*old+1) to hold
318 * the escaped output.
321 db_escape_string(JCR *jcr, B_DB *mdb, char *snew, char *old, int len)
323 mysql_real_escape_string(mdb->db, snew, old, len);
327 * Submit a general SQL command (cmd), and for each row returned,
328 * the sqlite_handler is called with the ctx.
330 bool db_sql_query(B_DB *mdb, const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
336 if (sql_query(mdb, query) != 0) {
337 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb));
341 if (result_handler != NULL) {
342 if ((mdb->result = sql_use_result(mdb)) != NULL) {
343 int num_fields = sql_num_fields(mdb);
345 /* We *must* fetch all rows */
346 while ((row = sql_fetch_row(mdb)) != NULL) {
348 /* the result handler returns 1 when it has
349 * seen all the data it wants. However, we
350 * loop to the end of the data.
352 if (result_handler(ctx, num_fields, row)) {
358 sql_free_result(mdb);
366 void my_mysql_free_result(B_DB *mdb)
370 mysql_free_result(mdb->result);
376 #ifdef HAVE_BATCH_FILE_INSERT
377 const char *my_mysql_batch_lock_path_query =
378 "LOCK TABLES Path write, batch write, Path as p write";
381 const char *my_mysql_batch_lock_filename_query =
382 "LOCK TABLES Filename write, batch write, Filename as f write";
384 const char *my_mysql_batch_unlock_tables_query = "UNLOCK TABLES";
386 const char *my_mysql_batch_fill_path_query =
387 "INSERT INTO Path (Path) "
388 "SELECT a.Path FROM "
389 "(SELECT DISTINCT Path FROM batch) AS a WHERE NOT EXISTS "
390 "(SELECT Path FROM Path AS p WHERE p.Path = a.Path)";
392 const char *my_mysql_batch_fill_filename_query =
393 "INSERT INTO Filename (Name) "
394 "SELECT a.Name FROM "
395 "(SELECT DISTINCT Name FROM batch) AS a WHERE NOT EXISTS "
396 "(SELECT Name FROM Filename AS f WHERE f.Name = a.Name)";
397 #endif /* HAVE_BATCH_FILE_INSERT */
399 #endif /* HAVE_MYSQL */