Unable to connect to mysql using spark

#1

Hi ,
i am unable to connect o mysql from spark , it was working previously .
SparkSession available as ‘spark’.

ordersrdd = spark.read.jdbc(url=“jdbc:mysql://ms.itversity.com/retail_db”,table=“orders”,properties={“user”:“retail_user”,“password”:“itversity”})
Traceback (most recent call last):
File “”, line 1, in
File “/usr/hdp/current/spark2-client/python/pyspark/sql/readwriter.py”, line 527, in jdbc
return self._df(self._jreader.jdbc(url, table, jprop))
File “/usr/hdp/current/spark2-client/python/lib/py4j-0.10.6-src.zip/py4j/java_gateway.py”, line 1160, in call
File “/usr/hdp/current/spark2-client/python/pyspark/sql/utils.py”, line 63, in deco
return f(*a, **kw)
File “/usr/hdp/current/spark2-client/python/lib/py4j-0.10.6-src.zip/py4j/protocol.py”, line 320, in get_return_value
py4j.protocol.Py4JJavaError: An error occurred while calling o48.jdbc.
: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)


Learn Spark 1.6.x or Spark 2.x on our state of the art big data labs

  • Click here for access to state of the art 13 node Hadoop and Spark Cluster

0 Likes

#2

I have the same problem. unable to connect to mysql using spark.

pyspark --master yarn --conf spark.ui.port=19010 --jars /usr/share/java/mysql-connector-java.jar --driver-class-path /usr/share/java/mysql-connector-java.jar

orderitems=spark.read.format("jdbc").\
  option("url","jdbc:mysql://ms.itversity.com").\
  option("user","retail_user").\
  option("password","itversity").\
  option("dbtable","retail_db.order_items").\
  load()

Traceback (most recent call last):
File “”, line 5, in
File “/usr/hdp/current/spark2-client/python/pyspark/sql/readwriter.py”, line 172, in load
return self._df(self._jreader.load())
File “/usr/hdp/current/spark2-client/python/lib/py4j-0.10.6-src.zip/py4j/java_gateway.py”, line 1160, in call
File “/usr/hdp/current/spark2-client/python/pyspark/sql/utils.py”, line 63, in deco
return f(*a, **kw)
File “/usr/hdp/current/spark2-client/python/lib/py4j-0.10.6-src.zip/py4j/protocol.py”, line 320, in get_return_value
py4j.protocol.Py4JJavaError: An error occurred while calling o103.load.
: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
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.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920)
at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2570)
at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2306)
at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:839)
at com.mysql.jdbc.JDBC4Connection.(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.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:63)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$$anonfun$createConnectionFactory$1.apply(JdbcUtils.scala:54)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCRDD$.resolveTable(JDBCRDD.scala:56)
at org.apache.spark.sql.execution.datasources.jdbc.JDBCRelation.(JDBCRelation.scala:115)
at org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:52)
at org.apache.spark.sql.execution.datasources.DataSource.resolveRelation(DataSource.scala:340)
at org.apache.spark.sql.DataFrameReader.loadV1Source(DataFrameReader.scala:239)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:227)
at org.apache.spark.sql.DataFrameReader.load(DataFrameReader.scala:164)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at py4j.reflection.MethodInvoker.invoke(MethodInvoker.java:244)
at py4j.reflection.ReflectionEngine.invoke(ReflectionEngine.java:357)
at py4j.Gateway.invoke(Gateway.java:282)
at py4j.commands.AbstractCommand.invokeMethod(AbstractCommand.java:132)
at py4j.commands.CallCommand.execute(CallCommand.java:79)
at py4j.GatewayConnection.run(GatewayConnection.java:214)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.NullPointerException
at com.mysql.jdbc.ConnectionImpl.getServerCharacterEncoding(ConnectionImpl.java:3281)
at com.mysql.jdbc.MysqlIO.sendConnectionAttributes(MysqlIO.java:1940)
at com.mysql.jdbc.MysqlIO.proceedHandshakeWithPluggableAuthentication(MysqlIO.java:1866)
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)
… 30 more

0 Likes

#3

@Swetha_Kondapalli @prakashmahali . - Can you please try now?

If you are facing the issue, please paste all steps that you are running.

0 Likes

#4

If there is any issue, use the below command to launch pyspark shell:

pyspark --master yarn --conf spark.ui.port=12345 --jars /usr/share/java/mysql-connector-java-new.jar --driver-class-path /usr/share/java/mysql-connector-java-new.jar

0 Likes

#5

facing the same error :
Step 1: pyspark --driver-class-path /usr/share/java/mysql-connector-java.jar
step2 :ordersrdd = spark.read.jdbc(url=“jdbc:mysql://ms.itversity.com/retail_db”,table=“orders”,properties={“user”:“retail_user”,“password”:“itversity”})

Error :

ordersrdd = spark.read.jdbc(url=“jdbc:mysql://ms.itversity.com/retail_db”,table=“orders”,properties={“user”:“retail_user”,“password”:“itversity”})
Traceback (most recent call last):
File “”, line 1, in
File “/usr/hdp/current/spark2-client/python/pyspark/sql/readwriter.py”, line 527, in jdbc
return self._df(self._jreader.jdbc(url, table, jprop))
File “/usr/hdp/current/spark2-client/python/lib/py4j-0.10.6-src.zip/py4j/java_gateway.py”, line 1160, in call
File “/usr/hdp/current/spark2-client/python/pyspark/sql/utils.py”, line 63, in deco
return f(*a, **kw)
File “/usr/hdp/current/spark2-client/python/lib/py4j-0.10.6-src.zip/py4j/protocol.py”, line 320, in get_return_value
py4j.protocol.Py4JJavaError: An error occurred while calling o140.jdbc.
: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server.
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)

0 Likes

#6

@prakashmahali

I think you are getting this error in your private system.
I have tried below commands in the lab its working fine.

pyspark --master yarn --conf spark.ui.port=12345 --jars /usr/share/java/mysql-connector-java.jar --driver-class-path /usr/share/java/mysql-connector-java.jar

orderDF=sqlContext.read.jdbc(“jdbc:mysql://ms.itversity.com”, “retail_db.orders”, properties={“user”: “retail_user”, “password”: “itversity”})

0 Likes

#7

Hi ,
I have tried in lab only . gw02.itversity.com.
it is not working with spark 1.6 and spark 2.3

0 Likes

#8

Syntax in your code is incorrect. You have to fix the syntax and run it again. Error is obvious from the logs.

0 Likes

#9

Your code have syntax errors. You need to use comma between property key and value. Errors are quite obvious, you need to look into errors thrown.

Also you can get help on function and it will give examples.

0 Likes

#10

It is due to recent upgrade of the source database.

Please use this path to connect to source database over JDBC.

pyspark2 --master yarn --conf spark.ui.port=19010 --jars /usr/hdp/2.6.5.0-292/sqoop/lib/mysql-connector-java-8.0.16.jar --driver-class-path /usr/hdp/2.6.5.0-292/sqoop/lib/mysql-connector-java-8.0.16.jar

0 Likes

#12

I have corrected the syntax error in 2nd line, if you mark my screen shot and after that also i am getting error . can you please post your screen shot for successful try.

0 Likes

#13

Thanks Swetha . It is working with your command .

0 Likes

closed #14
0 Likes