Bug #82686: MySQL error when running OptimizeDatabaseTableTask with pdo_mysql driver - TYPO3 Core - TYPO3 Forge

Custom queries

Accessibility
Easy tasks
Forge Triage
JavaScript issues
JavaScript Issues (w/o Epics/Stories)
My open issues
No feedback within 90 days
Open Bugs
Open issues of 10
Recently modified
Regressions
Stabilization Issues
Usability or UX

Watchers (2)

J W
Tim Schreiner

Actions

Copy link

Bug #82686

closed

MySQL error when running OptimizeDatabaseTableTask with pdo_mysql driver

Added by Alexander Stehlik about 7 years ago. Updated over 4 years ago.

Status:

Closed

Priority:

Should have

Assignee:

Manuel Selbach

Category:

scheduler

Target version:

Start date:

2017-10-06

Due date:

% Done:

100%

Estimated time:

TYPO3 Version:

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.

History
Notes
Property changes
Associated revisions

Actions

Copy link

Updated by Alexander Stehlik about 7 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.

Actions

Copy link

Updated by J W over 6 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.

Actions

Copy link

Updated by Eric Chavaillaz almost 5 years ago

Same here with TYPO3 9.5.13...

Actions

Copy link

Updated by Gerrit Code Review almost 5 years 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

Actions

Copy link

Updated by Gerrit Code Review almost 5 years 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

Actions

Copy link

Updated by Gerrit Code Review almost 5 years 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

Actions

Copy link