The PHP Anthology Volume I, Chapter 3 - PHP and MySQL

How do I repair a corrupt table?

Although it shouldn't happen, occasionally data stored in MySQL becomes corrupted. The are a number of (rare) circumstances where this can happen; Windows is particularly susceptible as it doesn't have the robust file locking mechanism of Unix-based systems. Servers with heavy loads, on which INSERT and UPDATE queries are common alongside SELECTs are also likely to suffer occasional corruption. Assuming you're using the MyISAM table type (you'll be using this unless you've specified otherwise), there's good news; in general , you should be able to recover all the data in a corrupt table.

Note that the information provided here represents a quick reference for those times when you need help fast. It's well worth reading the MySQL manual on Disaster Prevention and Recovery so that you know exactly what you're doing.

MySQL provides two important utilities to deal with corrupt tables, as well as a handy SQL syntax for those who can get to the MySQL command line.

First, the perror utility can be run from the command line to give you a rough idea of what MySQL error codes mean. The utility should be available from the bin subdirectory of your MySQL installation. Typing perror 145, for example, will tell you:

145 = Table was marked as crashed and should be repaired

From the command line, you can then use the utility myisamchk to check the database files themselves:

myisamchk /path/to/mysql/data/table_name

To repair a corrupt table with myisamchk, the syntax is as follows:

myisamchk -r /path/to/mysql/data/table_name

Using SQL, you can also check and fix tables using a query like this:

CHECK TABLE articles

And this:

REPAIR TABLE articles

With luck, you'll need to use these commands only once or twice, but it's worth being prepared in advance so you can react effectively (without even a hint of panic creeping into your actions).

Do I really need to write SQL?

A good quality to posses as a programmer is laziness—the desire to do as much as possible with the minimum amount of effort. Although you may not want to cite it as one of your strong points in a job interview, being motivated to make life easier for yourself is a significant boon in developing a well designed application.

Now that you've read this chapter on PHP and MySQL, I think it's a good time to reveal that I hate SQL not because there's anything wrong with it, as such, but because it always causes me grief. If there's a syntax error in my PHP, for example, PHP will find it for me. But PHP won't find errors in SQL statements, and MySQL error messages can be less than revealing. If I'm hand coding SQL in an application, I'll spend a fair amount of time debugging it—time I could have spent taking it easy!

What if you could avoid having to write SQL statements altogether? If you think back to the section called "How do I create flexible SQL statements?", where we constructed SQL strings "on the fly" based on incoming variables, you may have had an inkling that there would be some kind of generic solution to make generating SQL even easier. Well, there is! It's called PEAR::DB_DataObject.

DB_DataObject is a class that encapsulates the process of writing SQL statements in a simple API. It takes advantage of the native "grammar" of SQL and presents you with a mechanism that removes almost any need to write any SQL yourself. As an approach to dealing with databases, it's usually described as a database persistence layer, or, alternatively, as using the Data Access Objects (DAO) design pattern. You'll find further discussion of the general techniques used by DB_DataObject at the end of this chapter.

Here, I'll provide a short introduction to DB_DataObject to get you started, as it's a subject that could easily absorb a whole chapter if examined in depth. The DB_DataObject documentation on the PEAR Website should provide you with plenty of further help. The version we used here was 1.1; note that it requires that you have the PEAR::DB database abstraction library installed (see Appendix D, Working with PEAR for more information on installing PEAR libraries).

The first step in getting started with DB_DataObject is to point it at your database and tell it to generate the DataObject classes that will constitute your interface with the tables. DB_DataObject automatically examines your database, using MySQL's introspection functionality, and generates a class for each table in the database, as well as a configuration file containing the details of the columns defined by the table. To let DB_DataObject know where your database can be found, you need to provide it a configuration file like this one:

Example 3.33. db_dataobject.ini            
           
[DB_DataObject]            
; PEAR::DB DSN            
database        = mysql://harryf:secret@localhost/sitepoint            
; Location where sitepoint.ini schema file should be created            
schema_location = /htdocs/phpanth/SPLIB/ExampleApps/DataObject            
; Location where DataObject classes should be created            
class_location  = /htdocs/phpanth/SPLIB/ExampleApps/DataObject            
; Prefix for including files from your code            
require_prefix  = ExampleApps/DataObject            
; Classes should be prefixed with this string e.g. DataObject_User            
class_prefix    = DataObject_            
; Debugging information: 0=off, 1=display sql, 2=display results,            
; 3=everything            
debug = 0            
; Prevent SQL INSERT, UPDATE or DELETE from being performed            
debug_ignore_updates = false            
; Whether to die of error with a PEAR_ERROR_DIE or not            
dont_die = false

The above ini file obeys the same formatting rules as php.ini. Most important is the first line, which is a PEAR::DB DSN string that defines the variables needed to connect to the database. This file is used both to generate the DataObject classes, and to use them in performing queries.