Bug #5847 Data importing issues
Submitted: 1 Oct 2004 14:05 Modified: 5 Nov 2004 22:46
Reporter: Patrick Schoonveld Email Updates:
Status: No Feedback Impact on me:
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.20 OS:Linux (Linux (RH w/ custom 2.4.20))
Assigned to: CPU Architecture:Any

[1 Oct 2004 14:05] Patrick Schoonveld
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)

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

Any help you could provide would be great.

Thank you,
Patrick Schoonveld

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.
[5 Oct 2004 22:46] MySQL Verification Team
I wasn't able to repeat the behavior reported with the table delivery_geoip_cache.
If you can provide us the file data and the script used when that happens
would be nice.
You can upload the files at:


making reference to this bug report and notify here when done.

[14 Feb 2005 22:54] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".