Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have been having intermitant issues between drupal 7.14 and MediaTemple. Specifically I am getting this error:

PDOException: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction: SELECT revision.order_number AS order_number, revision.revision_id AS revision_id, revision.revision_uid AS revision_uid, revision.mail AS mail, revision.status AS status, revision.log AS log, revision.revision_timestamp AS revision_timestamp, revision.revision_hostname AS revision_hostname, revision.data AS data, base.order_id AS order_id, base.type AS type, base.uid AS uid, base.created AS created, base.changed AS changed, base.hostname AS hostname FROM {commerce_order} base INNER JOIN {commerce_order_revision} revision ON revision.revision_id = base.revision_id WHERE (base.order_id IN (:db_condition_placeholder_0)) FOR UPDATE; Array ( [:db_condition_placeholder_0] => 75 ) in DrupalDefaultEntityController->load() (line 196 of /nfs/c03/h01/mnt/81976/domains/wgsource.com/html/includes/entity.inc).

I was told my MT to change the type of the table to MyISAM, and that there was a problem between them and drupal. (see comment #23 here). This has not fixed my issue. I have a grid container with them, and the report, and configuration is as follows. Specifically I am worried about line number 39 of the MySQL report. What can I do to fix this?

MySQL Report

  1 MySQL 5.1.26-rc-5.1.26r  uptime 0 0:9:1         Tue May  8 14:50:04 2012
  2
  3 __ Key _________________________________________________________________
  4 Buffer used         0 of  24.00M  %Used:   0.00
  5   Current       4.49M            %Usage:  18.71
  6 Write hit       0.00%
  7 Read hit        0.00%
  8
  9 __ Questions ___________________________________________________________
 10 Total              23     0.0/s
 11   Com_             17     0.0/s  %Total:  73.91
 12   COM_QUIT         12     0.0/s           52.17
 13   -Unknown          9     0.0/s           39.13
 14   DMS               3     0.0/s           13.04
 15 Slow                0       0/s            0.00  %DMS:   0.00
 16 DMS                 3     0.0/s           13.04
 17   SELECT            3     0.0/s           13.04        100.00
 18   REPLACE           0       0/s            0.00          0.00
 19   DELETE            0       0/s            0.00          0.00
 20   INSERT            0       0/s            0.00          0.00
 21   UPDATE            0       0/s            0.00          0.00
 22 Com_               17     0.0/s           73.91
 23   show_status       9     0.0/s           39.13
 24   show_variab       3     0.0/s           13.04
 25   admin_comma       2     0.0/s            8.70
 26
 27 __ SELECT and Sort _____________________________________________________
 28 Scan                6     0.0/s %SELECT: 200.00
 29 Range               0       0/s            0.00
 30 Full join           0       0/s            0.00
 31 Range check         0       0/s            0.00
 32 Full rng join       0       0/s            0.00
 33 Sort scan           0       0/s
 34 Sort range          0       0/s
 35 Sort mrg pass       0       0/s
 36
 37 __ Query Cache _________________________________________________________
 38 Memory usage   17.07k of  12.00M  %Used:   0.14
 39 Block Fragmnt 100.00%
 40 Hits                0       0/s
 41 Inserts             1     0.0/s
 42 Insrt:Prune       1:1       0/s
 43 Hit:Insert     0.00:1
 44
 45 __ Table Locks _________________________________________________________
 46 Waited              0       0/s  %Total:   0.00
 47 Immediate          19     0.0/s
 48
 49 __ Tables ______________________________________________________________
 50 Open                8 of 1024    %Cache:   0.78
 51 Opened             15     0.0/s
 52
 53 __ Connections _________________________________________________________
 54 Max used            1 of   60      %Max:   1.67
 55 Total              14     0.0/s
 56
 57 __ Created Temp ________________________________________________________
 58 Disk table          2     0.0/s
 59 Table               5     0.0/s
 60 File                5     0.0/s
 61
 62 __ Threads _____________________________________________________________
 63 Running             1 of    1
 64 Cached              0 of    4      %Hit:  92.86
 65 Created             1     0.0/s
 66 Slow                0       0/s
 67
 68 __ Aborted _____________________________________________________________
 69 Clients             0       0/s
 70 Connects            0       0/s
 71
 72 __ Bytes _______________________________________________________________
 73 Sent           18.31k    33.8/s
 74 Received        1.49k     2.8/s

My SQL Configuration

#
# Base Container
#

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
max_heap_table_size = 8M
skip-name-resolve
open_files_limit = 8000

#
# * Basic Settings
#
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
language    = /usr/share/mysql/english
skip-external-locking
bind-address = 0.0.0.0

# For compatibility 
old_passwords   = 1

back_log=20
max_connections=60

# due to mysql internally considering connections from 'user@host1' as a separate user from 'user@host2',  max_user_connections is largely irrelevant in a clustered situation
max_user_connections=16
max_connect_errors=100000


key_buffer_size=24M
max_heap_table_size=4M
read_buffer_size=128K
read_rnd_buffer_size=128K
sort_buffer_size=256K
table_cache=1024
thread_cache_size=4
tmp_table_size=4M
join_buffer_size=4M
wait_timeout=60
thread_concurrency=4
innodb_file_per_table

max_allowed_packet  = 16M
thread_stack        = 128K

# query cache
query_cache_limit   = 1048576
query_cache_size        = 12M
query_cache_type        = 1

# slow query log
log-slow-queries    = /var/log/mysql/mysql-slow.log
long_query_time = 1

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer      = 16M

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock
share|improve this question
1  
It seems like whatever benefit the grid container provides is undermined by the way MT handles MySQL databases: drupal.org/node/1456544 . –  HTTP500 May 8 '12 at 22:27
    
Would this be fixed by upgrading to a dedicated server? –  Reid May 8 '12 at 22:56
1  
I'm not a MT customer so I don't know what to tell you. From reading some of those comments it sounds like MT wants to upsell you to get a "MySQL container". Others mentioned that it might be a problem when the cache tables get too big (in which case you could use memcache for your cache tables). MT's own fix seems to suggest that the variable table should be MyISAM. If it were me I'd go elsewhere. –  HTTP500 May 8 '12 at 23:10
    
Thank you. I will consider your suggestion. I already have a Grid container, but it does not seem to be helping. –  Reid May 8 '12 at 23:33

1 Answer 1

up vote 0 down vote accepted

MT engineers recognize that this is an issue & have provided a fix that is obtainable in 2 ways. The first & recommended method is:

1) Back up your data 2) Remove Drupal from your account 3) Re-install Drupal using the updated 1-click installation 4) Import your data

The second method isn't necessarily the BEST fix, but it has been tested. Note: this is not supported by (mt).

1) Dump the whole DB 2) Dump the variables table. "mysqldump -uUser -p db1234_drupal variable > db1234_drupal.variable.sql 3) Change the 'engine=INNODB' to 'engine=MyISAM' 3. Reimport your table

*Statements like these below, will now work: MySQL thread id 323367, query id 16367943 205.186.184.26 db94731_blair statistics SELECT 1 AS expression FROM variable variable WHERE ( (name = 'icl_manager_role') ) FOR UPDATE

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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