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