MediaWiki/Sql

From Freephile Wiki
Jump to navigation Jump to search

Server System Variables[edit | edit source]

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.[1] 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[edit | edit source]

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[edit | edit source]

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[edit | edit source]

References[edit source]

  1. https://bugs.mysql.com/bug.php?id=1341
    21 Oct 2015 17:57 James Day
    The now-GA 5.7 provides a feature to create general tablespaces anywhere you like and store multiple tables in them, as well as move individual tables between tablespaces, including per-file tablespaces and the system tablespace. Described at https://dev.mysql.com/doc/refman/5.7/en/general-tablespaces.html . As with the shared tablespace, once space has been allocated from the filesystem there is no way to release it. If you need that capability, use per-table tablespaces instead, those do fully release the space allocated to a table when it is dropped.

    This provides no way to make the shared tablespace smaller but it can be used to reduce the pain of emptying it by moving tables to a different common tablespace. Combine that with putting the undo log files in a different place, as they are now by default, and with temporary tables going to their own dedicated tablespace and there will be relatively little left in the shared tablespace.

    Even so, the process of moving tables and doing a new install to get rid of the disk space allocated to the shared tablespace is painful for installations that do have lots of data or redo there. (emphasis added)

    James Day, MySQL Senior Principal Support Engineer, Oracle