TimeTracker - MySQL problem

Discussion forum about Anuko Time Tracker
Post Reply
AlAraf
Posts: 7
Joined: Thu Aug 29, 2019 4:26 pm

TimeTracker - MySQL problem

Post by AlAraf » Thu Aug 29, 2019 6:01 pm

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
Posts: 284
Joined: Tue May 25, 2010 8:30 pm

Re: TimeTracker - MySQL problem

Post by wrc » Sun Sep 01, 2019 2:00 pm

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
Posts: 484
Joined: Wed May 26, 2010 5:55 pm

Re: TimeTracker - MySQL problem

Post by Nik » Sun Sep 01, 2019 9:40 pm

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
Posts: 484
Joined: Wed May 26, 2010 5:55 pm

Re: TimeTracker - MySQL problem

Post by Nik » Sun Sep 01, 2019 11:55 pm

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
Posts: 7
Joined: Thu Aug 29, 2019 4:26 pm

Re: TimeTracker - MySQL problem

Post by AlAraf » Mon Sep 02, 2019 10:30 am

Nik wrote:
Sun Sep 01, 2019 11:55 pm
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
Posts: 484
Joined: Wed May 26, 2010 5:55 pm

Re: TimeTracker - MySQL problem

Post by Nik » Mon Sep 02, 2019 11:26 am

AlAraf wrote:
Mon Sep 02, 2019 10:30 am
How can I configure TT to use native authentication?
You don't configure TT. Configure MySQL. It's an option during its installation.

AlAraf
Posts: 7
Joined: Thu Aug 29, 2019 4:26 pm

Re: TimeTracker - MySQL problem

Post by AlAraf » Mon Sep 02, 2019 12:02 pm

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
Posts: 484
Joined: Wed May 26, 2010 5:55 pm

Re: TimeTracker - MySQL problem

Post by Nik » Mon Sep 02, 2019 1:01 pm

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
Posts: 7
Joined: Thu Aug 29, 2019 4:26 pm

Re: TimeTracker - MySQL problem

Post by AlAraf » Mon Sep 02, 2019 1:02 pm

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

Post Reply