[ZBXNEXT-4569] "groups" (8.0.2) and "function" (8.0.1) are reserved words since MySQL 8.0 Created: 2017 Aug 02  Updated: 2024 Apr 10  Resolved: 2018 Jun 18

Status: Closed
Project: ZABBIX FEATURE REQUESTS
Component/s: API (A), Documentation (D), Frontend (F), Installation (I), Server (S)
Affects Version/s: None
Fix Version/s: 4.0.0alpha7, 4.0 (plan)

Type: New Feature Request Priority: Minor
Reporter: Peter Pasztor Assignee: Vladislavs Sokurenko
Resolution: Fixed Votes: 0
Labels: MySQL
Σ Remaining Estimate: Not Specified Remaining Estimate: Not Specified
Σ Time Spent: Not Specified Time Spent: Not Specified
Σ Original Estimate: Not Specified Original Estimate: Not Specified

Issue Links:
Causes
causes ZBX-14670 Wrong column name in zabbix 4.0.0-1.1... Closed
Duplicate
duplicates ZBXNEXT-4662 [Z3005] query failed: [1064] You have... Closed
duplicates ZBXNEXT-4644 Web display window data error Closed
duplicates ZBX-14705 zabbix3+ 在mysql8上无法使用 Closed
duplicates ZBX-14704 zabbix3+ mysql8 Closed
duplicates ZBX-14598 –Error in query [SELECT g.groupid,g.n... Closed
is duplicated by ZBX-14486 zabbix+mysql8.0.11 zabbix.groups syna... Closed
is duplicated by ZBX-13777 Error zabbix-proxy on MySQL 8.0.11 Closed
is duplicated by ZBX-14311 zabbix does not compile with MySQL 8.0.x Closed
is duplicated by ZBX-14367 zabbix server log: ERROR 1064 (42000)... Closed
Sub-Tasks:
Key
 Summary
 Type
 Status
 Assignee
ZBXNEXT-4570 Frontend - "groups" (8.0.2) and "func... Change Request (Sub-task) Closed Gregory Chalenko  
ZBXNEXT-4615 zabbix server fails after Upgrade fro... Change Request (Sub-task) Closed Andris Zeila  
Team: Team A
Team: Team A
Sprint: Sprint 14, Sprint 15, Sprint 16, Sprint 32, Sprint 33, Sprint 34, Sprint 35, Sprint 36
Story Points: 2

 Description   

Trying to install Zabbix 3.2.6, using MySQL 8.0.2 I got this message (among many similar ones):

   ...
    26:20170801:200803.540 completed 86% of database upgrade
    26:20170801:200804.646 completed 87% of database upgrade
    26:20170801:200805.131 completed 88% of database upgrade
    26:20170801:200805.935 [Z3005] query failed: [1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups
 (groupid)' at line 1 [alter table corr_condition_group add constraint c_corr_condition_group_2 foreign key (groupid) references groups (groupid)]
    26:20170801:200805.936 database upgrade failed

I checked, and as per the official MySQL documentation ( https://dev.mysql.com/doc/refman/8.0/en/keywords.html ), "groups" is added as a reserved word since version 8.0.2.

Also, as the schema.sql is properly backticked - there is a chance this requires a considerable refactoring effort to fix...

 Comments   
Comment by Peter Pasztor [ 2017 Aug 02 ]
  • Also affects 3.2.7
  • Verified that the problem does not appear when using MySQL 5.7.19
Comment by Vladislavs Sokurenko [ 2017 Aug 16 ]

I get following error

 16061:20170816:123626.324 [Z3005] query failed: [1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups (groupid) on delete cascade' at line 1 [alter table widget_field add constraint c_widget_field_2 foreign key (value_groupid) references groups (groupid) on delete cascade]
 16061:20170816:123626.324 database upgrade failed
Comment by Rostislav Palivoda [ 2017 Sep 11 ]

MySQL 8.0.2 not released yet. Proposed to postpone the issue until MySQL 8 official release.

Comment by Mikhail Grigorev [ 2018 Apr 23 ]

Hi

MySQL 8.0.11 is released in 19.04.2018

When will Zabbix developers be able to add support for MySQL 8.0?

Can I expect this in the next release?

Comment by Mikhail Grigorev [ 2018 Apr 23 ]

table groups to grp ?

or

table groups to groups_users ?

Comment by Tomasz Kłoczko [ 2018 May 02 ]

Can someone with MariaDB devel env test patch attached in ZBX-14311? Just tested my patch on MySQL 5.7.22 and does not causes compile issues. Only after about two days of using zabbix 3.4.8 binaries which have been using MySQL 8.0.11 client ABI I've decided to upgrade MySQL server as well and of course I the same issues with group and function keywords.  So this would be about zabbix and MySQL 8.0.x client API compliance. With ZBX-14311 patch zabbix will be MySQL 8.0.11 client API compliant

Second thing: if current zabbix is not ready to be used with MySQL 8.0.x server maybe it would be good to add a check during db connection initialization and if 8.0.x server found just print error message and exit with error message?

Comment by Vladislavs Sokurenko [ 2018 May 02 ]

Hello kloczek if you are interested you can have a look at development branch:
svn://svn.zabbix.com/branches/dev/ZBX-12451

my_bool is used as per MySQL documentation:
https://dev.mysql.com/doc/refman/5.7/en/c-api-auto-reconnect.html

If we are to use _Bool then following warnings appears

main.c: In function ‘main’:
main.c:3:2: warning: ISO C90 does not support boolean types [-Wpedantic]

 

Comment by Vladislavs Sokurenko [ 2018 May 28 ]

Implemented in:

  • 4.0.0alpha7 r81276,81314
Comment by richlv [ 2018 Jun 15 ]

(1) Looks like these changes have not made it into https://www.zabbix.com/documentation/4.0/manual/installation/upgrade_notes_400 .

martins-v RESOLVED in the upgrade notes for 4.0.

sasha In this section, changes in the database should be mentioned. The 'groups' table has been renamed to 'hstgrp', the 'function' field in the 'functions' table - to 'name'

REOPENED

martins-v Thanks, updated the upgrade note entry. RESOLVED

sasha It seems we use double quotes instead of single quotes. Or am I wrong?

martins-v There isn't really a convention about it, because it is not that often we have to document database changes in public documentation. I try to use single quotes as much as possible in dokuwiki, because they are not smartly converted by dokuwiki and I don't have to escape the smart conversion. To me single quotes also seem more "technical" than double quotes.

sasha Thanks for explanation! CLOSED

Generated at Sat Apr 20 01:21:07 EEST 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.