Bug #82686

MySQL error when running OptimizeDatabaseTableTask with pdo_mysql driver

Added by Alexander Stehlik over 2 years ago. Updated 3 months ago.

Status:
Closed
Priority:
Should have
Category:
scheduler
Target version:
-
Start date:
2017-10-06
Due date:
% Done:

100%

TYPO3 Version:
8
PHP Version:
7.1
Tags:
Complexity:
Is Regression:
Sprint Focus:

Description

When using the pdo_mysql database driver you get the following error when you execute the Optimize MySQL database tables (scheduler) task:

SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

It works fine with the mysqli driver.

Associated revisions

Revision a887df69 (diff)
Added by Manuel Selbach 5 months ago

[BUGFIX] Fix OptimizeDatabaseTableTask for PDO and MySQLi

With this change $connection::query() is used instead of
$connection::exec() which can not be used for statements
that return results, see:

https://www.php.net/manual/de/pdo.exec.php#61702

Resolves: #82686
Releases: master, 9.5, 8.7
Change-Id: Ic4b2cc8caec0bc74ffa730b09d03ca0009ca1751
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/62841
Tested-by: Benni Mack <>
Tested-by: TYPO3com <>
Tested-by: Andreas Fernandez <>
Reviewed-by: Benni Mack <>
Reviewed-by: Andreas Fernandez <>

Revision a0f12a52 (diff)
Added by Manuel Selbach 5 months ago

[BUGFIX] Fix OptimizeDatabaseTableTask for PDO and MySQLi

With this change $connection::query() is used instead of
$connection::exec() which can not be used for statements
that return results, see:

https://www.php.net/manual/de/pdo.exec.php#61702

Resolves: #82686
Releases: master, 9.5, 8.7
Change-Id: Ic4b2cc8caec0bc74ffa730b09d03ca0009ca1751
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/62805
Tested-by: TYPO3com <>
Tested-by: Andreas Fernandez <>
Reviewed-by: Andreas Fernandez <>

Revision 710bf8cd (diff)
Added by Manuel Selbach 5 months ago

[BUGFIX] Fix OptimizeDatabaseTableTask for PDO and MySQLi

With this change $connection::query() is used instead of
$connection::exec() which can not be used for statements
that return results, see:

https://www.php.net/manual/de/pdo.exec.php#61702

Resolves: #82686
Releases: master, 9.5, 8.7
Change-Id: Ic4b2cc8caec0bc74ffa730b09d03ca0009ca1751
Reviewed-on: https://review.typo3.org/c/Packages/TYPO3.CMS/+/62806
Tested-by: TYPO3com <>
Tested-by: Andreas Fernandez <>
Reviewed-by: Andreas Fernandez <>

History

#1 Updated by Alexander Stehlik over 2 years ago

I tried several things but I could not yet find a solution :(

This is what I tried so far:

Use MYSQL_ATTR_USE_BUFFERED_QUERY

Checking if current connection uses PDO and setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

Does not have any effect.

Set MYSQL_ATTR_USE_BUFFERED_QUERY in localconf

Setting this in the default database connection properties did not help either:

'driverOptions' => [
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
],

Close connection

Closing the connection after each OPTIMISE request. Fixes the error but causes MySQL warnings:

[Note] Aborted connection 241 to localhost: 'typo3_master' user: 'typo3_master' host: '172.0.0.1' (Got an error reading communication packets)

Conclusion

I'm switching to the mysqli driver for now for using this task.

Maybe someone with more PDO / MySQL skills finds a solution.

#2 Updated by Joshua Westerheide over 2 years ago

  • TYPO3 Version changed from 9 to 8

any updates on this?

We can reproduce it in TYPO3 8.7.10

When running the scheduler task the following exception gets thrown:

Session with id 074f03694368adedd7c0b24fa7ce6004 could not be updated: An exception occurred while executing 
'UPDATE `be_sessions` SET `ses_data` = ?, `ses_id` = ?, `ses_tstamp` = ? WHERE `ses_id` = ?' 
with params ["a:3:{s:26:\"formProtectionSessionToken\";s:64:\"8fa5a310289f86f08ceea9c30d4586c2a58dfddbe9be2c58988d147099c0c0a1\";s:31:\"TYPO3\\CMS\\Recordlist\\RecordList\";a:1:{s:12:\"search_field\";N;}s:27:\"core.template.flashMessages\";a:1:{i:0;O:37:\"TYPO3\\CMS\\Core\\Messaging\\FlashMessage\":6:{s:17:\"\u0000*\u0000storeInSession\";b:1;s:10:\"\u0000*\u0000classes\";a:5:{i:-2;s:6:\"notice\";i:-1;s:4:\"info\";i:0;s:7:\"success\";i:1;s:7:\"warning\";i:2;s:6:\"danger\";}s:8:\"\u0000*\u0000icons\";a:5:{i:-2;s:11:\"lightbulb-o\";i:-1;s:4:\"info\";i:0;s:5:\"check\";i:1;s:11:\"exclamation\";i:2;s:5:\"times\";}s:8:\"\u0000*\u0000title\";s:0:\"\";s:10:\"\u0000*\u0000message\";s:535:
\"Execution of task \"Optimize MySQL database tables (scheduler)\" failed with the following message: 
An exception occurred while executing 'SELECT `serialized_executions` FROM `tx_scheduler_task` WHERE `uid` = ?' with params [10]:\n\nSQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.\";s:11:\"\u0000*\u0000severity\";i:2;}}}", "074f03694368adedd7c0b24fa7ce6004", 1519375207, "074f03694368adedd7c0b24fa7ce6004"]: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

#3 Updated by Eric Chavaillaz 5 months ago

Same here with TYPO3 9.5.13...

#4 Updated by Gerrit Code Review 5 months ago

  • Status changed from New to Under Review

Patch set 1 for branch master of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/62841

#5 Updated by Gerrit Code Review 5 months ago

Patch set 1 for branch 9.5 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/62805

#6 Updated by Gerrit Code Review 5 months ago

Patch set 1 for branch TYPO3_8-7 of project Packages/TYPO3.CMS has been pushed to the review server.
It is available at https://review.typo3.org/c/Packages/TYPO3.CMS/+/62806

#7 Updated by Manuel Selbach 5 months ago

  • Status changed from Under Review to Resolved
  • % Done changed from 0 to 100

#8 Updated by Manuel Selbach 5 months ago

  • Assignee set to Manuel Selbach

#9 Updated by Benni Mack 3 months ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF