-
1. Re: Datasource disconnects, needs manual flush to work again. What should I do?
van.halbert Dec 22, 2015 8:12 AM (in response to m.ardito)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 Dec 22, 2015 9:53 AM (in response to van.halbert)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 Dec 22, 2015 9:39 AM (in response to m.ardito)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 Dec 22, 2015 10:26 AM (in response to rareddy)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 Dec 22, 2015 10:34 AM (in response to m.ardito)"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/4957155/mysql-testing-connection-with-query
-
6. Re: Datasource disconnects, needs manual flush to work again. What should I do?
m.ardito Dec 22, 2015 10:46 AM (in response to rareddy)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 Dec 23, 2015 9:17 AM (in response to m.ardito)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 Dec 28, 2015 8:17 AM (in response to shawkins)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 Jan 11, 2016 7:22 AM (in response to shawkins)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