List:General Discussion« Previous MessageNext Message »
From:mos Date:July 28 2008 2:39pm
Subject:Re: Adding index to Memory table LOSES ROWS!
View as plain text  
At 03:31 PM 7/27/2008, Kevin Hunter wrote:
>At 12:42p -0400 on Sat, 26 Jul 2008, mos wrote:
> > At 09:14 AM 7/26/2008, you wrote:
> >> The reproducible part is very important, because without it, it's
> >> suspect to be just your individual case, as with a bug in *your*
> >> application code, your hardware, or generally something in your setup.
> >
> > Well, I thought it might be my code too. That's why when the memory
> > table was built (without the index), I went to a SqlMgr and counted the
> > rows. None of the rows were missing. Then I did an Alter Table and added
> > one index to the memory table, and sure enough when it was finished 75%
> > of the rows were missing and no error was reported. Only the rows for
> > index values "A" to "E" were in the table so the indexing lost rows.
>
>That suggests to me a couple of things, both bugs with MySQL:
>
>- an out of memory error - MySQL should *not* fail, but tell you it
>   can't complete and return you to a known state.  An RDBMS should
>   *never* lose data.  Ever.

I agree. It took me 2 days to figure out the problem was MySQL and not my 
code. Over the weekend I was able to replicate the problem using 2 SQL 
statements.

create table MemTable engine=MEMORY select * from MyISAMTable;
-- All rows are copied correctly
alter table MemTable add index ix_Main(Prod_Code, Prod_Date);
-- Now only part of the MemTable is there and no error is reported
-- This time it lost records after "S". In my application it loses rows 
after "E" probably because I had other memory tables allocated which used 
up more memory. Still plenty of RAM available on the machine so it could be 
related to a .cnf setting.

It appears it is running out of memory for the Alter Table and leaving 
MemTable in damaged state. Maybe MySQL is trying to conserve memory by 
altering a Memory table without backing up the original table?


>- a piece of data in one of the rows of processing that MySQL doesn't
>   like, and therefore gives unexpected results.

If it always stopped on the same row, I'd agree. But it loses rows at 
different spots depending on how much memory is being used by other memory 
tables.

>This is definitely a
>   bug as this should not happen to begin with, and "An RDBMS should
>   *never* lose data.  Ever."

Amen. You're preaching to the choir here. :)

>Summary: I don't know what's up and have not encountered this.  But if
>you can, create a small test case that can reproduce the error.  Then
>fill out a bug at http://bugs.mysql.com/ .  Loss of data is absolutely a
>bug, and a critical one.

I will have to fine tune it to see if I can reduce the table size and play 
with the cnf settings. I don't think MySQL is checking for enough memory 
being available for an Alter Table statement before it runs it.

Mike


>A quick (< 3min) perusal of the bugs currently open did return any
>meaningful results.
>
>Kevin
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
Adding index to Memory table LOSES ROWS!mos25 Jul
  • Re: Adding index to Memory table LOSES ROWS!Kevin Hunter26 Jul
    • Re: Adding index to Memory table LOSES ROWS!mos26 Jul
      • Re: Adding index to Memory table LOSES ROWS!Kevin Hunter27 Jul
        • Re: Adding index to Memory table LOSES ROWS!mos28 Jul