MySQL integration in Drools Platform

[Nidhi Ajmera]

Hi,

From what I have read in various forums and documentation, we can integrate the Drools platform with any RDBMS like MySQL to store the rules and assets instead of the Guvnor repository which internally uses Jackrabbit (tree structure) to store the rules. 

I managed to install the MySQL on Drools but I always get the error when I try to do 'Test Connection'.

I followed this blog http://giordanomaestro.blogspot.co.uk/2015/02/install-jdbc-driver-on-wildfly.html and can see the MySQL datasource but I get the following error when I test the connection:

Unexpected HTTP response: 500

Request
{
    "address" => [
        ("subsystem" => "datasources"),
        ("data-source" => "MySQLDS")
    ],
    "operation" => "test-connection-in-pool"
}

Response

Internal Server Error
{
    "outcome" => "failed",
    "failure-description" => "JBAS010440: failed to invoke operation: JBAS010447: Connection is not valid",
    "rolled-back" => true
}

Can someone help me what is wrong with the connection?

Also, where in Drools do I need to specify that use MySQL instead of the default Jackrabbit implementation?

Many thanks,

Nidhi

[Michael Anstis]

Your question is multi-faceted:

1) The "Drools Webapp" up to 5.5 used JackRabbit to store asset data; and could be configured to use a RDBMS instead.

2) The "Drools Workbench Webapp" (6.x) uses GIT to store asset data and a RDBMS (of your choice/configuration) for process state information. GIT cannot be swapped out for a RDBMS. 

3) Drools "rules" can fetch data from RDBMS's. This requires you to provide a "Data Helper class" and integration with a RDBMS as you see fit.

Which of the three are you more specifically referring?

--
You received this message because you are subscribed to the Google Groups "Drools Usage" group.
To unsubscribe from this group and stop receiving emails from it, send an email to drools-usage...@googlegroups.com.
To post to this group, send email to drools...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/drools-usage/437ce1b5-5244-4771-b325-188fe026249c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[NAJ]

Hi Michael,

Thanks for your quick response.

Sorry but I think I am a bit confused.

I am trying to integrate the RDBMS(MySQL) on Drools Workbench 6.3.0.Final.

I see all the rule projects along with the versions are stored under /opt/wildfly/standalone/data in the form of .ser files. Is this how the rules are stored in the latest workbench version?

I have now configured the MySQL in Wildfly 8.2.1, but how do I configure the RDBMS to store the process states? Btw, is that only for jBPM bit?

I have gone through this documentation (https://docs.jboss.org/jbpm/v6.3/userguide/ch25.html) but still unable to get hang of step by step guide to some working examples.

I would really appreciate if you can point me to some examples.

Regards,

Nidhi 

[Michael Anstis]

> I see all the rule projects along with the versions are stored under /opt/wildfly/standalone/data in the form of .ser files. Is this how the rules are stored in the latest workbench version?

No. The Workbench stores all assets (files, projects etc) in GIT in a folder called ".niogit" normally under the Application Server's bin folder (e.g. jboss-eap-6.4/bin/.niogit). IDK what ".ser" files are.

> I have now configured the MySQL in Wildfly 8.2.1, but how do I configure the RDBMS to store the process states? Btw, is that only for jBPM bit?

Drools Workbench uses jBPM internally for Asset Management. The state of processes relating to Asset Management are stored in a RDBMS.

> I have gone through this documentation (https://docs.jboss.org/jbpm/v6.3/userguide/ch25.html) but still unable to get hang of step by step guide to some working examples.

@maciejs, @salaboy, @kris.. could you please point Nidhi to examples (tutorial, User Guide) as to how to configure jBPM to use MySQL?

> I would really appreciate if you can point me to some examples.

To view this discussion on the web visit https://groups.google.com/d/msgid/drools-usage/57415897-ff80-40b8-8e17-995c35dcf3bd%40googlegroups.com.

[Maciej Swiderski]

On 08.01.2016, at 16:42, Michael Anstis <michael...@gmail.com> wrote:

> I see all the rule projects along with the versions are stored under /opt/wildfly/standalone/data in the form of .ser files. Is this how the rules are stored in the latest workbench version?

No. The Workbench stores all assets (files, projects etc) in GIT in a folder called ".niogit" normally under the Application Server's bin folder (e.g. jboss-eap-6.4/bin/.niogit). IDK what ".ser" files are.

> I have now configured the MySQL in Wildfly 8.2.1, but how do I configure the RDBMS to store the process states? Btw, is that only for jBPM bit?

Drools Workbench uses jBPM internally for Asset Management. The state of processes relating to Asset Management are stored in a RDBMS.

> I have gone through this documentation (https://docs.jboss.org/jbpm/v6.3/userguide/ch25.html) but still unable to get hang of step by step guide to some working examples.

@maciejs, @salaboy, @kris.. could you please point Nidhi to examples (tutorial, User Guide) as to how to configure jBPM to use MySQL?

try this article: http://mswiderski.blogspot.com/2015/03/jbpm-62-installation-and-upgrade-from.html

Maciej

[NAJ]

Thanks Michael and Maciej!

I followed your blog and changed the persistence.xml file to reflect the right datasource and the dialect.

From Wildfly console when I try and do a test connection for the new datasource (jdbc:mysql://localhost:3306/smstest/Test) I get successful response; also when the server starts up it does say 

2016-01-20 13:52:58,697 INFO  [org.jboss.as.connector.subsystems.datasources] (MSC service thread 1-1) JBAS010400: Bound data source [java:/mysql/datasources/MySQLDS]

Also it is using the right dialect  

2016-01-20 14:45:20,327 INFO  [org.hibernate.dialect.Dialect] (ServerService Thread Pool -- 62) HHH000400: Using dialect: org.hibernate.dialect.MySQLDialect

But later crashes and gives the below error:

2016-01-20 14:45:22,057 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (ServerService Thread Pool -- 62) HHH000388: Unsuccessful: create table Attachment (id bigint not null auto_increment, accessType integer, attachedAt datetime, attachmentContentId bigint not null, contentType varchar(255), name varchar(255), attachment_size integer, attachedBy_id varchar(255), TaskData_Attachments_Id bigint, primary key (id))

2016-01-20 14:45:22,057 ERROR [org.hibernate.tool.hbm2ddl.SchemaUpdate] (ServerService Thread Pool -- 62) No database selected

2

As you can see in the connection string I have specified the database too.

Is there any other setting I have missed.

Could you please guide me in the right direction?

Many thanks,

Nidhi

[NAJ]

Hello Michael, Maciej,

Did anyone of you get a chance to look at my query below?

Any direction/pointers will be of huge help!!

Many thanks,

Nidhi

[Maciej Swiderski]

looks like data base urls might not be valid. Here is what I have locally and it does work without an issue - where jbpm is the data base name:

<datasource jndi-name="java:jboss/datasources/msqlJbpmPS" pool-name="mysqlDS" enabled="true" use-java-context="true">

                    <connection-url>jdbc:mysql://localhost:3306/jbpm</connection-url>

                    <driver>mysql</driver>

                    <security>

                        <user-name>jbpm</user-name>

                        <password>jbpm</password>

                    </security>

                </datasource>

Maciej

To view this discussion on the web visit https://groups.google.com/d/msgid/drools-usage/3464e554-1465-4ce7-a7e6-13bf3361e454%40googlegroups.com.