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=<dbdir> 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=<dbdir> 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`) |
+-+