How to debug failing schema updates?

[jaapcrezee]

How should I / can I debug this?

www-data@e9c8135da38b:/opt/kimai$ bin/console -n doctrine:schema:update
13:18:02 ERROR     [console] Error thrown while running command "-n doctrine:schema:update". Message: "An exception occurred in driver: SQLSTATE[HY000] [2002] No such file or directory" ["exception" => Doctrine\DBAL\Exception\ConnectionException { â¦},"command" => "-n doctrine:schema:update","message" => "An exception occurred in driver: SQLSTATE[HY000] [2002] No such file or directory"]

In AbstractMySQLDriver.php line 113:
                                                                                     
  An exception occurred in driver: SQLSTATE[HY000] [2002] No such file or directory  
                                                                                     

In PDOConnection.php line 50:
                                                    
  SQLSTATE[HY000] [2002] No such file or directory  
                                                    

In PDOConnection.php line 46:
                                                    
  SQLSTATE[HY000] [2002] No such file or directory  
                                                    

doctrine:schema:update [--complete] [--dump-sql] [-f|--force] [--em [EM]] [-h|--help] [-q|--quiet] [-v|vv|vvv|--verbose] [-V|--version] [--ansi] [--no-ansi] [-n|--no-interaction] [-e|--env ENV] [--no-debug] [--] <command>

The database connection is fine, because on startup the container shows some tables.

DATABASE_URL = mysql://kimai:<secret>@172.17.0.1/kimai
Using Mysql DB
Tables_in_kimai
<other tables in database shown here but removed because not relevant here>
kimai2_activities
kimai2_customers
kimai2_invoice_templates
kimai2_projects
kimai2_timesheet
kimai2_user_preferences
kimai2_users

[jaapcrezee]

ps running on

prod: Pulling from kimai/kimai2
Digest: sha256:47849c12a3ca1c4c1d871b3b03a67aee23b99e672c2655aa7c35f74cb580a15f
Status: Image is up to date for kimai/kimai2:prod

[kevinpapst]

I know thats not what you asked for: but may I ask why you execute that command? Kimai uses migrations for upgrading the database.

[jaapcrezee]

You may always ask that. I wanted to run that command because I got 500 responses from the webserver on some pages after having upgraded the docker container. When I looked into the migrations table, there were no recent migrations (in 2019). I thought that was strange combined with the 500. It took me some time to figure out that, when running manual commands in the container, I should make sure the DATABASE_URL environment variable is set (manually unfortunately). Once I got that going I could run the migration command manually. I did a doctrine:migrations:execute 20190201150324 which gave me some errors which let me to removing the timezone field from the database (timesheet). After that the migration did apply and now everything works again.
Not sure why migrations do not work automatically.
Could be because I did something wrong along the way, but it would be great if I could get some logging when things do not work. Should I just change the APP_ENV to dev?

This is how I use the container:

#!/bin/sh

IMAGE=kimai/kimai2:prod

docker run --restart always -d \
    -p 127.0.0.1:8083:8001 \
    --name kimai \
    -e DATABASE_PREFIX=kimai2_ \
    -e MAILER_FROM=<hidden> \
    -e APP_ENV=prod \
    -e APP_SECRET=<hidden> \
    -e SQL_PROTOCOL=mysql \
    -e SQL_DATABASE=kimai \
    -e SQL_USER=kimai \
    -e SQL_PASSWORD=<hidden> \
    -e SQL_HOST=172.17.0.1 \
    -e MAILER_URL=null \
    --volume /<hidden>/invoices:/opt/kimai/var/invoices \
    --volume /usr/share/zoneinfo/Europe/Amsterdam:/etc/localtime \
    $IMAGE

[kevinpapst]

Thats a prod container, so unless you really want to debug something, there is no need for dev.
Isn't there a var/log/prod.log inside the Kimai directory?
Did you read the Kimai documentation about updates https://github.com/kevinpapst/kimai2#updating-kimai? If you don't follow the correct steps, you may end up with a broken database. Probably not what you want ;-)

But why does a docker update produce 500?
Are the Kimai sources included in the images? If so, running a docker update could result in a silent Kimai update without the necessary update steps included. @tobybatch

[jaapcrezee]

It might be that ...

And upgrade your database:

bin/console doctrine:migrations:migrate

... is missing in the /startup.sh in kimai/kimai2:prod container.

[kevinpapst]

Ok, so I guess the sources are included. But running the migrations blindly is probably not the best solution. If I am right with my assumption, then I would recommend something like:
Run a shell script that fetches all entries from the migration_versions table and compare them to the files in kimai/src/Migration/ - substring magic needs to applied, to check if there are new Migration files which were not yet executed. If one is found, I would not start Kimai but dump a bold error message, that an update needs to be performed.
Remember that Kimai 2 is not yet released in a stable version and unfortunately in the last release there is a manually step required (a database convert command needs to be executed after the database migration).
If only the migration is executed, the user will think that everything is fine and continue using Kimai without checking if there is more to-do.

[tobybatch]

This is caused by the same problem that was in this issue: #14

See the change to the docker file in https://github.com/tobybatch/kimai2/blob/master/prod/README.md#runtime-args

The DATABASE_URL needs to be set at container start time.