Re: [call for review] wiki maintenance
On 11/28/2012 16:48, janI wrote: This is my final sql script for location old users without contributions, can someone please verify the script ? The script only collect user_id, user_name, user_registration in the table xmaint_uids. It is running right now, and once finished (in the morning if no errors) I will publish the names that will be removed, wait 72 hours and then remove them, unless objections. The delete statements that comes afterwards are quite simple: delete from user where user_id in (select user_id from xmaint_uids); delete from logging where log_user in (select user_id from xmaint_uids); Jan. Jan, You have locked down the wiki so tightly that even sysops can't create new pages. If this is necessary, we'll have to live with it, but please let us know for how long. If this is not necessary, please fix it. /tj/
Re: [call for review] wiki maintenance
On Thu, Nov 29, 2012 at 9:17 AM, janI j...@apache.org wrote: Yes I locked it hard down, on request from the communityI had to use the big knife to be fast. I am looking into how to open for sysop, but my concern is that at least 2 spam accounts (Or more correctly 2 that fit the pattern) have got changed rights and I cannot see to what...so either a sysop have done it, or they have somehow aquired sysop priviledges. Third option: users with legacy sysop privileges had their accounts hacked due to weak passwords or something. -Rob I will look further into the settings in a couple of hours. Jan. On 29 November 2012 11:37, tj t...@apache.org wrote: On 11/28/2012 16:48, janI wrote: This is my final sql script for location old users without contributions, can someone please verify the script ? The script only collect user_id, user_name, user_registration in the table xmaint_uids. It is running right now, and once finished (in the morning if no errors) I will publish the names that will be removed, wait 72 hours and then remove them, unless objections. The delete statements that comes afterwards are quite simple: delete from user where user_id in (select user_id from xmaint_uids); delete from logging where log_user in (select user_id from xmaint_uids); Jan. Jan, You have locked down the wiki so tightly that even sysops can't create new pages. If this is necessary, we'll have to live with it, but please let us know for how long. If this is not necessary, please fix it. /tj/
Re: [call for review] wiki maintenance
You might be very right, the user that changed the rights is created back in 2007, and is by the way one of user destined for deletion. Jan. On 29 November 2012 15:29, Rob Weir robw...@apache.org wrote: On Thu, Nov 29, 2012 at 9:17 AM, janI j...@apache.org wrote: Yes I locked it hard down, on request from the communityI had to use the big knife to be fast. I am looking into how to open for sysop, but my concern is that at least 2 spam accounts (Or more correctly 2 that fit the pattern) have got changed rights and I cannot see to what...so either a sysop have done it, or they have somehow aquired sysop priviledges. Third option: users with legacy sysop privileges had their accounts hacked due to weak passwords or something. -Rob I will look further into the settings in a couple of hours. Jan. On 29 November 2012 11:37, tj t...@apache.org wrote: On 11/28/2012 16:48, janI wrote: This is my final sql script for location old users without contributions, can someone please verify the script ? The script only collect user_id, user_name, user_registration in the table xmaint_uids. It is running right now, and once finished (in the morning if no errors) I will publish the names that will be removed, wait 72 hours and then remove them, unless objections. The delete statements that comes afterwards are quite simple: delete from user where user_id in (select user_id from xmaint_uids); delete from logging where log_user in (select user_id from xmaint_uids); Jan. Jan, You have locked down the wiki so tightly that even sysops can't create new pages. If this is necessary, we'll have to live with it, but please let us know for how long. If this is not necessary, please fix it. /tj/
Re: [call for review] wiki maintenance
On Thu, Nov 29, 2012 at 3:29 PM, Rob Weir robw...@apache.org wrote: On Thu, Nov 29, 2012 at 9:17 AM, janI j...@apache.org wrote: Yes I locked it hard down, on request from the communityI had to use the big knife to be fast. I am looking into how to open for sysop, but my concern is that at least 2 spam accounts (Or more correctly 2 that fit the pattern) have got changed rights and I cannot see to what...so either a sysop have done it, or they have somehow aquired sysop priviledges. Third option: users with legacy sysop privileges had their accounts hacked due to weak passwords or something. While possible, admin-level changes leaves traces in the logs. You are able to see who gave who admin rights and when. If the two accounts in question have elevated rights at the regular user account side, you can see this in http://wiki.openoffice.org/wiki/Special:ListUsers (select Administrators). I recognize all names there - there are no mystery users. You can see who has done what related to user rights here: http://wiki.openoffice.org/wiki/Special:Log/rights Removing single log entries is possible, but not from within the browser - you need root level access to the database. Clayton
Re: [call for review] wiki maintenance
Except the fact that the table logging has relative huge holes in it, and I do not know if it has something to do with the different moves. However this does not really bother me, what bothers me more is that the ooo-wiki VM is so weak (compared to my notebook) that a lot of my special select/join statements timed out...and the just because I have a simple where clause where user_id in (select user_id from wiki_maint_uids) and that returns about 27.000 users :-) I have to change my scripts a bit, so I have subtables. Jan. On 29 November 2012 15:41, C smau...@gmail.com wrote: On Thu, Nov 29, 2012 at 3:29 PM, Rob Weir robw...@apache.org wrote: On Thu, Nov 29, 2012 at 9:17 AM, janI j...@apache.org wrote: Yes I locked it hard down, on request from the communityI had to use the big knife to be fast. I am looking into how to open for sysop, but my concern is that at least 2 spam accounts (Or more correctly 2 that fit the pattern) have got changed rights and I cannot see to what...so either a sysop have done it, or they have somehow aquired sysop priviledges. Third option: users with legacy sysop privileges had their accounts hacked due to weak passwords or something. While possible, admin-level changes leaves traces in the logs. You are able to see who gave who admin rights and when. If the two accounts in question have elevated rights at the regular user account side, you can see this in http://wiki.openoffice.org/wiki/Special:ListUsers (select Administrators). I recognize all names there - there are no mystery users. You can see who has done what related to user rights here: http://wiki.openoffice.org/wiki/Special:Log/rights Removing single log entries is possible, but not from within the browser - you need root level access to the database. Clayton
Re: [call for review] wiki maintenance
On 29/11/2012 janI wrote: ooo-wiki VM is so weak (compared to my notebook) that a lot of my special select/join statements timed out...and the just because I have a simple where clause where user_id in (select user_id from wiki_maint_uids) I only had a quick look yesterday and I cannot check now, but there were several possible speed improvements, including using a simple JOIN instead if a LEFT JOIN and then discarding NULL matches, introducing a primary key or an index in the wiki_maint_uids table and using update on joins instead of subqueries. I didn't test any of these, and I didn't see the database, so don't trust the lines above too much! They are just semi-random thoughts based on the code I saw yesterday, and it could turn out that these changes slow down the queries instead of improving them. Regards, Andrea.
Re: [call for review] wiki maintenance
Thanks I did not know that LEFT JOIN and JOIN has different speeds on inodb tables. I made the fast fix, and split xmaint_uids into smaller tables, and is running now. BUT I am dead nervous of deleting wrong data !! so the review in general would be nice. I have also decided to post the user list here, to give people a chance to shout and get removed from the list. There are very little margin for errors on our database, and I would not like to cause problems (I think I already have used this years allowance). Thanks for your suggestions, I will implement a couple of your ideas before I release the final script. Jan. On 29 November 2012 19:09, Andrea Pescetti pesce...@apache.org wrote: On 29/11/2012 janI wrote: ooo-wiki VM is so weak (compared to my notebook) that a lot of my special select/join statements timed out...and the just because I have a simple where clause where user_id in (select user_id from wiki_maint_uids) I only had a quick look yesterday and I cannot check now, but there were several possible speed improvements, including using a simple JOIN instead if a LEFT JOIN and then discarding NULL matches, introducing a primary key or an index in the wiki_maint_uids table and using update on joins instead of subqueries. I didn't test any of these, and I didn't see the database, so don't trust the lines above too much! They are just semi-random thoughts based on the code I saw yesterday, and it could turn out that these changes slow down the queries instead of improving them. Regards, Andrea.
Re: [call for review] wiki maintenance
On 11/29/2012 13:19, janI wrote: Thanks I did not know that LEFT JOIN and JOIN has different speeds on inodb tables. I made the fast fix, and split xmaint_uids into smaller tables, and is running now. BUT I am dead nervous of deleting wrong data !! so the review in general would be nice. I have also decided to post the user list here, to give people a chance to shout and get removed from the list. There are very little margin for errors on our database, and I would not like to cause problems (I think I already have used this years allowance). Thanks for your suggestions, I will implement a couple of your ideas before I release the final script. Jan. Quick question: How do you expect anyone to review 30K account names? I suggest to go ahead and delete them. --/tj/ On 29 November 2012 19:09, Andrea Pescetti pesce...@apache.org wrote: On 29/11/2012 janI wrote: ooo-wiki VM is so weak (compared to my notebook) that a lot of my special select/join statements timed out...and the just because I have a simple where clause where user_id in (select user_id from wiki_maint_uids) I only had a quick look yesterday and I cannot check now, but there were several possible speed improvements, including using a simple JOIN instead if a LEFT JOIN and then discarding NULL matches, introducing a primary key or an index in the wiki_maint_uids table and using update on joins instead of subqueries. I didn't test any of these, and I didn't see the database, so don't trust the lines above too much! They are just semi-random thoughts based on the code I saw yesterday, and it could turn out that these changes slow down the queries instead of improving them. Regards, Andrea.
Re: [call for review] wiki maintenance
Jan, On 01.11.30 02:19am, janI said: Thanks I did not know that LEFT JOIN and JOIN has different speeds on inodb tables. I made the fast fix, and split xmaint_uids into smaller tables, and is running now. BUT I am dead nervous of deleting wrong data !! so the review in general would be nice. I have also decided to post the user list here, to give people a chance to shout and get removed from the list. There are very little margin for errors on our database, and I would not like to cause problems (I think I already have used this years allowance). Thanks for your suggestions, I will implement a couple of your ideas before I release the final script. Thanks for this tremendous work here. I'm still fighting with my class and research project at 3:00am. ^^; This server is a virtual machine. There is a limit to its CPU power. And Andrea's suggestion is right. Join operations actually creats a big n times m table first and then filter. That sucks when n and m are both large. Jan. On 29 November 2012 19:09, Andrea Pescetti pesce...@apache.org wrote: On 29/11/2012 janI wrote: ooo-wiki VM is so weak (compared to my notebook) that a lot of my special select/join statements timed out...and the just because I have a simple where clause where user_id in (select user_id from wiki_maint_uids) I only had a quick look yesterday and I cannot check now, but there were several possible speed improvements, including using a simple JOIN instead if a LEFT JOIN and then discarding NULL matches, introducing a primary key or an index in the wiki_maint_uids table and using update on joins instead of subqueries. I didn't test any of these, and I didn't see the database, so don't trust the lines above too much! They are just semi-random thoughts based on the code I saw yesterday, and it could turn out that these changes slow down the queries instead of improving them. Regards, Andrea. -- Best regards, imacat ^_*' ima...@mail.imacat.idv.tw PGP Key http://www.imacat.idv.tw/me/pgpkey.asc Woman's Voice News: http://www.wov.idv.tw/ Tavern IMACAT's http://www.imacat.idv.tw/ Woman in FOSS in Taiwan http://wofoss.blogspot.com/ OpenOffice http://www.openoffice.org/ EducOO/OOo4Kids Taiwan http://www.educoo.tw/ Greenfoot Taiwan http://greenfoot.westart.tw/ signature.asc Description: OpenPGP digital signature
