MediaWiki SQL queries

Reset a user password

Before reading further consider the password reset extension which gives a simple form to reset wiki passwords.

You need to log in to the database directly. First obtain the user's id as in the following example (alternatively, go to Special:Userlist and get the id from there):

SELECT user_id FROM prefix_user WHERE user_name='WikiSysop';

Then apply the query shown in the following example to set the password for that user id (here the WikiSysop user id is 1): Note that the password is a nested concatenation of both the md5 of the user_id combined with the md5 password.

UPDATE prefix_user SET user_password=md5(CONCAT('1-',md5('newpassword'))) WHERE user_id=1;

Set a user preference

You can change a preference such as the skin with the following SQL query. Remove the WHERE clause to apply to all users.

UPDATE user SET user_options = REPLACE(user_options,'skin=monobook','skin=organicdesign') WHERE user_name = 'Foo';

Select all user who have not made edits

SELECT user_name FROM user WHERE user_id NOT IN (SELECT DISTINCT rev_user FROM revision);

Select the text of the latest revision of a title

SELECT old_text FROM page,text,revision
 WHERE page_title='title'
   AND page_namespace=numeric_namespace
   AND rev_id=page_latest AND old_id=rev_text_id;

Search all pages with specific text in their current revision

SELECT page_namespace,page_title FROM page,text,revision
 WHERE rev_id=page_latest
   AND old_id=rev_text_id
   AND old_text like '%search phrase%'

See also

• Reset MySQL root password
• Renaming MySQL tables and databases
• MySQL
• SQL
• MediaWiki code snippets