| Bug #15742 | JOIN Views give strange errors on UPDATE | ||
|---|---|---|---|
| Submitted: | 14 Dec 2005 15:31 | Modified: | 14 Dec 2005 16:37 |
| Reporter: | Max Mether | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.15 | OS: | Any (All) |
| Assigned to: | CPU Architecture: | Any | |
[14 Dec 2005 15:43]
MySQL Verification Team
Max,
We were unable to repeat on Windows with 5.0.18.
C:\mysql5017>bin\mysql --defaults-file=c:\mysql5017\s5017.ini -uroot -p
--prompt="win5018>"
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 5.0.18-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
win5018>use test
Database changed
win5018>create table t1 (id int not null auto_increment primary key,
-> t2_id int);
Query OK, 0 rows affected (0.08 sec)
win5018>insert into t1 (t2_id) values
-> (1),
-> (2),
-> (2),
-> (1),
-> (4),
-> (2);
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates: 0 Warnings: 0
win5018>create table t2 (id int not null auto_increment primary key,
-> value char(3));
Query OK, 0 rows affected (0.08 sec)
win5018>insert into t2 (value) values
-> ('ZZZ'),
-> ('XXX'),
-> ('YYY'),
-> ('AAA'),
-> ('BBB'),
-> ('MMM');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
win5018>CREATE VIEW t_join AS select t1.id, t2.value from t1 join t2 on
-> t2_id=t2.id;
Query OK, 0 rows affected (0.08 sec)
win5018>update t_join SET Id = 8 WHERE ID = 1;
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
win5018>update t_join SET value = 'TTT' WHERE ID = 1;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
[14 Dec 2005 15:49]
Max Mether
Seems like this bug was fixed in 5.0.16, see the below link: http://bugs.mysql.com/bug.php?id=14027
[14 Dec 2005 16:37]
MySQL Verification Team
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.
If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information
about accessing the source trees is available at
http://www.mysql.com/doc/en/Installing_source_tree.html
Description: When trying to update a Join-View, strange error messages are given related to the columns used for the join How to repeat: mysql> create table t1 (id int not null auto_increment primary key, t2_id int); Query OK, 0 rows affected (0.07 sec) mysql> insert into t1 (t2_id) values -> (1), -> (2), -> (2), -> (1), -> (4), -> (2); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> create table t2 (id int not null auto_increment primary key, value char(3)); Query OK, 0 rows affected (0.09 sec) mysql> insert into t2 (value) values -> ('ZZZ'), -> ('XXX'), -> ('YYY'), -> ('AAA'), -> ('BBB'), -> ('MMM'); Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> CREATE VIEW t_join AS select t1.id, t2.value from t1 join t2 on t2_id=t2.id; Query OK, 0 rows affected (0.00 sec) Now we have the view. Let's try to update it: mysql> update t_join SET Id = 8 WHERE ID = 1; ERROR 1143 (42000): SELECT command denied to user 'root'@'localhost' for column 't2_id' in table 't1' mysql> update t_join SET value = 'TTT' WHERE ID = 1; ERROR 1143 (42000): SELECT command denied to user 'root'@'localhost' for column 't2_id' in table 't1' As we can see it gives bizarre errors. An INSERT (on only one of the tables) will work however: mysql> INSERT INTO t_join (value) values ('GGG'); Query OK, 1 row affected (0.00 sec)