com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException

[kyleburke]

Not sure what's causing the error below. I'm reading some data from cassandra and trying to write it to memSQL. Any ideas how to resolve the issue?

Here is my code:

import com.memsql.spark.connector.MemSQLContext

val memsqlContext = new MemSQLContext(sc)
val df = sqlContext.read.format("org.apache.spark.sql.cassandra").options(Map( "table" -> "customer", "keyspace" -> "cloudscm" )).load()
df.registerTempTable("customers")
val memdf = sqlContext.sql("SELECT id, site, customergroup, name from customers")
memdf.write.format("com.memsql.spark.connector").save("test.customer")

I created the table in memsql using the following command:

create table customer(id varchar(256), site varchar(256), customergroup varchar(256), name varchar(256));

Here is the error I get:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '())' at line 1
    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:422)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:377)
    at com.mysql.jdbc.Util.getInstance(Util.java:360)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:978)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2526)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:848)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:742)
    at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
    at org.apache.commons.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:291)
    at com.memsql.spark.connector.MemSQLCluster$$anonfun$createTable$1$$anonfun$apply$8.apply(MemSQLCluster.scala:141)
    at com.memsql.spark.connector.MemSQLCluster$$anonfun$createTable$1$$anonfun$apply$8.apply(MemSQLCluster.scala:140)
    at com.memsql.spark.connector.util.Loan.to(Loan.scala:6)
    at com.memsql.spark.connector.util.JDBCImplicits$ConnectionHelpers.withStatement(JDBCImplicits.scala:51)
    at com.memsql.spark.connector.MemSQLCluster$$anonfun$createTable$1.apply(MemSQLCluster.scala:140)
    at com.memsql.spark.connector.MemSQLCluster$$anonfun$createTable$1.apply(MemSQLCluster.scala:139)
    at com.memsql.spark.connector.util.Loan.to(Loan.scala:6)
    at com.memsql.spark.connector.MemSQLConnectionPool$.withConnection(MemSQLConnectionPool.scala:38)
    at com.memsql.spark.connector.MemSQLCluster$$anonfun$withMasterConn$1.apply(MemSQLCluster.scala:23)
    at com.memsql.spark.connector.MemSQLCluster$$anonfun$withMasterConn$1.apply(MemSQLCluster.scala:23)
    at com.memsql.spark.connector.MemSQLCluster.createTable(MemSQLCluster.scala:139)
    at org.apache.spark.sql.memsql.SparkImplicits$DataFrameFunctions.saveToMemSQL(SparkImplicits.scala:70)
    at org.apache.spark.sql.memsql.MemSQLTableRelation.insert(MemSQLRelation.scala:45)
    at org.apache.spark.sql.memsql.DefaultSource.createRelation(DefaultSource.scala:33)
    at org.apache.spark.sql.execution.datasources.ResolvedDataSource$.apply(ResolvedDataSource.scala:222)
    at org.apache.spark.sql.DataFrameWriter.save(DataFrameWriter.scala:148)
    at org.apache.spark.sql.DataFrameWriter.save(DataFrameWriter.scala:139)
    at $iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC.<init>(<console>:29)
    at $iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC.<init>(<console>:34)
    at $iwC$$iwC$$iwC$$iwC$$iwC$$iwC.<init>(<console>:36)
    at $iwC$$iwC$$iwC$$iwC$$iwC.<init>(<console>:38)
    at $iwC$$iwC$$iwC$$iwC.<init>(<console>:40)
    at $iwC$$iwC$$iwC.<init>(<console>:42)
    at $iwC$$iwC.<init>(<console>:44)
    at $iwC.<init>(<console>:46)
    at <init>(<console>:48)
    at .<init>(<console>:52)
    at .<clinit>(<console>)
    at .<init>(<console>:7)
    at .<clinit>(<console>)
    at $print(<console>)
    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:497)
    at org.apache.spark.repl.SparkIMain$ReadEvalPrint.call(SparkIMain.scala:1065)
    at org.apache.spark.repl.SparkIMain$Request.loadAndRun(SparkIMain.scala:1346)
    at org.apache.spark.repl.SparkIMain.loadAndRunReq$1(SparkIMain.scala:840)
    at org.apache.spark.repl.SparkIMain.interpret(SparkIMain.scala:871)
    at org.apache.spark.repl.SparkIMain.interpret(SparkIMain.scala:819)
    at org.apache.spark.repl.SparkILoop.reallyInterpret$1(SparkILoop.scala:857)
    at org.apache.spark.repl.SparkILoop.interpretStartingWith(SparkILoop.scala:902)
    at org.apache.spark.repl.SparkILoop.command(SparkILoop.scala:814)
    at org.apache.spark.repl.SparkILoop.processLine$1(SparkILoop.scala:657)
    at org.apache.spark.repl.SparkILoop.innerLoop$1(SparkILoop.scala:665)
    at org.apache.spark.repl.SparkILoop.org$apache$spark$repl$SparkILoop$$loop(SparkILoop.scala:670)
    at org.apache.spark.repl.SparkILoop$$anonfun$org$apache$spark$repl$SparkILoop$$process$1.apply$mcZ$sp(SparkILoop.scala:997)
    at org.apache.spark.repl.SparkILoop$$anonfun$org$apache$spark$repl$SparkILoop$$process$1.apply(SparkILoop.scala:945)
    at org.apache.spark.repl.SparkILoop$$anonfun$org$apache$spark$repl$SparkILoop$$process$1.apply(SparkILoop.scala:945)
    at scala.tools.nsc.util.ScalaClassLoader$.savingContextLoader(ScalaClassLoader.scala:135)
    at org.apache.spark.repl.SparkILoop.org$apache$spark$repl$SparkILoop$$process(SparkILoop.scala:945)
    at org.apache.spark.repl.SparkILoop.process(SparkILoop.scala:1059)
    at org.apache.spark.repl.Main$.main(Main.scala:31)
    at org.apache.spark.repl.Main.main(Main.scala)
    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:497)
    at org.apache.spark.deploy.SparkSubmit$.org$apache$spark$deploy$SparkSubmit$$runMain(SparkSubmit.scala:731)
    at org.apache.spark.deploy.SparkSubmit$.doRunMain$1(SparkSubmit.scala:181)
    at org.apache.spark.deploy.SparkSubmit$.submit(SparkSubmit.scala:206)
    at org.apache.spark.deploy.SparkSubmit$.main(SparkSubmit.scala:121)
    at org.apache.spark.deploy.SparkSubmit.main(SparkSubmit.scala)

[choochootrain]

what version of the memsql-spark-connector are you using?

inside the stack i see

    at com.memsql.spark.connector.MemSQLCluster$$anonfun$createTable$1$$anonfun$apply$8.apply(MemSQLCluster.scala:141)

which indicates that the MemSQLContext is trying to create the customers table before writing and that is what is failing.

Not sure why the create table call is failing here, could you gather some additional information?

• The dataframe schema: memDf.schema - there may be a bug in our create table code which is incorrectly building a CREATE TABLE statement from your dataframe. This is likely because the dataframe originated from Cassandra, which may use different intermediary types in the materialized dataframe.
• The logs from when CREATE TABLE failed in MemSQL - The default location for this will be in /var/lib/memsql/master-3306/tracelogs and there should be an entry in memsql.log for the failing query.

And in the interest of minimizing roundtrips could you also verify that your MemSQLContext is set to the correct database and the customers table exists before writing the dataframe? From your description it looks like you intend for the table to exist before writing so something else may be misconfigured.

When the MemSQLContext is writing into an existing table do you still see it fail?

[lucyyu]

Closed due to inactivity