SCM with external database

[#1]

Hi,

using director i am trying to create deployment with external mysql db. Got stuck with access issue .

[2019-03-14 23:36:15.024 +0500] INFO  [io-thread-4] - - - - - ssh:10.142.0.54: Installed:
[2019-03-14 23:36:15.024 +0500] INFO  [io-thread-4] - - - - - ssh:10.142.0.54: cloudera-manager-server.x86_64 0:6.1.1-853290.el7
[2019-03-14 23:36:15.348 +0500] INFO  [p-d9121c3c8c30-DefaultBootstrapDeploymentJob] b8c5a817-be73-497e-8ee2-891738075972 POST /api/d6.1/environments/gcp-env-01/deployments com.cloudera.launchpad.bootstrap.InstallMySQLDriver - c.c.launchpad.pipeline.AbstractJob: Installing MySQL driver on instance 6564aae4-b7cb-4b6a-a2e0-211cf08b457b
[2019-03-14 23:36:15.354 +0500] INFO  [p-d9121c3c8c30-DefaultBootstrapDeploymentJob] b8c5a817-be73-497e-8ee2-891738075972 POST /api/d6.1/environments/gcp-env-01/deployments com.cloudera.launchpad.bootstrap.InstallMySQLDriver - c.c.l.m.e.InstanceServiceEndpoints: Waiting until 2019-03-14T23:51:15.354+05:00 to discover SSH service endpoints for instance PluggableComputeInstance{ipAddress=10.142.0.54, delegate=null, hostEndpoints=[HostEndpoint{hostAddressString='10.142.0.54', hostAddress=Optional.of(/10.142.0.54)}, HostEndpoint{hostAddressString='35.196.30.180', hostAddress=Optional.of(/35.196.30.180)}]} Instance{virtualInstance=VirtualInstance{id='6564aae4-b7cb-4b6a-a2e0-211cf08b457b', template=InstanceTemplate{name='cm-centos7', type='n1-standard-1', image='https://www.googleapis.com/compute/v1/projects/centos-cloud/global/images/centos-7-v20190213', rackId='/default', bootstrapScriptsArePresent=true, preTerminateScriptsArePresent=false, config={usePreemptibleInstances=false, dataDiskSizeGb=20, instanceNamePrefix=cm, zone=us-east1-b, bootDiskType=Standard, networkName=default, dataDiskType=Standard, dataDiskCount=1, bootDiskSizeGb=60}, tags={owner=ranashd}, normalizeInstance=true, normalizationConfig=Optional.absent(), sshUsername=Optional.of(ranashd), sshHostKeyRetrievalType=NONE, groupId=Optional.of(60f3f2ee-815c-4980-9879-2a024ed83457)}}, capabilities=Optional.of(Capabilities{operatingSystemType=REDHAT_COMPATIBLE, operatingSystemVersion=REDHAT_COMPATIBLE_7, virtualizationType=UNKNOWN, packageManager=Optional.of(YUM), javaVendor=Optional.absent(), javaVersion=Optional.absent(), pythonVersion=Optional.of(2.7.5), passwordlessSudoEnabled=true, selinuxEnabled=true, iptablesEnabled=true, dnsConfigured=true, fqdn=Optional.of(cm-6564aae4-b7cb-4b6a-a2e0-211cf08b457b.us-east1-b.c.cloudera-232012.internal), clouderaManagerAgentInstalled=false, customScriptPaths={}}), cmHostId=Optional.absent(), cmHostUrl=Optional.absent(), hostKeyFingerprints=[], validationConditions=[], state=InstanceState{status=RUNNING, lastReported=2019-03-14T23:32:13.142+05:00, lastChecked=2019-03-14T23:32:13.142+05:00}, providerInstanceId=Optional.absent()}
[2019-03-14 23:36:15.354 +0500] INFO  [p-d9121c3c8c30-DefaultBootstrapDeploymentJob] b8c5a817-be73-497e-8ee2-891738075972 POST /api/d6.1/environments/gcp-env-01/deployments com.cloudera.launchpad.bootstrap.InstallMySQLDriver - c.c.l.m.e.InstanceServiceEndpoints: Checking every 30000 milliseconds
[2019-03-14 23:36:15.591 +0500] INFO  [p-d9121c3c8c30-DefaultBootstrapDeploymentJob] b8c5a817-be73-497e-8ee2-891738075972 POST /api/d6.1/environments/gcp-env-01/deployments com.cloudera.launchpad.pipeline.SudoRunScript - c.c.launchpad.pipeline.SudoRunScript: Setting Cloudera Altus Director environment variables.
[2019-03-14 23:36:16.482 +0500] INFO  [io-thread-3] - - - - - ssh:10.142.0.54: Jar successfully installed at /usr/share/java/mysql-connector-java.jar
[2019-03-14 23:36:17.710 +0500] INFO  [io-thread-2] - - - - - c.c.l.p.ssh.SshJobFireAndForget: Succeeded in running script (crc32: 749b03cf)
[2019-03-14 23:36:17.903 +0500] INFO  [p-d9121c3c8c30-DefaultBootstrapDeploymentJob] b8c5a817-be73-497e-8ee2-891738075972 POST /api/d6.1/environments/gcp-env-01/deployments com.cloudera.launchpad.pipeline.SetStatusJob - c.c.launchpad.pipeline.AbstractJob: Configuring external MYSQL database for Cloudera Manager
[2019-03-14 23:36:17.965 +0500] INFO  [io-thread-5] - - - - - ssh:10.142.0.54: /opt/cloudera/cm
[2019-03-14 23:36:17.966 +0500] INFO  [p-d9121c3c8c30-DefaultBootstrapDeploymentJob] b8c5a817-be73-497e-8ee2-891738075972 POST /api/d6.1/environments/gcp-env-01/deployments com.cloudera.launchpad.bootstrap.deployment.ConfigureExternalDatabaseForClouderaManager - c.c.l.c.ClouderaManagerDatabaseProvider: Creating database via command line: sudo '/opt/cloudera/cm/schema/create_database_scm_lsq85p3anqialkp12nogfhig3j.sh' -h 'dbmysql01.us-east1-b.c.cloudera-232012.internal' -P 3306 -u 'scm' -p'********' --src-host '%' -t '/opt/cloudera/cm' 'mysql' 'scm_lsq85p3anqialkp12nogfhig3j' 'uxnlmrno' '********'
[2019-03-14 23:36:17.966 +0500] INFO  [p-d9121c3c8c30-DefaultBootstrapDeploymentJob] b8c5a817-be73-497e-8ee2-891738075972 POST /api/d6.1/environments/gcp-env-01/deployments com.cloudera.launchpad.bootstrap.deployment.ConfigureExternalDatabaseForClouderaManager - c.c.l.c.ClouderaManagerDatabaseProvider: Uploading script create_database_scm_lsq85p3anqialkp12nogfhig3j.sh
[2019-03-14 23:36:18.030 +0500] INFO  [p-d9121c3c8c30-DefaultBootstrapDeploymentJob] b8c5a817-be73-497e-8ee2-891738075972 POST /api/d6.1/environments/gcp-env-01/deployments com.cloudera.launchpad.bootstrap.deployment.ConfigureExternalDatabaseForClouderaManager - c.c.l.c.ClouderaManagerDatabaseProvider: Uploaded to temporary location /tmp/create_database_scm_lsq85p3anqialkp12nogfhig3j.sh
[2019-03-14 23:36:18.030 +0500] INFO  [p-d9121c3c8c30-DefaultBootstrapDeploymentJob] b8c5a817-be73-497e-8ee2-891738075972 POST /api/d6.1/environments/gcp-env-01/deployments com.cloudera.launchpad.bootstrap.deployment.ConfigureExternalDatabaseForClouderaManager - c.c.l.c.ClouderaManagerDatabaseProvider: Setting execute permission and ownership on script and moving to correct location (passwordless sudo expected)
[2019-03-14 23:36:18.114 +0500] INFO  [p-d9121c3c8c30-DefaultBootstrapDeploymentJob] b8c5a817-be73-497e-8ee2-891738075972 POST /api/d6.1/environments/gcp-env-01/deployments com.cloudera.launchpad.bootstrap.deployment.ConfigureExternalDatabaseForClouderaManager - c.c.l.c.ClouderaManagerDatabaseProvider: Executing command line
[2019-03-14 23:36:18.258 +0500] INFO  [io-thread-6] - - - - - ssh:10.142.0.54: JAVA_HOME=/usr/java/jdk1.8.0_181-cloudera
[2019-03-14 23:36:19.504 +0500] INFO  [io-thread-6] - - - - - ssh:10.142.0.54: [                          main] DbProvisioner                  ERROR Exception when creating/dropping database with user 'scm' and jdbc url 'jdbc:mysql://dbmysql01.us-east1-b.c.cloudera-232012.internal:3306/?useUnicode=true&characterEncoding=UTF-8'
[2019-03-14 23:36:19.504 +0500] INFO  [io-thread-6] - - - - - ssh:10.142.0.54: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user 'scm'@'%' to database 'scm_lsq85p3anqialkp12nogfhig3j'
[2019-03-14 23:36:19.504 +0500] INFO  [io-thread-6] - - - - - ssh:10.142.0.54: at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)[:1.8.0_181]

[#2]

It looks like your scm user doesn't have proper privileges to access the database server. This could be a problem with the grants in your mysql instance.

[2019-03-14 23:36:19.504 +0500] INFO  [io-thread-6] - - - - - ssh:10.142.0.54: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user 'scm'@'%' to database 'scm_lsq85p3anqialkp12nogfhig3j'
[2019-03-14 23:36:19.504 +0500] INFO  [io-thread-6] - - - - - ssh:10.142.0.54: at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)[:1.8.0_181]

Cloudera Altus Director requires a username with full access to the database.

https://www.cloudera.com/documentation/director/latest/topics/director_external_db_using.html

[#3]

Hi,

Thanks for helpling me, i have followed the intruction on the using external database. i alos notes user "scm" only got access to scm database where the error show that script is trying to create or drop database scm_xxxxxx. This is stranage script is trying to create another database scm_xxxx. 

Regards,

Shahid Rana

[#4]

If Director is creating the database via templates it will append a randomly generated string to the scm prefix so that multiple deployments can use the same database server without naming conflicts.  The user you are using needs full administrative privileges to the entire database instance if going this route.  

If you are wanting to specify a pre-created database name and user, and only provide the user access to that one database, you will need to make sure to use something like the example in the attached link and deploy your environments via a conf file.

https://www.cloudera.com/documentation/director/latest/topics/director_external_db_using.html

cloudera-manager {
   # ...
   databases {
      CLOUDERA_MANAGER {
         name: scm1
         type: mysql
         host: 1.2.3.4
         port: 3306
         user: scmuser
         password: scmpassword
      }
   # ...
   }
}

type - The type of database, “MYSQL” or “POSTGRESQL.”
hostname - The name of the server host.
port - The listening port of the server.
name - The name of the database on the server.
username - The name of the user account having full access to the database.
password - The password for the user account.

Hope this helps!

[#5]

Hi There,

Got the rational mentioned in the response. I have grant the permission and i am able to create/drop database from mysql console but got the same error.

[2019-03-18 23:27:16.011 +0500] INFO  [p-d5772dd1d8f2-DefaultBootstrapDeploymentJob] 7ed3eb50-3516-4c79-a946-868d14598152 POST /api/d6.1/environments/gcp-env-01/deployments com.cloudera.launchpad.bootstrap.deployment.ConfigureExternalDatabaseForClouderaManager - c.c.l.c.ClouderaManagerDatabaseProvider: Executing command line
[2019-03-18 23:27:16.155 +0500] INFO  [io-thread-3] - - - - - ssh:10.142.0.59: JAVA_HOME=/usr/java/jdk1.8.0_181-cloudera
[2019-03-18 23:27:17.286 +0500] INFO  [io-thread-3] - - - - - ssh:10.142.0.59: [                          main] DbProvisioner                  ERROR Exception when creating/dropping database with user 'scm' and jdbc url 'jdbc:mysql://dbmysql01.us-east1-b.c.cloudera-232012.internal:3306/?useUnicode=true&characterEncoding=UTF-8'
[2019-03-18 23:27:17.286 +0500] INFO  [io-thread-3] - - - - - ssh:10.142.0.59: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Access denied for user 'scm'@'%' to database 'scm_uacfr6ubohasnigu87vr0g1eq1'

Further i didnot bind the local/public ip address in mysql, so by default it is running on 127.0.0.1:3306 where the blue line suggest this is trying to connect on local ip. 

should i need to bind mysql on local ip address?

where i am using HeidiSQL to connect remotely vis SSH tunne working fine.

Thanks,

Rana

[#6 · Accepted solution]

You appear to be using the username "scm" as an administrative user on the MySQL instance. The "scm" user must have permission to create and delete databases on the server. Normally, one would use the default MySQL "root" user, which already should have all of the necessary permissions.

- The string starting with "scm_" and ending with a random string is the generated name of the Cloudera Manager server database.

- The string "uxnlmrno" is the username for the user that shall be used to access the new database. Apparently, in your configuration file, you do not specify a usernamePrefix for the database. It is optional.

Director is running a script on the CM instance to perform the database work. (The script uses CM code, so it needs to run where CM is installed). It is trying to reach the database server at the full hostname ending in ".internal", and it seems that the connectivity there is working. You can double-check by running a MySQL client from the CM instance itself.

You say you can create and drop databases from the MySQL console. Does that use the "scm" user or, perhaps, the "root" user?