Support Questions
Find answers, ask questions, and share your expertise

MySQL Table: Groups creation and usage Issue

MySQL Table: Groups creation and usage Issue

Issue Detail: When we were creating Ambari meta tables in MySQL, there was a table with name: groups. MYSQL don't allow to create table with this name, so we renamed this to grps. Now when we are running command: ambari-server sync-ldap, it is trying to connect with table: groups, i can see the whole select query using table: groups. Any advice.

16 REPLIES 16

Re: MySQL Table: Groups creation and usage Issue

Mentor

@Mustafa Ali Qizilbash

I have done 100's of HDP installations while using Mysql as the metadata database but never came across an issue with this table, Unless you had a previous Ambari installation and you are using the same database but still that doesn't add up!

Indeed groups is an Ambari internal table create in MySQL, you shouldn't rename it

mysql> describe groups; 
+--------------+--------------+------+-------+---------+-------+ 
| Field        | Type         | Null | Key   | Default | Extra | 
+--------------+--------------+------+-------+---------+-------+ 
| group_id     | int(11)      | NO   | PRI   | 0       |       | 
| principal_id | bigint(20)   | NO   | MUL   | NULL    |       | 
| group_name   | varchar(255) | NO   | MUL   | NULL    |       | 
| ldap_group   | int(11)      | NO   |       | 0       |       | 
| group_type   | varchar(255) | NO   |       | LOCAL   |       | 
+--------------+--------------+------+-----+---------+-------+ 
5 rows in set (0.00 sec)

Just for curiosity what exactly were you installing to get this conflict?

Make a copy of the grp table and run the rename below,with correct ownership

RENAME TABLE grp TO groups;

Please elaborate if possible

Re: MySQL Table: Groups creation and usage Issue

Well, somehow, we are facing this issue.

MYSQL version = 8.0.11

HDP version= 2.6.3

Ambar version = 2.6.0.0

After it, we tried creating table with name groups and it failed. Then when simply changed the name to groups1, it got created.

As this is the first time, we are using MySQL as metastore, am sure, we must be missing a very minor fix :-).

Looking forward.

mysql> SHOW GLOBAL VARIABLES LIKE '%version%';

+-------------------------+------------------------------+

| Variable_name | Value |

+-------------------------+------------------------------+

| innodb_version | 8.0.11 |

| protocol_version | 10 |

| slave_type_conversions | |

| tls_version | TLSv1,TLSv1.1,TLSv1.2 |

| version | 8.0.11 |

| version_comment | MySQL Community Server - GPL |

| version_compile_machine | x86_64 |

| version_compile_os | Linux | | version_compile_zlib | 1.2.11 |

+-------------------------+------------------------------+

9 rows in set (0.00 sec)

mysql> CREATE TABLE groups ( -> group_id INTEGER,

-> principal_id BIGINT NOT NULL,

-> group_name VARCHAR(255) NOT NULL,

-> ldap_group INTEGER NOT NULL DEFAULT 0,

-> group_type VARCHAR(255) NOT NULL DEFAULT 'LOCAL',

-> CONSTRAINT PK_groups PRIMARY KEY (group_id),

-> CONSTRAINT FK_groups_principal_id FOREIGN KEY (principal_id) REFERENCES adminprincipal(principal_id),

-> CONSTRAINT UNQ_groups_0 UNIQUE (group_name, ldap_group));

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

mysql>

mysql>

mysql> CREATE TABLE groups1 (

-> group_id INTEGER,

-> principal_id BIGINT NOT NULL,

-> group_name VARCHAR(255) NOT NULL,

-> ldap_group INTEGER NOT NULL DEFAULT 0,

-> group_type VARCHAR(255) NOT NULL DEFAULT 'LOCAL',

-> CONSTRAINT PK_groups1 PRIMARY KEY (group_id),

-> CONSTRAINT FK_groups_principal_id1 FOREIGN KEY (principal_id) REFERENCES adminprincipal(principal_id),

-> CONSTRAINT UNQ_groups_0 UNIQUE (group_name, ldap_group));

Query OK, 0 rows affected (0.14 sec)

Re: MySQL Table: Groups creation and usage Issue

Above 2 create command are just to show, MySQL is not accepting groups as table name where accepting groups1 as table name.

Re: MySQL Table: Groups creation and usage Issue

Mentor

@Mustafa Ali Qizilbash

Ambari generates those tables when you run

SOURCE Ambari-DDL-MySQL-CREATE.sql;

Why did you have to do it manually?

Re: MySQL Table: Groups creation and usage Issue

Nops, not doing manually, just showing, when Ambari-DDL-MySQL-CREATE.sql script ran, above mentioned create table command to create table groups gave this 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

After R&D, came to know groups is a reverse word in MySQL, so table with groups name can not be created. We changed groups to grps and it worked out. In fact, in the same script, right after groups create table command, there is a command create members table, which uses table groups column as fkey, so we had to made a change in that create command as well, to reflect grps instead groups.

So, is there any thing we are missing like GROUPS as a table name can be used as table name in MySQL?

Looking forward.

Re: MySQL Table: Groups creation and usage Issue

Mentor

@Mustafa Ali Qizilbash

After creating the Ambari user !!

Did you run the following sequence where ambari is the ambari database and ambari_user is your ambari user and -pambari_user_password is the password

mysql -u ambari_user -pambari_user_password
CREATE DATABASE ambari;
USE ambari;
SOURCE /var/lib/ambari-server/resources/Ambari-DDL-MySQL-CREATE.sql;

Note the use ambari switch command within the MYSQL Cli

Re: MySQL Table: Groups creation and usage Issue

Yes, and that is where, we faced the create table groups error.

This is what we did

-----

mysql -u ambari -p

CREATE DATABASE ambari;

USE ambari;

Before running following script, open it and change

- the name of table groups to grps

- the Fkey in table members from groups to grps

Below script can be run at the time of ambari-server setup as well.

SOURCE /var/lib/ambari-server/resources/Ambari-DDL-MySQL-CREATE.sql;

All the 103 tables will be created.

commit;

quit;

-----

Once above is done, then

ambari-server setup

WARNING: SELinux is set to 'permissive' mode and temporarily disabled.

OK to continue [y/n] (y)? <Press Enter>

Customize user account for ambari-server daemon [y/n] (n)? <Press Enter>

Checking JDK...

[1] Oracle JDK 1.8 + Java Cryptography Extension (JCE) Policy Files 8

[2] Oracle JDK 1.7 + Java Cryptography Extension (JCE) Policy Files 7

[3] Custom JDK

==============================================================================

Enter choice (1): 3

Path to JAVA_HOME: /usr/java/jdk

Enter advanced database configuration [y/n] (n)? y

Configuring database...

==============================================================================

Choose one of the following options:

[1] - PostgreSQL (Embedded)

[2] - Oracle

[3] - MySQL / MariaDB

[4] - PostgreSQL

[5] - Microsoft SQL Server (Tech Preview)

[6] - SQL Anywhere

[7] - BDB

==============================================================================

Enter choice (1): 3

Hostname (localhost): HDP-NN.com

Port (3306): <Press Enter>

Database name (ambari): ambari

Username (ambari): ambari

Enter Database Password (bigdata): welcome1

Proceed with configuring remote database connection properties [y/n] (y)? y

Extracting system views...

.......ambari-admin-2.6.0.0.267.jar

....

Adjusting ambari-server permissions and ownership...

Ambari Server 'setup' completed successfully.

Re: MySQL Table: Groups creation and usage Issue

I believe, its MySQL version issue, we have installed a standalone instance of MySQL 5.7 and tried to create table with name groups and it worked :-).

Now we are going to remove MySQL 8.0 from our cluster and going to have MySQL 5.7.

Lets not close this thread till we test and confirm.

Stay put.

Cheers,

Re: MySQL Table: Groups creation and usage Issue

Mentor

@Mustafa Ali Qizilbash

That explains why you should stick strictly to the Hortonworks documentation. If you'd mentioned the version I would have outright told you it's not included in the compatible matrice

Hortonworks does rigorous testing against third-party tools before certifying it against its products. I have done 100's of installation but NEVER came across such an issue ...lessons learned to stick to technical documentation :-)

Update the thread and close after the thorough test but I am positive it will work like a charm.