Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
By palmergroup on
Hi, I was messing around w/ views and everything just basically went downhill. Here is what I got back from my host support because I wasn't able to 1. access the site and 2. access mysql.
Support response: 2006/10/29 21:18
I does look like a locked query is the problem. Here is a sample of your running processes:| 5531539 | layout | 10.1.3.18:55236 | layout_db | Query | 39824 | statistics | SELECT node.nid, node.title AS node_title, node.changed AS nod
e_changed, node.created AS node_create |
| 5535645 | layout | 10.1.3.18:56296 | layout_db | Query | 39728 | statistics | SELECT node.nid, node.title AS node_title, node.changed AS nod
e_changed, node.created AS node_create |
| 5538730 | layout | 10.1.3.18:39476 | layout_db | Query | 39649 | Locked | UPDATE users SET access = 1162144907 WHERE uid = 1
|
| 5539249 | layout | 10.1.3.18:39601 | layout_db | Query | 39636 | Locked | SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid =
s.uid WHERE s.sid = 'b681e5077cb033ff3 |
| 5541983 | layout | 10.1.3.18:40415 | layout_db | Query | 39566 | Locked | SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid =
s.uid WHERE s.sid = 'b358a20b3bff07aa1 |
| 5544138 | layout | 10.1.3.18:41047 | layout_db | Query | 39512 | Locked | SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid =
s.uid WHERE s.sid = 'b358a20b3bff07aa1 |
| 5546123 | layout | 10.1.3.18:41538 | layout_db | Query | 39463 | Locked | SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid =
s.uid WHERE s.sid = 'b358a20b3bff07aa1 |
| 5559666 | layout | 10.1.3.18:51814 | layout_db | Query | 39131 | Locked | SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid =
s.uid WHERE s.sid = '5a0075eec9e5a21d1 |
| 5565396 | layout | 10.1.3.18:53169 | layout_db | Query | 38982 | Locked | SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid =
s.uid WHERE s.sid = '5a0075eec9e5a21d1 |
| 5565508 | layout | 10.1.3.18:53191 | layout_db | Query | 38979 | Locked | SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid =
s.uid WHERE s.sid = '5a0075eec9e5a21d1 |
| 5565649 | layout | 10.1.3.18:53228 | layout_db | Query | 38976 | Locked | SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid =
s.uid WHERE s.sid = '5a0075eec9e5a21d1 |
| 5565828 | layout | 10.1.3.18:53278 | layout_db | Query | 38971 | Locked | SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid =
s.uid WHERE s.sid = '5a0075eec9e5a21d1 |I've killed off all of your queries, so you should be back up and running. My best guess is that you have a script that locks the sessions table, but can exit without unlocking the table.
Even after killing of your processes, I still see the following:
| 7380855 | layout | 10.1.3.23:42060 | layout_db | Query | 170 | statistics | SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node.created AS node_create |
| 7380853 | layout | 10.1.3.17:37870 | layout_db | Query | 170 | statistics | SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node.created AS node_create |
| 7380859 | layout | 10.1.3.15:41611 | layout_db | Query | 170 | statistics | SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node.created AS node_create |
| 7380860 | layout | 10.1.3.23:42061 | layout_db | Query | 170 | statistics | SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node.created AS node_create |
| 7380861 | layout | 10.1.3.23:42062 | layout_db | Query | 170 | statistics | SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node.created AS node_create |
| 7380862 | layout | 10.1.3.15:41612 | layout_db | Query | 170 | statistics | SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node.created AS node_create |
| 7380863 | layout | 10.1.3.23:42063 | layout_db | Query | 170 | statistics | SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node.created AS node_create |
| 7380866 | layout | 10.1.3.23:42064 | layout_db | Query | 170 | statistics | SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node.created AS node_create |
| 7380867 | layout | 10.1.3.12:60574 | layout_db | Query | 170 | statistics | SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node.created AS node_create |
| 7380868 | layout | 10.1.3.12:60575 | layout_db | Query | 170 | statistics | SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node.created AS node_create |
| 7380869 | layout | 10.1.3.23:42065 | layout_db | Query | 170 | statistics | SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node.created AS node_create |
| 7380873 | layout | 10.1.3.12:60578 | layout_db | Query | 170 | statistics | SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node.created AS node_create |
| 7380874 | layout | 10.1.3.12:60579 | layout_db | Query | 170 | statistics | SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node.created AS node_create |
| 7380875 | layout | 10.1.3.12:60580 | layout_db | Query | 170 | statistics | SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node.created AS node_create |
| 7380876 | layout | 10.1.3.15:41613 | layout_db | Query | 170 | statistics | SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node.created AS node_create |
| 7380878 | layout | 10.1.3.24:54600 | layout_db | Query | 170 | statistics | SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node.created AS node_create |Again, my best guess is that your script is exiting before closing the connectiong, but I'm not certain.
Support response: 2006/10/30 02:00
The query which seems to be causing problems is the following:SELECT node.nid, node.title AS node_title, node.changed AS node_changed, node.created AS node_created, users.name AS users_name, users.uid AS users_uid, node.nid AS node_nid FROM node node LEFT JOIN term_node term_node ON node.nid = term_node.nid LEFT JOIN term_node term_node2 ON node.nid = term_node2.nid LEFT JOIN term_node term_node3 ON node.nid = term_node3.nid LEFT JOIN term_node term_node4 ON node.nid = term_node4.nid LEFT JOIN term_node term_node5 ON node.nid = term_node5.nid LEFT JOIN term_node term_node6 ON node.nid = term_node6.nid LEFT JOIN term_node term_node7 ON node.nid = term_node7.nid LEFT JOIN term_node term_node8 ON node.nid = term_node8.nid LEFT JOIN term_node term_node9 ON node.nid = term_node9.nid LEFT JOIN term_node term_node10 ON node.nid = term_node10.nid LEFT JOIN term_node term_node11 ON node.nid = term_node11.nid LEFT JOIN term_node term_node12 ON node.nid = term_node12.nid LEFT JOIN term_node term_node13 ON node.nid = term_node13.nid LEFT JOIN term_node term_node14 ON node.nid = term_node14.nid LEFT JOIN term_node term_node15 ON node.nid = term_node15.nid LEFT JOIN term_node term_node16 ON node.nid = term_node16.nid LEFT JOIN term_node term_node17 ON node.nid = term_node17.nid LEFT JOIN term_node term_node18 ON node.nid = term_node18.nid LEFT JOIN users users ON node.uid = users.uid WHERE (!(term_node.tid = '1')) AND (!(term_node2.tid = '6')) AND (!(term_node3.tid = '7')) AND (!(term_node4.tid = '8')) AND (!(term_node5.tid = '2')) AND (!(term_node6.tid = '9')) AND (!(term_node7.tid = '10')) AND (!(term_node8.tid = '3')) AND (!(term_node9.tid = '11')) AND (!(term_node10.tid = '23')) AND (!(term_node11.tid = '4')) AND (!(term_node12.tid = '12')) AND (!(term_node13.tid = '13')) AND (!(term_node14.tid = '14')) AND (!(term_node15.tid = '5')) AND (!(term_node16.tid = '15')) AND (!(term_node17.tid = '16')) AND (node.status = '1') AND (!(term_node18.tid = '22'))
The tables look well indexed but your threads are all getting stuck in the "statistics" state.
I tried cutting the query down to only use the left joins from term_node thru term_node5 and it appears that you are going to be returning a TON of data. Don't know if you can optimize this query but this seems to be the one causing problems.
Best Regards,
David Bartle
Customer Support
(mt) Media Temple
877-578-4000
310-564-2007Support response: 2006/10/30 02:09
I ran an analyze table on all of the tables involved in this join (users, node, term_node).This at least allowed me to run the full query. However, I HIGHLY suggest that you optimize this query. The query as written above returns 327689 rows, most of which are duplicates.
Also, you are now getting a PHP error:
Fatal error: Allowed memory size of 104857600 bytes exhausted (tried to allocate 12 bytes) in /home/2003/domains/layout.com/html/includes/database.mysql.inc on line 136.
If your PHP script is attempting to allocate over 100 megs of memory, you should probably think about optimizing it.
Let us know if you have any furthur questions.
Best Regards,
David Bartle
Customer Support
(mt) Media Temple
877-578-4000
310-564-2007
Comments
Wow, quite a query! 100 megs
Wow, quite a query! 100 megs and still going!
Was it a custom view you created? Either your view was too ambitious or merlinofchaos should hear about this (in http://drupal.org/project/issues/views). Perhaps a missing DISTINCT clause somewhere in the query generation.
hi, thanks for the reply. I
hi, thanks for the reply. I was wondering, is there a way form me to just delete this specific view? if so, could you tell me what i would look for in mysql? i am not very good at sql.
Hmm... can't you access the
Hmm... can't you access the /admin/views page to delete (or modify) the view?
If you need to go to the database, I don't know where exactly the views module stores its queries there, and probably the initial query is different from what you see here because Drupal rewrites them for various reasons. You would have to ask merlin, as I said.
Otherwise, I would search in the views_* tables for (part of) the query and I would try making it SELECT DISTINCT etc. But... if the query was generated automatically by views, then there is a problem in the module which should be solved.
thanks for the help! that
thanks for the help! that solved the issue.