MySQL Bugs: #11528: mysqldump with XML crashes on huge innoDB table

Bug #11528	mysqldump with XML crashes on huge innoDB table
Submitted:	23 Jun 2005 12:49	Modified:	22 Nov 2005 16:51
Reporter:	Christian Hammers (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: XML functions	Severity:	S3 (Non-critical)
Version:	4.0.23	OS:	Linux (Debian GNU/Linux 3.1)
Assigned to:		CPU Architecture:	Any

Description:
From the Debian bug report available at http://bugs.debian.org/315524

On 2005-06-23 Joerg Rieger wrote:
> On Thu, Jun 23, 2005 at 02:00:37PM +0200, Christian Hammers wrote:
> > On 2005-06-23 Joerg Rieger wrote:
> > > while running a wikipedia database export as XML export the following
> > > error occurs reproducible:
> > > 
> > > # time mysql -u root -p -X -e "select cur_title, cur_text from cur" 
> > > wikidb > wiki.xml
> > > Enter password:
> > > ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during 
> > 
> > I've never encountered it and can't reproduce it with an arbitrary
> > table.
> > 
> > Does this problem only occur with a specific table? Does it continue
> > after you do an "REPAIR TABLE cur EXTENDED;" on it? If you, could you
> > send me a tabledump or a zipped database file so that I can give it
> > to the MySQL developers for investigation?
> > 
> > If you know about any anomalies like special charsets, binary data or
> > table formats else than innodb/myisam for this table it could be good to
> > know, too.
> 
> It's a database dump of the german (de) wikipedia[1] database, like 
> a month old or so. Because of that, the size of the database is quite 
> huge (roughly 1,8 GB). If you still want the file, I could upload it to 
> a server, but that'll take a while :-)
> 
> Since they use InnoDB as storage engine a repair table doesn't work:
...
 > I also have a wikinews DB within the same mysql installation, XML 
> export works fine with this one, short example:
> 
> <?xml version="1.0"?>
> 
> <resultset statement="select cur_title, cur_text from cur">
>   <row>
>           <cur_title>Hauptseite</cur_title>
> .
> .
> .
> 
>  
> However the size is considerably less than the other wiki DB (only 
> about 7,5 MB).
> 
> As far as I can recall, I have made a successfull XML dump of that 
> big wiki DB on the same machine a while ago. Maybe a feature backport 
> from mysql 5? But changelog doesn't state anything like it, so probaply 
> not.
> 
> 
> 
> 
> [1] http://download.wikimedia.org/
>

More information from the submitter can be asked by replying to 
    315524@bugs.debian.org

bye,

-christian-

How to repeat:
see above and d/l the gigabyte table :)

Suggested fix:
-

Hi!

Can you please tell me exactly what the table's CREATE statement is, and how many records does it have ?

... it will be easier for me to create a new GB table than to d/l one !

Thanks

BTW: the server version is 4.1.12 not 4.0.23.

The statement is:

mysql> show create table cur;

CREATE TABLE `cur` (
  `cur_id` int(8) unsigned NOT NULL auto_increment,
  `cur_namespace` tinyint(2) unsigned NOT NULL default '0',
  `cur_title` varchar(255) character set latin1 collate latin1_bin NOT NULL default '',
  `cur_text` mediumtext NOT NULL,
  `cur_comment` tinyblob NOT NULL,
  `cur_user` int(5) unsigned NOT NULL default '0',
  `cur_user_text` varchar(255) character set latin1 collate latin1_bin NOT NULL default '',
  `cur_timestamp` varchar(14) character set latin1 collate latin1_bin NOT NULL default '',
  `cur_restrictions` tinyblob NOT NULL,
  `cur_counter` bigint(20) unsigned NOT NULL default '0',
  `cur_is_redirect` tinyint(1) unsigned NOT NULL default '0',
  `cur_minor_edit` tinyint(1) unsigned NOT NULL default '0',
  `cur_is_new` tinyint(1) unsigned NOT NULL default '0',
  `cur_random` double unsigned NOT NULL default '0',
  `inverse_timestamp` varchar(14) character set latin1 collate latin1_bin NOT NULL default '',
  `cur_touched` varchar(14) character set latin1 collate latin1_bin NOT NULL default '',
  UNIQUE KEY `cur_id` (`cur_id`),
  UNIQUE KEY `name_title_dup_prevention` (`cur_namespace`,`cur_title`),
  KEY `cur_title` (`cur_title`),
  KEY `cur_timestamp` (`cur_timestamp`),
  KEY `cur_random` (`cur_random`),
  KEY `name_title_timestamp` (`cur_namespace`,`cur_title`,`inverse_timestamp`),
  KEY `user_timestamp` (`cur_user`,`inverse_timestamp`),
  KEY `usertext_timestamp` (`cur_user_text`,`inverse_timestamp`),
  KEY `jamesspecialpages` (`cur_is_redirect`,`cur_namespace`,`cur_title`,`cur_timestamp`),
  KEY `id_title_ns_red` (`cur_id`,`cur_title`,`cur_namespace`,`cur_is_redirect`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=1 |

mysql> select count(*) from cur;
+----------+
| count(*) |
+----------+
|   594599 |
+----------+
1 row in set (1 min 48.59 sec)

I have tried to reproduce this bug in 4.0, 4.1, and 5.0 without success.  I tested it under FC4 and had a 1.5GB dump.