1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
#
# BUG#57373: Multi update+InnoDB reports ER_KEY_NOT_FOUND if a
# table is updated twice
#
CREATE TABLE t1(
pk INT,
a INT,
PRIMARY KEY (pk)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (0,0);
UPDATE t1 AS A, t1 AS B SET A.pk = 1, B.a = 2;
# Should be (1,2)
SELECT * FROM t1;
pk a
1 2
DROP TABLE t1;
#
# BUG#11882110: UPDATE REPORTS ER_KEY_NOT_FOUND IF TABLE IS
# UPDATED TWICE
#
CREATE TABLE t1 (
col_int_key int,
pk int,
col_int int,
key(col_int_key),
primary key (pk)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1,2,3);
CREATE TABLE t2 (
col_int_key int,
pk_1 int,
pk_2 int,
col_int int,
key(col_int_key),
primary key (pk_1,pk_2)
) ENGINE=MyISAM;
INSERT INTO t2 VALUES (1,2,3,4);
UPDATE t1 AS A NATURAL JOIN t1 B SET A.pk=5,B.pk=7;
SELECT * FROM t1;
col_int_key pk col_int
1 7 3
UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_1=5,B.pk_1=7;
UPDATE t2 AS A NATURAL JOIN t2 B SET A.pk_2=10,B.pk_2=11;
SELECT * FROM t2;
col_int_key pk_1 pk_2 col_int
1 7 11 4
DROP TABLE t1,t2;
#
#Bug 11757486 - 49539: NON-DESCRIPTIVE ERR (ERROR 0 FROM STORAGE ENGINE)
# WITH MULTI-TABLE UPDATE
#
CREATE TABLE table_11757486 (field1 tinyint) ENGINE=MYISAM;
INSERT INTO table_11757486 VALUES (0),(0);
SET SESSION SQL_MODE=default;
UPDATE IGNORE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1;
Warnings:
Warning 1264 Out of range value for column 'field1' at row 1
Warning 1264 Out of range value for column 'field1' at row 2
UPDATE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1;
ERROR 22003: Out of range value for column 'field1' at row 1
SET SESSION SQL_MODE='';
UPDATE IGNORE (SELECT 128 as col1) x, table_11757486 SET field1=x.col1;
Warnings:
Warning 1264 Out of range value for column 'field1' at row 1
Warning 1264 Out of range value for column 'field1' at row 2
DROP TABLE table_11757486;
CREATE TABLE `t1` (
`a` int(11) NOT NULL auto_increment,
`b` int(11) default NULL,
PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
Warning 1681 Integer display width is deprecated and will be removed in a future release.
CREATE TABLE `t2` (
`a` int(11) NOT NULL auto_increment,
`b` int(11) default NULL,
PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
Warnings:
Warning 1681 Integer display width is deprecated and will be removed in a future release.
Warning 1681 Integer display width is deprecated and will be removed in a future release.
set @sav_binlog_format= @@session.binlog_format;
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
set @@session.binlog_format= mixed;
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
insert into t1 values (1,1),(2,2);
insert into t2 values (1,1),(4,4);
reset master;
UPDATE t2,t1 SET t2.a=t1.a+2;
ERROR 23000: Duplicate entry '3' for key 't2.PRIMARY'
select * from t2 /* must be (3,1), (4,4) */;
a b
3 1
4 4
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
binlog.000001 # Query # # BEGIN
binlog.000001 # Table_map # # table_id: # (test.t2)
binlog.000001 # Update_rows # # table_id: # flags: STMT_END_F
binlog.000001 # Query # # COMMIT
delete from t1;
delete from t2;
insert into t1 values (1,2),(3,4),(4,4);
insert into t2 values (1,2),(3,4),(4,4);
reset master;
UPDATE t2,t1 SET t2.a=t2.b where t2.a=t1.a;
ERROR 23000: Duplicate entry '4' for key 't2.PRIMARY'
include/show_binlog_events.inc
Log_name Pos Event_type Server_id End_log_pos Info
binlog.000001 # Query # # BEGIN
binlog.000001 # Table_map # # table_id: # (test.t2)
binlog.000001 # Update_rows # # table_id: # flags: STMT_END_F
binlog.000001 # Query # # COMMIT
drop table t1, t2;
set @@session.binlog_format= @sav_binlog_format;
Warnings:
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release.
# Bug#31640267: Assertion `trans_safe || updated_rows == 0 || thd->get_transaction()
CREATE TABLE t1 (
pk int NOT NULL,
col_int int DEFAULT NULL,
PRIMARY KEY (pk)
) ENGINE=MyISAM;
INSERT INTO t1 VALUES (2,9), (6,NULL), (0,7);
PREPARE s FROM
'UPDATE t1 AS a NATURAL JOIN t1 AS b SET a.col_int = 9, b.col_int = 9';
EXECUTE s;
SELECT ROW_COUNT();
ROW_COUNT()
1
EXECUTE s;
SELECT ROW_COUNT();
ROW_COUNT()
0
DEALLOCATE PREPARE s;
DROP TABLE t1;
|
