XF 1.5 Mysqli prepare error: Commands out of sync; you can't run this command now

[Mark E. Johnson]

Hello

I developed a XenForo website that uses database stored procedures and it works perfectly at one host but does not work at a different host. The working host is located here:

http://simracingonline.uk/test/racing/

This page loads data from results that are returned by stored procedures. This is our test site.

Then I installed the site on our Production server and it doesn’t work. I get this error when it runs the stored procedures:

An exception occurred: Mysqli prepare error: Commands out of sync; you can't run this command now

You can see the error here:

http://www.simracingonline.co.uk/racing

After spending hours and hours going through dozens of articles it seems the issue is related to the stored procedure returning multiple data sets. But this is not the case. Each procedure only returns one data set. As a test, I created a very simple stored procedure that has one statement that only returns one integer value. I get the same error message.

Both hosts use php 7.0. The test site (one.com) uses MariaDB 5.5 while the production site (fasthosts.co.uk) uses MySQL 5.6.

Can anyone offer any assistance? Thank you in advance.

Mark

 

[Mark E. Johnson]

After looking at this closer I don't think it's my stored procedure that is causing the problem. The source of the error seems to occur before my procedure is called. Here is the error stack. I'm really kind of clueless about this and could use some advice.

An exception occurred: Mysqli prepare error: Commands out of sync; you can't run this command now in /home/linweb37/s/simracingonline.co.uk-1081694400/user/htdocs/library/Zend/Db/Statement/Mysqli.php on line 77

Zend_Db_Statement_Mysqli->_prepare() in Zend/Db/Statement.php at line 115
Zend_Db_Statement->__construct() in Zend/Db/Adapter/Mysqli.php at line 381
Zend_Db_Adapter_Mysqli->prepare() in Zend/Db/Adapter/Abstract.php at line 478
Zend_Db_Adapter_Abstract->query() in Zend/Db/Adapter/Abstract.php at line 808
Zend_Db_Adapter_Abstract->fetchPairs() in XenForo/Template/Public.php at line 130
XenForo_Template_Public->_getTemplatesFromDataSource() in XenForo/Template/Abstract.php at line 572
XenForo_Template_Abstract->_loadTemplates() in XenForo/Template/Abstract.php at line 556
XenForo_Template_Abstract->_loadTemplate() in XenForo/Template/Abstract.php at line 180
XenForo_Template_Abstract->render() in XenForo/Template/Public.php at line 110
XenForo_Template_Public->render() in XenForo/ViewRenderer/HtmlPublic.php at line 140
XenForo_ViewRenderer_HtmlPublic->renderContainer() in XenForo/FrontController.php at line 639
XenForo_FrontController->renderView() in XenForo/FrontController.php at line 158
XenForo_FrontController->run() in /home/linweb37/s/simracingonline.co.uk-1081694400/user/htdocs/index.php at line 13

 

[Mike]

That error message is one of the last things the page does before being output, so I'd say it's almost certainly after your code. I also notice that the "default" pages appear to work, so I'd say with a fair amount of confidence that this is something related to your custom code.

The error you're receiving is https://dev.mysql.com/doc/refman/5.7/en/commands-out-of-sync.html That may indicate that you're not processing the results as expected. I'd note that we don't ever use stored procedures so we can't really guarantee that they work.

 

[Mark E. Johnson]

Thanks for the reply. It's interesting that it works on three different servers, two locally and one hosted. If it's something related to my custom code I would think it would not work on any server.

 

[Paul B]

That could be due to the two servers using MariaDB and MySQL.

 

[Mark E. Johnson]

Thanks, but that still makes no sense to me. It's actually working on both. MySQL 5.6 is installed on my local servers and MariaDB is installed on the host that is working. So it works with both. The host that doesn't work is MySQL 5.6. The only other thing I can think of is that the owner of the site has installed additional add-on since I started development of my add-on. Those additional add-ons are Not installed on my test servers.

Also, this might be a clue. I was able to grab the list of templates that are sent into function _getTemplateFromDataSources. The $templateList parameter contains 24 items. Then I reconstructed the query that is sent into fetchPairs using that template list. I got back only 20 results. It seems to be missing 4 rows. Not sure that is relevant.

 

[Mike]

It might be differences in actual MySQL client library (as in, what PHP is using). There could be difference in version or whether they're using mysqlnd. That's likely the extent of the guidance that we can provide though.