MySQL Bugs: #31514: temprary merge table

Bug #31514	temprary merge table
Submitted:	10 Oct 2007 18:57	Modified:	10 Nov 2007 20:23
Reporter:	Thiago Dias Fascin	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S2 (Serious)
Version:	5.0.27-standard-log	OS:	Linux (Fedora)
Assigned to:		CPU Architecture:	Any
Tags:	merge;temprary table;crash

Description:
Hi, my problem is, i'm receiving this error sometimes when i execute one select: Error: Table 'c' is marked as crashed and should be repaired. But the 'c' table is a temporary merge table.

I have 5 servers in a replication structure (db01, db02, db03, db04, db05). The db01 is the master and db02 and db03 has the same configurations as the master.

The commands at "How to repeat" are execute only at db02 and db03. And the command "CREATE TEMPORARY TABLE `mrg`" is executed in a stored procedure.

What is happen?

Tks.

How to repeat:
CREATE TABLE `t_1` (
  `id` varchar(32) NOT NULL default '',
  `date` datetime NOT NULL default '0000-00-00 00:00:00'
  PRIMARY KEY  (`id`),
  KEY `date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `t_2` (
  `id` varchar(32) NOT NULL default '',
  `date` datetime NOT NULL default '0000-00-00 00:00:00'
  PRIMARY KEY  (`id`),
  KEY `date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `t_3` (
  `id` varchar(32) NOT NULL default '',
  `date` datetime NOT NULL default '0000-00-00 00:00:00'
  PRIMARY KEY  (`id`),
  KEY `date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TEMPORARY TABLE `mrg` (
  `id` varchar(32) NOT NULL default '',
  `date` datetime NOT NULL default '0000-00-00 00:00:00'
  PRIMARY KEY  (`id`),
  KEY `callDate` (`date`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=FIRST UNION=(`db`.`t_1`,`db`.`t_2`,`db`.`t_3`);

CREATE TABLE `t_4` (
  `id` varchar(32) NOT NULL default '',
  PRIMARY KEY  (`charge_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

insert into db.t_1 ( '1', now() );
insert into db.t_2 ( '2', now() );
insert into db.t_3 ( '3', now() );

insert into db.t_4 ( '1', now() );
insert into db.t_4 ( '2', now() );
insert into db.t_4 ( '3', now() );

SELECT c.*
FROM mrg AS c
JOIN t_4 AS rc ON rc.id = c.id
GROUP BY rc.id
HAVING (x > y AND date = 0)

Table 'c' is marked as crashed and should be repaired;

Thank you for the report.

But version 5.0.27 is quite old and many bugs have been fixed since. Please upgrade to current version 5.0.45, try with it and inform us if problem still exists.

If you can repeat error with version 5.0.45 please provide correct test case with valid insert and select statements.

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