Ad Widget

Collapse

database upgrade failed : Specified key was too long; max key length is 3072 bytes

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • kro
    Junior Member
    • Nov 2019
    • 6
    #1

    database upgrade failed : Specified key was too long; max key length is 3072 bytes

    Hello, I did a few refactoring on my server and just upgraded zabbix docker image : zabbix/zabbix-server-mysql
    from 4.something to 5.0. I've also upgraded MariaDB.

    But zabbix won't start : while starting it returns this error then fails :
    Code:
    zabbix | 7:20200525:040444.998 starting automatic database upgrade
zabbix | 7:20200525:040444.998 [Z3005] query failed: [1071] Specified key was too long; max key length is 3072 bytes [create index items_1 on items (hostid,key_(1021))]
zabbix | 7:20200525:040444.998 database upgrade failed
    I've tried to convert the database collation but it hasn't changed anything :
    Code:
    MariaDB [zabbix]> SELECT @@character_set_database, @@collation_database;
+--------------------------+----------------------+
| @@character_set_database | @@collation_database |
+--------------------------+----------------------+
| utf8mb4 | utf8mb4_general_ci |
+--------------------------+----------------------+
1 row in set (0.000 sec)

MariaDB [zabbix]> ALTER DATABASE zabbix CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 1 row affected (0.001 sec)
    Any help would be very much appreciated here !
    Thanks !
    Tags: None
    • kro
      Junior Member
      • Nov 2019
      • 6
      #2
      any advice on this ?

      I'm stuck.. (I can't even rollback to 4.4) :
      Code:
      Current database version (mandatory/optional): 04050003/04050003. Required mandatory version: 04040000.
      I switched back to utf8mb4 / utf8mb4_general_ci.

      Code:
      MariaDB [zabbix]> SELECT @@GLOBAL.innodb_large_prefix;
+------------------------------+
| @@GLOBAL.innodb_large_prefix |
+------------------------------+
| 1 |
+------------------------------+
1 row in set (0.000 sec)

MariaDB [zabbix]> SELECT @@GLOBAL.innodb_file_format;
+-----------------------------+
| @@GLOBAL.innodb_file_format |
+-----------------------------+
| Barracuda |
+-----------------------------+
1 row in set (0.000 sec)

        Comment

        • kro
          Junior Member
          • Nov 2019
          • 6
          #3
          I fixed my issue !

          The solution is to convert every tables characters to utf8 and collation to utf8_bin. Not only the database default.

          To do so I followed this method :
          Code:
          SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'databasename';
          (replace databasename)
          This query returns a list of queries that you have to run to convert every single tables.

          For instance my database name is zabbix :
          Code:
          ALTER TABLE zabbix.trigger_tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.actions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.users_groups CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.maintenances_groups CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.regexps CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.interface_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.dchecks CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.maintenance_tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.media_type CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.widget CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.optemplate CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.opmessage_usr CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.httpstep CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.screens CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.host_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.trigger_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.httpstepitem CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.services_times CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.item_condition CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.dbversion CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.dashboard CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.proxy_autoreg_host CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.valuemaps CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.hosts_groups CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.sysmaps_links CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.opconditions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.lld_macro_path CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.history_text CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.task_close_problem CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.opcommand_grp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.graphs CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.group_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.alerts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.config_autoreg_tls CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.autoreg_host CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.maintenances_hosts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.item_application_prototype CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.triggers CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.opmessage CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.history_uint CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.history_str CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.host_tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.trends_uint CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.task_remote_command_result CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.sysmap_usrgrp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.expressions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.sysmap_user CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.proxy_history CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.profiles CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.corr_condition_tagvalue CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.opgroup CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.service_alarms CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.hstgrp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.services CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.sessions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.escalations CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.slideshow_usrgrp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.dservices CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.maintenances CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.corr_condition_tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.opcommand_hst CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.sysmaps_link_triggers CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.proxy_dhistory CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.opinventory CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.host_inventory CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.mappings CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.corr_condition_tagpair CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.correlation CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.opmessage_grp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.screen_user CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.trends CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.auditlog_details CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.rights CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.scripts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.corr_condition_group CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.acknowledges CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.sysmap_element_trigger CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.item_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.sysmap_url CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.widget_field CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.timeperiods CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.dashboard_user CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.task_remote_command CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.maintenances_windows CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.config CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.dashboard_usrgrp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.items_applications CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.slideshow_user CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.item_rtdata CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.sysmap_element_url CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.task_check_now CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.event_recovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.graphs_items CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.images CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.slides CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.users CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.auditlog CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.items CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.application_prototype CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.ids CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.functions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.icon_mapping CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.graph_theme CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.services_links CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.usrgrp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.sysmaps CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.group_prototype CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.applications CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.sysmap_shape CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.housekeeper CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.problem_tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.screen_usrgrp CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.task_acknowledge CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.httptest_field CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.drules CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.httptestitem CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.hosts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.conditions CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.interface CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.events CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.slideshows CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.corr_condition CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.item_preproc CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.application_template CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.globalmacro CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.media_type_param CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.history_log CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.globalvars CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.dhosts CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.application_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.hostmacro CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.opcommand CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.icon_map CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.problem CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.screens_items CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.task CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.operations CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.media CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.corr_operation CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.tag_filter CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.history CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.trigger_depends CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.httptest CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.event_suppress CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.sysmaps_elements CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.httpstep_field CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.event_tag CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.hosts_templates CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE zabbix.graph_discovery CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
          Last edited by kro; 27-05-2020, 02:13.

            Comment

            Previous template Next
            Working...