Mediawiki
Jump to navigation
Jump to search
MediaWiki wurde erfolgreich installiert.
Hilfe zur Benutzung und Konfiguration der Wiki-Software findest du im Benutzerhandbuch.
Starthilfen
- Liste der Konfigurationsvariablen
- MediaWiki-FAQ
- Mailingliste neuer MediaWiki-Versionen
- Lokalisiere MediaWiki für deine Sprache
Extensions
Deleting SPAM
- https://stackoverflow.com/questions/56699830/delete-mediawiki-spam-users-via-mysql/58305026#58305026
Useful SQL Queries
Effort
select
count(*),
year(r.rev_timestamp) as 'year',
month(r.rev_timestamp) as 'month',
day(r.rev_timestamp) as 'day',
hour(r.rev_timestamp) as 'hour',
convert(u.user_name using utf8) as 'user'
from revision r inner join user u on r.rev_user=u.user_id
where r.rev_timestamp>'202007'
group by 2,3,4,5,6
order by 2 desc, 3 desc, 4 desc, 5 desc
Revsision by time
select r.rev_timestamp,convert(u.user_name using utf8) as t
from revision r inner join user u on r.rev_user=u.user_id
where r.rev_timestamp>'202008'
order by r.rev_timestamp desc;
Revision/changes by user
select count(*),u.user_id,convert(u.user_name using utf8) as username
from revision r
inner join user u
on r.rev_user=u.user_id
group by 2
order by 1 desc
externallinks
describe externallinks
Field Type Null Key Default Extra
el_id int(10) unsigned NO PRI (null) auto_increment
el_from int(10) unsigned NO MUL 0
el_to blob NO MUL (null)
el_index blob NO MUL (null)
user
user details
select
convert(user_name using utf8) as name,
convert(user_touched using utf8) as time,
user_editcount
from user
order by 2 desc
name time user_editcount
Wf 20191009122047 689
pages by user
select count(*),u.user_id,convert(u.user_name using utf8) as username
from revision r
inner join user u
on r.rev_user=u.user_id
group by 2
order by 1 desc
remove invalid users
delete from user where not user_id in (1)
text table
utf8 content display
select
convert(old_text using utf8) as t
from text
Pages / Revisions / Text / User
select
convert(u.user_name using utf8) as username,
p.page_id,
convert(p.page_title using utf8) as pagetitle,
r.rev_user as userid,
convert(t.old_text using utf8) as text
from page p
inner join revision r
on p.page_id=r.rev_page
inner join user u
on r.rev_user=u.user_id
inner join text t
on r.rev_text_id=t.old_id
Delete text from invalid users
use limit of count is high e.g. > 20.000
select count(*) as textcount from text where old_id in (select rev_text_id from revision where not rev_user in (1));
set autocommit=0;
start transaction;
delete from text where old_id in (select rev_text_id from revision where not rev_user in (1)) ;
commit;
Count Delete Text not from users
select count(*) as textcount from text where old_id in (select rev_text_id from revision where not rev_user in (1));
delete
from text
where old_id in (select rev_text_id from revision where rev_user!=1)
optimize table to get back file space
optimize table text
root@io:/var/lib/mysql/mw1_23# ls -l
-rw-r----- 1 mysql mysql 8642 Oct 29 08:38 text.frm
-rw-r----- 1 mysql mysql 131072 Oct 29 08:38 text.ibd