12 Replies Latest reply on Aug 6, 2014 4:57 AM by Mounir ZAGHAB

    7.1.1. final cannot define Datasource for MySQL

    Hans-Michael Rupp Novice

      Hallo,

       

      I have tried to follow

      https://community.jboss.org/wiki/DataSourceConfigurationInAS7

      to define a DataSource for MySQL.

      I have tried to modify standalone.xml with

      <datasources>

          <datasource jndi-name="java:jboss/datasources/Betreuung" pool-name="Betreuung">

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

              <driver>com.mysql</driver>

              <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

              <pool>

                  <min-pool-size>10</min-pool-size>

                  <max-pool-size>100</max-pool-size>

                  <prefill>true</prefill>

              </pool>

              <security>

                  <user-name>test</user-name>

                  <password>test</password>

              </security>

              <statement>

                  <prepared-statement-cache-size>32</prepared-statement-cache-size>

                  <share-prepared-statements/>

              </statement>

          </datasource>

          <drivers>

              <driver name="com.mysql" module="com.mysql">

                  <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>

              </driver>

          </drivers>

       

      However each time I deploy my application or restart the server those changes are lost and the original standalone.xml is back.

        • 1. Re: 7.1.1. final cannot define Datasource for MySQL
          Hans-Michael Rupp Novice

          I think the original problem was, that I still had the file open in the editor.

          But still I have no working Datasource.

          When I look in the web console I see the Datasource listed but in the column "enabled" it says false.

          How do you enable a datsource????

          • 2. Re: 7.1.1. final cannot define Datasource for MySQL
            Hans-Michael Rupp Novice

            After switching to "Profile" I found an enable button. But  when I use it I get.

            "Unknown Error"

            From the log:

            16:31:36,605 ERROR [org.jboss.as.controller.management-operation] (HttpManagemen

            tService-threads - 5) JBAS014612: Operation ("enable") failed - address: ([

                ("subsystem" => "datasources"),

                ("data-source" => "Betreuung")

            ]): org.jboss.msc.service.DuplicateServiceException: Service jboss.data-source-c

            onfig.Betreuung is already registered

             

            ???

            Adding datsources used to be so simple in JBoss 4.2 ....

            • 3. Re: 7.1.1. final cannot define Datasource for MySQL
              James Perkins Master

              It sounds like the datasource may have been defined twice. You might be best to revert your standalone.xml and try again. Also pay careful attention to setting the MySQL library up as a module, https://community.jboss.org/wiki/DataSourceConfigurationInAS7#Installing_a_JDBC_driver_as_a_module. This is important because MySQL is not certified as a JDBC 4 driver.

              • 4. Re: 7.1.1. final cannot define Datasource for MySQL
                Hans-Michael Rupp Novice

                In jboss-as-7.1.1.Final\modules\com\mysql\main

                I have

                module.xml

                mysql-connector-java-5.0.7-bin.jar

                mysql-connector-java-5.0.7-bin.jar.index (has been created)

                 

                module.xml:

                <?xml version="1.0" encoding="UTF-8"?>

                <module xmlns="urn:jboss:module:1.0" name="com.mysql">

                    <resources>

                        <resource-root path="mysql-connector-java-5.0.7-bin.jar"/>

                    </resources>

                    <dependencies>

                        <module name="javax.api"/>

                    </dependencies>

                </module>

                 

                in standalone.xml

                <drivers>

                      <driver name="com.mysql" module="com.mysql">

                             <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>

                       </driver>

                 

                I have deleted the datasource entry in standalone.xml

                and tried to define the datasource with the webconsole.

                This has not worked since the mysql driver does not show up.

                 

                Is there a way to certify that deploying the driver as a module has worked?

                 

                Thanks for any input,

                 

                Hans

                • 5. Re: 7.1.1. final cannot define Datasource for MySQL
                  Helga Waage Newbie

                  It looks like your driver is defined as a transaction-enabled driver, but you are defining the datasource as non-transactional.  Try to define the datasource (in standalone.xml) similar to this:

                   

                    <xa-datasource jndi-name="java:/SystemDS" pool-name="SystemDS" enabled="true" use-java-context="true">

                                      <xa-datasource-property name="ServerName">

                                          localhost

                                      </xa-datasource-property>

                                      <xa-datasource-property name="DatabaseName">

                                          systemdb

                                      </xa-datasource-property>

                                      <xa-datasource-property name="User">

                                          username

                                      </xa-datasource-property>

                                      <xa-datasource-property name="Password">

                                          password

                                      </xa-datasource-property>

                                      <driver>com.mysql</driver>

                                      <transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>

                                      <xa-pool>

                                          <min-pool-size>100</min-pool-size>

                                          <max-pool-size>250</max-pool-size>

                                      </xa-pool>

                                      <security>

                                          <user-name>username</user-name>

                                          <password>password</password>

                                      </security>

                                      <validation>

                                          <check-valid-connection-sql>select 1</check-valid-connection-sql>

                                      </validation>

                                      <timeout>

                                          <idle-timeout-minutes>5</idle-timeout-minutes>

                                      </timeout>

                                      <statement>

                                          <track-statements>TRUE</track-statements>

                                      </statement>

                                  </xa-datasource>

                  I saw somewhere that you can define datasources in 7.1+ in xx-ds.xml files similar to the way it was done in 4.2.x, but I haven't tried it myself, since I had already moved my data definition into the standalone.xml file.

                  • 6. Re: 7.1.1. final cannot define Datasource for MySQL
                    Hans-Michael Rupp Novice

                    I have tried in standalone.xml

                     

                    <xa-datasource jndi-name="java:jboss/datasources/betreuungDS" pool-name="betreuung" enabled="true">

                                        <xa-datasource-property name="URL">

                                            jdbc:mysql://localhost:3306/betreuung

                                        </xa-datasource-property>

                                        <driver>com.mysql</driver>

                                        <xa-pool>

                                            <min-pool-size>10</min-pool-size>

                                            <max-pool-size>20</max-pool-size>

                                            <prefill>true</prefill>

                                        </xa-pool>

                                        <security>

                                            <user-name>root</user-name>

                                            <password>ritak1</password>

                                        </security>

                                    </xa-datasource>

                                    <drivers>

                                        <driver name="com.mysql" module="com.mysql">

                                            <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>

                                        </driver>

                                        <driver name="h2" module="com.h2database.h2">

                                            <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>

                                        </driver>

                                    </drivers>

                     

                    It seems to be crucial to add the enabled="true" attribute.

                    I see the datasource in the management web console. It is marked as "enabled"

                    When I test the connection I get

                     

                    Unknown error

                    Unexpected HTTP response: 500  Request { "address" => [ ("subsystem" => "datasources"), ("xa-data-source" => "betreuung") ], "operation" => "test-connection-in-pool" }  Response  Internal Server Error { "outcome" => "failed", "failure-description" => "JBAS010440: failed to invoke operation: JBAS010442: failed to match pool. Check JndiName: java:jboss/datasources/betreuungDS", "rolled-back" => true }

                     

                    In the server log I get

                    18:25:11,524 INFO  [org.jboss.as.controller] (Controller Boot Thread) JBAS014774

                    : Service status report

                    JBAS014775:    New missing/unsatisfied dependencies:

                          service jboss.jdbc-driver.com_mysql (missing) dependents: [service jboss.d

                    ata-source.java:jboss/datasources/betreuungDS]

                     

                     

                    • 7. Re: 7.1.1. final cannot define Datasource for MySQL
                      James Perkins Master

                      Did you add that datasource from the web console? If not you might want to try that. I don't have MySQL installed, so I couldn't test everything, but adding the module (with MySQL 5.1.19) and adding the driver to the standalone.xml I was able to configure it.

                       

                      Here is what my standalone.xml looked like afterwards:

                       

                              <subsystem xmlns="urn:jboss:domain:datasources:1.0">
                                  <datasources>
                                      <datasource jndi-name="java:jboss/datasources/ExampleDS" pool-name="ExampleDS" enabled="true" use-java-context="true">
                                          <connection-url>jdbc:h2:mem:test;DB_CLOSE_DELAY=-1</connection-url>
                                          <driver>h2</driver>
                                          <security>
                                              <user-name>sa</user-name>
                                              <password>sa</password>
                                          </security>
                                      </datasource>
                                      <xa-datasource jndi-name="java:jboss/datasources/betreuungDS" pool-name="betreuung" enabled="true" use-ccm="false">
                                          <xa-datasource-property name="url">
                                              localhost
                                          </xa-datasource-property>
                                          <driver>com.mysql</driver>
                                          <xa-pool>
                                              <is-same-rm-override>false</is-same-rm-override>
                                              <interleaving>false</interleaving>
                                              <pad-xid>false</pad-xid>
                                              <wrap-xa-resource>false</wrap-xa-resource>
                                          </xa-pool>
                                          <security>
                                              <user-name>myuser</user-name>
                                              <password>mypass</password>
                                          </security>
                                          <validation>
                                              <validate-on-match>false</validate-on-match>
                                              <background-validation>false</background-validation>
                                          </validation>
                                          <statement>
                                              <share-prepared-statements>false</share-prepared-statements>
                                          </statement>
                                      </xa-datasource>
                                      <drivers>
                                          <driver name="com.mysql" module="com.mysql">
                                              <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>
                                          </driver>
                                          <driver name="h2" module="com.h2database.h2">
                                              <xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
                                          </driver>
                                      </drivers>
                                  </datasources>
                              </subsystem>
                      
                      
                      • 8. Re: 7.1.1. final cannot define Datasource for MySQL
                        Helga Waage Newbie

                        Try to leave out the jdbc:mysql:// prefix and the port in the url.

                         

                        For me, this works:

                          <xa-datasource-property name="ServerName">

                                                localhost

                          </xa-datasource-property>

                         

                        This may also work:

                        <xa-datasource-property name="url">
                                localhost
                        </xa-datasource-property>

                         

                        Also, your mysql driver is very old, try to download a more recent one.

                        • 9. Re: 7.1.1. final cannot define Datasource for MySQL
                          Stephen Coy Master

                          Hans-Michael Rupp wrote:

                           

                          ...

                           

                          However each time I deploy my application or restart the server those changes are lost and the original standalone.xml is back.

                           

                          This is usually a consequence of changing the standalone.xml file with a text editor while the JBoss server is running...

                          • 11. Re: 7.1.1. final cannot define Datasource for MySQL
                            Kamiel Ahmadpour Newbie

                            Try to add the driver class explicitly:

                             

                            <driver name="com.mysql" module="com.mysql">

                                 <xa-datasource-class>com.mysql.jdbc.jdbc2.optional.MysqlXADataSource</xa-datasource-class>

                                 <driver-class>com.mysql.jdbc.Driver</driver-class>

                            </driver>

                            • 12. Re: 7.1.1. final cannot define Datasource for MySQL
                              Mounir ZAGHAB Newbie

                              this is caused by the double entry in the mysql jar file (META-INF/services/java.sql.Driver) as jboss 7.1.1 can't interpret it correctly, as a workaround you can add explicitly the tag <driver-class> (see comment above of Kamiel) or use a mysql driver older than 5.1.30

                              see issue Bug 1107120 – Failures when deploying MySQL JDBC driver to EAP 6.x