[ZBX-17711] Database history tables upgraded: No - After upgrade to 5.0.0 Created: 2020 May 12  Updated: 2024 Apr 10  Resolved: 2020 Jul 13

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Documentation (D), Packages (C)
Affects Version/s: None
Fix Version/s: 5.0.2rc1, 5.2.0alpha1, 5.2 (plan)

Type: Problem report Priority: Major
Reporter: Omar De Donato Assignee: Jurijs Klopovskis
Resolution: Fixed Votes: 9
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: Microsoft Word table_status.csv    
Issue Links:
Duplicate
is duplicated by ZBX-17774 double.sql should be included to sour... Closed
Team: Team I
Sprint: Sprint 64 (May 2020), Sprint 65 (Jun 2020), Sprint 66 (Jul 2020)
Story Points: 10

 Description   

Hi,

I've upgraded my Zabbix 4.4.8 to 5.0.0 following the documentation but on report.status page I see:
Database history tables upgraded: No

OS: CentOS 7
DB: MariaDB 10.2.31

I don't know if related to ZBX-17690

Attached result of SHOW TABLE STATUS IN zabbix; 

Followed this: https://www.zabbix.com/documentation/current/manual/appendix/install/db_charset_coll but no luck.

 Comments   
Comment by Cryptage [ 2020 May 12 ]

Hi,

Same problem for me  (Debian 10.4 - MariaDB 10.3).

I'm already on utf8 charset and utf8_bin collate.

MariaDB [(none)]> select concat('alter table ', table_schema, '.', table_name, ' collate utf8_bin;') from   information_schema.tables where  table_schema = 'zabbix';
+-----------------------------------------------------------------------------+
| concat('alter table ', table_schema, '.', table_name, ' collate utf8_bin;') |
+-----------------------------------------------------------------------------+
| alter table zabbix.graph_theme collate utf8_bin;                            |
| alter table zabbix.corr_condition_group collate utf8_bin;                   |
| alter table zabbix.trigger_depends collate utf8_bin;                        |
| alter table zabbix.opmessage collate utf8_bin;                              |
| alter table zabbix.trigger_tag collate utf8_bin;                            |
| alter table zabbix.lld_override_optemplate collate utf8_bin;                |
| alter table zabbix.lld_override_opstatus collate utf8_bin;                  |
| alter table zabbix.httpstepitem collate utf8_bin;                           |
| alter table zabbix.item_discovery collate utf8_bin;                         |
| alter table zabbix.applications collate utf8_bin;                           |
| alter table zabbix.hosts collate utf8_bin;                                  |
| alter table zabbix.corr_condition_tag collate utf8_bin;                     |
| alter table zabbix.history_uint collate utf8_bin;                           |
| alter table zabbix.event_tag collate utf8_bin;                              |
| alter table zabbix.sysmaps_link_triggers collate utf8_bin;                  |
| alter table zabbix.valuemaps collate utf8_bin;                              |
| alter table zabbix.task_result collate utf8_bin;                            |
| alter table zabbix.item_condition collate utf8_bin;                         |
| alter table zabbix.proxy_autoreg_host collate utf8_bin;                     |
| alter table zabbix.usrgrp collate utf8_bin;                                 |
| alter table zabbix.opinventory collate utf8_bin;                            |
| alter table zabbix.media_type_message collate utf8_bin;                     |
| alter table zabbix.task_data collate utf8_bin;                              |
| alter table zabbix.mappings collate utf8_bin;                               |
| alter table zabbix.actions collate utf8_bin;                                |
| alter table zabbix.proxy_history collate utf8_bin;                          |
| alter table zabbix.history_log collate utf8_bin;                            |
| alter table zabbix.problem_tag collate utf8_bin;                            |
| alter table zabbix.opcommand_grp collate utf8_bin;                          |
| alter table zabbix.trends_uint collate utf8_bin;                            |
| alter table zabbix.task_remote_command collate utf8_bin;                    |
| alter table zabbix.screens_items collate utf8_bin;                          |
| alter table zabbix.icon_map collate utf8_bin;                               |
| alter table zabbix.auditlog_details collate utf8_bin;                       |
| alter table zabbix.opmessage_usr collate utf8_bin;                          |
| alter table zabbix.slideshows collate utf8_bin;                             |
| alter table zabbix.media collate utf8_bin;                                  |
| alter table zabbix.conditions collate utf8_bin;                             |
| alter table zabbix.slideshow_user collate utf8_bin;                         |
| alter table zabbix.event_recovery collate utf8_bin;                         |
| alter table zabbix.opcommand collate utf8_bin;                              |
| alter table zabbix.drules collate utf8_bin;                                 |
| alter table zabbix.sysmaps_elements collate utf8_bin;                       |
| alter table zabbix.sessions collate utf8_bin;                               |
| alter table zabbix.maintenance_tag collate utf8_bin;                        |
| alter table zabbix.dbversion collate utf8_bin;                              |
| alter table zabbix.users collate utf8_bin;                                  |
| alter table zabbix.opgroup collate utf8_bin;                                |
| alter table zabbix.sysmap_element_trigger collate utf8_bin;                 |
| alter table zabbix.dhosts collate utf8_bin;                                 |
| alter table zabbix.dservices collate utf8_bin;                              |
| alter table zabbix.services_times collate utf8_bin;                         |
| alter table zabbix.autoreg_host collate utf8_bin;                           |
| alter table zabbix.hstgrp collate utf8_bin;                                 |
| alter table zabbix.rights collate utf8_bin;                                 |
| alter table zabbix.lld_override_ophistory collate utf8_bin;                 |
| alter table zabbix.escalations collate utf8_bin;                            |
| alter table zabbix.history collate utf8_bin;                                |
| alter table zabbix.widget_field collate utf8_bin;                           |
| alter table zabbix.slides collate utf8_bin;                                 |
| alter table zabbix.slideshow_usrgrp collate utf8_bin;                       |
| alter table zabbix.group_discovery collate utf8_bin;                        |
| alter table zabbix.host_tag collate utf8_bin;                               |
| alter table zabbix.icon_mapping collate utf8_bin;                           |
| alter table zabbix.functions collate utf8_bin;                              |
| alter table zabbix.lld_macro_path collate utf8_bin;                         |
| alter table zabbix.users_groups collate utf8_bin;                           |
| alter table zabbix.sysmap_user collate utf8_bin;                            |
| alter table zabbix.services_links collate utf8_bin;                         |
| alter table zabbix.opmessage_grp collate utf8_bin;                          |
| alter table zabbix.corr_operation collate utf8_bin;                         |
| alter table zabbix.lld_override_operation collate utf8_bin;                 |
| alter table zabbix.history_text collate utf8_bin;                           |
| alter table zabbix.dashboard collate utf8_bin;                              |
| alter table zabbix.application_prototype collate utf8_bin;                  |
| alter table zabbix.sysmap_element_url collate utf8_bin;                     |
| alter table zabbix.lld_override_condition collate utf8_bin;                 |
| alter table zabbix.ids collate utf8_bin;                                    |
| alter table zabbix.media_type_param collate utf8_bin;                       |
| alter table zabbix.dchecks collate utf8_bin;                                |
| alter table zabbix.item_application_prototype collate utf8_bin;             |
| alter table zabbix.task collate utf8_bin;                                   |
| alter table zabbix.graph_discovery collate utf8_bin;                        |
| alter table zabbix.sysmaps_links collate utf8_bin;                          |
| alter table zabbix.graphs collate utf8_bin;                                 |
| alter table zabbix.profiles collate utf8_bin;                               |
| alter table zabbix.interface collate utf8_bin;                              |
| alter table zabbix.opconditions collate utf8_bin;                           |
| alter table zabbix.config collate utf8_bin;                                 |
| alter table zabbix.module collate utf8_bin;                                 |
| alter table zabbix.task_remote_command_result collate utf8_bin;             |
| alter table zabbix.acknowledges collate utf8_bin;                           |
| alter table zabbix.sysmap_usrgrp collate utf8_bin;                          |
| alter table zabbix.application_discovery collate utf8_bin;                  |
| alter table zabbix.application_template collate utf8_bin;                   |
| alter table zabbix.graphs_items collate utf8_bin;                           |
| alter table zabbix.httptestitem collate utf8_bin;                           |
| alter table zabbix.items collate utf8_bin;                                  |
| alter table zabbix.alerts collate utf8_bin;                                 |
| alter table zabbix.operations collate utf8_bin;                             |
| alter table zabbix.httpstep collate utf8_bin;                               |
| alter table zabbix.optemplate collate utf8_bin;                             |
| alter table zabbix.corr_condition_tagpair collate utf8_bin;                 |
| alter table zabbix.housekeeper collate utf8_bin;                            |
| alter table zabbix.lld_override_optrends collate utf8_bin;                  |
| alter table zabbix.globalvars collate utf8_bin;                             |
| alter table zabbix.host_inventory collate utf8_bin;                         |
| alter table zabbix.proxy_dhistory collate utf8_bin;                         |
| alter table zabbix.corr_condition collate utf8_bin;                         |
| alter table zabbix.globalmacro collate utf8_bin;                            |
| alter table zabbix.sysmap_url collate utf8_bin;                             |
| alter table zabbix.dashboard_usrgrp collate utf8_bin;                       |
| alter table zabbix.hosts_groups collate utf8_bin;                           |
| alter table zabbix.services collate utf8_bin;                               |
| alter table zabbix.hostmacro collate utf8_bin;                              |
| alter table zabbix.media_type collate utf8_bin;                             |
| alter table zabbix.lld_override_opseverity collate utf8_bin;                |
| alter table zabbix.maintenances collate utf8_bin;                           |
| alter table zabbix.httptest_field collate utf8_bin;                         |
| alter table zabbix.host_discovery collate utf8_bin;                         |
| alter table zabbix.httpstep_field collate utf8_bin;                         |
| alter table zabbix.maintenances_groups collate utf8_bin;                    |
| alter table zabbix.hosts_templates collate utf8_bin;                        |
| alter table zabbix.items_applications collate utf8_bin;                     |
| alter table zabbix.scripts collate utf8_bin;                                |
| alter table zabbix.item_rtdata collate utf8_bin;                            |
| alter table zabbix.history_str collate utf8_bin;                            |
| alter table zabbix.dashboard_user collate utf8_bin;                         |
| alter table zabbix.lld_override_opperiod collate utf8_bin;                  |
| alter table zabbix.corr_condition_tagvalue collate utf8_bin;                |
| alter table zabbix.regexps collate utf8_bin;                                |
| alter table zabbix.expressions collate utf8_bin;                            |
| alter table zabbix.interface_snmp collate utf8_bin;                         |
| alter table zabbix.httptest collate utf8_bin;                               |
| alter table zabbix.group_prototype collate utf8_bin;                        |
| alter table zabbix.interface_discovery collate utf8_bin;                    |
| alter table zabbix.lld_override collate utf8_bin;                           |
| alter table zabbix.trends collate utf8_bin;                                 |
| alter table zabbix.lld_override_opdiscover collate utf8_bin;                |
| alter table zabbix.widget collate utf8_bin;                                 |
| alter table zabbix.timeperiods collate utf8_bin;                            |
| alter table zabbix.service_alarms collate utf8_bin;                         |
| alter table zabbix.auditlog collate utf8_bin;                               |
| alter table zabbix.task_check_now collate utf8_bin;                         |
| alter table zabbix.task_close_problem collate utf8_bin;                     |
| alter table zabbix.item_preproc collate utf8_bin;                           |
| alter table zabbix.images collate utf8_bin;                                 |
| alter table zabbix.triggers collate utf8_bin;                               |
| alter table zabbix.config_autoreg_tls collate utf8_bin;                     |
| alter table zabbix.correlation collate utf8_bin;                            |
| alter table zabbix.lld_override_optag collate utf8_bin;                     |
| alter table zabbix.screen_user collate utf8_bin;                            |
| alter table zabbix.maintenances_hosts collate utf8_bin;                     |
| alter table zabbix.trigger_discovery collate utf8_bin;                      |
| alter table zabbix.sysmaps collate utf8_bin;                                |
| alter table zabbix.sysmap_shape collate utf8_bin;                           |
| alter table zabbix.maintenances_windows collate utf8_bin;                   |
| alter table zabbix.lld_override_opinventory collate utf8_bin;               |
| alter table zabbix.events collate utf8_bin;                                 |
| alter table zabbix.screen_usrgrp collate utf8_bin;                          |
| alter table zabbix.opcommand_hst collate utf8_bin;                          |
| alter table zabbix.event_suppress collate utf8_bin;                         |
| alter table zabbix.tag_filter collate utf8_bin;                             |
| alter table zabbix.screens collate utf8_bin;                                |
| alter table zabbix.task_acknowledge collate utf8_bin;                       |
| alter table zabbix.problem collate utf8_bin;                                |
+-----------------------------------------------------------------------------+

 In zabbix_server.log, after the startup (not the same problem ?) :

 15063:20200512:152421.906 database is not upgraded to use double precision values

 From source code :

MariaDB [(none)]> select count(*) from information_schema.columns where table_schema='zabbix' and column_type='double';
+----------+
| count(*) |
+----------+
|        5 |
+----------+

MariaDB [(none)]> select * from information_schema.columns where table_schema='zabbix' and column_type='double'; +---------------+--------------+------------+---------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+--------------+-----------------------+ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | IS_GENERATED | GENERATION_EXPRESSION | +---------------+--------------+------------+---------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+--------------+-----------------------+ | def | zabbix | graphs | yaxismin | 5 | 0 | NO | double | NULL | NULL | 22 | NULL | NULL | NULL | NULL | double | | | select,insert,update,references | | NEVER | NULL | | def | zabbix | graphs | yaxismax | 6 | 100 | NO | double | NULL | NULL | 22 | NULL | NULL | NULL | NULL | double | | | select,insert,update,references | | NEVER | NULL | | def | zabbix | graphs | percent_left | 13 | 0 | NO | double | NULL | NULL | 22 | NULL | NULL | NULL | NULL | double | | | select,insert,update,references | | NEVER | NULL | | def | zabbix | graphs | percent_right | 14 | 0 | NO | double | NULL | NULL | 22 | NULL | NULL | NULL | NULL | double | | | select,insert,update,references | | NEVER | NULL | | def | zabbix | services | goodsla | 7 | 99.9 | NO | double | NULL | NULL | 22 | NULL | NULL | NULL | NULL | double | | | select,insert,update,references | | NEVER | NULL | +---------------+--------------+------------+---------------+------------------+----------------+-------------+-----------+--------------------------+------------------------+-------------------+---------------+--------------------+--------------------+----------------+-------------+------------+-------+---------------------------------+----------------+--------------+-----------------------+ 5 rows in set (0.009 sec)
Comment by Vladislavs Sokurenko [ 2020 May 13 ]

It seems our upgrade notes should be more specific, could you please have a a look at section "Enabling extended range of numeric (float) values"

Comment by Omar De Donato [ 2020 May 13 ]

Where I can find double.sql ? There isn't on my system updated via yum.

[root@zabbix ~]# locate double.sql
[root@zabbix ~]# yum list installed | grep zabbix
iksemel.x86_64 1.4-2.el7.centos @zabbix-non-supported
zabbix-agent.x86_64 5.0.0-1.el7 @zabbix
zabbix-apache-conf-scl.noarch 5.0.0-1.el7 @zabbix-frontend
zabbix-get.x86_64 5.0.0-1.el7 @zabbix
zabbix-release.noarch 5.0-1.el7 installed
zabbix-sender.x86_64 5.0.0-1.el7 @zabbix
zabbix-server-mysql.x86_64 5.0.0-1.el7 @zabbix
zabbix-web.noarch 5.0.0-1.el7 @zabbix-frontend
zabbix-web-deps-scl.noarch 5.0.0-1.el7 @zabbix-frontend
zabbix-web-mysql-scl.noarch 5.0.0-1.el7 @zabbix-frontend
[root@zabbix ~]#

Thanks

Comment by Dmitriy [ 2020 May 13 ]

wget https://git.zabbix.com/projects/ZBX/repos/zabbix/raw/database/mysql/double.sql

Comment by Roberto Lio [ 2020 May 13 ]

HI

same problem for me.

Solved using https://git.zabbix.com/projects/ZBX/repos/zabbix/raw/database/mysql/double.sql and $DB['DOUBLE_IEEE754'] = true;

in

/etc/zabbix/web/zabbix.conf.php

and restart the server

Comment by Omar De Donato [ 2020 May 13 ]

 

MariaDB [zabbix]> ALTER TABLE trends
 -> MODIFY value_min DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
 -> MODIFY value_avg DOUBLE PRECISION DEFAULT '0.0000' NOT NULL,
 -> MODIFY value_max DOUBLE PRECISION DEFAULT '0.0000' NOT NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [zabbix]> ALTER TABLE history MODIFY value DOUBLE PRECISION DEFAULT '0.0000' NOT NULL;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [zabbix]>

Then added in /etc/zabbix/web/zabbix.conf.php

$DB['DOUBLE_IEEE754'] = true;

After zabbix restart is gone.

Thanks all.
 

Comment by Jurijs Klopovskis [ 2020 May 13 ]

We will add double.sql file to server packages in 5.0.1 release.

In any case make sure to restart Zabbix server after applying the file.

Also this file is meant only for the server DB, should not be used on proxies.

Comment by Glebs Ivanovskis [ 2020 May 13 ]

In any case make sure to restart the server after applying the file.

Wouldn't it be a good idea to stop Zabbix server before applying the file?

Comment by Marco Hofmann [ 2020 May 13 ]

I'm sorry, stupid question; Restart which server? Database server or Zabbix Server daemon?

Comment by Roberto Lio [ 2020 May 13 ]

Simply restart  Zabbix Daemon

Comment by Cryptage [ 2020 May 13 ]

Working perfectly for me with double.sql and entry in zabbix.conf.php.

No more error on dashboard neither in log file.

Comment by Sascha Papini [ 2020 May 17 ]

What is the procedure for TimeScaleDB ? If I run the query I receive this error:

ERRORE: ONLY option not supported on hypertable operations

Generated at Mon Jun 08 03:50:08 EEST 2026 using Jira 10.3.18#10030018-sha1:5642e4ad348b6c2a83ebdba689d04763a2393cab.