ID: 19529
User updated by: [EMAIL PROTECTED]
Reported By: [EMAIL PROTECTED]
-Status: Feedback
+Status: Open
Bug Type: MySQL related
Operating System: Linux 2.4.18
PHP Version: 4.2.3
Assigned To: georg
New Comment:
MySQL is complaining about things not being cleaned up. This is because
any query that returns results (which one's don't -- any?) must get
them.
In case of an unbuffered query, we need to eat the rest of the rows
before exiting (like we do when a new query is run when an old
unbuffered query was not finished). I removed the warning in this case,
but you all can change it as you please.
The case that is hitting me (and EVERYONE out there using persistent
connections with current revs) is that there is a rollback. But there
is nothing to get the results of the rollback. This means, that the
next script to use this persistent connection will generally fail on
the first query, but might do alright on the others. For this reason, I
recommend anyone use PHP 4.2.3 (maybe earlier versions as well) to turn
off persistent connections for now.
Also, in CVS there is something to reset AUTOCOMMIT. It also did not
clean up and that causes additional issues. I removed it rather than
fixed it. Who says AUTOCOMMIT=1 should be the default for a certain
server? That is user configurable on the server side. Personally, I
like the idea of resetting all the variables that might have been
changed (including that one). There are a lot. No good way to do it
right now.
Oh, what else? Ah.. the code in CVS for mysql.connection.timeout. That
rocks! However, it sets the default to zero and then checks for -1 as a
sign not to include the option. Oops. So the default timeout value is
set to nothing when the user doesn't do anything. That is a
unpredictable change in behavior.
I have some changes below. My first time even looking at this code, so
look for any mistakes.
static int _restore_connection_defaults(zend_rsrc_list_entry *rsrc
TSRMLS_DC)
{
php_mysql_conn *link;
char query[128];
char user[128];
char passwd[128];
/* check if its a persistent link */
if (Z_TYPE_P(rsrc) != le_plink)
return 0;
link = (php_mysql_conn *) rsrc->ptr;
if (link->active_result_id) do {
int type;
MYSQL_RES *mysql_result;
mysql_result = (MYSQL_RES *)
zend_list_find(link->active_result_id, &type);
if (mysql_result && type==le_result) {
if (!mysql_eof(mysql_result)) {
while (mysql_fetch_row(mysql_result));
}
zend_list_delete(link->active_result_id);
link->active_result_id = 0;
}
} while(0);
/* rollback possible transactions */
strcpy (query, "ROLLBACK");
if (mysql_real_query(&link->conn, query, strlen(query)) !=0 ) {
MYSQL_RES *mysql_result=mysql_store_result(&link->conn);
mysql_free_result(mysql_result);
}
/* unset the current selected db */
#if MYSQL_VERSION_ID > 32329
strcpy (user, (char *)(&link->conn)->user);
strcpy (passwd, (char *)(&link->conn)->passwd);
mysql_change_user(&link->conn, user, passwd, "");
#endif
return 0;
}
And change the two copies of this:
if (connect_timeout != -1)
to
if (connect_timeout <= 0)
My 2 cents for the day.
Previous Comments:
------------------------------------------------------------------------
[2002-10-07 07:02:42] [EMAIL PROTECTED]
Removing the ROLLBACK seems to have fixed the problem for me too.
Reading the MySQL docs on the error message in question, it would seem
that just adding a mysql_free_result call before executing the ROLLBACK
query might fix things. I noticed that the PgSQL extension does
something similar when rolling back transactions at shutdown.
------------------------------------------------------------------------
[2002-10-06 14:51:51] [EMAIL PROTECTED]
Currently, neither mysql 4.x or 3.x supports enough functionality for
handling some problems when using persistent connections, e.g.
restoring session variables to global variables,
restoring auto_commit, unsetting user variables etc.
The probably error is not MySQL-version dependend. The 4.x clientlib is
100% backwards compatible to MySQL 3.x (> .23).
For some more information, it would be useful, if you could send me
some sources...
assigned to myself.
Georg
------------------------------------------------------------------------
[2002-10-06 12:23:42] [EMAIL PROTECTED]
The problem seems to have disappeared when the ROLLBACK was removed.
------------------------------------------------------------------------
[2002-10-06 11:02:28] [EMAIL PROTECTED]
Scratch the above; I was looking at the current CVS version. In 4.2.3
the function is still called _rollback_mysql_transactions and only does
the ROLLBACK, nothing more.
I just disabled the ROLLBACK as well, so if neither Erik nor myself see
any more errors, I think it's safe to assume that's where the problem
is. But shouldn't it always be safe to execute a rollback in MySQL,
even if transactions aren't in use?
------------------------------------------------------------------------
[2002-10-06 10:14:36] [EMAIL PROTECTED]
I agree that the most plausible cause of the problem is in the
_restore_connection_defaults function, which is the one responsible for
doing the ROLLBACK. That theory would seem to be supported by the fact
that the problem disappears when disabling persistent connections
(since the function does nothing when the connection isn't
persistent).
What I don't get is why executing ROLLBACK on a straight-MyISAM
database would cause problems. Is that a bug in MySQL?
If the ROLLBACK isn't the problem, it would have to be either the SET
AUTOCOMMIT=1 or the stuff about unsetting the selected DB. Since my
application uses only one DB and no transactions, I'm going to just
disable the entire function and recompile.
------------------------------------------------------------------------
The remainder of the comments for this report are too long. To view
the rest of the comments, please view the bug report online at
http://bugs.php.net/19529
--
Edit this bug report at http://bugs.php.net/?id=19529&edit=1
