- Subscribe to RSS Feed
- Mark Question as New
- Mark Question as Read
- Float this Question for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
Oozie database access problem.
Created ‎07-19-2018 01:47 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Created ‎07-19-2018 01:55 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Using ambari service check, everything is ok. but when add smtp mail server, it will throw some error information.
Created ‎07-19-2018 07:39 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
Created ‎07-19-2018 09:59 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Created ‎07-19-2018 07:46 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Created ‎07-19-2018 10:01 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Created ‎07-19-2018 09:56 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Created ‎07-19-2018 10:40 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.?
Created on ‎10-08-2018 03:38 PM - edited ‎08-17-2019 11:54 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 --------------------------------------
