ProjeQtOr free project management software - [SOLVED] Mysql lock wait timeout - ProjeQtOr
 

[SOLVED] Mysql lock wait timeout

More
06 Nov 2019 15:07 #1 by caccia
Hello,

We've suddenly started hitting very long delays in our production instance when saving data. The issue appears rather randomly, it seems to occur much more on some projects than on others, and mainly when saving logged work or left work for example.

As you can see from the log, it is an old version - hasn't been updated in a long time because it was nice and stable ! So I don't know why all of a sudden the surge in these errors/delays.

Errors which appear in the log:
2019-11-06 14:47:06.435 ***** ERROR ***** [V6.5.7] Exception-[HY000] SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
2019-11-06 14:47:06.436 ***** ERROR ***** [V6.5.7]    For query : update projecthistory set realWork='2456.6'  where id=235954
--
2019-11-06 14:47:21.139 ***** ERROR ***** [V6.5.7] Exception-[HY000] SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
2019-11-06 14:47:21.140 ***** ERROR ***** [V6.5.7]    For query : update planningelement set realWork='234644.52342' , leftWork='16355.93'  where id=3
--
2019-11-06 14:47:58.566 ***** ERROR ***** [V6.5.7] Exception-[HY000] SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
2019-11-06 14:47:58.566 ***** ERROR ***** [V6.5.7]    For query : update kpivalue set kpiValue='1890.3386454183'  where id=1612
--
2019-11-06 14:48:13.396 ***** ERROR ***** [V6.5.7] Exception-[HY000] SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
2019-11-06 14:48:13.396 ***** ERROR ***** [V6.5.7]    For query : update projecthistory set realWork='234644.52342' , leftWork='16355.93'  where id=235928
--
2019-11-06 14:48:20.178 ***** ERROR ***** [V6.5.7] Exception-[40001] SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction
2019-11-06 14:48:20.178 ***** ERROR ***** [V6.5.7]    For query : update planningelement set realWork='2453.8' , plannedWork='3321.8' , expectedProgress='538' , marginWork='-2865.8' , marginWorkPct='-628'  where id=90177
--
2019-11-06 14:50:26.172 ***** ERROR ***** [V6.5.7] Exception-[HY000] SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
2019-11-06 14:50:26.172 ***** ERROR ***** [V6.5.7]    For query : update planningelement set realWork='773.1' , leftWork='185.55'  where id=90950
--
2019-11-06 14:51:12.902 ***** ERROR ***** [V6.5.7] Exception-[HY000] SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
2019-11-06 14:51:12.902 ***** ERROR ***** [V6.5.7]    For query : update planningelement set realWork='234648.02342' , leftWork='16348.93'  where id=3
--
2019-11-06 14:51:19.051 ***** ERROR ***** [V6.5.7] Exception-[HY000] SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction
2019-11-06 14:51:19.053 ***** ERROR ***** [V6.5.7]    For query : update projecthistory set realWork='773.1' , leftWork='185.55'  where id=236183
--

I also regularly have some deadlocks logged in Mysql, on planningelement or kpihistory tables.

I don't expect a bugfix of course on this old version, but has anyone else encountered this type of error ? Any ideas for getting rid of these ? (I have tried restarting the server, but the errors re-appear).

Thanks!

Please Connexion or Create an account to join the conversation.

More
08 Nov 2019 09:45 #2 by babynus
This may happen when planning calculation is ongoin, for big projects.
On old versions, objets are locked at start of planning, and freed only after calculation is complete, so it may take some time...

We improved this on V8.0 to lock items as late as possible (we calculate all things with some caching, and when calculation is complete update only what has to be changed)
So you need to migrate to latest stable release to reduce this effect.
Or try and reduce planning calculation during work input.

Babynus
Administrator of ProjeQtOr web site

Please Connexion or Create an account to join the conversation.

More
08 Nov 2019 10:20 #3 by caccia
Hello,

FYI, finally found the cause - due to a typo, someone had mis-typed a date and instead of '2019-MM-DD' we had an activity start date of '201-MM-DD'.
So, as you suspected, planning calculations were a slightly overloaded, as everything was recalculated over 2000 years on every update ! :pinch:

Anyway, thanks for the insight - and yes, we have already started planning for migration, to catch up to the latest great features.
Thanks for the feedback !

Please Connexion or Create an account to join the conversation.

Moderators: babynusprotion
Time to create page: 0.031 seconds