InnoDB crash recovery after power failure

On the Friday, 16th July 2010. There was a power outage at our data center in LA. After power is restored, MySQL service refused to start. I checked the error log and realized InnoDB has been crashed.
 

1. The first thing I do is copy the raw database file as backup before doing anything:
   sudo cp -R /var/lib/mysql /var/lib/mysql-crash 
2. Edit the MySQL configuration file:
   sudo vi /etc/mysql/my.cnf
3. Insert the following line in [mysqld] section to force InnoDB recovery mode:
   innodb_force_recovery = 1
   
   From MySQL manual:

If you are able to dump your tables with an option value of at most 4, then you are relatively safe that only some data on corrupt individual pages is lost.

I tried from mode 1 to 4, but MySQL service refused to start. That's mean I have to accept some losses. I tried mode 5 (SRV_FORCE_NO_UNDO_LOG_SCAN), still no luck. This make me really worry as mode 6 is the last option. If MySQL service doesn't start my last option would be restored yesterday's backup.

Thanks God! Mode 6 (SRV_FORCE_NO_LOG_REDO) works! MySQL service started! Nonetheless, I couldn't query any InnoDb table. Every time  I do, I got this error:

Lost connection to MySQL server during query (XXXX)! 

When I tried to use mysqldump, I got the following error:

Couldn't execute 'show table status like '__session'': Lost connection to MySQL server during query (XXXX)

I tried to ignore the __session table, but the next table came up with the same problem and so on.

It took me hours to look for solutions on the Internet. Again, the best solution was restore from backup. Another solutions is restore InnoDB from only an .ibd file. Solution from www.chriscalender.com would work well if I enable innodb_file_per_table option. This allowed each InnoDB table to have its own tablespace like MyISAM storage engine. There would be an .ibd file created for every InnoDB table. But it's too late now. I can still extract the data from the .ibd raw file, but it would cost me a lot of time and effort.

I almost gave up!

Until now, I still don't know what made me change the recovery mode to 1 and restart MySQL service. Eureka, MySQL server restarted without any error.

Now, I backup all databases using the following command:
mysqldump --routines --triggers --single-transaction --quick -uroot -p --all-databases > all-dbs-100716.sql

In order to restore, I shutdown MySQL service and renamed the crashed database datadir. I installed the new database structure and restored everything back to just before the crashed using the following commands:

sudo mysql_install_db

mysql -uroot mysql < all-dbs-100716.sql

Thanks God! After the investigation, the corrupted table was __session. Our PHP session data are stored in the database to enable session between sub-domain. Some data from this table is lost, but it doesn't matter at all.

Here are some lessons learned:

1. Enable innodb_file_per_table. In the worst scenario, we can restore InnoDB from only an .ibd file.
2. It's good that we backup our database daily. Here is a simple command which can be executed from cron job:
   mysqldump --routines --triggers --single-transaction --quick -uroot -pXXX database-name | gzip -9 > database-name-$(date +%F).sql.gz
3. Change the InnoDB recovery mode from 1 to 6. If you cannot dump the databases, change back to mode 1. It might work! At least in our case. We already replicate the test with another server with the same crashed data.
4. When doing the recovery, change MySQL port to any other port instead of 3306. This make sure that all other services cannot access MySQL. No DELETE/UPDATE/INSERT statements can be performed in recovery mode. But it doesn't hurt to take extra precautions.
5. innodb_log_file_size is recommended to be about 25% of system RAM. Personally, I think this only apply to smaller/older server with less than 4GB RAM. In my case, I have innodb_log_file_size = 1GB even though my system have 16GB RAM. I don't think log file bigger than 1GB will do any benefit. However, setting the right figure would greatly reduce the insert and recovery time by at least 10 folds. Our database is about 2GB with more than 10 millions records. The default innodb_log_file_size is 5MB, which would takes forever to restore. I will do a test on this for the exact figures.

I am glad that I went through all these troubles. My old man told me over and over again that no pain, no gain!

Installing Zend Debugger for Ubuntu

I am still seeing so many people using var_dump(). I think most of us spend more than 50% of our time debugging. There is no excuse for investing some time for a better debugging tool. This guide will show you how to configure Zend Debugger for Zend Studio under Ubuntu.

Requirements:

• Zend Studio 7.x.x
• Firefox with Zend Studio toolbar

Assumptions:

• You are using Linux Ubuntu distribution. Other Linux distribution should be similar.
• You must already have Zend Studio installed.

You need the ZendDebugger.so extension loaded in order to enable Zend Debugger. This PHP extension comes with Zend Studio.

Finding ZendDebugger.so
If Zend Studio is installed, you can locate ZendDebugger.so as follow:

1. sudo updatedb
   Update the database of all the files and directories in our system. This command should be run by the cron job everyday. You may omit this step unless Zend Studio is newly installed.
2. locate ZendDebugger.so
   Find the exact path to ZendDebugger.so

Expected results:

/home/user/Zend/ZendStudio-7.2.1/plugins/org.zend.php.debug.debugger.linux.x86_5.3.7.v20100625/resources/php5/ZendDebugger.so
/home/user/Zend/ZendStudio-7.2.1/plugins/org.zend.php.debug.debugger.linux.x86_5.3.7.v20100625/resources/php53/ZendDebugger.so

Load ZendDebugger.so extension
I do not recommend editing the php.ini file as we can easily create a mess in there. Instead, we should create a new configuration file under /etc/php5/conf.d directory. All configuration files end with ini under that directory will be loaded every time PHP execution is called. We can easily maintain our configuration that way:

sudo cat > /etc/php5/conf.d/zend_debugger.ini

Paste from command line (ctrl+shift+v):

zend_extension=/full/path/to/ZendDebugger.so

zend_debugger.allow_hosts=127.0.0.1, 192.168.0.1/16

zend_debugger.expose_remotely=always

done with ctrl+c.

zend_debugger.allow_hosts - which hosts allowed to debug, separated by comma.
zend_debugger.expose_remotely - telling the web browser that Zend Debugger is enable on this server.

If you are using PHP 5.3, you must use .../php53/ZendDebugger.so. Otherwise, use .../php5/ZendDebugger.so.


Restart apache server:

sudo service apaches restart

Hopefully, there is no error. Otherwise, check the path to your ZendDebugger.so or the version of ZendDebugger.so you are loading. Most of the problem occur there.

Confirm Zend Debugger is installed
Create a test page as follow:

cat > test.php

Paste from command line (ctrl+shift+v):

<?php
echo phpinfo();

done with ctrl+c.

If you see the following image, your Zend Debugger is successfully configured on your server. In my case, it's just my personal laptop.

Let's debug...
Open Firefox with Zend Studio toolbar, go to your website click on the Debug button as show below:

Enjoy! But please remember, It is twice as difficult to debug a program as to write it. Therefore, if you put all of your creativity and effort into writing the program, you are not smart enough to debug it.
Anonymous

Vietnam - 4. Simplifying

How do I simplify the following problems?
Super long functions
Make staffs understand that ONE function should do ONE and only ONE specific task! Normally, the a function should contain no more than 20 LOC for our PHP web projects, excluding comments and line separators. Unless, it's a controller function.

Big fat controllers
To simplify the project, we should keep the controller skin, model fat!
Why? All the logics are in the controller, we shouldn't make it more complicated than it should be!

Logics found in the views
All the logics should be in the controller. The purpose of the view is displaying the data. The decoration should be in the CSS. Sound simple, but making all staffs understand this concept is not as simple as it should be. I need to give them concrete examples.

Zend_Form not being used
Most of our staffs do not understand Zend_Form. They think it's too complicated. I have to show them a concrete example of Zend_Form. The code from controller has been reduce from about 100 LOC to about 20 LOC.
Here are some features from Zend_Form:

• Has a lot of built in validators. Can validate number, string, phone number, credit card...
• Separate the code from HTML
• Can be decorated with Zend_Decorator
• Reusable code
• Easier to debug

Zend_Form is quite complex, example and documentation can be found here:
http://akrabat.com/php/simple-zend_form-example/
http://framework.zend.com/manual/en/zend.form.html

Variable name has no meaning
This is very hard to simplify! One simple rule is no abbreviation.
As most of the data type we are dealing with are string, number, array, Zend_Db_Table_Row, Zend_Db_Table_Rowset, therefore, I defined the following rules for our developers:

• Zend_Db_Table_Row - variable must end with Row. E.g.: $userRow
• Zend_Db_Table_Rowset - variable must end with Rowset. E.g.: $userRowset
• Array - we use plural to indicate array or self-describing name. E.g.: $userRows, $usernames, $userProfile
• String - normal variable name. E.g.: $username, $firstName,...
• Number - normal variable name. E.g.: $userAge, $earning,...

The most important concept I've taught our staffs about naming convention are:

• Variable/Class names should be noun unless you have a good reason for it not to be noun.
• Function names must be verb.
• Always use self-describing names.
• Never use shortcut.

English is a second language for our developers, therefore, we have to limit the vocabularies being used.

• All functions return a boolean must start with is, has, can.
• Function start with set accepts only ONE input parameter. E.g.: setEmail($email), setAge($age), setPassword($password)...
• Function start with update accepts an array as input parameter. E.g.: updateUserProfile($userProfile), updatePostalAddress($postalAddress), updateCreditCardInfos($creditCardInfos)...
• We have a lot of problems with naming the action controller. Here are some examples of inconsistency: getUsersAction, listUsersAction, showUsersAction,... I forced all developers to name it show. In fact, any action name from controller that involve displaying data must be started with show. Similar for edit/update/change & delete/remove/del & add/insert/insert-new/new...

Magic numbers
Magic numbers can be eliminated by moving to a constant file.

Global variables
Global variables can be used but it need to be well explained & self-describing. However, we should try to minimize the use of global variables.

It cost me a lot of time to put our staffs on track. Here are some fundamental rules for my staffs:

• All functions must have comments and @author.
• Global variables must have comments.
• Write short simple functions.
• No magic numbers.
• Variable and function names must be self-described.
• No abbreviation.
• If you think that section of code you've wrote is complex, then it is! Find a way to simplify it.

Everything should be made as simple as possible, but not simpler. Albert Einstein.

Vietnam - 3. Welcome to the real world

Please check my previous post if you haven't read it Standardizing.

After a few lectures about standardizing, simplifying, planning... Our staffs start working on the new project. I am quite busy designing other system whilst taking care of the servers. I let the staff work alone for one whole week!

Week 1:
Objective: Get the job done using the standards.

Results:
I expect the front end of the site to be completed. Yes, it is! I tested the site and found a few bugs. I dug into the code to fix it. Omg, I cannot believe that I could not understand what's going on! Here are some of the problems found:

1. Big fat controllers
2. Logic found in views
3. Models not being used much
4. Zend_Form is not used at all
5. Only a handful number of functions is found
6. Variable name has no meaning
7. LOOP in LOOP in LOOP
8. Hardly see any comment
9. Global variables are widely used
10. Magic numbers everywhere

I decided to inspect the CSS code, I found the following problems:

1. Inline style everywhere
2. ID selectors are widely used instead of class selectors
3. Many ID selectors having the same name
4. Normalization not being used
5. Short hand not being use much
6. Magic numbers everywhere, they are not even nice number. Imo, nice number are number that easy to remember and can divide many other numbers. Odd number should be avoided. 

The biggest problem is none of the standards specified is followed.

Well, one thing I cannot deny is they work hard and willing to learn. Remember, professionals built the Titanic, amateurs built Noah's Ark.

Vietnam - 2. Standardizing

Existing Office
There isn't any problem with setting up the office Vietnam as we already have an existing office with about 20 CSR. The CSR are working around the clock. We moved in, share the existing facilities.

SVN & Linux
Initially, I setup the subversion and local development environment. As a Linux geek, I forced everyone to use Linux, Ubuntu distribution. I don't see the need for Windows at all. Here are some reasons I think Linux is better. At least for our PHP developers.

• Free, easy to install
  
• No drivers needed
  
• Haven't got any complaint from our staffs about virus
• Represent our server environment
• Staffs cannot do much beside working
• The list is endless...
  

There are many cases you need Windows. Please do not argue here as I don't want to end up fighting the never ending war between Windows and Linux.

Coding Standard
I divided the coding standard to 3 sections:

1. PHP - Most of our web projects are based on the Zend Framework. Hence, we use the Zend Framework coding standard.
   
2. Database - Coding standard for SQL is a little bit tricky. I couldn't find any standard that suit our needs. I have to invent my own standard. Here are some guidelines:
   
   
   
   • Database name: lowercase, usually project name
   • Table name: plural, uppercase first letter, then CamelCase. Only the last word is plural. E.g.: UserProfiles
   • Reference table name: we use the word Maps to indicate. E.g.: UserGroupMaps
     
   • Field name: singular, uppercase first letter, then CamelCase. E.g.: FirstName, UserId
     
   • Primary key: should be the singular table name follow by Id. E.g.: UserId
     
   • Avoid using `-` and spaces
   • All table, field, functions, triggers, stored procedure names should be as meaningful as possible.
     
   • Never use abbreviation. Some people understand others don't, so avoid using it.
   • SQL keywords must be CAPITALIZED
3. CSS - All our web projects are using jQuery extensively and we are planning to keep it that way. jQuery/UI using hyphen `-` as word separator for CSS, we will stick with it. The key is making the class name as meaningful as possible.

The key here is to make everyone using the same simple standard. Talking the same language. Thinking of the same concept.

I long know simplicity is the key! Can I simplify it?

Vietnam - 1. Outsourcing

After working as a CTO (I considered myself more like a System Designer) for a small telco Filanity Corp. for 6 months. I realized the importance of outsourcing.

Our head office is located in Huntington Beach, California. However, we'd like to reduce the software development cost. I recruited a team of 10 developers in Vietnam.

• 1 x HTML/CSS/Photoshop Designer
• 1 x Photoshop/Flash Designer/Developer
• 8 x PHP Developers

I've live in Australia for almost 20 years. However, my Vietnamese is pretty good. There isn't any problem communicating with the Vietnamese developers.

The cost for the developers has been reduced significantly. A senior PHP developers would only cost us about $500 USD/month.

When hiring people in Vietnam, the following issues should be considered:

• Extremely hard to get Linux programmer.
• Spaghetti code.
• "Instant noodle" code. Most programmers are trained for that purpose. Most of the projects they've done are small projects. No maintenance is required.
  
• They can get the tasks done, but other people or teammate cannot understand.
  
• Don't be surprised if you see function with more than 100 lines of complex code.
• Wheels will be reinvented, many are not even round!
  

Well, they are quite smart. They just need to be trained!

After all, practice made perfect! Actually, only perfect practice made perfect!