5
/* dictionary manager interface to MySQL databases
7
/* #include <dict_mysql.h>
9
/* DICT *dict_mysql_open(name, open_flags, dict_flags)
14
/* dict_mysql_open() creates a dictionary of type 'mysql'. This
15
/* dictionary is an interface for the postfix key->value mappings
16
/* to mysql. The result is a pointer to the installed dictionary,
17
/* or a null pointer in case of problems.
19
/* The mysql dictionary can manage multiple connections to different
20
/* sql servers on different hosts. It assumes that the underlying data
21
/* on each host is identical (mirrored) and maintains one connection
22
/* at any given time. If any connection fails, any other available
23
/* ones will be opened and used. The intent of this feature is to eliminate
24
/* a single point of failure for mail systems that would otherwise rely
25
/* on a single mysql server.
29
/* Either the path to the MySQL configuration file (if it starts
30
/* with '/' or '.'), or the prefix which will be used to obtain
31
/* main.cf configuration parameters for this search.
33
/* In the first case, the configuration parameters below are
34
/* specified in the file as \fIname\fR=\fBvalue\fR pairs.
36
/* In the second case, the configuration parameters are
37
/* prefixed with the value of \fIname\fR and an underscore,
38
/* and they are specified in main.cf. For example, if this
39
/* value is \fImysqlsource\fR, the parameters would look like
40
/* \fImysqlsource_user\fR, \fImysqlsource_table\fR, and so on.
43
/* reference for outside use.
49
/* Configuration parameters:
51
/* The parameters encodes a number of pieces of information:
52
/* username, password, databasename, table, select_field,
53
/* where_field, and hosts:
55
/* Username for connecting to the database.
57
/* Password for the above.
59
/* Name of the database.
62
/* .IP \fIselect_field\fR
63
/* Name of the result field.
64
/* .IP \fIwhere_field\fR
65
/* Field used in the WHERE clause.
66
/* .IP \fIadditional_conditions\fR
67
/* Additional conditions to the WHERE clause.
69
/* List of hosts to connect to.
71
/* For example, if you want the map to reference databases of
72
/* the name "your_db" and execute a query like this: select
73
/* forw_addr from aliases where alias like '<some username>'
74
/* against any database called "vmailer_info" located on hosts
75
/* host1.some.domain and host2.some.domain, logging in as user
76
/* "vmailer" and password "passwd" then the configuration file
79
/* \fIuser\fR = \fBvmailer\fR
81
/* \fIpassword\fR = \fBpasswd\fR
83
/* \fIdbname\fR = \fBvmailer_info\fR
85
/* \fItable\fR = \fBaliases\fR
87
/* \fIselect_field\fR = \fBforw_addr\fR
89
/* \fIwhere_field\fR = \fBalias\fR
91
/* \fIhosts\fR = \fBhost1.some.domain\fR \fBhost2.some.domain\fR
94
/* dict(3) generic dictionary manager
105
/* System library. */
106
#include "sys_defs.h"
109
#include <sys/socket.h>
110
#include <netinet/in.h>
111
#include <arpa/inet.h>
120
/* Utility library. */
124
#include "mymalloc.h"
127
#include "split_at.h"
128
#include "find_inet.h"
132
/* Global library. */
134
#include "cfg_parser.h"
136
/* Application-specific. */
138
#include "dict_mysql.h"
140
/* need some structs to help organize things */
146
unsigned type; /* TYPEUNIX | TYPEINET */
147
unsigned stat; /* STATUNTRIED | STATFAIL | STATCUR */
148
time_t ts; /* used for attempting reconnection
149
* every so often if a host is down */
153
int len_hosts; /* number of hosts */
154
HOST **db_hosts; /* the hosts on which the databases
166
char *additional_conditions;
177
#define STATACTIVE (1<<0)
178
#define STATFAIL (1<<1)
179
#define STATUNTRIED (1<<2)
181
#define TYPEUNIX (1<<0)
182
#define TYPEINET (1<<1)
184
#define RETRY_CONN_MAX 100
185
#define RETRY_CONN_INTV 60 /* 1 minute */
186
#define IDLE_CONN_INTV 60 /* 1 minute */
188
/* internal function declarations */
189
static PLMYSQL *plmysql_init(char *hostnames[], int);
190
static MYSQL_RES *plmysql_query(PLMYSQL *, const char *, char *, char *, char *);
191
static void plmysql_dealloc(PLMYSQL *);
192
static void plmysql_close_host(HOST *);
193
static void plmysql_down_host(HOST *);
194
static void plmysql_connect_single(HOST *, char *, char *, char *);
195
static const char *dict_mysql_lookup(DICT *, const char *);
196
DICT *dict_mysql_open(const char *, int, int);
197
static void dict_mysql_close(DICT *);
198
static MYSQL_NAME *mysqlname_parse(const char *);
199
static HOST *host_init(const char *);
203
/**********************************************************************
204
* public interface dict_mysql_lookup
205
* find database entry return 0 if no alias found, set dict_errno
206
* on errors to DICT_ERRBO_RETRY and set dict_errno to 0 on success
207
*********************************************************************/
208
static const char *dict_mysql_lookup(DICT *dict, const char *name)
210
MYSQL_RES *query_res;
212
DICT_MYSQL *dict_mysql;
214
static VSTRING *result;
215
static VSTRING *query = 0;
219
char *name_escaped = 0;
221
dict_mysql = (DICT_MYSQL *) dict;
222
pldb = dict_mysql->pldb;
223
/* initialization for query */
224
query = vstring_alloc(24);
225
vstring_strcpy(query, "");
226
if ((name_escaped = (char *) mymalloc((sizeof(char) * (strlen(name) * 2) +1))) == NULL) {
227
msg_fatal("dict_mysql_lookup: out of memory.");
229
/* prepare the query */
230
mysql_escape_string(name_escaped, name, (unsigned int) strlen(name));
231
vstring_sprintf(query, "select %s from %s where %s = '%s' %s", dict_mysql->name->select_field,
232
dict_mysql->name->table, dict_mysql->name->where_field, name_escaped,
233
dict_mysql->name->additional_conditions);
235
msg_info("dict_mysql_lookup using sql query: %s", vstring_str(query));
236
/* free mem associated with preparing the query */
237
myfree(name_escaped);
238
/* do the query - set dict_errno & cleanup if there's an error */
239
if ((query_res = plmysql_query(pldb,
241
dict_mysql->name->dbname,
242
dict_mysql->name->username,
243
dict_mysql->name->password)) == 0) {
244
dict_errno = DICT_ERR_RETRY;
249
/* free the vstring query */
251
numrows = mysql_num_rows(query_res);
253
msg_info("dict_mysql_lookup: retrieved %d rows", numrows);
255
mysql_free_result(query_res);
259
result = vstring_alloc(10);
260
vstring_strcpy(result, "");
261
for (i = 0; i < numrows; i++) {
262
row = mysql_fetch_row(query_res);
264
vstring_strcat(result, ",");
265
for (j = 0; j < mysql_num_fields(query_res); j++) {
268
msg_info("dict_mysql_lookup: null field #%d row #%d", j, i);
269
mysql_free_result(query_res);
273
vstring_strcat(result, ",");
274
vstring_strcat(result, row[j]);
276
msg_info("dict_mysql_lookup: retrieved field: %d: %s", j, row[j]);
279
mysql_free_result(query_res);
280
return vstring_str(result);
283
/* dict_mysql_check_stat - check the status of a host */
285
static int dict_mysql_check_stat(HOST *host, unsigned stat, unsigned type,
288
if ((host->stat & stat) && (!type || host->type & type)) {
289
/* try not to hammer the dead hosts too often */
290
if (host->stat == STATFAIL && host->ts > 0 && host->ts >= t)
297
/* dict_mysql_find_host - find a host with the given status */
299
static HOST *dict_mysql_find_host(PLMYSQL *PLDB, unsigned stat, unsigned type)
306
t = time((time_t *) 0);
307
for (i = 0; i < PLDB->len_hosts; i++) {
308
if (dict_mysql_check_stat(PLDB->db_hosts[i], stat, type, t))
314
* Calling myrand() can deplete the random pool.
315
* Don't rely on the optimizer to weed out the call
318
idx = (count > 1) ? 1 + (count - 1) * (double) myrand() / RAND_MAX : 1;
320
for (i = 0; i < PLDB->len_hosts; i++) {
321
if (dict_mysql_check_stat(PLDB->db_hosts[i], stat, type, t) &&
323
return PLDB->db_hosts[i];
329
/* dict_mysql_get_active - get an active connection */
331
static HOST *dict_mysql_get_active(PLMYSQL *PLDB, char *dbname,
332
char *username, char *password)
334
const char *myname = "dict_mysql_get_active";
336
int count = RETRY_CONN_MAX;
338
/* Try the active connections first; prefer the ones to UNIX sockets. */
339
if ((host = dict_mysql_find_host(PLDB, STATACTIVE, TYPEUNIX)) != NULL ||
340
(host = dict_mysql_find_host(PLDB, STATACTIVE, TYPEINET)) != NULL) {
342
msg_info("%s: found active connection to host %s", myname,
348
* Try the remaining hosts.
349
* "count" is a safety net, in case the loop takes more than
350
* RETRY_CONN_INTV and the dead hosts are no longer skipped.
352
while (--count > 0 &&
353
((host = dict_mysql_find_host(PLDB, STATUNTRIED | STATFAIL,
354
TYPEUNIX)) != NULL ||
355
(host = dict_mysql_find_host(PLDB, STATUNTRIED | STATFAIL,
356
TYPEINET)) != NULL)) {
358
msg_info("%s: attempting to connect to host %s", myname,
360
plmysql_connect_single(host, dbname, username, password);
361
if (host->stat == STATACTIVE)
369
/* dict_mysql_event - callback: close idle connections */
371
static void dict_mysql_event(int unused_event, char *context)
373
HOST *host = (HOST *) context;
376
plmysql_close_host(host);
380
* plmysql_query - process a MySQL query. Return MYSQL_RES* on success.
381
* On failure, log failure and try other db instances.
382
* on failure of all db instances, return 0;
383
* close unnecessary active connections
386
static MYSQL_RES *plmysql_query(PLMYSQL *PLDB,
395
while ((host = dict_mysql_get_active(PLDB, dbname, username, password)) != NULL) {
396
if (!(mysql_query(host->db, query))) {
397
if ((res = mysql_store_result(host->db)) == 0) {
398
msg_warn("mysql query failed: %s", mysql_error(host->db));
399
plmysql_down_host(host);
402
msg_info("dict_mysql: successful query from host %s", host->hostname);
403
event_request_timer(dict_mysql_event, (char *) host, IDLE_CONN_INTV);
407
msg_warn("mysql query failed: %s", mysql_error(host->db));
408
plmysql_down_host(host);
416
* plmysql_connect_single -
417
* used to reconnect to a single database when one is down or none is
418
* connected yet. Log all errors and set the stat field of host accordingly
420
static void plmysql_connect_single(HOST *host, char *dbname, char *username, char *password)
422
if ((host->db = mysql_init(NULL)) == NULL)
423
msg_fatal("dict_mysql: insufficient memory");
424
if (mysql_real_connect(host->db,
425
(host->type == TYPEINET ? host->name : 0),
430
(host->type == TYPEUNIX ? host->name : 0),
433
msg_info("dict_mysql: successful connection to host %s",
435
host->stat = STATACTIVE;
437
msg_warn("connect to mysql server %s: %s",
438
host->hostname, mysql_error(host->db));
439
plmysql_down_host(host);
443
/* plmysql_close_host - close an established MySQL connection */
444
static void plmysql_close_host(HOST *host)
446
mysql_close(host->db);
448
host->stat = STATUNTRIED;
452
* plmysql_down_host - close a failed connection AND set a "stay away from
455
static void plmysql_down_host(HOST *host)
457
mysql_close(host->db);
459
host->ts = time((time_t *) 0) + RETRY_CONN_INTV;
460
host->stat = STATFAIL;
461
event_cancel_timer(dict_mysql_event, (char *) host);
464
/**********************************************************************
465
* public interface dict_mysql_open
466
* create association with database with appropriate values
467
* parse the map's config file
469
**********************************************************************/
470
DICT *dict_mysql_open(const char *name, int open_flags, int dict_flags)
472
DICT_MYSQL *dict_mysql;
477
if (open_flags != O_RDONLY)
478
msg_fatal("%s:%s map requires O_RDONLY access mode",
479
DICT_TYPE_MYSQL, name);
481
dict_mysql = (DICT_MYSQL *) dict_alloc(DICT_TYPE_MYSQL, name,
483
dict_mysql->dict.lookup = dict_mysql_lookup;
484
dict_mysql->dict.close = dict_mysql_close;
485
dict_mysql->dict.flags = dict_flags | DICT_FLAG_FIXED;
486
dict_mysql->name = mysqlname_parse(name);
487
dict_mysql->pldb = plmysql_init(dict_mysql->name->hostnames,
488
dict_mysql->name->len_hosts);
489
if (dict_mysql->pldb == NULL)
490
msg_fatal("couldn't intialize pldb!\n");
491
return (DICT_DEBUG (&dict_mysql->dict));
494
/* mysqlname_parse - parse mysql configuration file */
495
static MYSQL_NAME *mysqlname_parse(const char *mysqlcf)
497
const char *myname = "mysqlname_parse";
500
MYSQL_NAME *name = (MYSQL_NAME *) mymalloc(sizeof(MYSQL_NAME));
504
name->parser = cfg_parser_alloc(mysqlcf);
507
name->username = cfg_get_str(name->parser, "user", "", 0, 0);
510
name->password = cfg_get_str(name->parser, "password", "", 0, 0);
513
name->dbname = cfg_get_str(name->parser, "dbname", "", 1, 0);
516
name->table = cfg_get_str(name->parser, "table", "", 1, 0);
519
name->select_field = cfg_get_str(name->parser, "select_field", "", 1, 0);
522
name->where_field = cfg_get_str(name->parser, "where_field", "", 1, 0);
524
/* additional conditions */
525
name->additional_conditions = cfg_get_str(name->parser,
526
"additional_conditions",
529
/* mysql server hosts */
530
hosts = cfg_get_str(name->parser, "hosts", "", 0, 0);
532
/* coo argv interface */
533
hosts_argv = argv_split(hosts, " ,\t\r\n");
534
if (hosts_argv->argc == 0) { /* no hosts specified,
535
* default to 'localhost' */
537
msg_info("%s: %s: no hostnames specified, defaulting to 'localhost'",
539
argv_add(hosts_argv, "localhost", ARGV_END);
540
argv_terminate(hosts_argv);
542
name->len_hosts = hosts_argv->argc;
543
name->hostnames = (char **) mymalloc((sizeof(char *)) * name->len_hosts);
545
for (i = 0; hosts_argv->argv[i] != NULL; i++) {
546
name->hostnames[i] = mystrdup(hosts_argv->argv[i]);
548
msg_info("%s: %s: adding host '%s' to list of mysql server hosts",
549
myname, mysqlcf, name->hostnames[i]);
552
argv_free(hosts_argv);
558
* plmysql_init - initalize a MYSQL database.
559
* Return NULL on failure, or a PLMYSQL * on success.
561
static PLMYSQL *plmysql_init(char *hostnames[], int len_hosts)
566
if ((PLDB = (PLMYSQL *) mymalloc(sizeof(PLMYSQL))) == NULL) {
567
msg_fatal("mymalloc of pldb failed");
569
PLDB->len_hosts = len_hosts;
570
if ((PLDB->db_hosts = (HOST **) mymalloc(sizeof(HOST *) * len_hosts)) == NULL)
572
for (i = 0; i < len_hosts; i++) {
573
PLDB->db_hosts[i] = host_init(hostnames[i]);
579
/* host_init - initialize HOST structure */
580
static HOST *host_init(const char *hostname)
582
const char *myname = "mysql host_init";
583
HOST *host = (HOST *) mymalloc(sizeof(HOST));
584
const char *d = hostname;
588
host->hostname = mystrdup(hostname);
590
host->stat = STATUNTRIED;
594
* Ad-hoc parsing code. Expect "unix:pathname" or "inet:host:port", where
595
* both "inet:" and ":port" are optional.
597
if (strncmp(d, "unix:", 5) == 0) {
599
host->type = TYPEUNIX;
601
if (strncmp(d, "inet:", 5) == 0)
603
host->type = TYPEINET;
605
host->name = mystrdup(d);
606
if ((s = split_at_right(host->name, ':')) != 0)
607
host->port = ntohs(find_inet_port(s, "tcp"));
608
if (strcasecmp(host->name, "localhost") == 0) {
609
/* The MySQL way: this will actually connect over the UNIX socket */
612
host->type = TYPEUNIX;
616
msg_info("%s: host=%s, port=%d, type=%s", myname,
617
host->name ? host->name : "localhost",
618
host->port, host->type == TYPEUNIX ? "unix" : "inet");
622
/**********************************************************************
623
* public interface dict_mysql_close
624
* unregister, disassociate from database, freeing appropriate memory
625
**********************************************************************/
626
static void dict_mysql_close(DICT *dict)
629
DICT_MYSQL *dict_mysql = (DICT_MYSQL *) dict;
631
plmysql_dealloc(dict_mysql->pldb);
632
cfg_parser_free(dict_mysql->name->parser);
633
myfree(dict_mysql->name->username);
634
myfree(dict_mysql->name->password);
635
myfree(dict_mysql->name->dbname);
636
myfree(dict_mysql->name->table);
637
myfree(dict_mysql->name->select_field);
638
myfree(dict_mysql->name->where_field);
639
myfree(dict_mysql->name->additional_conditions);
640
for (i = 0; i < dict_mysql->name->len_hosts; i++) {
641
myfree(dict_mysql->name->hostnames[i]);
643
myfree((char *) dict_mysql->name->hostnames);
644
myfree((char *) dict_mysql->name);
648
/* plmysql_dealloc - free memory associated with PLMYSQL close databases */
649
static void plmysql_dealloc(PLMYSQL *PLDB)
653
for (i = 0; i < PLDB->len_hosts; i++) {
654
event_cancel_timer(dict_mysql_event, (char *) (PLDB->db_hosts[i]));
655
if (PLDB->db_hosts[i]->db)
656
mysql_close(PLDB->db_hosts[i]->db);
657
myfree(PLDB->db_hosts[i]->hostname);
658
if (PLDB->db_hosts[i]->name)
659
myfree(PLDB->db_hosts[i]->name);
660
myfree((char *) PLDB->db_hosts[i]);
662
myfree((char *) PLDB->db_hosts);
663
myfree((char *) (PLDB));
added
removed
src/global/dict_mysql.c
