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

Accessing Mysql DB on my Mac via Sqoop

Explorer

Hi,

I am trying to perform the following task: 'Import data from a table in a relational database into HDFS'.

To get a working example of this - I have created a mySQL database (hostname=127.0.0.1 and port=xxxx) on my Mac.

I have then logged in to ssh (http://127.0.0.1:4200/) root within a terminal and type:

sqoop list-databases --connect jdbc:mysql://127.0.0.1:xxxx --username root --password abcabcabc

Instead of a list of databases, I get the below output. Can anyone advise?

Warning: /usr/hdp/2.3.2.0-2950/accumulo does not exist! Accumulo imports will fail.        
Please set $ACCUMULO_HOME to the root of your Accumulo installation.                      
16/01/11 18:44:42 INFO sqoop.Sqoop: Running Sqoop version: 1.4.6.2.3.2.0-2950              
16/01/11 18:44:42 WARN tool.BaseSqoopTool: Setting your password on the command-line is ins
ecure. Consider using -P instead.                                                          
16/01/11 18:44:43 INFO manager.MySQLManager: Preparing to use a MySQL streaming resultset. 
SLF4J: Class path contains multiple SLF4J bindings.                                        
SLF4J: Found binding in [jar:file:/usr/hdp/2.3.2.0-2950/hadoop/lib/slf4j-log4j12-1.7.10.jar
!/org/slf4j/impl/StaticLoggerBinder.class]                                                
SLF4J: Found binding in [jar:file:/usr/hdp/2.3.2.0-2950/zookeeper/lib/slf4j-log4j12-1.6.1.j
ar!/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]                      
16/01/11 18:44:43 ERROR manager.CatalogQueryManager: Failed to list databases              
java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)    
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)                  
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)                      
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)                      
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:935)                      
        at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:4101)                        
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1300)                          
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2337)            
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2370)      
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2154)            
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)                  
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)                  
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)          
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorI
mpl.java:57)                                                                              
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorA
ccessorImpl.java:45)                                                                      
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)                
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)                            
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:381)              
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)      
        at java.sql.DriverManager.getConnection(DriverManager.java:571)                    
        at java.sql.DriverManager.getConnection(DriverManager.java:215)                    
        at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:885)        
        at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.jav
a:52)                                                                                      
        at org.apache.sqoop.manager.CatalogQueryManager.listDatabases(CatalogQueryManager.j
ava:57)                                                                                    
        at org.apache.sqoop.tool.ListDatabasesTool.run(ListDatabasesTool.java:49)          
        at org.apache.sqoop.Sqoop.run(Sqoop.java:148)                                      
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)                      
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184)                                
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226)                                  
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235)                                  
        at org.apache.sqoop.Sqoop.main(Sqoop.java:244)                                    
16/01/11 18:44:43 ERROR sqoop.Sqoop: Got exception running Sqoop: java.lang.RuntimeExceptio
n: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)  
java.lang.RuntimeException: java.sql.SQLException: Access denied for user 'root'@'localhost
' (using password: YES)                                                                    
        at org.apache.sqoop.manager.CatalogQueryManager.listDatabases(CatalogQueryManager.j
ava:73)                                                                                    
        at org.apache.sqoop.tool.ListDatabasesTool.run(ListDatabasesTool.java:49)          
        at org.apache.sqoop.Sqoop.run(Sqoop.java:148)                                      
        at org.apache.hadoop.util.ToolRunner.run(ToolRunner.java:70)                      
        at org.apache.sqoop.Sqoop.runSqoop(Sqoop.java:184)                                
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:226)                                  
        at org.apache.sqoop.Sqoop.runTool(Sqoop.java:235)                                  
        at org.apache.sqoop.Sqoop.main(Sqoop.java:244)                                    
Caused by: java.sql.SQLException: Access denied for user 'root'@'localhost' (using password
: YES)                                                                                    
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1073)                  
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3597)                      
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3529)                      
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:935)                      
        at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:4101)                        
        at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1300)                          
        at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2337)            
        at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2370)      
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2154)            
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:792)                  
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)                  
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)          
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorI
mpl.java:57)                                                                              
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorA
ccessorImpl.java:45)                                                                      
        at java.lang.reflect.Constructor.newInstance(Constructor.java:526)                
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)                            
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:381)              
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:305)      
        at java.sql.DriverManager.getConnection(DriverManager.java:571)                    
        at java.sql.DriverManager.getConnection(DriverManager.java:215)                    
        at org.apache.sqoop.manager.SqlManager.makeConnection(SqlManager.java:885)        
        at org.apache.sqoop.manager.GenericJdbcManager.getConnection(GenericJdbcManager.jav
a:52)                                                                                      
        at org.apache.sqoop.manager.CatalogQueryManager.listDatabases(CatalogQueryManager.j
ava:57)                                                                                    
        ... 7 more                            
1 ACCEPTED SOLUTION

@Revlin Abbi

try this : grant all privileges on *.* to 'root'@'localhost' identified by 'abcabcabc' with grant option;

sqoop list-databases --connect jdbc:mysql://127.0.0.1:xxxx --username root --password abcabcabc

root user does not have read/list permissions.

ERROR manager.CatalogQueryManager: Failed to list databases java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

View solution in original post

11 REPLIES 11

Mentor
@Revlin Abbi

you're confusing your local filesystem with Sandbox filesystem. You can create a mysql database in Sandbox as it already comes with mysql server, you can create a db in postgres as well, Ambari db is one such database. Unless you specify host-guest networking so that guest can access the host's services, you won't be able to achieve what you're doing. It's a lot simpler to work with mysql that is inside Sandbox guest machine.

Explorer

Hi Artem, sure I understand.

If I take the example database xademo (which I can see when viewing from Hive in Ambari) - how can I use sqoop to list this database?

Mentor

you typically wouldn't use sqoop to query Hive table. Login to mysql and list databases there. Once you know which db you'd like to import then you can use the command you'd used before. For exporting hive data, you'd use hive commands, pig scripts, hdfs commands, etc.

Expert Contributor

@Artem Ervits: I have the exact same problem. MySQL is hosted on an altogether different system which is not a cluster in my hadoop system but only hosts the mySQL database. I have user root@'IP' granted with all permission and I can connect through the command mysql -u -p -h but it is not throws the mentioned error whenever I try to connect via sqoop.What else could possibly be the problem?How do I fix this?

Mentor

@simran kaur check virtualbox networking configuration, you may need to port forward Mysql port in Virtualbox network settings.

New Contributor

I have to same environment set up and same issue. Within Virtual box using the bridged adapter (so no port forward set up there) Using my network router which has 3306 forwarded the my server with mysql instance. This all seems to be working since we can connect to the database using the command mysql -u -p -h. Any other ideas on why sqoop fails and throws the access denied? Maybe driver or config related? I also have granted root access from any host '%' on mysql so don't believe that is the issue, esp since connection with command line works.

@Revlin Abbi

try this : grant all privileges on *.* to 'root'@'localhost' identified by 'abcabcabc' with grant option;

sqoop list-databases --connect jdbc:mysql://127.0.0.1:xxxx --username root --password abcabcabc

root user does not have read/list permissions.

ERROR manager.CatalogQueryManager: Failed to list databases java.sql.SQLException: Access denied for user 'root'@'localhost' (using password: YES)

Expert Contributor
I have the exact same problem. MySQL is hosted on an altogether different system which is not a cluster in my hadoop system but only hosts the mySQL database. I have user root@'IP' granted with all permission and I can connect through the command mysql -u -p -h  but it is not throws the mentioned error whenever I try to connect via sqoop.What else could possibly be the problem?How do I fix this?

@neeraj

Mentor

here's a scenario that works on Sandbox

# Create Hive table

drop table if exists export_table;

create table export_table (

key int,

value string

)

row format delimited

fields terminated by ",";

# populate Hive with dummy data

insert into export_table values("1", "ExportedValue");

# confirm Hive table has data

select * from export_table;

# display the values in hive as hdfs files

hdfs dfs -cat /apps/hive/warehouse/export_table/000000_0

# export table to MySQL

# MySQL table must exist

su mysql

mysql -u root

create database export;

use export;

create table exported (rowkey int, value varchar(20));

exit;

# on HDP 2.3.2 Sandbox, SQOOP-1400 bug, use --driver com.mysql.jdbc.Driver to overcome the problem

# sqoop export from a Hive table into MySQL

sqoop export --connect jdbc:mysql://127.0.0.1/export --username hive --password hive --table exported --direct --export-dir /apps/hive/warehouse/export_table --driver com.mysql.jdbc.Driver

# login to Mysql and check the table

su mysql

mysql -u root

use export;

select * from exported;

exit;

Mentor

@Revlin Abbi has this been resolved? Can you post your solution or accept best answer?

New Contributor

We had the same issue, the MySQL was hosted in Google CloudSQL, the issue was that there was some syntax error and sqoop kept complaining about permissions. Once we fixed the syntax it started working, the command is : sqoop list-databases --connect jdbc:mysql://{google cloudsql ipaddress} --username {username} --password {pwd} . Another way to isolate if this is a Sqoop issue or db connectivity issue is to try and import the table instead of listing the databases, also not listing databases needs additional permission.

; ;