I am getting table corruption on one machine that I was testing a change
to get better performance.
This is using the C API to connect to the database. With the delete
without
the sort by id, I started getting the database key corruption. There
were about
2.6 million rows matched to be deleted, so I also limited to delete only
normally less
than 6000 at a time out of about 3.9 million rows. It is still
happening with 2.6 million
records and 1.3 million marked for deletion.
mysql> check table mzLog;
+--------------+-------+----------+-------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------+-------+----------+-------------------------------+
| mazama.mzLog | check | warning | Table is marked as crashed |
| mazama.mzLog | check | error | Found 2636585 keys of 2636586 |
| mazama.mzLog | check | error | Corrupt |
+--------------+-------+----------+-------------------------------+
My understanding was that if the table was locked write when deleting or
writing that the keys should be safe. Perhaps there is something else
I could
do to resolve this.
1. Add a entry:
Lock the table..
add a entry
unlock the table
or
2. Delete old entries no longer needed.
lock
Count the number of entries that match.
unlock
Lock the table
delete any logs marked for deletion up to 5000 at a time
(previously I did a sort by id LIMIT 5000), but the
sort was taking too much time, since 2.7 million
records
matched).
unlock table
or
3. List/search/query entries.
lock
count the number of matches (since we need to take a
limited number at time)
unlock
lock
query (perhaps loop with taking 5000 matches at a time
in get the next with the last id).
unlock
DROP TABLE IF EXISTS `mzLog`;
CREATE TABLE `mzLog` (
`hostname` varchar(20) default NULL,
`username` varchar(20) default NULL,
`vNodeID` int(11) NOT NULL,
`serialNumber` int(32) default NULL,
`physloc` varchar(20) default NULL,
`osID` int(11) default NULL,
`kernelID` int(11) default NULL,
`partitionID` int(11) default NULL,
`facility` varchar(10) default NULL,
`priority` varchar(10) default NULL,
`level` int(8) default -1,
`tag` varchar(10) default NULL,
`time` timestamp NOT NULL default CURRENT_TIMESTAMP,
`program` varchar(40) default NULL,
`msg` text,
`action` int(11) default '1',
`id` int(10) unsigned NOT NULL auto_increment,
`bootsession` int(20) default NULL,
`smw` varchar(32) default NULL,
`state` int(2) default -1,
`deletedate` timestamp NOT NULL,
`orig_time` timestamp NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `hostname` (`hostname`),
KEY `id` (`id`),
KEY `program` (`program`),
KEY `facility` (`facility`),
KEY `physloc` (`physloc`),
KEY `time` (`time`),
KEY `orig_time` (`orig_time`),
KEY `deletedate` (`deletedate`),
KEY `smw` (`smw`),
KEY `msg` (`msg`(1000)),
KEY `level` (`level`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
