Bug #25792 mysql server crashed on table(trigger) insert
Submitted: 23 Jan 2007 17:50 Modified: 9 Jun 2008 16:31
Reporter: Pablo Calder?n Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S1 (Critical)
Version:5.0.27 OS:Linux (suse 10)
Assigned to: Miguel Solorzano CPU Architecture:Any

[23 Jan 2007 17:50] Pablo Calder?n
Description:
full increment mysql memory until server crashed on insert into eventoFinal table.

I'm not speak english, please Excuse me. I'm very Distressed. thanks..  

How to repeat:
build this trigger:

create trigger `hEvento` AFTER INSERT on `eventoFinal` 
for each row BEGIN
	DECLARE hEventoId, odometroValue INT;
	DECLARE direccion, descripcionEvento CHAR(100);
	DECLARE orientacion, idMovilHevento CHAR(30);
	IF( NEW.id > 0 ) THEN
		SET hEventoId = NEW.id;
	ELSE	
		SET hEventoId = LAST_INSERT_ID();
	END IF;
	SET odometroValue = NEW.odometro;
	SET direccion = getDireccion( NEW.x, NEW.y, '/home/Read/GeodatosCO_v2/mapa.map', '/home/Read/GeodatosCO_v2' );
	SET orientacion = getOrientacion( NEW.heading, NEW.velocidad );
	SELECT patente INTO idMovilHevento FROM movil WHERE idVehiculo = NEW.idVehiculo;
	SELECT descripcion INTO descripcionEvento FROM tipoEvento WHERE idTipoEvento = NEW.tipo;
	INSERT IGNORE INTO H_Evento( idEvento, idEventoTipo, idMovil, timeStamp, velocidad, odometro, lugar, sentido, evento, idVehiculo )
	VALUES  ( hEventoId , NEW.tipo, idMovilHevento, NEW.timeStamp, NEW.velocidad, odometroValue, direccion, orientacion, descripcionEvento, NEW.idVehiculo  );
END;

Suggested fix:
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16777216
read_buffer_size=2093056
max_used_connections=5
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1039983 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x2b536440
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=0x2b680c08, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x817c123
0x825f7ea
0x80ebdc4
0x82a2838
0x82a6e06
0x829e65e
0x829e919
0x829eabe
0x82a1b65
0x82a3928
0x8117fdb
0x811b775
0x80ed948
0x82a2838
0x82a737f
0x829e715
0x829e919
0x829eb4e
0x82a1b65
0x82a3112
0x82acb46
0x81e9322
0x81ed11e
0x81989f2
0x819c6b1
0x819cce6
0x819ed0c
0xb7f8c34b
0xb7dcb65e
New value of fp=(nil) 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 0x9b37780 = INSERT INTO eventoFinal(tipo,nalarma,idVehiculo,x,y,fecha,hora,timeStamp,v                                                         elocidad,timeRestore,heading,odometro,odometroReal,gps,generico1,generico2,dig01,dig02,dig03,dig04,d                                                         ig05,dig06,output1,output2,output3,output4)VALUES(0,0,'0408',356147.06,6295700.70,'23-01-2007','10:4                                                         8:52',1169560132,0,1169560137,0,0.000000,0.000000,1,'   0.00','   0.00',0,0,0,0,0,0,0,0,0,0)
thd->thread_id=11443
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.
[23 Jan 2007 18:01] Miguel Solorzano
Thank you for the bug report. Could you please the complete test case
script with table create statement and data insert if needed. Thanks
in advance.
[23 Jan 2007 18:06] Pablo Calder?n
struct evetoFinalTable:

CREATE TABLE `eventoFinal` (
  `id` int(11) NOT NULL auto_increment,
  `tipo` int(11) unsigned default NULL,
  `nalarma` int(11) unsigned default NULL,
  `idVehiculo` varchar(15) default NULL,
  `idRuta` varchar(13) default NULL,
  `x` double default NULL,
  `y` double default NULL,
  `fecha` varchar(12) default NULL,
  `hora` varchar(12) default '00:00:00',
  `leido` tinyint(4) default '0',
  `timeStamp` int(15) default NULL,
  `velocidad` int(11) default NULL,
  `timeRestore` int(15) default NULL,
  `heading` int(11) default NULL,
  `odometro` float default '0',
  `odometroReal` double default NULL,
  `gps` tinyint(1) default NULL,
  `gpsFix` tinyint(1) default NULL,
  `datosCola` tinyint(1) default NULL,
  `generico1` varchar(20) default NULL,
  `generico2` varchar(20) default NULL,
  `generico3` varchar(20) default NULL,
  `generico4` varchar(20) default NULL,
  `generico5` varchar(20) default NULL,
  `generico6` varchar(20) default NULL,
  `dig01` tinyint(1) default NULL,
  `dig02` tinyint(1) default NULL,
  `dig03` tinyint(1) default NULL,
  `dig04` tinyint(1) default NULL,
  `dig05` tinyint(1) default NULL,
  `dig06` tinyint(1) default NULL,
  `output1` tinyint(4) default NULL,
  `output2` tinyint(4) default NULL,
  `output3` tinyint(4) default NULL,
  `output4` tinyint(4) default NULL,
  PRIMARY KEY  (`id`),
  KEY `fecha` (`fecha`),
  KEY `hora` (`hora`),
  KEY `x` (`x`),
  KEY `y` (`y`),
  KEY `key_time` (`timeStamp`),
  KEY `timeRestore` (`timeRestore`),
  KEY `nalarma` (`nalarma`),
  KEY `k1` (`idVehiculo`),
  KEY `k2` (`tipo`),
  KEY `odometroReal` (`odometroReal`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=225688 ;

struct H_Evento:

CREATE TABLE `H_Evento` (
  `idEvento` bigint(20) NOT NULL,
  `idEventoTipo` smallint(5) unsigned NOT NULL default '0',
  `idMovil` varchar(15) default NULL,
  `timeStamp` int(15) unsigned default NULL,
  `velocidad` smallint(5) unsigned default NULL,
  `odometro` double default NULL,
  `lugar` varchar(100) default NULL,
  `sentido` varchar(30) default NULL,
  `idVehiculo` varchar(15) default NULL,
  `evento` varchar(100) default NULL,
  PRIMARY KEY  (`idEvento`),
  KEY `key_movil` (`idMovil`),
  KEY `key_tiempo` (`timeStamp`),
  KEY `k1` (`idEventoTipo`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Note: trigger "getDirecccion" is a udf function
[24 Jan 2007 16:37] Miguel Solorzano
Thank you for the feedback. "Note: trigger "getDirecccion" is a udf function"
So could you please provide the udf function code. Thanks in advance.
[25 Jan 2007 15:06] Pablo Calder?n
I'd  provide the udf source, but it use an static linked library. Any way the alloc memory is done in the init function using init->ptr. I have red about the alloc memory; but I do not know if using funtions besides the main function can cause troubles. If you still need the source we will sent you the link to download  the mapserver library and the source.

Thank you:
[31 Jan 2007 21:27] Miguel Solorzano
Thank you for the feedback.
"If you still need the source we will sent you the link to
download  the mapserver library and the source."

Yes please provide it. Thanks in advance.
[2 Feb 2007 13:01] Pablo Calder?n
udf source

Attachment: udf_gis.cc (text/plain), 13.94 KiB.

[2 Feb 2007 13:02] Pablo Calder?n
thanks, uploaded udf source.
[9 May 2008 16:31] Miguel Solorzano
Thank you for the feedback. Do you have still the same behavior with latest
release?. I wasn't able to test your udf because I don't have #include <map.h>
in my environment Suse 10.3.
[9 Jun 2008 23:00] Bugs System
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".