2 * Bacula Catalog Database routines specific to MySQL
3 * These are MySQL specific routines -- hopefully all
4 * other files are generic.
6 * Kern Sibbald, March 2000
11 Copyright (C) 2000-2006 Kern Sibbald
13 This program is free software; you can redistribute it and/or
14 modify it under the terms of the GNU General Public License
15 version 2 as amended with additional clauses defined in the
16 file LICENSE in the main source directory.
18 This program is distributed in the hope that it will be useful,
19 but WITHOUT ANY WARRANTY; without even the implied warranty of
20 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
21 the file LICENSE for additional details.
26 /* The following is necessary so that we do not include
27 * the dummy external definition of DB.
29 #define __SQL_C /* indicate that this is sql.c */
36 /* -----------------------------------------------------------------------
38 * MySQL dependent defines and subroutines
40 * -----------------------------------------------------------------------
43 /* List of open databases */
44 static BQUEUE db_list = {&db_list, &db_list};
46 static pthread_mutex_t mutex = PTHREAD_MUTEX_INITIALIZER;
49 * Retrieve database type
58 * Initialize database data structure. In principal this should
59 * never have errors, or it is really fatal.
62 db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char *db_password,
63 const char *db_address, int db_port, const char *db_socket,
64 int mult_db_connections)
69 Jmsg(jcr, M_FATAL, 0, _("A user name for MySQL must be supplied.\n"));
72 P(mutex); /* lock DB queue */
73 /* Look to see if DB already open */
74 if (!mult_db_connections) {
75 for (mdb=NULL; (mdb=(B_DB *)qnext(&db_list, &mdb->bq)); ) {
76 if (bstrcmp(mdb->db_name, db_name) &&
77 bstrcmp(mdb->db_address, db_address) &&
78 mdb->db_port == db_port) {
79 Dmsg2(100, "DB REopen %d %s\n", mdb->ref_count, db_name);
82 return mdb; /* already open */
86 Dmsg0(100, "db_open first time\n");
87 mdb = (B_DB *) malloc(sizeof(B_DB));
88 memset(mdb, 0, sizeof(B_DB));
89 mdb->db_name = bstrdup(db_name);
90 mdb->db_user = bstrdup(db_user);
92 mdb->db_password = bstrdup(db_password);
95 mdb->db_address = bstrdup(db_address);
98 mdb->db_socket = bstrdup(db_socket);
100 mdb->db_port = db_port;
101 mdb->have_insert_id = TRUE;
102 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
104 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
105 mdb->cached_path = get_pool_memory(PM_FNAME);
106 mdb->cached_path_id = 0;
108 mdb->fname = get_pool_memory(PM_FNAME);
109 mdb->path = get_pool_memory(PM_FNAME);
110 mdb->esc_name = get_pool_memory(PM_FNAME);
111 qinsert(&db_list, &mdb->bq); /* put db in list */
117 * Now actually open the database. This can generate errors,
118 * which are returned in the errmsg
120 * DO NOT close the database or free(mdb) here !!!!
123 db_open_database(JCR *jcr, B_DB *mdb)
128 if (mdb->connected) {
132 mdb->connected = FALSE;
134 if ((errstat=rwl_init(&mdb->lock)) != 0) {
135 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
141 /* connect to the database */
142 #ifdef HAVE_EMBEDDED_MYSQL
143 mysql_server_init(0, NULL, NULL);
145 mysql_init(&(mdb->mysql));
146 Dmsg0(50, "mysql_init done\n");
147 /* If connection fails, try at 5 sec intervals for 30 seconds. */
148 for (int retry=0; retry < 6; retry++) {
149 mdb->db = mysql_real_connect(
150 &(mdb->mysql), /* db */
151 mdb->db_address, /* default = localhost */
152 mdb->db_user, /* login name */
153 mdb->db_password, /* password */
154 mdb->db_name, /* database name */
155 mdb->db_port, /* default port */
156 mdb->db_socket, /* default = socket */
157 CLIENT_FOUND_ROWS); /* flags */
159 /* If no connect, try once more in case it is a timing problem */
160 if (mdb->db != NULL) {
166 mdb->mysql.reconnect = 1; /* so connection does not timeout */
167 Dmsg0(50, "mysql_real_connect done\n");
168 Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n", mdb->db_user, mdb->db_name,
169 mdb->db_password==NULL?"(NULL)":mdb->db_password);
171 if (mdb->db == NULL) {
172 Mmsg2(&mdb->errmsg, _("Unable to connect to MySQL server. \n"
173 "Database=%s User=%s\n"
174 "It is probably not running or your password is incorrect.\n"),
175 mdb->db_name, mdb->db_user);
180 if (!check_tables_version(jcr, mdb)) {
185 #ifdef HAVE_THREAD_SAFE_MYSQL
189 mdb->connected = TRUE;
195 db_close_database(JCR *jcr, B_DB *mdb)
200 db_end_transaction(jcr, mdb);
203 #if defined(HAVE_THREAD_SAFE_MYSQL)
206 if (mdb->ref_count == 0) {
208 if (mdb->connected && mdb->db) {
210 #ifdef HAVE_EMBEDDED_MYSQL
214 rwl_destroy(&mdb->lock);
215 free_pool_memory(mdb->errmsg);
216 free_pool_memory(mdb->cmd);
217 free_pool_memory(mdb->cached_path);
218 free_pool_memory(mdb->fname);
219 free_pool_memory(mdb->path);
220 free_pool_memory(mdb->esc_name);
227 if (mdb->db_password) {
228 free(mdb->db_password);
230 if (mdb->db_address) {
231 free(mdb->db_address);
233 if (mdb->db_socket) {
234 free(mdb->db_socket);
242 * Return the next unique index (auto-increment) for
243 * the given table. Return NULL on error.
245 * For MySQL, NULL causes the auto-increment value
248 int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index)
250 strcpy(index, "NULL");
256 * Escape strings so that MySQL is happy
258 * NOTE! len is the length of the old string. Your new
259 * string must be long enough (max 2*old+1) to hold
260 * the escaped output.
263 db_escape_string(char *snew, char *old, int len)
265 mysql_escape_string(snew, old, len);
269 /* Should use mysql_real_escape_string ! */
270 unsigned long mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned long length);
322 * Submit a general SQL command (cmd), and for each row returned,
323 * the sqlite_handler is called with the ctx.
325 int db_sql_query(B_DB *mdb, const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
331 if (sql_query(mdb, query) != 0) {
332 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb));
336 if (result_handler != NULL) {
337 if ((mdb->result = sql_use_result(mdb)) != NULL) {
340 /* We *must* fetch all rows */
341 while ((row = sql_fetch_row(mdb)) != NULL) {
343 /* the result handler returns 1 when it has
344 * seen all the data it wants. However, we
345 * loop to the end of the data.
348 if (result_handler(ctx, num_fields, row)) {
354 sql_free_result(mdb);
362 #endif /* HAVE_MYSQL */