#19529 [Ana->Fbk]: Occational "Commands out of sync" errors

ID: 19529 Updated by: zak@php.net Reported By: sroussey@network54.com -Status: Analyzed +Status: Feedback Bug Type: MySQL related Operating System: Linux 2.4.18 PHP Version: 4.2.3 Assigned To: georg New Comment: A possible fix (based on discussions between Rasmus and the MySQL team) has just been committed to CVS. If the fix works praise Rasmus, if it fails, blame me! :) Please try the latest CVS version and see if the bug can be reproduced. Also, regarding the connect_timeout tests. If connect_timeout is set to -1, then we want to rely on the MySQL settings for this option, rather than attempting to override the option with a value set in PHP. Previous Comments: ------------------------------------------------------------------------ [2002-10-09 02:56:39] rasmus@php.net I think the real problem here is the order things happen in. A ROLLBACK or an AUTOCOMMIT query do not return a result set and thus do not need to be dealt with as you describe here. It is more likely that there is an existing result set that has not be explicitly freed in the PHP script and the PHP destructor for that result set is being called after the call to restore_connection_default. That's when you would get an out-of-sync error because the ROLLBACK is called with an outstanding result set. So, for those of you experiencing this problem, try explicitly calling mysql_free_result() on your result sets. ------------------------------------------------------------------------ [2002-10-08 21:10:15] sroussey@network54.com 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. ------------------------------------------------------------------------ [2002-10-07 07:02:42] valvatne@pvv.org 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] georg@php.net 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] erik@gavert.net The problem seems to have disappeared when the ROLLBACK was removed. ------------------------------------------------------------------------ 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