IONOS hosting, SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared error
Development and Programming
I am building a new site and using Structure to help me build the pages. I used to run my site on Expression Engine when it was at versions 1 and 2, but I am re-building from scratch using EE 5.3.1. I have fully working website on my local Ubuntu development server with no issues, but I have encountered an intermittent, and now common issue when loading pages:
SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared:
I will look into trying to find out if this is an EE issue or a structure issue or actually an issue where EE will not run using IONOS hosting and their MySQL set up. I am in the dark at the moment. My initial research into the issue points to increasing the table definition cache and table open cache, but when I try to do this on IONOS I get permissions issues, that I am not a SUPER user. I will post this question here to their technical department to see what they say.
Has any one encountered this issue recently and can give me any pointers/solutions please. I used to really love EE and would like to get this working. I am looking forward to future improvements with EE as it moves up to version 6, but I need to get past this issue to continue my development with the CMS.
Thank you and Kind regards, Peter
Below is the tail end of the error I am getting:
exp_channel_data_field_61 ON t.entry_id = exp_channel_data_field_61.entry_id LEFT JOIN exp_channel_data_field_62 ON t.entry_id = exp_channel_data_field_62.entry_id LEFT JOIN exp_channel_data_field_63 ON t.entry_id = exp_channel_data_field_63.entry_id WHERE t.entry_id IN (27)ORDER BY FIELD(t.entry_id, 27)
ee/legacy/database/drivers/mysqli/mysqli_connection.php:117
Stack Trace: Please include when reporting this error
ee/legacy/database/drivers/mysqli/mysqli_driver.php(112): CI_DB_mysqli_connection->query(‘SELECT t.entry…’) ee/legacy/database/DB_driver.php(270): CI_DB_mysqli_driver->_execute(‘SELECT t.entry…’) ee/legacy/database/DB_driver.php(180): CI_DB_driver->simple_query(‘SELECT t.entry…’) ee/EllisLab/Addons/channel/mod.channel.php(248): CI_DB_driver->query(‘SELECT t.entry…’) ee/legacy/libraries/Template.php(1849): Channel->entries() ee/legacy/libraries/Template.php(1484): EE_Template->process_tags() ee/legacy/libraries/Template.php(580): EE_Template->tags() ee/legacy/libraries/Template.php(236): EE_Template->parse(‘{!– ra:0000000…’, true, 1, false) ee/legacy/libraries/Template.php(1137): EE_Template->fetch_and_parse(‘Showcase’, ‘showcase-the-ho…’, true, 1) ee/legacy/libraries/Template.php(1145): EE_Template->process_sub_templates(‘{!– ra:0000000…’) ee/legacy/libraries/Template.php(628): EE_Template->process_sub_templates(‘{!– ra:0000000…’) ee/legacy/libraries/Template.php(236): EE_Template->parse(‘{!– ra:0000000…’, false, 1, false) ee/legacy/libraries/Template.php(167): EE_Template->fetch_and_parse(”, Array, false) ee/legacy/libraries/Core.php(661): EE_Template->run_template_engine(”, ”) ee/legacy/controllers/ee.php(63): EE_Core->generate_page() ee/EllisLab/ExpressionEngine/Core/Core.php(241): call_user_func_array(Array, Array) ee/EllisLab/ExpressionEngine/Core/Core.php(110): EllisLab\ExpressionEngine\Core\Core->runController(Array) ee/EllisLab/ExpressionEngine/Boot/boot.php(151): EllisLab\ExpressionEngine\Core\Core->run(Object(EllisLab\ExpressionEngine\Core\Request)) index.php(173): require_once(‘…’) index.php(173): require_once(‘…’)
It looks like I have found the solution, I have altered the file in the legacy area of the ee folder, commenting out setEmulatePrepares so that the following is not set:
$this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, $on);
This was suggested after searching through Stack Overflow :
https://stackoverflow.com/questions/10113562/pdo-mysql-use-pdoattr-emulate-prepares-or-not
First- thanks for coming back with the solution. Very much appreciated.
I’ve seen this happen 1 other time, I read the same search results you did, recommended upping the table_definition_cache. Looking at the MySQL docs, the default is -1, which means it autosizes. The minimum is 400 - see https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_table_definition_cache, and that’s what they were running with.
However, they were on a shared server and couldn’t tweak it and ended up moving servers so we didn’t delve deeply into it.
I don’t love hacking, I’m just not sure right now whether there’s a change/config that should be made in EE or whether we’ll recommend upping the table cache as a requirement or… something else. I’ll bounce it around with one of the more server savy guys.
And just for a complete record, EE is flipping that setting on/off some based on this logic:
/**
* Set emulate prepares to false for SELECT statements so as not to clash
* with ATTR_STRINGIFY_FETCHES, but keep it on for all other queries since
* some cannot run with it off.
*/
private function setEmulatePrepares($query)
{
if ($this->mysqlnd)
{
$on = strncasecmp($query, 'SELECT', 6) != 0;
$this->connection->setAttribute(PDO::ATTR_EMULATE_PREPARES, $on);
}I assume you’re running on the mysqlnd driver, yes? Otherwise that would be largely moot. And it was a select, so it should have been forced to ‘off’. Which… doesn’t make a ton of sense to me.
We’ll bat this one around and see if it’s something that needs a change and I’ll let you know how it shakes out. Thanks again for taking the time to layout what you’ve done.
HI Robin, And thank you for your reply.
The IONOS package I am using is a shared server package. I cannot change the table_definition_cache for my own set up or CMS.
No, I don’t like hacking either, but other than move to another hosting service, as this is on a shared server, there does not, at present seem to be another option using the mysqlnd driver, then to comment this out so that PDO::ATTR_EMULATE_PREPARES is set to off.
Yes, maybe in an advanced section of the CMS, on version 6, this could be changed so this option could be set to on or off for shared server hosting set ups.
Since doing the above I have had no issues.
Kind regards,
Peter
IONOS (1&1) don’t have the best reputation for hosting. I worked on an clients EE site hosted there and had numerous problems. Moving to a more compatible host solved the problem for my client.