Same problem for me with PHP 4.2.1 and MySQL 4.0.x.
mysql_query sometimes fails just after calling mysql_select_db (I'm not using mysql_db_query). (only under high query per second).
Sometimes I'm not experiencing the error message but mysql_query fails to return a valid resource identifier (and then mysql_fetch_row fails) - again first mysql_query after mysql_select_db is affected -.
I'm using pconnect with mysql.
I'm linking statically with the mysql lib provided with mysql 4.0.x.

Mysql 4.x is still beta quality. So please stick to the stable releases for now.

I don't use the MySQL 4 libs.

Additional information: this issue seems to only happen when using mysql_pconnect. I switched to mysql_connect and the issue has gone away. Seems to be an issue with persistent connections.

IMHO, it's not a good idea to use MySQL 4.x. It's certainly not a good idea to use 3.x libs to access a 4.x server. 

I agree MySQL 4.x is still in beta quality (although it works quite well), but before upgrading to php 4.2.1 I had strictly no problem with PHP / MySQL 4.x (and I'm using MySQL 4.x libs).

Sorry to reopen. I think it was changed to Bogus because of MySQL 4 usage. However, I use 3.23.49a for the server and use the built-in client software that comes with PHP 4.2.3. Other users see the same errors with 4.0.x servers. 

Essentially this is a client PHP issue.

I am now logging all mysql errors to a file so I can see how different changes affect things. As far as I can see, this condition only happens under high load and only when using pconnect.

Again, nothing to do with MySQL 4.

Last thing! I do not use transactions or any transactional table handlers. Just plain MySQL with MySQL table types.

I have been looking through CVS log at lxr.php.net to see what has changed in mysql support in PHP this last year. One change was the inclusion of mysql.connect_timeout.

In the docs it says the default is 0. This is backed up by the default:
496  mysql_globals->connect_timeout = 0;

However, I expected the line:
352  STD_PHP_INI_ENTRY_EX("mysql.connect_timeout","",...
to have ("mysql.connect_timeout","0",

Also, things seem odd to me:
651                         if (connect_timeout != -1)
749                 if (connect_timeout != -1)

Why is it checking for the connect_timeout as to -1 rather than zero? I think telling mysql that the connect timeout is zero causes the problems: the connection closes really quick! Well, it seems to be a race condition anyhow. As a default. Ick. And exploited by the time the connect is reused.

I set mysql.connect_timeout = -1 in the .ini file as a temp workaround. Seems to be working. I will wait a day and report if this indeed eliminates these errors.

Last data point: the workaround in my last message works with 3.23.x but not 4.0.x as far as I can tell. Here is my test matrix:

PHP client using built-in mysql libs.

Server:  3.23.49a  4.0.2
         --------  ----
connect     OK      OK
pconnect   fail    fail
pconnect    OK     fail
 w/ fix

I'm having this problem also.
I replaced all pconnects with connect, this makes thing work.
But the httpd processes are leaking alot of memory. Had to restart httpd after a while when it was using 900MB swap.

It looks like php is not correctly freeing query memory when there are still rows left to fetch. 
I'm running mysql 4.0.4, it was working well until I upgraded the php to 4.2.3. I have tried with both the built in mysql client lib and the 4.0.4 one.

I'm having the same problem; it appears to have started when I upgraded PHP from 4.2.2 to 4.2.3. At the same time, I upgraded Apache from 1.3.26 to 1.3.27. I wasn't using the built-in client lib, but have switched to it now... although after reading the comments here, I don't expect that to fix things.

I'd just like to emphasize that I'm experiencing this bug using stable versions of both Apache (1.3.27) and MySQL (3.23.44). PHP definitely seems to be the culprit.

Another note: sroussey@network54.com states that the problem seems to only happen at high load, and is intermittent. In my experience, it seems to happen after long periods of running. More specifically, my (production) system will run fine for hours with 30+ queries/second, and then suddenly start having several of the "Commands out of sync" error messages per second. They are still intermittent (ie. they don't appear at every query), but these symptoms seem to indicate a memory leak or other delayed-impact bug as the cause of the problems.

What happens is that the mysql connection that gets the problem is locked up. It won't ever work again.

After a while all of our mysql connections showed the problem making the site completely unusable. As of v4.2.3 php does a ROLLBACK each time connection is re-used. ROLLBACKS will give an error if updates have been done on a MyISAM table, I have removed that query now. Will see if that solves it.

It looks like all us are running quite many queries per second? I see the problem on a site running 60+ queries per second.

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.

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?

The problem seems to have disappeared when the ROLLBACK was removed.

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

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.

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.

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.

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.

Recent CVS does not help 4.0.2 over the long term (24 hours). I woke up and the log was full of the error. Will try 3.23.49a for 24 hours and report back.

As far as explicitly calling mysql_free_result(), yikes! Trying to find any missing will be a pain! 

I guess the real way to deal with this is (yuck) track all the result sets and dispose of any left over when the connection is being reset. Therefore getting the order right.

Unless we can reset the connenction last. Is there a way to order the callbacks?

PS: In this bug system, should I be using Add Comment rather than Edit Submission? I keep reseting the Status and I don't mean to. Maybe the bug system should add whatever is the current choice is to the list available.

OK, no need to wait. Getting the errors already with 3.23.49a.

I looked at the change in CVS and it only frees a memory leak from unbuffered queries.

reassigning

Interesting stuff. I am heading out on the road for about 
a month. I will follow up with this as soon as I get 
stable network access again. 

A forum I'm helping with also ran into this problem (the site runs vbulletin).  I'm trying to track down exactly when it started happening.  I can say that with apache 1.3.26 and php 4.2.1 and mysql 4.23.49, it didn't happen, nor with apache2.0.43+php4.2.3+mysql4.23.49.  It definately does happen with apache 1.3.27+php4.2.3+mysql4.23.53, leading me to believe that it's an issue with something that changed in mysql since last spring, but unless I misread, that contradicts some other reports here.  Disabling vbulletin's persistent connection usage eliminated the errors, of which there were tons.

Upgraded to PHP 4.2.3, using EZ Portal Content Server.

Linux 2.2.15

MySQL client 3.22.32

Apache 1.3.26

Was working before upgrade to PHP 4.2.3, now getting 30% 
command sync errors, this is not a heavily loaded site, 
just in dev stage. 

I have stopped httpd, mysql, and restarted, and problem is 
exhibited straight away.

Thus doubtful load or time related. 

Turned off persistant connections, problem went away.

Fix soon?

Workarounds:
A) Don't use persistent connections.
B) If you must, you need to edit PHP source, 
ext/mysql/php_mysql.c (I think). Look for a callback 
function that has ROLLBACK in it. Either disable the 
callback or comment out all the lines in it.

PHP 4.3.0 should do (B) over what is in 4.2.1. Better to 
still not have transactional garantee than to break all 
sites and then still not have transactional garantee.

My $0.02.

i did't get any error with
apache1.3.26
mod php compiled with mysql4.0.2 client lib
connect to mysql4.0.4
but once i rebuild php, with mysql4.0.4 client lib, get the same error as u
unnable to do mysql_select_db, and unnable to connect from remote, event use connect do not help(not sure)

hope this info help u guys

forgot to say, my php is 4.2.2

sorry for my wrong reporting, my problem is made by my wrong re-built, after done ./cvsclean and ./config.nice and make/make install, problem gone
:)

by now, i have to stick to 4.2.2, hope this bug(#19529) will be fix soon

Fixed in cvs/4_3 Branch.