Issues

ZF-1842: Commands out of sync error

Description

Reportedly there is a regression failure in the Mysqli adapter in ZF 1.0.1. Report from Al Briggs:

In release 1.0.1 I have found that I get errors on some pages of my application with mysql.

The Mysql error is : Commands out of sync.

We cannot reproduce this in our development environment so the mysqli version maybe important.

However using a previous version of mysqli (attached) and there are no errors.

I am not sure but this may be related to ZF 1780 which seems to have changed the way cursors are used.

Comments

Contacted user for more details about a reproducable test case. What is his application is doing when he sees this error?

In general, MySQL client (regardless of programming interface) doesn't like it when you have two cursors open.

Here is the code that is causing the error, error is caused at line: $topic = new Topic();:


$db = Zend_Registry::get('dbAdapter');
$testObj = new Test();
$tests = $testObj->fetchAll("USERS_ID =".$this->view->user->ID );

    $testArray = array();
    //loop through all of the topics
    foreach ($tests as $test) {     
        $tDHelper = new TestDisplayHelper();

        $tDHelper->testSource =$test->TEST_SOURCE;

        $query = "SELECT COUNT(answers.ID) AS NUMBER_CORRECT_ANSWERS FROM questions, answers WHERE
            questions.ID = answers.QUESTIONS_ID AND
            answers.TESTS_ID = $test->ID AND
            CORRECT_YN = 1";
        $row = $db->fetchRow($query);   
        $tDHelper->correctAnswers= $row['NUMBER_CORRECT_ANSWERS'];  
        $query = "SELECT COUNT(answers.ID) AS NUMBER_ANSWERS FROM questions, answers WHERE
            questions.ID = answers.QUESTIONS_ID AND
            answers.TESTS_ID = $test->ID";
        $row = $db->fetchRow($query);   
        $tDHelper->ID = $test->ID;
        $tDHelper->totalQuestions= $row['NUMBER_ANSWERS'];  
        $tDHelper->testDate = new Zend_Date($test->TEST_DTM,'YYYY-MM-dd HH:mm:ss');
        array_push($testArray,$tDHelper);
    }       
    $this->view->testList = $testArray;     

    $arrData = Array();
    //used to place items in the data array
    $i=0;
    //loop through all of the topics
    $topic = new Topic();

I have this problem, too. I instantiate 6 or 7 Zend_Db_Tables in one php page. I know this is an missing mysqli::free_result everywhere in Zend_Db_Adapter_Mysqli. But i didn't found the right place to insert it without loosing my results.

Hi,

I'm trying to reproduce this error but I am not having positive results. I mean, it works for me and the error doesn't occur.

Here is my environment, reported from "php -i" * Windows XP * PHP 5.2.3 (May 31 2007) * MySQL client library 5.0.37 * Xdebug v2.0.0

Can you please specify what version of operating system, PHP, and MySQL client you use in the environment where you see the errors? Also please specify the versions of your other environment that does not produce the errors.

Linking to ZF-1780 which may be related.

Hi Bill,

here is m php environment:

System => Linux equinox 2.6.15-26-386 #1 PREEMPT Thu Aug 3 02:52:00 UTC 2006 i686 Build Date => Jul 19 2006 00:05:59 Server API => Command Line Interface Virtual Directory Support => disabled Configuration File (php.ini) Path => /etc/php5/cli/php.ini PHP API => 20041225 PHP Extension => 20050922 Zend Extension => 220051025 Debug Build => no Thread Safety => disabled Zend Memory Manager => enabled IPv6 Support => enabled mysql Ver 14.12 Distrib 5.0.22, for pc-linux-gnu (i486) using readline 5.1

It is an Kubuntu 6.06.1, but i have reproduce this error on an Windows XP machine with actual xampp, too. This problem is describet in the mysql manual. When i use the mysqli::free_result method, i can instantiate hundrets off Zend_Db_Table objects without this error (only the script runtime is the limit). But however, i didn't found the right place for mysqli::free_result without removing the fetched results. This error is when the adapter fetches the table infos or later when fetching data after an query. If i use Zend_Cache, this problem comes later.

My example:

Zend_Db_Table_Abstract::setDefaultAdapter($dbAdapter); $table1 = new Zend_Db_Table_Extend1(); $table2 = new Zend_Db_Table_Extend2(); $table3 = new Zend_Db_Table_Extend3(); $table4 = new Zend_Db_Table_Extend4(); $table5 = new Zend_Db_Table_Extend5(); $table6 = new Zend_Db_Table_Extend6(); // error here $table7 = new Zend_Db_Table_Extend7(); // or here

All Zend_Db_Tables are different mysql tables (MyISAM).

Sorry, the php version is missing. It is an 5.1.2.

I got the same error in the production environment. The application was running properly in my development server.

  • Production:

PHP Version 5.2.5 System Linux [SERVER] 2.6.9-023stab044.11-enterprise #1 SMP Sun Sep 30 12:15:39 MSD 2007 i686
Build Date Jan 9 2008 11:33:11
Server API Apache 2.0 Handler
Virtual Directory Support disabled
PHP API 20041225
PHP Extension 20060613
Zend Extension 220060519
Thread Safety disabled
Zend Memory Manager enabled

MySQL - 4.1.20

Apache Version Apache
Apache API Version 20020903

MysqlI Support enabled Client API library version 4.1.20
Client API header version 4.1.20
mysqli.max_links 32 mysqli.reconnect Off

  • Development (WAMP5):

PHP Version 5.2.1 System Windows NT [SERVER] 5.1 build 2600 (Windows XP Professional SP2) Build Date Feb 7 2007 23:10:31
Server API Apache 2.0 Handler
Virtual Directory Support enabled
PHP API 20041225
PHP Extension 20060613
Zend Extension 220060519
Thread Safety enabled
Zend Memory Manager enabled

MySQL 5.0.27-community-nt

Apache Version Apache/2.2.4 (Win32) PHP/5.2.1
Apache API Version 20051115

MysqlI Support enabled Client API library version 5.0.22
Client API header version 5.0.22
mysqli.max_links -1 mysqli.reconnect Off

The issue is probably to do with the MySql version. I'm trying to update to 5...

This issue should have been fixed for the 1.5 release.

Please categorize/fix as needed.

This doesn't appear to have been fixed in 1.5.0. Please update if this is not correct.

I think for this has been included in 1.6 (presumably, the issue is that stored procedures are being called and there is an empty result set still in the buffer).

In 1.6, the fix introduced was:

http://framework.zend.com/code/browse/…

This has cleared up my issues with "out of sync" exceptions.

I am inclined to close this issue, unless there is a compelling use case that would suggest otherwise.

I believe there is another workaround that is probably better practice- close your statement and connection. This is an error that comes up intermittently for mysqli if the connection isn't closed, from what I understand.

What a silly thing to say Mr Sinclair!

The statements are handled by the framework and it is the framework that should handle closing and anything else with the connections.

For me this issue is closed and the bug was fixed a long time ago - but it was not down to bad practice by developers - but by a bug that existed in the 1.0.1 release - now thankfully fixed.