2 Bacula® - The Network Backup Solution
4 Copyright (C) 2000-2007 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 plus additions
11 that are listed in the file LICENSE.
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 John Walker.
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 BQUEUE db_list = {&db_list, &db_list};
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 /* Look to see if DB already open */
87 if (!mult_db_connections) {
88 for (mdb=NULL; (mdb=(B_DB *)qnext(&db_list, &mdb->bq)); ) {
89 if (bstrcmp(mdb->db_name, db_name) &&
90 bstrcmp(mdb->db_address, db_address) &&
91 mdb->db_port == db_port) {
92 Dmsg2(100, "DB REopen %d %s\n", mdb->ref_count, db_name);
95 Dmsg3(100, "initdb ref=%d connected=%d db=%p\n", mdb->ref_count,
96 mdb->connected, mdb->db);
97 return mdb; /* already open */
101 Dmsg0(100, "db_open first time\n");
102 mdb = (B_DB *)malloc(sizeof(B_DB));
103 memset(mdb, 0, sizeof(B_DB));
104 mdb->db_name = bstrdup(db_name);
105 mdb->db_user = bstrdup(db_user);
107 mdb->db_password = bstrdup(db_password);
110 mdb->db_address = bstrdup(db_address);
113 mdb->db_socket = bstrdup(db_socket);
115 mdb->db_port = db_port;
116 mdb->have_insert_id = true;
117 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
119 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
120 mdb->cached_path = get_pool_memory(PM_FNAME);
121 mdb->cached_path_id = 0;
123 mdb->fname = get_pool_memory(PM_FNAME);
124 mdb->path = get_pool_memory(PM_FNAME);
125 mdb->esc_name = get_pool_memory(PM_FNAME);
126 mdb->esc_path = get_pool_memory(PM_FNAME);
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) {
152 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
158 /* connect to the database */
159 #ifdef xHAVE_EMBEDDED_MYSQL
160 // mysql_server_init(0, NULL, NULL);
162 mysql_init(&mdb->mysql);
164 Dmsg0(50, "mysql_init done\n");
165 /* If connection fails, try at 5 sec intervals for 30 seconds. */
166 for (int retry=0; retry < 6; retry++) {
167 mdb->db = mysql_real_connect(
168 &(mdb->mysql), /* db */
169 mdb->db_address, /* default = localhost */
170 mdb->db_user, /* login name */
171 mdb->db_password, /* password */
172 mdb->db_name, /* database name */
173 mdb->db_port, /* default port */
174 mdb->db_socket, /* default = socket */
175 CLIENT_FOUND_ROWS); /* flags */
177 /* If no connect, try once more in case it is a timing problem */
178 if (mdb->db != NULL) {
184 mdb->mysql.reconnect = 1; /* so connection does not timeout */
185 Dmsg0(50, "mysql_real_connect done\n");
186 Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n", mdb->db_user, mdb->db_name,
187 mdb->db_password==NULL?"(NULL)":mdb->db_password);
189 if (mdb->db == NULL) {
190 Mmsg2(&mdb->errmsg, _("Unable to connect to MySQL server.\n"
191 "Database=%s User=%s\n"
192 "MySQL connect failed either server not running or your authorization is incorrect.\n"),
193 mdb->db_name, mdb->db_user);
198 mdb->connected = true;
199 if (!check_tables_version(jcr, mdb)) {
204 Dmsg3(100, "opendb ref=%d connected=%d db=%p\n", mdb->ref_count,
205 mdb->connected, mdb->db);
212 db_close_database(JCR *jcr, B_DB *mdb)
217 db_end_transaction(jcr, mdb);
219 sql_free_result(mdb);
221 Dmsg3(100, "closedb ref=%d connected=%d db=%p\n", mdb->ref_count,
222 mdb->connected, mdb->db);
223 if (mdb->ref_count == 0) {
225 if (mdb->connected) {
226 Dmsg1(100, "close db=%p\n", mdb->db);
227 mysql_close(&mdb->mysql);
229 #ifdef xHAVE_EMBEDDED_MYSQL
230 // mysql_server_end();
233 rwl_destroy(&mdb->lock);
234 free_pool_memory(mdb->errmsg);
235 free_pool_memory(mdb->cmd);
236 free_pool_memory(mdb->cached_path);
237 free_pool_memory(mdb->fname);
238 free_pool_memory(mdb->path);
239 free_pool_memory(mdb->esc_name);
240 free_pool_memory(mdb->esc_path);
247 if (mdb->db_password) {
248 free(mdb->db_password);
250 if (mdb->db_address) {
251 free(mdb->db_address);
253 if (mdb->db_socket) {
254 free(mdb->db_socket);
262 * This call is needed because the message channel thread
263 * opens a database on behalf of a jcr that was created in
264 * a different thread. MySQL then allocates thread specific
265 * data, which is NOT freed when the original jcr thread
266 * closes the database. Thus the msgchan must call here
267 * to cleanup any thread specific data that it created.
269 void db_thread_cleanup()
277 * Return the next unique index (auto-increment) for
278 * the given table. Return NULL on error.
280 * For MySQL, NULL causes the auto-increment value
283 int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index)
285 strcpy(index, "NULL");
291 * Escape strings so that MySQL is happy
293 * NOTE! len is the length of the old string. Your new
294 * string must be long enough (max 2*old+1) to hold
295 * the escaped output.
298 db_escape_string(char *snew, char *old, int len)
300 mysql_escape_string(snew, old, len);
304 /* Should use mysql_real_escape_string ! */
305 unsigned long mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned long length);
357 * Submit a general SQL command (cmd), and for each row returned,
358 * the sqlite_handler is called with the ctx.
360 int db_sql_query(B_DB *mdb, const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
366 if (sql_query(mdb, query) != 0) {
367 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb));
371 if (result_handler != NULL) {
372 if ((mdb->result = sql_use_result(mdb)) != NULL) {
373 int num_fields = sql_num_fields(mdb);
375 /* We *must* fetch all rows */
376 while ((row = sql_fetch_row(mdb)) != NULL) {
378 /* the result handler returns 1 when it has
379 * seen all the data it wants. However, we
380 * loop to the end of the data.
382 if (result_handler(ctx, num_fields, row)) {
388 sql_free_result(mdb);
396 void my_mysql_free_result(B_DB *mdb)
399 mysql_free_result(mdb->result);
404 char *my_mysql_batch_lock_path_query = "LOCK TABLES Path write, "
409 char *my_mysql_batch_lock_filename_query = "LOCK TABLES Filename write, "
411 " Filename as f write ";
413 char *my_mysql_batch_unlock_tables_query = "UNLOCK TABLES";
415 char *my_mysql_batch_fill_path_query = "INSERT INTO Path (Path) "
416 " SELECT a.Path FROM "
417 " (SELECT DISTINCT Path "
422 " WHERE p.Path = a.Path) ";
424 char *my_mysql_batch_fill_filename_query = "INSERT INTO Filename (Name) "
425 " SELECT a.Name FROM "
426 " (SELECT DISTINCT Name "
430 " FROM Filename AS f "
431 " WHERE f.Name = a.Name) ";
433 #endif /* HAVE_MYSQL */