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 <greenl...@gmail.com> 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 <greenl...@gmail.com>
> 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 <greenl...@gmail.com>
>> 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 <jhyde.apa...@gmail.com>
>>> 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 <greenl...@gmail.com>
>>>> 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 <greenl...@gmail.com>
>>>> 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 <jhyde.apa...@gmail.com>
>>>> 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 <greenl...@gmail.com>
>>>> >>> 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 <jhyde.apa...@gmail.com
>>>> >
>>>> >>> 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 <
>>>> greenl...@gmail.com>
>>>> >>>>> 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.<init>(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$FunctionToCacheLoader.load(CacheLoader.java:165)
>>>> >>>>>> at
>>>> >>>>>>
>>>> >>>>>
>>>> >>>
>>>> com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3529)
>>>> >>>>>> at
>>>> >>>>>
>>>> >>>
>>>> com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2278)
>>>> >>>>>> at
>>>> >>>>>>
>>>> >>>>>
>>>> >>>
>>>> com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2155)
>>>> >>>>>> at
>>>> >>> com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2045)
>>>> >>>>>> ... 29 more
>>>> >>>>>> Caused by: java.sql.SQLException: Cannot load JDBC driver class
>>>> >>>>>> 'com.mysql.cj.jdbc.Driver'
>>>> >>>>>> at
>>>> >>>>>>
>>>> >>>>>
>>>> >>>
>>>> org.apache.commons.dbcp2.BasicDataSource.createConnectionFactory(BasicDataSource.java:489)
>>>> >>>>>> at
>>>> >>>>>>
>>>> >>>>>
>>>> >>>
>>>> org.apache.commons.dbcp2.BasicDataSource.createDataSource(BasicDataSource.java:599)
>>>> >>>>>> at
>>>> >>>>>>
>>>> >>>>>
>>>> >>>
>>>> org.apache.commons.dbcp2.BasicDataSource.getConnection(BasicDataSource.java:809)
>>>> >>>>>> at
>>>> >>>>>>
>>>> >>>>>
>>>> >>>
>>>> org.apache.calcite.adapter.jdbc.JdbcUtils$DialectPool.dialect(JdbcUtils.java:94)
>>>> >>>>>> ... 34 more
>>>> >>>>>> Caused by: java.lang.ClassNotFoundException:
>>>> com.mysql.cj.jdbc.Driver
>>>> >>>>>> at
>>>> >>>>>>
>>>> >>>>>
>>>> >>>
>>>> java.base/jdk.internal.loader.BuiltinClassLoader.loadClass(BuiltinClassLoader.java:581)
>>>> >>>>>> at
>>>> >>>>>>
>>>> >>>>>
>>>> >>>
>>>> java.base/jdk.internal.loader.ClassLoaders$AppClassLoader.loadClass(ClassLoaders.java:178)
>>>> >>>>>> at
>>>> java.base/java.lang.ClassLoader.loadClass(ClassLoader.java:522)
>>>> >>>>>> at
>>>> >>>>>>
>>>> >>>>>
>>>> >>>
>>>> org.apache.commons.dbcp2.BasicDataSource.createConnectionFactory(BasicDataSource.java:483)
>>>> >>>>>> ... 37 more
>>>> >>>>>
>>>> >>>>>
>>>> >>>
>>>> >>>
>>>>
>>>>
