I decided to provide a second example on a single host with the same database. I start with 5.1.22. [analyst@taosecurity ~]$ uname -a FreeBSD taosecurity.taosecurity.com 7.0-RELEASE FreeBSD 7.0-RELEASE #0: Sun Feb 24 19: [analyst@taosecurity ~]$ mysql -u sguil -p sguildb -e 'select user(), now(), version(), database()' Enter password: +-----------------+---------------------+-----------+------------+ | user() | now() | version() | database() | +-----------------+---------------------+-----------+------------+ | sguil@localhost | 2008-04-11 19:44:03 | 5.1.22-rc | sguildb | +-----------------+---------------------+-----------+------------+ [analyst@taosecurity ~]$ mysql -u sguil -p sguildb -e 'explain select count(*) from sancp' Enter password: +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ | 1 | SIMPLE | sancp | index | NULL | dst_port | 3 | NULL | 421 | Using index | +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ [analyst@taosecurity ~]$ mysql -u sguil -p sguildb -e 'show create table sancp'Enter password: +-+ | Table | Create Table | +-+ | sancp | CREATE TABLE `sancp` ( `sid` int(10) unsigned NOT NULL, `sancpid` bigint(20) unsigned NOT NULL, `start_time` datetime NOT NULL, `end_time` datetime NOT NULL, `duration` int(10) unsigned NOT NULL, `ip_proto` tinyint(3) unsigned NOT NULL, `src_ip` int(10) unsigned DEFAULT NULL, `src_port` smallint(5) unsigned DEFAULT NULL, `dst_ip` int(10) unsigned DEFAULT NULL, `dst_port` smallint(5) unsigned DEFAULT NULL, `src_pkts` int(10) unsigned NOT NULL, `src_bytes` int(10) unsigned NOT NULL, `dst_pkts` int(10) unsigned NOT NULL, `dst_bytes` int(10) unsigned NOT NULL, `src_flags` tinyint(3) unsigned NOT NULL, `dst_flags` tinyint(3) unsigned NOT NULL, KEY `p_key` (`sid`,`sancpid`), KEY `src_ip` (`src_ip`), KEY `dst_ip` (`dst_ip`), KEY `dst_port` (`dst_port`), KEY `src_port` (`src_port`), KEY `start_time` (`start_time`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`sancp_taosecurity_20071224`) | +-+ Next I delete the 5.1.22 installation and replace it with 5.1.23. [analyst@taosecurity ~]$ su - Password: taosecurity:/root# cd /var/db/pkg taosecurity:/var/db/pkg# pkg_delete -f mysql-client-5.1.22/ pkg_delete: package 'mysql-client-5.1.22' is required by these other packages and may not be deinstalled (but I'll delete it anyway): mysql-server-5.1.22 mysqltcl-3.03 pkg_delete: unable to completely remove directory '/usr/local/lib/mysql' pkg_delete: unable to completely remove directory '/usr/local/share/mysql' pkg_delete: couldn't entirely delete package (perhaps the packing list is incorrectly specified?) taosecurity:/var/db/pkg# pkg_delete -f mysql-server-5.1.22/ Stopping mysql. Waiting for PIDS: 752. taosecurity:/var/db/pkg# fetch ftp://ftp.freebsd.org/pub/FreeBSD/ports/i386/packages-7-stable/databases/mysql-server-5.1.23.tbz mysql-server-5.1.23.tbz 100% of 5285 kB 204 kBps 00m00s taosecurity:/var/db/pkg# fetch ftp://ftp.freebsd.org/pub/FreeBSD/ports/i386/packages-7-stable/databases/mysql-client-5.1.23.tbz mysql-client-5.1.23.tbz 100% of 990 kB 155 kBps taosecurity:/var/db/pkg# pkg_add mysql-client-5.1.23.tbz taosecurity:/var/db/pkg# pkg_add mysql-server-5.1.23.tbz mysql:*:88: You already have a group "mysql", so I will use it. mysql:*:88:88::0:0:MySQL Daemon:/nonexistent:/sbin/nologin You already have a user "mysql", so I will use it. ************************************************************************ Remember to run mysql_upgrade (with the optional --datadir= flag) the first time you start the MySQL server after an upgrade from an earlier version. ************************************************************************ taosecurity:/var/db/pkg# /usr/local/etc/rc.d/mysql-server start Starting mysql. taosecurity:/var/db/pkg# mysql_upgrade -p Enter password: Looking for 'mysql' in: mysql Looking for 'mysqlcheck' in: mysqlcheck Running 'mysqlcheck'... mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.general_log Error : You can't use locks with log tables. status : OK mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.servers OK mysql.slow_log Error : You can't use locks with log tables. status : OK mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK sguildb.data OK sguildb.data_taosecurity_20071224 OK sguildb.event OK sguildb.event_taosecurity_20071224 OK sguildb.history OK sguildb.icmphdr OK sguildb.icmphdr_taosecurity_20071224 OK sguildb.nessus OK sguildb.nessus_data OK sguildb.pads OK sguildb.portscan OK sguildb.sancp OK sguildb.sancp_taosecurity_20071224 OK sguildb.sensor OK sguildb.status OK sguildb.tcphdr OK sguildb.tcphdr_taosecurity_20071224 OK sguildb.udphdr OK sguildb.udphdr_taosecurity_20071224 OK sguildb.user_info OK sguildb.version OK Running 'mysql_fix_privilege_tables'... OK taosecurity:/var/db/pkg# logout Notice 5.1.23 behaves like 5.1.22. [analyst@taosecurity ~]$ mysql -u sguil -p sguildb -e 'select user(), now(), version(), database()' Enter password: +-----------------+---------------------+-----------+------------+ | user() | now() | version() | database() | +-----------------+---------------------+-----------+------------+ | sguil@localhost | 2008-04-11 19:51:59 | 5.1.23-rc | sguildb | +-----------------+---------------------+-----------+------------+ [analyst@taosecurity ~]$ mysql -u sguil -p sguildb -e 'explain select count(*) from sancp' Enter password: +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ | 1 | SIMPLE | sancp | index | NULL | dst_port | 3 | NULL | 421 | Using index | +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ [analyst@taosecurity ~]$ mysql -u sguil -p sguildb -e 'show create table sancp' Enter password: +-+ | Table | Create Table | +-+ | sancp | CREATE TABLE `sancp` ( `sid` int(10) unsigned NOT NULL, `sancpid` bigint(20) unsigned NOT NULL, `start_time` datetime NOT NULL, `end_time` datetime NOT NULL, `duration` int(10) unsigned NOT NULL, `ip_proto` tinyint(3) unsigned NOT NULL, `src_ip` int(10) unsigned DEFAULT NULL, `src_port` smallint(5) unsigned DEFAULT NULL, `dst_ip` int(10) unsigned DEFAULT NULL, `dst_port` smallint(5) unsigned DEFAULT NULL, `src_pkts` int(10) unsigned NOT NULL, `src_bytes` int(10) unsigned NOT NULL, `dst_pkts` int(10) unsigned NOT NULL, `dst_bytes` int(10) unsigned NOT NULL, `src_flags` tinyint(3) unsigned NOT NULL, `dst_flags` tinyint(3) unsigned NOT NULL, KEY `p_key` (`sid`,`sancpid`), KEY `src_ip` (`src_ip`), KEY `dst_ip` (`dst_ip`), KEY `dst_port` (`dst_port`), KEY `src_port` (`src_port`), KEY `start_time` (`start_time`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`sancp_taosecurity_20071224`) | +-+ Now I downgrade to 5.0.51a. [analyst@taosecurity ~]$ su - Password: taosecurity:/root# cd /var/db/pkg taosecurity:/var/db/pkg# pkg_delete -f mysql-client- mysql-client-5.1.23.tbz mysql-client-5.1.23/ taosecurity:/var/db/pkg# pkg_delete -f mysql-client-5.1.23 pkg_delete: package 'mysql-client-5.1.23' is required by these other packages and may not be deinstalled (but I'll delete it anyway): mysql-server-5.1.23 pkg_delete: unable to completely remove directory '/usr/local/lib/mysql' pkg_delete: unable to completely remove directory '/usr/local/share/mysql' pkg_delete: couldn't entirely delete package (perhaps the packing list is incorrectly specified?) taosecurity:/var/db/pkg# pkg_delete -f mysql-server-5.1.23 Stopping mysql. Waiting for PIDS: 1117, 1117. taosecurity:/var/db/pkg# fetch ftp://ftp.freebsd.org/pub/FreeBSD/ports/i386/packages-7-stable/databases/mysql-server-5.0.51a.tbz mysql-server-5.0.51a.tbz 100% of 4873 kB 107 kBps 00m00s taosecurity:/var/db/pkg# fetch ftp://ftp.freebsd.org/pub/FreeBSD/ports/i386/packages-7-stable/databases/mysql-client-5.0.51a.tbz mysql-client-5.0.51a.tbz 100% of 897 kB 161 kBps taosecurity:/var/db/pkg# pkg_add mysql-client-5.0.51a.tbz taosecurity:/var/db/pkg# pkg_add mysql-server-5.0.51a.tbz mysql:*:88: You already have a group "mysql", so I will use it. mysql:*:88:88::0:0:MySQL Daemon:/nonexistent:/sbin/nologin You already have a user "mysql", so I will use it. ************************************************************************ Remember to run mysql_upgrade (with the optional --datadir= flag) the first time you start the MySQL server after an upgrade from an earlier version. ************************************************************************ taosecurity:/var/db/pkg# /usr/local/etc/rc.d/mysql-server start Starting mysql. taosecurity:/var/db/pkg# mysql_upgrade -p Enter password: Looking for 'mysql' in: mysql Looking for 'mysqlcheck' in: mysqlcheck Running 'mysqlcheck'... mysql.columns_priv OK mysql.db OK mysql.event OK mysql.func OK mysql.general_log Error : Can't find file: 'general_log' (errno: 2) error : Corrupt mysql.help_category OK mysql.help_keyword OK mysql.help_relation OK mysql.help_topic OK mysql.host OK mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc OK mysql.procs_priv OK mysql.servers OK mysql.slow_log Error : Can't find file: 'slow_log' (errno: 2) error : Corrupt mysql.tables_priv OK mysql.time_zone OK mysql.time_zone_leap_second OK mysql.time_zone_name OK mysql.time_zone_transition OK mysql.time_zone_transition_type OK mysql.user OK sguildb.data OK sguildb.data_taosecurity_20071224 OK sguildb.event OK sguildb.event_taosecurity_20071224 OK sguildb.history OK sguildb.icmphdr OK sguildb.icmphdr_taosecurity_20071224 OK sguildb.nessus OK sguildb.nessus_data OK sguildb.pads OK sguildb.portscan OK sguildb.sancp OK sguildb.sancp_taosecurity_20071224 OK sguildb.sensor OK sguildb.status OK sguildb.tcphdr OK sguildb.tcphdr_taosecurity_20071224 OK sguildb.udphdr OK sguildb.udphdr_taosecurity_20071224 OK sguildb.user_info OK sguildb.version OK Repairing tables mysql.general_log Error : Can't find file: 'general_log' (errno: 2) error : Corrupt mysql.slow_log Error : Can't find file: 'slow_log' (errno: 2) error : Corrupt Running 'mysql_fix_privilege_tables'... OK taosecurity:/var/db/pkg# logout When running 5.0.51a I get the expected results ("Select tables optimized away"). [analyst@taosecurity ~]$ mysql -u sguil -p sguildb -e 'select user(), now(), version(), database()' Enter password: +-----------------+---------------------+-----------+------------+ | user() | now() | version() | database() | +-----------------+---------------------+-----------+------------+ | sguil@localhost | 2008-04-11 19:54:40 | 5.0.51a | sguildb | +-----------------+---------------------+-----------+------------+ [analyst@taosecurity ~]$ mysql -u sguil -p sguildb -e 'explain select count(*) from sancp' Enter password: +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away | +----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+ [analyst@taosecurity ~]$ mysql -u sguil -p sguildb -e 'show create table sancp' Enter password: +-+ | Table | Create Table | +-+ | sancp | CREATE TABLE `sancp` ( `sid` int(10) unsigned NOT NULL, `sancpid` bigint(20) unsigned NOT NULL, `start_time` datetime NOT NULL, `end_time` datetime NOT NULL, `duration` int(10) unsigned NOT NULL, `ip_proto` tinyint(3) unsigned NOT NULL, `src_ip` int(10) unsigned default NULL, `src_port` smallint(5) unsigned default NULL, `dst_ip` int(10) unsigned default NULL, `dst_port` smallint(5) unsigned default NULL, `src_pkts` int(10) unsigned NOT NULL, `src_bytes` int(10) unsigned NOT NULL, `dst_pkts` int(10) unsigned NOT NULL, `dst_bytes` int(10) unsigned NOT NULL, `src_flags` tinyint(3) unsigned NOT NULL, `dst_flags` tinyint(3) unsigned NOT NULL, KEY `p_key` (`sid`,`sancpid`), KEY `src_ip` (`src_ip`), KEY `dst_ip` (`dst_ip`), KEY `dst_port` (`dst_port`), KEY `src_port` (`src_port`), KEY `start_time` (`start_time`) ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(`sancp_taosecurity_20071224`) | +-+