Last modified: 2013-06-18 16:52:07 UTC

Wikimedia Bugzilla is closed!

Wikimedia has migrated from Bugzilla to Phabricator. Bug reports should be created and updated in Wikimedia Phabricator instead. Please create an account in Phabricator and add your Bugzilla email address to it.
Wikimedia Bugzilla is read-only. If you try to edit or create any bug report in Bugzilla you will be shown an intentional error message.
In order to access the Phabricator task corresponding to a Bugzilla report, just remove "static-" from its URL.
You could still run searches in Bugzilla or access your list of votes but bug reports will obviously not be up-to-date in Bugzilla.
Bug 23847 - Clean up table user_newtalk
Clean up table user_newtalk
Status: RESOLVED FIXED
Product: Wikimedia
Classification: Unclassified
Site requests (Other open bugs)
unspecified
All All
: Low enhancement (vote)
: ---
Assigned To: Nobody - You can work on this!
: shell
Depends on:
Blocks: 16660
  Show dependency treegraph
 
Reported: 2010-06-08 19:08 UTC by Umherirrender
Modified: 2013-06-18 16:52 UTC (History)
5 users (show)

See Also:
Web browser: ---
Mobile Platform: ---
Assignee Huggle Beta Tester: ---


Attachments

Description Umherirrender 2010-06-08 19:08:19 UTC
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.
Comment 1 Umherirrender 2010-06-18 18:34:41 UTC
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
)
Comment 2 p858snake 2010-06-19 01:16:43 UTC
keywords: +shell
Comment 3 JeLuF 2010-06-19 20:46:58 UTC
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
Comment 4 Umherirrender 2010-06-20 16:46:50 UTC
(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.
Comment 5 Roan Kattouw 2010-06-20 18:12:17 UTC
Subqueries cannot be used in MySQL 4.
Comment 6 Umherirrender 2010-07-30 09:44:31 UTC
(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.
Comment 7 Liangent 2010-07-30 10:02:14 UTC
Special:Version says it's MySQL 5.1.46-facebook-r3489-log now ... and why is it Facebook?
Comment 8 p858snake 2010-07-30 10:07:38 UTC
(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?
Comment 9 p858snake 2010-07-30 10:07:53 UTC
bah i see it
Comment 10 Sam Reed (reedy) 2011-07-08 22:20:10 UTC
(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...
Comment 11 Sam Reed (reedy) 2011-09-16 12:50:31 UTC
Everywhere is now MySQL 5.1
Comment 12 Sam Reed (reedy) 2011-09-16 12:52:59 UTC
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
Comment 13 Umherirrender 2012-04-14 19:23:37 UTC
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)
Comment 14 Umherirrender 2012-07-07 20:26:51 UTC
Please add a status info. Thanks.
Comment 15 Umherirrender 2012-08-27 19:25:07 UTC
Please add a status info. Thanks.
Comment 16 Umherirrender 2012-09-04 18:51:32 UTC
Please add a status info. Thanks.
Comment 17 Umherirrender 2012-09-29 10:44:13 UTC
No more question on wiki about wrong new messages bars, so there was something done. Thanks.

Note You need to log in before you can comment on or make changes to this bug.


Navigation
Links