SQLSTATA[HY000]: General error: 1615 Prepared statment Needs to be re-prepared

[Saharel001]

sysPass Version
3.0.0-rc8 (300.18122601)

Describe the bug
SQLSTATA[HY000]: General error: 1615 Prepared statment Needs to be re-prepared

To Reproduce
Steps to reproduce the behavior:

1. Go to 'url'
2. Logon ''
3. Error on mainpage
4. Error on "Items and Customizations" tab.

All other looks fine…..

Event log
2019-01-03 06:49:22 [EXCEPTION] [N/A] (P) SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared
#0 /var/www/html/syspass/lib/SP/Storage/Database/Database.php(234): PDOStatement->execute()
#1 /var/www/html/syspass/lib/SP/Storage/Database/Database.php(174): SP\Storage\Database\Database->prepareQueryData(Object(SP\Storage\Database\QueryData))
#2 /var/www/html/syspass/lib/SP/Storage/Database/Database.php(135): SP\Storage\Database\Database->doQuery(Object(SP\Storage\Database\QueryData))
#3 /var/www/html/syspass/lib/SP/Repositories/Account/AccountRepository.php(572): SP\Storage\Database\Database->doSelect(Object(SP\Storage\Database\QueryData), true)
#4 /var/www/html/syspass/lib/SP/Services/Account/AccountService.php(697): SP\Repositories\Account\AccountRepository->search(Object(SP\DataModel\ItemSearchData))
#5 /var/www/html/syspass/app/modules/web/Controllers/ItemManagerController.php(221): SP\Services\Account\AccountService->search(Object(SP\DataModel\ItemSearchData))
#6 /var/www/html/syspass/app/modules/web/Controllers/ItemManagerController.php(111): SP\Modules\Web\Controllers\ItemManagerController->getAccountsList()
#7 /var/www/html/syspass/app/modules/web/Controllers/ItemManagerController.php(72): SP\Modules\Web\Controllers\ItemManagerController->getGridTabs()
#8 [internal function]: SP\Modules\Web\Controllers\ItemManagerController->indexAction()
#9 /var/www/html/syspass/lib/SP/Bootstrap.php(234): call_user_func_array(Array, Array)
#10 [internal function]: SP\Bootstrap->SP{closure}(Object(Klein\Request), Object(Klein\Response), Object(Klein\ServiceProvider), Object(Klein\App), Object(Klein\Klein), Object(Klein\DataCollection\RouteCollection), Array)
#11 /var/www/html/syspass/vendor/klein/klein/src/Klein/Klein.php(886): call_user_func(Object(Closure), Object(Klein\Request), Object(Klein\Response), Object(Klein\ServiceProvider), Object(Klein\App), Object(Klein\Klein), Object(Klein\DataCollection\RouteCollection), Array)
#12 /var/www/html/syspass/vendor/klein/klein/src/Klein/Klein.php(588): Klein\Klein->handleRouteCallback(Object(Klein\Route), Object(Klein\DataCollection\RouteCollection), Array)
#13 /var/www/html/syspass/lib/SP/Bootstrap.php(456): Klein\Klein->dispatch(Object(Klein\Request))
#14 /var/www/html/syspass/lib/Base.php(75): SP\Bootstrap::run(Object(DI\Container))
#15 /var/www/html/syspass/index.php(28): require('/var/www/html/s...')
#16 {main}

Platform (please complete the following information):
SERVER_VERSION : 10.1.20-MariaDB
CLIENT_VERSION : 10.1.20-MariaDB
SERVER_INFO : Uptime: 65923 Threads: 1 Questions: 249930 Slow queries: 0 Opens: 479 Flush tables: 1 Open tables: 415 Queries per second avg: 3.791
Apache/2.4.6 (CentOS) OpenSSL/xxxxxxx PHP/7.2.12

Erweiterungen: Core, date, libxml, openssl, pcre, zlib, filter, hash, Reflection, SPL, session, standard, apache2handler, bz2, calendar, ctype, curl, dom, mbstring, fileinfo, ftp, gd, gettext, gmp, iconv, json, ldap, exif, mysqli, PDO, pdo_mysql, pdo_sqlite, Phar, shmop, SimpleXML, sockets, sqlite3, tokenizer, xml, wddx, xmlreader, xmlwriter, xsl, zip, Zend OPcache
Nicht verfügbare Erweiterungen:
Verwendeter Speicher: 4096 KB

[nuxsmin]

Hello, I've faced same issue when running sysPass on a hosting environment, and it seems that PHP's MySQL driver needs to raise the max open tables counter.

Regards

[Saharel001]

mariadb 10.1.20
Default table_open_cache is 400

i use this guide to fix it...
http://techinfobest.com/optimize-mysql-table_open_cache/

THX

[Saharel001]

Problem came back, after deep analyse i found that mariadb variable table_definition_cache ist set to 400 to.
But in my case Open_table_definitions was 416.

The SQL command
SET GLOBAL table_definition_cache = 1024;

fix it.... hopefully forever ;)

[ghost]

I am experiencing exactly the same problem. It makes me wonder because I don not have a large database.

[ghost]

FLUSH TABLES solves this. I guess this should happen on a regular basis?

[nuxsmin]

@jucajuca this issue is mostly related to a hosting provider configuration about open tables limit, that is frequently reached when running apps on shared environments.

[HugoHeneault]

Hi there,

Same issue here, but I can't change the table_definition_cache parameter on my hoster.
So I found this solution but I don't know where I could add the PDO options.

Any ideas?

Thanks

[HugoHeneault]

Found it!

In lib/SP/Storage/Database/MySQLHandler.php I forced ATTR_EMULATE_PREPARES to true everywhere I found it.

No more issues.

[visionpointmarketing]

@HugoHeneault I tried you solution and still get the error. I'm trying to setup on a shared hosting environment on SiteGround. Did you have to take any other steps besides changing the ATTR_EMULATE_PREPARES attribute to true? Did you reinstall with this update or just make the change to the file? Any additional details would be appreciated.

Thanks!

[nuxsmin]

@visionpointmarketing hello, you don't need to reinstall after setting the parameter.

Unfortunately, this is a known issue running on hosting providers (sysPass demo site has been configured in this way) and using the native MySQL query handler.

Regards

[adundovi]

I hit the same error. I installed SysPass both with "Shared hosting" and without. The result is the same. Tried also all proposed solutions (increasing all mariadb limits, ATTR_EMULATE_PREPARES) and nothing worked. Only flushing tables worked, temporary.

PHP message: [EXCEPTION] [N/A] SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared
#0 /var/www/syspass/httpdocs/lib/SP/Storage/Database/Database.php(234): PDOStatement->execute()
#1 /var/www/syspass/httpdocs/lib/SP/Storage/Database/Database.php(174): SP\Storage\Database\Database->prepareQueryData(Object(SP\Storage\Database\QueryData))
#2 /var/www/syspass/httpdocs/lib/SP/Storage/Database/Database.php(135): SP\Storage\Database\Database->doQuery(Object(SP\Storage\Database\QueryData))
#3 /var/www/syspass/httpdocs/lib/SP/Repositories/Account/AccountRepository.php(716): SP\Storage\Database\Database->doSelect(Object(SP\Storage\Database\QueryData), true)
#4 /var/www/syspass/httpdocs/lib/SP/Services/Account/AccountSearchService.php(154): SP\Repositories\Account\AccountRepository->getByFilter(Object(SP\Services\Account\AccountSearchFilter), Object(SP\Mvc\Model\QueryCondition))
#5 /var/www/syspass/httpdocs/app/modules/web/C...
PHP message: [EXCEPTION] [N/A] SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared
#0 /var/www/syspass/httpdocs/lib/SP/Storage/Database/Database.php(174): SP\Storage\Database\Database->prepareQueryData(Object(SP\Storage\Database\QueryData))
#1 /var/www/syspass/httpdocs/lib/SP/Storage/Database/Database.php(135): SP\Storage\Database\Database->doQuery(Object(SP\Storage\Database\QueryData))
#2 /var/www/syspass/httpdocs/lib/SP/Repositories/Account/AccountRepository.php(716): SP\Storage\Database\Database->doSelect(Object(SP\Storage\Database\QueryData), true)
#3 /var/www/syspass/httpdocs/lib/SP/Services/Account/AccountSearchService.php(154): SP\Repositori

[morevsky]

Hello. Is there any solution to this problem? Thanks!

[KadotyGamer]

Any update on this, I am having the same issue. I have to do FLUSH TABLES; each time to get it to work and this is only temporary. I have also done all the steps above. I am hosting it on a dedicated server with Plesk.

[budus]

I have tried the solution of @HugoHeneault and it work for me.

Change the file "/httpdocs/pm/lib/SP/Storage/Database/MySQLHandler.php":

1. At Line 42 change
   PDO::ATTR_EMULATE_PREPARES => false,
   to
   PDO::ATTR_EMULATE_PREPARES => true,

2. At line 114 change
   $this->db->setAttribute(PDO::ATTR_EMULATE_PREPARES, version_compare($serverVersion, '5.1.17', '<'));
   to
   $this->db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

I am not sure this solution don't produce other problems, but for this issue seems to work.

[diakourtis]

PDO::ATTR_EMULATE_PREPARES, true Return all numbers as integers. So, the whole system falls as the calculations are wrong. I am still looking to find a solution if someone figure it out!

[nuxsmin]

Quite old but, some light on this topic:

• https://stackoverflow.com/a/10455228
• Tuples Insertion with float & double values lose the decimal portion laravel/framework#23850 (comment)
• https://stackoverflow.com/a/53392034

To sum up, PDO::ATTR_EMULATE_PREPARES should be set to false on newer versions of MariaDB/MySQL, because it will use the query cache for "known" queries, so query time would be faster. I've had some issues with hosting providers, in which the table cache was full, and ongoing queries failed to be processed.

[CodeNinja1337]

@nuxsmin, This change only resolves this issue for me.

Change the file "/httpdocs/pm/lib/SP/Storage/Database/MySQLHandler.php" on line 114 from
$this->db->setAttribute(PDO::ATTR_EMULATE_PREPARES, version_compare($serverVersion, '5.1.17', '<'));
to
$this->db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);

[nuxsmin]

Thanks for your contribution @CodeNinja1337 .

This change needs to be performed with care because:

https://stackoverflow.com/a/10455228 and https://stackoverflow.com/a/58830039

So it should be merged and tested within the next release branch (v3.2) instead.

[KadotyGamer]

Is there anything I need to do to avoid this error? I am currently running the latest version of v3.2 and I am stilling getting this error:

SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared

Flush tables; does fix it but is temporary

I am running MariaDB with Plesk