Bug #22584 | last_insert_id not updated after inserting a record through a updatable view | ||
---|---|---|---|
Submitted: | 22 Sep 2006 9:12 | Modified: | 15 Nov 2006 18:42 |
Reporter: | Giuseppe Maxia | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 5.0.26-BK, 5.0.24 and 5.1.12 | OS: | Linux (Linux) |
Assigned to: | Tomash Brechko | CPU Architecture: | Any |
Tags: | auto_increment, LAST_INSERT_ID, Views |
[22 Sep 2006 9:12]
Giuseppe Maxia
[22 Sep 2006 10:17]
Valeriy Kravchuk
Thank you for a bug report. Verified just as described with 5.0.26-BK on Linux. May be also related to bug #21726.
[20 Oct 2006 14: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/14073 ChangeSet@1.2353, 2006-10-20 18:31:08+04:00, kroki@moonlight.intranet +3 -0 BUG#22584: last_insert_id not updated after inserting a record through a updatable view. When there's a VIEW on a base table that have AUTO_INCREMENT column, and this VIEW doesn't provide an access such column, after INSERT to such VIEW LAST_INSERT_ID() did not return the value just generated. This behaviour is intended and correct, because if the VIEW doesn't list some columns then these columns are effectively hidden from the user, and so any side effects of inserting default values to them. However, there was a bug that such statement inserting into a view would reset LAST_INSERT_ID() instead of leaving it unchanged. This patch restores the original value of LAST_INSERT_ID() instead of resetting it to zero.
[26 Oct 2006 12:13]
Konstantin Osipov
The patch was approved by email. Notice that the patch does not make the value of LAST_INSERT_ID() available: rather, it makes sure it's not modified by an insert through a view. The logic is that if you do not see a base table column in a view, you do not see any side effects caused by modification of that column through an insert into that view. This needs to be documented in the manual.
[27 Oct 2006 9:35]
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/14460 ChangeSet@1.2291, 2006-10-27 13:32:41+04:00, kroki@moonlight.intranet +3 -0 BUG#22584: last_insert_id not updated after inserting a record through a updatable view. When there's a VIEW on a base table that have AUTO_INCREMENT column, and this VIEW doesn't provide an access such column, after INSERT to such VIEW LAST_INSERT_ID() did not return the value just generated. This behaviour is intended and correct, because if the VIEW doesn't list some columns then these columns are effectively hidden from the user, and so any side effects of inserting default values to them. However, there was a bug that such statement inserting into a view would reset LAST_INSERT_ID() instead of leaving it unchanged. This patch restores the original value of LAST_INSERT_ID() instead of resetting it to zero.
[27 Oct 2006 12:15]
Tomash Brechko
Queued to 5.0-runtime and 5.1-runtime.
[2 Nov 2006 17:43]
Dmitry Lenev
Fix was pushed into 5.0.29 and 5.1.13
[15 Nov 2006 18:42]
Paul DuBois
Noted in 5.0.30 (not 5.0.29), 5.1.13 changelogs. If a table contains an AUTO_INCREMENT column, inserting into an insertable view on the table that does not include the AUTO_INCREMENT column should not change the value of LAST_INSERT_ID(), because the side effects of inserting default values into columns not part of the view should not be visible. MySQL was incorrectly setting LAST_INSERT_ID() to zero. Also pointed this out in the CREATE VIEW section.