DoctrineMigrations\Version20210802160837 fails in Development installation for verison 1.15 and above

[j-be]

Describe the bug

When trying to setup 1.15 (or higher) in local dev-env using the guide here bin/console kimai:reset-dev fails with:

[error] Migration DoctrineMigrations\Version20210802160837 failed during Execution. Error: "An exception occurred while executing 'ALTER TABLE kimai2_timesheet CHANGE date_tz date_tz DATE DEFAULT 'NULL' NOT NULL':

SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'date_tz'"

                                                                                                                      
 [ERROR] Failed to execute a migrations: An exception occurred while executing 'ALTER TABLE kimai2_timesheet CHANGE   
         date_tz date_tz DATE DEFAULT 'NULL' NOT NULL':                                                               
                                                                                                                      
         SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'date_tz'                  

It works fine for 1.14.3, but it still fails if I then checkout 1.15 (or higher) and do:

• composer install
• npm install --force
• bin/console kimai:update

I can see how DEFAULT 'NULL' doesn't make any sense on a NOT NULL column. As to why Doctrine seems to think that is a good idea I do not know.

To Reproduce

1. Clone the repo
2. Run composer install and npm install --force
3. Start an empty MariaDB, e.g. using docker run -e MYSQL_ROOT_PASSWORD=kimai -p 3306:3306 mariadb
4. Set APP_ENV=dev and DATABASE_URL=mysql://root:kimai@127.0.0.1:3306/kimai?charset=utf8&serverVersion=5.7 in .env
5. Execute bin/console kimai:reset:dev and say y to both questions
6. See the error

Logfile

$ bin/console kimai:reset-dev
Do you want to create the database y/N ?y
Created database `kimai` for connection named default
Do you want to drop and re-create the schema y/N ?y

 Dropping database schema...

                                                                                                                      
 [OK] Database schema dropped successfully!                                                                           
                                                                                                                      

int(0)
int(0)
[notice] Migrating up to DoctrineMigrations\Version20210802174320
[warning] Migration DoctrineMigrations\Version20191113132640 was executed but did not result in any SQL statements.
[warning] Migration DoctrineMigrations\Version20210802152814 was executed but did not result in any SQL statements.
[error] Migration DoctrineMigrations\Version20210802160837 failed during Execution. Error: "An exception occurred while executing 'ALTER TABLE kimai2_timesheet CHANGE date_tz date_tz DATE DEFAULT 'NULL' NOT NULL':

SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'date_tz'"

                                                                                                                      
 [ERROR] Failed to execute a migrations: An exception occurred while executing 'ALTER TABLE kimai2_timesheet CHANGE   
         date_tz date_tz DATE DEFAULT 'NULL' NOT NULL':                                                               
                                                                                                                      
         SQLSTATE[42000]: Syntax error or access violation: 1067 Invalid default value for 'date_tz'

Additional context

• Kimai version: 1.15 and higher
• PHP version: PHP 7.4.3 from package repository
• Device: Desktop running Linux Mint 20.2 (based on Ubuntu LTS)
• MariaDB: 10.3 through 10.5 (latest at the time of writing)
• Browser: not relevant

[kevinpapst]

Yeah, annoying. The problem lies in your connection string.
You need to set the correct server version in your .env, see https://github.com/kevinpapst/kimai2/blob/master/.env.dist#L5

[j-be]

That was the issue - very weird indeed. Thanks for the quick reply. Closing.

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs. If you use Kimai on a daily basis, please consider donating to support further development of Kimai.