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.

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.

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

A full machine backup is recommended before performing any further action.

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)

MySQL timezone setup

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 [email protected], 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

timezone migration

Solved

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

->  How to setup GLPI API