Problem setup ambari 2.7.1.0 with mysql 8.0.13

[#1]

when i exec mysql> source /var/lib/ambari-server/resources/Ambari-DDL-MySQL-CREATE.sql;

then i found one table cannot be created in mysql 8.0.3

CREATE TABLE members (

member_id INTEGER, group_id INTEGER NOT NULL,

user_id INTEGER NOT NULL,

CONSTRAINT PK_members PRIMARY KEY (member_id),

CONSTRAINT FK_members_group_id FOREIGN KEY (group_id) REFERENCES groups (group_id),

CONSTRAINT FK_members_user_id FOREIGN KEY (user_id) REFERENCES users (user_id),

CONSTRAINT UNQ_members_0 UNIQUE (group_id, user_id));

error:

ERROR 1064 (42000): 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 ( group_id INTEGER, principal_id BIGINT NOT NULL, group_name VARCHA' at line 1 ERROR 1064 (42000): 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 (group_id), CONSTRAINT FK_members_user_id FOREIGN KEY (user_id) REFEREN' at line 6

please tell me how should i change the syntax,thanks

[#2 · Accepted solution]

@Theef Qian

According to the SupportMatrics MySQL 8 is not Supported Database for Ambari 2.7.1

Please take a look at: https://supportmatrix.hortonworks.com/

With Ambari 2.7.1 you should be using MySQL versions as MySQL 5.7 OR Maria DB 10.2

.

So even if you can fix the mentioned table .. still there will be possibility that when ambari will try to run some other Groups relatd query then it might fail again. Hence better you use the tested and certified database.

[#3]

thanks for replying,i will change the version

[#4]

yes, i have also met similar problem with ambari 2.7.3. the reason is that in mysql8.0, "groups" has been a keyword.

i modified the sql script to create the table not calling "groups", and then change it back to "groups" manully in mysql.

however, in ambari, I can't use the group management function. I think I should modify the sql statements about "groups" in ambari, but i can't find it

hope the new version will fix it

[#5]

Windows 10 comes with various features and settings. When the sync your settings are turned on, it tracks the settings that users take into account and set them for further use on any window 10 device.