Page MenuHomePhabricator

Clean up table user_newtalk
Closed, ResolvedPublic

Description

After fixing of bug 14077 there are many wrong entries in the table user_newtalk. Please remove all entries of non-existing pages.

When this is the wrong place, feel free to move it.

Thanks.


Version: unspecified
Severity: enhancement

Details

Reference
bz23847

Related Objects

StatusSubtypeAssignedTask
OpenFeatureNone
ResolvedNone

Event Timeline

bzimport raised the priority of this task from to Low.Nov 21 2014, 11:10 PM
bzimport set Reference to bz23847.
bzimport added a subscriber: Unknown Object (MLST).

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
)

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?

(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...

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.