TimeTracker - MySQL problem

[AlAraf]

I'm trying to install TimeTracker on my Debian machine, but when I open https://timetracker.mydomain.com/dbinstall.php I get database connection error:
Environment Checks
WEB-INF/templates_c/ directory is writable.
WEB-INF/config.php file exists.
DSN is defined.
PHP version: 7.0.33-0+deb9u3, good enough.
mysqli PHP extension is loaded.
mbstring PHP extension is loaded.
gd PHP extension is loaded.
Error: connection to database failed. MDB2 Error: unknown error

I have created timetracker database, database user and granted him all privileges to timetracker database. Connection string in config.php is correct (username, password, database name), I have verified it twice.

Next step I tried to create database structure manually using mysql.sql scrip but it seems that it's not compatible with my MySQL version:
mysql -h localhost -u ttuser -p -D timetracker < mysql.sql
Enter password:
ERROR 1064 (42000) at line 76: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'rank, status)' at line 1


I have PHP php7.0 (7.0.33-0+deb9u3) and MySQL ver 8.0.17. Is there any chance to update TimeTracker to work with the latest PHP and MySQL?

[wrc]

Well, line 76 is:

Code: Select all

# Create an index that guarantees unique active and inactive role ranks in each group.
create unique index role_idx on tt_roles(group_id, rank, status);

[Nik]

The problem with MySQL appears to be that "rank" is now a reserved word since their version 8.0.2, and therefore must be surrounded by backticks in all queries. Try using MariaDB perhaps for a quick fix.

Line #76 is fixable by doing the above, but there are many others, line #115 in mysql.sql, etc, etc.

[Nik]

Rank keyword is now backticked in TT version 1.19.8.5052. This should fix the manual database population step and hopefully the application in its entirety, assuming you get the connection working. For the connection to work, perhaps the easiest is to use native authentication (as in MySQL 5.6).

[AlAraf]

Rank keyword is now backticked in TT version 1.19.8.5052. This should fix the manual database population step and hopefully the application in its entirety, assuming you get the connection working. For the connection to work, perhaps the easiest is to use native authentication (as in MySQL 5.6).

Thank's SQL script seems to be fixed. But still can't connect to database: "MDB2 Error: unknown error".

My database connection string in config.php file is as follows:
define('DSN', 'mysqli://ttuser:SecretPassword@localhost/timetracker?charset=utf8mb4');

Password is correct, I can connect from console (mysql -h localhost -u ttuser -p) using the same password, list tables, etc..

How can I configure TT to use native authentication?

[Nik]

How can I configure TT to use native authentication?

You don't configure TT. Configure MySQL. It's an option during its installation.

[AlAraf]

Ok, I managed to reconfigure MySQL to use mysql_native_password. Now connection to database via PHP is working.
I tried to login to TT using start-up credentials (admin/secret) but nothing happens, it seems like login.php goes into a loop if I eneter correct login and password, it is still asking me for login and password. When I enter incorrect password, I get such an information, so password is verified with hash in database.


Here are my logs:
root@local:/var/log/apache2# cat timetracker.mydomain.com_error.log
[Mon Sep 02 14:32:16.267558 2019] [:error] [pid 1032] [client 1.1.1.1:14027] PHP Warning: Unknown: open(/var/lib/php/sessions/sess_kfgbgpen262qtserh7vo1l0ba4, O_RDWR) failed: Permission denied (13) in Unknown on line 0, referer: https://timetracker.mydomain.com/login.php
[Mon Sep 02 14:32:16.267619 2019] [:error] [pid 1032] [client 1.1.1.1:14027] PHP Warning: Unknown: Failed to write session data (files). Please verify that the current setting of session.save_path is correct (/var/lib/php/sessions) in Unknown on line 0, referer: https://timetracker.mydomain.com/login.php
[Mon Sep 02 14:32:16.320730 2019] [:error] [pid 1032] [client 1.1.1.1:14027] PHP Warning: session_write_close(): open(/var/lib/php/sessions/sess_kfgbgpen262qtserh7vo1l0ba4, O_RDWR) failed: Permission denied (13) in /var/www/timetracker.mydomain.com/WEB-INF/lib/Auth.class.php on line 43, referer: https://timetracker.mydomain.com/login.php
[Mon Sep 02 14:32:16.320764 2019] [:error] [pid 1032] [client 1.1.1.1:14027] PHP Warning: session_write_close(): Failed to write session data (files). Please verify that the current setting of session.save_path is correct (/var/lib/php/sessions) in /var/www/timetracker.mydomain.com/WEB-INF/lib/Auth.class.php on line 43, referer: https://timetracker.mydomain.com/login.php
[Mon Sep 02 14:32:16.613265 2019] [:error] [pid 1032] [client 1.1.1.1:14027] PHP Warning: Unknown: open(/var/lib/php/sessions/sess_kfgbgpen262qtserh7vo1l0ba4, O_RDWR) failed: Permission denied (13) in Unknown on line 0, referer: https://timetracker.mydomain.com/login.php
[Mon Sep 02 14:32:16.613287 2019] [:error] [pid 1032] [client 1.1.1.1:14027] PHP Warning: Unknown: Failed to write session data (files). Please verify that the current setting of session.save_path is correct (/var/lib/php/sessions) in Unknown on line 0, referer: https://timetracker.mydomain.com/login.php


root@local:/var/log/apache2# ls -al /var/lib/php/sessions
drwxr-xr-x 2 root root 4096 sty 1 2017 .
drwxr-xr-x 4 root root 4096 cze 9 20:05 ..


root@local:/var/lib/php/sessions# cat /etc/php/7.0/apache2/php.ini | grep "^[^;]" | grep "session."
session.save_handler = files
session.use_strict_mode = 0
session.use_cookies = 1
session.use_only_cookies = 1
session.name = PHPSESSID
session.auto_start = 0
session.cookie_lifetime = 0
session.cookie_path = /
session.cookie_domain =
session.cookie_httponly =
session.serialize_handler = php
session.gc_probability = 0
session.gc_divisor = 1000
session.gc_maxlifetime = 1440
session.referer_check =
session.cache_limiter = nocache
session.cache_expire = 180
session.use_trans_sid = 0
session.hash_function = 0
session.hash_bits_per_character = 5

Line 43 of /var/www/timetracker.mydomain.com/WEB-INF/lib/Auth.class.php file is "session_write_close();".


Any idea what is wrong and how to fix it?

[Nik]

Looks like PHP cannot write your session information to file (/var/lib/php/sessions). Fix this first. Check if this path exists and permissions are set properly.

I don't see any issues on my test Debian install with MySQL 8.

[AlAraf]

Ok, I managed to fix it by my self (chown www-data:www-data /var/lib/php/sessions/).