MediaWiki/Sql

Server System Variables

It's important with MySQL (and MariaDB) to use innodb_file_per_table ON so that you do not end up with an extremely large and ever-growing ibdata1 file. You can't change this setting on an existing database server. You have to set it up initially, and migrate dumps from an old server. If your server is online, you can use replication to a properly configured slave to minimize downtime. Meza and QualityBox use innodb_file_per_table.

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 Workbench 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