2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2008 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
39 /* The following is necessary so that we do not include
40 * the dummy external definition of DB.
42 #define __SQL_C /* indicate that this is sql.c */
49 /* -----------------------------------------------------------------------
51 * MySQL dependent defines and subroutines
53 * -----------------------------------------------------------------------
56 /* List of open databases */
57 static dlist *db_list = NULL;
59 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
62 * Retrieve database type
71 * Initialize database data structure. In principal this should
72 * never have errors, or it is really fatal.
75 db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char *db_password,
76 const char *db_address, int db_port, const char *db_socket,
77 int mult_db_connections)
82 Jmsg(jcr, M_FATAL, 0, _("A user name for MySQL must be supplied.\n"));
85 P(mutex); /* lock DB queue */
86 if (db_list == NULL) {
87 db_list = New(dlist(mdb, &mdb->link));
89 /* Look to see if DB already open */
90 if (!mult_db_connections) {
91 foreach_dlist(mdb, db_list) {
92 if (bstrcmp(mdb->db_name, db_name) &&
93 bstrcmp(mdb->db_address, db_address) &&
94 mdb->db_port == db_port) {
95 Dmsg2(100, "DB REopen %d %s\n", mdb->ref_count, db_name);
98 Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", mdb->ref_count,
99 mdb->connected, mdb->db);
100 return mdb; /* already open */
104 db_check_backend_thread_safe();
105 Dmsg0(100, "db_open first time\n");
106 mdb = (B_DB *)malloc(sizeof(B_DB));
107 memset(mdb, 0, sizeof(B_DB));
108 mdb->db_name = bstrdup(db_name);
109 mdb->db_user = bstrdup(db_user);
111 mdb->db_password = bstrdup(db_password);
114 mdb->db_address = bstrdup(db_address);
117 mdb->db_socket = bstrdup(db_socket);
119 mdb->db_port = db_port;
120 mdb->have_insert_id = true;
121 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
123 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
124 mdb->cached_path = get_pool_memory(PM_FNAME);
125 mdb->cached_path_id = 0;
127 mdb->fname = get_pool_memory(PM_FNAME);
128 mdb->path = get_pool_memory(PM_FNAME);
129 mdb->esc_name = get_pool_memory(PM_FNAME);
130 mdb->esc_path = get_pool_memory(PM_FNAME);
131 mdb->allow_transactions = mult_db_connections;
132 db_list->append(mdb); /* put db in list */
133 Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", mdb->ref_count,
134 mdb->connected, mdb->db);
140 * Now actually open the database. This can generate errors,
141 * which are returned in the errmsg
143 * DO NOT close the database or free(mdb) here !!!!
146 db_open_database(JCR *jcr, B_DB *mdb)
151 if (mdb->connected) {
156 if ((errstat=rwl_init(&mdb->lock)) != 0) {
158 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
159 be.bstrerror(errstat));
164 /* connect to the database */
165 #ifdef xHAVE_EMBEDDED_MYSQL
166 // mysql_server_init(0, NULL, NULL);
168 mysql_init(&mdb->mysql);
170 Dmsg0(50, "mysql_init done\n");
171 /* If connection fails, try at 5 sec intervals for 30 seconds. */
172 for (int retry=0; retry < 6; retry++) {
173 mdb->db = mysql_real_connect(
174 &(mdb->mysql), /* db */
175 mdb->db_address, /* default = localhost */
176 mdb->db_user, /* login name */
177 mdb->db_password, /* password */
178 mdb->db_name, /* database name */
179 mdb->db_port, /* default port */
180 mdb->db_socket, /* default = socket */
181 CLIENT_FOUND_ROWS); /* flags */
183 /* If no connect, try once more in case it is a timing problem */
184 if (mdb->db != NULL) {
190 mdb->mysql.reconnect = 1; /* so connection does not timeout */
191 Dmsg0(50, "mysql_real_connect done\n");
192 Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n", mdb->db_user, mdb->db_name,
193 mdb->db_password==NULL?"(NULL)":mdb->db_password);
195 if (mdb->db == NULL) {
196 Mmsg2(&mdb->errmsg, _("Unable to connect to MySQL server.\n"
197 "Database=%s User=%s\n"
198 "MySQL connect failed either server not running or your authorization is incorrect.\n"),
199 mdb->db_name, mdb->db_user);
200 #if MYSQL_VERSION_ID >= 40101
201 Dmsg3(50, "Error %u (%s): %s\n",
202 mysql_errno(&(mdb->mysql)), mysql_sqlstate(&(mdb->mysql)),
203 mysql_error(&(mdb->mysql)));
205 Dmsg2(50, "Error %u: %s\n",
206 mysql_errno(&(mdb->mysql)), mysql_error(&(mdb->mysql)));
212 mdb->connected = true;
213 if (!check_tables_version(jcr, mdb)) {
218 Dmsg3(100, "opendb ref=%d connected=%d db=%p\n", mdb->ref_count,
219 mdb->connected, mdb->db);
221 /* Set connection timeout to 8 days specialy for batch mode */
222 sql_query(mdb, "SET wait_timeout=691200");
223 sql_query(mdb, "SET interactive_timeout=691200");
230 db_close_database(JCR *jcr, B_DB *mdb)
235 db_end_transaction(jcr, mdb);
237 sql_free_result(mdb);
239 Dmsg3(100, "closedb ref=%d connected=%d db=%p\n", mdb->ref_count,
240 mdb->connected, mdb->db);
241 if (mdb->ref_count == 0) {
242 db_list->remove(mdb);
243 if (mdb->connected) {
244 Dmsg1(100, "close db=%p\n", mdb->db);
245 mysql_close(&mdb->mysql);
247 #ifdef xHAVE_EMBEDDED_MYSQL
248 // mysql_server_end();
251 rwl_destroy(&mdb->lock);
252 free_pool_memory(mdb->errmsg);
253 free_pool_memory(mdb->cmd);
254 free_pool_memory(mdb->cached_path);
255 free_pool_memory(mdb->fname);
256 free_pool_memory(mdb->path);
257 free_pool_memory(mdb->esc_name);
258 free_pool_memory(mdb->esc_path);
265 if (mdb->db_password) {
266 free(mdb->db_password);
268 if (mdb->db_address) {
269 free(mdb->db_address);
271 if (mdb->db_socket) {
272 free(mdb->db_socket);
279 void db_check_backend_thread_safe()
281 #ifdef HAVE_BATCH_FILE_INSERT
282 if (!mysql_thread_safe()) {
283 Emsg0(M_ABORT, 0, _("MySQL client library must be thread-safe "
284 "when using BatchMode.\n"));
290 * This call is needed because the message channel thread
291 * opens a database on behalf of a jcr that was created in
292 * a different thread. MySQL then allocates thread specific
293 * data, which is NOT freed when the original jcr thread
294 * closes the database. Thus the msgchan must call here
295 * to cleanup any thread specific data that it created.
297 void db_thread_cleanup()
305 * Return the next unique index (auto-increment) for
306 * the given table. Return NULL on error.
308 * For MySQL, NULL causes the auto-increment value
311 int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index)
313 strcpy(index, "NULL");
319 * Escape strings so that MySQL is happy
321 * NOTE! len is the length of the old string. Your new
322 * string must be long enough (max 2*old+1) to hold
323 * the escaped output.
326 db_escape_string(JCR *jcr, B_DB *mdb, char *snew, char *old, int len)
328 mysql_real_escape_string(mdb->db, snew, old, len);
332 * Submit a general SQL command (cmd), and for each row returned,
333 * the sqlite_handler is called with the ctx.
335 bool db_sql_query(B_DB *mdb, const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
341 if (sql_query(mdb, query) != 0) {
342 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb));
346 if (result_handler != NULL) {
347 if ((mdb->result = sql_use_result(mdb)) != NULL) {
348 int num_fields = sql_num_fields(mdb);
350 /* We *must* fetch all rows */
351 while ((row = sql_fetch_row(mdb)) != NULL) {
353 /* the result handler returns 1 when it has
354 * seen all the data it wants. However, we
355 * loop to the end of the data.
357 if (result_handler(ctx, num_fields, row)) {
363 sql_free_result(mdb);
371 void my_mysql_free_result(B_DB *mdb)
375 mysql_free_result(mdb->result);
381 #ifdef HAVE_BATCH_FILE_INSERT
382 const char *my_mysql_batch_lock_path_query =
383 "LOCK TABLES Path write, batch write, Path as p write";
386 const char *my_mysql_batch_lock_filename_query =
387 "LOCK TABLES Filename write, batch write, Filename as f write";
389 const char *my_mysql_batch_unlock_tables_query = "UNLOCK TABLES";
391 const char *my_mysql_batch_fill_path_query =
392 "INSERT INTO Path (Path) "
393 "SELECT a.Path FROM "
394 "(SELECT DISTINCT Path FROM batch) AS a WHERE NOT EXISTS "
395 "(SELECT Path FROM Path AS p WHERE p.Path = a.Path)";
397 const char *my_mysql_batch_fill_filename_query =
398 "INSERT INTO Filename (Name) "
399 "SELECT a.Name FROM "
400 "(SELECT DISTINCT Name FROM batch) AS a WHERE NOT EXISTS "
401 "(SELECT Name FROM Filename AS f WHERE f.Name = a.Name)";
402 #endif /* HAVE_BATCH_FILE_INSERT */
404 #endif /* HAVE_MYSQL */