Bug 452824

Looks like you've already filed this upstream, which is really the correct place for it.  I don't see anything 
else in their bugzilla that looks like they might have recently fixed such a bug, so that knee-jerk advice 
about updating to a (slightly) newer version doesn't sound helpful at all.  What you'll need to do is provide 
them with enough info to reproduce the problem --- the whole query, the table definitions, and perhaps 
some test data too.

mysql verify in the upstream bugreport that it's a bug in 5.0.45 which is fixed
in 52. would it be possible to somehow patch 45?

Not with no patch provided, and 5.0.52 not released ...

yes but with the current rh's mysql the server repeatedly crash (more than once
a day) while if i downgrade to rhel-5.1's mysql-server the problem disappear. so
the current version is worse then the previous, imho rh should have to try to
fix it or downgrade it's mysql-server to a previous version.

there are much newer mysql version and those version fix this bug. it'd be useful to upgade to newer version or at least backport their fix to the current rhel-5's mysql.

any progress?

please, could you add the exact steps how to reproduce the problem?

- I've tried to refine it from the upstream bug report but had no success to reproduce the crash

my try:

1. install mysql-5.0.45-7.el5
2. service mysqld start
3. create tables - see below
4. tail -f /var/log/mysqld.log

none of the supported architectures experienced a crash


SQL code used to create the tables:

create database bz452824;
use bz452824;

CREATE TABLE `geo_road_custom` (
  `ID` int(11) NOT NULL,
  `NAME` varchar(50) default NULL,
  `TYPE` varchar(20) default NULL,
  `POLYLINE` geometry NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `ID` (`ID`),
  SPATIAL KEY `POLYLINE` (`POLYLINE`(32))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL
SECURITY DEFINER VIEW `geo_road_custom_ordered` AS select `r`.`ID` AS `ID`,`r`.`NAME` AS
`NAME`,`r`.`TYPE` AS `TYPE`,`r`.`POLYLINE` AS `POLYLINE` from `geo_road_custom` `r` order
by `r`.`TYPE` desc,`r`.`POLYLINE` desc;

CREATE TABLE `geo_road_base` (
  `ID` int(11) NOT NULL,
  `NAME` varchar(50) default NULL,
  `TYPE` varchar(20) default NULL,
  `POLYLINE` geometry NOT NULL,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `ID` (`ID`),
  SPATIAL KEY `POLYLINE` (`POLYLINE`(32))
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER
VIEW `geo_road` AS select `r`.`ID` AS `ID`,`r`.`NAME` AS `NAME`,`r`.`TYPE` AS
`TYPE`,`r`.`POLYLINE` AS `POLYLINE` from `geo_road_base` `r` where ((`r`.`TYPE` =
_utf8'Highway') or (`r`.`TYPE` = _utf8'Freeway')) order by `r`.`TYPE` desc,`r`.`POLYLINE`
desc;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY
DEFINER VIEW `geo_all_road` AS select `rc`.`ID` AS `ID`,`rc`.`NAME` AS `NAME`,`rc`.`TYPE`
AS `TYPE`,`rc`.`POLYLINE` AS `POLYLINE` from `geo_road_custom_ordered` `rc` union select
`r`.`ID` AS `ID`,`r`.`NAME` AS `NAME`,`r`.`TYPE` AS `TYPE`,`r`.`POLYLINE` AS `POLYLINE`
from `geo_road` `r`;

the real problem is that the datebase is google maps's database which is commercial so i can't send the whole database to you. that was the reason why i send the table format to the upstream bugreport where they confirm the problem. i can send you the same set of table format, but not too much more. anyway it was on both i386 and x86_64 and mysql-5.0.45 if you simple upgrade mysql to at least  mysql-5.0.52 than it solves the problem.

An advisory has been issued which should help the problem
described in this bug report. This report is therefore being
closed with a resolution of ERRATA. For more information
on therefore solution and/or where to find the updated files,
please follow the link below. You may reopen this bug report
if the solution does not work for you.

http://rhn.redhat.com/errata/RHSA-2009-1289.html