Hi all,
I have an extremely strange problem here. A particular part of a project I work
on has two SQL queries -- both selects, both join similar tables, and neither
modifies anything.
Running the first one works until I run the second. After that, running the
first returns no results (it previously returned 1 row). The only way to make
the first run again is to comment out a particular part of it (which is also in
the other query) and run it. Uncommenting that part then makes it run as usual
until the second query is run again.
I can provide more information on the actual data and tables involved if
required but am leaving it out for now as I have a feeling this may be some
sort of caching issue (because of the repeated part of query being key to
making it work again). If anyone feels this information may assist in
diagnosing the problem please let me know.
The first query (the one which breaks) is this:
SELECT *
FROM privilege Paccess
JOIN privilege Phome
ON Phome.user_id = '26'
AND Phome.type = 'network-member'
-- AND (Phome.network_id = Paccess.network_id OR Paccess.network_id IS NULL)
WHERE Paccess.user_id = '4'
AND Paccess.type = 'user-manage';
The commented out line is the one which must be commented to make it run again.
The second query (the one which makes the first one break) is this:
SELECT U.id, U.name
FROM user U
-- find each user's home network
JOIN privilege Phome
ON Phome.user_id = U.id
AND Phome.type = 'network-member'
-- current user's user-management access rights
LEFT JOIN privilege Paccess
ON Paccess.user_id = '4'
AND (Paccess.network_id = Phome.network_id OR Paccess.network_id IS NULL)
AND Paccess.type = 'user-manage';
You can clearly see the second to last line is repeated from the first.
This is running on MySQL Ver14.12 Distrib 5.0.22 for Win32 on a Windows 2000
box. I have set up a Windows XP box with same version and duplicated the
database content onto it but cannot replicate the problem there.
Please help, this is driving me mad!
--rob
______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
