Re: Problem using MySQL with JDBC
It looks as if com.mysql.cj.jdbc.Driver is not on your class path. If you are launching via SQLLine, you will need to edit the sqlline shell script to add a jar (or jars) to your class path. Julian > On Oct 6, 2021, at 10:43 AM, Justin Swanhart wrote: > > I am probably making some obvious mistake, but I am having a problem > getting a simple MySQL JDBC connection working. > > I have the latest version of the Connector/J MySQL java client driver. I > have a MySQL 8 server running on the local machine, and the following model > JSON: > { > version: '1.0', > defaultSchema: 'ssb', > schemas: [ >{ > name: 'ssb', > type: 'custom', > factory: 'org.apache.calcite.adapter.jdbc.JdbcSchema$Factory', > operand: { >jdbcDriver: 'com.mysql.cj.jdbc.Driver', >jdbcUrl: 'jdbc:mysql://localhost/ssb', >jdbcUser: 'root', >jdbcPassword: '' > } >} > ] > } > > I have the MySQL driver in my CLASSPATH. I can compile a simple test > program which verifies that the class com.mysql.jdbc.Driver exists. > > While the test program works, sqlline does not: > $ ./sqlline -d com.mysql.cj.jdbc.Driver -u jdbc:mysql://root@localhost/ssb > Building Apache Calcite 1.28.0-SNAPSHOT > scan complete in 1ms > Could not find driver com.mysql.cj.jdbc.Driver > > Any suggestions about what I might be doing wrong? > > Using the !connect command yields a large backtrace (while probably not > useful, included for completeness): > sqlline version 1.11.0 > sqlline> !connect jdbc:calcite:model=test.json admin admin > WARNING: An illegal reflective access operation has occurred > WARNING: Illegal reflective access by com.google.protobuf.UnsafeUtil > (file:/home/justin/.gradle/caches/modules-2/files-2.1/com.google.protobuf/protobuf-java/3.6.1/d06d46ecfd92ec6d0f3b423b4cd81cb38d8b924/protobuf-java-3.6.1.jar) > to field java.nio.Buffer.address > WARNING: Please consider reporting this to the maintainers of > com.google.protobuf.UnsafeUtil > WARNING: Use --illegal-access=warn to enable warnings of further illegal > reflective access operations > WARNING: All illegal access operations will be denied in a future release > java.lang.RuntimeException: Error instantiating JsonCustomSchema(name=ssb) > at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:277) > at > org.apache.calcite.model.JsonCustomSchema.accept(JsonCustomSchema.java:66) > at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:200) > at org.apache.calcite.model.ModelHandler.(ModelHandler.java:106) > at org.apache.calcite.jdbc.Driver$1.onConnectionInit(Driver.java:101) > at > org.apache.calcite.avatica.UnregisteredDriver.connect(UnregisteredDriver.java:139) > at sqlline.DatabaseConnection.connect(DatabaseConnection.java:135) > at sqlline.DatabaseConnection.getConnection(DatabaseConnection.java:192) > at sqlline.Commands.connect(Commands.java:1481) > at sqlline.Commands.connect(Commands.java:1355) > at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native > Method) > at > java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) > at > java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.base/java.lang.reflect.Method.invoke(Method.java:566) > at > sqlline.ReflectiveCommandHandler.execute(ReflectiveCommandHandler.java:44) > at sqlline.SqlLine.dispatch(SqlLine.java:818) > at sqlline.SqlLine.begin(SqlLine.java:596) > at sqlline.SqlLine.start(SqlLine.java:269) > at sqlline.SqlLine.main(SqlLine.java:208) > Caused by: com.google.common.util.concurrent.UncheckedExecutionException: > java.lang.RuntimeException: java.sql.SQLException: Cannot load JDBC driver > class 'com.mysql.cj.jdbc.Driver' > at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2051) > at com.google.common.cache.LocalCache.get(LocalCache.java:3951) > at com.google.common.cache.LocalCache.getOrLoad(LocalCache.java:3974) > at > com.google.common.cache.LocalCache$LocalLoadingCache.get(LocalCache.java:4958) > at > com.google.common.cache.LocalCache$LocalLoadingCache.getUnchecked(LocalCache.java:4964) > at > org.apache.calcite.adapter.jdbc.JdbcUtils$DialectPool.get(JdbcUtils.java:116) > at > org.apache.calcite.adapter.jdbc.JdbcSchema.createDialect(JdbcSchema.java:200) > at org.apache.calcite.adapter.jdbc.JdbcSchema.create(JdbcSchema.java:136) > at org.apache.calcite.adapter.jdbc.JdbcSchema.create(JdbcSchema.java:123) > at org.apache.calcite.adapter.jdbc.JdbcSchema.create(JdbcSchema.java:175) > at > org.apache.calcite.adapter.jdbc.JdbcSchema$Factory.create(JdbcSchema.java:570) > at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:272) > ... 18 more > Caused by: java.lang.RuntimeException: java.sql.SQLException: Cannot load > JDBC driver class 'com.mysql.cj.jdbc.Driver' > at > org.apache.calcite.adapter.jdbc.JdbcUtils$DialectPool.dialect(JdbcUtils.java:101) > at > com.google.common.cache.CacheLoader$FunctionTo
Re: Problem using MySQL with JDBC
Hi, The jar is in the classpath, and I can run the java command that the sqlline script runs directly, and I get the same error. The following works in a test java program with the CLASSPATH set: conn = DriverManager.getConnection("jdbc:mysql://localhost/ssb?" + "user=root"); I can create a resultset from a SELECT statement and fetch the results. But this does not (throws ClassNotFound exception): Class.forName("com.mysql.cj.jdbc.Driver").newInstance(); neither does Class.forName("com.mysql.cj.jdbc.Driver").newInstance(); I am confused, because the JDBC connection works as long as I don't use Class.forName. I don't know how to figure out the name of the class that is actually being used. I'm sorry if this is silly, but I do not have much java experience. The MySQL documentation says to use com.mysql.cj.jdbc.Driver: https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-connect-drivermanager.html I suppose this is some kind of MySQL JDBC problem, so this may not be the right mailing list, but if you have any suggestions before I start looking elsewhere, I would appreciate it. On Wed, Oct 6, 2021 at 1:59 PM Julian Hyde wrote: > It looks as if com.mysql.cj.jdbc.Driver is not on your class path. > > If you are launching via SQLLine, you will need to edit the sqlline shell > script to add a jar (or jars) to your class path. > > Julian > > > > On Oct 6, 2021, at 10:43 AM, Justin Swanhart > wrote: > > > > I am probably making some obvious mistake, but I am having a problem > > getting a simple MySQL JDBC connection working. > > > > I have the latest version of the Connector/J MySQL java client driver. I > > have a MySQL 8 server running on the local machine, and the following > model > > JSON: > > { > > version: '1.0', > > defaultSchema: 'ssb', > > schemas: [ > >{ > > name: 'ssb', > > type: 'custom', > > factory: 'org.apache.calcite.adapter.jdbc.JdbcSchema$Factory', > > operand: { > >jdbcDriver: 'com.mysql.cj.jdbc.Driver', > >jdbcUrl: 'jdbc:mysql://localhost/ssb', > >jdbcUser: 'root', > >jdbcPassword: '' > > } > >} > > ] > > } > > > > I have the MySQL driver in my CLASSPATH. I can compile a simple test > > program which verifies that the class com.mysql.jdbc.Driver exists. > > > > While the test program works, sqlline does not: > > $ ./sqlline -d com.mysql.cj.jdbc.Driver -u jdbc:mysql://root@localhost > /ssb > > Building Apache Calcite 1.28.0-SNAPSHOT > > scan complete in 1ms > > Could not find driver com.mysql.cj.jdbc.Driver > > > > Any suggestions about what I might be doing wrong? > > > > Using the !connect command yields a large backtrace (while probably not > > useful, included for completeness): > > sqlline version 1.11.0 > > sqlline> !connect jdbc:calcite:model=test.json admin admin > > WARNING: An illegal reflective access operation has occurred > > WARNING: Illegal reflective access by com.google.protobuf.UnsafeUtil > > > (file:/home/justin/.gradle/caches/modules-2/files-2.1/com.google.protobuf/protobuf-java/3.6.1/d06d46ecfd92ec6d0f3b423b4cd81cb38d8b924/protobuf-java-3.6.1.jar) > > to field java.nio.Buffer.address > > WARNING: Please consider reporting this to the maintainers of > > com.google.protobuf.UnsafeUtil > > WARNING: Use --illegal-access=warn to enable warnings of further illegal > > reflective access operations > > WARNING: All illegal access operations will be denied in a future release > > java.lang.RuntimeException: Error instantiating > JsonCustomSchema(name=ssb) > > at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:277) > > at > > > org.apache.calcite.model.JsonCustomSchema.accept(JsonCustomSchema.java:66) > > at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:200) > > at org.apache.calcite.model.ModelHandler.(ModelHandler.java:106) > > at org.apache.calcite.jdbc.Driver$1.onConnectionInit(Driver.java:101) > > at > > > org.apache.calcite.avatica.UnregisteredDriver.connect(UnregisteredDriver.java:139) > > at sqlline.DatabaseConnection.connect(DatabaseConnection.java:135) > > at sqlline.DatabaseConnection.getConnection(DatabaseConnection.java:192) > > at sqlline.Commands.connect(Commands.java:1481) > > at sqlline.Commands.connect(Commands.java:1355) > > at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native > > Method) > > at > > > java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) > > at > > > java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > > at java.base/java.lang.reflect.Method.invoke(Method.java:566) > > at > > > sqlline.ReflectiveCommandHandler.execute(ReflectiveCommandHandler.java:44) > > at sqlline.SqlLine.dispatch(SqlLine.java:818) > > at sqlline.SqlLine.begin(SqlLine.java:596) > > at sqlline.SqlLine.start(SqlLine.java:269) > > at sqlline.SqlLine.main(SqlLine.java:208) > > Caused by: com.google.
Re: Problem using MySQL with JDBC
Does your environment use shading? Maybe Class.forName with a constant argument is handled by the shading, but Calcite is calling Class.forName with a dynamic argument. > On Oct 6, 2021, at 11:41 AM, Justin Swanhart wrote: > > Hi, > > The jar is in the classpath, and I can run the java command that the > sqlline script runs directly, and I get the same error. > > The following works in a test java program with the CLASSPATH set: > >conn = > DriverManager.getConnection("jdbc:mysql://localhost/ssb?" + > "user=root"); > I can create a resultset from a SELECT statement and fetch the results. > > But this does not (throws ClassNotFound exception): > Class.forName("com.mysql.cj.jdbc.Driver").newInstance(); > neither does > Class.forName("com.mysql.cj.jdbc.Driver").newInstance(); > > I am confused, because the JDBC connection works as long as I don't use > Class.forName. I don't know how to figure out the name of the class that > is actually being used. I'm sorry if this is silly, but I do not have much > java experience. The MySQL documentation says to use > com.mysql.cj.jdbc.Driver: > https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-connect-drivermanager.html > > I suppose this is some kind of MySQL JDBC problem, so this may not be the > right mailing list, but if you have any suggestions before I start looking > elsewhere, I would appreciate it. > > On Wed, Oct 6, 2021 at 1:59 PM Julian Hyde wrote: > >> It looks as if com.mysql.cj.jdbc.Driver is not on your class path. >> >> If you are launching via SQLLine, you will need to edit the sqlline shell >> script to add a jar (or jars) to your class path. >> >> Julian >> >> >>> On Oct 6, 2021, at 10:43 AM, Justin Swanhart >> wrote: >>> >>> I am probably making some obvious mistake, but I am having a problem >>> getting a simple MySQL JDBC connection working. >>> >>> I have the latest version of the Connector/J MySQL java client driver. I >>> have a MySQL 8 server running on the local machine, and the following >> model >>> JSON: >>> { >>> version: '1.0', >>> defaultSchema: 'ssb', >>> schemas: [ >>> { >>> name: 'ssb', >>> type: 'custom', >>> factory: 'org.apache.calcite.adapter.jdbc.JdbcSchema$Factory', >>> operand: { >>> jdbcDriver: 'com.mysql.cj.jdbc.Driver', >>> jdbcUrl: 'jdbc:mysql://localhost/ssb', >>> jdbcUser: 'root', >>> jdbcPassword: '' >>> } >>> } >>> ] >>> } >>> >>> I have the MySQL driver in my CLASSPATH. I can compile a simple test >>> program which verifies that the class com.mysql.jdbc.Driver exists. >>> >>> While the test program works, sqlline does not: >>> $ ./sqlline -d com.mysql.cj.jdbc.Driver -u jdbc:mysql://root@localhost >> /ssb >>> Building Apache Calcite 1.28.0-SNAPSHOT >>> scan complete in 1ms >>> Could not find driver com.mysql.cj.jdbc.Driver >>> >>> Any suggestions about what I might be doing wrong? >>> >>> Using the !connect command yields a large backtrace (while probably not >>> useful, included for completeness): >>> sqlline version 1.11.0 >>> sqlline> !connect jdbc:calcite:model=test.json admin admin >>> WARNING: An illegal reflective access operation has occurred >>> WARNING: Illegal reflective access by com.google.protobuf.UnsafeUtil >>> >> (file:/home/justin/.gradle/caches/modules-2/files-2.1/com.google.protobuf/protobuf-java/3.6.1/d06d46ecfd92ec6d0f3b423b4cd81cb38d8b924/protobuf-java-3.6.1.jar) >>> to field java.nio.Buffer.address >>> WARNING: Please consider reporting this to the maintainers of >>> com.google.protobuf.UnsafeUtil >>> WARNING: Use --illegal-access=warn to enable warnings of further illegal >>> reflective access operations >>> WARNING: All illegal access operations will be denied in a future release >>> java.lang.RuntimeException: Error instantiating >> JsonCustomSchema(name=ssb) >>> at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:277) >>> at >>> >> org.apache.calcite.model.JsonCustomSchema.accept(JsonCustomSchema.java:66) >>> at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:200) >>> at org.apache.calcite.model.ModelHandler.(ModelHandler.java:106) >>> at org.apache.calcite.jdbc.Driver$1.onConnectionInit(Driver.java:101) >>> at >>> >> org.apache.calcite.avatica.UnregisteredDriver.connect(UnregisteredDriver.java:139) >>> at sqlline.DatabaseConnection.connect(DatabaseConnection.java:135) >>> at sqlline.DatabaseConnection.getConnection(DatabaseConnection.java:192) >>> at sqlline.Commands.connect(Commands.java:1481) >>> at sqlline.Commands.connect(Commands.java:1355) >>> at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native >>> Method) >>> at >>> >> java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) >>> at >>> >> java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) >>> at java.base/java.lang.reflect.Method.invoke(Method.java:566)
Re: Problem using MySQL with JDBC
Hi, The class.forName doesn't work for me though (tried org.mysql.cj.jdbc.Driver and org.mysql.jdbc.Driver), but just using a "jdbc:mysql://" connection string does work. It makes sense that class.forName throws the same exception for me as Calcite, but I don't understand why just using a connection string works, so I am really confused. I assume some other class name is being used when I just use the connection string, but I don't know how to figure out what that class name is. On Wed, Oct 6, 2021 at 2:55 PM Julian Hyde wrote: > Does your environment use shading? Maybe Class.forName with a constant > argument is handled by the shading, but Calcite is calling Class.forName > with a dynamic argument. > > > On Oct 6, 2021, at 11:41 AM, Justin Swanhart > wrote: > > > > Hi, > > > > The jar is in the classpath, and I can run the java command that the > > sqlline script runs directly, and I get the same error. > > > > The following works in a test java program with the CLASSPATH set: > > > >conn = > > DriverManager.getConnection("jdbc:mysql://localhost/ssb?" + > > "user=root"); > > I can create a resultset from a SELECT statement and fetch the results. > > > > But this does not (throws ClassNotFound exception): > > Class.forName("com.mysql.cj.jdbc.Driver").newInstance(); > > neither does > > Class.forName("com.mysql.cj.jdbc.Driver").newInstance(); > > > > I am confused, because the JDBC connection works as long as I don't use > > Class.forName. I don't know how to figure out the name of the class that > > is actually being used. I'm sorry if this is silly, but I do not have > much > > java experience. The MySQL documentation says to use > > com.mysql.cj.jdbc.Driver: > > > https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-connect-drivermanager.html > > > > I suppose this is some kind of MySQL JDBC problem, so this may not be the > > right mailing list, but if you have any suggestions before I start > looking > > elsewhere, I would appreciate it. > > > > On Wed, Oct 6, 2021 at 1:59 PM Julian Hyde > wrote: > > > >> It looks as if com.mysql.cj.jdbc.Driver is not on your class path. > >> > >> If you are launching via SQLLine, you will need to edit the sqlline > shell > >> script to add a jar (or jars) to your class path. > >> > >> Julian > >> > >> > >>> On Oct 6, 2021, at 10:43 AM, Justin Swanhart > >> wrote: > >>> > >>> I am probably making some obvious mistake, but I am having a problem > >>> getting a simple MySQL JDBC connection working. > >>> > >>> I have the latest version of the Connector/J MySQL java client > driver. I > >>> have a MySQL 8 server running on the local machine, and the following > >> model > >>> JSON: > >>> { > >>> version: '1.0', > >>> defaultSchema: 'ssb', > >>> schemas: [ > >>> { > >>> name: 'ssb', > >>> type: 'custom', > >>> factory: 'org.apache.calcite.adapter.jdbc.JdbcSchema$Factory', > >>> operand: { > >>> jdbcDriver: 'com.mysql.cj.jdbc.Driver', > >>> jdbcUrl: 'jdbc:mysql://localhost/ssb', > >>> jdbcUser: 'root', > >>> jdbcPassword: '' > >>> } > >>> } > >>> ] > >>> } > >>> > >>> I have the MySQL driver in my CLASSPATH. I can compile a simple test > >>> program which verifies that the class com.mysql.jdbc.Driver exists. > >>> > >>> While the test program works, sqlline does not: > >>> $ ./sqlline -d com.mysql.cj.jdbc.Driver -u jdbc:mysql://root@localhost > >> /ssb > >>> Building Apache Calcite 1.28.0-SNAPSHOT > >>> scan complete in 1ms > >>> Could not find driver com.mysql.cj.jdbc.Driver > >>> > >>> Any suggestions about what I might be doing wrong? > >>> > >>> Using the !connect command yields a large backtrace (while probably not > >>> useful, included for completeness): > >>> sqlline version 1.11.0 > >>> sqlline> !connect jdbc:calcite:model=test.json admin admin > >>> WARNING: An illegal reflective access operation has occurred > >>> WARNING: Illegal reflective access by com.google.protobuf.UnsafeUtil > >>> > >> > (file:/home/justin/.gradle/caches/modules-2/files-2.1/com.google.protobuf/protobuf-java/3.6.1/d06d46ecfd92ec6d0f3b423b4cd81cb38d8b924/protobuf-java-3.6.1.jar) > >>> to field java.nio.Buffer.address > >>> WARNING: Please consider reporting this to the maintainers of > >>> com.google.protobuf.UnsafeUtil > >>> WARNING: Use --illegal-access=warn to enable warnings of further > illegal > >>> reflective access operations > >>> WARNING: All illegal access operations will be denied in a future > release > >>> java.lang.RuntimeException: Error instantiating > >> JsonCustomSchema(name=ssb) > >>> at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:277) > >>> at > >>> > >> > org.apache.calcite.model.JsonCustomSchema.accept(JsonCustomSchema.java:66) > >>> at org.apache.calcite.model.ModelHandler.visit(ModelHandler.java:200) > >>> at org.apache.calcite.model.ModelHandler.(ModelHandler.java:106) > >>> at org.apache.calcite.jdbc.Driver$1.onConnectionInit(Dri
Re: Problem using MySQL with JDBC
Hi, So the example from MySQL is: [justin@localhost calcite.old]$ cat LoadDriver.java import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; // Notice, do not import com.mysql.jdbc.* // or you will have problems! public class LoadDriver { public static void main(String[] args) { // The newInstance() call is a work around for some // broken Java implementations try{ Class.forName("com.mysql.jdbc.Driver").newInstance(); } catch(Exception ex) { } } } The code ignores an exception that is thrown when using .newInstance(). The program outputs: Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. (it outputs nothing if the new driver name is used) So it seems that the MySQL driver throws an exception when used this way that has to be ignored, and Calcite is not ignoring it? If that is the case, it seems like a problem with the MySQL driver, but I just want to confirm with you before I go report a bug and look silly :) On Wed, Oct 6, 2021 at 2:59 PM Justin Swanhart wrote: > Hi, > > The class.forName doesn't work for me though (tried > org.mysql.cj.jdbc.Driver and org.mysql.jdbc.Driver), but just using a > "jdbc:mysql://" connection string does work. > > It makes sense that class.forName throws the same exception for me as > Calcite, but I don't understand why just using a connection string works, > so I am really confused. I assume some other class name is being used when > I just use the connection string, but I don't know how to figure out what > that class name is. > > On Wed, Oct 6, 2021 at 2:55 PM Julian Hyde wrote: > >> Does your environment use shading? Maybe Class.forName with a constant >> argument is handled by the shading, but Calcite is calling Class.forName >> with a dynamic argument. >> >> > On Oct 6, 2021, at 11:41 AM, Justin Swanhart >> wrote: >> > >> > Hi, >> > >> > The jar is in the classpath, and I can run the java command that the >> > sqlline script runs directly, and I get the same error. >> > >> > The following works in a test java program with the CLASSPATH set: >> > >> >conn = >> > DriverManager.getConnection("jdbc:mysql://localhost/ssb?" + >> > "user=root"); >> > I can create a resultset from a SELECT statement and fetch the results. >> > >> > But this does not (throws ClassNotFound exception): >> > Class.forName("com.mysql.cj.jdbc.Driver").newInstance(); >> > neither does >> > Class.forName("com.mysql.cj.jdbc.Driver").newInstance(); >> > >> > I am confused, because the JDBC connection works as long as I don't use >> > Class.forName. I don't know how to figure out the name of the class >> that >> > is actually being used. I'm sorry if this is silly, but I do not have >> much >> > java experience. The MySQL documentation says to use >> > com.mysql.cj.jdbc.Driver: >> > >> https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-connect-drivermanager.html >> > >> > I suppose this is some kind of MySQL JDBC problem, so this may not be >> the >> > right mailing list, but if you have any suggestions before I start >> looking >> > elsewhere, I would appreciate it. >> > >> > On Wed, Oct 6, 2021 at 1:59 PM Julian Hyde >> wrote: >> > >> >> It looks as if com.mysql.cj.jdbc.Driver is not on your class path. >> >> >> >> If you are launching via SQLLine, you will need to edit the sqlline >> shell >> >> script to add a jar (or jars) to your class path. >> >> >> >> Julian >> >> >> >> >> >>> On Oct 6, 2021, at 10:43 AM, Justin Swanhart >> >> wrote: >> >>> >> >>> I am probably making some obvious mistake, but I am having a problem >> >>> getting a simple MySQL JDBC connection working. >> >>> >> >>> I have the latest version of the Connector/J MySQL java client >> driver. I >> >>> have a MySQL 8 server running on the local machine, and the following >> >> model >> >>> JSON: >> >>> { >> >>> version: '1.0', >> >>> defaultSchema: 'ssb', >> >>> schemas: [ >> >>> { >> >>> name: 'ssb', >> >>> type: 'custom', >> >>> factory: 'org.apache.calcite.adapter.jdbc.JdbcSchema$Factory', >> >>> operand: { >> >>> jdbcDriver: 'com.mysql.cj.jdbc.Driver', >> >>> jdbcUrl: 'jdbc:mysql://localhost/ssb', >> >>> jdbcUser: 'root', >> >>> jdbcPassword: '' >> >>> } >> >>> } >> >>> ] >> >>> } >> >>> >> >>> I have the MySQL driver in my CLASSPATH. I can compile a simple test >> >>> program which verifies that the class com.mysql.jdbc.Driver exists. >> >>> >> >>> While the test program works, sqlline does not: >> >>> $ ./sqlline -d com.mysql.cj.jdbc.Driver -u jdbc:mysql://root@localhost >> >> /ssb >> >>> Building Apache Calcite 1.28.0-SNAPSHOT >> >>> scan complete in 1ms >> >>> Could not find driver com.mysql.cj.jdbc.Driver >> >>> >> >>> Any suggestions about what I might
Re: Problem using MySQL with JDBC
What happens if you remove the line jdbcDriver: 'com.mysql.cj.jdbc.Driver’, from your Calcite model? Hopefully, it just works. Most drivers load automatically these days, and if you don’t specify the class name, Calcite won’t try to load it manually. > On Oct 6, 2021, at 12:07 PM, Justin Swanhart wrote: > > Hi, > > So the example from MySQL is: > [justin@localhost calcite.old]$ cat LoadDriver.java > import java.sql.Connection; > import java.sql.DriverManager; > import java.sql.SQLException; > > // Notice, do not import com.mysql.jdbc.* > // or you will have problems! > > public class LoadDriver { >public static void main(String[] args) { >// The newInstance() call is a work around for some >// broken Java implementations > try{ > Class.forName("com.mysql.jdbc.Driver").newInstance(); > } catch(Exception ex) { >} > } > } > > The code ignores an exception that is thrown when using .newInstance(). > The program outputs: > Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver > class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered > via the SPI and manual loading of the driver class is generally unnecessary. > (it outputs nothing if the new driver name is used) > > So it seems that the MySQL driver throws an exception when used this way > that has to be ignored, and Calcite is not ignoring it? > > If that is the case, it seems like a problem with the MySQL driver, but I > just want to confirm with you before I go report a bug and look silly :) > > On Wed, Oct 6, 2021 at 2:59 PM Justin Swanhart wrote: > >> Hi, >> >> The class.forName doesn't work for me though (tried >> org.mysql.cj.jdbc.Driver and org.mysql.jdbc.Driver), but just using a >> "jdbc:mysql://" connection string does work. >> >> It makes sense that class.forName throws the same exception for me as >> Calcite, but I don't understand why just using a connection string works, >> so I am really confused. I assume some other class name is being used when >> I just use the connection string, but I don't know how to figure out what >> that class name is. >> >> On Wed, Oct 6, 2021 at 2:55 PM Julian Hyde wrote: >> >>> Does your environment use shading? Maybe Class.forName with a constant >>> argument is handled by the shading, but Calcite is calling Class.forName >>> with a dynamic argument. >>> On Oct 6, 2021, at 11:41 AM, Justin Swanhart >>> wrote: Hi, The jar is in the classpath, and I can run the java command that the sqlline script runs directly, and I get the same error. The following works in a test java program with the CLASSPATH set: conn = DriverManager.getConnection("jdbc:mysql://localhost/ssb?" + "user=root"); I can create a resultset from a SELECT statement and fetch the results. But this does not (throws ClassNotFound exception): Class.forName("com.mysql.cj.jdbc.Driver").newInstance(); neither does Class.forName("com.mysql.cj.jdbc.Driver").newInstance(); I am confused, because the JDBC connection works as long as I don't use Class.forName. I don't know how to figure out the name of the class >>> that is actually being used. I'm sorry if this is silly, but I do not have >>> much java experience. The MySQL documentation says to use com.mysql.cj.jdbc.Driver: >>> https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-connect-drivermanager.html I suppose this is some kind of MySQL JDBC problem, so this may not be >>> the right mailing list, but if you have any suggestions before I start >>> looking elsewhere, I would appreciate it. On Wed, Oct 6, 2021 at 1:59 PM Julian Hyde >>> wrote: > It looks as if com.mysql.cj.jdbc.Driver is not on your class path. > > If you are launching via SQLLine, you will need to edit the sqlline >>> shell > script to add a jar (or jars) to your class path. > > Julian > > >> On Oct 6, 2021, at 10:43 AM, Justin Swanhart > wrote: >> >> I am probably making some obvious mistake, but I am having a problem >> getting a simple MySQL JDBC connection working. >> >> I have the latest version of the Connector/J MySQL java client >>> driver. I >> have a MySQL 8 server running on the local machine, and the following > model >> JSON: >> { >> version: '1.0', >> defaultSchema: 'ssb', >> schemas: [ >> { >>name: 'ssb', >>type: 'custom', >>factory: 'org.apache.calcite.adapter.jdbc.JdbcSchema$Factory', >>operand: { >> jdbcDriver: 'com.mysql.cj.jdbc.Driver', >> jdbcUrl: 'jdbc:mysql://localhost/ssb', >> jdbcUser: 'root', >> jdbcPassword: '' >>} >> } >> ] >> } >> >> I have the MySQL driver in my CLASSPATH. I can comp
Re: Problem using MySQL with JDBC
Caused by: com.google.common.util.concurrent.UncheckedExecutionException: java.lang.RuntimeException: java.sql.SQLException: Cannot create JDBC driver of class '' for connect URL 'jdbc:mysql://localhost/ssb' On Wed, Oct 6, 2021 at 3:14 PM Julian Hyde wrote: > What happens if you remove the line > > jdbcDriver: 'com.mysql.cj.jdbc.Driver’, > > from your Calcite model? Hopefully, it just works. Most drivers load > automatically these days, and if you don’t specify the class name, Calcite > won’t try to load it manually. > > > On Oct 6, 2021, at 12:07 PM, Justin Swanhart > wrote: > > > > Hi, > > > > So the example from MySQL is: > > [justin@localhost calcite.old]$ cat LoadDriver.java > > import java.sql.Connection; > > import java.sql.DriverManager; > > import java.sql.SQLException; > > > > // Notice, do not import com.mysql.jdbc.* > > // or you will have problems! > > > > public class LoadDriver { > >public static void main(String[] args) { > >// The newInstance() call is a work around for some > >// broken Java implementations > > try{ > > Class.forName("com.mysql.jdbc.Driver").newInstance(); > > } catch(Exception ex) { > >} > > } > > } > > > > The code ignores an exception that is thrown when using .newInstance(). > > The program outputs: > > Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver > > class is `com.mysql.cj.jdbc.Driver'. The driver is automatically > registered > > via the SPI and manual loading of the driver class is generally > unnecessary. > > (it outputs nothing if the new driver name is used) > > > > So it seems that the MySQL driver throws an exception when used this way > > that has to be ignored, and Calcite is not ignoring it? > > > > If that is the case, it seems like a problem with the MySQL driver, but I > > just want to confirm with you before I go report a bug and look silly :) > > > > On Wed, Oct 6, 2021 at 2:59 PM Justin Swanhart > wrote: > > > >> Hi, > >> > >> The class.forName doesn't work for me though (tried > >> org.mysql.cj.jdbc.Driver and org.mysql.jdbc.Driver), but just using a > >> "jdbc:mysql://" connection string does work. > >> > >> It makes sense that class.forName throws the same exception for me as > >> Calcite, but I don't understand why just using a connection string > works, > >> so I am really confused. I assume some other class name is being used > when > >> I just use the connection string, but I don't know how to figure out > what > >> that class name is. > >> > >> On Wed, Oct 6, 2021 at 2:55 PM Julian Hyde > wrote: > >> > >>> Does your environment use shading? Maybe Class.forName with a constant > >>> argument is handled by the shading, but Calcite is calling > Class.forName > >>> with a dynamic argument. > >>> > On Oct 6, 2021, at 11:41 AM, Justin Swanhart > >>> wrote: > > Hi, > > The jar is in the classpath, and I can run the java command that the > sqlline script runs directly, and I get the same error. > > The following works in a test java program with the CLASSPATH set: > > conn = > DriverManager.getConnection("jdbc:mysql://localhost/ssb?" + > "user=root"); > I can create a resultset from a SELECT statement and fetch the > results. > > But this does not (throws ClassNotFound exception): > Class.forName("com.mysql.cj.jdbc.Driver").newInstance(); > neither does > Class.forName("com.mysql.cj.jdbc.Driver").newInstance(); > > I am confused, because the JDBC connection works as long as I don't > use > Class.forName. I don't know how to figure out the name of the class > >>> that > is actually being used. I'm sorry if this is silly, but I do not have > >>> much > java experience. The MySQL documentation says to use > com.mysql.cj.jdbc.Driver: > > >>> > https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-connect-drivermanager.html > > I suppose this is some kind of MySQL JDBC problem, so this may not be > >>> the > right mailing list, but if you have any suggestions before I start > >>> looking > elsewhere, I would appreciate it. > > On Wed, Oct 6, 2021 at 1:59 PM Julian Hyde > >>> wrote: > > > It looks as if com.mysql.cj.jdbc.Driver is not on your class path. > > > > If you are launching via SQLLine, you will need to edit the sqlline > >>> shell > > script to add a jar (or jars) to your class path. > > > > Julian > > > > > >> On Oct 6, 2021, at 10:43 AM, Justin Swanhart > > wrote: > >> > >> I am probably making some obvious mistake, but I am having a problem > >> getting a simple MySQL JDBC connection working. > >> > >> I have the latest version of the Connector/J MySQL java client > >>> driver. I > >> have a MySQL 8 server running on the local machine, and the > following > > model > >> JSON:
Re: Problem using MySQL with JDBC
[justin@localhost calcite]$ cat ../calcite.old/test.json { version: '1.0', defaultSchema: 'ssb', schemas: [ { name: 'ssb', type: 'custom', factory: 'org.apache.calcite.adapter.jdbc.JdbcSchema$Factory', operand: { jdbcUrl: 'jdbc:mysql://localhost/ssb', jdbcUser: 'root', jdbcPassword: '' } } ] } On Wed, Oct 6, 2021 at 3:15 PM Justin Swanhart wrote: > Caused by: com.google.common.util.concurrent.UncheckedExecutionException: > java.lang.RuntimeException: java.sql.SQLException: Cannot create JDBC > driver of class '' for connect URL 'jdbc:mysql://localhost/ssb' > > On Wed, Oct 6, 2021 at 3:14 PM Julian Hyde wrote: > >> What happens if you remove the line >> >> jdbcDriver: 'com.mysql.cj.jdbc.Driver’, >> >> from your Calcite model? Hopefully, it just works. Most drivers load >> automatically these days, and if you don’t specify the class name, Calcite >> won’t try to load it manually. >> >> > On Oct 6, 2021, at 12:07 PM, Justin Swanhart >> wrote: >> > >> > Hi, >> > >> > So the example from MySQL is: >> > [justin@localhost calcite.old]$ cat LoadDriver.java >> > import java.sql.Connection; >> > import java.sql.DriverManager; >> > import java.sql.SQLException; >> > >> > // Notice, do not import com.mysql.jdbc.* >> > // or you will have problems! >> > >> > public class LoadDriver { >> >public static void main(String[] args) { >> >// The newInstance() call is a work around for some >> >// broken Java implementations >> > try{ >> > Class.forName("com.mysql.jdbc.Driver").newInstance(); >> > } catch(Exception ex) { >> >} >> > } >> > } >> > >> > The code ignores an exception that is thrown when using .newInstance(). >> > The program outputs: >> > Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new >> driver >> > class is `com.mysql.cj.jdbc.Driver'. The driver is automatically >> registered >> > via the SPI and manual loading of the driver class is generally >> unnecessary. >> > (it outputs nothing if the new driver name is used) >> > >> > So it seems that the MySQL driver throws an exception when used this way >> > that has to be ignored, and Calcite is not ignoring it? >> > >> > If that is the case, it seems like a problem with the MySQL driver, but >> I >> > just want to confirm with you before I go report a bug and look silly :) >> > >> > On Wed, Oct 6, 2021 at 2:59 PM Justin Swanhart >> wrote: >> > >> >> Hi, >> >> >> >> The class.forName doesn't work for me though (tried >> >> org.mysql.cj.jdbc.Driver and org.mysql.jdbc.Driver), but just using a >> >> "jdbc:mysql://" connection string does work. >> >> >> >> It makes sense that class.forName throws the same exception for me as >> >> Calcite, but I don't understand why just using a connection string >> works, >> >> so I am really confused. I assume some other class name is being used >> when >> >> I just use the connection string, but I don't know how to figure out >> what >> >> that class name is. >> >> >> >> On Wed, Oct 6, 2021 at 2:55 PM Julian Hyde >> wrote: >> >> >> >>> Does your environment use shading? Maybe Class.forName with a constant >> >>> argument is handled by the shading, but Calcite is calling >> Class.forName >> >>> with a dynamic argument. >> >>> >> On Oct 6, 2021, at 11:41 AM, Justin Swanhart >> >>> wrote: >> >> Hi, >> >> The jar is in the classpath, and I can run the java command that the >> sqlline script runs directly, and I get the same error. >> >> The following works in a test java program with the CLASSPATH set: >> >> conn = >> DriverManager.getConnection("jdbc:mysql://localhost/ssb?" + >> "user=root"); >> I can create a resultset from a SELECT statement and fetch the >> results. >> >> But this does not (throws ClassNotFound exception): >> Class.forName("com.mysql.cj.jdbc.Driver").newInstance(); >> neither does >> Class.forName("com.mysql.cj.jdbc.Driver").newInstance(); >> >> I am confused, because the JDBC connection works as long as I don't >> use >> Class.forName. I don't know how to figure out the name of the class >> >>> that >> is actually being used. I'm sorry if this is silly, but I do not >> have >> >>> much >> java experience. The MySQL documentation says to use >> com.mysql.cj.jdbc.Driver: >> >> >>> >> https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-usagenotes-connect-drivermanager.html >> >> I suppose this is some kind of MySQL JDBC problem, so this may not be >> >>> the >> right mailing list, but if you have any suggestions before I start >> >>> looking >> elsewhere, I would appreciate it. >> >> On Wed, Oct 6, 2021 at 1:59 PM Julian Hyde >> >>> wrote: >> >> > It looks as if com.mysql.cj.jdbc.Driver is not on your class path. >> > >> > If you are launching via SQLLine, you w
Re: Problem using MySQL with JDBC
Hi, I had to really go down a rabbit hole here to figure out exactly what was wrong (see below) but I was able to resolve the problem by adding the following to core/build.gradle.kts: implementation(files("/home/justin/testing/calcite/mysql-connector-java.jar")) It seems that the environmental CLASSPATH is not incorporated into the path stored in the .jar file. I ended up compiling a custom version of dbcp2 that printed out the list of available JDBC drivers and the MySQL driver was not listed. After adding it to the gradle file, the CLASSPATH was set correctly and the list included the MySQL driver. I reverted all my changes except the one to core/build.gradle.kts and it works. I assume this is the right thing to do, but if not, let me know. On Wed, Oct 6, 2021 at 3:16 PM Justin Swanhart wrote: > [justin@localhost calcite]$ cat ../calcite.old/test.json > { > version: '1.0', > defaultSchema: 'ssb', > schemas: [ > { > name: 'ssb', > type: 'custom', > factory: 'org.apache.calcite.adapter.jdbc.JdbcSchema$Factory', > operand: { > jdbcUrl: 'jdbc:mysql://localhost/ssb', > jdbcUser: 'root', > jdbcPassword: '' > } > } > ] > } > > On Wed, Oct 6, 2021 at 3:15 PM Justin Swanhart > wrote: > >> Caused by: com.google.common.util.concurrent.UncheckedExecutionException: >> java.lang.RuntimeException: java.sql.SQLException: Cannot create JDBC >> driver of class '' for connect URL 'jdbc:mysql://localhost/ssb' >> >> On Wed, Oct 6, 2021 at 3:14 PM Julian Hyde >> wrote: >> >>> What happens if you remove the line >>> >>> jdbcDriver: 'com.mysql.cj.jdbc.Driver’, >>> >>> from your Calcite model? Hopefully, it just works. Most drivers load >>> automatically these days, and if you don’t specify the class name, Calcite >>> won’t try to load it manually. >>> >>> > On Oct 6, 2021, at 12:07 PM, Justin Swanhart >>> wrote: >>> > >>> > Hi, >>> > >>> > So the example from MySQL is: >>> > [justin@localhost calcite.old]$ cat LoadDriver.java >>> > import java.sql.Connection; >>> > import java.sql.DriverManager; >>> > import java.sql.SQLException; >>> > >>> > // Notice, do not import com.mysql.jdbc.* >>> > // or you will have problems! >>> > >>> > public class LoadDriver { >>> >public static void main(String[] args) { >>> >// The newInstance() call is a work around for some >>> >// broken Java implementations >>> > try{ >>> > Class.forName("com.mysql.jdbc.Driver").newInstance(); >>> > } catch(Exception ex) { >>> >} >>> > } >>> > } >>> > >>> > The code ignores an exception that is thrown when using .newInstance(). >>> > The program outputs: >>> > Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new >>> driver >>> > class is `com.mysql.cj.jdbc.Driver'. The driver is automatically >>> registered >>> > via the SPI and manual loading of the driver class is generally >>> unnecessary. >>> > (it outputs nothing if the new driver name is used) >>> > >>> > So it seems that the MySQL driver throws an exception when used this >>> way >>> > that has to be ignored, and Calcite is not ignoring it? >>> > >>> > If that is the case, it seems like a problem with the MySQL driver, >>> but I >>> > just want to confirm with you before I go report a bug and look silly >>> :) >>> > >>> > On Wed, Oct 6, 2021 at 2:59 PM Justin Swanhart >>> wrote: >>> > >>> >> Hi, >>> >> >>> >> The class.forName doesn't work for me though (tried >>> >> org.mysql.cj.jdbc.Driver and org.mysql.jdbc.Driver), but just using a >>> >> "jdbc:mysql://" connection string does work. >>> >> >>> >> It makes sense that class.forName throws the same exception for me as >>> >> Calcite, but I don't understand why just using a connection string >>> works, >>> >> so I am really confused. I assume some other class name is being >>> used when >>> >> I just use the connection string, but I don't know how to figure out >>> what >>> >> that class name is. >>> >> >>> >> On Wed, Oct 6, 2021 at 2:55 PM Julian Hyde >>> wrote: >>> >> >>> >>> Does your environment use shading? Maybe Class.forName with a >>> constant >>> >>> argument is handled by the shading, but Calcite is calling >>> Class.forName >>> >>> with a dynamic argument. >>> >>> >>> On Oct 6, 2021, at 11:41 AM, Justin Swanhart >>> >>> wrote: >>> >>> Hi, >>> >>> The jar is in the classpath, and I can run the java command that the >>> sqlline script runs directly, and I get the same error. >>> >>> The following works in a test java program with the CLASSPATH set: >>> >>> conn = >>> DriverManager.getConnection("jdbc:mysql://localhost/ssb?" + >>> "user=root"); >>> I can create a resultset from a SELECT statement and fetch the >>> results. >>> >>> But this does not (throws ClassNotFound exception): >>> Class.forName("com.mysql.cj.jdbc.Driver").newInstance(); >>> neither does >>> Class.forNa
Re: Problem using MySQL with JDBC
And I immediately hit another little roadblock: Transaction isolation level TRANSACTION_REPEATABLE_READ is not supported. Default (TRANSACTION_NONE) will be used instead. 0: jdbc:calcite:schemaType=JDBC> !tables +---+-++--+-+--++---+---++ | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | REMARKS | TYPE_CAT | TYPE_SCHEM | TYPE_NAME | SELF_REFERENCING_COL_NAME | REF_GENERATION | +---+-++--+-+--++---+---++ | | adhoc | customer | TABLE| | || | || | | adhoc | dim_date | TABLE| | || | || | | adhoc | lineorder | TABLE| | || | || | | adhoc | part | TABLE| | || | || | | adhoc | supplier | TABLE| | || | || | | metadata| COLUMNS| SYSTEM TABLE | | || | || | | metadata| TABLES | SYSTEM TABLE | | || | || +---+-++--+-+--++---+---++ 0: jdbc:calcite:schemaType=JDBC> select count(*) from lineorder where LO_OrderDateKey=19980101; log4j:WARN No appenders could be found for logger (org.apache.calcite.sql.parser). log4j:WARN Please initialize the log4j system properly. log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info. Error: Error while executing SQL "select count(*) from lineorder where LO_OrderDateKey=19980101": From line 1, column 22 to line 1, column 30: Object 'LINEORDER' not found within 'adhoc'; did you mean 'lineorder'? (state=,code=0) So two issues here. 1) MySQL does support REPEATABLE-READ and 2) it is case sensitive by default. Is there something I need to change to make Calcite respect the case in which I enter literals? I can work around issue two by enclosing my table and column names in double quotes, which is fine for testing, but I would like it to respect the case of the tables and columns without using literals. Any suggestions? On Thu, Oct 7, 2021 at 4:08 PM Justin Swanhart wrote: > Hi, > > I had to really go down a rabbit hole here to figure out exactly what was > wrong (see below) but I was able to resolve the problem by adding the > following to core/build.gradle.kts: > > implementation(files("/home/justin/testing/calcite/mysql-connector-java.jar")) > > It seems that the environmental CLASSPATH is not incorporated into the > path stored in the .jar file. I ended up compiling a custom version of > dbcp2 that printed out the list of available JDBC drivers and the MySQL > driver was not listed. After adding it to the gradle file, the CLASSPATH > was set correctly and the list included the MySQL driver. I reverted all > my changes except the one to core/build.gradle.kts and it works. > > I assume this is the right thing to do, but if not, let me know. > > On Wed, Oct 6, 2021 at 3:16 PM Justin Swanhart > wrote: > >> [justin@localhost calcite]$ cat ../calcite.old/test.json >> { >> version: '1.0', >> defaultSchema: 'ssb', >> schemas: [ >> { >> name: 'ssb', >> type: 'custom', >> factory: 'org.apache.calcite.adapter.jdbc.JdbcSchema$Factory', >> operand: { >> jdbcUrl: 'jdbc:mysql://localhost/ssb', >> jdbcUser: 'root', >> jdbcPassword: '' >> } >> } >> ] >> } >> >> On Wed, Oct 6, 2021 at 3:15 PM Justin Swanhart >> wrote: >> >>> Caused by: >>> com.google.common.util.concurrent.UncheckedExecutionException: >>> java.lang.RuntimeException: java.sql.SQLException: Cannot create JDBC >>> driver of class '' for connect URL 'jdbc:mysql://localhost/ssb' >>> >>> On Wed, Oct 6, 2021 at 3:14 PM Julian Hyde >>> wrote: >>> What happens if you remove the line jdbcDriver: 'com.mysql.cj.jdbc.Driver’, from your Calcite model? Hopefully, it just works. Most drivers load automatically these days, and if you don’t specify the class name, Calcite won’t try to load it manually. > On Oct 6, 2021, at 12:07 PM, Justin Swanhart wrote: > > Hi, > > So the example from MySQL is: > [justin@localhost calcite.old]$ cat LoadDriver.java > import java.sql.Connection; > import java.sql.DriverManager; > import java.sq