Newsgroups: php.internals
Path: news.php.net
Xref: news.php.net php.internals:61117
Mailing-List: contact internals-help@lists.php.net; run by ezmlm
Received-SPF: error (pb1.pair.com: domain schlueters.de from 217.114.211.66 cause and error)
To: Alexey Zakhlestin <indeyets@gmail.com>
Cc: Rasmus Lerdorf <rasmus@lerdorf.com>, PHP internals
 <internals@lists.php.net>
In-Reply-To: <5A04F34F-A25C-43D7-A1A5-DA29976C91D4@gmail.com>
References: <4FF853C6.4030503@lerdorf.com>
	 <1341829022.3766.19.camel@guybrush>
	 <5A04F34F-A25C-43D7-A1A5-DA29976C91D4@gmail.com>
Content-Type: multipart/mixed; boundary="=-R6T2XTCYTT3h3Ts2mN1H"
Date: Mon, 09 Jul 2012 15:54:05 +0200
Message-ID: <1341842045.3766.28.camel@guybrush>
Mime-Version: 1.0
Subject: Re: [PHP-DEV] MYSQL_OPT_RECONNECT
From: johannes@schlueters.de (Johannes =?ISO-8859-1?Q?Schl=FCter?=)

--=-R6T2XTCYTT3h3Ts2mN1H
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 8bit

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 ...

--=-R6T2XTCYTT3h3Ts2mN1H
Content-Disposition: attachment; filename="mysql_reconnect_ps.c.txt"
Content-Type: text/plain; name="mysql_reconnect_ps.c.txt"; charset="UTF-8"
Content-Transfer-Encoding: 7bit

#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));
}

--=-R6T2XTCYTT3h3Ts2mN1H--