~drizzle-trunk/drizzle/development

CREATE TABLE t1 ( `id` int(11) NOT NULL default '0', `id2` int(11) NOT NULL default '0', `id3` int(11) NOT NULL default '0', `dummy1` char(30) default NULL, PRIMARY KEY (`id`,`id2`), KEY `index_id3` (`id3`)) ENGINE=MyISAM;

insert into t1 (id,id2) values (1,1),(1,2),(1,3);

LOCK TABLE t1 WRITE;

select dummy1,count(distinct id) from t1 group by dummy1;

update t1 set id=-1 where id=1;

LOCK TABLE t1 READ;

--error 1099

update t1 set id=1 where id=1;

--error 1100

create table t2 SELECT * from t1;

create temporary table t2 SELECT * from t1;

drop table if exists t2;

unlock tables;

create table t2 SELECT * from t1;

LOCK TABLE t1 WRITE,t2 write;

insert into t2 SELECT * from t1;

update t1 set id=1 where id=-1;

drop table t1,t2;

# Check bug with INSERT ... SELECT with lock tables

CREATE TABLE t1 (

index1 smallint(6) default NULL,

nr smallint(6) default NULL,

KEY index1(index1)

) ENGINE=MyISAM;

CREATE TABLE t2 (

nr smallint(6) default NULL,

name varchar(20) default NULL

) ENGINE=MyISAM;

INSERT INTO t2 VALUES (1,'item1');

INSERT INTO t2 VALUES (2,'item2');

# problem begins here!

lock tables t1 write, t2 read;

insert into t1 select 1,nr from t2 where name='item1';

insert into t1 select 2,nr from t2 where name='item2';

unlock tables;

check table t1;

# Check error message

lock tables t1 write;

check table t2;

--error 1100

insert into t1 select index1,nr from t1;

unlock tables;

lock tables t1 write, t1 as t1_alias read;

insert into t1 select index1,nr from t1 as t1_alias;

--error ER_TABLE_NOT_LOCKED

drop table t1,t2;

unlock tables;

drop table t1,t2;

# BUG#5390 - problems with merge tables

# Supplement test for the after-fix optimization

# Check that a dropped table is correctly removed from a lock.

create table t1 (c1 int);

create table t2 (c1 int);

create table t3 (c1 int);

lock tables t1 write, t2 write, t3 write;

# This removes one table after the other from the lock.

drop table t2, t3, t1;

# Check that a lock merge works.

create table t1 (c1 int);

create table t2 (c1 int);

create table t3 (c1 int);

lock tables t1 write, t2 write, t3 write, t1 as t4 read;

alter table t2 add column c2 int;

drop table t1, t2, t3;

# Bug7241 - Invalid response when DELETE .. USING and LOCK TABLES used.

create table t1 ( a int(11) not null auto_increment, primary key(a));

create table t2 ( a int(11) not null auto_increment, primary key(a));

lock tables t1 write, t2 read;

delete from t1 using t1,t2 where t1.a=t2.a;

delete t1 from t1,t2 where t1.a=t2.a;

--error 1099

delete from t2 using t1,t2 where t1.a=t2.a;

--error 1099

delete t2 from t1,t2 where t1.a=t2.a;

--error ER_TABLE_NOT_LOCKED_FOR_WRITE

drop table t1,t2;

unlock tables;

drop table t2,t1;

100

101

--echo End of 4.1 tests.

102

103

104

105

# Bug#18884 "lock table + global read lock = crash"

106

# The bug is not repeatable, just add the test case.

107

108

--disable_warnings

109

drop table if exists t1;

110

--enable_warnings

111

create table t1 (a int);

112

lock table t1 write;

113

--error ER_LOCK_OR_ACTIVE_TRANSACTION

114

flush tables with read lock;

115

unlock tables;

116

drop table t1;

117

118

119

120

# Test LOCK TABLE on system tables. See bug#9953: CONVERT_TZ requires

121

# mysql.time_zone_name to be locked.

122

123

--disable_warnings

124

DROP TABLE IF EXISTS t1;

125

--enable_warnings

126

127

CREATE TABLE t1 (i INT);

128

129

LOCK TABLES mysql.time_zone READ, t1 READ;

130

UNLOCK TABLES;

131

132

LOCK TABLES mysql.time_zone READ, t1 WRITE;

133

UNLOCK TABLES;

134

135

LOCK TABLES mysql.time_zone READ;

136

UNLOCK TABLES;

137

138

LOCK TABLES mysql.time_zone WRITE;

139

UNLOCK TABLES;

140

141

# If at least one system table is locked for WRITE, then all other

142

# tables should be system tables locked also for WRITE.

143

LOCK TABLES mysql.time_zone READ, t1 READ;

144

145

--error ER_WRONG_LOCK_OF_SYSTEM_TABLE

146

LOCK TABLES mysql.time_zone WRITE, t1 READ;

147

148

--error ER_WRONG_LOCK_OF_SYSTEM_TABLE

149

LOCK TABLES mysql.time_zone WRITE, t1 WRITE;

150

151

DROP TABLE t1;

152

153

--echo

154

--echo Cleanup.

155

--echo

156

157

--echo End of 5.1 tests.

Older »