My client site has huge data, which they want to download into CSV through View data export module, when multiple users are trying to download the csv file, its resulting in an error.

In log messages I can see error like-

PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction:

When I am trying to see my innodb status through: mysqlengine innodb status\G; its showing deadlock for insert into cache table.

Can anyone help in this?

  • 5
    possible duplicate of How to get rid of Deadlocks and Lock time out type issues? – Shawn Conn Sep 9 '15 at 16:02
  • Also, consider moving caches to memcached or similar solution. – Mołot Sep 9 '15 at 16:11
  • @Shawn Conn, I have already checked that post, tried that, I am getting error while applying patches mentioned in the link contained in that post. Any other way? – user-pj Sep 9 '15 at 16:17
  • 1
    Drupal 7.22 ? If that's indeed your Drupal version, then forget whatever you're working on ... and go find out what was Drupal 7.32 all about ... and upgrade to a more recent Drupal version ... – Pierre.Vriens Sep 9 '15 at 16:31
  • It cant be done now as it can cause various other problem in live environment, is there any other solution to it? – user-pj Sep 9 '15 at 16:46

https://www.drupal.org/project/apdqc is a pretty good solution for the issue you're describing. Install the module and go to the status report page (admin/reports/status); it will then run a gamut of tests and give you advice on things to change in your settings.php, php.ini, and my.cnf files.

The root cause of most of the cache deadlocks is that a truncate query and insert query are in conflict with each other.

| improve this answer | |
  • The same answer was posted under question marked as potential duplicate... – Mołot Sep 9 '15 at 19:33
  • @mikeytown2 I have seen that module, its available in beta version, as far as I know its not suggestable to use beta version in production site. will it be ok to use it in live environment? will it be ok to use it with Drupal 7.22? – user-pj Sep 10 '15 at 8:12
  • @user First step is to upgrade drupal to the latest version. A lot of deadlocks where fixed in 7.25 and a massive security hole was fixed in 7.32. I use the apdqc beta in production without issues. There are some edge cases that need to be addressed in it before the 1.0 version is out. – mikeytown2 Sep 10 '15 at 19:00
  • @mikeytown2, I have upgraded my Drupal version, installed apdqc module, I have converted Cache table collations and Cache table indexes still I am seeing those in my status report, moreover I tried to enable mysqli extensions on my server but unfortunately I couldn't find a relevent place or way to enable them, can you guide me on that as I can see deadlock on cache_menu table and multiple users are not able to download csv report. – user-pj Sep 15 '15 at 10:57
  • I have enabled the extensions, no more operations to do, but the problem is when multiple users try to download the csv report simultaneously then it will result in white screen. – user-pj Sep 15 '15 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.