Re: MYSQL_OPT_RECONNECT

On Mon, 2012-07-09 at 16:49 +0400, Alexey Zakhlestin wrote: > On 09.07.2012, at 14:17, Johannes Schlüter wrote: > > > an example like this: > > > > $pdo = new PDO("mysql:..."); > > $select = $pdo->prepare("SELECT * FROM t WHERE id = ?"); > > /* do something ... connection break in between */ > > $delete = $pdo->prepare("DELETE FROM t WHERE id = ?"); > > $select->execute([1]); > > > > This will in fact do a DELETE, not a SELECT as the statement handle > > effectively is nothing but a per-connection counted unsigned long. > > Well, this sounds like a bug > > Prepared statements should become invalid once connection is lost and > further attempts to execute them should lead to exception Nothing PHP can do about.*) The reconnect, if requested, happens silently. I've attached a simple test program (warning: not a good example for libmysql usage, missing some error handling, leaking everything etc.) demonstrating this behavior. Compile it using something like gcc mysql_config --cflags --libs mysql_reconnect_ps.c after editing username and password for your setup. If you comment out the mysql_kill() call the first statement will be executed, if you leave it as is the second will be executed. johannes *) Well there's one thing PHP could do - check the thread-id for every operation, if it changed there was a reconnect -- but better not use silent reconnects at all ...

#include <unistd.h> #include <mysql.h> #include <stdio.h> void main() { MYSQL_STMT *stmt1, *stmt2; my_bool my_true = 1; MYSQL *mysql = mysql_init(NULL); mysql_options(mysql, MYSQL_OPT_RECONNECT, &my_true); if (!mysql_real_connect(mysql, "localhost", "USER", "PASS", "test", 0, NULL, 0 )) { fprintf(stderr, "mysql_real_connect() failed: %s", mysql_error(mysql)); return; } stmt1 = mysql_stmt_init(mysql); mysql_stmt_prepare(stmt1, "SELECT 1", sizeof("SELECT 1")-1); mysql_kill(mysql, mysql_thread_id(mysql)); stmt2 = mysql_stmt_init(mysql); mysql_stmt_prepare(stmt2, "SELECT 2 UNION SELECT 3", sizeof("SELECT 2 UNION SELECT 3")-1); if (mysql_stmt_execute(stmt1)) { fprintf(stderr, "Error: %s\n", mysql_stmt_error(stmt1)); return; } mysql_stmt_store_result(stmt1); printf("got %i rows\n", mysql_stmt_num_rows(stmt1)); }