9 Replies Latest reply on Jan 11, 2016 7:22 AM by m.ardito

    Datasource disconnects, needs manual flush to work again. What should I do?

    m.ardito

      I always studied/tested teiid servers locally, starting the local AS, played with DS, VDBs, testet with Squirrel (jdbc) and msaccess (odbc), I learned the basics, and now I wish to move in an "always on" lan server. Now I have a teiid server (latest "8.12.3 With EAP") in the lan (on debian), and I'm testing it with same datasources and simple VDBs.

       

      Everything works, as it did locally, but I have a problem about datasource disconnections/reconnections.

       

      I have basically two test DS, one to a lan MSSQL server and another to a lan MYSQL server.

       

      After the first night, I tested again with Squirrel could query a simple VBD to MSSQL, while it could not query a simple VDB to MYSQL.

      I had errors that referred errors like TEIID30504, teiid SQLState: 50000, ErrorCode: 30504

       

      I digged the teiid web admin console, tried to reload the VDB and found the problem to be that the connection was not active anymore.

      "TEIID11010 com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed."

       

      I've found that sometimes I need to flush the datasource (manually from the teiid web console), and everything works again imediately...

      I can reproduce this, killing the connection on the MYSQL server (from the teiid server).

       

      Sometimes also flush is not enough, and happens:

      "TEIID11010 com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

      The last packet successfully received from the server was 1.284.760 milliseconds ago.  The last packet sent successfully to the server was 0 milliseconds ago."

       

      This I can only solved by disabiling/re-enabling the DS.

       

      What should I do? What I do wrong? What useful can I post here for you to help me ? Thanks.

       

      Marco

        • 1. Re: Datasource disconnects, needs manual flush to work again. What should I do?
          van.halbert

          Marco,

           

          There are configuration options on the data source that can be used.  Example from the Teiid data source doc's:

           

          <datasources>

              <datasource jndi-name="java:/mysql-ds" pool-name="mysqlDS">

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

                  <connection-url>jdbc:mysql://{host}:3306/{database}</connection-url>

                  <pool>

                      <prefill>false</prefill>

                      <use-strict-min>false</use-strict-min>

                      <flush-strategy>FailingConnectionOnly</flush-strategy>

                  </pool>

                  <security>

                      <user-name>{user}</user-name>

                      <password>{password}</password>

                  </security>

              </datasource>                              

          </datasources>

           

          There are also options for the connection pool to check the connection before handing it out.

          For more detail on configuration, goto:  https://docs.jboss.org/author/display/WFLY9/DataSource+configuration

          • 2. Re: Datasource disconnects, needs manual flush to work again. What should I do?
            m.ardito

            Hi,

            thanks for your hints. In the DS I already tried to check the flush-strategy options, through the teiid web admin console.

            At first, it was empty (default should be FailingConnectionOnly).

            I edited the DS specifying all possible values, FailingConnectionOnly, IdleConnections and EntirePool, but it seemed to make no difference in reconnection behaviour from the Client: I'll try again, anyway.

             

            About the second link, I'll check, and try to figure out what can be done...  feel free to suggest what traces/logs to check to get what is cousing this issue...

             

            edit: I tried also to kikk the MSSQL connectin from its source server, and as in the MYSQL case, teiid can't reconnect, until I click "flush" in the DS pool area....

             

            where can I learn what exactly "flush" does, and maybe how to force an automatic flush periodically...?

             

            Marco

            • 3. Re: Datasource disconnects, needs manual flush to work again. What should I do?
              rareddy

              See https://docs.jboss.org/jbossas/docs/Server_Configuration_Guide/beta500/html/ch13s13.html

               

              use ping SQL to check the connection like below


              <check-valid-connection-sql> select * from duel </check-valid-connection-sql>

              • 4. Re: Datasource disconnects, needs manual flush to work again. What should I do?
                m.ardito

                Thanks,

                I just tried like this, but now even flush does not work.... here is the DS taken from the XML file as it is now...

                 

                (edit: now the sql checker is "select 1 from DUAL", but again as before it reconnects only after manual flush )

                 

                <datasource jta="true" jndi-name="java:/testmysql" pool-name="testmysql" enabled="true" use-ccm="true" statistics-enabled="false">

                        <connection-url>jdbc:mysql://192.168.x.x:3306/fred</connection-url>

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

                        <driver>mysql-connector-java-5.1.22-bin.jar</driver>

                        <pool>

                                <min-pool-size>0</min-pool-size>

                                <max-pool-size>0</max-pool-size>

                                <prefill>false</prefill>

                                <use-strict-min>false</use-strict-min>

                                <flush-strategy>FailingConnectionOnly</flush-strategy>

                        </pool>

                        <security>

                                <user-name>xxx</user-name>

                                <password>xxx</password>

                        </security>

                        <validation>

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

                                <validate-on-match>false</validate-on-match>

                                <background-validation>false</background-validation>

                        </validation>

                        <timeout>

                                <set-tx-query-timeout>false</set-tx-query-timeout>

                                <blocking-timeout-millis>0</blocking-timeout-millis>

                                <idle-timeout-minutes>0</idle-timeout-minutes>

                                <query-timeout>0</query-timeout>

                                <use-try-lock>0</use-try-lock>

                                <allocation-retry>0</allocation-retry>

                                <allocation-retry-wait-millis>0</allocation-retry-wait-millis>

                        </timeout>

                        <statement>

                                <share-prepared-statements>false</share-prepared-statements>

                        </statement>

                </datasource>

                • 5. Re: Datasource disconnects, needs manual flush to work again. What should I do?
                  rareddy

                  "Select * from Dual" was meant to be as an example, you need to find right test SQL for database in question. Some examples are here

                   

                  http://stackoverflow.com/questions/10982281/mysql-connection-validity-test-in-datasource-select-1-or-something-better

                  http://stackoverflow.com/questions/4957155/mysql-testing-connection-with-query

                  • 6. Re: Datasource disconnects, needs manual flush to work again. What should I do?
                    m.ardito

                    I tried a few working queries... but every time, if I kill the conection from the real backend server, teiid does not restart a connection until I manually flush...

                    before manually flushing, i get in the client errors like

                    "Error: TEIID30504 Remote org.teiid.core.TeiidProcessingException: TEIID30504 mysql5-connector: 0 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT g_0.`conto` AS c_0, g_0.`descrizione` AS c_1, g_0.`split_fornitori` AS c_2, g_0.`ivaind` AS c_3, g_0.`attivo` AS c_4 FROM `fred`.`conti_ripartizioni` AS g_0 LIMIT 100]

                    SQLState:  50000 ErrorCode: 30504"

                     

                    then if I manually flush, everything magically works again...

                     

                    I need to solve this, and at least temporarily find a workaround, like a cronjob to flush datasources, until I get/solve why it fails..

                    • 7. Re: Datasource disconnects, needs manual flush to work again. What should I do?
                      shawkins

                      You need validate on match or background validation to be true:

                       

                      <validate-on-match>true</validate-on-match>


                      Then it should issue the validation sql.  You can also use:


                      <valid-connection-checker-class-name>

                        com.mysql.jdbc.integration.jboss.MysqlValidConnectionChecker

                        </valid-connection-checker-class-name>

                       

                      Rather than specifying the test query.

                      • 8. Re: Datasource disconnects, needs manual flush to work again. What should I do?
                        m.ardito

                        Thanks, Steven. Our company is closed for holidays until january 10, (we work in the learning/training field).

                        I'll test your (precious) hints only then but for now I wish say thank you . I really hope 2016 will be our "Teiid" year.

                        • 9. Re: Datasource disconnects, needs manual flush to work again. What should I do?
                          m.ardito

                          Yes! This triggers the connection restart at client request... Thanks, now I'll explore all other parameters...

                           

                          Thank you very much for supporting my newbieness....

                           

                          Marco