MySQL Bugs: #17591: Updatable view not possible with trigger or stored function

Bug #17591	Updatable view not possible with trigger or stored function
Submitted:	20 Feb 2006 18:16	Modified:	12 Sep 2006 1:26
Reporter:	Mike Gent	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S1 (Critical)
Version:	5.0.18	OS:	Windows (Windows)
Assigned to:	Tomash Brechko	CPU Architecture:	Any

Description:
I have 1 MyISAM table, 1 Archive table, 1 View pointing to the Archive table, and 1 Trigger for inserts from the MyISAM table to the View. If I manually insert a row into the View, that data can be seen in the Archive table correctly. If I insert the same data into the MyISAM table expecting the Trigger to automatically insert into the View and finally seen the Archive table, it does not work.

I need to perform this method of operations until Archive tables support partitions and indexing. The Trigger needs to always point to the same name (i.e. the View name), but the View will be altered daily to point to a new Archive table name.

There is no reason why the Trigger shouldn't act exactly as the manual insert into the View. There is a read lock on the Archive table, even though I don't want nor need one.

How to repeat:
-- Table, View, and Trigger Definition

CREATE TABLE Events (
	ID int(11) NOT NULL auto_increment,
	Timestamp int(11) NOT NULL default '0',
	Data varchar(255) NOT NULL default '',
	PRIMARY KEY (ID)
) ENGINE=MyISAM;

CREATE TABLE History_20060217 (
	ID int(11) NOT NULL default '0',
	Timestamp int(11) NOT NULL default '0',
	Data varchar(255) NOT NULL default ''
) ENGINE=ARCHIVE;

CREATE ALGORITHM = MERGE VIEW History
AS (SELECT * FROM History_20060217);

DELIMITER |
CREATE TRIGGER EventsToHistory AFTER INSERT ON Events
	FOR EACH ROW BEGIN
		INSERT INTO History VALUES (NEW.ID, NEW.Timestamp, NEW.Data);
	END
|
DELIMITER ;

-- WORKS

mysql> INSERT INTO History (ID, Timestamp, Data) VALUES (1, UNIX_TIMESTAMP(now()), 'A');
Query OK, 1 row affected (0.00 sec)

-- FAILS!!

mysql> INSERT INTO Events (Timestamp, Data) VALUES (UNIX_TIMESTAMP(now()), 'A');
ERROR 1099 (HY000): Table 'history_20060217' was locked with a READ lock and can't be updated

Thank you for a bug report. Verified just as described.

Change title, as bug also affects stored functions.

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/10126

ChangeSet@1.2235, 2006-08-07 20:47:52+04:00, kroki@moonlight.intranet +3 -0
  BUG#17591: Updatable view not possible with trigger or stored function
  
  When a view was used inside a trigger or a function, lock type for
  tables used in a view was always set to READ, even if we were trying
  to update the view.
  
  The solution is to set lock type properly.

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/10761

ChangeSet@1.2235, 2006-08-23 17:37:26+04:00, kroki@moonlight.intranet +3 -0
  BUG#17591: Updatable view not possible with trigger or stored function
  
  When a view was used inside a trigger or a function, lock type for
  tables used in a view was always set to READ, even if we were trying
  to update the view.
  
  The solution is to set lock type properly.

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/10994

ChangeSet@1.2235, 2006-08-29 14:32:59+04:00, kroki@moonlight.intranet +3 -0
  BUG#17591: Updatable view not possible with trigger or stored function
  
  When a view was used inside a trigger or a function, lock type for
  tables used in a view was always set to READ (thus making the view
  non-updatable), even if we were trying to update the view.
  
  The solution is to set lock type properly.

Queued to 5.0-runtime.

Pushed into 5.0.25 and 5.1.12

Noted in 5.0.25, 5.1.12 changelogs.

Views could not be updated within a stored function or trigger.