MySQL Bugs: #12277: mysqld crashes when compiling any function

Bug #12277	mysqld crashes when compiling any function
Submitted:	29 Jul 2005 19:19	Modified:	3 Aug 2005 16:28
Reporter:	Ken Brown	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.10a	OS:	Windows (Windows 2003)
Assigned to:		CPU Architecture:	Any

Description:
Have a number of Functions and SPs all working in beta 9 but all fail to compile in beta 10 with the error

Instruction at 0x00580766 ref'd my memory at 0x20200020

ie
DROP FUNCTION IF EXISTS citygate.GetPK//
CREATE FUNCTION citygate.GetPK() 
  RETURNS int(11)
  LANGUAGE SQL
  NOT DETERMINISTIC
  SQL SECURITY DEFINER
  COMMENT 'Obtain next available primary key value'
BEGIN
  DECLARE PK, PK2 INT;
  SELECT MASTER_PK INTO PK FROM citygate.P_KEYS FOR UPDATE;
  SET PK2 = PK;
  SET PK2 = PK2 + 1;
  UPDATE citygate.P_KEYS SET MASTER_PK = PK2;
  RETURN PK;
END//

How to repeat:
Upgrade from beta 9 or install beta 10 clean
rollback to beta 9 removes the problem

Could you please provide a complete test case. I was unable for
to get the crash with your sample:

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.10a-beta-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter //
mysql> CREATE FUNCTION citygate.GetPK()
    ->   RETURNS int(11)
    ->   LANGUAGE SQL
    ->   NOT DETERMINISTIC
    ->   SQL SECURITY DEFINER
    ->   COMMENT 'Obtain next available primary key value'
    -> BEGIN
    ->   DECLARE PK, PK2 INT;
    ->   SELECT MASTER_PK INTO PK FROM citygate.P_KEYS FOR UPDATE;
    ->   SET PK2 = PK;
    ->   SET PK2 = PK2 + 1;
    ->   UPDATE citygate.P_KEYS SET MASTER_PK = PK2;
    ->   RETURN PK;
    -> END//
Query OK, 0 rows affected (0.08 sec)

mysql>

OK then - vanilla works but I suspect a lot of folks with large databases are going to want to apply upgrades

did you have a working 5.09 then upgrade then redo the function - thats what caused my fail

having loads of other problems as well (with a vanilla install and a clean db create
i.e.
mysql> delimiter //
mysql> DROP TRIGGER A_CUSTOMER_PROSPECT.TBI_A_CUSTOMER_PROSPECT//
ERROR 1360 (HY000): Trigger does not exist
mysql> CREATE TRIGGER TBI_A_CUSTOMER_PROSPECT
    -> BEFORE INSERT ON A_CUSTOMER_PROSPECT
    -> FOR EACH ROW
    -> BEGIN
    ->  DECLARE EXIT HANDLER FOR SQLEXCEPTION
    ->  BEGIN
    ->          CALL SECLOGACT(0,1010,'TBI_A_CUSTOMER_PROSPECT','INSERT','A_CUST
OMER_PROSPECT');
    ->  END;
    ->  SET NEW.REC_ABANDON_NAME = USER();
    ->  SET NEW.REC_ABANDON_STAMP = NOW();
    -> END;//
ERROR 1359 (HY000): Trigger already exists

This one really has be

Could you please write here a definition of table A_CUSTOMER_PROSPECT ?

As requested - alothough this works fine in 5.09 - and is the case for all tables not just this one

CREATE TABLE A_CUSTOMER_PROSPECT (
  PROSPECT_PK INTEGER UNSIGNED NOT NULL,
  PRODUCT_TITLE VARCHAR(255) NULL,
  PRODUCT_START DATETIME NULL,
  PRODUCT_END DATETIME NULL,
  PRODUCT_REVIEW DATETIME NULL,
  PRODUCT_REVIEW_FREQ VARCHAR(255) NULL,
  PROSPECT_VALUE FLOAT NULL,
  PROSPECT_FAIL_DATE DATETIME NOT NULL,
  PROSPECT_FAIL_OWNER VARCHAR(255) NOT NULL,
  PROSPECT_TO_DEL BOOL NOT NULL DEFAULT '0',
  REC_CREATE_NAME VARCHAR(255) NULL,
  REC_CREATE_STAMP DATETIME NULL,
  REC_ABANDON_NAME VARCHAR(255) NULL,
  REC_ABANDON_STAMP DATETIME NULL,
  PRIMARY KEY(PROSPECT_PK)
)
TYPE=InnoDB;

Hi!

I was unable to reproduce this bug under WinXP/SP2:

mysql> CREATE TRIGGER TBI_A_CUSTOMER_PROSPECT
    -> BEFORE INSERT ON A_CUSTOMER_PROSPECT
    -> FOR EACH ROW
    -> BEGIN
    -> DECLARE EXIT HANDLER FOR SQLEXCEPTION
    -> BEGIN
    -> CALL SECLOGACT(0,1010,'TBI_A_CUSTOMER_PROSPECT','INSERT','A_CUST OMER_PRO
SPECT');
    -> END;
    -> SET NEW.REC_ABANDON_NAME = USER();
    -> SET NEW.REC_ABANDON_STAMP = NOW();
    -> END;//
Query OK, 0 rows affected (0.02 sec)

Since I'm running Windows 2003 I don't really care if it works on XP
Don't know if it has anything to do with it but that table also has 2 triggers attached

CREATE TRIGGER TBI_A_CUSTOMER_PROSPECT
BEFORE INSERT ON A_CUSTOMER_PROSPECT
FOR EACH ROW
BEGIN
	SET NEW.REC_ABANDON_NAME = USER();
	SET NEW.REC_ABANDON_STAMP = NOW();
END;//
CREATE TRIGGER TBU_A_CUSTOMER_PROSPECT
BEFORE UPDATE ON A_CUSTOMER_PROSPECT
FOR EACH ROW
BEGIN
	SET NEW.PROSPECT_PK = OLD.PROSPECT_PK;
	SET NEW.PRODUCT_TITLE = OLD.PRODUCT_TITLE;
	SET NEW.PRODUCT_START = OLD.PRODUCT_START;
	SET NEW.PRODUCT_END = OLD.PRODUCT_END;
	SET NEW.PRODUCT_REVIEW = OLD.PRODUCT_REVIEW;
	SET NEW.PRODUCT_REVIEW_FREQ = OLD.PRODUCT_REVIEW_FREQ;
	SET NEW.PROSPECT_VALUE = OLD.PROSPECT_VALUE;
	SET NEW.REC_CREATE_NAME = OLD.REC_CREATE_NAME;
	SET NEW.REC_CREATE_STAMP = REC_CREATE_STAMP;
	SET NEW.REC_ABANDON_NAME = OLD.REC_ABANDON_NAME;
	SET NEW.REC_ABANDON_STAMP = OLD.REC_ABANDON_STAMP;
END;//

What I did notice was the trigger went in fine the first time but the second and subsequent i.e. drop if exist then create is where I have the probem

Sorry I was not able to reproduce the bug on Windows 2003 server. I tried to drop and create triggers several times.

So
You build a innodb database on 5.09 then added triggers and functions
Upgraded to 5.0.10
Dropped and re-added the trigger and had no problems?

How do you enable all the service debug logging so I can get a bug trace back to you for investigation?

build script

Attachment: citygate.sql (application/octet-stream, text), 70.64 KiB.

Try this then
create new schema citygate and load in attached ddl
then run in the trigger create - will be ok
try runing the trigger create again - i get the errors
ps can you mark the create script as private

Hi, Ken!

Please note the following item in the ChangeLog for version 5.0.10
(http://dev.mysql.com/doc/mysql/en/news-5-0-10.html):

<quote>
The namespace for triggers has changed. Previously, trigger names had to be unique per table. Now they must be unique within the schema (database). An implication of this change is that DROP TRIGGER syntax now uses a schema name instead of a table name (schema name is optional and, if omitted, the current schema will be used).

Note: When upgrading from a previous version of MySQL 5 to MySQL 5.0.10 or newer, you must drop all triggers before upgrading and re-create them after or DROP TRIGGER will not work after the upgrade. (Bug #5892)
</quote>

I think that at least part of you problems is explained by it.

I tested using the attached my.ini and script files. Created on 5.0.9
and upgrade to 5.0.10a and 5.0.11 (bk source server) on Windows
2003 Server and XP Pro.

yip works fine now thanks