MySQL Bugs: #27164: Crash when mixing InnoDB and MyISAM Geospatial tables

Bug #27164	Crash when mixing InnoDB and MyISAM Geospatial tables
Submitted:	15 Mar 2007 6:35	Modified:	1 Apr 2007 23:20
Reporter:	Travers Carter	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	4.1.23/5.0.37,5.1.17BK	OS:	Linux (Linux x86_64, suse93 x32)
Assigned to:	Georgi Kodinov	CPU Architecture:	Any
Tags:	crash

Description:
When inserting into a table with a POINT column immediately after creating an InnoDB TABLE, followed by the MyISAM table containing the POINT column the MySQL server frequently crashes (maybe 30-40% of the time).

- The crash occurs on Linux x86_64 (CentOS v4.4)
- The crash doesn't appear to be repeatable on Linux i386 (Fedora Core 6)
- MySQL was installed from the 5.0.37-0.glibc23 RPMs 
- The crash still occurs after clean re-install with mysql_install_db.
- The crash doesn't occur in MySQL 5.0.27

The following appear in the log when the crash occurs
------------------------------
thd=0x1682f30
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x450ca128, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
(nil)
New value of fp=0x1682f30 failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x163e0b0 = INSERT INTO `location` VALUES (1,NULL)
thd->thread_id=2
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

Number of processes running now: 0
--------------------------

How to repeat:
DROP DATABASE IF EXISTS crashtest;
CREATE DATABASE crashtest;

DROP TABLE IF EXISTS `language`;
CREATE TABLE `language` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `Name` varchar(255) NOT NULL default '',
  `Code` varchar(10) NOT NULL default '',
  `Charset` varchar(100) NOT NULL default '',
  `Collation` varchar(100) NOT NULL default '',
  `Rank` int(10) unsigned NOT NULL default '0',
  `DateLocale` varchar(50) NOT NULL default '',
  `Available` tinyint(1) NOT NULL default '0',
  `Status` int(10) unsigned default NULL,
  `FontSize` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `Code` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='For storing available languages';

DROP TABLE IF EXISTS `location`;
CREATE TABLE `location` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `Coordinates` point default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `location` VALUES (1,NULL);

verified as described.

looks like out of memory killer is killing mysqld.  on windows, we get this instead:
ERROR 1041 (HY000): Out of memory; check if mysqld or some other process uses all available memory;

On linux:

070314 16:03:57 [Note] /home/sbester/server/5.0/mysql-5.0.37-linux-i686/bin/mysqld: ready for connections.
Version: '5.0.37-debug'  socket: '/tmp/mysql.sock'  port: 3307  yes

Number of processes running now: 0
070315 21:20:29  mysqld restarted

simpler testcase:

drop table if exists `t1`;
create table `t1`(`a` point)engine=myisam;
insert into `t1` values (NULL);

run it a few times, mostly it will give error message of out of memory, but sometimes it will crash entire server...

The simplified test case only crashed once out of around hundred executions in my environment (including trying a drop/re-create of the database and restarting the daemon in between a few of tests).

My test system has 1G of physical ram and 2G of swap space.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22782

ChangeSet@1.2417, 2007-03-23 16:25:55+02:00, gkodinov@magare.gmz +3 -0
  Bug #27164: not reseting the data pointer
  to 0 causes wrong (large) length to be read from the
  row when storing NULL values to POINT columns.
  This large length is then used to allocate a block of
  memory that (on some OSes) causes the trouble.
  Fixed by calling the base class reset() method from
  Field_geom::reset() that is called when storing a NULL 
  value into the column.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22915

ChangeSet@1.2417, 2007-03-26 10:52:41+03:00, gkodinov@magare.gmz +3 -0
  Bug #27164: not reseting the data pointer
  to 0 causes wrong (large) length to be read from the
  row when storing NULL values to POINT columns.
  This large length is then used to allocate a block of
  memory that (on some OSes) causes the trouble.
  Fixed by calling the base class reset() method from
  Field_geom::reset() that is called when storing a NULL 
  value into the column.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/22931

ChangeSet@1.2625, 2007-03-26 13:17:40+03:00, gkodinov@magare.gmz +3 -0
  Bug #27164: not reseting the data pointer
   to 0 causes wrong (large) length to be read
   from the row in _mi_calc_blob_length() when 
   storing NULL values in (e.g) POINT columns.
   This large length is then used to allocate
   a block of memory that (on some OSes) causes
   trouble.
   Fixed by calling the base class's 
   Field_blob::reset() from Field_geom::reset()
   that is called when storing a NULL value into
   the column.

Pushed into 5.1.18-beta

Pushed into 5.0.40

Pushed into 4.1.23

Noted in 4.1.23, 5.0.40, 5.1.18 changelogs.

Storing NULL values in spatial fields caused excessive memory
allocation and crashes on some systems.