List:General Discussion« Previous MessageNext Message »
From:mos Date:July 26 2008 4:42pm
Subject:Re: Adding index to Memory table LOSES ROWS!
View as plain text  
At 09:14 AM 7/26/2008, you wrote:
>At 5:52p -0400 on Fri, 25 Jul 2008, mos wrote:
>[Adding index to memory table silently loses data]
>First thing, what version are you using?

MySQL 5.0.24a on Windows XP Pro with 3gb RAM. The server is on the same 
machine as the client since I'm the only one using the database.

>Second thing, although it would still be broken, did you read the MySQL
>docs to make sure that the Memory table type supports the index *type*
>you attempted to add?

It is a compound index of ProdCode Char(17) and Date. I just let it use the 
default index type (hash?) for the memory table.

> > 1) Why does adding an index lose the rows?
> > 2) Why wasn't an error reported?
>[ squint ]
>My first thought is that MySQL and the Memory table type are used by
>*lotsa* folks, with success, so I find it difficult to believe that this
>wouldn't have been caught well before now.

Well, the memory table is around 300MB and I don't know if people have 
tables that large.
I should have mentioned that this process works for the first dozen 
iterations. I'm processing years of data and it doesn't fail until the year 
2007. Each year after the memory table is created, it updates a MyISAM 
table and the memory table is dropped before repeating the process for 
another year. I'm wondering if MySQL or Windows is running out of resources?

Now there are about a dozen variables used in the calculations for some of 
the columns. Am I suppose to clear these variables before they are re-used?

>Are you positive this problem isn't hardware related?  A few bad memory
>locations could easily explain this.

I'm pretty sure. This is repeatable on the same year of data (2007) over 
and over again. If it were RAM then it would likely be more random. I 
suspect a couple more years may be missing data too.

>If you're certain this is a bug in MySQL, creating a small,
>*reproducible* test case and reporting this through the proper channels
>( will go much further to fixing the problem.
>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. I dropped the index 
and the rows were still missing. So that rules out my code.  MySQL may be 
running out of RAM for the memory table (still plenty of RAM available on 
the machine). I may have to keep bumping up Max_Heap_Table_Size and 
Tmp_Table_Size to see if it solves the problem. Even if it does work, I 
can't really trust memory tables because it doesn't give me an error when 
it fails. It just keeps on going as if nothing has happened. That's what 
scares me.


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