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.
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.
[box type=”warning”] A full machine backup is recommended before performing any further action.[/box]
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:
(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;
Migrate the existing databases to the new time zone, type the following command:
Any user can now set their own timezones and both warnings have disappeared.