cassandra.result 11 KB
Newer Older
1 2 3
drop table if exists t0, t1;
create table t1 (a int) engine=cassandra 
thrift_host='localhost' keyspace='foo' column_family='colfam';
Sergey Petrunya's avatar
Sergey Petrunya committed
4
ERROR 42000: Incorrect column name 'First column must be NOT NULL'
5 6
create table t1 (a int primary key, b int) engine=cassandra
thrift_host='localhost' keyspace='foo' column_family='colfam';
Sergey Petrunya's avatar
Sergey Petrunya committed
7
ERROR HY000: Unable to connect to foreign data source: Default TException. [Keyspace foo does not exist]
8 9 10 11 12 13 14 15
create table t1 (rowkey char(10) primary key, column1 char(10)) engine=cassandra 
thrift_host='127.0.0.2' keyspace='foo' column_family='colfam';
ERROR HY000: Unable to connect to foreign data source: connect() failed: Connection refused [1]
create table t1 (rowkey char(10) primary key, column1 char(10)) engine=cassandra 
thrift_host='localhost' keyspace='no_such_keyspace' column_family='colfam';
ERROR HY000: Unable to connect to foreign data source: Default TException. [Keyspace no_such_keyspace does not exist]
create table t1 (rowkey char(10) primary key, column1 char(10)) engine=cassandra 
thrift_host='localhost' keyspace='no_such_keyspace';
Sergey Petrunya's avatar
Sergey Petrunya committed
16
ERROR HY000: Unable to connect to foreign data source: keyspace and column_family table options must be specified
17
# Now, create a table for real and insert data
Sergey Petrunya's avatar
Sergey Petrunya committed
18
create table t1 (pk varchar(36) primary key, data1 varchar(60), data2 bigint) engine=cassandra 
19
thrift_host='localhost' keyspace='mariadbtest2' column_family='cf1';
20 21
# Just in case there were left-overs from previous:
delete from t1;
22
select * from t1;
Sergey Petrunya's avatar
Sergey Petrunya committed
23
pk	data1	data2
24 25
insert into t1 values ('rowkey10', 'data1-value', 123456);
insert into t1 values ('rowkey11', 'data1-value2', 34543);
26
insert into t1 values ('rowkey12', 'data1-value3', 454);
27
select * from t1;
Sergey Petrunya's avatar
Sergey Petrunya committed
28
pk	data1	data2
29 30 31 32
rowkey12	data1-value3	454
rowkey10	data1-value	123456
rowkey11	data1-value2	34543
explain
Sergey Petrunya's avatar
Sergey Petrunya committed
33
select * from t1 where pk='rowkey11';
34 35
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	const	PRIMARY	PRIMARY	38	const	1	
Sergey Petrunya's avatar
Sergey Petrunya committed
36 37
select * from t1 where pk='rowkey11';
pk	data1	data2
38
rowkey11	data1-value2	34543
Sergey Petrunya's avatar
Sergey Petrunya committed
39
delete from t1 where pk='rowkey11';
40
select * from t1;
Sergey Petrunya's avatar
Sergey Petrunya committed
41
pk	data1	data2
42 43 44
rowkey12	data1-value3	454
rowkey10	data1-value	123456
rowkey11	NULL	NULL
45 46
delete from t1;
select * from t1;
Sergey Petrunya's avatar
Sergey Petrunya committed
47
pk	data1	data2
48 49 50 51 52 53 54 55
#
# A query with filesort (check that table_flags() & HA_REC_NOT_IN_SEQ,
#  also check ::rnd_pos()
#
insert into t1 values ('rowkey10', 'data1-value', 123456);
insert into t1 values ('rowkey11', 'data1-value2', 34543);
insert into t1 values ('rowkey12', 'data1-value3', 454);
select * from t1 order by data2;
Sergey Petrunya's avatar
Sergey Petrunya committed
56
pk	data1	data2
57 58 59 60
rowkey12	data1-value3	454
rowkey11	data1-value2	34543
rowkey10	data1-value	123456
delete from t1;
61
drop table t1;
62 63 64 65 66 67 68 69
#
# MDEV-476: Cassandra: Server crashes in calculate_key_len on DELETE with ORDER BY
#
CREATE TABLE t1 (rowkey BIGINT PRIMARY KEY, a BIGINT) ENGINE=CASSANDRA
thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf2';
INSERT INTO t1 VALUES (1,1),(2,2);
DELETE FROM t1 ORDER BY a LIMIT 1;
DROP TABLE t1;
70 71 72 73 74 75 76 77 78 79 80 81
#
# Batched INSERT
#
show variables like 'cassandra_insert_batch_size';
Variable_name	Value
cassandra_insert_batch_size	100
show status like 'cassandra_row_insert%';
Variable_name	Value
Cassandra_row_inserts	8
Cassandra_row_insert_batches	7
CREATE TABLE t1 (rowkey BIGINT PRIMARY KEY, a BIGINT) ENGINE=CASSANDRA
thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf2';
82
delete from t1;
83 84 85 86 87 88 89 90 91 92 93 94 95
INSERT INTO t1 VALUES (1,1),(2,2);
DELETE FROM t1 ORDER BY a LIMIT 1;
DROP TABLE t1;
show status like 'cassandra_row_insert%';
Variable_name	Value
Cassandra_row_inserts	10
Cassandra_row_insert_batches	8
# FLUSH STATUS doesn't work for our variables, just like with InnoDB.
flush status;
show status like 'cassandra_row_insert%';
Variable_name	Value
Cassandra_row_inserts	10
Cassandra_row_insert_batches	8
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 147 148 149 150 151 152 153 154 155 156 157 158
#
# Batched Key Access
#
# Control variable (we are not yet able to make use of MRR's buffer)
show variables like 'cassandra_multi%';
Variable_name	Value
cassandra_multiget_batch_size	100
# MRR-related status variables:
show status like 'cassandra_multi%';
Variable_name	Value
Cassandra_multiget_reads	0
Cassandra_multiget_keys_scanned	0
Cassandra_multiget_rows_read	0
CREATE TABLE t1 (rowkey BIGINT PRIMARY KEY, a BIGINT) ENGINE=CASSANDRA
thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf2';
delete from t1;
INSERT INTO t1 VALUES (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
set @tmp_jcl=@@join_cache_level;
set join_cache_level=8;
explain select * from t1 A, t1 B where B.rowkey=A.a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	A	ALL	NULL	NULL	NULL	NULL	1000	Using where
1	SIMPLE	B	eq_ref	PRIMARY	PRIMARY	8	test.A.a	1	Using join buffer (flat, BKAH join); multiget_slice
select * from t1 A, t1 B where B.rowkey=A.a;
rowkey	a	rowkey	a
0	0	0	0
1	1	1	1
2	2	2	2
3	3	3	3
4	4	4	4
5	5	5	5
6	6	6	6
7	7	7	7
8	8	8	8
9	9	9	9
show status like 'cassandra_multi%';
Variable_name	Value
Cassandra_multiget_reads	1
Cassandra_multiget_keys_scanned	10
Cassandra_multiget_rows_read	10
insert into t1 values(1, 8);
insert into t1 values(3, 8);
insert into t1 values(5, 8);
insert into t1 values(7, 8);
select * from t1 A, t1 B where B.rowkey=A.a;
rowkey	a	rowkey	a
0	0	0	0
2	2	2	2
4	4	4	4
6	6	6	6
1	8	8	8
7	8	8	8
8	8	8	8
5	8	8	8
3	8	8	8
9	9	9	9
show status like 'cassandra_multi%';
Variable_name	Value
Cassandra_multiget_reads	2
Cassandra_multiget_keys_scanned	16
Cassandra_multiget_rows_read	16
delete from t1;
drop table t1;
159 160 161 162 163 164 165 166 167 168
#
# MDEV-480: TRUNCATE TABLE on a Cassandra table does not remove rows
#
CREATE TABLE t1 (rowkey BIGINT PRIMARY KEY, a BIGINT) ENGINE=CASSANDRA
thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf2';
INSERT INTO t1 VALUES (0,0),(1,1),(2,2);
truncate table t1;
select * from t1;
rowkey	a
drop table t1;
169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185
#
# MDEV-494, part #1: phantom row for big full-scan selects
#
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
CREATE TABLE t1 (rowkey BIGINT PRIMARY KEY, a BIGINT) ENGINE=CASSANDRA
thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf2';
insert into t1 select A.a + 10 * B.a + 100*C.a, 12345 from t0 A, t0 B, t0 C;
select count(*) from t1;
count(*)
1000
select count(*) from t1 where a=12345;
count(*)
1000
delete from t1;
drop table t1;
drop table t0;
Sergey Petrunya's avatar
Sergey Petrunya committed
186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201
# 32-bit INT type support
CREATE TABLE t1 (rowkey BIGINT PRIMARY KEY, intcol INT) ENGINE=CASSANDRA
thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf3';
insert into t1 values (10,10);
insert into t1 values (12,12);
delete from t1;
drop table t1;
# 
# Try accessing column family w/o explicitly defined columns
#
CREATE TABLE t1 (my_primary_key varchar(10) PRIMARY KEY) ENGINE=CASSANDRA
thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf10';
ERROR HY000: Internal error: 'target column family has no key_alias defined, PRIMARY KEY column must be named 'rowkey''
CREATE TABLE t1 (rowkey varchar(10) PRIMARY KEY) ENGINE=CASSANDRA
thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf10';
DROP TABLE t1;
202 203 204 205 206 207 208 209 210 211 212
# 
# Timestamp datatype support
#
CREATE TABLE t2 (rowkey bigint PRIMARY KEY, datecol timestamp) ENGINE=CASSANDRA
thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf4';
delete from t2;
insert into t2 values (1, '2012-08-29 01:23:45');
select * from t2;
rowkey	datecol
1	2012-08-29 01:23:45
delete from t2;
213 214 215 216 217 218
# MDEV-498: Cassandra: Inserting a timestamp does not work on a 32-bit system
INSERT INTO t2 VALUES (10,'2012-12-12 12:12:12');
SELECT * FROM t2;
rowkey	datecol
10	2012-12-12 12:12:12
delete from t2;
219 220 221 222 223 224 225 226 227 228 229 230 231 232
#
# (no MDEV#) Check that insert counters work correctly
#
create table t0 (a int);
insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
set cassandra_insert_batch_size=10;
insert into t2 select A.a+10*B.a, now() from t0 A, t0 B;
inserts	insert_batches
100	10
set cassandra_insert_batch_size=1;
insert into t2 select A.a+10*B.a+100, now() from t0 A, t0 B;
inserts	insert_batches
100	100
delete from t2;
233
drop table t2;
234
drop table t0;
Sergey Petrunya's avatar
Sergey Petrunya committed
235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268
# 
# UUID datatype support
#
CREATE TABLE t2 (rowkey bigint PRIMARY KEY, uuidcol char(36)) ENGINE=CASSANDRA
thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf5';
delete from t2;
insert into t2 values(1,'9b5658dc-f32f-11e1-94cd-f46d046e9f09');
insert into t2 values(2,'not-an-uuid');
ERROR 22003: Out of range value for column 'uuidcol' at row 1
insert into t2 values(3,'9b5658dc-f32f-11e1=94cd-f46d046e9f09');
ERROR 22003: Out of range value for column 'uuidcol' at row 1
insert into t2 values(4,'9b5658dc-fzzf-11e1-94cd-f46d046e9f09');
ERROR 22003: Out of range value for column 'uuidcol' at row 1
insert into t2 values
(5,'9b5658dc-f11f-11e1-94cd-f46d046e9f09'),
(6,'9b5658dc-f11f011e1-94cd-f46d046e9f09');
ERROR 22003: Out of range value for column 'uuidcol' at row 2
select * from t2;
rowkey	uuidcol
1	9b5658dc-f32f-11e1-94cd-f46d046e9f09
5	9b5658dc-f11f-11e1-94cd-f46d046e9f09
delete from t2;
drop table t2;
CREATE TABLE t2 (rowkey char(36) PRIMARY KEY, col1 int) ENGINE=CASSANDRA
thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf6';
delete from t2;
insert into t2 values('9b5658dc-f32f-11e1-94cd-f46d046e9f09', 1234);
insert into t2 values('not-an-uuid', 563);
ERROR 22003: Out of range value for column 'rowkey' at row 1
select * from t2;
rowkey	col1
9b5658dc-f32f-11e1-94cd-f46d046e9f09	1234
delete from t2;
drop table t2;
Sergey Petrunya's avatar
Sergey Petrunya committed
269 270 271
# 
# boolean datatype support
#
272
CREATE TABLE t2 (rowkey int PRIMARY KEY, boolcol bool) ENGINE=CASSANDRA
273 274 275 276 277 278 279 280 281
thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf7';
insert into t2 values (0, 0);
insert into t2 values (1, 1);
select * from t2;
rowkey	boolcol
0	0
1	1
delete from t2;
drop table t2;
Sergey Petrunya's avatar
Sergey Petrunya committed
282 283 284
# 
# Counter datatype support (read-only)
#
285 286 287 288 289 290 291
CREATE TABLE t2 (rowkey varchar(32) PRIMARY KEY, countercol bigint) ENGINE=CASSANDRA
thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf8';
select * from t2;
rowkey	countercol
cnt1	1
cnt2	100
drop table t2;
Sergey Petrunya's avatar
Sergey Petrunya committed
292 293 294 295 296 297
# 
# Check that @@cassandra_default_thrift_host works
#
show variables like 'cassandra_default_thrift_host';
Variable_name	Value
cassandra_default_thrift_host	
298
set @tmp=@@cassandra_default_thrift_host;
Sergey Petrunya's avatar
Sergey Petrunya committed
299 300 301 302 303 304 305 306 307 308 309
set cassandra_default_thrift_host='localhost';
ERROR HY000: Variable 'cassandra_default_thrift_host' is a GLOBAL variable and should be set with SET GLOBAL
set global cassandra_default_thrift_host='localhost';
# Try creating a table without specifying thrift_host:
CREATE TABLE t2 (rowkey varchar(32) PRIMARY KEY, countercol bigint) ENGINE=CASSANDRA
keyspace='mariadbtest2' column_family = 'cf8';
select * from t2;
rowkey	countercol
cnt1	1
cnt2	100
drop table t2;
310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328
set global cassandra_default_thrift_host=@tmp;
#
# Consistency settings
#
show variables like 'cassandra_%consistency';
Variable_name	Value
cassandra_read_consistency	ONE
cassandra_write_consistency	ONE
set @tmp=@@cassandra_write_consistency;
# Unfortunately, there is no easy way to check if setting have the effect..
set cassandra_write_consistency='ONE';
set cassandra_write_consistency='QUORUM';
set cassandra_write_consistency='LOCAL_QUORUM';
set cassandra_write_consistency='EACH_QUORUM';
set cassandra_write_consistency='ALL';
set cassandra_write_consistency='ANY';
set cassandra_write_consistency='TWO';
set cassandra_write_consistency='THREE';
set cassandra_write_consistency=@tmp;