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 19:16	Modified:	12 Sep 2006 3:26
Reporter:	Mike Gent
Status:	Closed
Category:	Server: Locking	Severity:	S1 (Critical)
Version:	5.0.18	OS:	Microsoft Windows (Windows)
Assigned to:	Bugs System	Target Version:

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.