MediaWiki/Sql

Debugging with the General Query Log

In the mysql console, you can use a global variable to turn logging on

SET global general_log = 1;

And specify the log file (The MySQL user must have write permission to this file.)

SET global general_log_file = '/var/log/mariadb/mariadb.log';

Then you can do some action that you want to examine the queries for, and TURN OFF the general query log immediately. A busy server will fill up all disk space with a huge log file if you don't turn it off.

SET global general_log = 0;

Query Examples

Some database queries that you might want to use and bookmark in your database administration tool such as MySQL Query Browser. http://dev.mysql.com/downloads/gui-tools/5.0.html or SQLyog http://www.webyog.com/en/index.php

-- what database schema do you want to use?
use wiki_demo;
-- What would a create database statement look like?
show create database wiki_demo;
-- How about a table?
show create table wiki_demo.l10n_cache;
# What character collations are specified for each table and column (that supersede any global declaration or default.)
SELECT 
  TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME 
FROM information_schema.columns AS cols 
WHERE table_schema = 'wiki_demo';

Here is a query that will show you the pages in your wiki that contain a particular string. In this example, I wanted to find all pages that were using the 'Icon' extension parser extension magic word '#icon'.

SELECT DISTINCT 
  page_title
FROM 
  page
  RIGHT JOIN 
  revision 
  ON page_id = rev_page
WHERE 
  rev_text_id
  IN (
    SELECT 
      old_id
    FROM 
      text
    WHERE 
      # old_text LIKE '%#icon%' 
      old_text REGEXP('#icon') # regex is easier to deal with
    )

A more complex query that summarizes some information about an article (You supply the article title)

SELECT 
  p.page_id 'id', 
  p.page_title 'title', 
  p.page_namespace 'namespace', 
  t.old_id 'latest text id', 
  t.old_text 'content', 
  r.rev_id, 
  r.rev_comment 'comment', 
  r.rev_user, 
  r.rev_user_text, 
  r.rev_parent_id 'previous revision' 
FROM 
  text t 
  RIGHT JOIN 
  revision r 
  ON t.old_id = r.rev_text_id 
  RIGHT JOIN 
  page p 
  ON r.rev_id = p.page_latest
WHERE 
  p.page_title = 'Main_Page'

See Also

• Collation
• mw:Manual:Database_access