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
(http://bugs.mysql.com/) 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.
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
