PDA

View Full Version : Big Problem with MySQL db



mamo
04-07-2006, 07:31 AM
Hi all,
I've read all the post on this forum and all the documentation but I have great problem to connect pentaho to MySQL datasource, I've read that there are some files to modify so I did it and I report them now...




under \\jboss\\server\\default\\deploy I have the file

===============================================
xmysqlds-ds.xml and this file contains




XMySqlDS
jdbc:mysql://localhost:3306/test2
com.mysql.jdbc.Driver
USERNAME
PASSWORD



===============================================

under \\jboss\\server\\default\\deploy\\pentaho.war\\WEB-INF I have 2 files, these are :

web.xml whick contains









org.apache.jsp.index_jsp
org.apache.jsp.index_jsp



org.apache.jsp.testpage_jsp
org.apache.jsp.testpage_jsp



org.apache.jsp.WEB_002dINF.queries.arrows_jsp
org.apache.jsp.WEB_002dINF.queries.arrows_jsp



org.apache.jsp.WEB_002dINF.queries.colors_jsp
org.apache.jsp.WEB_002dINF.queries.colors_jsp



org.apache.jsp.WEB_002dINF.queries.fourhier_jsp
org.apache.jsp.WEB_002dINF.queries.fourhier_jsp



org.apache.jsp.WEB_002dINF.queries.mondrian_jsp
org.apache.jsp.WEB_002dINF.queries.mondrian_jsp



org.apache.jsp.WEB_002dINF.queries.testquery_jsp
org.apache.jsp.WEB_002dINF.queries.testquery_jsp



org.apache.jsp.wcf.showxml_jsp
org.apache.jsp.wcf.showxml_jsp



org.apache.jsp.jsp.Chart_jsp
org.apache.jsp.jsp.Chart_jsp



org.apache.jsp.jsp.Navigate_jsp
org.apache.jsp.jsp.Navigate_jsp



org.apache.jsp.jsp.PieChart_jsp
org.apache.jsp.jsp.PieChart_jsp



org.apache.jsp.jsp.Pivot_jsp
org.apache.jsp.jsp.Pivot_jsp



org.apache.jsp.jsp.PivotBusy_jsp
org.apache.jsp.jsp.PivotBusy_jsp



org.apache.jsp.jsp.PivotError_jsp
org.apache.jsp.jsp.PivotError_jsp



org.apache.jsp.jsp.Publish_jsp
org.apache.jsp.jsp.Publish_jsp



org.apache.jsp.jsp.SchedulerAdmin_jsp
org.apache.jsp.jsp.SchedulerAdmin_jsp



org.apache.jsp.jsp.Task_jsp
org.apache.jsp.jsp.Task_jsp



org.apache.jsp.jsp.TestSuite_jsp
org.apache.jsp.jsp.TestSuite_jsp



org.apache.jsp.jsp.UpdateSampleEmails_jsp
org.apache.jsp.jsp.UpdateSampleEmails_jsp



org.apache.jsp.jsp.Widgets_jsp
org.apache.jsp.jsp.Widgets_jsp



org.apache.jsp.index_jsp
/index.jsp



org.apache.jsp.testpage_jsp
/testpage.jsp



org.apache.jsp.WEB_002dINF.queries.arrows_jsp
/WEB-INF/queries/arrows.jsp



org.apache.jsp.WEB_002dINF.queries.colors_jsp
/WEB-INF/queries/colors.jsp



org.apache.jsp.WEB_002dINF.queries.fourhier_jsp
/WEB-INF/queries/fourhier.jsp



org.apache.jsp.WEB_002dINF.queries.mondrian_jsp
/WEB-INF/queries/mondrian.jsp



org.apache.jsp.WEB_002dINF.queries.testquery_jsp
/WEB-INF/queries/testquery.jsp



org.apache.jsp.wcf.showxml_jsp
/wcf/showxml.jsp



org.apache.jsp.jsp.Chart_jsp
/jsp/Chart.jsp



org.apache.jsp.jsp.Navigate_jsp
/jsp/Navigate.jsp



org.apache.jsp.jsp.PieChart_jsp
/jsp/PieChart.jsp



org.apache.jsp.jsp.Pivot_jsp
/jsp/Pivot.jsp



org.apache.jsp.jsp.PivotBusy_jsp
/jsp/PivotBusy.jsp



org.apache.jsp.jsp.PivotError_jsp
/jsp/PivotError.jsp



org.apache.jsp.jsp.Publish_jsp
/jsp/Publish.jsp



org.apache.jsp.jsp.SchedulerAdmin_jsp
/jsp/SchedulerAdmin.jsp



org.apache.jsp.jsp.Task_jsp
/jsp/Task.jsp



org.apache.jsp.jsp.TestSuite_jsp
/jsp/TestSuite.jsp



org.apache.jsp.jsp.UpdateSampleEmails_jsp
/jsp/UpdateSampleEmails.jsp



org.apache.jsp.jsp.Widgets_jsp
/jsp/Widgets.jsp






solution-path




base-url
http://localhost:8080/pentaho/



locale-language





locale-country









com.tonbeller.wcf.controller.RequestContextFactory
com.tonbeller.wcf.controller.RequestContextFactoryImpl



JPivotController
com.tonbeller.wcf.controller.RequestFilter

errorJSP
PivotError
URI of error page


busyJSP
PivotBusy
This page is displayed if a the user clicks
on a query before the previous query has finished




JPivotController
/Pivot




com.tonbeller.tbutils.res.ResourcesFactoryContextListener



org.pentaho.system.SolutionContextListener



org.pentaho.session.PentahoHttpSessionListener





DisplayChart
org.jfree.chart.servlet.DisplayChart



GetChart
GetChart
Default configuration created for servlet.
com.tonbeller.jpivot.chart.GetChart


Print
Print
Default configuration created for servlet.
com.tonbeller.jpivot.print.PrintServlet




ViewAction
org.pentaho.ui.servlet.ViewAction



content
org.pentaho.ui.servlet.UIServlet



ServiceAction
org.pentaho.ui.servlet.HttpWebService



GetImage
org.pentaho.ui.servlet.GetImage



GetMondrianModel
org.pentaho.ui.servlet.GetMondrianModel



ShowTestResult
org.pentaho.ui.servlet.ShowTestResult



Pivot
/jsp/Pivot.jsp



Chart
/jsp/Chart.jsp


Navigate
/jsp/Navigate.jsp


PieChart
/jsp/PieChart.jsp


Publish
/jsp/Publish.jsp


Task
/jsp/Task.jsp


SchedulerAdmin
/jsp/SchedulerAdmin.jsp


TestSuite
/jsp/TestSuite.jsp


UpdateSampleEmails
/jsp/UpdateSampleEmails.jsp


Widgets
/jsp/Widgets.jsp



PivotError
/jsp/PivotError.jsp



PivotBusy
/jsp/PivotBusy.jsp




DisplayChart
/DisplayChart


Print
/Print


GetChart
/GetChart





Chart
/Chart


Navigate
/Navigate


PieChart
/PieChart


Publish
/Publish


SchedulerAdmin
/SchedulerAdmin


Task
/Task


TestSuite
/TestSuite


UpdateSampleEmails
/UpdateSampleEmails


Widgets
/Widgets





ViewAction
/ViewAction



content
/content



ServiceAction
/ServiceAction



GetImage
/getImage



GetMondrianModel
/GetMondrianModel



ShowTestResult
/ShowTestResult



Pivot
/Pivot



PivotError
/PivotError



PivotBusy
/PivotBusy



30



index.jsp


http://www.tonbeller.com/wcf
/WEB-INF/wcf/wcf-tags.tld



http://www.tonbeller.com/jpivot
/WEB-INF/jpivot/jpivot-tags.tld



SampleData Connection
jdbc/SampleData
javax.sql.DataSource
Container



Quartz Connection
jdbc/Quartz
javax.sql.DataSource
Container



Shark Connection
jdbc/Shark
javax.sql.DataSource
Container



XMySqlDs Connection
jdbc/XMySqlDS
javax.sql.DataSource
Container




===============================================

and jboss-web.xml which contains









jdbc/SampleData
javax.sql.DataSource
java:/SampleData



jdbc/Quartz
javax.sql.DataSource
java:/Quartz



jdbc/Shark
javax.sql.DataSource
java:/Shark



jdbc/XMySqlDS
javax.sql.DataSource
java:/XMySqlDS



===============================================

My database name is test2 and it has only a field called ville and on this tableI have only a field called Addresse, and here I have 2 raw whicj are the name of the 2 street

and this is SQL_Datasource.xaction I have this



Copy of Copy of Copy of HelloWorld.xaction
6. le texte c'est bien, les base de données c'est mieux
1
DEBUG

James Dixon
Faut que ca marche

rule
Copy of Copy of Copy of HelloWorld.png



Product Development




list











org.pentaho.component.SQLLookupRule
rule

XMySqlDS





==============================================

My targe is to extract the name of the 2 street when I launch SQL_Datasource.xaction but I receive this error

Errore: ConnectFactory.ERROR_0001 - La connessione al database non può essere stabilita a: XMySqlDS (org.pentaho.component.SQLLookupRule)
Errore: SQLBaseComponent.ERROR_0006 - Non è possibile eseguire SQL_Datasource.xaction (org.pentaho.component.SQLLookupRule)
Errore: RuntimeContext.ERROR_0012 - LActionDefinition per {0} non è stata eseguita con successo (org.pentaho.runtime.RuntimeContext)
Errore: SolutionEngine.ERROR_0007 - Esecuzione dell'Action Sequence fallita (org.pentaho.solution.SolutionEngine)


WHAT CAN I DO TO RESOLVE THIS PROBLEM????

thanks a lot

mbatchelor
04-07-2006, 08:21 AM
Hi there,

Typically problems like these stem from one of the following:

1- Case issues. In Java, case is extremely important. com.mysql.jdbc.driver is not the same thing as com.mysql.jdbc.Driver. Also, a JNDI name of MySqlDS is not the same thing as a JNDI name of MySQLDS. If one file references MySqlDS, and another file uses MySQLDS, you are certain to have a failure. This happens about 40% of the time.

2- Following directions. By this, I mean not including the resource definition in the jboss-web.xml, not including the resource reference in the web.xml, not copying the jdbc driver into the correct place, etc. In this case, carefully following directions in the Advanced Installation Guide will take care of the problem. This happens about 40% of the time.

3- Port conflicts/RDBMS setup problems. By this, I mean that either the database is not configured at all to listen to TCP requests, the RDBMS server isn't even running, is not installed on the default port but the JDBC driver reference isn't specifying the port, or the port the server is listening on conflicts with some other running program. This happens about 18% of the time.

4- We have a genuine bug. By this, I mean that you've done everything correctly, and your stuff flat out doesn't work because we've neglected something, or just coded it wrong. This happens about 2% of the time.

If you can spend time examing your configuration, you may find out that your case falls into one of the first three categories. If, after you've checked, and double-checked, you still can't get things to work, then please post a .zip file to the forum that contains the following files and we'll have a look.

a- Your action sequence (xxxx.xaction)
b- Your datasource definition (xxxx-ds.xml)
c- Your web.xml
d- Your jboss-web.xml
e- Your server error log


I hope this helps,

Marc

mamo
04-07-2006, 11:15 AM
Thanks for your reply, what can I say? The mysql is running in correct way con standar port, I've read all the file which I post in the attachment and I think that there are no problems infact I have set these files reading other post and the auctor of this post says that all work well....

I can find the error, I hope in your answer for a solution
Thanks a lot http://forums.pentaho.org/archived_att/files/all_files.zip

mbatchelor
04-07-2006, 05:01 PM
What can I say. It seems to be #2 on my previous list.

Here's the deal. Have a look at the log file, and this is what I see:

org.jboss.util.NestedSQLException:
Could not create connection; - nested throwable:
(org.jboss.resource.JBossResourceException: Failed to register driver for: com.mysql.jdbc.Driver; -
nested throwable: (java.lang.ClassNotFoundException: com.mysql.jdbc.Driver)); -
nested throwable: (org.jboss.resource.JBossResourceException: Could not create connection; -
nested throwable: (org.jboss.resource.JBossResourceException: Failed to register driver for: com.mysql.jdbc.Driver; -
nested throwable: (java.lang.ClassNotFoundException: com.mysql.jdbc.Driver)))


The problem is that it cannot locate your JDBC driver. This goes back to the JDBC driver .jar file not being placed in the correct place in JBoss. Did you copy the mysql-connector-java-xxxx-bin.jar to the correct directory in JBoss?

Please consult the solutions guide to get the correct destination for the JDBC driver under JBoss.

Take care,

Marc

mamo
04-08-2006, 03:05 AM
Excuse for my questions but where can I find the mysql-connector-java-xxxx-bin.jar ? Can I download them? If yes from where?

Now I go to read what you suggested me...

thanks a lot

mbatchelor
04-08-2006, 04:31 AM
If you go to http://www.mysql.com/products/connector/ and click on Connector/J that will take you to the place that you can download the JDBC driver for mysql.

Take care,

Marc

mamo
04-08-2006, 08:42 AM
Now it's all PERFECT I've downloaded the connector, later
I put them in the correct path and at the end I can see the data on the field of my db....THANKS A LOT

Now plese not kill me, but I have another question on the same argument.

I want to create a cube from my sql database and I want to have a result like the Slice and Dice under Analysis Examples. To do this I' ve created 2 files.

these are:

test2.mondrian.xml
and provaconnessione.xaction


my MySQL databse has this structure under the table ville and the neme of the db is test2
+-------------------------+---------+----------+--------+
| Adresse | settore|numero | valore |
+-------------------------+---------+----------+--------+
| laviosa |1 |1 |1 |
| ariosto |2 |1 |3 |
| montenaolpeo |1 |2 |3 |
| cippa |2 |2 |4 |
| lippa |0 |0 |0 |
+--------------+---------+--------+--------+

when I try to launch provaconnessione.xaction I obtain this error
JPivot Error ...
An error happened servicing a JPivot request. Please see the server console for more details.

I attach the files..

Have you another great idea to resolve my problem? THANKS A LOT http://forums.pentaho.org/archived_att/files/files.zip

Post edited by: mamo, at: 04/08/2006 12:43

mbatchelor
04-08-2006, 11:12 AM
Well, without knowing what JPivot was complaining about, you'd probably get better feedback trying your MDX query just using an MDX lookup rule. In that way, you can test your model and query before introducing JPivot into the mix.

Check out the samples/datasources/MDX_Datasource.xaction. This will clear some trees out of the way.

Offhand, looking at your data, and your model, it doesn't seem that your model at all supports the data you showed in the post.

There is no reference to Adresse (which is your only string column that could possibly be a dimension). You've used numeric columns as levels in a dimension (which doesn't make any sense).

I suggest you look at the following for assistance:

http://mondrian.sourceforge.net/olap.html
http://mondrian.sourceforge.net/mdx.html
http://mondrian.sourceforge.net/schema.html
http://mondrian.sourceforge.net/aggregate_tables.html

mamo
04-08-2006, 11:45 AM
perfect, I'll do

espel
06-02-2006, 06:00 AM
Hello:
I've been fighting with the Mysql connection a long time and i cant make it work. Maybe you could help :S
I read the forum and the "Creatoing pentaho solution" documents. bu ti still cant make it work.

I downloaded the mysql JDBC driver from http://www.mysql.com/products/connector/
I move the mysql-connector-java-3.1.13-bin.jar to {pci}/jboss/server/default/lib directory

I did a "ps" and mysqld is running, i tested the conection with telnet localhost 3306 and it works too.

I'm sending the requested files so you can take a look.

I'm running pentaho-demo over a red hat 9.

When i try to connect to pentaho i get a
HTTP Status 404 - /pentaho/Navigate
message from tomcat

But it used to work fine after the modification of the files to do a JNDI.

Thank!

PD: If a missed details please let me know and i'll post them immediatly. http://forums.pentaho.org/archived_att/files/all.zip

Post edited by: espel, at: 06/02/2006 10:01

Post edited by: espel, at: 06/02/2006 10:38

espel
06-02-2006, 07:00 AM
I apollogize, i found myself an error on jboss-web.xml

But now i got that other error:

Error: ConnectFactory.ERROR_0001 - Database connection could not be established to: MySqlDS (org.pentaho.component.SQLLookupRule)

I post my updated config files again

Thanks ! http://forums.pentaho.org/archived_att/files/all1.zip

Post edited by: espel, at: 06/02/2006 11:02

mdamour
06-02-2006, 07:21 AM
It looks like your config is good. The reason that this is failing is that there is a dependency on aspectj which is used for pooling. I'd find the jar that has the aspectj classes and once your java.lang.NoClassDefFoundError: org/aspectj/lang/Signature goes away, I think you'll find that your connections will work.

Hope this helps-
Mike

espel
06-02-2006, 08:58 AM
Hello:
Ok I will work on that.
Thanks for such a quick reply !

espel
06-02-2006, 09:31 AM
Hello:
I've tried this:

java -cp /usr/local/mysql-connector-java-3.1.13/src/lib/aspectjrt.jar /usr/local/mysql-connector-java-3.1.13/mysql-connector-java-3.1.13-bin.jar

export CLASSPATH=/usr/local/mysql-connector-java-3.1.13/mysql-connector-java-3.1.13-bin.jar:/usr/local/mysql-connector-java-3.1.13/src/lib/aspectjrt.jar

And i got that:

Exception in thread "main" java.lang.NoClassDefFoundError: /usr/local/mysql-connector-java-3/1/13/mysql-connector-java-3/1/13-bin/jar


I read the documentation that comes with connector/j and that's all i could do :S.

I know that mysql-connector is not the topic in this forum but maybe you understand what happening here.

Thanks !

mdamour
06-02-2006, 10:38 AM
The first line:

java -cp /usr/local/mysql-connector-java-3.1.13/src/lib/aspectjrt.jar
/usr/local/mysql-connector-java-3.1.13/mysql-connector-java-3.1.13-bin.jar
does not reference any class to startup. It looks like there is possibly a space between the jars on the -cp for java, should be separated with your platform specific separator (colon on unix).

I'm not sure why you're modifying these paths, or to what you're modifying the paths on.

If you're running this stuff in JBoss, just drop both jars into the jboss default/lib and it should be happy.

Hope this helps-
Mike

espel
06-03-2006, 10:36 AM
Hello:

that's what i did. I dropped both jars into jboss default/lib but i get the same error :(.

Thanks !

espel
06-05-2006, 04:19 AM
Hello:
Now im getting that other error.

08:54:12,235 ERROR [URLDeploymentScanner] Incomplete Deployment listing:

--- Incompletely deployed packages ---
org.jboss.deployment.DeploymentInfo@7d94f205 { url=file:/opt/pentaho-demo/jboss/server/default/deploy/mysql-ds.xml }
deployer: org.jboss.deployment.XSLSubDeployer@9722c9
status: Deployment FAILED reason: Trying to install an already registered mbean: jboss.jca:service=LocalTxCM,name=MySqlDS
state: FAILED
watch: file:/opt/pentaho-demo/jboss/server/default/deploy/mysql-ds.xml
altDD: null
lastDeployed: 1149508438678
lastModified: 1149508438000
mbeans:

Thanks ! http://forums.pentaho.org/archived_att/files/all2.zip

espel
06-05-2006, 04:50 AM
Hello:
Now im getting that other error.

08:54:12,235 ERROR [URLDeploymentScanner] Incomplete Deployment listing:

--- Incompletely deployed packages ---
org.jboss.deployment.DeploymentInfo@7d94f205 { url=file:/opt/pentaho-demo/jboss/server/default/deploy/mysql-ds.xml }
deployer: org.jboss.deployment.XSLSubDeployer@9722c9
status: Deployment FAILED reason: Trying to install an already registered mbean: jboss.jca:service=LocalTxCM,name=MySqlDS
state: FAILED
watch: file:/opt/pentaho-demo/jboss/server/default/deploy/mysql-ds.xml
altDD: null
lastDeployed: 1149508438678
lastModified: 1149508438000
mbeans:

Thanks !

mdamour
06-05-2006, 06:50 AM
How many *-ds.xml files do you have containing the name MySqlDS? You can only have one.. I'm suspecting this to be a possible problem.

Let me know if that helps at all.
-Mike

espel
06-05-2006, 07:22 AM
Hello:
It did help :) know i have the same porblem than before.

Error: ConnectFactory.ERROR_0001 - Database connection could not
be established to: MySqlDS (org.pentaho.component.SQLLookupRule)

I'm sending the files again.

I think is something wrong with the driver :(

Thanks ! http://forums.pentaho.org/archived_att/files/all3.zip

mdamour
06-05-2006, 07:39 AM
Is mysql running on your machine (localhost)? It appears as though it your JBoss is configured correctly now, the drivers are being loaded and the -ds.xml file is being used to map JNDI but it cannot connect to mysql. Be sure your mysql is running and/or configured to allow remote connections.

-Mike

espel
06-06-2006, 03:31 AM
Hello:
This is the situation: Pentaho is installed in the server. that's why i put localhost, that server is running web apps and they connect to mysql.
anyway i've made some test to assure that's running and i've raplaced localhost with 127.0.0.1 just to be sure:

First I checked for running service:

[root@barney pentaho-demo]# ps -A | grep mysql
2998 ? 00:00:00 mysqld_safe
3034 ? 00:00:04 mysqld
3041 ? 00:00:25 mysqld
3042 ? 00:00:00 mysqld
3043 ? 00:00:00 mysqld
3044 ? 00:00:00 mysqld
3045 ? 00:00:00 mysqld
3056 ? 00:08:16 mysqld
3057 ? 00:06:12 mysqld
3058 ? 00:00:00 mysqld
3059 ? 00:00:08 mysqld

And then I checked connection:

[root@barney pentaho-demo]# telnet 127.0.0.1 3306
Trying 127.0.0.1...
Connected to barney.espel.com (127.0.0.1).
Escape character is '^]'.
4
4.0.20-standard-log
ÃEN7g$xU|,Connection closed by foreign host.

Maybe you know another test to do or something else.

I send you the files again and the new log server.

Thanks ! http://forums.pentaho.org/archived_att/files/all4.zip

Post edited by: espel, at: 06/06/2006 07:34

mdamour
06-06-2006, 06:19 AM
Ok, well now it looks like your database is running. It was definitely NOT running based on the previous log. The error you are getting now is directly from mysql, so your drivers are installed correctly and you are connecting to mysql now.

The issue is that you are trying to connect to mysql as the root user and this is not permitted. You're going to have to open up remote root access to your mysql or create another user with remote access.

-Mike

espel
06-06-2006, 06:58 AM
Hello:

I created a new user in the mysql server, updated the mysql-ds.xml file with the new data.
And this is my new error :).

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception

java.lang.NullPointerException
org.pentaho.util.UIUtil.formatSuccessMessage(Unknown Source)
org.pentaho.ui.servlet.ViewAction.doGet(Unknown Source)
javax.servlet.http.HttpServlet.service(HttpServlet.java:697)
javax.servlet.http.HttpServlet.service(HttpServlet.java:810)
org.jboss.web.tomcat.filters.ReplyHeaderFilter.doFilter(Unknown Source)

note The full stack trace of the root cause is available in the Apache Tomcat/5.5.9 logs.

Thanks ! http://forums.pentaho.org/archived_att/files/all5.zip

mbatchelor
06-06-2006, 07:12 AM
When you created the new user, were you certain to grant that user access to the database you're playing with?

Something like:



GRANT ALL PRIVILEGES ON theDB.* TO 'new_user'@'localhost'
IDENTIFIED BY 'userPassword';

GRANT ALL PRIVILEGES ON theDB.* TO 'new_user'@'%'
IDENTIFIED BY 'userPassword';



The first line grants access to the user operating on the same machine as the mysql database. The second line grants access to the user without regard to what machine they're coming from. This is known as creating remote access for the user.

Please see the mysql documentation for more details.

Marc

espel
06-06-2006, 07:25 AM
Hello:
I grant the acces with mysqladministrator :S. i dont know if that's enought. anyway i will try as u said. and i will tell you the details

Now im trying to connect to another database (informix) and happend almost the same. I tryed to acces that db with Dbvisulizer which has JDBC driver and I could connect to it. So I think i will be much easier to debug.

Thanks http://forums.pentaho.org/archived_att/files/all6.zip