a SELECT to find the rows:

SELECT *
FROM user_newtalk
WHERE user_id
IN (

SELECT n.user_id
FROM (
user_newtalk n
JOIN user u ON u.user_id = n.user_id
)
LEFT JOIN PAGE p ON u.user_name = p.page_title
AND p.page_namespace =3
WHERE p.page_title IS NULL
)
OR user_ip
IN (

SELECT n.user_ip
FROM user_newtalk n
LEFT JOIN PAGE p ON n.user_ip = p.page_title
AND p.page_namespace =3
WHERE p.page_title IS NULL
AND n.user_id =0
)

keywords: +shell

jeluf wrote:

ERROR 1064 (00000): You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT n.user_id
FROM (
user_newtalk n
JOIN user u ON u.user_id

Probably due to:

Server version: 4.0.40-wikimedia-log

(In reply to comment #3)

Probably due to:
Server version: 4.0.40-wikimedia-log

Maybe, I have tested with MySQL 5.1.37

Are each of the both subselects working?

Or anybody know the difference between that versions, and can help?

Thanks.

Subqueries cannot be used in MySQL 4.

(In reply to comment #5)

Subqueries cannot be used in MySQL 4.

Thanks for that informationen.

Have anybody a better query?

Each of the subquery should work stand alone.

Special:Version says it's MySQL 5.1.46-facebook-r3489-log now ... and why is it Facebook?

(In reply to comment #7)

Special:Version says it's MySQL 5.1.46-facebook-r3489-log now ... and why is it
Facebook?

Which install are you looking at?

bah i see it

(In reply to comment #7)

Special:Version says it's MySQL 5.1.46-facebook-r3489-log now ... and why is it
Facebook?

It's a facebook optimised version of MySQL

There's still some clusters with MySQL from memory...

Everywhere is now MySQL 5.1

SELECT * FROM user_newtalk WHERE user_id IN ( SELECT n.user_id FROM ( user_newtalk n JOIN user u ON u.user_id = n.user_id ) LEFT JOIN page p ON u.user_name = p.page_title AND p.page_namespace =3 WHERE p.page_title IS NULL ) OR user_ip IN ( SELECT n.user_ip FROM user_newtalk n LEFT JOIN page p ON n.user_ip = p.page_title AND p.page_namespace =3 WHERE p.page_title IS NULL AND n.user_id =0 );

PAGE makes it unhappy

current(In reply to comment #12)

PAGE makes it unhappy

means a sql error or a long query run time? Feel free to make the query better or split it in two queries (one for users and one for anons)

Please add a status info. Thanks.

Please add a status info. Thanks.

Please add a status info. Thanks.

No more question on wiki about wrong new messages bars, so there was something done. Thanks.