Actions
Bug #104296
closed
An exception occurred while executing a query: Out of range value for column 't3ver_state' at row 1
Status:
Closed
Priority:
Should have
Assignee:
-
Category:
Database API (Doctrine DBAL)
Target version:
-
Start date:
2024-07-02
Due date:
% Done:
0%
Estimated time:
TYPO3 Version:
13
PHP Version:
8.3
Tags:
Complexity:
Is Regression:
Sprint Focus:
Description
I've updated from 13.1.x to TYPO3 13.2.1 and executed the reference update as requested. This leads to the following error:
[ERROR] An exception occurred while executing a query: Out of range value for column 't3ver_state' at row 1
Full output:
Update index of table sys_file_reference
----------------------------------------
0/51 [░░░░░░░░░░░░░░░░░░░░░░░░░░░░] 0% < 1 sec/< 1 sec 24.0 MiB
In ExceptionConverter.php line 90:
[Doctrine\DBAL\Exception\DriverException (1264)]
An exception occurred while executing a query: Out of range value for column 't3ver_state' at row 1
Exception trace:
at /var/projects/own/daniel-siepmann/project/vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php:90
Doctrine\DBAL\Driver\API\MySQL\ExceptionConverter->convert() at /var/projects/own/daniel-siepmann/project/vendor/doctrine/dbal/src/Connection.php:1364
Doctrine\DBAL\Connection->handleDriverException() at /var/projects/own/daniel-siepmann/project/vendor/doctrine/dbal/src/Connection.php:1306
Doctrine\DBAL\Connection->convertExceptionDuringQuery() at /var/projects/own/daniel-siepmann/project/vendor/doctrine/dbal/src/Connection.php:871
Doctrine\DBAL\Connection->executeStatement() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-core/Classes/Database/Query/BulkInsertQuery.php:188
TYPO3\CMS\Core\Database\Query\BulkInsertQuery->execute() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-core/Classes/Database/Connection.php:217
TYPO3\CMS\Core\Database\Connection->bulkInsert() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-core/Classes/Database/ReferenceIndex.php:434
TYPO3\CMS\Core\Database\ReferenceIndex->updateRefIndexTable() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-core/Classes/Database/ReferenceIndex.php:349
TYPO3\CMS\Core\Database\ReferenceIndex->updateIndex() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-backend/Classes/Command/ReferenceIndexUpdateCommand.php:66
TYPO3\CMS\Backend\Command\ReferenceIndexUpdateCommand->execute() at /var/projects/own/daniel-siepmann/project/vendor/symfony/console/Command/Command.php:279
Symfony\Component\Console\Command\Command->run() at /var/projects/own/daniel-siepmann/project/vendor/symfony/console/Application.php:1047
Symfony\Component\Console\Application->doRunCommand() at /var/projects/own/daniel-siepmann/project/vendor/symfony/console/Application.php:316
Symfony\Component\Console\Application->doRun() at /var/projects/own/daniel-siepmann/project/vendor/symfony/console/Application.php:167
Symfony\Component\Console\Application->run() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-core/Classes/Console/CommandApplication.php:112
TYPO3\CMS\Core\Console\CommandApplication->run() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-cli/typo3:23
{closure}() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-cli/typo3:24
include() at /var/projects/own/daniel-siepmann/project/vendor/bin/typo3:119
In StatementError.php line 28:
[Doctrine\DBAL\Driver\Mysqli\Exception\StatementError (1264)]
Out of range value for column 't3ver_state' at row 1
Exception trace:
at /var/projects/own/daniel-siepmann/project/vendor/doctrine/dbal/src/Driver/Mysqli/Exception/StatementError.php:28
Doctrine\DBAL\Driver\Mysqli\Exception\StatementError::upcast() at /var/projects/own/daniel-siepmann/project/vendor/doctrine/dbal/src/Driver/Mysqli/Statement.php:72
Doctrine\DBAL\Driver\Mysqli\Statement->execute() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-adminpanel/Classes/Log/LoggingStatement.php:52
TYPO3\CMS\Adminpanel\Log\LoggingStatement->execute() at /var/projects/own/daniel-siepmann/project/vendor/doctrine/dbal/src/Connection.php:865
Doctrine\DBAL\Connection->executeStatement() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-core/Classes/Database/Query/BulkInsertQuery.php:188
TYPO3\CMS\Core\Database\Query\BulkInsertQuery->execute() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-core/Classes/Database/Connection.php:217
TYPO3\CMS\Core\Database\Connection->bulkInsert() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-core/Classes/Database/ReferenceIndex.php:434
TYPO3\CMS\Core\Database\ReferenceIndex->updateRefIndexTable() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-core/Classes/Database/ReferenceIndex.php:349
TYPO3\CMS\Core\Database\ReferenceIndex->updateIndex() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-backend/Classes/Command/ReferenceIndexUpdateCommand.php:66
TYPO3\CMS\Backend\Command\ReferenceIndexUpdateCommand->execute() at /var/projects/own/daniel-siepmann/project/vendor/symfony/console/Command/Command.php:279
Symfony\Component\Console\Command\Command->run() at /var/projects/own/daniel-siepmann/project/vendor/symfony/console/Application.php:1047
Symfony\Component\Console\Application->doRunCommand() at /var/projects/own/daniel-siepmann/project/vendor/symfony/console/Application.php:316
Symfony\Component\Console\Application->doRun() at /var/projects/own/daniel-siepmann/project/vendor/symfony/console/Application.php:167
Symfony\Component\Console\Application->run() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-core/Classes/Console/CommandApplication.php:112
TYPO3\CMS\Core\Console\CommandApplication->run() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-cli/typo3:23
{closure}() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-cli/typo3:24
include() at /var/projects/own/daniel-siepmann/project/vendor/bin/typo3:119
In Statement.php line 68:
[mysqli_sql_exception (1264)]
Out of range value for column 't3ver_state' at row 1
Exception trace:
at /var/projects/own/daniel-siepmann/project/vendor/doctrine/dbal/src/Driver/Mysqli/Statement.php:68
mysqli_stmt->execute() at /var/projects/own/daniel-siepmann/project/vendor/doctrine/dbal/src/Driver/Mysqli/Statement.php:68
Doctrine\DBAL\Driver\Mysqli\Statement->execute() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-adminpanel/Classes/Log/LoggingStatement.php:52
TYPO3\CMS\Adminpanel\Log\LoggingStatement->execute() at /var/projects/own/daniel-siepmann/project/vendor/doctrine/dbal/src/Connection.php:865
Doctrine\DBAL\Connection->executeStatement() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-core/Classes/Database/Query/BulkInsertQuery.php:188
TYPO3\CMS\Core\Database\Query\BulkInsertQuery->execute() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-core/Classes/Database/Connection.php:217
TYPO3\CMS\Core\Database\Connection->bulkInsert() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-core/Classes/Database/ReferenceIndex.php:434
TYPO3\CMS\Core\Database\ReferenceIndex->updateRefIndexTable() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-core/Classes/Database/ReferenceIndex.php:349
TYPO3\CMS\Core\Database\ReferenceIndex->updateIndex() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-backend/Classes/Command/ReferenceIndexUpdateCommand.php:66
TYPO3\CMS\Backend\Command\ReferenceIndexUpdateCommand->execute() at /var/projects/own/daniel-siepmann/project/vendor/symfony/console/Command/Command.php:279
Symfony\Component\Console\Command\Command->run() at /var/projects/own/daniel-siepmann/project/vendor/symfony/console/Application.php:1047
Symfony\Component\Console\Application->doRunCommand() at /var/projects/own/daniel-siepmann/project/vendor/symfony/console/Application.php:316
Symfony\Component\Console\Application->doRun() at /var/projects/own/daniel-siepmann/project/vendor/symfony/console/Application.php:167
Symfony\Component\Console\Application->run() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-core/Classes/Console/CommandApplication.php:112
TYPO3\CMS\Core\Console\CommandApplication->run() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-cli/typo3:23
{closure}() at /var/projects/own/daniel-siepmann/project/vendor/typo3/cms-cli/typo3:24
include() at /var/projects/own/daniel-siepmann/project/vendor/bin/typo3:119
referenceindex:update [-c|--check]
SQL:
select t3ver_state from sys_file_reference group by t3ver_state ┌─────────────┐ │ t3ver_state │ ├─────────────┤ │ -1 │ │ 0 │ │ 1 │ └─────────────┘ 3 rows in set Time: 0.030s
Updated by Daniel Siepmann 5 months ago
It might make sense in general to collect those exceptions and continue execution to have an index as good as possible?
Updated by Garvin Hicking 5 months ago
- Category set to Database API (Doctrine DBAL)
- Status changed from New to Needs Feedback
Could be related to some auto db issue. Did you run the DB analyzer first, are all migrations executed?
What's the DB type for "t3ver_state" in that table for you, is it nullable? Which db driver and type do you use!
Updated by Daniel Siepmann 5 months ago
I run the DB Analyzer until no more changes could be applied.
There are no upgrade wizards to run.
I'm using "MySQL 10.5.23-MariaDB-0+deb11u1" with "mysqli".
describe sys_file_reference ┌──────────────────┬──────────────────────┬──────┬─────┬─────────┬────────────────┐ │ Field │ Type │ Null │ Key │ Default │ Extra │ ├──────────────────┼──────────────────────┼──────┼─────┼─────────┼────────────────┤ │ uid │ int(10) unsigned │ NO │ PRI │ <null> │ auto_increment │ │ pid │ int(10) unsigned │ NO │ MUL │ 0 │ │ │ tstamp │ int(10) unsigned │ NO │ │ 0 │ │ │ crdate │ int(10) unsigned │ NO │ │ 0 │ │ │ deleted │ smallint(5) unsigned │ NO │ MUL │ 0 │ │ │ hidden │ smallint(5) unsigned │ NO │ │ 0 │ │ │ sys_language_uid │ int(11) │ NO │ │ 0 │ │ │ l10n_parent │ int(10) unsigned │ NO │ MUL │ 0 │ │ │ l10n_state │ text │ YES │ │ <null> │ │ │ l10n_diffsource │ mediumblob │ YES │ │ <null> │ │ │ t3ver_oid │ int(10) unsigned │ NO │ MUL │ 0 │ │ │ t3ver_wsid │ int(10) unsigned │ NO │ │ 0 │ │ │ t3ver_state │ smallint(6) │ NO │ │ 0 │ │ │ t3ver_stage │ int(11) │ NO │ │ 0 │ │ │ uid_local │ int(11) │ NO │ MUL │ 0 │ │ │ uid_foreign │ int(11) │ NO │ MUL │ 0 │ │ │ tablenames │ varchar(64) │ NO │ MUL │ │ │ │ fieldname │ varchar(64) │ NO │ │ │ │ │ sorting_foreign │ int(11) │ NO │ │ 0 │ │ │ title │ tinytext │ YES │ │ <null> │ │ │ description │ longtext │ YES │ │ <null> │ │ │ alternative │ text │ YES │ │ <null> │ │ │ link │ text │ NO │ │ '' │ │ │ crop │ longtext │ YES │ │ <null> │ │ │ autoplay │ smallint(5) unsigned │ NO │ │ 0 │ │ └──────────────────┴──────────────────────┴──────┴─────┴─────────┴────────────────┘
I had the same issue with tt_content.
I've "fixed" it by updating all -1 to 1. Not sure if that's okay.
-1 should not be in use since v12.0.0 already it seems: https://github.com/TYPO3/typo3/commit/13977d176acde180cfbcf36a9decdfc81fe3da8b
Updated by Garvin Hicking 5 months ago
Yes, probably some older wizard didn't run or so?
The DBAL migration will be worked on by Stefan in the next weeks, and will try to also address migration failures due to data truncation, so this may likely be handled with better warnings.
Due to -1 existing there was probably a mismarch of the DB column declaration of sys_refindex vs sys_file_metadata for t3ver_state. The migrator recognizes a negative value and thus doesnt change the unsigned state, but in the other table those values don'f exist.
Would it be ok with you to close this issue for now due to legacy data, and see what gets improved on v13 here?
Updated by Garvin Hicking 5 months ago
- Status changed from Needs Feedback to Closed
Thanks! :) Maybe also running dbdoctor on the DB could reveal inconsistencies?
Actions