Ubuntu 12.04
MySQL DB 5.5.28
MariaDB JDBC driver 1.0.0
JDK 1.6.0_26
Crystal Report Runtime API

Are you sure did not mix anything there? The stacktrace you refer to, is from earlier unreleased version of the driver, as indicated by presence of "org.skysql" packages. the MariaDB-JDBC has "org.mariadb" packages

I'm not able to reproduce with the below test, using the 1.0.0 MariaDB-JDBC driver? I created database statistic and table call with int call_id in it prior to the test.

Connection c = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root","");
c.createStatement().executeQuery("SELECT `call`.`call_id` FROM `statistic`.`call` `call`");

The 1.0.0 version of MariaDB JDBC can be downloaded from https://downloads.mariadb.org/client-java/1.0.0/

I'm sorry - cut, copy, paste problem.

Here is a brand new stack-trace:

Caused by: java.sql.SQLSyntaxErrorException: 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 '.`call` `call`' at line 1
at org.mariadb.jdbc.internal.SQLExceptionMapper.get(SQLExceptionMapper.java:145)
at org.mariadb.jdbc.internal.SQLExceptionMapper.throwException(SQLExceptionMapper.java:110)
at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:236)
at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:288)
... 65 more
Caused by: org.mariadb.jdbc.internal.common.QueryException: 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 '.`call` `call`' at line 1
at org.mariadb.jdbc.internal.mysql.MySQLProtocol.getResult(MySQLProtocol.java:726)
at org.mariadb.jdbc.internal.mysql.MySQLProtocol.executeQuery(MySQLProtocol.java:777)
at org.mariadb.jdbc.MySQLStatement.execute(MySQLStatement.java:228)
... 66 more

Thanks Heiko. However I still have difficulty reproducing the problem - c.createStatement().executeQuery("SELECT `call`.`call_id` FROM `statistic`.`call` `call`")
works flawlessly for me (and I think it should for you,too)

Can you create a similarly small example to illustrate the problem?

Hello,

I made a lot of test and you are right.

With a simple test application it works. But the same SQL string generated by Crystal Report or injected during runtime did not work.
We always see the SQLSyntaxException.

We are using log4j, is it possible to enable more detailed logging from MariaDB client library?

Thanks and best regards,
Heiko Tropartz

More detailed logging is not possible at the moment in the 1.0.0

I would be very helpful to enable general log (SET GLOBAL general_log = 'ON' , with powerful user account, and switching it OFF after the end of the experiment) to see the queries as they come on the server side.

While this still might be insufficient to analyze the error (e.g with prepared statements, or query could have changed after normalization with nativeSQL), but it might give some clue. It is probably worth checking if CrystalReports has itself any kind of JDBC tracing

If none of the above is not possible on some reason, just tell, and I'll build a custom jar that dumps more info on syntax exceptions.

Hello,

after analyzing a huge amount of logs, I found 2 differences between the MySQL J/Connector and the MariaDB client library.

1. When the Crystal Report engine tries to access the database via the MariaDB library, I found the following log message several times:

org.mariadb.jdbc.CommonDatabaseMetaData getUDTs
INFO: getting empty result set, get UTDs

2. The Crystal Report engine creates different SQL queries.

MariaDB: SELECT `new_table`.`test` FROM `def`.`test`.`new_table` `new_table`
MySQL: SELECT `new_table`.`test` FROM `test`.`new_table` `new_table`

I don't know if the 'def' in front of 'test'.'new_table' has something to do with the empty ResultSet of getUTDs, but these are the only differences I found.
But why is ResultSet of "getUTDs" empty?

I think the 'def' string is wrong and that is the reason for the SQLSyntaxException from the MariaDB client library.

Good, you second hint seems correct . The query indeed throws syntax error.
So, my current theory is that the 'def' in the second query is generated by one of the DatabaseMetaData methods, that query information_schema, which indeed has a single catalog named "def" (which is something nobody knows anymore what is good for). The handling of JDBC catalogs vs schemas in the first released version had several bugs that were fixed for the next release (they are tracked in CONJ-10) recently, so I believe your problem would disappear with the next release.

However I would certainly appreciate if you can test the JDBC dirver before the release.

For your convenience, I built the from the latest source, and uploaded to my skydrive : https://skydrive.live.com/#cid=FF0C950417B4F8A4&id=FF0C950417B4F8A4%21110
Of course, you may also build youself, using bzr and maven, directly from launchpad , as described here : https://kb.askmonty.org/en/mariadb-jdbc-driver/

Very good, the version from your skydrive works.
I will do more tests in the next days, but I think this issue is solved with CONJ-10.

Thank you very much for your help.