PDA

View Full Version : Newbie problem with JDBC connection to MySQL



aprice2704
08-11-2003, 01:43 PM
Hi there!
I have Tomcat and Java installed and can run queries against the foodmart db in MySQL from a simple Java program.
Here is my mondrian.properties file (ex copyright notice) which is in <tomcat>/bin:
# Comma-separated list of JDBC drivers
mondrian.jdbcDrivers=com.mysql.jdbc.Driver,sun.jdbc.odbc.JdbcOdbc;
# JDBC connect string used by the test suite.
mondrian.foodmart.jdbcURL=jdbc:mysql://localhost/foodmart?user=hsdev&password=password
# Tracing
mondrian.trace.level=2
# Tomcat
catalina.home=/root/jakarta-tomcat-4.1.24
# Uncomment the following lines to run subsets of the test suite
mondrian.test.Class=mondrian.olap.fun.BuiltinFunTable
mondrian.test.Name=testToggleDrillState
----------- end
When I try to run the query example, with any example I get:
java.lang.NullPointerException
at sun.jdbc.odbc.JdbcOdbcDriver.initialize(JdbcOdbcDriver.java:436)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:153)
at java.sql.DriverManager.getConnection(DriverManager.java:512)
at java.sql.DriverManager.getConnection(DriverManager.java:140)
at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:100)
at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:53)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:122)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:53)
at mondrian.web.servlet.MDXQueryServlet.processRequest(MDXQueryServlet.java:76)
at mondrian.web.servlet.MDXQueryServlet.doGet(MDXQueryServlet.java:200)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2415)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:594)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:392)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:565)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:619)
at java.lang.Thread.run(Thread.java:534)
----------- end
It always seems to want to load the odbc driver even when I remove this entirely from the mondrian.properties. And it never seems to find the correct one.
Any ideas anyone? I am new to all of this and it has taken several days of thrashing around to get this far.
Cheers, and many thanks in advance
=B-) Andy

jhyde
08-11-2003, 02:26 PM
Remove the ';' at the end of the 'mondrian.jdbcDrivers' line. Does that help?

aprice2704
08-11-2003, 04:58 PM
I originally had it without. I'll try it again since I have now changed the order of the two drivers. Oddly Mondrian always seems to load the odbc driver first.
Many thanks for the idea though
=B-)

jhyde
08-11-2003, 09:00 PM
There might be a problem with the '&'. Try '&#38;', for example:
mondrian.foodmart.jdbcURL=jdbc:mysql://localhost/foodmart?user=hsdev&#38;password=password

aprice2704
08-12-2003, 12:10 PM
Thanks again, I did try that, but again without success. I should mention this log from catalina.out:
Starting service Tomcat-Standalone
Apache Tomcat/4.1.24
Aug 12, 2003 2:42:45 PM org.apache.coyote.http11.Http11Protocol start
INFO: Starting Coyote HTTP/1.1 on port 2003
Aug 12, 2003 2:42:45 PM org.apache.jk.common.ChannelSocket init
INFO: JK2: ajp13 listening on /0.0.0.0:8009
Aug 12, 2003 2:42:45 PM org.apache.jk.server.JkMain start
INFO: Jk running ID=0 time=2/426 config=/root/jakarta-tomcat-4.1.24/conf/jk2.properties
Mondrian: properties loaded from 'file:/root/jakarta-tomcat-4.1.24/bin/mondrian.properties'
Mondrian: loaded 0 system properties
Mondrian: JDBC driver sun.jdbc.odbc.JdbcOdbcDriver loaded successfully
Mondrian: Warning: JDBC driver com.mysql.jdbc.Driver not found
Is the problem in the connection url or with some aspect of the connector setup do you think?
btw: here is the little java prog. that works correctly on the same machine:
import java.sql.*;
// a simple java program to test how to access a db
// by using JDBC
public class MyJDBC {
public static void main(String[] args) {
try {
// Load the JDBC driver for MySQL
Class.forName("com.mysql.jdbc.Driver").newInstance();
// Connect to the DB using the above driver
Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/foodmart", "hsdev", "<password>");
System.out.println("Connected");
Statement stmt = con.createStatement();
String query = "SELECT * FROM region WHERE sales_country='Canada'";
ResultSet rs = stmt.executeQuery(query);
while (rs.next()) {
String s = rs.getString("sales_city");
System.out.println(s);
}
}catch( Exception ex)
{
ex.printStackTrace();
System.out.println("Connection failed");
}
}
}
I have also tried with the 3306 in the connection url in mondrian.properties, with the same result on both web stack trace output and catalina.out
Is there another source for debugging info? Where does output indicated by mondrian.trace.level=2 go?
Thanks again for your patience. Mondrian looks so cool, I can't wait to get it going!!
=B-)

jhyde
08-12-2003, 12:22 PM
Ah, that explains it. The 'JDBC driver com.mysql.jdbc.Driver not found' message indicates that you need to put the MySQL driver on the CLASSPATH. One way to do this is to put the mysql JAR file into <tomcat>/common/lib.

aprice2704
08-12-2003, 01:51 PM
Drat! I swear I had that in there at some point, must have removed it in my efforts to fix this.
Now the catalina.out is looking happier:
Starting service Tomcat-Standalone
Apache Tomcat/4.1.24
Aug 12, 2003 5:06:07 PM org.apache.coyote.http11.Http11Protocol start
INFO: Starting Coyote HTTP/1.1 on port 2003
Aug 12, 2003 5:06:07 PM org.apache.jk.common.ChannelSocket init
INFO: JK2: ajp13 listening on /0.0.0.0:8009
Aug 12, 2003 5:06:07 PM org.apache.jk.server.JkMain start
INFO: Jk running ID=0 time=2/180 config=/root/jakarta-tomcat-4.1.24/conf/jk2.properties
Mondrian: properties loaded from 'file:/root/jakarta-tomcat-4.1.24/bin/mondrian.properties'
Mondrian: loaded 0 system properties
Mondrian: JDBC driver sun.jdbc.odbc.JdbcOdbcDriver loaded successfully
Mondrian: JDBC driver com.mysql.jdbc.Driver loaded successfully
Unfortunately I now get this in the MDX sample query:
java.lang.NullPointerException
at sun.jdbc.odbc.JdbcOdbcDriver.initialize(JdbcOdbcDriver.java:436)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:153)
at java.sql.DriverManager.getConnection(DriverManager.java:512)
at java.sql.DriverManager.getConnection(DriverManager.java:140)
at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:100)
at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:53)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:122)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:53)
at mondrian.web.servlet.MDXQueryServlet.processRequest(MDXQueryServlet.java:76)
at mondrian.web.servlet.MDXQueryServlet.doGet(MDXQueryServlet.java:200)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)....
I dunno quite where to look, is there a spot the jdbc driver sends errors?
Cheers,
Andy

jhyde
08-12-2003, 02:20 PM
It's strange that the JDBC-ODBC bridge (sun.jdbc.odbc.JdbcOdbcDriver) appears in the stack trace. This makes me that you are using the wrong connect string. If the connect string looked like 'jdbc:mysql:...' the MySQL driver should be called upon to make the connection.

ebengtso
08-13-2003, 02:09 AM
You can also try:
jdbc\:mysql\://localhost:3306/foodmart?user=hsdev&password=password

aprice2704
08-19-2003, 02:39 PM
Since this last post I have tried a clean install of 1.0 on a windows m/c with the same (exactly) result.
It occurred to me that perhaps the mondrian.war was interfering somehow, so I removed all copies of that, leaving just the unpacked version in webapps.
I can start and stop the app in the Tomcat manager.
I have removed all references to ODBC from the mondrian.properties file and checked that there is no other file called mondrian.properties anywhere on the machine.
I still get:
java.lang.NullPointerException
at sun.jdbc.odbc.JdbcOdbcDriver.initialize(JdbcOdbcDriver.java:436)
at sun.jdbc.odbc.JdbcOdbcDriver.connect(JdbcOdbcDriver.java:153)
at java.sql.DriverManager.getConnection(DriverManager.java:512)
at java.sql.DriverManager.getConnection(DriverManager.java:140)
at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:100)
at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:53)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:122)
at mondrian.olap.DriverManager.getConnection(DriverManager.java:53) ....
I have tried numerous variations on the connect string, without material difference in the result.
catalina.out shows this:
Every 1s: tail --lines=20 catalina.out Tue Aug 19 18:29:29 2003
Aug 19, 2003 6:19:03 PM org.apache.commons.modeler.Registry loadRegistry
INFO: Loading registry information
Aug 19, 2003 6:19:03 PM org.apache.commons.modeler.Registry getRegistry
INFO: Creating new Registry instance
Aug 19, 2003 6:19:04 PM org.apache.commons.modeler.Registry getServer
INFO: Creating MBeanServer
Aug 19, 2003 6:19:05 PM org.apache.coyote.http11.Http11Protocol init
INFO: Initializing Coyote HTTP/1.1 on port 2003
Starting service Tomcat-Standalone
Apache Tomcat/4.1.24
Aug 19, 2003 6:19:12 PM org.apache.coyote.http11.Http11Protocol start
INFO: Starting Coyote HTTP/1.1 on port 2003
Aug 19, 2003 6:19:12 PM org.apache.jk.common.ChannelSocket init
INFO: JK2: ajp13 listening on /0.0.0.0:8009
Aug 19, 2003 6:19:12 PM org.apache.jk.server.JkMain start
INFO: Jk running ID=0 time=1/479 config=/root/jakarta-tomcat-4.1.24/conf/jk2.properties
Mondrian: properties loaded from 'file:/root/jakarta-tomcat-4.1.24/bin/mondrian.properties'
Mondrian: loaded 0 system properties
Mondrian: JDBC driver sun.jdbc.odbc.JdbcOdbcDriver loaded successfully
Mondrian: JDBC driver com.mysql.jdbc.Driver loaded successfully
The connect string and drivers are as shown:
# Comma-separated list of JDBC drivers
mondrian.jdbcDrivers=com.mysql.jdbc.Driver
#,sun.jdbc.odbc.JdbcOdbcDriver
mondrian.trace.level=2
# JDBC connect string used by the test suite.
mondrian.foodmart.jdbcURL=jdbc\:mysql\://localhost:3306/foodmart?user=hsdev\&;password=
As you can see, the ODBC driver is commented out. Mangling the mysql driver spec causes an error to show in catalina.out, so it does seem to be using the mondrian.properties, but always loads the sun ODBC driver first.
I'm quite at my wits end. After beating my head against this for a couple of weeks I'm exactly where I started really.
Any help much appreciated, congrats on release 1.0!!
Is there any way to get more debugging info out?
Cheers,
Andy

jhyde
08-19-2003, 09:17 PM
Comment out the mondrian.jdbcDrivers and mondrian.foodmart.jdbcURL properties, and set the mondrian.test.connectString property, as follows (all one line):
mondrian.test.connectString=Provider=mondrian;Jdbc=jdbc:mysql://localhost:3306/foodmart;JdbcUser=hsdev;JdbcPassword=;Catalog=/WEB-INF/queries/FoodMart.xml;JdbcDrivers=com.mysql.jdbc.Driver
Let me know where this gets you. If this doesn't work, at the very least, I hope you'll get different error messages!

aprice2704
08-22-2003, 11:16 AM
Mostly the same :(
This is mondrian 0.6 btw.
catalina.out now shows this:
Starting service Tomcat-Standalone
Apache Tomcat/4.1.24
Aug 22, 2003 3:02:43 PM org.apache.coyote.http11.Http11Protocol start
INFO: Starting Coyote HTTP/1.1 on port 2003
Aug 22, 2003 3:02:43 PM org.apache.jk.common.ChannelSocket init
INFO: JK2: ajp13 listening on /0.0.0.0:8009
Aug 22, 2003 3:02:43 PM org.apache.jk.server.JkMain start
INFO: Jk running ID=0 time=5/189 config=/root/jakarta-tomcat-4.1.24/conf/jk2.properties
Mondrian: properties loaded from 'file:/root/jakarta-tomcat-4.1.24/bin/mondrian.properties'
Mondrian: loaded 0 system properties
Mondrian: JDBC driver sun.jdbc.odbc.JdbcOdbcDriver loaded successfully
Mondrian: Warning: JDBC driver org.hsqldb.jdbcDriver not found
Mondrian: Warning: JDBC driver oracle.jdbc.OracleDriver not found
Mondrian: JDBC driver com.mysql.jdbc.Driver loaded successfully
I get exactly the error on the sample query web page though.
Here is the mondrian.properties:
# Comma-separated list of JDBC drivers
# mondrian.jdbcDrivers=com.mysql.jdbc.Driver
#,sun.jdbc.odbc.JdbcOdbcDriver
mondrian.trace.level=2
# JDBC connect string used by the test suite.
#mondrian.foodmart.jdbcURL=jdbc\:mysql\://localhost:3306/foodmart?user=hsdev\&;password=<password>
mondrian.test.connectString=Provider=mondrian;Jdbc=jdbc:mysql://localhost:3306/f
oodmart;JdbcUser=hsdev;JdbcPassword=<password>;Catalog=/WEB-INF/queries/FoodMart.xml;JdbcD
rivers=com.mysql.jdbc.Driver
# Tomcat
catalina.home=/root/jakarta-tomcat-4.1.24
I hope that helps. Should I give in and try accessing via ODBC? It seems very determined to use it.
Thx again
Andy =B-)

jhyde
08-22-2003, 11:33 AM
I don't know. I give up. Mondrian is just a Java program trying to access MySQL via its JDBC driver, so provided (a) the driver is on the CLASSPATh, (b) the driver is registered, (c) the correct JDBC connect string is being issued, it should just work. I can't say more than that.
The remaining options are try debugging it; try writing a test program which connects to MySQL via the driver; or just revert to ODBC.
Sorry I can't be more help.

chptma
09-09-2003, 12:13 PM
Hi, trying to trow some light in the problem (I have the same)....
In the code I suspect about some lines with things that seems to me like hard code pointig to a jdbc:odbc:...
I'm not solving the problem I just try to delimit this...
src/main/mondrian/jolap/MondrianJolapConnectionFactory.java this lines seems to me like a default...
propertyList.put("Password", properties.getPassword());
propertyList.put("Jdbc", "jdbc:odbc:MondrianFoodMart");
propertyList.put("Catalog", "
file:///e:/mondrian/demo/FoodMart.xml
");
Twice..
src/main/mondrian/rolap/Test.java
try {
Class.forName("com.ms.jdbc.odbc.JdbcOdbcDriver");
} catch (ClassNotFoundException e) {
...
String connectString = "jdbc:odbc:DSN=FoodMart2";
src/main/mondrian/xmla/XmlaTest.java
private static final String dataSource = "Provider=Mondrian;Jdbc=jdbc:odbc:MondrianFoodMart;Catalog=file:/E:/mondrian/demo/FoodMart.xml;JdbcDrivers=sun.jdbc.odbc.JdbcOdbcDriver;";
Time to time some references to E:\ but I'm not sure about nothing
Greetings...