[esb-issues] [JBoss JIRA] Resolved: (JBESB-928) Unexpected/incorrect/consistent behavior with SQL listener - when writing to DB via Java/JDBC

Unexpected/incorrect/consistent behavior with SQL listener - when writing to DB via Java/JDBC --------------------------------------------------------------------------------------------- Key: JBESB-928 URL: http://jira.jboss.com/jira/browse/JBESB-928 Project: JBoss ESB Issue Type: Bug Security Level: Public(Everyone can see) Components: Transports Affects Versions: 4.2 Milestone Release 3 Environment: RHEL5 ESB built from SVN on August 24 Reporter: Len DiMaggio Assigned To: Tom Cunningham Priority: Minor Fix For: 4.2.1 Attachments: supporting_files.zip Problem description: I'm seeing odd/incorrect behavior with the SQLprovider listener as used in the helloworld_sql_action quickstart. If the records are written to the database via Java/JDBC, strange things happen, in a consistent pattern. Background: The helloworld_sql_action quickstart as distributed runs without error. If the quickstart is modified to include an action based on org.jboss.soa.esb.actions.SystemPrintln, the quickstart is able to generate a event log file. Note that the quickstart as distributed writes records to its DB via an SQL script. In the course of writing an automated test for this quickstart (/trunk/product/samples/quickstarts/tests/src/org/jboss/soa/esb/quickstart/test/HelloWorldSQLActionTest.java), I discovered the behavior described below. I was able to recreate this behavior with a simple class added to the quickstart's source tree. Steps to recreate the problem: 1) Copy the files listed below to their directories and rebuild ESB (ant dist). 2) cd to /trunk/product/samples/quickstarts/helloworld_sql_action 3) First - to verify that the new class ("TempTest") is able to write to the target DB: a. start mysql b. ant createdb c. ant runtemptest d. mysql --user=root --password= e. (in mysql) use test_sql_gateway f. (in mysql) select * from gateway_table; g. verify that (5) records have been written to the DB: +-----------+----------------------------------------------------------------------------+------------+ | unique_id | data_column | status_col | +-----------+----------------------------------------------------------------------------+------------+ | 1 | data 111111[jbesb-filename]/tmp/TempTest.log[/jbesb-filename] | p | | 2 | data 22[jbesb-filename]/tmp/TempTest.log[/jbesb-filename] | p | | 3 | data 333333333333111111[jbesb-filename]/tmp/TempTest.log[/jbesb-filename] | p | | 4 | data d d d d[jbesb-filename]/tmp/TempTest.log[/jbesb-filename] | p | | 5 | data data last record[jbesb-filename]/tmp/TempTest.log[/jbesb-filename] | p | +-----------+----------------------------------------------------------------------------+------------+ 5 rows in set (0.01 sec) 4) To illustrate the first of two problems - where the org.jboss.soa.esb.actions.SystemPrintln action never fires. a. start up the ESB server b. ant dropdb c. ant createdb d. ant clean e. ant compile f. ant deploy g. ant runtemptest h. in the ESB server log, verify that these messages are not written for each of the (5) records) 13:20:36,650 INFO [STDOUT] ** 'message' attribute not set **: 13:20:36,650 INFO [STDOUT] Writing /tmp/TempTest.log i. verify that the /tmp/TempTest.log file is not created j. verify that no records are written to the gateway_table in the test_sql_gateway database 5) To illustrate the 2nd problem - where the org.jboss.soa.esb.actions.SystemPrintln action fires - given enough time - but results in records not being written to the DB a. In TempTest.java, uncomment the sleep() statement at line 63 - adds (30) seconds per record a. ant dropdb b. ant createdb c. ant clean d. ant compile e. ant runtemptest h. in the ESB server log, verify that these messages are written for each of the (5) records) 13:20:36,650 INFO [STDOUT] ** 'message' attribute not set **: 13:20:36,650 INFO [STDOUT] Writing /tmp/TempTest.log i. verify that this text is written to the /tmp/TempTest.log file: {unique_id=1, data_column=data 111111[jbesb-filename]/tmp/TempTest.log[/jbesb-filename] }{unique_id=2, data_column=data 22[jbesb-filename]/tmp/TempTest.log[/jbesb-filename] }{unique_id=3, data_column=data 333333333333111111[jbesb-filename]/tmp/TempTest.log[/jbesb-filename] }{unique_id=4, data_column=data d d d d[jbesb-filename]/tmp/TempTest.log[/jbesb-filename] }{unique_id=5, data_column=data data last record[jbesb-filename]/tmp/TempTest.log[/jbesb-filename] j. verify that no records are written to the gateway_table in the test_sql_gateway database Supporting files - attached to this JIRA - you'll need to use these files to recreate the problem. These files have not been checked into the trunk so as to not affect the ESB GA code freeze. build.xml - copy to trunk/product/samples/quickstarts/helloworld_sql_action/ TempTest.java - copy to trunk/product/samples/quickstarts/helloworld_sql_action/src/org/jboss/soa/esb/samples/quickstart/helloworldsqlaction/ jboss-esb.xml - copy to trunk/product/samples/quickstarts/helloworld_sql_action/ SystemPrintln.java - copy to trunk/product/rosetta/src/org/jboss/soa/esb/actions/