Bug #29769 mistaken warning rolling back UPDATE with JOIN and error in WHERE clause

Submitted: 12 Jul 2007 18:27 Modified: 31 Jul 2007 15:33
Reporter: Daniel Convissor Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.41-community/5.1 OS:Linux
Assigned to: Assigned Account CPU Architecture:Any

[12 Jul 2007 18:27] Daniel Convissor
Description:
MySQL mistakenly produces warning when rolling back transaction on an UPDATE query that has both a JOIN and an error in the WHERE clause.

How to repeat:
-- MySQL mistakenly produces warning when rolling
-- back transaction on an UPDATE query that has both
-- a JOIN and an error in the WHERE clause.
--
-- Warning:
-- Some non-transactional changed tables couldn't be rolled back

CREATE DATABASE db_a;
USE db_a;

CREATE TABLE t_a (
  c_1 int,
  c_2 int
) ENGINE=InnoDB;

CREATE TABLE t_b (
  c_1 int,
  c_2 int
) ENGINE=InnoDB;

INSERT INTO t_a VALUES (1, 1);
INSERT INTO t_b VALUES (1, 1);

-- GOOD
BEGIN;
UPDATE t_a SET c_2 = 2 WHERE c_3 = 1;
-- ERROR 1054 (42S22): Unknown column 'c_3' in 'where clause'
ROLLBACK;
-- Query OK, 0 rows affected (0.00 sec)

-- GOOD
BEGIN;
UPDATE t_a JOIN t_b USING (c_1) SET t_a.c_3 = 2;
-- ERROR 1054 (42S22): Unknown column 't_a.c_3' in 'field list'
ROLLBACK;
-- Query OK, 0 rows affected (0.00 sec)

-- BAD
BEGIN;
UPDATE t_a JOIN t_b USING (c_1) SET t_a.c_2 = 2 WHERE t_a.c_3 = 1;
-- ERROR 1054 (42S22): Unknown column 't_a.c_3' in 'where clause'
ROLLBACK;
-- Query OK, 0 rows affected, 1 warning (0.00 sec)

SHOW WARNINGS\G
--   Level: Warning
--    Code: 1196
-- Message: Some non-transactional changed tables couldn't be rolled back

SELECT VERSION();
-- Tested on:
-- 4.1.22-log
-- 5.0.41-community

DROP DATABASE db_a;
DROP DATABASE db_b;
[12 Jul 2007 18:51] MySQL Verification Team
Thank you for the bug report. Verified as described.
[16 Jul 2007 13:29] Heikki Tuuri
This is probably a MySQL bug, because InnoDB does not track non-transactional tables.
[16 Jul 2007 16:08] 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/30972

ChangeSet@1.2530, 2007-07-16 19:08:21+03:00, gkodinov@magare.gmz +3 -0
  Bug #29769: mistaken warning rolling back UPDATE with JOIN 
  and error in WHERE clause
  
  The trans_safe flag was initialized inconsistently with 
  transactional_tables flag. Thus caused 
  multi_update::~multi_update to erroneously set the flag
  that there are non-transactional table updated and as a 
  result the user is getting a wrong warning.
  Fixed by setting the trans_safe flag to the correct 
  value (off)
[31 Jul 2007 15:33] Georgi Kodinov
This bug is fixed by the fix for bug #27417.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.068 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.