Failed connection on MySQL after deploy on teiid-embedded-6.
luca_gioppo May 20, 2009 5:01 AMThis is a followup from this thread http://www.jboss.org/index.html?module=bb&op=posting&mode=newtopic&f=298
Here is the probem:
Created a new vbd based on a couple of tables from MySQL just to try to see all the stuff in iReport.
Here are the steps done:
[list=]open the Designer perspective
[list=]Create a new Model Project
[list=]Go to the UDF tab and add all the jars you'll use for JDBC (if you plan to use Oracle or MySQL for example)
[list=]Then import from your DB the model using import -> Metadata Modeling -> Metadata from jdbc database
[list=]Create a new source You will have to create a new driver configuration and you will have to specify again the path of the jdbc driver
insert connection data and test the connection
[list=]Now you have the phisical model it's time to create a view so:
new "metadata model" transform from existing view, model type = view model
[list=]choose the existing model
[list=]now create the VDB from the view
[list=]add all the models you need
[list=]than export it as file to the deploy folder of the embedded-teiid installation
Now we junp into iReport
Create the connection that test correctly.
Issue a query to get the fields and create the report and here is the error/surprise (that comes from teiid since is an extract from the log that I write below:
Session mag 20, 2009 10:35:09.719 ---------------------------------------------- java.version=1.6.0_04 java.vendor=Sun Microsystems Inc. BootLoader constants: OS=Windows XP, ARCH=x86 mag 20, 2009 10:35:10.844 [AWT-EventQueue-1|0] WARNING <DQP|0> Service "dqp.authorization" is null. Can not install it in DQP. mag 20, 2009 10:35:12.375 [AWT-EventQueue-1|0] ERROR <DQP|0> Connector Binding superInfodir_1.MySQL_excel Connector failed to start, check the properties and start again. com.metamatrix.common.application.exception.ApplicationLifecycleException: Could not start Connector ConnectorID<1>. Access denied for user 'excel'@'localhost' (using password: YES) at org.teiid.dqp.internal.datamgr.impl.ConnectorManager.initStartConnector(ConnectorManager.java:443) at org.teiid.dqp.internal.datamgr.impl.ConnectorManager.start(ConnectorManager.java:336) at com.metamatrix.dqp.embedded.services.EmbeddedDataService.startConnectorBinding(EmbeddedDataService.java:231) at com.metamatrix.dqp.embedded.services.EmbeddedDataService.startService(EmbeddedDataService.java:345) at com.metamatrix.dqp.embedded.services.EmbeddedBaseDQPService.start(EmbeddedBaseDQPService.java:73) at com.metamatrix.common.application.Application.installService(Application.java:78) at com.metamatrix.common.application.Application.start(Application.java:63) at org.teiid.dqp.internal.process.DQPCore.start(DQPCore.java:563) at com.metamatrix.jdbc.EmbeddedConnectionFactoryImpl.initialize(EmbeddedConnectionFactoryImpl.java:113) at com.metamatrix.jdbc.EmbeddedConnectionFactoryImpl.createConnection(EmbeddedConnectionFactoryImpl.java:79) at com.metamatrix.jdbc.EmbeddedDriver$EmbeddedTransport.createConnection(EmbeddedDriver.java:502) at com.metamatrix.jdbc.EmbeddedDriver.createConnection(EmbeddedDriver.java:134) at com.metamatrix.jdbc.EmbeddedDriver.connect(EmbeddedDriver.java:114) at com.jaspersoft.ireport.designer.connection.JDBCConnection.getConnection(JDBCConnection.java:149) at com.jaspersoft.ireport.designer.connection.JDBCConnection.test(JDBCConnection.java:456) at com.jaspersoft.ireport.designer.connection.gui.ConnectionDialog.jButtonTestActionPerformed(ConnectionDialog.java:316) at com.jaspersoft.ireport.designer.connection.gui.ConnectionDialog.access$300(ConnectionDialog.java:25) at com.jaspersoft.ireport.designer.connection.gui.ConnectionDialog$4.actionPerformed(ConnectionDialog.java:271) at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995) at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318) at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387) at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242) at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236) at java.awt.Component.processMouseEvent(Component.java:6041) at javax.swing.JComponent.processMouseEvent(JComponent.java:3265) at java.awt.Component.processEvent(Component.java:5806) at java.awt.Container.processEvent(Container.java:2058) at java.awt.Component.dispatchEventImpl(Component.java:4413) at java.awt.Container.dispatchEventImpl(Container.java:2116) at java.awt.Component.dispatchEvent(Component.java:4243) at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4322) at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3986) at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3916) at java.awt.Container.dispatchEventImpl(Container.java:2102) at java.awt.Window.dispatchEventImpl(Window.java:2440) at java.awt.Component.dispatchEvent(Component.java:4243) at java.awt.EventQueue.dispatchEvent(EventQueue.java:599) at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273) at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183) at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:177) at java.awt.Dialog$1.run(Dialog.java:1045) at java.awt.Dialog$3.run(Dialog.java:1097) at java.security.AccessController.doPrivileged(Native Method) at java.awt.Dialog.show(Dialog.java:1095) at java.awt.Component.show(Component.java:1422) at java.awt.Component.setVisible(Component.java:1375) at java.awt.Window.setVisible(Window.java:806) at java.awt.Dialog.setVisible(Dialog.java:985) at com.jaspersoft.ireport.designer.connection.gui.ConnectionsDialog.jButtonModifyParameterActionPerformed(ConnectionsDialog.java:534) at com.jaspersoft.ireport.designer.connection.gui.ConnectionsDialog.access$500(ConnectionsDialog.java:55) at com.jaspersoft.ireport.designer.connection.gui.ConnectionsDialog$8.actionPerformed(ConnectionsDialog.java:243) at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995) at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318) at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387) at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242) at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236) at java.awt.Component.processMouseEvent(Component.java:6041) at javax.swing.JComponent.processMouseEvent(JComponent.java:3265) at java.awt.Component.processEvent(Component.java:5806) at java.awt.Container.processEvent(Container.java:2058) at java.awt.Component.dispatchEventImpl(Component.java:4413) at java.awt.Container.dispatchEventImpl(Container.java:2116) at java.awt.Component.dispatchEvent(Component.java:4243) at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4322) at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3986) at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3916) at java.awt.Container.dispatchEventImpl(Container.java:2102) at java.awt.Window.dispatchEventImpl(Window.java:2440) at java.awt.Component.dispatchEvent(Component.java:4243) at java.awt.EventQueue.dispatchEvent(EventQueue.java:599) at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273) at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183) at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:177) at java.awt.Dialog$1.run(Dialog.java:1045) at java.awt.Dialog$3.run(Dialog.java:1097) at java.security.AccessController.doPrivileged(Native Method) at java.awt.Dialog.show(Dialog.java:1095) at java.awt.Component.show(Component.java:1422) at java.awt.Component.setVisible(Component.java:1375) at java.awt.Window.setVisible(Window.java:806) at java.awt.Dialog.setVisible(Dialog.java:985) at com.jaspersoft.ireport.designer.connection.gui.ConnectionsDialog.setVisible(ConnectionsDialog.java:640) at com.jaspersoft.ireport.designer.menu.DatasourcesAction.performAction(DatasourcesAction.java:23) at org.openide.util.actions.CallableSystemAction$1.run(CallableSystemAction.java:118) at org.netbeans.modules.openide.util.ActionsBridge.doPerformAction(ActionsBridge.java:77) at org.openide.util.actions.CallableSystemAction.actionPerformed(CallableSystemAction.java:114) at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:1995) at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2318) at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:387) at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:242) at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:236) at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:272) at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:272) at java.awt.Component.processMouseEvent(Component.java:6041) at javax.swing.JComponent.processMouseEvent(JComponent.java:3265) at org.openide.awt.ToolbarButton.processMouseEvent(ToolbarButton.java:61) at java.awt.Component.processEvent(Component.java:5806) at java.awt.Container.processEvent(Container.java:2058) at java.awt.Component.dispatchEventImpl(Component.java:4413) at java.awt.Container.dispatchEventImpl(Container.java:2116) at java.awt.Component.dispatchEvent(Component.java:4243) at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4322) at java.awt.LightweightDispatcher.processMouseEvent(Container.java:3986) at java.awt.LightweightDispatcher.dispatchEvent(Container.java:3916) at java.awt.Container.dispatchEventImpl(Container.java:2102) at java.awt.Window.dispatchEventImpl(Window.java:2440) at java.awt.Component.dispatchEvent(Component.java:4243) at java.awt.EventQueue.dispatchEvent(EventQueue.java:599) at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:273) at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:183) at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:173) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:168) at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:160) at java.awt.EventDispatchThread.run(EventDispatchThread.java:121) Caused by: org.teiid.connector.api.ConnectorException: Access denied for user 'excel'@'localhost' (using password: YES) at org.teiid.connector.jdbc.JDBCConnector.getConnection(JDBCConnector.java:200) at org.teiid.connector.jdbc.JDBCConnector.testConnection(JDBCConnector.java:141) at org.teiid.connector.jdbc.JDBCConnector.start(JDBCConnector.java:132) at org.teiid.dqp.internal.datamgr.impl.ConnectorWrapper.start(ConnectorWrapper.java:48) at org.teiid.dqp.internal.pooling.connector.PooledConnector.start(PooledConnector.java:110) at org.teiid.dqp.internal.datamgr.impl.ConnectorManager.initStartConnector(ConnectorManager.java:407) ... 113 more Caused by: java.sql.SQLException: Access denied for user 'excel'@'localhost' (using password: YES) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:812) at com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3284) at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1182) at com.mysql.jdbc.Connection.createNewIO(Connection.java:2670) at com.mysql.jdbc.Connection.<init>(Connection.java:1531) at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266) at org.teiid.connector.jdbc.JDBCConnector$1.invoke(JDBCConnector.java:286) at $Proxy18.getConnection(Unknown Source) at org.teiid.connector.jdbc.JDBCConnector.getConnection(JDBCConnector.java:193) ... 118 more mag 20, 2009 10:35:43.547 [QueryProcessorQueue0|0] ERROR <DQP|0> ############# PW EXITING on 00000000-0000-0002-0000-000000000002.0 - error occurred ########### [ComponentNotFoundException]Connector Binding "superInfodir_1.MySQL_excel Connector" failed to start, or did not start correctly. Please check the connector binding properties. Check the log file for more information. at com.metamatrix.dqp.embedded.services.EmbeddedDataService.selectConnector(EmbeddedDataService.java:122) at org.teiid.dqp.internal.process.capabilities.ConnectorCapabilitiesFinder.findCapabilities(ConnectorCapabilitiesFinder.java:64) at org.teiid.dqp.internal.process.capabilities.SharedCachedFinder.findCapabilities(SharedCachedFinder.java:64) at com.metamatrix.query.optimizer.relational.rules.CapabilitiesUtil.getCapabilities(CapabilitiesUtil.java:511) at com.metamatrix.query.optimizer.relational.rules.CapabilitiesUtil.requiresCriteria(CapabilitiesUtil.java:522) at com.metamatrix.query.optimizer.relational.rules.RulePlaceAccess.addAccessNode(RulePlaceAccess.java:139) at com.metamatrix.query.optimizer.relational.rules.RulePlaceAccess.execute(RulePlaceAccess.java:77) at com.metamatrix.query.optimizer.relational.RelationalPlanner.executeRules(RelationalPlanner.java:400) at com.metamatrix.query.optimizer.relational.RelationalPlanner.optimize(RelationalPlanner.java:144) at com.metamatrix.query.optimizer.QueryOptimizer.optimize(QueryOptimizer.java:241) at com.metamatrix.query.optimizer.QueryOptimizer.recursiveOptimize(QueryOptimizer.java:215) at com.metamatrix.query.optimizer.QueryOptimizer.optimizePlan(QueryOptimizer.java:123) at org.teiid.dqp.internal.process.Request.generatePlan(Request.java:534) at org.teiid.dqp.internal.process.PreparedStatementRequest.generatePlan(PreparedStatementRequest.java:167) at org.teiid.dqp.internal.process.Request.processRequest(Request.java:623) at org.teiid.dqp.internal.process.RequestWorkItem.processNew(RequestWorkItem.java:373) at org.teiid.dqp.internal.process.RequestWorkItem.process(RequestWorkItem.java:206) at org.teiid.dqp.internal.process.AbstractWorkItem.run(AbstractWorkItem.java:44) at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907) at java.lang.Thread.run(Thread.java:619)
The strange thing is that from the designer I can execute the VDB and issue the same query without any problemas.
The error seem to imply that there is a connection problem with MySQl (is the same DB and the connector binding should be the same, at last it was produced by the designer ;-))
Opening the VDB file that is an archive I get this connector binding data:
<ConnectorBindings> <Connector Name="MySQL_excel Connector" ComponentType="MySQL JDBC Connector" LastChangedDate="2009-05-20T09:22:18.641+01:00" CreationDate="2009-05-20T09:22:18.641+01:00" QueuedService="false" routingUUID="mmuuid:96fab70f-7af5-41de-a0de-b3b04a135663"> <Properties> <Property Name="metamatrix.service.essentialservice">false</Property> <Property Name="ExceptionOnMaxRows">true</Property> <Property Name="IsXA">false</Property> <Property Name="Password">{mm-encrypt}o1gkHywAe/la6DLw12KEwg==</Property> <Property Name="ExtensionTranslationClass">org.teiid.connector.jdbc.mysql.MySQLTranslator</Property> <Property Name="ResultSetCacheScope">vdb</Property> <Property Name="ResultSetCacheMaxSize">0</Property> <Property Name="User">excel</Property> <Property Name="TrimStrings">false</Property> <Property Name="MaxSQLLength">16384</Property> <Property Name="ConnectorClass">org.teiid.connector.jdbc.JDBCConnector</Property> <Property Name="MaxResultRows">10000</Property> <Property Name="URL">jdbc:mysql://localhost:3306/excel</Property> <Property Name="ResultSetCacheMaxAge">0</Property> <Property Name="UseCommentsInSourceQuery">false</Property> <Property Name="ConnectionSource">com.mysql.jdbc.Driver</Property> <Property Name="ResultSetCacheEnabled">false</Property> <Property Name="ConnectorTypeClassPath">extensionjar:connector_patch.jar;extensionjar:connector-jdbc-6.0.0.jar;</Property> <Property Name="ConnectorThreadTTL">120000</Property> <Property Name="ServiceClassName">com.metamatrix.server.connector.service.ConnectorService</Property> <Property Name="SetCriteriaBatchSize">1000</Property> <Property Name="DeployedName">superInfodir_1.MySQL_excel Connector</Property> <Property Name="UseBindVariables">false</Property> <Property Name="SourceConnectionTestInterval">600</Property> <Property Name="ConnectorMaxThreads">20</Property> <Property Name="ServiceMonitoringEnabled">true</Property> <Property Name="Immutable">false</Property> <Property Name="ConnectorClassPath">extensionjar:mysql-jdbc.jar</Property> </Properties> </Connector> </ConnectorBindings>
Looking around i do not find the com.metamatrix.server.connector.service.ConnectorService, but I did not open all the jars inside the teiid-embedded so ...
Please any help is appreciated, since I'm bumping around.
Thanks
Luca