WoW Raid Manager Defect Tracking System
Mantis Bugtracker
Main | My View | View Issues | Change Log | Roadmap | Docs
  

Viewing Issue Simple Details [ Jump to Notes ] [ View Advanced ] [ Issue History ] [ Print ]
ID Category Severity Reproducibility Date Submitted Last Update
0000052 [WoW Raid Manager (WRM)] Raid View/Signup Issues crash always 2008-08-13 15:36 2008-08-20 15:48
Reporter douglasw View Status public  
Assigned To douglasw
Priority high Resolution fixed  
Status closed   Product Version HEAD
Summary 0000052: View Profiles Not Signed Up Crashes on MySQL 4.0.
Description MySQL 4.0 does not support SubQueries. Thus the "NOT IN" query used in this section does not work.
Additional Information
Tags No tags attached.
Attached Files

- Relationships

-  Notes
(0000063)
douglasw (administrator)
2008-08-13 15:39

 Found on the net:

I am trying to run the following command on mysql 4.0.25 :

SELECT COUNT(a.id) FROM steers_content AS a INNER JOIN steers_content_frontpage AS f ON f.content_id = a.id INNER JOIN steers_categories AS cc ON cc.id = a.catid INNER JOIN steers_sections AS s ON s.id = a.sectionid LEFT JOIN steers_users AS u ON u.id = a.created_by LEFT JOIN steers_groups AS g ON a.access = g.id WHERE a.state = 1 AND ( a.publish_up = '0000-00-00 00:00:00' OR a.publish_up <= '2007-02-05 22:18' ) AND ( a.publish_down = '0000-00-00 00:00:00' OR a.publish_down >= '2007-02-05 22:18' ) AND s.published = 1 AND cc.published = 1 AND a.access <= 2 AND s.access <= 2 AND cc.access <= 2 AND a.id NOT IN (SELECT DISTINCT ArticleID FROM steers_contentviews WHERE UserID = 62)

It gives an error. If I remove the last part of the query: AND a.id NOT IN (SELECT DISTINCT ArticleID FROM steers_contentviews WHERE UserID = 62) then it works fine. So I assume that this version of mysql does not support the "not in" part of the query.

Do you know of another way of rewording the query so that mysql 4.0.25 will accept it? (By the way, I am running a later version of mysql on my test machine and the query runs fine but the live server is only running mysql 4.0.25)

ANSWER:

Subqueries were not supported until version 4.1

to the FROM list, add LEFT JOIN steers_contentviews ON a.id = ArticleID AND UserID = 62
and then to the WHERE clause, add AND steers_contentviews.ArticleID IS NULL

-------------------------
Thus changed the following SQL:

$sql = sprintf("SELECT profile_id, username, last_login_time FROM " . $phpraid_config['db_prefix'] . "profile " .
   "WHERE profile_id NOT IN " .
    "(SELECT profile_id FROM " . $phpraid_config['db_prefix'] . "signups " .
     "WHERE raid_id = %s)", quote_smart($raid_id));

To:

$sql = sprintf("SELECT a.profile_id, a.username, a.last_login_time " .
"FROM " . $phpraid_config['db_prefix'] . "profile AS a " .
"LEFT JOIN " . $phpraid_config['db_prefix'] . "signups ON a.profile_id = " . $phpraid_config['db_prefix'] . "signups.profile_id " .
"AND " . $phpraid_config['db_prefix'] . "signups.raid_id = %s " .
"WHERE " . $phpraid_config['db_prefix'] . "signups.profile_id IS NULL", quote_smart($raid_id));
(0000064)
douglasw (administrator)
2008-08-20 15:48

 Released in v3.5.0 Final

- Issue History
Date Modified Username Field Change
2008-08-13 15:36 douglasw New Issue
2008-08-13 15:36 douglasw Status new => assigned
2008-08-13 15:36 douglasw Assigned To => douglasw
2008-08-13 15:39 douglasw Status assigned => resolved
2008-08-13 15:39 douglasw Fixed in Version => 3.5.0
2008-08-13 15:39 douglasw Resolution open => fixed
2008-08-13 15:39 douglasw Note Added: 0000063
2008-08-20 15:48 douglasw Status resolved => closed
2008-08-20 15:48 douglasw Note Added: 0000064

Copyright © 2000 - 2008 Mantis Group
Powered by Mantis Bugtracker