[
https://issues.apache.org/jira/browse/AMBARI-26314?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17932036#comment-17932036
]
Basapuram Kumar commented on AMBARI-26314:
------------------------------------------
For fresh / direct installations, this will go seamlessly.
For migrations, here are the observations and detailed analysis.
During the migration, the existing {{GROUPS}} table in the Ambari database is
causing conflicts due to its name. As a result, the Ambari UI fails to start
and throws the following error when attempting to log in.
During the migrations, after starting ambari server, and at the time of logging
into Ambari server, we will be hitting the below issue.
{panel}
Internal Exception: java.sql.SQLSyntaxErrorException: Table
'ambari.groups_info' doesn't exist
Error Code: 1146
Call: SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
groups_info WHERE (group_id = ?)
bind => [1 parameter bound]
Query: ReadObjectQuery(name="group" referenceClass=GroupEntity )
{panel}
To resolve this issue, rename the {{GROUPS}} table to {{groups_info}} in the
Ambari backend database.
h2. For MySQL backed clusters,
Log in to the MySQL server hosting the Ambari database.
{code:java}
mysql> use ambari;
mysql> show tables lIKE 'groups'; {code}
Response
{code:java}
+---------------------------+
| Tables_in_ambari (groups) |
+---------------------------+
| groups |
+---------------------------+ {code}
Verify the data
{code:java}
mysql> select * from `groups`; {code}
RenameRename the table *groups* to *groups_info* using the following command:
{code:java}
mysql> RENAME TABLE `groups` TO groups_info; {code}
now verify the same data as it was in groups table earlier.
Verify the data
{code:java}
mysql> select * from groups_info; {code}
<It should have same data as it was earlier in *groups* table>
h2. For Oracle
Log in to the Oracle server hosting the Ambari database.
Verify the existing GROUPS table
{code:java}
SQL> SELECT table_name FROM user_tables WHERE table_name LIKE '%GROUPS%';
{code}
Response
{code:java}
TABLE_NAME
---------------------------
GROUPS {code}
Rename the table *groups* to *groups_info* using the following command:
{code:java}
SQL> RENAME GROUPS TO GROUPS_INFO ; {code}
Successful rename operation resulted output as *Table renamed.*
Verify the data as well once rename is completed.
h2. For Postgres Backed databases.
Log in to the Postgres server hosting the Ambari database.
Verify the existing *GROUPS* table
{code:java}
ambari=> \dt+
{code}
Verify the *GROUPS* table
Rename the table {{groups}} to {{groups_info}} using the following command:
{code:java}
ambari=> ALTER TABLE groups RENAME TO groups_info;
{code}
Successful rename operation resulted output as {*}ALTER TABLE{*}.
Verify the data as well once rename is completed.
> Ambari's ldap-sync issue for oralce-19.x+
> -----------------------------------------
>
> Key: AMBARI-26314
> URL: https://issues.apache.org/jira/browse/AMBARI-26314
> Project: Ambari
> Issue Type: Bug
> Components: ambari-server
> Environment: Tried on RHEl-8 & RHEL-9
> Oracle-19c,20.x
> MySQL-8.x
>
> Reporter: Basapuram Kumar
> Priority: Major
> Attachments: Screenshot 2025-03-02 at 17.10.01.png,
> image-2025-03-02-17-33-02-167.png, image-2025-03-02-17-33-26-676.png,
> image-2025-03-02-18-03-54-383.png
>
> Time Spent: 10m
> Remaining Estimate: 0h
>
> During the test with Oracle-19.x and higher version, observed that ldap
> syncing is failing for its groups with. the below error.
>
>
> {code:java}
> ambari-server sync-ldap --all {code}
>
>
> Response:
>
> {code:java}
> [root@ora19codp32333 conf]# ambari-server sync-ldap --all
> Using python /usr/bin/python3.11
> Syncing with LDAP...
> Enter Ambari Admin login: admin
> Enter Ambari Admin password:Fetching LDAP configuration from DB.
> Syncing all...ERROR: Exiting with exit code 1.
> REASON: Caught exception running LDAP sync. Exception [EclipseLink-4002]
> (Eclipse Persistence Services - 2.6.2.v20151217-774c696):
> org.eclipse.persistence.exceptions.DatabaseException
> Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00942: table or
> view does not existError Code: 942
> Call: SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> "groups"
> Query: ReadAllQuery(referenceClass=GroupEntity sql="SELECT group_id,
> group_name, group_type, ldap_group, principal_id FROM "groups"") {code}
> !image-2025-03-02-18-03-54-383.png!
>
> For sample LDAP setup , used the knox's Demo LDAP.
> +*Repro steps.*+
> For Setting up LDAP, please add the knox into the Ambari,
>
> !image-2025-03-02-17-33-02-167.png!
>
> Followed by start the Demo LDAP.
> !image-2025-03-02-17-33-26-676.png!
> h2. Install Ldapsearch
> #
> \{{ dnf install -y openldap-clients}}
> h2. Search User
> #
> {code:java}
> ldapsearch -x -H ldap://`hostname -f`:33389 \
> -D "uid=admin,ou=people,dc=hadoop,dc=apache,dc=org" \
> -w admin-password \
> -b "dc=hadoop,dc=apache,dc=org" {code}
> Response
> {code:java}
> # extended LDIF
> #
> # LDAPv3
> # base <dc=hadoop,dc=apache,dc=org> with scope subtree
> # filter: (objectclass=*)
> # requesting: ALL
> ## sam, people, hadoop.apache.org
> dn: uid=sam,ou=people,dc=hadoop,dc=apache,dc=org
> sn: sam
> cn: sam
> objectclass: top
> objectclass: person
> objectclass: organizationalPerson
> objectclass: inetOrgPerson
> userpassword:: c2FtLXBhc3N3b3Jk
> uid: sam# analyst, groups, hadoop.apache.org
> dn: cn=analyst,ou=groups,dc=hadoop,dc=apache,dc=org
> cn: analyst
> objectclass: groupofnames
> objectclass: top
> member: uid=sam,ou=people,dc=hadoop,dc=apache,dc=org
> member: uid=tom,ou=people,dc=hadoop,dc=apache,dc=org
> description: analyst group# scientist, groups, hadoop.apache.org
> dn: cn=scientist,ou=groups,dc=hadoop,dc=apache,dc=org
> cn: scientist
> objectclass: groupofnames
> objectclass: top
> member: uid=sam,ou=people,dc=hadoop,dc=apache,dc=org
> description: scientist group# admin, people, hadoop.apache.org
> dn: uid=admin,ou=people,dc=hadoop,dc=apache,dc=org
> sn: Admin
> cn: Admin
> objectclass: top
> objectclass: person
> objectclass: organizationalPerson
> objectclass: inetOrgPerson
> userpassword:: YWRtaW4tcGFzc3dvcmQ=
> uid: admin# keyadmin, people, hadoop.apache.org
> dn: uid=keyadmin,ou=people,dc=hadoop,dc=apache,dc=org
> sn: keyAdmin
> cn: keyAdmin
> objectclass: top
> objectclass: person
> objectclass: organizationalPerson
> objectclass: inetOrgPerson
> userpassword:: a2V5YWRtaW4tcGFzc3dvcmQ=
> uid: keyadmin# people, hadoop.apache.org
> dn: ou=people,dc=hadoop,dc=apache,dc=org
> ou: people
> objectclass: top
> objectclass: organizationalUnit# admin, groups, hadoop.apache.org
> dn: cn=admin,ou=groups,dc=hadoop,dc=apache,dc=org
> cn: admin
> objectclass: groupofnames
> objectclass: top
> member: uid=admin,ou=people,dc=hadoop,dc=apache,dc=org
> description: admin group# tom, people, hadoop.apache.org
> dn: uid=tom,ou=people,dc=hadoop,dc=apache,dc=org
> sn: tom
> cn: tom
> objectclass: top
> objectclass: person
> objectclass: organizationalPerson
> objectclass: inetOrgPerson
> userpassword:: dG9tLXBhc3N3b3Jk
> uid: tom# hadoop.apache.org
> dn: dc=hadoop,dc=apache,dc=org
> dc: hadoop
> o: Hadoop
> objectclass: top
> objectclass: organization
> objectclass: dcObject# groups, hadoop.apache.org
> dn: ou=groups,dc=hadoop,dc=apache,dc=org
> ou: groups
> objectclass: top
> objectclass: organizationalUnit
> description: generic groups branch# guest, people, hadoop.apache.org
> dn: uid=guest,ou=people,dc=hadoop,dc=apache,dc=org
> sn: User
> cn: Guest
> objectclass: top
> objectclass: person
> objectclass: organizationalPerson
> objectclass: inetOrgPerson
> userpassword:: Z3Vlc3QtcGFzc3dvcmQ=
> uid: guest# search result
> search: 2
> result: 0 Success# numResponses: 12
> # numEntries: 11 {code}
> h2. Amabri Server Setup-ldap
>
> {code:java}
> ambari-server setup-ldap {code}
>
> Response and provide the prompted details
> {code:java}
> [root@ora19codp32333 conf]# ambari-server setup-ldap
> Using python /usr/bin/python3.11
> Enter Ambari Admin login: admin
> Enter Ambari Admin password: <admin>Fetching LDAP configuration from DB.
> Primary LDAP Host (ora19codp32333.acceldata.ce):
> Primary LDAP Port (33389):
> Secondary LDAP Host <Optional>:
> Secondary LDAP Port <Optional>:
> Use SSL [true/false] (false):
> User object class (inetOrgPerson): person
> User ID attribute (uid):
> Group object class (groupOfNames):
> Group name attribute (cn):
> Group member attribute (member):
> Distinguished name attribute (dn):
> Search Base (dc=hadoop,dc=apache,dc=org):
> Referral method [follow/ignore] (follow):
> Bind anonymously [true/false] (false):
> Bind DN (uid=admin,ou=people,dc=hadoop,dc=apache,dc=org):
> Enter Bind DN Password: <admin-password>
> Confirm Bind DN Password: <admin-password>
> Handling behavior for username collisions [convert/skip] for LDAP sync
> (skip): convert
> Force lower-case user names [true/false] (false):true
> Results from LDAP are paginated when requested [true/false] (true):
> ====================
> Review Settings
> ====================
> Primary LDAP Host (ora19codp32333.acceldata.ce): ora19codp32333.acceldata.ce
> Primary LDAP Port (33389): 33389
> Use SSL [true/false] (false): false
> User object class (inetOrgPerson): person
> User ID attribute (uid): uid
> Group object class (groupOfNames): groupOfNames
> Group name attribute (cn): cn
> Group member attribute (member): member
> Distinguished name attribute (dn): dn
> Search Base (dc=hadoop,dc=apache,dc=org): dc=hadoop,dc=apache,dc=org
> Referral method [follow/ignore] (follow): follow
> Bind anonymously [true/false] (false): false
> Handling behavior for username collisions [convert/skip] for LDAP sync
> (skip): convert
> Force lower-case user names [true/false] (false): true
> Results from LDAP are paginated when requested [true/false] (true): true
> ambari.ldap.connectivity.bind_dn:
> uid=admin,ou=people,dc=hadoop,dc=apache,dc=org
> ambari.ldap.connectivity.bind_password: *****
> Save settings [y/n] (y)?
> Saving LDAP properties...
> Saving LDAP properties finished
> Ambari Server 'setup-ldap' completed successfully. {code}
> h2. Ambari Server - sync-ldap
>
> {code:java}
> [root@ora19codp32333 conf]# ambari-server sync-ldap --all
> Using python /usr/bin/python3.11
> Syncing with LDAP...
> Enter Ambari Admin login: admin
> Enter Ambari Admin password:Fetching LDAP configuration from DB.
> Syncing all...ERROR: Exiting with exit code 1.
> REASON: Caught exception running LDAP sync. Exception [EclipseLink-4002]
> (Eclipse Persistence Services - 2.6.2.v20151217-774c696):
> org.eclipse.persistence.exceptions.DatabaseException
> Internal Exception: java.sql.SQLSyntaxErrorException: ORA-00942: table or
> view does not existError Code: 942
> Call: SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> "groups"
> Query: ReadAllQuery(referenceClass=GroupEntity sql="SELECT group_id,
> group_name, group_type, ldap_group, principal_id FROM "groups"") {code}
>
>
> Logging into Oracle-19c
>
> {code:java}
> sqlplus ambari77/bigdata@//localhost:1521/MORALPDB {code}
>
>
>
> {code:java}
> SQL> DESC GROUPS;
> Name Null? Type
> ----------------------------------------- --------
> ----------------------------
> GROUP_ID NOT NULL NUMBER(10)
> PRINCIPAL_ID NOT NULL NUMBER(19)
> GROUP_NAME NOT NULL VARCHAR2(255)
> LDAP_GROUP NUMBER(10)
> GROUP_TYPE NOT NULL VARCHAR2(255) {code}
>
>
> Select the table
>
> {code:java}
> select * from GROUPS;
> -- no rows selected{code}
> Verify the failed query, which failed during the sync-ldap
>
>
> [This is the exact query failed during syncing]
>
> {code:java}
> SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> "groups" {code}
> Response
>
>
> {code:java}
> SQL> SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> "groups"
> 2 ;
> SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> "groups"
> *
> ERROR at line 1:
> ORA-00942: table or view does not exist {code}
>
>
>
> Lets try other variants of groups to make it work from oracle-19c+
> The below combination of queries works fine.
>
> {code:java}
> SQL> SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> "GROUPS";
> no rows selected
> SQL> SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> groups;
> no rows selected
> SQL> SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> GROUPS;
> no rows selected {code}
>
>
> If we execute the same queries in {*}MySQL-8.x+{*}, here are the results.
>
> {code:java}
> mysql> SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> "GROUPS";
> 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"' at line 1
> mysql> SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> groups;
> 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' at line 1
>
> mysql> SELECT group_id, group_name, group_type, ldap_group, principal_id FROM
> GROUPS;
> 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' at line 1{code}
>
>
> And one more thing is that if we use directtly table name as groups, it fails
> saying as its a *reserved key.*
>
> {code:java}
> mysql> SELECT group_id, group_name, group_type, ldap_group, principal_id
> FROM groups;
> 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' at line 1 {code}
>
>
> +*Recommendation*+
> I would like to recommend a suggestion, which will be compatible to both
> MySQL >= 8.x and Oracle >= 19.x
>
> Soon I am going to open a PR, which will address this fix.
> With the changes, added in the PR, able to work with the both MySQL and
> Oracle.
>
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
