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:Microsoft Windows (Windows)
Assigned to: Tomash Brechko CPU Architecture:Any

[20 Feb 2006 18:16] Mike Gent
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
[21 Feb 2006 16:39] Valeriy Kravchuk
Thank you for a bug report. Verified just as described.
[7 Aug 2006 13:47] Tomash Brechko
Change title, as bug also affects stored functions.
[7 Aug 2006 16:48] Bugs System
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.
[23 Aug 2006 13:39] Bugs System
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.
[29 Aug 2006 10:33] Bugs System
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.
[29 Aug 2006 12:25] Tomash Brechko
Queued to 5.0-runtime.
[31 Aug 2006 10:23] Konstantin Osipov
Pushed into 5.0.25 and 5.1.12
[12 Sep 2006 1:26] Paul Dubois
Noted in 5.0.25, 5.1.12 changelogs.

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