3.10 Porting Code and Migrating Databases

Although the new mysqli extension has many benefits over the older extension, they come at a cost. Depending upon your code, porting to the new extension can be a surprisingly tiresome task. If you've used a database abstraction layer ”such as PEAR DB, ADOdb, or MDB ”this task is simplified considerably. Even though you're only switching from one version of MySQL to another, the new extension is sufficiently different that quite a bit of work is necessary to make the switch.

However, you probably will want to use mysqli for your new projects, and that requires an upgrade to MySQL 4.1. Since an out-of-the-box MySQL 4.1 is incompatible with mysql , this presents a bit of a dilemma. You want the benefits of mysqli without worrying about legacy code.

Another option is just to make the switch to a database abstraction layer. This has some advantages and some disadvantages. The primary benefit is portability, but this comes at a cost of speed and features. For instance, you cannot bind output parameters with PEAR DB, nor does DB support all of the latest MySQLi options, such as mysqli_multi_query( ) and ssl_set( ) .

This section presents a variety of options for handling the conversion process. Each one has various positive and negative attributes. Some require more hours of work; others are faster to implement, but at a cost of execution speed. None of these solutions are bad in and of themselves , but, given certain circumstances, some should be preferable to others. In the end, you must weigh the trade-offs and make the decision for yourself.

3.10.1 Migration Paths

The biggest problem is that you're really making three migrations instead of one. You're migrating from:

• PHP 4 to PHP 5

• MySQL 3.2.x (or MySQL 4.0) to MySQL 4.1

• mysql to mysqli

All three migrations are non-trivial and require careful planning. Trying to do all three simultaneously is very risky. However, with preparation, there are a number of ways to handle the problem so the task does not become unmanageable.

3.10.1.1 Don't migrate anything

The fastest migration path is making a clean break between your old code and your new code. Legacy code runs under PHP 4, MySQL 3.2.x or MySQL 4.0.x, and mysql . New code runs under PHP 5, MySQL 4.1, and mysqli .

The advantage is no wasted time; however, you must replicate your entire setup ”web server, database server, document trees ”and run two parallel systems. (Alternatively, you can run one version of PHP as a CGI. See Appendix C for more details.) This presents additional administrative overhead because you have twice as many pieces of software to maintain.

Additionally, if you have even one piece of code that needs to run in both environments, you may end up making a majority of the changes anyway. Therefore, this solution is suggested only for people with discrete scripts that are written for a single project and not supported on an ongoing basis, except for minimal maintenance.

3.10.1.2 Migrate only to MySQL 4.1

The second easiest migration path is to keep separate systems like before but switch to a common MySQL 4.1 backend. This allows you to keep your databases in sync because both older PHP 4 projects and newer PHP 5-based projects can share the same database tables.

While your old projects cannot use the new mysqli extension, because they're still using PHP 4, they can still take advantage of server-level features in MySQL 4.1, such as subselects and transactions, that are not dependent on the client.

This solution does require a bit of work. In addition to installing MySQL 4.1, you must move your database tables to the new server. This process is not too difficult, but it's not backward compatible. MySQL 3.2.x uses the ISAM table format to store data; MySQL 4.0 and above use the MyISAM format. Although MySQL 4.0 supports both types of tables, MySQL 4.1 does not support the old format unless you specifically request it. Additionally, the InnoDB format used by MySQL for transactions is unavailable under most pre-4.1 versions of MySQL.

There is another downside. Specifically, MySQL 4.1 has a new password schema, so you must run MySQL with the --old-passwords flag if you want to use mysql with MySQL 4.1. The upgraded password system is more secure than earlier versions; however, it's incompatible with older MySQL clients , such as the one bundled with PHP 4. Therefore, MySQL provides an option to downgrade the authentication mechanism, to be backward compatible at the expense of some security.

This does not mean running MySQL 4.1 with --old-passwords is insecure . It's actually more secure than MySQL 4.0, but it's not as secure as a regular MySQL 4.1 configuration.

3.10.1.3 Migrate to MySQL 4.1 and PHP 5

A major disadvantage to the two earlier solutions is that you cannot run PHP 4 and PHP 5 as modules on the same web server. (You can set one up as a module and the other as a CGI, but that is not acceptable for most production environments.)

This means you need to run two versions of Apache (or whatever web server you're using). These servers must either have different hostnames or use different ports on the same hostname. Also, your old code cannot take advantage of any of the new features offered in PHP 5.

Luckily, a minimal port to PHP 5 is not difficult ( assuming you don't make extensive use of DOM XML or XSLT functions; see Chapter 5 for more details). Thus, you may want to move all your code to PHP 5 but continue using the mysql extension for old projects and use mysqli only for new ones.

This allows you to avoid both the hassle of switching your entire code base over to mysqli and the pain of administering two sets of web servers and document hierarchies.

The disadvantage to this solution is that you're required to invest a good amount of time into migrating both code and data. Plus, you need to compile both extensions into PHP, which adds to the amount of memory taken up by your web server processes. Last, it can become confusing or annoying to track which projects use what version of the MySQL extensions.

3.10.1.4 Complete migration to MySQL 4.1, PHP 5, and mysqli

The final choice is a 100% migration of all three products. This is by far the most complex maneuver, especially if you're not using a database abstraction layer. Many mysql functions are altered in or removed from mysqli ; although, to be fair, most of these functions are rather esoteric.

However, you gain significant benefits from this solution: faster execution times, stronger security, and cleaner code for all your programs. After expending this one-time cost, you never need to worry about the old code again, because all your scripts run under the same environment.

3.10.1.5 Making the right decision

After hashing through all those choices, you can see that the weaselly answer to "What should I do?" is "It depends." Unfortunately, sometimes the weaselly answer is also the truth.

Nevertheless, it's best to remember this is not an all-or-nothing decision that gets made once and is then forever set in stone. There's nothing wrong with, for example, trying to see if you can get by without porting all your old code to PHP 5. If in two months you decide that was a mistake, you can then go ahead and make the switch.

You can always stop your migration at any time, see if you're happy with your setup, and restart it later if need be. This approach is actually safer because it gives you time to discover bugs on a step-by-step basis. Additionally, it may actually be faster to migrate this way because it's easier to find bugs when you can isolate them to the latest level of changes.

That's why you're strongly encouraged to follow the path outlined next for the transition. This way, you can come back to the book and not worry about forgetting any steps.

3.10.2 Migration Plan of Attack

Due to the various incompatibilities between PHP 4 and PHP 5, mysql and mysqli , and MySQL 4.0 and MySQL 4.1, the easiest solution is to migrate your application and data in the following order:

1. Transfer your database tables to MySQL 4.1.2 (or higher), but continue to use mysql . This requires you to modify how MySQL 4.1 stores your password.

2. Port your code to PHP 5, but continue using mysql .

3. Switch to using the mysqli extension, but don't use any of the new features.

4. Take advantage of the new MySQL 4.1 features available in mysqli .

Since switching databases and porting code simultaneously is a recipe for disaster, don't try to do everything in one giant step. Instead, break the task down into bite- sized pieces, so you can ensure each individual step is successful. Otherwise, when you encounter a bug, it will be very difficult to track down.

This list is specifically ordered to avoid incompatibilities and ease your migration pain.

3.10.2.1 Step 1: Migrating from MySQL 3.2.x or MySQL 4.0.x to MySQL 4.1.2

The basic process for migrating from MySQL 3.2.x (or MySQL 4.0) to MySQL 4.1.2 is simple; however, there are many minor changes between the two versions that are not documented here, because they're relevant only to a small group of users.

"Section 2.5: Upgrading/Downgrading MySQL" in the MySQL Manual is the definitive source for all these differences. In addition to following the directions here, you should read this section to ensure that nothing slips through the cracks. In particular, developers using Windows or replication should be sure to consult the manual for changes specific to their setup. The material is available online at http://www.mysql.com/doc/en/Upgrade.html.

The material in this section assumes you not only have access to a command line, but are comfortable using it. An alternative solution is to use a web-based interface, such as phpMyAdmin (http:://www.phpmyadmin.net). However, as of now, this product has experimental support for PHP 5 and MySQL 4.1. Therefore, you must figure out how to implement these steps in those products on your own.

Before you do anything, back up your databases. This allows you to restore your data in case anything happens during the upgrade process. See http://www.mysql.com/doc/en/Backup.html for how to store data.

Since MySQL 4.1 uses the MyISAM table type, you must upgrade your tables to the new format. If you're using MySQL 4.0.x, this is easy. You can use either of these two methods to upgrade the tables:

1. Run the mysql_convert_table_format script that comes with MySQL. This requires Perl and DBI, and you must set a few variables at the top of the script.

2. Issue the following SQL command for each table: ALTER TABLE table_name TYPE=MyISAM; . Substitute your table names for table_name .

If you're still using MySQL 3.2.x, it's more problematic because this version of MySQL doesn't understand the MyISAM format. The recommended MySQL solution is to first upgrade to MySQL 4.0. Once you've done this, you can use the methods just described. Alternatively, you can configure MySQL 4.1 to support the older ISAM format by passing in the --with-isam flag during compilation.

This option lets MySQL 4.1 read in the databases using the old format and lets you convert tables to MyISAM (or InnoDB). This is not a default configuration setting for MySQL 4.1, so you cannot use a prebuilt package or installer. Therefore, although it's more work to upgrade to MySQL 4.0 before 4.1, it allows you to avoid compiling MySQL yourself, which can be a major plus.

Before you can move onto MySQL 4.1, you also need to alter the MySQL GRANT tables. This lets MySQL store the securer passwords. Do this even if you're not planning on using mysqli , because it enables a few other features. There's another script available that makes all the necessary changes, so you need to:

1. Run the mysql_fix_privilege_tables script.

2. Since this script connects to MySQL as root , you need to provide the MySQL root password. On MySQL 4.0 systems, do mysql_fix_privilege_tables password . If you're upgrading directly to MySQL 4.1, do mysql_fix_privilege_tables --password=password .

3. Shut down and restart the MySQL server.

See http://www.mysql.com/doc/en/Upgrading-grant-tables.html for more details.

Now you are finally ready to switch over to MySQL 4.1.2 or higher. (MySQL 4.1.0 and 4.1.1 won't work correctly with PHP 5 in some cases.) Go to the MySQL web site, download the server, and follow the directions for installing it. Remember, if you're upgrading directly from MySQL 3.2.x, you must compile your own version of MySQL to enable ISAM support.

Shut down the old version of MySQL and restart the new one. If you want to support PHP 4 or PHP 5 with mysql , add the --old-passwords flag. The MySQL Manual has more information on this topic at http://www.mysql.com/doc/en/Password_hashing.html.

3.10.2.2 Step 2: Porting to PHP 5

When porting PHP 4 code to PHP 5, make migrating to MySQLi your last step, not your first. You can still use the older MySQL extension with PHP 5, so it's not necessary to switch to MySQLi to run PHP 5. As you'll see, the only XML extension in PHP 5 that's backward compatible with PHP 4 is SAX. DOM, XSLT, and XPath all require you to alter your code. All other major PHP 4 extensions continue to work as-is under PHP 5. Therefore, unlike with your XML code, you have the luxury of controlling when you should port your MySQL code.

It makes sense to convert to mysqli as the final step of your migration process. You can use your web site under PHP 5 without mysqli ; that's not true for other extensions. This strategy minimizes the initial downtime and gets a working version of your site up for testing while you migrate to MySQLi.

Since the rest of this book covers migrating to PHP 5, the specific details of the migration process are omitted here to prevent infinite recursion.

3.10.2.3 Step 3: Migrating to mysqli

A two-pronged migration strategy is best because there are both API changes and new features in mysqli . The first step is to make sure your existing code is compatible with the new mysqli API. This finally allows you to deploy your current site under PHP 5 and MySQL 4.1, but without the old mysql extension.

Once that's done, you should then work new features into your code base. It's tempting to upgrade everything at once, but this always ends up taking more time than expected.

The largest change in the API is that there is no longer a concept of the "current database." Many mysql functions allow you to omit a database handle, automatically defaulting to the last connection. This feature has been eliminated. Although it was convenient , it made it very difficult to update the functions to support new MySQL features without breaking backward compatibility or requiring you to pass the database handle anyway.

Therefore, you're now required to always pass a database connection. Additionally, this handle is now the first function parameter. It was previously the final parameter, so it could be optional. This location mimics the MySQL API and makes the argument order more consistent.

Besides altering these functions, another set of functions have been eliminated entirely. In most cases, these are the more obscure mysql functions, such as mysql_field_table( ) . Many can be easily replicated with a simple SQL query; others require a few queries.

The two "most used" missing functions are mysql_pconnect( ) and mysql_escape_string( ) . Switch these to mysqli_connect( ) and mysqli_real_escape_string( ) , respectively.

In addition to mysql_connect( ) , whose new API has already been detailed, the functions in Table 3-3 have all been modified to require a database handle as their first argument.

Table 3-3. MySQL functions with a changed argument list

mysql	mysqli
mysql_connect([string hostname[:port][:/path/to/socket] [, string username [, string password [, bool new [, int flags]]]]])	mysqli_connect([string hostname [,string username [,string password [,string database_name [,int port [,string socket]]]]]])
mysql_select_db(string database_name [, int link_identifier])	mysqli_select_db(int link_identifier, string database_name)
mysql_query(string query [, int link_identifier])	mysqli_query(int link_identifier, string query [,int resultmode])
mysql_fetch_field(resource result [, int field_offset])	mysqli_fetch_field(object result)
mysql_change_user(string user , string password [, string database [, resource link_identifier]])	mysqli_change_user(int link_identifier, string user, string password, string database)
mysql_real_escape_string(string to_be_escaped [, int link_identifier])	mysqli_real_escape_string(int link_identifier, string to_be_escaped)
mysql_affected_rows([int link_identifier])	mysqli_affected_rows(int link_identifier)
mysql_close([int link_identifier])	mysqli_close(int link_identifier)
mysql_error([int link_identifier])	mysqli_error(int link_identifier)
mysql_errno([int link_identifier])	mysqli_errno(int link_identifier)
mysql_get_host_info([int link_identifier])	mysqli_get_host_info (int link_identifier)
mysql_get_proto_info([int link_identifier])	mysqli_get_proto_info(int link_identifier)
mysql_get_server_info([int link_identifier])	mysqli_get_server_info(int link_identifier)
mysql_info([int link_identifier])	mysqli_info(int link_identifier)
mysql_insert_id([int link_identifier])	mysqli_insert_id(int link_identifier)
mysql_ping([int link_identifier])	mysqli_ping(int link_identifier)
mysql_stat([int link_identifier])	mysqli_stat(int link_identifier)
mysql_thread_id([int link_identifier])	mysqli_thread_id(int link_identifier)

Table 3-4 contains a list of functions not transferred to mysqli and their replacement versions. In many cases these functions were already deprecated, so your code probably won't contain many instances of these functions.

Table 3-4. Functions not ported to mysqli

mysql	mysqli
mysql_create_db($database, $db);	mysqli_query($db, "CREATE DATABASE $database");
mysql_drop_db($database, $db);	mysqli_query($db, "DROP DATABASE $database");
mysql_escape_string($string);	mysqli_real_escape_string($string);
mysql_field_flags($result, $i);	$fields = mysqli_fetch_field($result); $fields[$i]->flags;
mysql_field_len($result, $i)	$fields = mysqli_fetch_field($result); $fields[$i]->max_length;
mysql_field_name($result, $i)	$fields = mysqli_fetch_field($result); $fields[$i]-> name ;
mysql_field_table($result, $i)	$fields = mysqli_fetch_field($result); $fields[$i]->table;
mysql_field_type($result, $i)	$fields = mysqli_fetch_field($result); $fields[$i]->type;
mysql_db_name($result, $i)	mysqli_data_seek($result, $i); $row = mysqli_fetch_row($result);
mysql_db_query($database, $query, $db);	mysqli_select_db($db, $database); mysqli_query($db, $query);
mysql_list_dbs($db);	mysqli_query($db, "SHOW DATABASES");
mysql_list_fields($database, $table, $db)	mysqli_use_db($db, $database); mysqli_query($db, "SHOW COLUMNS FROM $table");
mysql_list_processes($db)	mysqli_query($db, "SHOW PROCESSLIST");
mysql_list_tables($database, $db)	mysqli_query($db, "SHOW TABLES FROM $database");
mysql_pconnect($hostname, $username, $password);	mysqli_connect($hostname, $username, $password);
mysql_result($result, $column);	$row = mysqli_fetch_row($result); $row[$column];
mysql_tablename($result, $i)	mysqli_data_seek($r, $i); $row = mysqli_fetch_row($r);
mysql_unbuffered_query( )	mysqli_real_query( ); and mysqli_use_result( );

3.10.2.4 Adding new features

Once all that is finally complete, take a deep breath . Now you can move on to the fun part. Go back over your applications and locate places where the new features can improve your code.

In particular, bound parameters can significantly speed up queries and also reduce the potential for SQL injection attacks. You'll probably also discover places where using a subselect can reduce the complexity of your PHP script because you've moved the logic into MySQL.