Failed to set migration status #4766

chrisonline · 2024-04-10T08:46:49Z

chrisonline
Apr 10, 2024

I have just updated the docker image, but after start I see in the logs following error at migrating to the new version:

Database `kimai` for connection named default already exists. Skipped.
[notice] Migrating up to DoctrineMigrations\Version20240326125247
[error] Migration DoctrineMigrations\Version20231130000719 failed during Execution. Error: "An exception occurred while executing a query: SQLSTATE[HY000]: General error: 1093 You can't specify target table 'u1' for update in FROM clause"
 [ERROR] Failed to set migration status: An exception occurred while executing a
         query: SQLSTATE[HY000]: General error: 1093 You can't specify target   
         table 'u1' for update in FROM clause                                   
+ /opt/kimai/bin/console -n kimai:update

After login I see this error in the logs:


[2024-04-10T08:36:21.780713+00:00] request.CRITICAL: Uncaught PHP Exception Twig\Error\RuntimeError: "An exception has been thrown during the rendering of a template ("An exception occurred while executing a query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'k0_.number' in 'field list'")." at datatable.html.twig line 14 {"exception":"[object] (Twig\\Error\\RuntimeError(code: 0): An exception has been thrown during the rendering of a template (\"An exception occurred while executing a query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'k0_.number' in 'field list'\"). at /opt/kimai/templates/datatable.html.twig:14)\n[previous exception] [object] (Doctrine\\DBAL\\Exception\\InvalidFieldNameException(code: 1054): An exception occurred while executing a query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'k0_.number' in 'field list' at /opt/kimai/vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php:67)\n[previous exception] [object] (Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 1054): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'k0_.number' in 'field list' at /opt/kimai/vendor/doctrine/dbal/src/Driver/PDO/Exception.php:28)\n[previous exception] [object] (PDOException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'k0_.number' in 'field list' at /opt/kimai/vendor/doctrine/dbal/src/Driver/PDO/Statement.php:130)"} {"channel":"request"}

Can someone help how I can fix this?

Running latest docker images from kimai/kimai2:fpm on a Synology nas with mysql 5.7.9 docker image!

chrisonline · 2024-04-10T09:16:57Z

chrisonline
Apr 10, 2024
Author

Have fixe it via manual SQL Update who adds the new 2 columns, but would be good to find the cause.
Otherwise I have to manually upgrade again on the next version ;-)

5 replies

kevinpapst Apr 10, 2024
Maintainer

Nah, that's not the way...
I assume you meant the activity/project columns, but manually trying to fix things is a bad idea, its impossible now to give any help.

The only migration that has u1 is this here: https://github.com/kimai/kimai/blob/main/migrations/Version20231130000719.php
And there were two more migrations after that.

Did you set the correct migration status?

chrisonline Apr 10, 2024
Author

Currently I have only executed this 2 SQL's from the 'Version20240326125247':
ALTER TABLE kimai2_activities ADD number VARCHAR(10) DEFAULT NULL'
ALTER TABLE kimai2_projects ADD number VARCHAR(10) DEFAULT NULL'

Now it is loading again.

But if you want I can of course drop this 2 columns so we can find the root cause.
Let me know.

kevinpapst Apr 10, 2024
Maintainer

Try editing API password in your user profile, to see if you can create/read the new API tokens

chrisonline Apr 10, 2024
Author

Does not work. If I click on "API Zugang" I see an error 500.

Crash in the log:
[2024-04-10T12:09:57.891812+00:00] request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\TableNotFoundException: "An exception occurred while executing a query: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'kimai.kimai2_access_token' doesn't exist" at ExceptionConverter.php line 49 {"exception":"[object] (Doctrine\\DBAL\\Exception\\TableNotFoundException(code: 1146): An exception occurred while executing a query: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'kimai.kimai2_access_token' doesn't exist at /opt/kimai/vendor/doctrine/dbal/src/Driver/API/MySQL/ExceptionConverter.php:49)\n[previous exception] [object] (Doctrine\\DBAL\\Driver\\PDO\\Exception(code: 1146): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'kimai.kimai2_access_token' doesn't exist at /opt/kimai/vendor/doctrine/dbal/src/Driver/PDO/Exception.php:28)\n[previous exception] [object] (PDOException(code: 42S02): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'kimai.kimai2_access_token' doesn't exist at /opt/kimai/vendor/doctrine/dbal/src/Driver/PDO/Statement.php:130)"} {"channel":"request"}

kevinpapst Apr 10, 2024
Maintainer

As expected. We should fix the SQL statement for you and then run the migrations.

kevinpapst · 2024-04-10T12:17:53Z

kevinpapst
Apr 10, 2024
Maintainer

This seems to be the initial statement that is failing:

delete u1 from kimai2_user_preferences u1 where u1.name like '%.%' and exists(select 1 from (SELECT name, user_id FROM kimai2_user_preferences WHERE name LIKE '%_%') u2 where u2.name = replace(u1.name, '.', '_') and u1.user_id = u2.user_id)

I tested this on MySQL and MariaDB versions and it never failed on me, but maybe this is problematic for certain DB versions.
Can you try that manually in your docker

13 replies

kevinpapst Apr 11, 2024
Maintainer

Can you do me a favor and copy & paste the output of mysql -v ? I'd like to know the minor release version for the DATABASE_URL

       - DATABASE_URL=mysql://kimaiuser:kimaipassword@sqldb/kimai?charset=utf8mb4&serverVersion=8.3.0

chrisonline Apr 11, 2024
Author

Yes of course. Where should I call this command?
Inside the mysql docker container or kimai installation?

I have done it now in the mysql container:

bash-4.4# mysql -V 
mysql  Ver 8.3.0 for Linux on x86_64 (MySQL Community Server - GPL)

kevinpapst Apr 11, 2024
Maintainer

Oh, hahaha, I didn't expect that it is really 8.3.0 - thanks, then I can safely merge the documentation update

kevinpapst Apr 11, 2024
Maintainer

And we'll see if others runs into the same issue.
The update is out for two weeks now, so hopefully many people used mysql latest.

Thanks for helping with the tests!

chrisonline Apr 11, 2024
Author

Thanks for helping with the tests!

Your welcome.
I have now a Test system installed so if you need again help to test something out please let me know.

nbaud · 2024-10-21T14:03:35Z

nbaud
Oct 21, 2024

I followed the steps in the documentation for upgrading, but stuck on this. I tried some manual tricks but to no avail. I am upgrading from 1.28.1 to 2.3.0 (I know it's a big step)

The main issue is this

[notice] Migrating up to DoctrineMigrations\Version20240926111739
[error] Migration DoctrineMigrations\Version20230327143628 failed during Execution. Error: "The table with name "kimai2.kimai2_working_times" already exists."
                                                                                                                        
 [ERROR] The table with name "kimai2.kimai2_working_times" already exists.

from

Run composer recipes at any time to see the status of your Symfony recipes.

Executing script cache:clear [OK]
Executing script assets:install [OK]

nico@gigai1:/var/www/kimai2$ bin/console kimai:update

Kimai updates running ...
=========================

[notice] Migrating up to DoctrineMigrations\Version20240926111739
[error] Migration DoctrineMigrations\Version20230327143628 failed during Execution. Error: "The table with name "kimai2.kimai2_working_times" already exists."
                                                                                                                        
 [ERROR] The table with name "kimai2.kimai2_working_times" already exists.                                              
                                                                                                                        

nico@gigai1:/var/www/kimai2$ bin/console doctrine:migrations:status
+----------------------+----------------------+------------------------------------------------------------------------+
| Configuration                                                                                                        |
+----------------------+----------------------+------------------------------------------------------------------------+
| Storage              | Type                 | Doctrine\Migrations\Metadata\Storage\TableMetadataStorageConfiguration |
|                      | Table Name           | migration_versions                                                     |
|                      | Column Name          | version                                                                |
|----------------------------------------------------------------------------------------------------------------------|
| Database             | Driver               | Doctrine\DBAL\Driver\PDO\MySQL\Driver                                  |
|                      | Name                 | kimai2                                                                 |
|----------------------------------------------------------------------------------------------------------------------|
| Versions             | Previous             | DoctrineMigrations\Version20230126002049                               |
|                      | Current              | DoctrineMigrations\Version20230126002050                               |
|                      | Next                 | DoctrineMigrations\Version20230327143628                               |
|                      | Latest               | DoctrineMigrations\Version20240926111739                               |
|----------------------------------------------------------------------------------------------------------------------|
| Migrations           | Executed             | 62                                                                     |
|                      | Executed Unavailable | 0                                                                      |
|                      | Available            | 70                                                                     |
|                      | New                  | 8                                                                      |
|----------------------------------------------------------------------------------------------------------------------|
| Migration Namespaces | DoctrineMigrations   | /var/www/kimai2/migrations                                             |
+----------------------+----------------------+------------------------------------------------------------------------+
nico@gigai1:/var/www/kimai2$

3 replies

nbaud Oct 21, 2024

Also

nico@gigai1:/var/www/kimai2$ bin/console doctrine:migrations:status
+----------------------+----------------------+------------------------------------------------------------------------+
| Configuration                                                                                                        |
+----------------------+----------------------+------------------------------------------------------------------------+
| Storage              | Type                 | Doctrine\Migrations\Metadata\Storage\TableMetadataStorageConfiguration |
|                      | Table Name           | migration_versions                                                     |
|                      | Column Name          | version                                                                |
|----------------------------------------------------------------------------------------------------------------------|
| Database             | Driver               | Doctrine\DBAL\Driver\PDO\MySQL\Driver                                  |
|                      | Name                 | kimai2                                                                 |
|----------------------------------------------------------------------------------------------------------------------|
| Versions             | Previous             | DoctrineMigrations\Version20230126002049                               |
|                      | Current              | DoctrineMigrations\Version20230126002050                               |
|                      | Next                 | DoctrineMigrations\Version20230327143628                               |
|                      | Latest               | DoctrineMigrations\Version20240926111739                               |
|----------------------------------------------------------------------------------------------------------------------|
| Migrations           | Executed             | 62                                                                     |
|                      | Executed Unavailable | 0                                                                      |
|                      | Available            | 70                                                                     |
|                      | New                  | 8                                                                      |
|----------------------------------------------------------------------------------------------------------------------|
| Migration Namespaces | DoctrineMigrations   | /var/www/kimai2/migrations                                             |
+----------------------+----------------------+------------------------------------------------------------------------+

nbaud Oct 21, 2024

that part

composer install --no-dev --optimize-autoloader

Went all fine

kevinpapst Oct 21, 2024
Maintainer

What you describe doesn't make sense at all.
2.3.0 was released a year ago.
Also the table you mention is created in the middle of the entire process.
It can't already exist, unless your ran the upgrade before and it failed.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Failed to set migration status #4766

{{title}}

{{editor}}'s edit

{{editor}}'s edit

Replies: 3 comments 21 replies

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

Failed to set migration status #4766

chrisonline Apr 10, 2024

Replies: 3 comments · 21 replies

chrisonline Apr 10, 2024 Author

kevinpapst Apr 10, 2024 Maintainer

chrisonline Apr 10, 2024 Author

kevinpapst Apr 10, 2024 Maintainer

chrisonline Apr 10, 2024 Author

kevinpapst Apr 10, 2024 Maintainer

kevinpapst Apr 10, 2024 Maintainer

kevinpapst Apr 11, 2024 Maintainer

chrisonline Apr 11, 2024 Author

kevinpapst Apr 11, 2024 Maintainer

kevinpapst Apr 11, 2024 Maintainer

chrisonline Apr 11, 2024 Author

nbaud Oct 21, 2024

nbaud Oct 21, 2024

nbaud Oct 21, 2024

kevinpapst Oct 21, 2024 Maintainer

chrisonline
Apr 10, 2024

Replies: 3 comments 21 replies

chrisonline
Apr 10, 2024
Author

kevinpapst Apr 10, 2024
Maintainer

chrisonline Apr 10, 2024
Author

kevinpapst Apr 10, 2024
Maintainer

chrisonline Apr 10, 2024
Author

kevinpapst Apr 10, 2024
Maintainer

kevinpapst
Apr 10, 2024
Maintainer

kevinpapst Apr 11, 2024
Maintainer

chrisonline Apr 11, 2024
Author

kevinpapst Apr 11, 2024
Maintainer

kevinpapst Apr 11, 2024
Maintainer

chrisonline Apr 11, 2024
Author

nbaud
Oct 21, 2024

kevinpapst Oct 21, 2024
Maintainer