MindTouch Community Portal
Logged in: Logged in as: Log in
Register
Search:
Page last modified 14:04, 5 Jan 2011 by jamesv
MindTouch Community Portal > MindTouch Community Portal > Documentation > MindTouch Administration Guide > MySQL Database > Verify database schema using CheckDb
Page Notifications Off
Redirected from User:PeteE/CheckDb
Was this page helpful?

Verify database schema using CheckDb

    1. 1. Overview
    2. 2. Goal
    3. 3. Configuration
    4. 4. Running CheckDb
    5. 5. Linux:
    6. 6. Advanced
      1. 6.1. Running CheckDb from NUnit
      2. 6.2. Tests
      3. 6.3. TestDbConnection
      4. 6.4. CheckTablesExist
      5. 6.5. CheckColumns
      6. 6.6. CheckWikiDbPermissions
      7. 6.7. CheckMySqlProcPermissions
      8. 6.8. CheckCatalogUtf8
      9. 6.9. CheckCatalogCollationUtf8
      10. 6.10. CheckConnectionCharsetMatchesDatabaseCharset
      11. 6.11. CheckConnectionCollationMatchesDatabaseCollation
      12. 6.12. CheckHomePageExists
      13. 6.13. CheckParentPages
    7. 7. What's Next
    8. 8. Related

    Overview

    mindtouch.deki.checkdb.exe is a tool to check the integrity of a MindTouch database.  CheckDb runs a series of tests to verify the database table schema, indexes, character set, etc.  As MindTouch evolves, we will continue to add new tests to CheckDb.  CheckDb is an executable that can be run on it's own but each test is also an NUnit test which can be run from the NUnit GUI or console.

    Goal

    After completing this tutorial you will have successfully configured and ran a database check.

    Configuration

    CheckDb requires an xml configuration file named mindtouch.deki.checkdb.config.xml to be located in the same directory as the mindtouch.deki.checkdb.exe binary.  Here is an example of the mindtouch.deki.checkdb.config.xml

    <config>
  <db-catalog>wikidb</db-catalog>
  <db-user>someuser</db-user>
  <db-password>somepassword</db-password>
  <db-options>pooling=true; Connection Timeout=5; Connection Lifetime=30; Protocol=socket; Min Pool Size=2; Max Pool Size=50; Connection Reset=false;character set=utf8;ProcedureCacheSize=25;Use Procedure Bodies=true;</db-options>
  <db-server>dekidev</db-server>
  <db-port>3306</db-port>
</config>

     

    CheckDb also relies on an xml file (mindtouch.deki.checkdb.schema.xml) which defines what the MindTouch database schema should look like.  This file is auto-generated based on a clean MindTouch installation and shouldn't need to be modified.

     

    Running CheckDb

    Linux:

    To run checkdb from the command line, do the following:

    cd /var/www/dekiwiki/bin
mono mindtouch.deki.checkdb.exe

     

    Windows:

    To run checkdb, simply execute the file below:

    C:\Program Files\MindTouch\MindTouch\web\bin\mindtouch.deki.checkdb.exe
     

    This will then check the MySQL database.  To use this script for more advanced setups please see the information below.

    Advanced

    Running CheckDb from NUnit

    To run the checkdb unit tests with nunit-console2 execute:

    nunit-console2 mindtouch.deki.checkdb.exe

    Tor run the checkdb tests from nunit-gui, you'll want to get the mindtouch.deki.checkdb.nunit file from our SVN repository

    curl https://svn.mindtouch.com/source/public/dekiwiki/trunk/src/tests/CheckDb/mindtouch.deki.checkdb.nunit -o mindtouch.deki.checkdb.nunit

    Edit this file in a text editor and change the appbase to:

    appbase="."

    Then load the mindtouch.deki.checkdb.nunit file and run the tests

    Tests

    TestDbConnection

    This tests verifies that you can connect to the database using the credentials specified in the mindtouch.deki.checkdb-config.xml

     

    CheckTablesExist

    This tests verifies that each table in the mindtouch.deki.checkdb.schema.xml file also exists in the wiki database.  Any additional tables that make exist in the wiki database that aren't in mindtouch.deki.checkdb.schema.xml are ignored. 

    CheckColumns

    This tests iterates over each table and checks the definition of the columns and indexes for each table.

    CheckWikiDbPermissions

    This test checks the GRANTs for the wiki database user.  NOTE: this test checks the default permissions that are granted by the installer.  It's possible that this test may fail even though your wiki is setup properly if you've locked down permissions more tightly.

    CheckMySqlProcPermissions

    This test checks to make sure the database user has select access to the mysql.proc table (in order to execute stored procedures).

    CheckCatalogUtf8

    This test verifies that the CHARACTER SET of the wiki database is utf8.

    CheckCatalogCollationUtf8

    This test verifies that the COLLATION of the wiki database is utf8_general_ci.

    CheckConnectionCharsetMatchesDatabaseCharset

    This test ensures that your connection string is using the proper database charset.

    CheckConnectionCollationMatchesDatabaseCollation

    This test ensures that your connection string is using the proper database collation.

    CheckHomePageExists

    This tests verifies that the home page of the wiki exists. 

    CheckParentPages

    This tests checks each page in the pages table and checks for any orphaned pages.

    What's Next

    It is recommended to take routine backups in case a database becomes corrupted beyond recovery.  You can learn more about database backups in our Backup Documentation.  

    Was this page helpful?
    Tag page
    What links here

    Files 0

    Attach file or image
     
    You must login to post a comment.
    MindTouch Social Knowledge Base

    Copyright © 2011 MindTouch, Inc. Powered by