Oozie database access problem.

[#1]

After install HDP2.6.3 and Ambari2.6.2.2, In oozie-error.log, I found some strange information about database connection problem.

-----------------

2018-07-19 09:29:17,244 ERROR SchemaCheckXCommand:517 - SERVER[hadoopS4] USER[-] GROUP[-] TOKEN[-] APP[-] JOB[-] ACTION[-] An Exception occured while talking to the database: Access denied for user 'root'@'hadoopS4' (using password: YES) java.sql.SQLException: Access denied for user 'root'@'hadoopS4' (using password: YES) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:959) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3870) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3806) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:871) at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1686) at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1207) at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2254) at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2285) at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2084) at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:795) at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:44) at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at com.mysql.jdbc.Util.handleNewInstance(Util.java:404) at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:400) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:327) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:247) at org.apache.oozie.command.SchemaCheckXCommand.execute(SchemaCheckXCommand.java:88) at org.apache.oozie.command.SchemaCheckXCommand.execute(SchemaCheckXCommand.java:63) at org.apache.oozie.command.XCommand.call(XCommand.java:287) at java.util.concurrent.FutureTask.run(FutureTask.java:266) at org.apache.oozie.service.CallableQueueService$CallableWrapper.run(CallableQueueService.java:178) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) at java.lang.Thread.run(Thread.java:745)

-----------------

I am sure below things are fine:

1. jdbc-mysql.jar has been added.

2. use command could connect oozie database from oozie server. "mysql -uroot -pxxxx -hhadoops4".

3. Install oozie from ambari. the db connection is fine.

4. All of oozie tables were created automatically during oozie installation.

[#2]

Using ambari service check, everything is ok. but when add smtp mail server, it will throw some error information.

[#3]

@wei mou

Seems like the privilege issue at the MySQL end. Please try running the below command:

grant all privileges on *.* to 'root'@'hadoopS4' using password 'xxx';
flush privileges;

[#4]

Thanks for your information. Yes I totally agree with what your said.

And I have executed the command before installation.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '***' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY '***' WITH GRANT OPTION; flush privileges;

The problem still exits. But the function is ok.

[#5]

@wei mou

If you create the MySQL database you should have run a command similar to this:
Assumption

• root pwassword is welcome1
  
• Oozie user is oozie
• MySQL databases host is MySQL_host

mysql -u root -pwelcome1 
create database oozie; 
create user oozie identified by 'oozie'; 
grant all on oozie.* to oozie; 
GRANT ALL PRIVILEGES ON *.* TO 'oozie'@'localhost' IDENTIFIED BY 'oozie'; 
GRANT ALL PRIVILEGES ON *.* TO 'oozie'@'<MySQL_host>'IDENTIFIED BY 'oozie' with admin option; 
flush privileges; 
quit; 

To test that the oozie user can connect successfully

mysql -u oozie -poozie 
show databases; 
quit;

It seems your problem is the privileges and the oozie table belong to root not the oozie user

[#6]

Thanks for your replay.

I used root account to connect oozie db. this account has fully privileges to execute everything in db.

The strange thing is oozie works fine without any problem.

[#7]

Thanks for your rely.

Actually, using root account to connect the oozie database with full privileges. I have try to connect the database by command line. Everything is ok~

And check the DB configuration to verify whether the tables are generated during the installation. Everything is fine too.

Checking the service of oozie and submit the job from hue. Thing is ok too~

I don't know why this error is occured in the log file.

[#8]

@wei mou

YES, of course, you need to connect like root but what was missing was the privileges for the oozie user to the oozie database ! Which now has been solved can you explain or share how you are adding smtp mail server.?

[#9]

Same problem but more complex. Can you help me ?

• oozie DB is created
• user/pass and privileges are set OK
• Connection test is OK

• I can connect through command line from the same server emulating JDBC connector with sqlline:

# java -Djava.ext.dirs=/home/user/jline_sqlline__mysql_connector/ sqlline.SqlLine
sqlline version 1.0.2 by Marc Prud'hommeaux
sqlline> !connect jdbc:mysql://pro-hadoop-ambari/oozie oozie XXXXXXX
Connecting to jdbc:mysql://pro-hadoop-ambari/oozie
Connected to: MySQL (version 5.7.23)
Driver: MySQL-AB JDBC Driver (version mysql-connector-java-5.1.17-SNAPSHOT ( Revision: ${bzr.revision-id} ))
Autocommit status: true
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:mysql://pro-hadoop-ambari/oozie> 

• But ... the service doesn't start due to a JDBC error 😞

Validate DB Connection
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/hdp/2.6.5.0-292/oozie/libserver/slf4j-log4j12-1.6.6.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hdp/2.6.5.0-292/oozie/lib/slf4j-simple-1.6.6.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
DONE
DB schema does not exist
Check OOZIE_SYS table does not exist
DONE
Create SQL schema


Error: A connection could not be obtained for driver class "com.mysql.jdbc.Driver" and URL "jdbc:mysql://pro-hadoop-ambari/oozie".  You may have specified an invalid URL.


Stack trace for the error was (for debug purposes):
--------------------------------------
<openjpa-2.4.1-r422266:1730418 fatal user error> org.apache.openjpa.util.UserException: A connection could not be obtained for driver class "com.mysql.jdbc.Driver" and URL "jdbc:mysql://pro-hadoop-ambari/oozie".  You may have specified an invalid URL.
 at org.apache.openjpa.jdbc.schema.DataSourceFactory.newConnectException(DataSourceFactory.java:272)
 at org.apache.openjpa.jdbc.schema.DataSourceFactory.installDBDictionary(DataSourceFactory.java:258)
 at org.apache.openjpa.jdbc.conf.JDBCConfigurationImpl.getConnectionFactory(JDBCConfigurationImpl.java:733)
 at org.apache.openjpa.jdbc.conf.JDBCConfigurationImpl.getDataSource(JDBCConfigurationImpl.java:878)
 at org.apache.openjpa.jdbc.conf.JDBCConfigurationImpl.getDataSource2(JDBCConfigurationImpl.java:920)
 at org.apache.openjpa.jdbc.schema.SchemaTool.<init>(SchemaTool.java:132)
 at org.apache.openjpa.jdbc.meta.MappingTool.newSchemaTool(MappingTool.java:314)
 at org.apache.openjpa.jdbc.meta.MappingTool.record(MappingTool.java:495)
 at org.apache.openjpa.jdbc.meta.MappingTool.run(MappingTool.java:1095)
 at org.apache.openjpa.jdbc.meta.MappingTool.run(MappingTool.java:1006)
 at org.apache.openjpa.jdbc.meta.MappingTool$1.run(MappingTool.java:939)
 at org.apache.openjpa.lib.conf.Configurations.launchRunnable(Configurations.java:762)
 at org.apache.openjpa.lib.conf.Configurations.runAgainstAllAnchors(Configurations.java:752)
 at org.apache.openjpa.jdbc.meta.MappingTool.main(MappingTool.java:934)
 at org.apache.oozie.tools.OozieDBCLI.createUpgradeDB(OozieDBCLI.java:1191)
 at org.apache.oozie.tools.OozieDBCLI.createDB(OozieDBCLI.java:198)
 at org.apache.oozie.tools.OozieDBCLI.run(OozieDBCLI.java:131)
 at org.apache.oozie.tools.OozieDBCLI.main(OozieDBCLI.java:79)
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Access denied for user 'oozie'@'pro-hadoop-ambari' (using password: YES))
 at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
 at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
 at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
 at org.apache.openjpa.jdbc.schema.DBCPDriverDataSource.getDBCPConnection(DBCPDriverDataSource.java:74)
 at org.apache.openjpa.jdbc.schema.AutoDriverDataSource.getConnection(AutoDriverDataSource.java:42)
 at org.apache.openjpa.jdbc.schema.SimpleDriverDataSource.getConnection(SimpleDriverDataSource.java:76)
 at org.apache.openjpa.lib.jdbc.DelegatingDataSource.getConnection(DelegatingDataSource.java:118)
 at org.apache.openjpa.lib.jdbc.DecoratingDataSource.getConnection(DecoratingDataSource.java:92)
 at org.apache.openjpa.jdbc.schema.DataSourceFactory.installDBDictionary(DataSourceFactory.java:250)
 ... 16 more
Caused by: java.sql.SQLException: Access denied for user 'oozie'@'pro-hadoop-ambari' (using password: YES)
 at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4187)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4119)
 at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:927)
 at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1709)
 at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1252)
 at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2488)
 at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2521)
 at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2306)
 at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:839)
 at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
 at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
 at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
 at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
 at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
 at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:421)
 at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:350)
 at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
 at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
 at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
 at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
 ... 24 more
--------------------------------------