Bureaucrats, confirmed, Administrators
4,558
edits
Changes
Jump to navigation
Jump to search
no edit summary
== Debugging with the General Query Log ==
In the mysql console, you can use a global variable to turn logging on
<source lang="mysql">
SET global general_log = 1;
</source>
And specify the log file (The MySQL user must have write permission to this file.)
<source lang="mysql">
SET global general_log_file = '/var/log/mariadb/mariadb.log';
</source>
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.
<source lang="mysql">
SET global general_log = 0;
</source>
== 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
<source lang="mysql">
-- 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';
</source>
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'.
<source lang="sqlmysql">
SELECT DISTINCT
page_title
A more complex query that summarizes some information about an article
(You supply the article title)
<source lang="sqlmysql">
SELECT
p.page_id 'id',
== See Also ==
* [[Collation]]
* [[mw:Manual:Database_access]]
