By Jasonrj on

I've been browsing my site and doing nothing but updating .css files and refreshing a page to test these changes. Suddenly I'm receiving this error after about 2 minutes of any page on my site trying to load. This problem only occurs while I'm logged in, if I try from another browser or incognito I have no problems loading the site instantly.

I had a similar error recently and the error involved something with my menu database. Someone told me how to drop or clear (not sure of terminology) the menu cache in PHPMyAdmin and that solved it. Is there something similar that can be done for me now, and why is this happening? Should I change my cache settings?

Uncaught exception thrown in session handler.
PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction: SELECT 1 AS expression FROM {sessions} sessions WHERE ( (sid = :db_condition_placeholder_0) AND (ssid = :db_condition_placeholder_1) ) FOR UPDATE; Array ( [:db_condition_placeholder_0] => xTnkLkf7GsMrWYTTGi3LDySaVi84unQn1Y_UYi21AaQ [:db_condition_placeholder_1] => ) in _drupal_session_write() (line 206 of /nfs/c08/h04/mnt/126855/domains/mydomain.com/html/includes/session.inc).

***UPDATE***: It appears the problem is with my web host (Media Temple) and how they handle MySQL databases. I'm no expert but they apparently will isolate heavy users temporarily so the entire shared hosting environment doesn't get bogged down. This causes problems with Drupal 7 and has completely crashed/corrupted 3 separate installs of mine so far as well as countless other Media Temple customers. In each of these instances I was doing nothing more than testing a new site, updating styles and refreshing frequently. I'm NOT a high resource user (see last paragraph).

I opened a ticket with them and they are well aware of the issue, but said "... the current architecture of the (gs) Grid-Service SmartPool may not be up to your site's specific needs." Speaking of my "needs" to install and use Drupal 7 without complication.

I like Media Temple, but you can Google this issue, there is a long thread here on Drupal.org (http://drupal.org/node/1041298) where a Media Temple employee named Sara goes into some detail about the issue.

Their solution is to completely dump the database, make some changes, and re-create it (beyond my comfort level) or use their own 1-click install (which has been lagging behind on security updates for at least the several weeks I've been using it now). Also, the 1-click install may not completely solve the problem. Just tonight while I was uploading a .css file, clearing caches, and refreshing the site maybe 4 or 5 times my site crashed yet again. This time it wasn't permanent though, it began working again after about an hour of spitting errors after 5+ minute timeouts. Perhaps this is the best solution available with Media Temple?

I build all my sites with Drupal, and since I'll be moving more and more onto D7, it looks like I may be forced to find a new host. It should also be mentioned that I'm by no means a high resource user. Media Temple gauges use using their custom "GPU" monitor. With my most basic Grid-Service package I'm allotted 2,000 GPUs per month. I usually use less than 10, I peaked at 27 one month.

⋅ Categories: Drupal 7.x

Comments

John_B’s picture

John_B commented

It is probably a server error rather than a Drupal error. Best tune mysql, at least to set a longer timeout, and if you want the site to run faster, ensure that you have allocated sufficient memory to mysql caching.

If you are on shared hosting, and cannot do that, just complain to the host, or upgrade to your own server. Probably one of the other sites you are sharing with is hammering the server, so you have less resources available. That is a risk on shared hosting and on some VPSs.

Digit Professionals specialising in Drupal, WordPress & CiviCRM support for publishers in non-profit and related sectors

Jasonrj’s picture

Jasonrj commented

A longer time out? As I said, it's timing out after about 2 minutes.

This is only occurring on one session. I can browse the site perfectly fine from a different browser or incognito/private mode. I can even login to the same username and browse everything instantly and perfectly fine.

jcontraros’s picture

jcontraros commented

Try this.. http://madhavvyas.blogspot.com/2011/07/general-error-cant-createwrite-to...

John_B’s picture

John_B commented

Sounds like a mysql tuning problem. The above post suggest increasing innodb_buffer_pool to 500M. Which may be a good idea. Unless the site is on shared hosting, or a VPS with only 500M. In which case it would be mad!

Digit Professionals specialising in Drupal, WordPress & CiviCRM support for publishers in non-profit and related sectors

rafinskipg’s picture

rafinskipg commented

Did you found a solution?

If not, try this:
http://www.latindevelopers.com/articulos/mysql/mysql-error-code-2006.php

Then tell us... I'm interested

Jasonrj’s picture

Jasonrj commented

I didn't really find a solution, but I found the cause of the problem. I updated my initial post with all the details, but it's a problem isolated to Media Temple customers such as myself.

rafinskipg’s picture

rafinskipg commented

I have the same problem and i am not in Media Temple. I have my own apache server and my database...
:/
Did you use dsm()¿

Jasonrj’s picture

Jasonrj commented

Sorry, I don't know what dsm() is. I just ran a default install, and did nothing special. Media Temple said in their thread that using their self-installer should solve the problem. That seems to be the case for now, but I don't know how they're solving it. I just know it has something to do with the database type or something. The only issue I've had since using their self-installer has been a one-time issue that lasted about 30 minutes of the site not responding, but instead of corrupting and destroying the whole site, it just went back to working fine after a while.

rafinskipg’s picture

rafinskipg commented

Another thing... are you using dsm() function?

I was looking, dsm() writes a very big record in the session table... maybe the link i sent you in the first comment may help...

Tell us

rafinskipg’s picture

rafinskipg commented

I have been looking in posts...

Maybe a INNODB problem?
http://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeou...

dga5000’s picture

dga5000 commented

I had this error message whilst performing a number of actions in Drupal 7. For me the solution turned out to be updating the version of PHP.

My Solution

Open the Acquia Dev Desktop Control Panel
Click Settings
Click the 'Config' tab
Under Default PHP versions tick: PHP 5.3.9
Click OK

DGA

Druper’s picture

Druper commented

I just had the same problem with an MT site (D 7.15), here's the response I got from Media Temple:

There is an issue with Drupal 7 when it comes to MySQL. Many tables need to be converted to MyISAM to work correctly on the (gs) Grid-Service. They are as follows, and you should be able to perform the conversion using phpMyAdmin:

drupal_cache_* (any tables that start with this need conversion)
drupal_variable

Does this make any sense and is it safe to do? Will Drupal be able to recognize/communicate with these tables?

I am a MySQL dope, so any advice is greatly appreciated.

John_B’s picture

John_B commented

In principle it should work and be safe, though the process of changing to MyISAM sometimes introduces errors. If you are only doing it cache tables, no problem, but corrupting the variables table might be more troubling. Anyway, if you take a database backup first you can always roll back.

Digit Professionals specialising in Drupal, WordPress & CiviCRM support for publishers in non-profit and related sectors

Druper’s picture

Druper commented

I tried their solution, changing only the CACHE_* and VARIABLE tables. It was a time consuming, tedious SOB of a job, cache table was very cranky about changing but finally did, variable table just wouldn't alter. I finally had to copy it, alter the copy and rename the two tables. Even that resulted in a deformed variable table that had to be manually corrected.

I suppose it all would have been easier if I had copied the DB and done it locally.

Anyway, the change seemed to work as advertised, no more errors with the added benefit of generally quicker browser response.

So I thought, what the heck, let's try it with a fully MyISAM DB. Another admin had created just that (he was smart enough to do it locally), so I plugged it in to the site. So far, no problems and all seems to be ticking along smartly.

Now I just have to figure out what those extra settings (PACK_KEYS, CHECKSUM, DELAY_KEY_WRITE, and ROW_FORMAT) are for. (That's a rhetorical question, don't anybody waste time on an answer, thanks.)

Thanks for the help!

Jasonrj’s picture

Jasonrj commented

FYI, I tried their solution and it appeared to work for me as well--for a little while--then I ended up with the same errors again.

I ended up paying for their dedicated MySQL service in addition to my Grid-Service plan and haven't had any issues since, and all my sites are incredibly faster (though I'm now paying twice as much).

Triskelion’s picture

Triskelion commented

I have a large site which was/became corrupted during a DDOS attack by a botnet. I could not do anything administrative because of these lock races on cache clearing. When I did manage to look at the logs, they were filled with PDO exceptions: Lock Wait Timeouts.

I had created a backup using:

mysqldump -p -B > MyDB.defs

After reading your comment, I looked at the table definitions, and I found a mixture of InnoDB and MyISAM tables. I changed all references to InnoDB over to MyISAM, and restored the database using the new definitions.


cat MyDB.defs | sed -e "s/InnoDB/MyISAM/g" > MyISAMDB.defs
mysql -p < MyISAMDB.defs

Now all tables are MyISAM, and I went to admin/config/development/performance, clicked on the 'Clear Caches' button, and they cleared without a problem in seconds. The whole site runs faster.

I stopped using InnoDB in my own work years ago. I don't anticipate any problems, but I intend to wait a few days just to be sure. Then I will probably do the MyISAM conversion on all my Drupal sites.

Why has this not been standardized in the coding standards?

John_B’s picture

John_B commented

Drupal 7 (and recent versions of Wordpress) default to creating Innodb tables where possible whereas older versions used MyIsam. An older Drupal site which has been upgraded often has a mix. Innodb should be faster for writes and more stable. Its speed depends largely on the version of MySQL you are using (later versions, and or MariaDB or Percona and so on being faster), and of the extent to which your configurations in my.ini are tuned for one or the other engine. The advantage of using all one type of table in every site on the server is that you can tune you my.ini according to the needs of your chosen type.

Digit Professionals specialising in Drupal, WordPress & CiviCRM support for publishers in non-profit and related sectors

Triskelion’s picture

Triskelion commented

Thank you for your response, John.

I have read Narayan Newton's article at http://tag1consulting.com/MySQL_Engines_MyISAM_vs_InnoDB, and he makes an excellent case for InnoDB.

The site in question is not the first to experience this Lock Wait Timeout. In both cases the sites have been in existence since the early days of Drupal 5, and have been upgraded to 6 then 7 (currently 7.16). I never experienced lock wait timeouts before 7.14 The problem has brought two sites to their knees when a botnet launched multiple (failed) login attempts on the site. The caches don't clear, and the site freezes.

In the first case, the login attempts all used the same user name, and I managed to get it under control by blocking IP's. Three weeks and 6000 blocked ip's later that site is still working.

The second case was similar, except the user names, emails, and IP's were all random. I would restore the database from a dump, restart mysqld, and it took about 5 minutes to load the 'Recent Log Messages' report, which showed several new Lock Wait Timeout messages. Those that I managed to read were given on attempts to clear cache, and cache_bootstrap. Trying to clear the log messages gave me a delay then a WSOD.

Changing the engine to MyISAM on all tables gave me my site back, and it is more responsive than I have ever seen it.

From the title of this issue, I am not the only one experiencing this problem. The causes might vary, but if it has something to do with configuration and load, perhaps some guidelines might be in order. Otherwise, what has triggered this problem in D7?

Version information: Drupal 7.16, php 5.3.14, MySQL 5.1.63

John_B’s picture

John_B commented

I eventually accepted that the cost of decent hosting was worth it. Now I host my Drupal sites to a higher (but far more expensive) standard they run faster on the remote server than on my Windows laptop, (and my clients love the speed their sites load--but I do not make any profit on hosting client sites). But a lot of time and effort and money has gone into sorting out hosting on which such problems are never seen, regardless of the database engine or the complexity of the site. It is really a question of each person making their own judgement about where to strike the balance between quality and price. If using MyISAM enables you to get better performance from the available resources, and you can live with the slightly higher chance of crashed sites owing to db corruption (which I have aso seen on Innodb databases), that is a reasonable choice to make. I have gone the other way and ensured all my sites have Innodb so I can tune my server for that, which I think is also a reasonable choice, albeit not a profitable one for me.

Digit Professionals specialising in Drupal, WordPress & CiviCRM support for publishers in non-profit and related sectors

Triskelion’s picture

Triskelion commented

I am running a LAMP environment on redundant rack-mounted Dell Poweredge servers, and serving over a T1 line.

I too have seen corruption in both MyISAM and InnoDB environments. In most cases a simple REPAIR table command will fix MyISAM tables. I have also seen a single corrupted write to ibdata1 and the ib_logfiles take out every InnoDB table in every database on the server! This is why I perform regular dumps, just in case lightning (literally) strikes twice :-)

The issue is the appearance of Lock Wait Timeout errors with D7. Is there a conflict between Drupal locking and the internal InnoDB row locking? What is new in Drupal 7 that would cause this when the same functional site problems did not cause the errors under D6, or even earlier D7? If it is a server configuration issue, what are the new InnoDB configuration requirements for my.cnf to run D7?

John_B’s picture

John_B commented

This is not normal. Have you set your innodb_lock_wait_timeout to a sufficiently long value for your needs (I suppose to be fixed considering disk speed and the amount of IO traffic being generated by mysql, though just trying 300 seems a good start)?

For me, having good hardware was only part of finding hosting which actually works for D7. The effort and quality of work put into custom tuning was absolutely key to sorting out some problems (including lock timeouts). If you are not sharing your mysql server disk IO with demanding neighbours, it does sound as though there is some bug on your site or some setting on the server which is not quite right.

Digit Professionals specialising in Drupal, WordPress & CiviCRM support for publishers in non-profit and related sectors

likewinters’s picture

likewinters commented

There is an issue with Drupal 7 when it comes to MySQL. Many tables need to be converted to MyISAM to work correctly on the (gs) Grid-Service. They are as follows, and you should be able to perform the conversion using phpMyAdmin:

drupal_cache_* (any tables that start with this need conversion)
drupal_variable

This worked great for me. I used a program called adminer to access my db.

Anonymous’s picture

Anonymous (not verified) commented

To fix this go to your database table xxxx_variable and change Type from innodb To MyISAM

Anonymous’s picture

Anonymous (not verified) commented

the problem is media temple, they use MySQL Engines: MyISAM and drupal is designed to use InnoDB..
Media temple MySQL Engines: MyISAM To save memory.. don't ask me why they are being cheap, but they are offering the same service as lets say GoDaddy and charging the customer a premium price. that is a rip off if you ask me.