1

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
  • 5
    possible duplicate of How to get rid of Deadlocks and Lock time out type issues?
    – Shawn Conn
    Sep 9, 2015 at 16:02
  • Also, consider moving caches to memcached or similar solution.
    – Mołot
    Sep 9, 2015 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, 2015 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 ... Sep 9, 2015 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, 2015 at 16:46

1 Answer 1

0

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.

11
  • The same answer was posted under question marked as potential duplicate...
    – Mołot
    Sep 9, 2015 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, 2015 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, 2015 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, 2015 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, 2015 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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