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 * Initialize database data structure. In principal this should
50 * never have errors, or it is really fatal.
53 db_init_database(JCR *jcr, const char *db_name, const char *db_user, const char *db_password,
54 const char *db_address, int db_port, const char *db_socket,
55 int mult_db_connections)
60 Jmsg(jcr, M_FATAL, 0, _("A user name for MySQL must be supplied.\n"));
63 P(mutex); /* lock DB queue */
64 /* Look to see if DB already open */
65 if (!mult_db_connections) {
66 for (mdb=NULL; (mdb=(B_DB *)qnext(&db_list, &mdb->bq)); ) {
67 if (bstrcmp(mdb->db_name, db_name) &&
68 bstrcmp(mdb->db_address, db_address) &&
69 mdb->db_port == db_port) {
70 Dmsg2(100, "DB REopen %d %s\n", mdb->ref_count, db_name);
73 return mdb; /* already open */
77 Dmsg0(100, "db_open first time\n");
78 mdb = (B_DB *) malloc(sizeof(B_DB));
79 memset(mdb, 0, sizeof(B_DB));
80 mdb->db_name = bstrdup(db_name);
81 mdb->db_user = bstrdup(db_user);
83 mdb->db_password = bstrdup(db_password);
86 mdb->db_address = bstrdup(db_address);
89 mdb->db_socket = bstrdup(db_socket);
91 mdb->db_port = db_port;
92 mdb->have_insert_id = TRUE;
93 mdb->errmsg = get_pool_memory(PM_EMSG); /* get error message buffer */
95 mdb->cmd = get_pool_memory(PM_EMSG); /* get command buffer */
96 mdb->cached_path = get_pool_memory(PM_FNAME);
97 mdb->cached_path_id = 0;
99 mdb->fname = get_pool_memory(PM_FNAME);
100 mdb->path = get_pool_memory(PM_FNAME);
101 mdb->esc_name = get_pool_memory(PM_FNAME);
102 qinsert(&db_list, &mdb->bq); /* put db in list */
108 * Now actually open the database. This can generate errors,
109 * which are returned in the errmsg
111 * DO NOT close the database or free(mdb) here !!!!
114 db_open_database(JCR *jcr, B_DB *mdb)
119 if (mdb->connected) {
123 mdb->connected = FALSE;
125 if ((errstat=rwl_init(&mdb->lock)) != 0) {
126 Mmsg1(&mdb->errmsg, _("Unable to initialize DB lock. ERR=%s\n"),
132 /* connect to the database */
133 #ifdef HAVE_EMBEDDED_MYSQL
134 mysql_server_init(0, NULL, NULL);
136 mysql_init(&(mdb->mysql));
137 Dmsg0(50, "mysql_init done\n");
138 /* If connection fails, try at 5 sec intervals for 30 seconds. */
139 for (int retry=0; retry < 6; retry++) {
140 mdb->db = mysql_real_connect(
141 &(mdb->mysql), /* db */
142 mdb->db_address, /* default = localhost */
143 mdb->db_user, /* login name */
144 mdb->db_password, /* password */
145 mdb->db_name, /* database name */
146 mdb->db_port, /* default port */
147 mdb->db_socket, /* default = socket */
148 CLIENT_FOUND_ROWS); /* flags */
150 /* If no connect, try once more in case it is a timing problem */
151 if (mdb->db != NULL) {
157 mdb->mysql.reconnect = 1; /* so connection does not timeout */
158 Dmsg0(50, "mysql_real_connect done\n");
159 Dmsg3(50, "db_user=%s db_name=%s db_password=%s\n", mdb->db_user, mdb->db_name,
160 mdb->db_password==NULL?"(NULL)":mdb->db_password);
162 if (mdb->db == NULL) {
163 Mmsg2(&mdb->errmsg, _("Unable to connect to MySQL server. \n"
164 "Database=%s User=%s\n"
165 "It is probably not running or your password is incorrect.\n"),
166 mdb->db_name, mdb->db_user);
171 if (!check_tables_version(jcr, mdb)) {
176 #ifdef HAVE_THREAD_SAFE_MYSQL
180 mdb->connected = TRUE;
186 db_close_database(JCR *jcr, B_DB *mdb)
191 db_end_transaction(jcr, mdb);
194 #ifdef HAVE_TREAD_SAFE_MYSQL
197 if (mdb->ref_count == 0) {
199 if (mdb->connected && mdb->db) {
201 #ifdef HAVE_EMBEDDED_MYSQL
205 rwl_destroy(&mdb->lock);
206 free_pool_memory(mdb->errmsg);
207 free_pool_memory(mdb->cmd);
208 free_pool_memory(mdb->cached_path);
209 free_pool_memory(mdb->fname);
210 free_pool_memory(mdb->path);
211 free_pool_memory(mdb->esc_name);
218 if (mdb->db_password) {
219 free(mdb->db_password);
221 if (mdb->db_address) {
222 free(mdb->db_address);
224 if (mdb->db_socket) {
225 free(mdb->db_socket);
233 * Return the next unique index (auto-increment) for
234 * the given table. Return NULL on error.
236 * For MySQL, NULL causes the auto-increment value
239 int db_next_index(JCR *jcr, B_DB *mdb, char *table, char *index)
241 strcpy(index, "NULL");
247 * Escape strings so that MySQL is happy
249 * NOTE! len is the length of the old string. Your new
250 * string must be long enough (max 2*old+1) to hold
251 * the escaped output.
254 db_escape_string(char *snew, char *old, int len)
256 mysql_escape_string(snew, old, len);
260 /* Should use mysql_real_escape_string ! */
261 unsigned long mysql_real_escape_string(MYSQL *mysql, char *to, const char *from, unsigned long length);
313 * Submit a general SQL command (cmd), and for each row returned,
314 * the sqlite_handler is called with the ctx.
316 int db_sql_query(B_DB *mdb, const char *query, DB_RESULT_HANDLER *result_handler, void *ctx)
322 if (sql_query(mdb, query) != 0) {
323 Mmsg(mdb->errmsg, _("Query failed: %s: ERR=%s\n"), query, sql_strerror(mdb));
327 if (result_handler != NULL) {
328 if ((mdb->result = sql_use_result(mdb)) != NULL) {
331 /* We *must* fetch all rows */
332 while ((row = sql_fetch_row(mdb)) != NULL) {
334 /* the result handler returns 1 when it has
335 * seen all the data it wants. However, we
336 * loop to the end of the data.
339 if (result_handler(ctx, num_fields, row)) {
345 sql_free_result(mdb);
353 #endif /* HAVE_MYSQL */