<div dir="ltr">Here's my datasource config:<br><br><div> <Resource name="jdbc/mysql" auth="Container" type="javax.sql.DataSource"</div><div> username="midpoint" password="pass"</div><div> url="jdbc:mysql://SERVER:3306/midpoint_dev"</div><div> driverClassName="com.mysql.jdbc.Driver"</div><div> accessToUnderlyingConnectionAllowed="true"</div><div> initialSize="5" maxWait="5000"</div><div> maxActive="30" maxIdle="5"</div><div> validationQuery="select 1"</div><div> poolPreparedStatements="true"/></div><div><br></div><div>And here's my config.xml:<br><br><div> <repository></div><div> <embedded>false</embedded></div><div> <repositoryServiceFactoryClass>com.evolveum.midpoint.repo.sql.SqlRepositoryFactory</repositoryServiceFactoryClass></div><div> <hibernateHbm2ddl>validate</hibernateHbm2ddl></div><div> <hibernateDialect>com.evolveum.midpoint.repo.sql.util.MidPointMySQLDialect</hibernateDialect></div><div> <dataSource>java:comp/env/jdbc/mysql</dataSource></div><div> </repository></div><div> <taskManager></div><div> <clustered>true</clustered></div><div> <jmxUsername>midpoint</jmxUsername></div><div> <jmxPassword>password</jmxPassword></div><div> </taskManager></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Mon, Aug 15, 2016 at 11:44 AM, Pavol Mederly <span dir="ltr"><<a href="mailto:mederly@evolveum.com" target="_blank">mederly@evolveum.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF" text="#000000">
<p>Hello Florin,</p>
<p>this is really interesting. Please, could you also share your
midPoint config.xml, as well as your data source configuration?
(except credentials, of course)</p><span class="">
<p>Best regards,<br>
</p>
<pre cols="72">Pavol Mederly
Software developer
<a href="http://evolveum.com" target="_blank">evolveum.com</a>
</pre>
</span><div><div class="h5"><div>On 15.08.2016 19:39, Florin. Stingaciu
wrote:<br>
</div>
<blockquote type="cite">
<div dir="ltr">Hello Pavol,
<div><br>
</div>
<div>Thanks for your detailed response. I tried setting up the
datasource with validationQuery set up properly for the mySQL
backed I have. However, upon service restart I get the
following errors: <a href="http://pastebin.com/8dpGN0JC" target="_blank">http://pastebin.com/<wbr>8dpGN0JC</a><br>
<br>
To save you a click, it seems as though the connection is set
up in readonly mode or some other strange things happen. I've
tried setting <span>readonly</span><span>=</span><span>"false"</span><span> in the resource
definition in server.xml but that didn't help. I will
continue researching this, however any guidance would be
quite appreciated. </span></div>
<div><span><br>
</span></div>
<div><span>Thanks!</span></div>
<div><span>-F </span></div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Mon, Aug 15, 2016 at 2:29 AM, Pavol
Mederly <span dir="ltr"><<a href="mailto:mederly@evolveum.com" target="_blank">mederly@evolveum.com</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div bgcolor="#FFFFFF" text="#000000">
<p>Hello Florin,</p>
<p>having looked at your logs, it seems that maybe
explicit setting of validationQuery in Quartz data
source setup would help. (See <a href="http://www.quartz-scheduler.org/documentation/quartz-2.x/configuration/ConfigDataSources.html" target="_blank">http://www.quartz-scheduler.or<wbr>g/documentation/quartz-2.x/con<wbr>figuration/ConfigDataSources.<wbr>html</a>.)</p>
<p>Unfortunately, current midPoint implementation does not
allow to configure Quartz data source parameters. So,
there are the following three possibilities:</p>
<ol>
<li>Take an alternative route, and use application
server-defined data source (with validationQuery set
up).<br>
</li>
<li>Patch Task Manager implementation by adding
appropriate lines to Quartz configuration (see
LocalNodeManager.java:87-90).</li>
<li>Wait until we implement it - I've created an issue <a href="https://jira.evolveum.com/browse/MID-3347" target="_blank">MID-3347</a> for this.<br>
</li>
</ol>
<p>As for the first one (externally defined data source):
Please see <a href="https://wiki.evolveum.com/display/midPoint/Repository+Configuration#RepositoryConfiguration-Datasourceconfiguration" target="_blank">https://wiki.evolveum.com/disp<wbr>lay/midPoint/Repository+Config<wbr>uration#RepositoryConfiguratio<wbr>n-Datasourceconfiguration</a>
on how to configure midPoint repository with the data
source. This data source will be used also by Quartz, if
not overriden in <taskManager> section. It should
work but I don't remember if someone actually tested
this.</p>
<p>Concerning autoReconnect: I have no experiences with
this setting. I agree with you that about the hesitation
of using it in production environment. If really needed,
I'd recommend to separate midPoint repository
configuration from Quartz configuration by using
different JDBC URLs for the two: the standard one for
the repository and the one with "autoReconnect=true" for
Quartz. In this way, potential negative effects should
be restricted to task management functionality only.
But, overall, I'd suggest trying to eliminate the
problem by setting validationQuery first.<br>
</p>
<p>Best regards,<br>
</p>
<pre cols="72">Pavol Mederly
Software developer
<a href="http://evolveum.com" target="_blank">evolveum.com</a>
</pre>
<div>
<div>
<div>On 14.08.2016 22:40, Florin. Stingaciu wrote:<br>
</div>
</div>
</div>
<blockquote type="cite">
<div>
<div>
<div dir="ltr">Hello,
<div><br>
</div>
<div>I'm trying to configure an active/active
configuration, and experiencing some issues with
the Quartz scheduler. The SQL connection seems
to timeout quite often and result in many
warning messages. I'm also experiencing some
errors -- as the timeout closes the connection,
some processes are still trying to commit using
that stale handler. </div>
<div><br>
</div>
<div>Here are some relevant logs from both
midPoint instances in this cluster: <a href="http://pastebin.com/JFjqrLnT" target="_blank">http://pastebin.com/J<wbr>FjqrLnT</a> <a href="http://pastebin.com/1vy4iYPY" target="_blank">http://pastebin.com/1v<wbr>y4iYPY</a><br>
<br>
Also here's the relevant portion of my
config.xml file: <a href="http://pastebin.com/WbydcCC8" target="_blank">http://pastebin.com/Wbyd<wbr>cCC8</a></div>
<div><br>
Following the suggestions in the warnings, I've
started to look at autoReconnect propriety of
the JDBC connector and applied it to my configs
like so:</div>
<div><br>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><span><jdbcUrl>jdbc:mysql://SERVER:3<wbr>306/midpoint_dev?autoReconnect<wbr>=true</jdbcUrl></span></blockquote>
<div><br>
</div>
<div>Since implementing this change, the errors
and warnings seem to have disappeared. I will
continue to monitor the logs and ensure this
actually the case. </div>
<div><br>
</div>
<div>Reading the mysql docs, I found that this
is not recommended as this may cause data
inconsistency issues and that stale connection
exceptions should be properly caught within
the application. Namely:</div>
<div><br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex">The
use of this feature is not recommended,
because it has side effects related to session
state and data consistency when applications
don't handle SQLExceptions properly, and is
only designed to be used when you are unable
to configure your application to handle
SQLExceptions resulting from dead and stale
connections properly.</blockquote>
<div><br>
Do you have any recommended configuration for
this scenario? I'd like to move my current
production environment in an active active
configuration, however as of right now I'm
hesitant to do so in order to avoid any data
corruption. Especially since it's quite
difficult to test for data consistency issues
that may arise from using autoReconnect in my
dev environment. <br>
<br>
Thanks, </div>
<div>-F </div>
<div> </div>
</div>
</div>
<br>
<fieldset></fieldset>
<br>
</div>
</div>
<pre>______________________________<wbr>_________________
midPoint mailing list
<a href="mailto:midPoint@lists.evolveum.com" target="_blank">midPoint@lists.evolveum.com</a>
<a href="http://lists.evolveum.com/mailman/listinfo/midpoint" target="_blank">http://lists.evolveum.com/mail<wbr>man/listinfo/midpoint</a>
</pre>
</blockquote>
</div>
______________________________<wbr>_________________
midPoint mailing list
<a href="mailto:midPoint@lists.evolveum.com" target="_blank">midPoint@lists.evolveum.com</a>
<a href="http://lists.evolveum.com/mailman/listinfo/midpoint" rel="noreferrer" target="_blank">http://lists.evolveum.com/mail<wbr>man/listinfo/midpoint</a>
</blockquote></div>
</div>
<fieldset></fieldset>
<pre>______________________________<wbr>_________________
midPoint mailing list
<a href="mailto:midPoint@lists.evolveum.com" target="_blank">midPoint@lists.evolveum.com</a>
<a href="http://lists.evolveum.com/mailman/listinfo/midpoint" target="_blank">http://lists.evolveum.com/<wbr>mailman/listinfo/midpoint</a>
</pre>
</blockquote>
</div></div></div><br>______________________________<wbr>_________________<br>
midPoint mailing list<br>
<a href="mailto:midPoint@lists.evolveum.com">midPoint@lists.evolveum.com</a><br>
<a href="http://lists.evolveum.com/mailman/listinfo/midpoint" rel="noreferrer" target="_blank">http://lists.evolveum.com/<wbr>mailman/listinfo/midpoint</a><br>
<br></blockquote></div><br></div>