bin/console fails with SQLSTATE[HY000] [2002]

[betonme]

I'm using Docker version 18.06.1-ce, build e68fc7a on a Debian system.

First I've adapted Your Docker-Compose file:
https://github.com/tobybatch/kimai2/blob/master/docker-compose.yml

After running into problems, I used it unchanged with the same result.

The web interface is working fine.
First test to create a user was fine.

Then using the console to import my Kimai V1 data, I tested to create a user.
But it fails with:
[ERROR] Reason: An exception occurred in driver: SQLSTATE[HY000] [2002] No such file or directory

Also I can not import the data, same error.

Any ideas? Thanks

[tobybatch]

@betonme Can I see your docker-compose file please? It looks like the console can't find the sqlite file but I need to see the environment.

[betonme]

I'm using MySQL and used the sample Docker-Compose file unchanged
docker-compose.yml.txt

[betonme]

Same problem with
docker --version
Docker version 18.09.3, build 774a1f4
docker-compose --version
docker-compose version 1.24.0-rc1, build 0f3d4dda

[orzechow]

I'm facing the same issue when trying to create a user.
I share the data folder with my host system, because I want to keep changes / actually use the time tracker.

What I do:

> docker run --rm -ti -p 8001:8001 --name kimai2 -v ${HOME}/.config/kimai/:/opt/kimai/var/data/ -e SQL_DATABASE=/opt/kimai/var/data/kimai.sqlite kimai/kimai2:prod

> docker exec kimai2 bin/console kimai:create-user admin admin@example.com ROLE_SUPER_ADMIN admin

 [ERROR] Failed to create user: admin                                           

 [ERROR] Reason: An exception occurred in driver: SQLSTATE[HY000] [2002] No such
         file or directory 

The /.config/kimai folder is created beforehand with the correct permissions. Kimai even generates the kimai.sqlite file there:

> ls -l ~/.config/kimai
-rw-rw-r-- 1 www-data www-data 106496 Mär  6 14:27 kimai.sqlite

I'm using your docker image from dockerhub, no custom compose file or similar.

[tobybatch]

@orzechow @betonme Sorry for being a but slow, my job's been a bit hectic.

The web ui can create a new user so there is something up with the console set up. I'll investigate.

[betonme]

No problem.

For the completeness, I'm using a separate container running MySQL.

The main problem is, I can't import the data of a kimai v1 MySQL database.
Since creating a user leads to the same error, it should be a general problem.

[rschaerer]

Hi @betonme , @orzechow and @tobybatch

The main problem is, I can't import the data of a kimai v1 MySQL database.
Since creating a user leads to the same error, it should be a general problem.

I'm having the same issues with trying to import the kimai v1 database wich throws the error

[ERROR] Failed to import users: An exception occurred in driver: SQLSTATE[HY000] [2002] No such file or directory

❓ Is there something I can do to help fix this? 🤔

[rschaerer]

Some more context from my second try:

I emptied the table kimai_users so the table just had the structure but no data anymore and this is the output of the still failing import:

[OK] Fetched Kimai v1 data, trying to import now ...                           

 [OK] Imported users: 0                                                         

 [ERROR] Reason: An exception occurred in driver: SQLSTATE[HY000] [2002] No such
         file or directory                                                      

 [ERROR] Failed to create customer: Testkunde                           

After this row there are endless errors like this for every customer and the log goes on the same with projects:

[OK] Imported customers: 0                                                     

 [ERROR] Failed to create project: TestProjekt                                  

 [ERROR] Reason: An exception occurred in driver: SQLSTATE[HY000] [2002] No such
         file or directory                                  

and finally:

 [OK] Imported projects: 0                                 

Then there's something strange happening with activities, it displays 8 times the well known error and once a warning about an empty name and in the end it shows that it created 9 activities. However if i take a look at the activities table after the import the activities table is empty and nothing has been imported:

[ERROR] Failed to create activity: Entwicklung                                 

 [ERROR] Reason: An exception occurred in driver: SQLSTATE[HY000] [2002] No such
         file or directory                                                      

 [WARNING] Found empty activity name, setting it to: 5c8a8c0e8f1e8              

 [ERROR] Failed to create activity: 5c8a8c0e8f1e8                               

 [ERROR] Reason: An exception occurred in driver: SQLSTATE[HY000] [2002] No such
         file or directory                                                      

 [OK] Created global activities: 9                                              

 [OK] Imported activities: 9          

After this, importing the timesheets fails completely (as it does with the users on the first try):

Importing timesheets, please wait

 [ERROR] Failed to import timesheet records: An exception occurred in driver:   
         SQLSTATE[HY000] [2002] No such file or directory                       
         #0                                                                     
         /opt/kimai/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php(169
         ): Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException('An      
         exception oc...', Object(Doctrine\DBAL\Driver\PDOException))  

[kevinpapst]

Ignore the "Imported activities: 9" - this is a false-positive in the importer, which obviously doesn't fail gracefully when the database is not available.

You could try to prepend the DATABASE_URL to the import command and see what happens, so something like:

DATABASE_URL=sqlite:///%kernel.project_dir%/var/data/kimai2.sqlite bin/console kimai:import-v1 ...

Can you show me the contents of your .env file?

[rschaerer]

Hi @kevinpapst

I'm using a sql server database and not sqlite for saving data.

I've checked that my database is connected correctly by adding an user through the webinterface and adding some test data (customer, project and activity). Timetracking works this way and the entries are being saved in the database.

🏁 I don't think that I'm having issues with writing to the database, so I don't understand why I should try to prepend the DATABASE_URL to the import command. 🤔

I'm running the docker build and don't have an .env file, but these are the env variables of the running tobybatch/kimai2 container:

PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin
HOSTNAME=[removed]
APP_ENV=prod
SQL_PROTOCOL=mysql
SQL_DATABASE=kimai
SQL_USER=root
SQL_PASSWORD=[removed]
SQL_HOST=[removed]
PHPIZE_DEPS=autoconf 		dpkg-dev 		file 		g++ 		gcc 		libc-dev 		make 		pkg-config 		re2c
PHP_INI_DIR=/usr/local/etc/php
APACHE_CONFDIR=/etc/apache2
APACHE_ENVVARS=/etc/apache2/envvars
PHP_EXTRA_BUILD_DEPS=apache2-dev
PHP_EXTRA_CONFIGURE_ARGS=--with-apxs2 --disable-cgi
PHP_CFLAGS=-fstack-protector-strong -fpic -fpie -O2
PHP_CPPFLAGS=-fstack-protector-strong -fpic -fpie -O2
PHP_LDFLAGS=-Wl,-O1 -Wl,--hash-style=both -pie
GPG_KEYS=[removed] [removed]
PHP_VERSION=7.2.9
PHP_URL=https://secure.php.net/get/php-7.2.9.tar.xz/from/this/mirror
PHP_ASC_URL=https://secure.php.net/get/php-7.2.9.tar.xz.asc/from/this/mirror
PHP_SHA256=[removed]
PHP_MD5=
DATABASE_PREFIX=kimai2_
MAILER_FROM=kimai@example.com
APP_SECRET=[removed]
TRUSTED_PROXIES=false
TRUSTED_HOSTS=false
MAILER_URL=null://localhost
TZ=Europe/London
HOME=/var/www

[kevinpapst]

What about simply giving it a try?

Well, okay here it goes:
It works in the WebUI, because DATABASE_URL is available there through the webserver configs.
Doctrine uses this variable when running console commands.
As you don't have a .env file (which would be used otherwise) PHP looks in your process envs.
And I don't see the DATABASE_URL in your environment.

So: prepend it to the command to set it temporarily and see what happens.

[rschaerer]

@tobybatch your hint with the sqlite url made me make some progress:

I've set up a new container with the default settings (sqlite as data storage) and tried to do the same import with the same command which was failing before: 🎉 THIS WORKS! The data is being imported.

So i think it has something to do with the database where the data should be saved to.

❓ What would be the correct DATABASE_URL for a mysql server database? 🤔 (or let me search for it on the internet and i'll edit this post later... i've got to go now, so i won't check this until tomorrow or later.)

[kevinpapst]

https://www.kimai.org/documentation/installation.html

[tobybatch]

I think there may be an issue about where kimai is picking up the DB URL from when it runs from the CLI rather than through apache. I will investigate. I've just finished a sprint at work so may have some time next week.

[rschaerer]

🎉 Thanks to @kevinpapst , my issue is resolved 🎉

Steps to reproduce:

1️⃣ Set a proper DATABASE_URL as env variable as suggested by @kevinpapst .

An example for a proper database url is DATABASE_URL=mysql://user:password@127.0.0.1:3306/database, as described in the kimai documentation.

2️⃣ Make sure all users have an unique email address in the importing database table kimai_users in the row mail. The mail field must not be empty!

3️⃣ Make sure the table kimai_users of the target database is empty, because duplicates (=existing user with the same email address as an user which is being tried to import) throw this error and stop the import: [ERROR] Object(App\Entity\User).email: This value is already used.

CC: @betonme & @orzechow

[orzechow]

Okay, I could also fix my problem.

I always tested with no kimai2.sqlite file (I wanted Kimai to create an empty database).

First of all I've mixed up SQL_DATABASE and DATABASE_URL 🙄

This

docker run --rm -ti -p 8001:8001 --name kimai2 -v ${HOME}/.config/kimai/:/opt/kimai/var/data/ -e DATABASE_URL=sqlite:///%kernel.project_dir%/var/data/kimai.sqlite kimai/kimai2:prod
docker exec kimai2 bin/console kimai:create-user admin admin@example.com ROLE_SUPER_ADMIN admin

works now (thus also the default without DATABASE_URL)

Probably I also didn't provide proper user rights for www-data on my host machines target folder 🙄

Confusingly

docker run --rm -ti -p 8001:8001 --name kimai2 -v ${HOME}/.config/kimai/:/opt/kimai/var/data/ -e DATABASE_URL=sqlite:///opt/kimai/var/data/kimai.sqlite kimai/kimai2:prod
docker exec kimai2 bin/console kimai:create-user admin admin@example.com ROLE_SUPER_ADMIN admin

opens/creates the database, but still fails in adding the admin user, even though this should be the correct path...
Reason: An exception occurred in driver: SQLSTATE[HY000] [14] unable to open database file

[tobybatch]

@rschaerer @orzechow @betonme Again sorry for delay.

The issue here is how the prod image picks up it's DB url. It is built in the startup script and that is fine for the web instance but when you shell in to use the console it is not set. I have switched round the way the databse url is built.

The DATABASE_URL must be passed into the container at start time.

https://github.com/tobybatch/kimai2/blob/master/prod/README.md#runtime-args

I think this will fix the problem.

[betonme]

Hi,

that's fixed my issue. Thanks