Issues

X columns are not compatible with timezones usage

On the main GLPI page there is at least a warning:

(Number) columns are not compatible with timezones usage.

Access to timezone database (mysql) is not allowed

In the user profile of a new or upgraded installation to GLPI 9.5 it shows the following warning message:

Access to timezone database (mysql) is not allowed.

Explanation

As from GLPI version 9.5, the database date fields are no longer DATETIME to become TIMEZONE. For installations that work in several time zones this is a great improvement, since each user will receive the hours depending on the configuration of their profile, regardless of the system / database time.

This change does not occur automatically, and in the case of updates, all DATETIME fields will have to be migrated to TIMEZONE.

Solution

[box type=”warning”] A full machine backup is recommended before performing any further action.[/box]

MariaDB configuration

You will have to initialize the data of the time zones of the time zones of your system, to achieve it, execute the following command with the root user of MariaDB:

mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -p -u root mysql

Set a time zone

Edit the configuration file:

vim /etc/mysql/my.cnf

(the path depends on the operating system) and add the default time zone to [mysqld]:

default-time-zone = 'Europe/Madrid'

(in this example Europe / Madrid)

Restart the service

Restart the service:

systemctl restart mariadb

Allow DB user limited access to timezones table

The GLPi database user must have read access to the mysql.time_zone_name table. Assuming the GLPi user is glpi@localhost, run the commands:

mysql -u root -p

GRANT SELECT ON `mysql`.`time_zone_name` TO 'glpi'@'localhost';
FLUSH PRIVILEGES;

Migration

Migrate the existing databases to the new time zone, type the following command:

bin/console glpi:migration:timestamps

Solved

Any user can now set their own timezones and both warnings have disappeared.