MySQL Bugs: #21482: LOAD DATA FROM MASTER fails with "Failed rebuilding the index of dumped table"

Bug #21482	LOAD DATA FROM MASTER fails with "Failed rebuilding the index of dumped table"
Submitted:	7 Aug 2006 15:05	Modified:	5 Sep 2006 17:14
Reporter:	Kaspars Foigts	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.24	OS:	Windows (Win32)
Assigned to:		CPU Architecture:	Any
Tags:	replication

Description:
Replication master is 4.1.16 on Linux (Gentoo). Replication slave is set on Windows Server 2003 Standart x64 Edition.

When I try doing "LOAD DATA FROM MASTER" on slave, it fails with error:

ERROR 1187 (HY000): Failed rebuilding the index of  dumped table 'virtual'

Following is my try to fix the problem:

mysql> analyze table virtual;
+--------------+---------+----------+----------+
| Table        | Op      | Msg_type | Msg_text |
+--------------+---------+----------+----------+
| mail.virtual | analyze | status   | OK       |
+--------------+---------+----------+----------+
1 row in set (0.00 sec)

mysql> optimize table virtual;
+--------------+----------+----------+---------------------------------------------------------------------------------+
| Table        | Op       | Msg_type | Msg_text                                                                        |
+--------------+----------+----------+---------------------------------------------------------------------------------+
| mail.virtual | optimize | error    | Table '.\mail\virtual' is marked as crashed and last (automatic?) repair failed |
+--------------+----------+----------+---------------------------------------------------------------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> repair table virtual;

[.. Zillion of similar lines skipped  ..]

| mail.virtual | repair | info     | Found block with too small length at 446436; Skipped                        |
| mail.virtual | repair | info     | Found link that points at 7956016937328534629 (outside data file) at 446476 |
| mail.virtual | repair | info     | Found link that points at 7017587061556601957 (outside data file) at 446532 |
| mail.virtual | repair | info     | Found link that points at 8390891610109601907 (outside data file) at 446580 |
| mail.virtual | repair | info     | Found link that points at 4643340289877763374 (outside data file) at 446652 |
| mail.virtual | repair | info     | Found link that points at 7958775753685428079 (outside data file) at 446700 |
| mail.virtual | repair | info     | Found link that points at 7886717956790316150 (outside data file) at 446760 |
| mail.virtual | repair | info     | Found link that points at 8245080961527722542 (outside data file) at 446812 |
| mail.virtual | repair | info     | Found link that points at 7449361097971348014 (outside data file) at 446868 |
| mail.virtual | repair | info     | Found link that points at 7024036972052898157 (outside data file) at 447028 |
| mail.virtual | repair | info     | Found link that points at 8532461415194976621 (outside data file) at 447136 |
| mail.virtual | repair | info     | Found link that points at 7094969779268829292 (outside data file) at 447192 |
| mail.virtual | repair | error    | Not enough memory for blob at 447232 (need 1819110518)                      |
| mail.virtual | repair | status   | Operation failed                                                            |
+--------------+--------+----------+-----------------------------------------------------------------------------+
10737 rows in set (0.14 sec)

So, nothing helps. Table mail.virtual on master looks like this:

CREATE TABLE `virtual` (
  `alias` varchar(255) NOT NULL default '',
  `dest` longtext,
  `username` varchar(50) NOT NULL default '',
  `status` int(11) NOT NULL default '1',
  UNIQUE KEY `alias_2` (`alias`,`dest`(254)),
  KEY `alias` (`alias`),
  KEY `username` (`username`),
  KEY `status` (`status`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

And its files have sizes as follows:

2.0M    /var/lib/mysql/mail/virtual.MYD
3.4M    /var/lib/mysql/mail/virtual.MYI
12K     /var/lib/mysql/mail/virtual.frm

I know about existing problems when replicating between different versions and even better - between different OSes, and will cope, if bug will be marked as wontfix...

P.S. Problem does not occur when replicating from mentioned Gentoo-ed 4.1.16 to other Gentooed 4.1.8.

How to repeat:
Sorry, but I cannot provide you with a dump of this table because of confidentiality.

Thank you for the bug report. I will try to repeat this bug with data on
my own, however if you can provide privately a dump with few data which
allows to repeat the issue would be nice. Also can you provide the
my.cnf and my.ini files.

Thanks in advance.

Sorry, I removed mysql.ini for 5th mysql on slave machine, because I needed it working. It was the default configuration with utf-8 as default charset, combined myisam and innodb storage, 20 concurrent connections and networking, configured via configuration wizard directly after install.

my.cnf on master (it is combined from various versions and experiments, so - messed up):

[mysqld]
set-variable = max_connections=1000
set-variable = wait_timeout=3600
max_connect_errors = 100000
max_connections = 1000
max_user_connections = 1000
net_read_timeout = 3000
net_write_timeout = 3000
log-slow-queries
log-bin
server-id       = 1
query_cache_size= 256M
set-variable = innodb_buffer_pool_size=1G
skip-bdb
user            = mysql
basedir         = /usr/local/mysql
datadir         = /var/lib/mysql
tmpdir          = /tmp
skip-slave-start
skip-locking
set-variable    = key_buffer_size=128M
set-variable    = max_allowed_packet=100M
set-variable    = thread_stack=128K
bind-address    = 0.0.0.0
port            = 3306

I cannot provide dump because of sensitivity of this data. There are 32259 rows in this table. Average lengths of rows:

alias: 15.94 chars
dest: 14.72 chars
username: 14.96 chars

I guess there should be enough data to try to repeat this problem, if it can be done at all.

Thank you for the bug report. The LOAD DATA FROM MASTER is currently deprecated,
please see: http://dev.mysql.com/doc/refman/5.0/en/load-data-from-master.html.