MySQL Bugs: #26156: hangs when joining to multi-database view

Bug #26156	hangs when joining to multi-database view
Submitted:	7 Feb 2007 18:21	Modified:	12 May 2007 16:05
Reporter:	Joe Murray	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S1 (Critical)
Version:	5.0.24a	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
Context: integrating applications by replacing table in one with view to tables in two others.
Symptom: databases hang, processlist shows query is in statistics state. One query that should return 0 rows fairly quickly was running for 8.5 hours before database instance restarted, during which time tables appeared locked, DESC didn't return, etc.  The query is:
# Query_time: 30888  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
use netofficedev;
SELECT topic.id, topic.project, topic.owner, topic.subject, topic.status, topic.last_post, topic.posts, topic.published, mem.id, mem.login, mem.name, mem.email_work, pro.id, pro.name, mem2.login, mem2.email_work, mem2.name
FROM no_topics topic
LEFT OUTER JOIN sso_no_members mem ON mem.id = topic.owner
LEFT OUTER JOIN sso_no_projects pro ON pro.id = topic.project
LEFT OUTER JOIN no_posts pos ON pos.topic = topic.id and pos.created = topic.last_post
LEFT OUTER JOIN sso_no_members mem2 ON mem2.id = pos.member
 WHERE topic.project = '2' ORDER BY topic.last_post DESC;

NB: no_topics has no rows, and the sso_no_members and sso_no_projects views each have 2 rows, and can be accessed without problems and with good performance (<<1sec).

Here are the View definitions:

CREATE VIEW sso_no_projects AS
SELECT cg.id AS id, 1 AS organization, cm.contact_id AS owner, dcg.field_priority_value AS priority, dcg.field_status_value AS status, cg.name AS name, cg.description AS description, '' AS url_dev, dog.website AS url_prod, dn.created AS created, dn.changed AS modified, 1 AS published, 33554432 AS upload_max, 0 AS phase_set, 0 AS type
FROM civicrm.civicrm_group AS cg INNER JOIN drupal.og_civicrm AS doc ON cg.domain_id=doc.did AND cg.id=doc.gid INNER JOIN drupal.og AS dog ON doc.nid=dog.nid INNER JOIN drupal.og_uid AS dou ON doc.nid=dou.nid INNER JOIN civicrm.civicrm_uf_match AS cm ON dou.uid=cm.uf_id INNER JOIN drupal.node AS dn ON doc.nid=dn.nid INNER JOIN drupal.content_type_group AS dcg ON dn.nid=dcg.nid
WHERE
dou.is_admin=1;

CREATE VIEW sso_no_members AS
SELECT cc.id AS id, co.organization_name AS organization, 
du.name AS login, du.pass AS password, 
CONCAT(ci.first_name, ' ', ci.last_name) AS name, NULL AS title, 
cew.email AS email_work, ceh.email AS email_home,
cpw.phone AS phone_work, cph.phone AS phone_home,
IF(cphm.phone,cphm.phone,cpwm.phone) AS mobile,
IF(cphf.phone,cphf.phone,cpwf.phone) AS fax,
NULL AS comments, NULL AS profil, 
du.created AS created, 0 AS logout_time, NULL AS last_page,
0 AS timezone, 0 AS location, 0 AS supervisor, 0 AS agent, 
'default' AS theme, 0 AS layout, 1 as timeticket
FROM civicrm.civicrm_contact AS cc 
INNER JOIN civicrm.civicrm_uf_match AS cm ON cc.id=cm.contact_id AND cc.domain_id=cm.domain_id 
INNER JOIN drupal.users AS du ON cm.uf_id=du.uid 
INNER JOIN civicrm.civicrm_individual AS ci ON ci.contact_id=cc.id 
LEFT JOIN civicrm.civicrm_organization AS co ON co.contact_id=cc.id 
LEFT JOIN civicrm.civicrm_location_work AS clw ON clw.entity_id=cc.id 
LEFT JOIN civicrm.civicrm_email AS cew ON clw.id=cew.location_id 
LEFT JOIN civicrm.civicrm_phone_phone AS cpw ON clw.id=cpw.location_id 
LEFT JOIN civicrm.civicrm_phone_mobile AS cpwm ON clw.id=cpwm.location_id 
LEFT JOIN civicrm.civicrm_phone_fax AS cpwf ON clw.id=cpwf.location_id 
LEFT JOIN civicrm.civicrm_location_home AS clh ON clh.entity_id=cc.id 
LEFT JOIN civicrm.civicrm_email AS ceh ON clh.id=ceh.location_id 
LEFT JOIN civicrm.civicrm_phone_phone AS cph ON clh.id=cph.location_id 
LEFT JOIN civicrm.civicrm_phone_mobile AS cphm ON clw.id=cpwm.location_id 
LEFT JOIN civicrm.civicrm_phone_fax AS cphf ON clw.id=cpwf.location_id;

USE civicrm;

CREATE VIEW civicrm_location_work AS 
SELECT id, entity_id FROM civicrm.civicrm_location
WHERE entity_table='civicrm_contact' AND location_type_id = 2;

CREATE VIEW civicrm_location_home AS 
SELECT id, entity_id FROM civicrm.civicrm_location 
WHERE entity_table='civicrm_contact' AND location_type_id=2;

CREATE VIEW civicrm_phone_phone AS
SELECT phone, location_id FROM civicrm.civicrm_phone WHERE phone_type='Phone';

CREATE VIEW civicrm_phone_mobile AS
SELECT phone, location_id FROM civicrm.civicrm_phone WHERE phone_type='Mobile';

CREATE ALGORITHM=MERGE VIEW civicrm_phone_fax AS
SELECT phone, location_id FROM civicrm.civicrm_phone WHERE phone_type='Fax';

How to repeat:
restart MySQL. Rerun query.

Suggested fix:
I'm thinking of trying to use ALGORITHM=TEMPTABLE, but don't know if that will have any effect.

The query can be simplified and still cause the hang as follows:
SELECT topic.id
FROM no_topics topic
LEFT OUTER JOIN sso_no_members mem ON mem.id = topic.owner
LEFT OUTER JOIN no_posts pos ON pos.topic = topic.id
AND pos.created = topic.last_post
LEFT OUTER JOIN sso_no_members mem2 ON mem2.id = pos.member;

Removing any of the outer joins solves the problem with causing a hang.

the no_posts table has no rows.

Thank you for the report.

Please try using current 5.0.33 version and you can repeat hang, please provide output of SHOW CREATE TABLE for each underlying table.

The ISP won't update the version of MySQL till later. I found that using ALGORITHM=TEMPTABLE works around issue.

I've attached the SHOW CREATE TABLE outputs to the files tab.

show create table output.txt

Attachment: show create table.txt (text/plain), 12.77 KiB.

Please also provide output of SHOW CREATE TABLE civicrm_domain

Here's the output of SHOW CREATE TABLE civicrm_domain:

Table  Create Table  
civicrm_domain CREATE TABLE `civicrm_domain` (\n  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Domain ID',\n  `name` varchar(64) collate utf8_unicode_ci default NULL COMMENT 'Name of Domain / Organization',\n  `description` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'Description of Domain.',\n  `contact_name` varchar(64) collate utf8_unicode_ci default NULL COMMENT 'Name of the person responsible for this domain',\n  `email_domain` varchar(64) collate utf8_unicode_ci default NULL COMMENT 'The domain from which outgoing email for this domain will appear to originate',\n  `email_return_path` varchar(64) collate utf8_unicode_ci default NULL COMMENT 'The domain from which outgoing email for this domain will appear to originate',\n  `config_backend` text collate utf8_unicode_ci COMMENT 'Backend configuration.',\n  `config_frontend` text collate utf8_unicode_ci COMMENT 'Frontend configuration.',\n  PRIMARY KEY  (`id`),\n  UNIQUE KEY `UI_name` (`name`)\n) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Please also provide output of SHOW CREATE TABLE civicrm_location

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Feedback is still needed.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".