zabbixDBdump.sql
[ZBX-20664] Zabbix MySQL DB upgrade 5.0 -> 6.0 fails Created: 2022 Feb 23 Updated: 2022 Mar 01 Resolved: 2022 Mar 01 |
|
| Status: | Closed |
| Project: | ZABBIX BUGS AND ISSUES |
| Component/s: | None |
| Affects Version/s: | 6.0.0 |
| Fix Version/s: | None |
| Type: | Incident report | Priority: | Trivial |
| Reporter: | Avery Willard | Assignee: | Zabbix Support Team |
| Resolution: | Fixed | Votes: | 0 |
| Labels: | database, upgrade | ||
| Remaining Estimate: | Not Specified | ||
| Time Spent: | Not Specified | ||
| Original Estimate: | Not Specified | ||
| Environment: |
Docker, MySQL 8.0, Ubuntu 20.04.4, Zabbix 6.0 |
||
| Attachments: |
zabbixDBdump.sql
|
| Description |
|
Steps to reproduce: docker-compose -f docker-compose_v3_ubuntu_mysql_latest.yaml up -d zabbix server container automatically attempts to upgrade MySQL DB from 05000000 to 06000000. MySQL Container is on version 8.0. Result: Starting Zabbix Server. Zabbix 6.0.0 (revision 5203d2e). Press Ctrl+C to exit. 7:20220223:145114.601 Starting Zabbix Server. Zabbix 6.0.0 (revision 5203d2e). 7:20220223:145114.601 ****** Enabled features ****** 7:20220223:145114.601 SNMP monitoring: YES 7:20220223:145114.601 IPMI monitoring: YES 7:20220223:145114.601 Web monitoring: YES 7:20220223:145114.601 VMware monitoring: YES 7:20220223:145114.601 SMTP authentication: YES 7:20220223:145114.601 ODBC: YES 7:20220223:145114.601 SSH support: YES 7:20220223:145114.601 IPv6 support: YES 7:20220223:145114.601 TLS support: YES 7:20220223:145114.601 ****************************** 7:20220223:145114.601 using configuration file: /etc/zabbix/zabbix_server.conf 7:20220223:145114.626 current database version (mandatory/optional): 05000000/05000002 7:20220223:145114.626 required mandatory version: 06000000 7:20220223:145114.626 starting automatic database upgrade 7:20220223:145114.663 completed 0% of database upgrade 7:20220223:145114.741 completed 1% of database upgrade 7:20220223:145114.906 completed 2% of database upgrade 7:20220223:145115.028 completed 3% of database upgrade 7:20220223:145115.155 completed 4% of database upgrade 7:20220223:145115.280 completed 5% of database upgrade 7:20220223:145115.432 completed 6% of database upgrade 7:20220223:145115.460 completed 7% of database upgrade 7:20220223:145115.750 completed 8% of database upgrade 7:20220223:145115.973 completed 9% of database upgrade 7:20220223:145115.974 [Z3005] query failed: [1050] Table 'trigger_queue' already exists [create table trigger_queue ( `objectid` bigint unsigned not null, `type` integer default '0' not null, `clock` integer default '0' not null, `ns` integer default '0' not null ) engine=innodb] 7:20220223:145115.974 database upgrade failed Expected: Successful DB Upgrade |
| Comments |
| Comment by Alexey Pustovalov [ 2022 Feb 23 ] |
|
do you have anything in trigger_queue table? |
| Comment by Avery Willard [ 2022 Feb 24 ] |
|
No, trigger_queue table is empty. |
| Comment by Alexey Pustovalov [ 2022 Feb 24 ] |
|
show us please, output: show create table trigger_queue; |
| Comment by Avery Willard [ 2022 Feb 24 ] |
mysql> show create table trigger_queue; +---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | trigger_queue | CREATE TABLE `trigger_queue` ( `objectid` bigint unsigned NOT NULL, `type` int NOT NULL DEFAULT '0', `clock` int NOT NULL DEFAULT '0', `ns` int NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin | +---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
| Comment by Alexey Pustovalov [ 2022 Feb 24 ] |
|
Execute the following SQL query and continue upgrade process (just start Zabbix server again): drop table trigger_queue; |
| Comment by Avery Willard [ 2022 Feb 24 ] |
|
I had reverted back to 5.0 to have some functionality, I've removed the old 5.0 containers and started 6.0 containers, but the zabbix 6.0 server fails immediately with: ** Updating '/etc/zabbix/zabbix_server.conf' parameter "User": 'zabbix'...updated Starting Zabbix Server. Zabbix 6.0.0 (revision 5203d2e). Press Ctrl+C to exit. 7:20220224:095129.108 Starting Zabbix Server. Zabbix 6.0.0 (revision 5203d2e). 7:20220224:095129.108 ****** Enabled features ****** 7:20220224:095129.109 SNMP monitoring: YES 7:20220224:095129.109 IPMI monitoring: YES 7:20220224:095129.109 Web monitoring: YES 7:20220224:095129.109 VMware monitoring: YES 7:20220224:095129.109 SMTP authentication: YES 7:20220224:095129.109 ODBC: YES 7:20220224:095129.109 SSH support: YES 7:20220224:095129.109 IPv6 support: YES 7:20220224:095129.109 TLS support: YES 7:20220224:095129.109 ****************************** 7:20220224:095129.109 using configuration file: /etc/zabbix/zabbix_server.conf 7:20220224:095129.134 current database version (mandatory/optional): 05030112/05030112 7:20220224:095129.134 required mandatory version: 06000000 7:20220224:095129.134 starting automatic database upgrade 7:20220224:095129.135 [Z3005] query failed: [1050] Table 'httptest_tag' already exists [create table httptest_tag ( `httptesttagid` bigint unsigned not null, `httptestid` bigint unsigned not null, `tag` varchar(255) default '' not null, `value` varchar(255) default '' not null, primary key (httptesttagid) ) engine=innodb] 7:20220224:095129.135 database upgrade failed Seeing that httptest_tag was empty, I did show create table httptest_tag; and then drop table httptest_tag; and rebuilt the containers. The Zabbix server 6.0 container failed again with this message: Starting Zabbix Server. Zabbix 6.0.0 (revision 5203d2e). Press Ctrl+C to exit. 7:20220224:095454.324 Starting Zabbix Server. Zabbix 6.0.0 (revision 5203d2e). 7:20220224:095454.324 ****** Enabled features ****** 7:20220224:095454.324 SNMP monitoring: YES 7:20220224:095454.324 IPMI monitoring: YES 7:20220224:095454.324 Web monitoring: YES 7:20220224:095454.324 VMware monitoring: YES 7:20220224:095454.324 SMTP authentication: YES 7:20220224:095454.324 ODBC: YES 7:20220224:095454.325 SSH support: YES 7:20220224:095454.325 IPv6 support: YES 7:20220224:095454.325 TLS support: YES 7:20220224:095454.325 ****************************** 7:20220224:095454.325 using configuration file: /etc/zabbix/zabbix_server.conf 7:20220224:095454.351 current database version (mandatory/optional): 05030112/05030112 7:20220224:095454.351 required mandatory version: 06000000 7:20220224:095454.351 starting automatic database upgrade 7:20220224:095454.365 completed 0% of database upgrade 7:20220224:095454.405 completed 1% of database upgrade 7:20220224:095454.406 [Z3005] query failed: [1050] Table 'sysmaps_element_tag' already exists [create table sysmaps_element_tag ( `selementtagid` bigint unsigned not null, `selementid` bigint unsigned not null, `tag` varchar(255) default '' not null, `value` varchar(255) default '' not null, `operator` integer default '0' not null, primary key (selementtagid) ) engine=innodb] 7:20220224:095454.406 database upgrade failed sysmaps_element_tag is empty too, do I just need to go through and remove these empty tables? |
| Comment by Alexey Pustovalov [ 2022 Feb 24 ] |
|
feel free to drop sysmaps_element_tag as well if it is empty. |
| Comment by Avery Willard [ 2022 Feb 24 ] |
|
ok, I dropped sysmaps_element_tag, rebuilt containers and the zabbix server container failed again with: Starting Zabbix Server. Zabbix 6.0.0 (revision 5203d2e). Press Ctrl+C to exit. 8:20220224:101600.949 Starting Zabbix Server. Zabbix 6.0.0 (revision 5203d2e). 8:20220224:101600.949 ****** Enabled features ****** 8:20220224:101600.949 SNMP monitoring: YES 8:20220224:101600.949 IPMI monitoring: YES 8:20220224:101600.949 Web monitoring: YES 8:20220224:101600.949 VMware monitoring: YES 8:20220224:101600.949 SMTP authentication: YES 8:20220224:101600.949 ODBC: YES 8:20220224:101600.949 SSH support: YES 8:20220224:101600.949 IPv6 support: YES 8:20220224:101600.949 TLS support: YES 8:20220224:101600.949 ****************************** 8:20220224:101600.949 using configuration file: /etc/zabbix/zabbix_server.conf 8:20220224:101601.004 current database version (mandatory/optional): 05030115/05030115 8:20220224:101601.004 required mandatory version: 06000000 8:20220224:101601.004 starting automatic database upgrade 8:20220224:101601.018 completed 0% of database upgrade 8:20220224:101601.058 completed 1% of database upgrade 8:20220224:101601.233 completed 2% of database upgrade 8:20220224:101601.267 completed 3% of database upgrade 8:20220224:101601.272 completed 4% of database upgrade 8:20220224:101601.321 completed 5% of database upgrade 8:20220224:101601.328 completed 6% of database upgrade 8:20220224:101601.384 completed 7% of database upgrade 8:20220224:101601.470 completed 8% of database upgrade 8:20220224:101601.508 completed 9% of database upgrade 8:20220224:101601.535 completed 10% of database upgrade 8:20220224:101601.573 completed 11% of database upgrade 8:20220224:101601.577 [Z3005] query failed: [1050] Table 'report' already exists [create table report ( `reportid` bigint unsigned not null, `userid` bigint unsigned not null, `name` varchar(255) default '' not null, `description` varchar(2048) default '' not null, `status` integer default '0' not null, `dashboardid` bigint unsigned not null, `period` integer default '0' not null, `cycle` integer default '0' not null, `weekdays` integer default '0' not null, `start_time` integer default '0' not null, `active_since` integer default '0' not null, `active_till` integer default '0' not null, `state` integer default '0' not null, `lastsent` integer default '0' not null, `info` varchar(2048) default '' not null, primary key (reportid) ) engine=innodb] 8:20220224:101601.578 database upgrade failed
the report table is empty. |
| Comment by Alexey Pustovalov [ 2022 Feb 24 ] |
|
How is it possible? could you show us full list of existing tables? How do you start Zabbix server container? Why you rebuild it each time? |
| Comment by Avery Willard [ 2022 Feb 24 ] |
mysql> show tables; +----------------------------+ | Tables_in_zabbix | +----------------------------+ | acknowledges | | actions | | alerts | | auditlog | | auditlog_details | | autoreg_host | | conditions | | config | | config_autoreg_tls | | corr_condition | | corr_condition_group | | corr_condition_tag | | corr_condition_tagpair | | corr_condition_tagvalue | | corr_operation | | correlation | | dashboard | | dashboard_page | | dashboard_user | | dashboard_usrgrp | | dbversion | | dchecks | | dhosts | | drules | | dservices | | escalations | | event_recovery | | event_suppress | | event_tag | | events | | expressions | | functions | | globalmacro | | globalvars | | graph_discovery | | graph_theme | | graphs | | graphs_items | | group_discovery | | group_prototype | | ha_node | | history | | history_log | | history_str | | history_text | | history_uint | | host_discovery | | host_inventory | | host_tag | | hostmacro | | hosts | | hosts_groups | | hosts_templates | | housekeeper | | hstgrp | | httpstep | | httpstep_field | | httpstepitem | | httptest | | httptest_field | | httptest_tag | | httptestitem | | icon_map | | icon_mapping | | ids | | images | | interface | | interface_discovery | | interface_snmp | | item_condition | | item_discovery | | item_parameter | | item_preproc | | item_rtdata | | item_tag | | items | | lld_macro_path | | lld_override | | lld_override_condition | | lld_override_opdiscover | | lld_override_operation | | lld_override_ophistory | | lld_override_opinventory | | lld_override_opperiod | | lld_override_opseverity | | lld_override_opstatus | | lld_override_optag | | lld_override_optemplate | | lld_override_optrends | | maintenance_tag | | maintenances | | maintenances_groups | | maintenances_hosts | | maintenances_windows | | media | | media_type | | media_type_message | | media_type_param | | module | | opcommand | | opcommand_grp | | opcommand_hst | | opconditions | | operations | | opgroup | | opinventory | | opmessage | | opmessage_grp | | opmessage_usr | | optemplate | | problem | | problem_tag | | profiles | | proxy_autoreg_host | | proxy_dhistory | | proxy_history | | regexps | | report | | report_param | | report_user | | report_usrgrp | | rights | | role | | role_rule | | script_param | | scripts | | service_alarms | | service_problem | | service_problem_tag | | service_status_rule | | service_tag | | services | | services_links | | services_times | | sessions | | sla | | sla_excluded_downtime | | sla_schedule | | sla_service_tag | | sysmap_element_trigger | | sysmap_element_url | | sysmap_shape | | sysmap_url | | sysmap_user | | sysmap_usrgrp | | sysmaps | | sysmaps_element_tag | | sysmaps_elements | | sysmaps_link_triggers | | sysmaps_links | | tag_filter | | task | | task_acknowledge | | task_check_now | | task_close_problem | | task_data | | task_remote_command | | task_remote_command_result | | task_result | | timeperiods | | token | | trends | | trends_uint | | trigger_depends | | trigger_discovery | | trigger_queue | | trigger_tag | | triggers | | users | | users_groups | | usrgrp | | valuemap | | valuemap_mapping | | widget | | widget_field | +----------------------------+ 175 rows in set (0.00 sec) I'm sorry I misread, I see I should just be restarting not rebuilding. |
| Comment by Avery Willard [ 2022 Feb 24 ] |
|
If I've messed up my database my upgrading to 6.0 wrong I have daily backups of my database going back a few weeks. |
| Comment by Alexey Pustovalov [ 2022 Feb 24 ] |
|
it looks like you have all tables from 6.0 already. Please create a dump of database table structure, and upload the dump to check consistency. |
| Comment by Avery Willard [ 2022 Feb 24 ] |
|
dump attached: |
| Comment by Alexey Pustovalov [ 2022 Feb 24 ] |
|
It looks like the DB is broken around 5050016 patch. update dbversion set mandatory = '05050016', optional = '05050016'; But in case of any errors inside of data, it is necessary to perform recovery from backup ( |
| Comment by Avery Willard [ 2022 Feb 24 ] |
|
I tried that and I got error [Z3005] query failed: [1050] Table 'ha_node' already exists [create table ha_node ( That table wasn't empty but since I have a backup anyways so I tried deleting it and got this error: [Z3005] query failed: [1054] Unknown column 'dbversion_status' in 'config' [alter table `config` modify `dbversion_status` text not null] So I did as you said and started over from the backup. I removed all containers, rebuilt them, then restored the database from a backup from before I started the Zabbix 6.0 upgrade process, but I'm greeted with the same "[Z3005] query failed: [1050] Table 'trigger_queue' already exists" error from the beginning. |
| Comment by Avery Willard [ 2022 Feb 25 ] |
|
It seems like no matter how far back I go in my backups I get that same 'trigger queue already exists" error. I've reverted to Zabbix 5.0 for now. |
| Comment by Avery Willard [ 2022 Feb 25 ] |
|
Here's the tables in my 5.0 database backup for before I started the upgrade process: +----------------------------+ | Tables_in_zabbix | +----------------------------+ | acknowledges | | actions | | alerts | | application_discovery | | application_prototype | | application_template | | applications | | auditlog | | auditlog_details | | autoreg_host | | conditions | | config | | config_autoreg_tls | | corr_condition | | corr_condition_group | | corr_condition_tag | | corr_condition_tagpair | | corr_condition_tagvalue | | corr_operation | | correlation | | dashboard | | dashboard_user | | dashboard_usrgrp | | dbversion | | dchecks | | dhosts | | drules | | dservices | | escalations | | event_recovery | | event_suppress | | event_tag | | events | | expressions | | functions | | globalmacro | | globalvars | | graph_discovery | | graph_theme | | graphs | | graphs_items | | group_discovery | | group_prototype | | history | | history_log | | history_str | | history_text | | history_uint | | host_discovery | | host_inventory | | host_tag | | hostmacro | | hosts | | hosts_groups | | hosts_templates | | housekeeper | | hstgrp | | httpstep | | httpstep_field | | httpstepitem | | httptest | | httptest_field | | httptestitem | | icon_map | | icon_mapping | | ids | | images | | interface | | interface_discovery | | interface_snmp | | item_application_prototype | | item_condition | | item_discovery | | item_parameter | | item_preproc | | item_rtdata | | items | | items_applications | | lld_macro_path | | lld_override | | lld_override_condition | | lld_override_opdiscover | | lld_override_operation | | lld_override_ophistory | | lld_override_opinventory | | lld_override_opperiod | | lld_override_opseverity | | lld_override_opstatus | | lld_override_optag | | lld_override_optemplate | | lld_override_optrends | | maintenance_tag | | maintenances | | maintenances_groups | | maintenances_hosts | | maintenances_windows | | mappings | | media | | media_type | | media_type_message | | media_type_param | | module | | opcommand | | opcommand_grp | | opcommand_hst | | opconditions | | operations | | opgroup | | opinventory | | opmessage | | opmessage_grp | | opmessage_usr | | optemplate | | problem | | problem_tag | | profiles | | proxy_autoreg_host | | proxy_dhistory | | proxy_history | | regexps | | rights | | role | | role_rule | | screen_user | | screen_usrgrp | | screens | | screens_items | | scripts | | service_alarms | | services | | services_links | | services_times | | sessions | | slides | | slideshow_user | | slideshow_usrgrp | | slideshows | | sysmap_element_trigger | | sysmap_element_url | | sysmap_shape | | sysmap_url | | sysmap_user | | sysmap_usrgrp | | sysmaps | | sysmaps_elements | | sysmaps_link_triggers | | sysmaps_links | | tag_filter | | task | | task_acknowledge | | task_check_now | | task_close_problem | | task_data | | task_remote_command | | task_remote_command_result | | task_result | | timeperiods | | trends | | trends_uint | | trigger_depends | | trigger_discovery | | trigger_queue | | trigger_tag | | triggers | | users | | users_groups | | usrgrp | | valuemaps | | widget | | widget_field | +----------------------------+ 170 rows in set (0.00 sec) |
| Comment by Edgar Akhmetshin [ 2022 Mar 01 ] |
|
Hello Avery Original schema has 166 tables in LTS 5.0.20, this tables are not part of 5.0: item_parameter role role_rule trigger_queue If they are empty, remove them before upgrade. Regards, |
| Comment by Avery Willard [ 2022 Mar 01 ] |
|
Not sure how those tables got in the database but deleting them worked, thanks Edgar and Alexey. |
| Comment by Avery Willard [ 2022 Mar 01 ] |
|
Deleting extra tables allowed the database to upgrade successfully. |
