Description:
I created a fairly simple table (2 timestamps, and 5 integers) and
started loading data into it, just under 8M rows. I used the LOAD DATA..
command over a Perl DBI connection to insert data every day, around 100k
rows per day.
At random intervals, I have experienced two problems. The first, and
most common, is the dates mangling. Here is an example:
| o+,/-+--,- 20:73:14 |
| ,-,--0)-/0 70:28:66 |
| w/)/-**-0, 78:96:37 |
| x*./-'0-,* 92:97:97 |
| x.'.-0+-// 81:31:25 |
| y0(.-+(-(- 71:86:63 |
| z0'--0.-)( 36:60:84 |
| z0'--0.-)( 36:91:57 |
| �352-96-56 58:21:62 |
| �009-89-64 06:22:26 |
The second problem is MySQL returning positive confirmation of the
LOAD,but no data appearing. This again seems to happen after the table
has sat for a little while. I can use the LOAD command on the mysql
commandline or Perl DBI, get a positive confirmation that the data
inserted, but select statements don't return any of the new data.
Another problem that might be related (although the previous was
occurring before this happened) is when we changed our packet
size from 4M to 64M to accomodate these larger rows we had a number of
performance problems the next day, including lock pileups, server
freezing, and several active tables being corrupted.
One table in particular, delivery_geoip_cache gave us a lot of problems
and the following messages:
Got 127 from Table Handler (when attempting to query them)
---------
Found wrong stored record at 1953276
Wrong bytesec: 112-111-110 at 1953820; Skipped
Wrong bytesec: 76- 69- 84 at 1994264; Skipped
Wrong bytesec: 2- 0- 17 at 6416928; Skipped
(when attempting to fix with myisamchk -r)
---------
We restored the table files from a backup database which was not having
these issues, but immediately the table was corrupted again. Finally
we were able to restore the table by droping and importing from a dump.
The table has the following schema:
CREATE TABLE delivery_geoip_cache (
delivery_geoip_cache_id int(11) unsigned NOT NULL auto_increment,
site_id int(11) NOT NULL default '0',
timerange datetime NOT NULL default '0000-00-00 00:00:00',
count int(11) NOT NULL default '0',
countrycode varchar(4) NOT NULL default '',
PRIMARY KEY (delivery_geoip_cache_id,timerange,site_id),
KEY countrycode (countrycode),
KEY site_countrycode (site_id,countrycode),
KEY timerange (timerange)
) TYPE=MyISAM;
it is part of a series of tables which see a lot of insert action. It
has about 6.5 mil rows and has about a 150M MYD and 300M MYI file
format.
Any help you could provide would be great.
Thank you,
Patrick Schoonveld
schoonveld@ostg.com
How to repeat:
I have been unable to identify when and where these dates go awry. The
files look good (valid dates, etc), and if I drop the table and reinsert
the data, it usually goes in clean. It seems to happen after a period of
time, when new DB connections are made. Interestingly, this particular
issue has started to plague other tables as well. A colleague of mine noted
it in a completely unrelated table that had previously had no issue and
is loaded using INSERT statements.
