MySQL Bugs: #844: Importing a large number of records leads to 'error 127 from the table handler'

Bug #844	Importing a large number of records leads to 'error 127 from the table handler'
Submitted:	14 Jul 2003 15:52	Modified:	14 Feb 2005 23:49
Reporter:	Daniel Kasak (Candidate Quality Contributor)	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S2 (Serious)
Version:	4.0.13	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
I'm running Slackware 8.1.
Everything is compiled with gcc-2.95.3

The server is an AMD K6-2 500Mhz with 256MB of RAM. It doesn't appear to have any hardware / software issues...

Build environment variables:

CFLAGS="-O2 -march=k6-2"
CXX=gcc
CXXFLAGS="-O2 -march=k6-2 -felide-constructors"

Configure options:

--prefix=/usr/local/mysql-4.0.13
--enable-thread-safe-client
--enable-local-infile
--enable-assembler

The problem occurs after using 'load data infile' on a large csv file ( in this case around 1,000,000 rows ) into a MyISAM table. The table is truncated each time before use ( and also usually has myisamchk run on it ). Soon after the load data infile operation, I will get 'error 127 from the table handler' when selecting from or updating the table. The error occurs at a different place each time ( as far as I can tell ).

See 'how to repeat' for specifics...

How to repeat:
Note - I've used 'mysqldump -d' to only give table defs. If you need actual data, I can provide it - I left it out mainly for size reasons...

In a database 'RSLCom':

CREATE TABLE CallData (
  Association char(1) default '',
  RSLCOMID varchar(10) default '',
  Client varchar(60) default '',
  Suburb varchar(30) default '',
  Address_1 varchar(50) default '',
  Address_2 varchar(50) default '',
  Address_3 varchar(50) default '',
  State char(3) default '',
  Postcode smallint(5) unsigned default '0',
  ServiceType_1 varchar(50) default '',
  ServiceType_2 varchar(50) default '',
  AccountNo varchar(10) default '',
  BillingGroup varchar(50) default '',
  CallingNo varchar(20) default '',
  CalledNo varchar(25) default '',
  CallTypeID varchar(20) default '',
  CallType varchar(60) default '',
  Date_STRING varchar(20) default NULL,
  Time_STRING varchar(20) default NULL,
  Seconds mediumint(8) unsigned default '0',
  Cost_STRING varchar(15) default NULL,
  Retail_STRING varchar(15) default NULL,
  GST_STRING varchar(15) default NULL,
  CallSource varchar(50) default '',
  Destination varchar(50) default '',
  FromPrefix varchar(50) default '',
  ToPrefix varchar(50) default '',
  LocalCalls varchar(255) default '',
  Source char(2) default '',
  ID int(10) unsigned NOT NULL auto_increment,
  MyStamp timestamp(14) NOT NULL,
  Date date default '0000-00-00',
  Time datetime default '0000-00-00 00:00:00',
  Cost double NOT NULL default '0',
  Retail double NOT NULL default '0',
  GST double NOT NULL default '0',
  BillingCode varchar(10) NOT NULL default '',
  LocalCallNo mediumint(8) unsigned NOT NULL default '0',
  LocalCallRate double NOT NULL default '0',
  BLines mediumint(8) unsigned NOT NULL default '0',
  ServEquipLosing double NOT NULL default '0',
  PRIMARY KEY  (ID),
  KEY IDX_Client (Client),
  KEY IDX_BillingCode (BillingCode),
  KEY IDX_RSLCOMID (RSLCOMID),
  KEY IDX_AccountNo (AccountNo),
  KEY IDX_CallingNo (CallingNo)
) TYPE=MyISAM;

CREATE TABLE LinkTable (
  MyStamp timestamp(14) NOT NULL,
  RSLCOMID varchar(10) default '',
  RSLCOMAcct varchar(10) default '',
  RSLCOMBillingCode varchar(10) NOT NULL default '',
  FileNo mediumint(8) unsigned default '0',
  SubNo tinyint(3) unsigned default '0',
  SavingNo mediumint(8) unsigned default '0',
  Local double default '0',
  Nat double default '0',
  Intl double default '0',
  Mob double default '0',
  OneEight double default '0',
  Service double default '0',
  SE1Type smallint(5) unsigned default '0',
  SE1No smallint(5) unsigned default '0',
  SE2Type smallint(5) unsigned default '0',
  SE2No smallint(5) unsigned default '0',
  No1Date date default NULL,
  BillingLevel tinyint(3) unsigned default '0',
  ServiceLevel tinyint(3) unsigned default '0',
  PABX tinyint(3) unsigned default '0',
  Comment tinytext,
  PRIMARY KEY  (RSLCOMBillingCode),
  KEY IDX_RSLCOMID (RSLCOMID),
  KEY IDX_RSLCOMAcct (RSLCOMAcct),
  KEY IDX_FileNo_SubNo (FileNo,SubNo),
  KEY IDX_SavingNo (SavingNo)
) TYPE=MyISAM;

CREATE TABLE SEPremiums (
  SEID tinyint(3) unsigned NOT NULL auto_increment,
  MyStamp timestamp(14) NOT NULL,
  Rate double default '0',
  EffectiveDate date NOT NULL default '0000-00-00',
  Charge double default '0',
  PRIMARY KEY  (SEID,EffectiveDate)
) TYPE=MyISAM;

CREATE TABLE TempBilledAccountNos (
  AccountNo varchar(30) NOT NULL default '',
  MyStamp timestamp(14) NOT NULL
) TYPE=MyISAM;

---

The Perl script that controls the import process ( import_rslcom.pl ):

#!/usr/bin/perl -w

use strict ;
use DBI ;

my $dbh = DBI->connect( 'dbi:mysql:RSLCom','root','nusaus168' ) || die "Database connection not made: $DBI::errstr" ;

print (localtime() . "\nTruncating CallData...\n\n") ;

my $sql = qq{ truncate table CallData } ;
my $sth = $dbh->prepare($sql) ;
$sth->execute() ;

print (localtime() . "\nImporting csv file into MySQL...\n\n") ;

$sql = qq{ load data infile '/usr/local/bills_on_disk/ElectronicBills2MySQL/RSLCom_Consortium.txt'
                into table CallData
                fields terminated by ','
                optionally enclosed by '\"'
                lines terminated by '\\r\\n'
                ignore 1 lines } ;

$sth = $dbh->prepare($sql) ;
$sth->execute() ;

print (localtime() . "\nRepairing Date, Time & Currency fields and getting local calls stuff...\n\n") ;

$sql = qq{ update CallData set
        Date=concat(substring(Date_STRING,locate('/',Date_STRING)+4,4), '-',
        substring(Date_STRING,locate('/',Date_STRING)+1,2), '-',
        if(locate('/',Date_STRING)=2,concat('0',left(Date_STRING,1)),left(Date_STRING,2))),
        Time=concat('1899-12-30 ', right(Time_STRING,8)),
        Cost=right(Cost_STRING,length(Cost_STRING)-1),
        Retail=right(Retail_STRING,length(Retail_STRING)-1),
        GST=right(GST_STRING,length(GST_STRING)-1),
        LocalCallNo=if(locate('Local Call',LocalCalls)>0,left(LocalCalls,locate(' ',LocalCalls)),0),
        LocalCallRate=if(locate('Local Call',LocalCalls)>0,Cost*100/left(LocalCalls,locate(' ',LocalCalls)),0) } ;

$sth = $dbh->prepare($sql) ;
$sth->execute() ;

print (localtime() . "\nUpdating BillingCode from LinkTable...\n\n") ;

$sql = qq{ update CallData inner join LinkTable
        on CallData.RSLCOMID=LinkTable.RSLCOMID
        set CallData.BillingCode=if(LinkTable.BillingLevel=1,CallData.RSLCOMID,CallData.AccountNo) } ;

$sth = $dbh->prepare($sql) ;
$sth->execute() ;

print (localtime() . "\nMaking a list of distinct clients...\n\n") ;

$sql = qq{ drop table if exists DistinctClients } ;
$sth = $dbh->prepare($sql) ;
$sth->execute() ;

$sql = qq{ create table DistinctClients
        select RSLCOMID, AccountNo, Client
        from CallData group by RSLCOMID, AccountNo, Client } ;

$sth = $dbh->prepare($sql) ;
$sth->execute() ;

print (localtime() . "\nMaking a list of billed accounts...\n\n") ;

$sql = "truncate table TempBilledAccountNos" ;
$sth = $dbh->prepare($sql) ;
$sth->execute() ;

$sql = qq{ create temporary table tmp_TempBilledAccountNos
        select CallData.AccountNo from LinkTable inner join CallData
        on LinkTable.RSLCOMBillingCode=CallData.BillingCode
        left join TempBilledAccountNos
        on CallData.AccountNo=TempBilledAccountNos.AccountNo
        where TempBilledAccountNos.AccountNo is null
        and LinkTable.SavingNo is not null
        group by CallData.AccountNo } ;

$sth = $dbh->prepare($sql) ;
$sth->execute() ;

print (localtime() . "\nMaking a list of billed accounts...\n\n") ;

$sql = "truncate table TempBilledAccountNos" ;
$sth = $dbh->prepare($sql) ;
$sth->execute() ;

$sql = qq{ create temporary table tmp_TempBilledAccountNos
        select CallData.AccountNo from LinkTable inner join CallData
        on LinkTable.RSLCOMBillingCode=CallData.BillingCode
        left join TempBilledAccountNos
        on CallData.AccountNo=TempBilledAccountNos.AccountNo
        where TempBilledAccountNos.AccountNo is null
        and LinkTable.SavingNo is not null
        group by CallData.AccountNo } ;

$sth = $dbh->prepare($sql) ;
$sth->execute() ;

$sql = qq{ insert into TempBilledAccountNos ( AccountNo ) select * from tmp_TempBilledAccountNos } ;
$sth = $dbh->prepare($sql) ;
$sth->execute() ;

print (localtime() . "\nMatching SNE charges with SEPremiums table...\n\n") ;

$sql = qq{ select EffectiveDate, Charge from SEPremiums where Charge is not null order by EffectiveDate desc } ;
$sth = $dbh->prepare($sql) ;
$sth->execute() ;

my $previousdate="" ;

while ( my $row=$sth->fetchrow_hashref ) {
        $sql = qq{ update CallData
                set BLines=left(LocalCalls,locate(' ', LocalCalls)-1)
                where CallData.Date>='$row->{EffectiveDate}' } ;

        if ($previousdate ne "") {
                $sql .= qq{ and CallData.Date<'$previousdate' } ;
        }

        $sql .= qq{ and Cost/left(LocalCalls,locate(' ', LocalCalls)-1)=$row->{Charge}
                and locate('Business T', LocalCalls)>0 } ;

        my $cmd = $dbh->prepare($sql) ;
        $cmd->execute() ;

        $previousdate = $row->{EffectiveDate} ;
}

print (localtime() . "\nFinished!\n\n") ;

$sth->finish() ;
$dbh->disconnect() ;

---

Typical Output from above script:

bash-2.05# ./import_rslcom.pl 
Mon Jul 14 15:54:15 2003
Truncating CallData...

Mon Jul 14 15:54:16 2003
Importing csv file into MySQL...

Mon Jul 14 16:18:34 2003
Repairing Date, Time & Currency fields and getting local calls stuff...

Mon Jul 14 16:27:38 2003
Updating BillingCode from LinkTable...

Mon Jul 14 17:05:39 2003
Making a list of distinct clients...

Mon Jul 14 17:07:52 2003
Making a list of billed accounts...

DBD::mysql::st execute failed: Got error 127 from table handler at ./import_rslcom.pl line 86.
Mon Jul 14 17:08:27 2003
Matching SNE charges with SEPremiums table...

Mon Jul 14 17:12:10 2003
Finished!

bash-2.05# 

Suggested fix:
No idea.
I admit that it could be a compiler bug ( I used the -march=k6 flag ) but I haven't had any other issues with MySQL compiled in this way ... everything else is running smoothly enough.

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

With random data I was not able to repeat this crash.

Can you please provide some lines for loading which can expose the bug?

If it only happens with large file please compress it and upload it to
ftp://support.mysql.com/pub/mysql/secret/

I did not meant to close it with my previous comment.

Daniel,

Please help us investigate this bug. 
Your reports is perfect. Sometimes such bugs are data dependant.

I've uploaded a tar.bz2 file which has table dumps of all the necessary tables, the import script and also the file to import from ( it's huge ).

The upload is called bug_no_844_-_MyIsam_error_127.tar.bz2 and I put it in the /pub/mysql/secret folder on support.mysql.com

Hopefully someone can reproduce the problem. Alternativley, it could be a gcc bug ( again: I used -march=k6 to compile it ).

It's not particularly urgent for us - this particular system is still in development, and we can always switch to InnoDB ( assuming it doesn't do the same thing ).

Thanks for such a quick response.

Daniel,

Thank you for excelent test case!

Unfortunately I was unable to reproduce corruption. 

It is very important to try repeating corruption with our binary distribution. 

If our binary is safe then problem is with your build (which should be also examined).

Can you also upload corrupted table just after you find this corruption? It might give us some clue what happened.

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".

Oops! I didn't realise this bug was still open.

I've long since assumed it was either a compiler bug or a hardware issue.
Either way, we've upgraded from our K6-2 to an Athlon XP, and have not any any similar issues.

If something is particularly keen to rule out the possibility of a compiler bug, I can track down another K6-2 system and try to reproduce.

Otherwise, assume this was a hardware issue and close the bug :)