Hi,
I have a requirement where I should be able to open connection to mysql from a server
which is running continuously and pass the connection to a child of this server after
it
does a exec to a totally different executable. This will enable me to reuse a mysql
connection
initiated by one process and used by another process - which though is a child of the
first process but is a totally different executable. Thus, I will be able to initiate
connection pooling for a daemon like smtp without keeping on initiating
mysql_real_connect()
on each and every mail.
I have done this by doing mysql_init() and mysql_real_connect() in the parent.
In the exec'ed process, I do mysql_init() followed my vio_new() and my_net_init().
The above logic works for me, But i am not sure if I am doing the right thing. Also
there is a difference
in the some of the values of the mysql structure in parent and the child.
Also enclosing two programs try.c and try1.c, which demonstrate the above. try does a
mysql_real_connect()
to open a mysql_connection with socket descriptor 3. The program try1 the uses this
same descriptor to
restablish the connection to the mysqld server without initiating a new socket
connection.
The output of the mysql structure by try is
--------------------------------------------
host localhost
user vpopmail
pass ssh-1.5-
sock /tmp/mysql.sock
vers 4.0.0-alpha-log
Host Info Localhost via UNIX socket
info (null)
db vpopmail
Affected Rows 0
Insert id 0
extra_info 0
thread_id 103
packet_length 0
port 3306
client_flag 8333
server_capabilities 44
Protocol version 10
Field Count 0
Server Status 2
Server Language 8
Mysql Status 0
Free me 0
Reconnect 1
Scramble 0Q,$n~OR
The output of the mysql structure by try1 is
--------------------------------------------
host localhost
user vpopmail
pass ssh-1.5-
sock /tmp/mysql.sock
vers
Host Info Localhost via UNIX socket
info (null)
db (null)
Affected Rows 0
Insert id 0
extra_info 0
thread_id 0
packet_length 0
port 3306
client_flag 8333
server_capabilities 0
Protocol version 10
Field Count 0
Server Status 2
Server Language 0
Mysql Status 0
Free me 0
Reconnect 1
Scramble
As you can see the following variables are not getting set in the exec'ed process.
mysql.server_version
mysql.thread_id
mysql.server_language
mysql.scramble_buff
Is there anyting i am missing? (However, the program works fine and the query also
works fine.
The mysql show process list also shows that the connection is being reused. However I
am not
sure, if this method could cause any problems).
The programs try.c and try1.c are below
/* try.c */
#include <stdio.h>
#include <fcntl.h>
#include <mysql.h>
main()
{
MYSQL mysql;
mysql_init(&mysql);
if (!(mysql_real_connect(&mysql, "localhost", "vpopmail", "*******",
"vpopmail", 3306, 0, 0)))
{
fprintf(stderr, "mysql_real_connect: %s\n", mysql_error(&mysql));
return(1);
}
printf("host %s\n", mysql.host);
printf("user %s\n", mysql.user);
printf("pass %s\n", mysql.passwd);
printf("sock %s\n", mysql.unix_socket);
printf("vers %s\n", mysql.server_version);
printf("Host Info %s\n", mysql.host_info);
printf("info %s\n", mysql.info);
printf("db %s\n", mysql.db);
printf("Affected Rows %d\n", mysql.affected_rows);
printf("Insert id %d\n", mysql.insert_id);
printf("extra_info %d\n", mysql.extra_info);
printf("thread_id %d\n", mysql.thread_id);
printf("packet_length %d\n", mysql.packet_length);
printf("port %d\n", mysql.port);
printf("client_flag %d\n", mysql.client_flag);
printf("server_capabilities %d\n", mysql.server_capabilities);
printf("Protocol version %d\n", mysql.protocol_version);
printf("Field Count %d\n", mysql.field_count);
printf("Server Status %d\n", mysql.server_status);
printf("Server Language %d\n", mysql.server_language);
printf("Mysql Status %d\n", mysql.status);
printf("Free me %d\n", mysql.free_me);
printf("Reconnect %d\n", mysql.reconnect);
printf("Scramble %s\n", mysql.scramble_buff);
getchar();
execl("/tmp/try1", "/tmp/try1", 0);
}
/* try1.c */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <pwd.h>
#include <my_global.h>
#include <my_sys.h>
#include <mysys_err.h>
#include <m_string.h>
#include <m_ctype.h>
#include "mysql.h"
#include "mysql_version.h"
#include "mysqld_error.h"
#include "errmsg.h"
#include <violite.h>
#include <sys/stat.h>
#include <signal.h>
#include <time.h>
#include <sys/socket.h>
#include <netinet/in.h>
#include <arpa/inet.h>
#include <netdb.h>
#if defined(THREAD) && !defined(__WIN__)
#include <my_pthread.h> /* because of signal() */
#endif
static my_bool mysql_client_init = 0;
uint mysql_port = 0;
my_string mysql_unix_port = 0;
#define CLIENT_CAPABILITIES (CLIENT_LONG_PASSWORD | CLIENT_LONG_FLAG |
CLIENT_LOCAL_FILES | CLIENT_TRANSACTIONS)
#include <errno.h>
#define SOCKET_ERROR -1
#if !defined(__WIN__) && defined(SIGPIPE) && !defined(THREAD)
#define init_sigpipe_variables sig_return old_signal_handler=(sig_return) 0;
#define set_sigpipe(mysql) if ((mysql)->client_flag & CLIENT_IGNORE_SIGPIPE)
old_signal_handler=signal(SIGPIPE,pipe_sig_handler)
#define reset_sigpipe(mysql) if ((mysql)->client_flag & CLIENT_IGNORE_SIGPIPE)
signal(SIGPIPE,old_signal_handler);
#else
#define init_sigpipe_variables
#define set_sigpipe(mysql)
#define reset_sigpipe(mysql)
#endif
static void mysql_read_default_options(struct st_mysql_options *, const char *,
const char *);
static void end_server(MYSQL * mysql);
static void free_old_query(MYSQL *);
MYSQL *STDCALL mysqlreconnect(my_socket, MYSQL *, const char *, const char *,
const char *, const char *, uint,
const char
*, uint);
int my_net_init(NET * net, Vio * vio);
main()
{
MYSQL mysql;
MYSQL_RES *res;
mysql_init(&mysql);
if (!(mysqlreconnect(3, &mysql, "localhost", "vpopmail", "********",
"vpopmail", 3306, 0, 0)))
{
fprintf(stderr, "mysql_real_connect: %s\n", mysql_error(&mysql));
return (1);
}
printf("host %s\n", mysql.host);
printf("user %s\n", mysql.user);
printf("pass %s\n", mysql.passwd);
printf("sock %s\n", mysql.unix_socket);
printf("vers %s\n", mysql.server_version);
printf("Host Info %s\n", mysql.host_info);
printf("info %s\n", mysql.info);
printf("db %s\n", mysql.db);
printf("Affected Rows %d\n", mysql.affected_rows);
printf("Insert id %d\n", mysql.insert_id);
printf("extra_info %d\n", mysql.extra_info);
printf("thread_id %d\n", mysql.thread_id);
printf("packet_length %d\n", mysql.packet_length);
printf("port %d\n", mysql.port);
printf("client_flag %d\n", mysql.client_flag);
printf("server_capabilities %d\n", mysql.server_capabilities);
printf("Protocol version %d\n", mysql.protocol_version);
printf("Field Count %d\n", mysql.field_count);
printf("Server Status %d\n", mysql.server_status);
printf("Server Language %d\n", mysql.server_language);
printf("Mysql Status %d\n", mysql.status);
printf("Free me %d\n", mysql.free_me);
printf("Reconnect %d\n", mysql.reconnect);
printf("Scramble %s\n", mysql.scramble_buff);
if (mysql_query(&mysql, "select * from vpopmail where pw_name='mbhangui'"))
{
fprintf(stderr, "mysql_query: %s\n", mysql_error(&mysql));
return (1);
}
res = mysql_store_result(&mysql);
mysql_free_result(res);
getchar();
}
MYSQL *STDCALL
mysqlreconnect(my_socket sock, MYSQL * mysql, const char *host, const char *user,
const char *passwd, const char *db, uint port,
const char *unix_socket, uint client_flag)
{
char buff[NAME_LEN + USERNAME_LENGTH + 100], charset_name_buff[16];
char *end, *host_info, *charset_name;
ulong pkt_length;
NET *net = &mysql->net;
init_sigpipe_variables DBUG_ENTER("mysql_real_connect");
DBUG_PRINT("enter", ("host: %s db: %s user: %s", host ? host : "(Null)", db
? db : "(Null)", user ? user : "(Null)"));
/*
* Don't give sigpipe errors if the client doesn't want them
*/
set_sigpipe(mysql);
net->vio = 0; /* If something goes wrong */
/*
* use default options
*/
if (mysql->options.my_cnf_file || mysql->options.my_cnf_group)
{
mysql_read_default_options(&mysql->options,
(mysql->options.my_cnf_file ? mysql->options.my_cnf_file : "my"),
mysql->options.my_cnf_group);
my_free(mysql->options.my_cnf_file, MYF(MY_ALLOW_ZERO_PTR));
my_free(mysql->options.my_cnf_group, MYF(MY_ALLOW_ZERO_PTR));
mysql->options.my_cnf_file = mysql->options.my_cnf_group = 0;
}
/*
* Some empty-string-tests are done because of ODBC
*/
if (!host || !host[0])
host = mysql->options.host;
if (!user || !user[0])
user = mysql->options.user;
if (!passwd)
{
passwd = mysql->options.password;
#ifndef DONT_USE_MYSQL_PWD
if (!passwd)
passwd = getenv("MYSQL_PWD"); /* get it from environment
(haneke) */
#endif
}
if (!db || !db[0])
db = mysql->options.db;
if (!port)
port = mysql->options.port;
if (!unix_socket)
unix_socket = mysql->options.unix_socket;
mysql->reconnect = 1; /* Reconnect as default */
mysql->server_status = SERVER_STATUS_AUTOCOMMIT;
#if defined(HAVE_SYS_UN_H)
if ((!host || !strcmp(host, LOCAL_HOST)) && (unix_socket || mysql_unix_port))
{
host = LOCAL_HOST;
if (!unix_socket)
unix_socket = mysql_unix_port;
host_info = (char *) ER(CR_LOCALHOST_CONNECTION);
DBUG_PRINT("info", ("Using UNIX sock '%s'", unix_socket));
net->vio = vio_new(sock, VIO_TYPE_SOCKET, TRUE);
} else
#endif
{
unix_socket = 0; /* This is not used */
if (!port)
port = mysql_port;
if (!host)
host = LOCAL_HOST;
sprintf(host_info = buff, ER(CR_TCP_CONNECTION), host);
DBUG_PRINT("info", ("Server name: '%s'. TCP sock: %d", host, port));
net->vio = vio_new(sock, VIO_TYPE_TCPIP, FALSE);
}
if (!net->vio || my_net_init(net, net->vio))
{
vio_delete(net->vio);
net->vio = 0;
net->last_errno = CR_OUT_OF_MEMORY;
strmov(net->last_error, ER(net->last_errno));
goto error;
}
vio_keepalive(net->vio, TRUE);
/*
* Get version info
*/
mysql->protocol_version = PROTOCOL_VERSION; /* Assume this */
if (mysql->options.connect_timeout && vio_poll_read(net->vio,
mysql->options.connect_timeout))
{
net->last_errno = CR_SERVER_LOST;
strmov(net->last_error, ER(net->last_errno));
goto error;
}
/*
net_flush(net);
if ((pkt_length = net_safe_read(mysql)) == packet_error)
goto error;
*/
/*
* Check if version of protocoll matches current one
*/
net->read_pos[0] = PROTOCOL_VERSION;
mysql->protocol_version = net->read_pos[0];
DBUG_DUMP("packet", (char *) net->read_pos, 10);
DBUG_PRINT("info", ("mysql protocol version %d, server=%d", PROTOCOL_VERSION,
mysql->protocol_version));
if (mysql->protocol_version != PROTOCOL_VERSION)
{
net->last_errno = CR_VERSION_ERROR;
sprintf(net->last_error, ER(CR_VERSION_ERROR),
mysql->protocol_version, PROTOCOL_VERSION);
goto error;
}
end = strend((char *) net->read_pos + 1);
mysql->thread_id = uint4korr(end + 1);
end += 5;
strmake(mysql->scramble_buff, end, 8);
end += 9;
if (pkt_length >= (uint) (end + 1 - (char *) net->read_pos))
mysql->server_capabilities = uint2korr(end);
if (pkt_length >= (uint) (end + 18 - (char *) net->read_pos))
{
/*
* New protocol with 16 bytes to describe server characteristics
*/
mysql->server_language = end[2];
mysql->server_status = uint2korr(end + 3);
}
/*
* Set character set
*/
if ((charset_name = mysql->options.charset_name))
{
const char *save = charsets_dir;
if (mysql->options.charset_dir)
charsets_dir = mysql->options.charset_dir;
mysql->charset = get_charset_by_name(mysql->options.charset_name,
MYF(MY_WME));
charsets_dir = save;
} else
if (mysql->server_language)
{
charset_name = charset_name_buff;
sprintf(charset_name, "%d", mysql->server_language); /* In case of
errors */
if (!(mysql->charset = get_charset((uint8) mysql->server_language,
MYF(0))))
mysql->charset = default_charset_info; /* shouldn't be fatal
*/
} else
mysql->charset = default_charset_info;
if (!mysql->charset)
{
net->last_errno = CR_CANT_READ_CHARSET;
if (mysql->options.charset_dir)
sprintf(net->last_error, ER(net->last_errno), charset_name ?
charset_name : "unknown", mysql->options.charset_dir);
else
{
char cs_dir_name[FN_REFLEN];
get_charsets_dir(cs_dir_name);
sprintf(net->last_error, ER(net->last_errno), charset_name ?
charset_name : "unknown", cs_dir_name);
}
goto error;
}
/*
* Save connection information
*/
if (!user)
user = "";
if (!passwd)
passwd = "";
if (!my_multi_malloc
(MYF(0), &mysql->host_info, (uint) strlen(host_info) + 1,
&mysql->host, (uint) strlen(host) + 1, &mysql->unix_socket,
unix_socket ? (uint) strlen(unix_socket) + 1 : (uint) 1,
&mysql->server_version, (uint) (end - (char *) net->read_pos),
NullS) || !(mysql->user = my_strdup(user, MYF(0))) || !(mysql->passwd
= my_strdup(passwd, MYF(0))))
{
strmov(net->last_error, ER(net->last_errno = CR_OUT_OF_MEMORY));
goto error;
}
strmov(mysql->host_info, host_info);
strmov(mysql->host, host);
if (unix_socket)
strmov(mysql->unix_socket, unix_socket);
else
mysql->unix_socket = 0;
strmov(mysql->server_version, (char *) net->read_pos + 1);
mysql->port = port;
client_flag |= mysql->options.client_flag;
/*
* Send client information for access check
*/
client_flag |= CLIENT_CAPABILITIES;
if (db)
client_flag |= CLIENT_CONNECT_WITH_DB;
#ifdef HAVE_COMPRESS
if ((mysql->server_capabilities & CLIENT_COMPRESS) && (mysql->options.compress
|| (client_flag & CLIENT_COMPRESS)))
client_flag |= CLIENT_COMPRESS; /*- We will use compression */
else
#endif
client_flag &= ~CLIENT_COMPRESS;
int2store(buff, client_flag);
mysql->client_flag = client_flag;
DBUG_PRINT("info",
("Server version = '%s' capabilites: %ld status: %d
client_flag: %d", mysql->server_version,
mysql->server_capabilities, mysql->server_status,
client_flag));
int3store(buff + 2, max_allowed_packet);
if (user && user[0])
strmake(buff + 5, user, 32); /* Max user name */
DBUG_PRINT("info", ("user: %s", buff + 5));
end = scramble(strend(buff + 5) + 1, mysql->scramble_buff, passwd, (my_bool)
(mysql->protocol_version == 9));
if (db && (mysql->server_capabilities & CLIENT_CONNECT_WITH_DB))
{
end = strmake(end + 1, db, NAME_LEN);
mysql->db = my_strdup(db, MYF(MY_WME));
db = 0;
}
/*-
if (my_net_write(net,buff,(ulong) (end-buff)) || net_flush(net) ||
net_safe_read(mysql) == packet_error)
goto error;
*/
if (client_flag & CLIENT_COMPRESS) /* We will use compression */
net->compress = 1;
/*
if (db && mysql_select_db(mysql, db))
goto error;*/
if (mysql->options.init_command)
{
my_bool reconnect = mysql->reconnect;
mysql->reconnect = 0;
if (mysql_query(mysql, mysql->options.init_command))
goto error;
mysql_free_result(mysql_use_result(mysql));
mysql->reconnect = reconnect;
}
if (mysql->options.rpl_probe && mysql_rpl_probe(mysql))
goto error;
DBUG_PRINT("exit", ("Mysql handler: %lx", mysql));
reset_sigpipe(mysql);
DBUG_RETURN(mysql);
error:
reset_sigpipe(mysql);
DBUG_PRINT("error", ("message: %u (%s)", net->last_errno, net->last_error));
{
/* Free alloced memory */
my_bool free_me = mysql->free_me;
end_server(mysql);
mysql->free_me = 0;
mysql_close(mysql);
mysql->free_me = free_me;
}
DBUG_RETURN(0);
}
/****************************************************************************
** Get options from my.cnf
****************************************************************************/
static const char *default_options[] = { "port", "socket", "compress", "password",
"pipe", "timeout", "user",
"init-command", "host", "database", "debug", "return-found-rows",
"ssl-key", "ssl-cert", "ssl-ca", "ssl-capath", "ssl-cipher"
"character-set-dir", "default-character-set", "interactive-timeout",
"connect_timeout", "replication-probe", "enable-reads-from-master",
"repl-parse-query",
NullS
};
static TYPELIB option_types = { array_elements(default_options) - 1,
"options", default_options
};
static void
mysql_read_default_options(struct st_mysql_options *options, const char *filename,
const char *group)
{
int argc;
char *argv_buff[1], **argv;
const char *groups[3];
DBUG_ENTER("mysql_read_default_options");
DBUG_PRINT("enter", ("file: %s group: %s", filename, group ? group : "NULL"));
argc = 1;
argv = argv_buff;
argv_buff[0] = (char *) "client";
groups[0] = (char *) "client";
groups[1] = (char *) group;
groups[2] = 0;
load_defaults(filename, groups, &argc, &argv);
if (argc != 1) /*
* If some default
option
*/
{
char **option = argv;
while (*++option)
{
/*
* DBUG_PRINT("info",("option: %s",option[0]));
*/
if (option[0][0] == '-' && option[0][1] == '-')
{
char *end = strcend(*option, '=');
char *opt_arg = 0;
if (*end)
{
opt_arg = end + 1;
*end = 0; /*
* Remove '='
*/
}
switch (find_type(*option + 2, &option_types, 2))
{
case 1: /*
* port
*/
if (opt_arg)
options->port = atoi(opt_arg);
break;
case 2: /*
* socket
*/
if (opt_arg)
{
my_free(options->unix_socket,
MYF(MY_ALLOW_ZERO_PTR));
options->unix_socket =
my_strdup(opt_arg, MYF(MY_WME));
}
break;
case 3: /*
* compress
*/
options->compress = 1;
break;
case 4: /* password */
if (opt_arg)
{
my_free(options->password,
MYF(MY_ALLOW_ZERO_PTR));
options->password = my_strdup(opt_arg,
MYF(MY_WME));
}
break;
case 5: /* pipe */
options->named_pipe = 1; /* Force named
pipe */
break;
case 20: /* connect_timeout */
case 6: /* timeout */
if (opt_arg)
options->connect_timeout =
atoi(opt_arg);
break;
case 7: /*
* user
*/
if (opt_arg)
{
my_free(options->user,
MYF(MY_ALLOW_ZERO_PTR));
options->user = my_strdup(opt_arg,
MYF(MY_WME));
}
break;
case 8: /*
* init-command
*/
if (opt_arg)
{
my_free(options->init_command,
MYF(MY_ALLOW_ZERO_PTR));
options->init_command =
my_strdup(opt_arg, MYF(MY_WME));
}
break;
case 9: /*
* host
*/
if (opt_arg)
{
my_free(options->host,
MYF(MY_ALLOW_ZERO_PTR));
options->host = my_strdup(opt_arg,
MYF(MY_WME));
}
break;
case 10: /*
* database
*/
if (opt_arg)
{
my_free(options->db,
MYF(MY_ALLOW_ZERO_PTR));
options->db = my_strdup(opt_arg,
MYF(MY_WME));
}
break;
case 11: /*
* debug
*/
mysql_debug(opt_arg ? opt_arg :
"d:t:o,/tmp/client.trace");
break;
case 12: /*
* return-found-rows
*/
options->client_flag |= CLIENT_FOUND_ROWS;
break;
case 13: /*
* Ignore SSL options
*/
case 14:
case 15:
case 16:
break;
case 17: /*
* charset-lib
*/
my_free(options->charset_dir,
MYF(MY_ALLOW_ZERO_PTR));
options->charset_dir = my_strdup(opt_arg,
MYF(MY_WME));
break;
case 18:
my_free(options->charset_name,
MYF(MY_ALLOW_ZERO_PTR));
options->charset_name = my_strdup(opt_arg,
MYF(MY_WME));
break;
case 19: /*
* Interactive-timeout
*/
options->client_flag |= CLIENT_INTERACTIVE;
break;
case 21: /*
* replication probe
*/
options->rpl_probe = 1;
break;
case 22: /*
*
enable-reads-from-master
*/
options->rpl_parse = 1;
break;
case 23: /*
* repl-parse-query
*/
options->no_master_reads = 0;
break;
default:
DBUG_PRINT("warning", ("unknown option: %s",
option[0]));
}
}
}
}
free_defaults(argv);
DBUG_VOID_RETURN;
}
/**************************************************************************
** Shut down connection
**************************************************************************/
static void
end_server(MYSQL * mysql)
{
DBUG_ENTER("end_server");
if (mysql->net.vio != 0)
{
init_sigpipe_variables DBUG_PRINT("info", ("Net: %s",
vio_description(mysql->net.vio)));
set_sigpipe(mysql);
vio_delete(mysql->net.vio);
reset_sigpipe(mysql);
mysql->net.vio = 0; /* Marker */
}
net_end(&mysql->net);
free_old_query(mysql);
DBUG_VOID_RETURN;
}
static void
free_old_query(MYSQL * mysql)
{
DBUG_ENTER("free_old_query");
if (mysql->fields)
free_root(&mysql->field_alloc, MYF(0));
init_alloc_root(&mysql->field_alloc, 8192, 0); /* Assume rowlength < 8192 */
mysql->fields = 0;
mysql->field_count = 0; /* For API */
DBUG_VOID_RETURN;
}
/*
struct charset_info_st *charset;
MYSQL_FIELD *fields;
MEM_ROOT field_alloc;
struct st_mysql_options options;
char scramble_buff[9];
Set if this is the original connection, not a master or a slave we have
added though mysql_rpl_probe() or mysql_set_master()/ mysql_add_slave()
my_bool rpl_pivot;
pointers to the master, and the next slave
connections, points to itself if lone connection
struct st_mysql* master, *next_slave;
struct st_mysql* last_used_slave; needed for round-robin slave pick
needed for send/read/store/use result to work correctly with replication
struct st_mysql* last_used_con;
} MYSQL;
*/
Regards Manny
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
