| Author |
Message
|
| RadhamaniRamadoss |
 Posted: Sun May 19, 2013 10:31 pm Post subject: JDBC connection to MySQL using DatabaseRetrieve node |
 |
|
Apprentice
Joined: 08 Oct 2009
Posts: 42
|
Hi,
I am trying to make JDBC call to MySQL Database server.I tried this through a Java program which worked fine.
Then I tried with Javacompute node with below url,
"jdbc:mysql://127.0.0.1:3306/test?user=root&password=XYZ"
This also worked fine.
Considering the performance aspects,as there would be huge number of retrievals required from Database,I am trying to achieve this with DatabaseRetrieve node.
But its throwing Access denied error.
I have setup MySql service and then I have setup mqsisetdbparms as below,
<---------------------------------------------------------------------------------->
RadMySql
connectionUrlFormat='jdbc:mysql://[serverName]:[portNumber]/[databaseName]:
ser=[user];password=[password];'
connectionUrlFormatAttr1=''
connectionUrlFormatAttr2=''
connectionUrlFormatAttr3=''
connectionUrlFormatAttr4=''
connectionUrlFormatAttr5=''
databaseName='test'
databaseType='mysql'
databaseVersion='default_Database_Version'
description='default_Description'
environmentParms='default_none'
jarsURL='E:\Softwares\mysql-connector-java-5.0.8'
maxConnectionPoolSize='0'
portNumber='3306'
securityIdentity='RadMySqlsec5'
serverName='127.0.0.1'
type4DatasourceClassName='com.mysql.jdbc.jdbc2.optional.MysqlDataSource'
type4DriverClassName='com.mysql.jdbc.Driver'
<---------------------------------------------------------------------------------->
mqsisetdbparms RadBroker -n jdbc::RadMySqlsec5 -u root -p XYZ
(RadMySqlsec is security identity name)
mqsichangeproperties RadBroker -c JDBCProviders -o RadMySql -n securityIdentity -v RadMySqlsec5
<---------------------------------------------------------------------------------->
In MYSQL,
GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'XYZ';
<---------------------------------------------------------------------------------->
In Database retrieve node,
I have given 'Data Source name' as 'RadMySql'
<---------------------------------------------------------------------------------->
I restarted the broker ,redeployed the flow.
But I am getting below error,while execution,
<---------------------------------------------------------------------------------->
In Trace,
2013-05-19 21:24:52.059879 5036 UserTrace BIP6262I: Using a JDBC connection URL to connect to the JDBC driver class for node: 'Broker 'RadBroker'; Execution Group 'default'; Message Flow 'Database_Proj'; Node 'Database Retrieve'; Node Type 'DatabaseRetrieve'.
The JDBC connection URL is ''jdbc:mysql://127.0.0.1:3306/test:user=root;password=xxxxxxxx;''.
No user action required.
2013-05-19 21:24:52.724151 5036 Error BIP2628E: Exception condition detected on input node 'Database_Proj.MQ Input'.
The input node 'Database_Proj.MQ Input' detected an error whilst processing a message. The message flow has been rolled-back and, if the message was being processed in a unit of work, it will remain on the input queue to be processed again. Following messages will indicate the cause of this exception.
Check the error messages which follow to determine why the exception was generated, and take action as described by those messages.
2013-05-19 21:24:52.724235 5036 RecoverableException BIP2230E: Error detected whilst processing a message in node 'Database_Proj.Database Retrieve'.
The message broker detected an error whilst processing a message in node 'Database_Proj.Database Retrieve'. An exception has been thrown to cut short the processing of the message.
See the following messages for details of the error.
2013-05-19 21:24:52.724277 5036 DatabaseException BIP6233E: An error occurred in node: 'Broker 'RadBroker'; Execution Group 'default'; Message Flow 'Database_Proj'; Node 'Database Retrieve'; Node Type 'DatabaseRetrieve' There was a problem establishing a connection to the given database URL: 'jdbc:mysql://127.0.0.1:3306/test:user=root;password=xxxxxxxx;' Exception details: ' error message: Access denied for user ''@'localhost' (using password: NO), SQLState value: 28000, vendor's error code: 1045, stack trace: [com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:885)
com.mysql.jdbc.MysqlIO.secureAuth411(MysqlIO.java:3421)
com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1247)
com.mysql.jdbc.Connection.createNewIO(Connection.java:2775)
com.mysql.jdbc.Connection.<init>(Connection.java:1555)
com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
com.ibm.broker.jdbcnodes.JDBCConnection.createConnection(JDBCConnection.java:272)
com.ibm.broker.jdbcnodes.JDBCDatabaseManager.getThreadOwnedConnections(JDBCDatabaseManager.java:623)
com.ibm.broker.jdbcnodes.DatabaseRetrieveNode.evaluate(DatabaseRetrieveNode.java:398)
com.ibm.broker.plugin.MbNode.evaluate(MbNode.java:1472)]'
The node was unable to establish a connection to the given database URL.
Contact your WebSphere Message Broker administrator.
2013-05-19 21:24:53.816818 5036 Error BIP2648E: Message backed out to a queue; node 'Database_Proj.MQ Input'.
<---------------------------------------------------------------------------------->
In trace log above, the broker seems to be able to form the jdbc url correctly as set in mqsisetdbparms 'jdbc:mysql://127.0.0.1:3306/test:user=root;password=xxxxxxxx;',
But immediately in the next line , it throws error as ,
Access denied for user ''@'localhost' (using password: NO)
Not sure where I am missing.The same credentials is working in JavaCompute node.In Database retrieve node why the broker is unable to connect even after forming url and also its missing the username and password in url while connecting.
I checked in MySql forums but could not find a solution....
Please someone assist me in this...I really have no clue on this issue...
 |
|
| Back to top |
|
 |
| McueMart |
| Posted: Mon May 20, 2013 12:22 am Post subject: |
|
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
So you have told us that your connection works correctly when you use a connection string of:
| Code: |
jdbc:mysql://127.0.0.1:3306/test?user=root&password=XYZ
|
But when you are using a Configurable Service, you are using a connection format pattern of
| Code: |
| 'jdbc:mysql://[serverName]:[portNumber]/[databaseName]:user=[user];password=[password]; |
Which the error is showing resolves to the following after variable replacement:
| Code: |
jdbc:mysql://127.0.0.1:3306/test:user=root;password=xxxxxxxx;
|
What do you reckon could be the issue? |
|
| Back to top |
|
|
| RadhamaniRamadoss |
| Posted: Mon May 20, 2013 3:35 am Post subject: |
|
|
Apprentice
Joined: 08 Oct 2009
Posts: 42
|
Eventhough the url is properly formed ' 'jdbc:mysql://127.0.0.1:3306/test:user=root;password=xxxxxxxx;',
' immediately in the next line,the error is like,
'Access denied for user ''@'localhost' (using password: NO) '
An extract from the logs again,
<---------------------------------------------------------------------------->
There was a problem establishing a connection to the given database URL: 'jdbc:mysql://127.0.0.1:3306/test:user=root;password=xxxxxxxx;' Exception details: ' error message: Access denied for user ''@'localhost' (using password: NO), SQLState value: 28000, vendor's error code: 1045, stack trace: [com.mysql.jdbc.SQLError.createSQLException(SQLError.java:946)
<---------------------------------------------------------------------------->
Because of this when I debug,I am seeing 'Problem encountered in establishing JDBC connection with Database in DatabaseRetrieve node'.
and the error is more specifically ,'Acces denied for ''@'localhost' (using password: NO).
Please assist me....
Last edited by RadhamaniRamadoss on Mon May 20, 2013 8:03 am; edited 1 time in total |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon May 20, 2013 5:21 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| RadhamaniRamadoss wrote: |
Eventhough the url is properly formed ' 'jdbc:mysql://127.0.0.1:3306/test:user=root;password=xxxxxxxx;', |
No, that's not properly formed.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| McueMart |
| Posted: Mon May 20, 2013 6:59 am Post subject: |
|
|
Chevalier
Joined: 29 Nov 2011
Posts: 490
Location: UK...somewhere
|
I thought I had made my reply clear enough. Clearly I failed  |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon May 20, 2013 7:18 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| McueMart wrote: |
| I thought I had made my reply clear enough. Clearly I failed |
I liked your reply.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| RadhamaniRamadoss |
| Posted: Mon May 20, 2013 8:02 am Post subject: |
|
|
Apprentice
Joined: 08 Oct 2009
Posts: 42
|
| Vitor wrote: |
| RadhamaniRamadoss wrote: |
Eventhough the url is properly formed ' 'jdbc:mysql://127.0.0.1:3306/test:user=root;password=xxxxxxxx;', |
No, that's not properly formed. |
But the same url works fine in Javacompute node.
If this is not the correct url,could you please let me know what is the corretc url format I should use? |
|
| Back to top |
|
|
| kash3338 |
| Posted: Mon May 20, 2013 9:35 pm Post subject: |
|
|
Shaman
Joined: 08 Feb 2009
Posts: 709
Location: Chennai, India
|
| RadhamaniRamadoss wrote: |
| But the same url works fine in Javacompute node. |
They are not the same URL and that is what is being pointed out by couple of experts here,
This is the URL you used in Java,
| Quote: |
"jdbc:mysql://127.0.0.1:3306/test?user=root&password=XYZ"
|
And this is the one in DB Retrieve node,
| Quote: |
'jdbc:mysql://127.0.0.1:3306/test:user=root;password=xxxxxxxx;'
|
Are they the same? |
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue May 21, 2013 6:01 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Sometimes it's the question that's important, and not the answer.
?
: |
|
| Back to top |
|
|
| RadhamaniRamadoss |
| Posted: Tue May 21, 2013 6:47 am Post subject: |
|
|
Apprentice
Joined: 08 Oct 2009
Posts: 42
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Tue May 21, 2013 8:49 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You need to create a connectionURLFormat string that properly represents a pattern that will create a valid connectionURL when the various [field] blocks are filled in.
The exact issue with your pattern is that your connectionURLFormat has a ":" when your working connection url has a "?". |
|
| Back to top |
|
|
| RadhamaniRamadoss |
| Posted: Tue May 21, 2013 1:44 pm Post subject: |
|
|
Apprentice
Joined: 08 Oct 2009
Posts: 42
|
Okay...This hint really worked!!!
Thanks much!
I changed the url as below in the command,
mqsichangeProperties RadBroker -c JDBCProviders -o RadMySql -n connectionUrlFormat -v jdbc:mysql://[serverName]:[portNumber]/[databaseName]?user=[user]"&"password=[password];   |
|
| Back to top |
|
|
|
|
