Difference between revisions of "Verify Database Schema"

Jump to: navigation, search

Note: You are currently viewing documentation for Moodle 1.9. Up-to-date documentation for the latest stable version is available here: Verify Database Schema.

 
Line 3: Line 3:
 
The solution is after doing an upgrade to compare the database schema of the "production" site to that of a newly created site (where no upgrades have been performed) using '''exactly''' the same code base. There are a number of ways of doing this, but this article outlines a simple way using the Unix command line.
 
The solution is after doing an upgrade to compare the database schema of the "production" site to that of a newly created site (where no upgrades have been performed) using '''exactly''' the same code base. There are a number of ways of doing this, but this article outlines a simple way using the Unix command line.
  
# Complete the upgrade
+
* Complete the upgrade
# Generate the database schema from your recently upgraded site using the following command:
+
* Generate the database schema from your recently upgraded site using the following command:
 
     mysqldump -d -u root -p ''myproductiondb'' >production.schema
 
     mysqldump -d -u root -p ''myproductiondb'' >production.schema
# Copy the code  of your production database to a new (web accessible) location (this is important, it *must* be the same version)
+
* Copy the code  of your production database to a new (web accessible) location (this is important, it *must* be the same version)
# Create a new, empty database and moodledata area for the new site as per the [[Installation]] instructions
+
* Create a new, empty database and moodledata area for the new site as per the [[Installation]] instructions
# Edit the config.php file to point at the new database and locations, or delete it and use the install script
+
* Edit the config.php file to point at the new database and locations, or delete it and use the install script
# Run the install script to generate the (if applicable) config.php file and the database (admin and site values are not important)
+
* Run the install script to generate the (if applicable) config.php file and the database (admin and site values are not important)
# Generate the database schema from your new site using the following command:
+
* Generate the database schema from your new site using the following command:
 
     mysqldump -d -u root -p ''mycleandb'' >clean.schema
 
     mysqldump -d -u root -p ''mycleandb'' >clean.schema
# Run the following command to detect the differences
+
* Run the following command to detect the differences
     diff -y production.schema clean.schema
+
     diff -y production.schema clean.schema >db.diff
 +
 
 +
You can now look at ''db.diff'' with your favorite editor to see the differences.
 +
 
 +
==Interpreting the diff file==

Revision as of 13:52, 4 April 2008

If you have been upgrading your Moodle site over several versions, it is possible (likely) even that some differences may have crept in between the database table definitions (the "schema") in your database and the version you would get creating a new empty site. This happens because of small errors or oversights in the upgrade scripts. Most of these differences are not harmful, but some may cause strange or unexpected errors. For example, if a default value has been added to a field and this was not reflected in an upgrade script code that assumes the presence of the default may fail to work as expected.

The solution is after doing an upgrade to compare the database schema of the "production" site to that of a newly created site (where no upgrades have been performed) using exactly the same code base. There are a number of ways of doing this, but this article outlines a simple way using the Unix command line.

  • Complete the upgrade
  • Generate the database schema from your recently upgraded site using the following command:
   mysqldump -d -u root -p myproductiondb >production.schema
  • Copy the code of your production database to a new (web accessible) location (this is important, it *must* be the same version)
  • Create a new, empty database and moodledata area for the new site as per the Installation instructions
  • Edit the config.php file to point at the new database and locations, or delete it and use the install script
  • Run the install script to generate the (if applicable) config.php file and the database (admin and site values are not important)
  • Generate the database schema from your new site using the following command:
   mysqldump -d -u root -p mycleandb >clean.schema
  • Run the following command to detect the differences
   diff -y production.schema clean.schema >db.diff

You can now look at db.diff with your favorite editor to see the differences.

Interpreting the diff file