diff options
Diffstat (limited to '')
83 files changed, 5653 insertions, 0 deletions
diff --git a/mysql-test/suite/plugins/disabled.def b/mysql-test/suite/plugins/disabled.def new file mode 100644 index 00000000..e69de29b --- /dev/null +++ b/mysql-test/suite/plugins/disabled.def diff --git a/mysql-test/suite/plugins/r/audit_null.result b/mysql-test/suite/plugins/r/audit_null.result new file mode 100644 index 00000000..ada85b66 --- /dev/null +++ b/mysql-test/suite/plugins/r/audit_null.result @@ -0,0 +1,115 @@ +set @old_global_general_log=@@global.general_log; +set global general_log=OFF; +create user testuser@localhost; +grant select on *.* to testuser@localhost; +install plugin audit_null soname 'adt_null'; +select 1; +1 +1 +select foobar; +ERROR 42S22: Unknown column 'foobar' in 'field list' +show status like 'audit_null%'; +Variable_name Value +Audit_null_called 9 +Audit_null_general_error 1 +Audit_null_general_log 3 +Audit_null_general_result 2 +create procedure au1(x char(16)) select concat("test1", x); +call au1("-12"); +concat("test1", x) +test1-12 +show status like 'audit_null%'; +Variable_name Value +Audit_null_called 22 +Audit_null_general_error 1 +Audit_null_general_log 7 +Audit_null_general_result 5 +create table t1 (a int); +insert t1 values (1), (2); +select * from t1; +a +1 +2 +rename table t1 to t2; +alter table t2 add column b int; +create definer=testuser@localhost view v1 as select t2.a+1, t2_copy.a+2 from t2, t2 as t2_copy; +select * from v1; +t2.a+1 t2_copy.a+2 +2 3 +3 3 +2 4 +3 4 +drop view v1; +create temporary table t2 (a date); +insert t2 values ('2020-10-09'); +select * from t2; +a +2020-10-09 +alter table t2 add column b int; +drop table t2; +explain select distinct * from t2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using temporary +select distinct * from t2; +a b +1 NULL +2 NULL +drop table t2; +uninstall plugin audit_null; +Warnings: +Warning 1620 Plugin is busy and will be uninstalled on shutdown +drop procedure au1; +drop user testuser@localhost; +set global general_log=@old_global_general_log; +root[root] @ localhost [] >> select 1 +root[root] @ localhost [] >> select foobar +root[root] @ localhost [] >> show status like 'audit_null%' +root[root] @ localhost [] >> create procedure au1(x char(16)) select concat("test1", x) +root[root] @ localhost [] mysql.proc : write +root[root] @ localhost [] >> call au1("-12") +root[root] @ localhost [] mysql.proc : read +root[root] @ localhost [] >> select concat("test1", NAME_CONST('x',_latin1'-12' COLLATE 'latin1_swedish_ci')) +root[root] @ localhost [] >> show status like 'audit_null%' +root[root] @ localhost [] >> create table t1 (a int) +root[root] @ localhost [] test.t1 : create +root[root] @ localhost [] >> insert t1 values (1), (2) +root[root] @ localhost [] test.t1 : write +root[root] @ localhost [] mysql.table_stats : read +root[root] @ localhost [] mysql.column_stats : read +root[root] @ localhost [] mysql.index_stats : read +root[root] @ localhost [] >> select * from t1 +root[root] @ localhost [] test.t1 : read +root[root] @ localhost [] >> rename table t1 to t2 +root[root] @ localhost [] test.t1 : rename to test.t2 +root[root] @ localhost [] mysql.table_stats : write +root[root] @ localhost [] mysql.column_stats : write +root[root] @ localhost [] mysql.index_stats : write +root[root] @ localhost [] >> alter table t2 add column b int +root[root] @ localhost [] test.t2 : alter +root[root] @ localhost [] test.t2 : read +root[root] @ localhost [] >> create definer=testuser@localhost view v1 as select t2.a+1, t2_copy.a+2 from t2, t2 as t2_copy +root[root] @ localhost [] test.t2 : read +root[root] @ localhost [] test.t2 : read +root[root] @ localhost [] >> select * from v1 +root[root] @ localhost [] test.t2 : read +root[root] @ localhost [] test.t2 : read +root[root] @ localhost [] mysql.table_stats : read +root[root] @ localhost [] mysql.column_stats : read +root[root] @ localhost [] mysql.index_stats : read +root[root] @ localhost [] >> drop view v1 +root[root] @ localhost [] >> create temporary table t2 (a date) +root[root] @ localhost [] >> insert t2 values ('2020-10-09') +root[root] @ localhost [] >> select * from t2 +root[root] @ localhost [] >> alter table t2 add column b int +root[root] @ localhost [] >> drop table t2 +root[root] @ localhost [] >> explain select distinct * from t2 +root[root] @ localhost [] test.t2 : read +root[root] @ localhost [] >> select distinct * from t2 +root[root] @ localhost [] test.t2 : read +root[root] @ localhost [] >> drop table t2 +root[root] @ localhost [] mysql.table_stats : write +root[root] @ localhost [] mysql.column_stats : write +root[root] @ localhost [] mysql.index_stats : write +root[root] @ localhost [] test.t2 : drop +root[root] @ localhost [] >> uninstall plugin audit_null +root[root] @ localhost [] mysql.plugin : write diff --git a/mysql-test/suite/plugins/r/audit_null_debug.result b/mysql-test/suite/plugins/r/audit_null_debug.result new file mode 100644 index 00000000..727bd67a --- /dev/null +++ b/mysql-test/suite/plugins/r/audit_null_debug.result @@ -0,0 +1,20 @@ +alter table mysql.plugin engine=myisam; +Warnings: +Warning 1478 Table storage engine 'MyISAM' does not support the create option 'TRANSACTIONAL=1' +set @old_dbug=@@debug_dbug; +call mtr.add_suppression("Index for table.*mysql.plugin.MYI"); +call mtr.add_suppression("Index for table 'plugin' is corrupt; try to repair it"); +SET debug_dbug='+d,myisam_pretend_crashed_table_on_usage'; +install plugin audit_null soname 'adt_null'; +ERROR HY000: Index for table './mysql/plugin.MYI' is corrupt; try to repair it +SET debug_dbug=@old_dbug; +install plugin audit_null soname 'adt_null'; +SET debug_dbug='+d,myisam_pretend_crashed_table_on_usage'; +uninstall plugin audit_null; +ERROR HY000: Index for table './mysql/plugin.MYI' is corrupt; try to repair it +SET debug_dbug=@old_dbug; +uninstall plugin audit_null; +uninstall plugin audit_null; +ERROR 42000: PLUGIN audit_null does not exist +delete from mysql.plugin where name='audit_null'; +alter table mysql.plugin engine=aria; diff --git a/mysql-test/suite/plugins/r/auth_ed25519.result b/mysql-test/suite/plugins/r/auth_ed25519.result new file mode 100644 index 00000000..6769d867 --- /dev/null +++ b/mysql-test/suite/plugins/r/auth_ed25519.result @@ -0,0 +1,77 @@ +create function ed25519_password returns string soname "auth_ed25519.so"; +select ed25519_password(); +ERROR HY000: Can't initialize function 'ed25519_password'; Wrong arguments to ed25519_password() +select ed25519_password(1); +ERROR HY000: Can't initialize function 'ed25519_password'; Wrong arguments to ed25519_password() +select ed25519_password("foo", "bar"); +ERROR HY000: Can't initialize function 'ed25519_password'; Wrong arguments to ed25519_password() +select ed25519_password("foo"); +ERROR HY000: Can't initialize function 'ed25519_password'; Authentication plugin ed25519 is not loaded +install soname 'auth_ed25519'; +select ed25519_password("foo"); +ed25519_password("foo") +vubFBzIrapbfHct1/J72dnUryz5VS7lA6XHH8sIx4TI +select ed25519_password("foobar"); +ed25519_password("foobar") +qv2mG6HWCuy32Slb5xhV4THStewNz2VINVPbgk+XAJ8 +select ed25519_password("foo bar"); +ed25519_password("foo bar") +Y5fV74JAVRMOK2cdnUsYS+WW9sXaaL/o+6WGKOgqnzc +select ed25519_password(NULL); +ed25519_password(NULL) +NULL +select * from information_schema.plugins where plugin_name='ed25519'; +PLUGIN_NAME ed25519 +PLUGIN_VERSION 1.1 +PLUGIN_STATUS ACTIVE +PLUGIN_TYPE AUTHENTICATION +PLUGIN_TYPE_VERSION 2.2 +PLUGIN_LIBRARY auth_ed25519.so +PLUGIN_LIBRARY_VERSION 1.14 +PLUGIN_AUTHOR Sergei Golubchik +PLUGIN_DESCRIPTION Elliptic curve ED25519 based authentication +PLUGIN_LICENSE GPL +LOAD_OPTION ON +PLUGIN_MATURITY Stable +PLUGIN_AUTH_VERSION 1.1 +create user test1@localhost identified via ed25519 using 'ZIgUREUg5PVgQ6LskhXmO+eZLS0nC8be6HPjYWR4YJY'; +show grants for test1@localhost; +Grants for test1@localhost +GRANT USAGE ON *.* TO `test1`@`localhost` IDENTIFIED VIA ed25519 USING 'ZIgUREUg5PVgQ6LskhXmO+eZLS0nC8be6HPjYWR4YJY' +drop user test1@localhost; +create user test1@localhost identified via ed25519 using 'foo'; +ERROR HY000: Password hash should be 43 characters long +create user test1@localhost identified via ed25519 using '>>>1234567890123456789012345678901234567890'; +ERROR HY000: Password hash should be base64 encoded +create user test1@localhost identified via ed25519 using password('foo'); +show grants for test1@localhost; +Grants for test1@localhost +GRANT USAGE ON *.* TO `test1`@`localhost` IDENTIFIED VIA ed25519 USING 'vubFBzIrapbfHct1/J72dnUryz5VS7lA6XHH8sIx4TI' +select ed25519_password('foo'); +ed25519_password('foo') +vubFBzIrapbfHct1/J72dnUryz5VS7lA6XHH8sIx4TI +set password for test1@localhost = password('bar'); +show create user test1@localhost; +CREATE USER for test1@localhost +CREATE USER `test1`@`localhost` IDENTIFIED VIA ed25519 USING 'pfzkeWMzkTefY1oshXS+/kATeN51M+4jxi3/cbyTd10' +select ed25519_password('bar'); +ed25519_password('bar') +pfzkeWMzkTefY1oshXS+/kATeN51M+4jxi3/cbyTd10 +set password for test1@localhost = 'ZIgUREUg5PVgQ6LskhXmO+eZLS0nC8be6HPjYWR4YJY'; +show create user test1@localhost; +CREATE USER for test1@localhost +CREATE USER `test1`@`localhost` IDENTIFIED VIA ed25519 USING 'ZIgUREUg5PVgQ6LskhXmO+eZLS0nC8be6HPjYWR4YJY' +connect(localhost,test1,public,test,PORT,SOCKET); +connect con1, localhost, test1, public; +ERROR 28000: Access denied for user 'test1'@'localhost' (using password: YES) +connect con1, localhost, test1, secret; +select current_user(); +current_user() +test1@localhost +disconnect con1; +connection default; +drop user test1@localhost; +uninstall plugin ed25519; +select ed25519_password("foo"); +ERROR HY000: Can't initialize function 'ed25519_password'; Authentication plugin ed25519 is not loaded +drop function ed25519_password; diff --git a/mysql-test/suite/plugins/r/auth_v0100.result b/mysql-test/suite/plugins/r/auth_v0100.result new file mode 100644 index 00000000..da3cce74 --- /dev/null +++ b/mysql-test/suite/plugins/r/auth_v0100.result @@ -0,0 +1,22 @@ +install soname 'auth_0x0100'; +select plugin_name, plugin_type_version from information_schema.plugins where +plugin_type='authentication' and plugin_library is not null order by plugin_name; +plugin_name plugin_type_version +auth_0x0100 1.0 +create user tt identified via auth_0x0100; +create user zzzzzzzzzzzzzzzz; +grant select on test.* to zzzzzzzzzzzzzzzz; +connect(localhost,tt,,test,MASTER_MYPORT,MASTER_MYSOCK); +connect c0,localhost,tt; +ERROR 28000: Access denied for user 'tt'@'localhost' (using password: YES) +grant proxy on zzzzzzzzzzzzzzzz to tt; +connect c1,localhost,tt; +connection c1; +select user(), current_user(), @@external_user; +user() tt@localhost +current_user() zzzzzzzzzzzzzzzz@% +@@external_user oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo. +connection default; +drop user tt; +drop user zzzzzzzzzzzzzzzz; +uninstall plugin auth_0x0100; diff --git a/mysql-test/suite/plugins/r/binlog-simple_plugin_check.result b/mysql-test/suite/plugins/r/binlog-simple_plugin_check.result new file mode 100644 index 00000000..6c960ee3 --- /dev/null +++ b/mysql-test/suite/plugins/r/binlog-simple_plugin_check.result @@ -0,0 +1,19 @@ +INSTALL SONAME "simple_password_check"; +SELECT PLUGIN_NAME FROM INFORMATION_SCHEMA.PLUGINS +WHERE PLUGIN_NAME='simple_password_check'; +PLUGIN_NAME +simple_password_check +# +# MDEV-14031 Password policy causes replication failure +# +CREATE USER user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85'; +CREATE USER user2@localhost IDENTIFIED BY 'bsg9#d.cem#!85'; +ERROR HY000: Your password does not satisfy the current policy requirements +DROP USER user1@localhost; +include/show_binlog_events.inc +Log_name Pos Event_type Server_id End_log_pos Info +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; CREATE USER user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85' +master-bin.000001 # Gtid # # GTID #-#-# +master-bin.000001 # Query # # use `test`; DROP USER user1@localhost +UNINSTALL PLUGIN simple_password_check; diff --git a/mysql-test/suite/plugins/r/cassandra.result b/mysql-test/suite/plugins/r/cassandra.result new file mode 100644 index 00000000..67ae2cab --- /dev/null +++ b/mysql-test/suite/plugins/r/cassandra.result @@ -0,0 +1,636 @@ +drop table if exists t0, t1; +# +# Check variables and status counters +# +show status like 'cassandra%'; +Variable_name Value +Cassandra_row_inserts 0 +Cassandra_row_insert_batches 0 +Cassandra_multiget_keys_scanned 0 +Cassandra_multiget_reads 0 +Cassandra_multiget_rows_read 0 +Cassandra_network_exceptions 0 +Cassandra_timeout_exceptions 0 +Cassandra_unavailable_exceptions 0 +show variables like 'cassandra%'; +Variable_name Value +cassandra_default_thrift_host +cassandra_failure_retries 3 +cassandra_insert_batch_size 100 +cassandra_multiget_batch_size 100 +cassandra_read_consistency ONE +cassandra_rnd_batch_size 10000 +cassandra_write_consistency ONE +# +# Test various errors on table creation. +# +create table t1 (a int) engine=cassandra +thrift_host='localhost' keyspace='foo' column_family='colfam'; +ERROR 42000: This table type requires a primary key +create table t1 (a int primary key, b int) engine=cassandra +thrift_host='localhost' keyspace='foo' column_family='colfam'; +ERROR HY000: Unable to connect to foreign data source: Default TException. [Keyspace foo does not exist] +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'; +ERROR HY000: Unable to connect to foreign data source: keyspace and column_family table options must be specified +# Now, create a table for real and insert data +create table t1 (pk varchar(36) primary key, data1 varchar(60), data2 bigint) engine=cassandra +thrift_host='localhost' keyspace='mariadbtest2' column_family='cf1'; +# Just in case there were left-overs from previous: +delete from t1; +select * from t1; +pk data1 data2 +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; +pk data1 data2 +rowkey12 data1-value3 454 +rowkey10 data1-value 123456 +rowkey11 data1-value2 34543 +explain +select * from t1 where pk='rowkey11'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 38 const 1 +select * from t1 where pk='rowkey11'; +pk data1 data2 +rowkey11 data1-value2 34543 +delete from t1 where pk='rowkey11'; +select * from t1; +pk data1 data2 +rowkey12 data1-value3 454 +rowkey10 data1-value 123456 +delete from t1; +select * from t1; +pk data1 data2 +# +# 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; +pk data1 data2 +rowkey12 data1-value3 454 +rowkey11 data1-value2 34543 +rowkey10 data1-value 123456 +delete from t1; +drop table t1; +# +# 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; +# +# 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'; +delete from t1; +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 +# +# 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_keys_scanned 0 +Cassandra_multiget_reads 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_keys_scanned 10 +Cassandra_multiget_reads 1 +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_keys_scanned 16 +Cassandra_multiget_reads 2 +Cassandra_multiget_rows_read 16 +delete from t1; +drop table t1; +# +# 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; +# +# 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; +# 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; +# +# 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; +# 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; +# +# (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; +drop table t2; +drop table t0; +# +# 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; +# +# boolean datatype support +# +CREATE TABLE t2 (rowkey int PRIMARY KEY, boolcol bool) ENGINE=CASSANDRA +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; +# +# Counter datatype support (read-only) +# +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; +# +# Check that @@cassandra_default_thrift_host works +# +show variables like 'cassandra_default_thrift_host'; +Variable_name Value +cassandra_default_thrift_host +set @tmp=@@cassandra_default_thrift_host; +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; +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; +# +# varint datatype support +# +CREATE TABLE t2 (rowkey varchar(32) PRIMARY KEY, varint_col varbinary(32)) ENGINE=CASSANDRA +thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf9'; +select rowkey, hex(varint_col) from t2; +rowkey hex(varint_col) +val-01 01 +val-0x123456 123456 +val-0x12345678 12345678 +drop table t2; +# now, let's check what happens when MariaDB's column is not wide enough: +CREATE TABLE t2 (rowkey varchar(32) PRIMARY KEY, varint_col varbinary(2)) ENGINE=CASSANDRA +thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf9'; +select rowkey, hex(varint_col) from t2; +ERROR HY000: Internal error: Unable to convert value for field `varint_col` from Cassandra's data format. Source data is 4 bytes, 0x12345678 +drop table t2; +# +# Decimal datatype support +# +CREATE TABLE t2 (rowkey varchar(32) PRIMARY KEY, decimal_col varbinary(32)) ENGINE=CASSANDRA +thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf11'; +select rowkey, hex(decimal_col) from t2; +rowkey hex(decimal_col) +val_1.5 000000010F +val_0.5 0000000105 +val_1234 0000000004D2 +drop table t2; +# +# Mapping TIMESTAMP -> int64 +# +set @save_tz= @@time_zone; +set time_zone='UTC'; +CREATE TABLE t2 (rowkey bigint PRIMARY KEY, datecol timestamp) ENGINE=CASSANDRA +thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf4'; +insert into t2 values (1, '2012-08-29 01:23:45'); +INSERT INTO t2 VALUES (10,'2012-08-29 01:23:46'); +drop table t2; +CREATE TABLE t2 (rowkey bigint PRIMARY KEY, datecol bigint) ENGINE=CASSANDRA +thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf4'; +select * from t2; +rowkey datecol +1 1346203425000 +10 1346203426000 +delete from t2; +drop table t2; +set time_zone=@save_tz; +# +# Check whether changing parameters with ALTER TABLE works. +# +CREATE TABLE t2 (rowkey varchar(32) PRIMARY KEY, decimal_col varbinary(32)) ENGINE=CASSANDRA +thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf11'; +drop table t2; +CREATE TABLE t2 (rowkey varchar(32) PRIMARY KEY, decimal_col varbinary(32)) ENGINE=CASSANDRA +thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf11'; +alter table t2 column_family='cf12'; +Writes made during ALTER TABLE +0 +drop table t2; +# +# UPDATE command support +# +create table t1 (pk varchar(36) primary key, data1 varchar(60), data2 bigint) engine=cassandra +thrift_host='localhost' keyspace='mariadbtest2' column_family='cf1'; +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; +pk data1 data2 +rowkey12 data1-value3 454 +rowkey10 data1-value 123456 +rowkey11 data1-value2 34543 +update t1 set data1='updated-1' where pk='rowkey11'; +select * from t1; +pk data1 data2 +rowkey12 data1-value3 454 +rowkey10 data1-value 123456 +rowkey11 updated-1 34543 +update t1 set pk='new-rowkey12' where pk='rowkey12'; +select * from t1; +pk data1 data2 +rowkey10 data1-value 123456 +new-rowkey12 data1-value3 454 +rowkey11 updated-1 34543 +delete from t1; +drop table t1; +# +# Dynamic columns support +# +CREATE TABLE t2 (rowkey bigint PRIMARY KEY, uuidcol blob DYNAMIC_COLUMN_STORAGE=1) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf5'; +drop table t2; +#error: dynamic column is not a blob +CREATE TABLE t2 (rowkey bigint PRIMARY KEY, uuidcol char(36) DYNAMIC_COLUMN_STORAGE=1) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf5'; +ERROR 42000: Incorrect column specifier for column 'uuidcol' +#error: double dynamic column +CREATE TABLE t2 (rowkey bigint PRIMARY KEY, uuidcol blob DYNAMIC_COLUMN_STORAGE=1, textcol blob DYNAMIC_COLUMN_STORAGE=1) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf5'; +ERROR 42000: Incorrect column specifier for column 'textcol' +# +# Dynamic column read +# +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,'9b5658dc-f32f-11e1-94cd-f46d046e9f0a'); +drop table t2; +CREATE TABLE t2 (rowkey bigint PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf5'; +select rowkey, column_list(dyn), column_get(dyn, 'uuidcol' as char) from t2; +rowkey column_list(dyn) column_get(dyn, 'uuidcol' as char) +1 `uuidcol` 9b5658dc-f32f-11e1-94cd-f46d046e9f09 +2 `uuidcol` 9b5658dc-f32f-11e1-94cd-f46d046e9f0a +drop table t2; +CREATE TABLE t2 (rowkey bigint PRIMARY KEY, uuidcol char(36)) ENGINE=CASSANDRA +thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf5'; +delete from t2; +drop table t2; +# +# Dynamic column insert +# +CREATE TABLE t2 (rowkey bigint PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf5'; +insert into t2 values (1, column_create("dyn1", 1, "dyn2", "two")); +select rowkey, column_json(dyn) from t2; +rowkey column_json(dyn) +1 {"dyn1":"1","dyn2":"two"} +delete from t2; +drop table t2; +# bigint +CREATE TABLE t1 (rowkey bigint PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf2'; +insert into t1 values (1, column_create("dyn1", 1, "dyn2", "two", 'a', 254324)); +insert into t1 values (2, column_create("dyn1", 1, "dyn2", "two", 'a', 2543)); +select rowkey, column_json(dyn) from t1; +rowkey column_json(dyn) +1 {"a":254324,"dyn1":"1","dyn2":"two"} +2 {"a":2543,"dyn1":"1","dyn2":"two"} +delete from t1; +drop table t1; +# int +CREATE TABLE t1 (rowkey bigint PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf3'; +insert into t1 values (1, column_create("dyn1", 1, "dyn2", "two", 'intcol', 254324)); +insert into t1 values (2, column_create("dyn1", 1, "dyn2", "two", 'intcol', 2543)); +select rowkey, column_json(dyn) from t1; +rowkey column_json(dyn) +1 {"dyn1":"1","dyn2":"two","intcol":254324} +2 {"dyn1":"1","dyn2":"two","intcol":2543} +delete from t1; +drop table t1; +# timestamp +CREATE TABLE t1 (rowkey bigint PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf4'; +insert into t1 values (1, column_create("dyn1", 1, "dyn2", "two", 'datecol', 254324)); +insert into t1 values (2, column_create("dyn1", 1, "dyn2", "two", 'datecol', 2543)); +select rowkey, column_json(dyn) from t1; +rowkey column_json(dyn) +1 {"dyn1":"1","dyn2":"two","datecol":254324} +2 {"dyn1":"1","dyn2":"two","datecol":2543} +delete from t1; +drop table t1; +# boolean +CREATE TABLE t1 (rowkey int PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf7'; +insert into t1 values (1, column_create("dyn1", 1, "dyn2", "two", 'boolcol', 254324)); +insert into t1 values (2, column_create("dyn1", 1, "dyn2", "two", 'boolcol', 0)); +select rowkey, column_json(dyn) from t1; +rowkey column_json(dyn) +1 {"dyn1":"1","dyn2":"two","boolcol":1} +2 {"dyn1":"1","dyn2":"two","boolcol":0} +select rowkey, column_json(dyn) from t1; +rowkey column_json(dyn) +1 {"dyn1":"1","dyn2":"two","boolcol":1} +2 {"dyn1":"1","dyn2":"two","boolcol":0} +update t1 set dyn=column_add(dyn, "dyn2", null, "dyn3", "3"); +select rowkey, column_json(dyn) from t1; +rowkey column_json(dyn) +1 {"dyn1":"1","dyn3":"3","boolcol":1} +2 {"dyn1":"1","dyn3":"3","boolcol":0} +update t1 set dyn=column_add(dyn, "dyn1", null) where rowkey= 1; +select rowkey, column_json(dyn) from t1; +rowkey column_json(dyn) +1 {"dyn3":"3","boolcol":1} +2 {"dyn1":"1","dyn3":"3","boolcol":0} +update t1 set dyn=column_add(dyn, "dyn3", null, "a", "ddd"); +select rowkey, column_json(dyn) from t1; +rowkey column_json(dyn) +1 {"a":"ddd","boolcol":1} +2 {"a":"ddd","dyn1":"1","boolcol":0} +update t1 set dyn=column_add(dyn, "12345678901234", "ddd"); +select rowkey, column_json(dyn) from t1; +rowkey column_json(dyn) +1 {"a":"ddd","boolcol":1,"12345678901234":"ddd"} +2 {"a":"ddd","dyn1":"1","boolcol":0,"12345678901234":"ddd"} +update t1 set dyn=column_add(dyn, "12345678901234", null); +select rowkey, column_json(dyn) from t1; +rowkey column_json(dyn) +1 {"a":"ddd","boolcol":1} +2 {"a":"ddd","dyn1":"1","boolcol":0} +update t1 set dyn=column_add(dyn, 'boolcol', null) where rowkey= 2; +select rowkey, column_json(dyn) from t1; +rowkey column_json(dyn) +1 {"a":"ddd","boolcol":1} +2 {"a":"ddd","dyn1":"1"} +update t1 set rowkey= 3, dyn=column_add(dyn, "dyn1", null, 'boolcol', 0) where rowkey= 2; +select rowkey, column_json(dyn) from t1; +rowkey column_json(dyn) +1 {"a":"ddd","boolcol":1} +3 {"a":"ddd","boolcol":0} +delete from t1; +drop table t1; +CREATE TABLE t1 (rowkey varchar(10) PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cfd1'; +select * from t1; +ERROR HY000: Internal error: Unable to convert value for field `dyn` from Cassandra's data format. Name length exceed limit of 16383: 'very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_v +drop table t1; +CREATE TABLE t1 (rowkey int PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) +ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cfd2'; +DELETE FROM t1; +insert into t1 values (1, column_create("dyn", 1)); +select rowkey, column_list(dyn) from t1; +rowkey column_list(dyn) +1 `dyn` +delete from t1; +DROP TABLE t1; +CREATE TABLE t1 (rowkey int PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) +ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cfd2'; +insert into t1 values (1,'9b5658dc-f32f-11e1-94cd-f46d046e9f0a'); +ERROR HY000: Encountered illegal format of dynamic column string +delete from t1; +DROP TABLE t1; +# +# MDEV-565: Server crashes in ha_cassandra::write_row on +# inserting NULL into a dynamic column +# +CREATE TABLE t1 (rowkey int PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) +ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cfd2'; +insert into t1 values (1, NULL); +delete from t1; +DROP TABLE t1; +# +# strange side effect of Cassandra - remiving all columns of primary +# key removes all row. +# +CREATE TABLE t1 (rowkey int PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) +ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cfd2'; +INSERT INTO t1 VALUES(2,column_create("ab","ab")); +select rowkey, column_json(dyn) from t1; +rowkey column_json(dyn) +2 {"ab":"ab"} +UPDATE t1 set dyn=NULL; +select rowkey, column_json(dyn) from t1; +rowkey column_json(dyn) +INSERT INTO t1 VALUES(2,column_create("ab","ab")); +select rowkey, column_json(dyn) from t1; +rowkey column_json(dyn) +2 {"ab":"ab"} +UPDATE t1 set dyn=""; +select rowkey, column_json(dyn) from t1; +rowkey column_json(dyn) +delete from t1; +DROP TABLE t1; +# +# MDEV-4005 #Server crashes on creating a Cassandra table +# with a mix of static and dynamic columns +# +DROP TABLE IF EXISTS t1, t2; +CREATE TABLE t1 ( +pk int primary key, +col_int int, +dyncol blob DYNAMIC_COLUMN_STORAGE=yes +) ENGINE=cassandra keyspace='bug' thrift_host = '127.0.0.1' column_family='cf1'; +drop table t1; +# +# MDEV-4000: Mapping between Cassandra blob (BytesType) and MySQL BLOB does not work +# +create table t1 (rowkey int primary key, b blob ) ENGINE=CASSANDRA thrift_host = '127.0.0.1' `keyspace`='mariadbtest2' `column_family`='cf13'; +insert into t1 values (1, 'fooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo-bar'); +insert into t1 values (2, 'qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq-baz'); +select * from t1; +rowkey b +1 fooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo-bar +2 qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq-baz +drop table t1; +# +# MDEV-4001: Cassandra: server crashes in ha_cassandra::end_bulk_insert on INSERT .. SELECT with a non-existing column +# +create table t1 (rowkey int primary key, a int) ENGINE=cassandra thrift_host='127.0.0.1' keyspace='mariadbtest2' column_family='cf14'; +insert into t1 (a) select b from t1; +ERROR 42S22: Unknown column 'b' in 'field list' +drop table t1; diff --git a/mysql-test/suite/plugins/r/cassandra_qcache.result b/mysql-test/suite/plugins/r/cassandra_qcache.result new file mode 100644 index 00000000..32e9db8b --- /dev/null +++ b/mysql-test/suite/plugins/r/cassandra_qcache.result @@ -0,0 +1,16 @@ +drop table if exists t1, t2; +create table t1 (rowkey int primary key, a int) engine=cassandra +thrift_host='localhost' keyspace='mariadbtest2' column_family='cf1'; +create table t2 like t1; +set global query_cache_size=1024*1024; +select * from t1; +rowkey a +insert into t2 values (1,1); +select * from t1; +rowkey a +1 1 +select sql_no_cache * from t1; +rowkey a +1 1 +drop table t1,t2; +set global QUERY_CACHE_SIZE=0; diff --git a/mysql-test/suite/plugins/r/cassandra_reinstall.result b/mysql-test/suite/plugins/r/cassandra_reinstall.result new file mode 100644 index 00000000..f4874894 --- /dev/null +++ b/mysql-test/suite/plugins/r/cassandra_reinstall.result @@ -0,0 +1,14 @@ +install soname 'ha_cassandra'; +select plugin_name,plugin_status,plugin_library from information_schema.plugins where plugin_name = 'cassandra'; +plugin_name plugin_status plugin_library +CASSANDRA ACTIVE ha_cassandra.so +uninstall plugin cassandra; +select plugin_name,plugin_status,plugin_library from information_schema.plugins where plugin_name = 'cassandra'; +plugin_name plugin_status plugin_library +install soname 'ha_cassandra'; +select plugin_name,plugin_status,plugin_library from information_schema.plugins where plugin_name = 'cassandra'; +plugin_name plugin_status plugin_library +CASSANDRA ACTIVE ha_cassandra.so +uninstall plugin cassandra; +select plugin_name,plugin_status,plugin_library from information_schema.plugins where plugin_name = 'cassandra'; +plugin_name plugin_status plugin_library diff --git a/mysql-test/suite/plugins/r/cracklib_password_check.result b/mysql-test/suite/plugins/r/cracklib_password_check.result new file mode 100644 index 00000000..1194e6ee --- /dev/null +++ b/mysql-test/suite/plugins/r/cracklib_password_check.result @@ -0,0 +1,50 @@ +install soname "cracklib_password_check"; +select * from information_schema.plugins where plugin_name='cracklib_password_check'; +PLUGIN_NAME cracklib_password_check +PLUGIN_VERSION 1.0 +PLUGIN_STATUS ACTIVE +PLUGIN_TYPE PASSWORD VALIDATION +PLUGIN_TYPE_VERSION 1.0 +PLUGIN_LIBRARY cracklib_password_check.so +PLUGIN_LIBRARY_VERSION 1.14 +PLUGIN_AUTHOR Sergei Golubchik +PLUGIN_DESCRIPTION Password validation via CrackLib +PLUGIN_LICENSE GPL +LOAD_OPTION ON +PLUGIN_MATURITY Stable +PLUGIN_AUTH_VERSION 1.0 +grant select on *.* to foocar identified by 'foocar'; +ERROR HY000: Your password does not satisfy the current policy requirements +show warnings; +Level Code Message +Warning 1819 cracklib: it is based on your username +Error 1819 Your password does not satisfy the current policy requirements +grant select on *.* to foocar identified by 'racoof'; +ERROR HY000: Your password does not satisfy the current policy requirements +show warnings; +Level Code Message +Warning 1819 cracklib: it is based on your username +Error 1819 Your password does not satisfy the current policy requirements +grant select on *.* to foo@barbar identified by 'barbar'; +ERROR HY000: Your password does not satisfy the current policy requirements +show warnings; +Level Code Message +Warning 1819 cracklib: it does not contain enough DIFFERENT characters +Error 1819 Your password does not satisfy the current policy requirements +grant select on *.* to foobar identified by 'qwerty'; +ERROR HY000: Your password does not satisfy the current policy requirements +show warnings; +Level Code Message +Warning 1819 cracklib: it is based on a dictionary word +Error 1819 Your password does not satisfy the current policy requirements +grant select on *.* to foobar identified by 'q$%^&*rty'; +drop user foobar; +# +# MDEV-9851: CREATE USER w/o IDENTIFIED BY clause causes crash +# when using cracklib plugin +# +create user 'newuser'@'localhost'; +ERROR HY000: Your password does not satisfy the current policy requirements +uninstall plugin cracklib_password_check; +create user foo1 identified by 'pwd'; +drop user foo1; diff --git a/mysql-test/suite/plugins/r/dialog.result b/mysql-test/suite/plugins/r/dialog.result new file mode 100644 index 00000000..0fcbb41e --- /dev/null +++ b/mysql-test/suite/plugins/r/dialog.result @@ -0,0 +1,26 @@ +install plugin three_attempts soname 'dialog_examples.so'; +create user test_dialog identified via three_attempts using 'SECRET'; +# +# -pSECRET is picked up, no questions asked. +# +select user(), current_user(); +user() current_user() +test_dialog@localhost test_dialog@% +# +# without -p. up to three questions are asked on the stdin. +# athentication is successful, the correct pasword is on the third line +# +Password, please: *** +Password, please: **** +Password, please: ****** +select user(), current_user(); +user() current_user() +test_dialog@localhost test_dialog@% +# +# athentication is unsuccessful, first three lines are all wrong +# +Password, please: *** +Password, please: **** +Password, please: ***** +drop user test_dialog; +uninstall plugin three_attempts; diff --git a/mysql-test/suite/plugins/r/false_dupes-6543.result b/mysql-test/suite/plugins/r/false_dupes-6543.result new file mode 100644 index 00000000..22accaaa --- /dev/null +++ b/mysql-test/suite/plugins/r/false_dupes-6543.result @@ -0,0 +1,5 @@ +install soname 'ha_federated'; +install soname 'ha_federated'; +install soname 'ha_federatedx'; +ERROR HY000: Function 'FEDERATED' already exists +uninstall soname 'ha_federated'; diff --git a/mysql-test/suite/plugins/r/feedback_plugin_install.result b/mysql-test/suite/plugins/r/feedback_plugin_install.result new file mode 100644 index 00000000..c7f7a5c7 --- /dev/null +++ b/mysql-test/suite/plugins/r/feedback_plugin_install.result @@ -0,0 +1,16 @@ +install plugin feedback soname 'feedback.so'; +select plugin_status from information_schema.plugins where plugin_name='feedback'; +plugin_status +ACTIVE +select * from information_schema.feedback where variable_name like 'feed%' + and variable_name not like '%_uid' + and variable_name not like '%debug%'; +VARIABLE_NAME VARIABLE_VALUE +FEEDBACK used 2 +FEEDBACK version 1.1 +FEEDBACK_HTTP_PROXY +FEEDBACK_SEND_RETRY_WAIT 60 +FEEDBACK_SEND_TIMEOUT 60 +FEEDBACK_URL http://mariadb.org/feedback_plugin/post +FEEDBACK_USER_INFO mysql-test +uninstall plugin feedback; diff --git a/mysql-test/suite/plugins/r/feedback_plugin_load.result b/mysql-test/suite/plugins/r/feedback_plugin_load.result new file mode 100644 index 00000000..d76625bd --- /dev/null +++ b/mysql-test/suite/plugins/r/feedback_plugin_load.result @@ -0,0 +1,26 @@ +select plugin_status from information_schema.plugins where plugin_name='feedback'; +plugin_status +ACTIVE +SELECT variable_value INTO @feedback_used FROM information_schema.feedback where variable_name = 'FEEDBACK used'; +SELECT variable_value = @feedback_used + 1 as 'MUST BE 1' FROM information_schema.feedback where variable_name = 'FEEDBACK used'; +MUST BE 1 +1 +select * from information_schema.feedback where variable_name like 'feed%' + and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used' + and variable_name not like '%debug%'; +VARIABLE_NAME VARIABLE_VALUE +FEEDBACK version 1.1 +FEEDBACK_HTTP_PROXY +FEEDBACK_SEND_RETRY_WAIT 60 +FEEDBACK_SEND_TIMEOUT 60 +FEEDBACK_URL http://mariadb.org/feedback_plugin/post +FEEDBACK_USER_INFO mysql-test +SELECT VARIABLE_VALUE>0, VARIABLE_NAME FROM INFORMATION_SCHEMA.FEEDBACK +WHERE VARIABLE_NAME LIKE 'Collation used %' +ORDER BY VARIABLE_NAME; +VARIABLE_VALUE>0 VARIABLE_NAME +1 Collation used binary +1 Collation used latin1_swedish_ci +1 Collation used utf8mb4_bin +1 Collation used utf8_bin +1 Collation used utf8_general_ci diff --git a/mysql-test/suite/plugins/r/feedback_plugin_send.result b/mysql-test/suite/plugins/r/feedback_plugin_send.result new file mode 100644 index 00000000..5a48c703 --- /dev/null +++ b/mysql-test/suite/plugins/r/feedback_plugin_send.result @@ -0,0 +1,30 @@ +select plugin_status from information_schema.plugins where plugin_name='feedback'; +plugin_status +ACTIVE +SELECT variable_value INTO @feedback_used FROM information_schema.feedback where variable_name = 'FEEDBACK used'; +SELECT variable_value = @feedback_used + 1 as 'MUST BE 1' FROM information_schema.feedback where variable_name = 'FEEDBACK used'; +MUST BE 1 +1 +select * from information_schema.feedback where variable_name like 'feed%' + and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used' + and variable_name not like '%debug%'; +VARIABLE_NAME VARIABLE_VALUE +FEEDBACK version 1.1 +FEEDBACK_HTTP_PROXY +FEEDBACK_SEND_RETRY_WAIT 60 +FEEDBACK_SEND_TIMEOUT 60 +FEEDBACK_URL http://mariadb.org/feedback_plugin/post +FEEDBACK_USER_INFO mysql-test +SELECT VARIABLE_VALUE>0, VARIABLE_NAME FROM INFORMATION_SCHEMA.FEEDBACK +WHERE VARIABLE_NAME LIKE 'Collation used %' +ORDER BY VARIABLE_NAME; +VARIABLE_VALUE>0 VARIABLE_NAME +1 Collation used binary +1 Collation used latin1_swedish_ci +1 Collation used utf8mb4_bin +1 Collation used utf8_bin +1 Collation used utf8_general_ci +set global sql_mode=ONLY_FULL_GROUP_BY; +# restart +6: feedback plugin: report to 'http://mariadb.org/feedback_plugin/post' was sent +6: feedback plugin: server replied 'ok' diff --git a/mysql-test/suite/plugins/r/fulltext_plugin.result b/mysql-test/suite/plugins/r/fulltext_plugin.result new file mode 100644 index 00000000..2c104c98 --- /dev/null +++ b/mysql-test/suite/plugins/r/fulltext_plugin.result @@ -0,0 +1,7 @@ +INSTALL PLUGIN simple_parser SONAME 'mypluglib.so'; +CREATE TABLE t1(a TEXT, b TEXT, FULLTEXT(a) WITH PARSER simple_parser); +ALTER TABLE t1 ADD FULLTEXT(b) WITH PARSER simple_parser; +DROP TABLE t1; +UNINSTALL PLUGIN simple_parser; +show status like 'a%status'; +Variable_name Value diff --git a/mysql-test/suite/plugins/r/locales.result b/mysql-test/suite/plugins/r/locales.result new file mode 100644 index 00000000..106bf229 --- /dev/null +++ b/mysql-test/suite/plugins/r/locales.result @@ -0,0 +1,252 @@ +select * from information_schema.locales; +ID NAME DESCRIPTION MAX_MONTH_NAME_LENGTH MAX_DAY_NAME_LENGTH DECIMAL_POINT THOUSAND_SEP ERROR_MESSAGE_LANGUAGE +0 en_US English - United States 9 9 . , english +1 en_GB English - United Kingdom 9 9 . , english +2 ja_JP Japanese - Japan 3 3 . , japanese +3 sv_SE Swedish - Sweden 9 7 , swedish +4 de_DE German - Germany 9 10 , . german +5 fr_FR French - France 9 8 , french +6 ar_AE Arabic - United Arab Emirates 6 8 . , english +7 ar_BH Arabic - Bahrain 6 8 . , english +8 ar_JO Arabic - Jordan 12 8 . , english +9 ar_SA Arabic - Saudi Arabia 12 8 . english +10 ar_SY Arabic - Syria 12 8 . , english +11 be_BY Belarusian - Belarus 10 10 , . english +12 bg_BG Bulgarian - Bulgaria 9 10 , english +13 ca_ES Catalan - Catalan 8 9 , english +14 cs_CZ Czech - Czech Republic 8 7 , czech +15 da_DK Danish - Denmark 9 7 , . danish +16 de_AT German - Austria 9 10 , german +17 es_ES Spanish - Spain 10 9 , spanish +18 et_EE Estonian - Estonia 9 9 , estonian +19 eu_ES Basque - Basque 9 10 , english +20 fi_FI Finnish - Finland 9 11 , english +21 fo_FO Faroese - Faroe Islands 9 12 , . english +22 gl_ES Galician - Galician 8 8 , english +23 gu_IN Gujarati - India 10 8 . , english +24 he_IL Hebrew - Israel 7 5 . , english +25 hi_IN Hindi - India 7 9 . , hindi +26 hr_HR Croatian - Croatia 8 11 , english +27 hu_HU Hungarian - Hungary 10 9 , . hungarian +28 id_ID Indonesian - Indonesia 9 6 , . english +29 is_IS Icelandic - Iceland 9 12 , . english +30 it_CH Italian - Switzerland 9 9 , ' italian +31 ko_KR Korean - Korea 3 3 . , korean +32 lt_LT Lithuanian - Lithuania 9 14 , . english +33 lv_LV Latvian - Latvia 10 11 , english +34 mk_MK Macedonian - FYROM 9 10 , english +35 mn_MN Mongolia - Mongolian 18 6 , . english +36 ms_MY Malay - Malaysia 9 6 . , english +37 nb_NO Norwegian(Bokml) - Norway 9 7 , . norwegian +38 nl_NL Dutch - The Netherlands 9 9 , dutch +39 pl_PL Polish - Poland 11 12 , polish +40 pt_BR Portuguese - Brazil 9 7 , portuguese +41 pt_PT Portuguese - Portugal 9 7 , portuguese +42 ro_RO Romanian - Romania 10 8 , . romanian +43 ru_RU Russian - Russia 8 11 , russian +44 ru_UA Russian - Ukraine 8 11 , . russian +45 sk_SK Slovak - Slovakia 9 8 , slovak +46 sl_SI Slovenian - Slovenia 9 10 , english +47 sq_AL Albanian - Albania 7 10 , . english +48 sr_RS Serbian - Serbia 9 10 . serbian +49 ta_IN Tamil - India 10 8 . , english +50 te_IN Telugu - India 10 9 . , english +51 th_TH Thai - Thailand 10 8 . , english +52 tr_TR Turkish - Turkey 7 9 , . english +53 uk_UA Ukrainian - Ukraine 8 9 , . ukrainian +54 ur_PK Urdu - Pakistan 6 6 . , english +55 vi_VN Vietnamese - Vietnam 16 11 , . english +56 zh_CN Chinese - Peoples Republic of China 3 3 . , english +57 zh_TW Chinese - Taiwan 3 2 . , english +58 ar_DZ Arabic - Algeria 6 8 . , english +59 ar_EG Arabic - Egypt 6 8 . , english +60 ar_IN Arabic - Iran 6 8 . , english +61 ar_IQ Arabic - Iraq 6 8 . , english +62 ar_KW Arabic - Kuwait 6 8 . , english +63 ar_LB Arabic - Lebanon 12 8 . , english +64 ar_LY Arabic - Libya 6 8 . , english +65 ar_MA Arabic - Morocco 6 8 . , english +66 ar_OM Arabic - Oman 6 8 . , english +67 ar_QA Arabic - Qatar 6 8 . , english +68 ar_SD Arabic - Sudan 6 8 . , english +69 ar_TN Arabic - Tunisia 6 8 . , english +70 ar_YE Arabic - Yemen 6 8 . , english +71 de_BE German - Belgium 9 10 , . german +72 de_CH German - Switzerland 9 10 . ' german +73 de_LU German - Luxembourg 9 10 , . german +74 en_AU English - Australia 9 9 . , english +75 en_CA English - Canada 9 9 . , english +76 en_IN English - India 9 9 . , english +77 en_NZ English - New Zealand 9 9 . , english +78 en_PH English - Philippines 9 9 . , english +79 en_ZA English - South Africa 9 9 . , english +80 en_ZW English - Zimbabwe 9 9 . , english +81 es_AR Spanish - Argentina 10 9 , . spanish +82 es_BO Spanish - Bolivia 10 9 , spanish +83 es_CL Spanish - Chile 10 9 , spanish +84 es_CO Spanish - Columbia 10 9 , spanish +85 es_CR Spanish - Costa Rica 10 9 . spanish +86 es_DO Spanish - Dominican Republic 10 9 . spanish +87 es_EC Spanish - Ecuador 10 9 , spanish +88 es_GT Spanish - Guatemala 10 9 . spanish +89 es_HN Spanish - Honduras 10 9 . spanish +90 es_MX Spanish - Mexico 10 9 . spanish +91 es_NI Spanish - Nicaragua 10 9 . spanish +92 es_PA Spanish - Panama 10 9 . spanish +93 es_PE Spanish - Peru 10 9 . spanish +94 es_PR Spanish - Puerto Rico 10 9 . spanish +95 es_PY Spanish - Paraguay 10 9 , spanish +96 es_SV Spanish - El Salvador 10 9 . spanish +97 es_US Spanish - United States 10 9 . , spanish +98 es_UY Spanish - Uruguay 10 9 , spanish +99 es_VE Spanish - Venezuela 10 9 , spanish +100 fr_BE French - Belgium 9 8 , . french +101 fr_CA French - Canada 9 8 , french +102 fr_CH French - Switzerland 9 8 , french +103 fr_LU French - Luxembourg 9 8 , french +104 it_IT Italian - Italy 9 9 , italian +105 nl_BE Dutch - Belgium 9 9 , . dutch +106 no_NO Norwegian - Norway 9 7 , . norwegian +107 sv_FI Swedish - Finland 9 7 , swedish +108 zh_HK Chinese - Hong Kong SAR 3 3 . , english +109 el_GR Greek - Greece 11 9 , . greek +110 rm_CH Romansh - Switzerland 9 9 , ' english +show locales; +Id Name Description Error_Message_Language +0 en_US English - United States english +1 en_GB English - United Kingdom english +2 ja_JP Japanese - Japan japanese +3 sv_SE Swedish - Sweden swedish +4 de_DE German - Germany german +5 fr_FR French - France french +6 ar_AE Arabic - United Arab Emirates english +7 ar_BH Arabic - Bahrain english +8 ar_JO Arabic - Jordan english +9 ar_SA Arabic - Saudi Arabia english +10 ar_SY Arabic - Syria english +11 be_BY Belarusian - Belarus english +12 bg_BG Bulgarian - Bulgaria english +13 ca_ES Catalan - Catalan english +14 cs_CZ Czech - Czech Republic czech +15 da_DK Danish - Denmark danish +16 de_AT German - Austria german +17 es_ES Spanish - Spain spanish +18 et_EE Estonian - Estonia estonian +19 eu_ES Basque - Basque english +20 fi_FI Finnish - Finland english +21 fo_FO Faroese - Faroe Islands english +22 gl_ES Galician - Galician english +23 gu_IN Gujarati - India english +24 he_IL Hebrew - Israel english +25 hi_IN Hindi - India hindi +26 hr_HR Croatian - Croatia english +27 hu_HU Hungarian - Hungary hungarian +28 id_ID Indonesian - Indonesia english +29 is_IS Icelandic - Iceland english +30 it_CH Italian - Switzerland italian +31 ko_KR Korean - Korea korean +32 lt_LT Lithuanian - Lithuania english +33 lv_LV Latvian - Latvia english +34 mk_MK Macedonian - FYROM english +35 mn_MN Mongolia - Mongolian english +36 ms_MY Malay - Malaysia english +37 nb_NO Norwegian(Bokml) - Norway norwegian +38 nl_NL Dutch - The Netherlands dutch +39 pl_PL Polish - Poland polish +40 pt_BR Portuguese - Brazil portuguese +41 pt_PT Portuguese - Portugal portuguese +42 ro_RO Romanian - Romania romanian +43 ru_RU Russian - Russia russian +44 ru_UA Russian - Ukraine russian +45 sk_SK Slovak - Slovakia slovak +46 sl_SI Slovenian - Slovenia english +47 sq_AL Albanian - Albania english +48 sr_RS Serbian - Serbia serbian +49 ta_IN Tamil - India english +50 te_IN Telugu - India english +51 th_TH Thai - Thailand english +52 tr_TR Turkish - Turkey english +53 uk_UA Ukrainian - Ukraine ukrainian +54 ur_PK Urdu - Pakistan english +55 vi_VN Vietnamese - Vietnam english +56 zh_CN Chinese - Peoples Republic of China english +57 zh_TW Chinese - Taiwan english +58 ar_DZ Arabic - Algeria english +59 ar_EG Arabic - Egypt english +60 ar_IN Arabic - Iran english +61 ar_IQ Arabic - Iraq english +62 ar_KW Arabic - Kuwait english +63 ar_LB Arabic - Lebanon english +64 ar_LY Arabic - Libya english +65 ar_MA Arabic - Morocco english +66 ar_OM Arabic - Oman english +67 ar_QA Arabic - Qatar english +68 ar_SD Arabic - Sudan english +69 ar_TN Arabic - Tunisia english +70 ar_YE Arabic - Yemen english +71 de_BE German - Belgium german +72 de_CH German - Switzerland german +73 de_LU German - Luxembourg german +74 en_AU English - Australia english +75 en_CA English - Canada english +76 en_IN English - India english +77 en_NZ English - New Zealand english +78 en_PH English - Philippines english +79 en_ZA English - South Africa english +80 en_ZW English - Zimbabwe english +81 es_AR Spanish - Argentina spanish +82 es_BO Spanish - Bolivia spanish +83 es_CL Spanish - Chile spanish +84 es_CO Spanish - Columbia spanish +85 es_CR Spanish - Costa Rica spanish +86 es_DO Spanish - Dominican Republic spanish +87 es_EC Spanish - Ecuador spanish +88 es_GT Spanish - Guatemala spanish +89 es_HN Spanish - Honduras spanish +90 es_MX Spanish - Mexico spanish +91 es_NI Spanish - Nicaragua spanish +92 es_PA Spanish - Panama spanish +93 es_PE Spanish - Peru spanish +94 es_PR Spanish - Puerto Rico spanish +95 es_PY Spanish - Paraguay spanish +96 es_SV Spanish - El Salvador spanish +97 es_US Spanish - United States spanish +98 es_UY Spanish - Uruguay spanish +99 es_VE Spanish - Venezuela spanish +100 fr_BE French - Belgium french +101 fr_CA French - Canada french +102 fr_CH French - Switzerland french +103 fr_LU French - Luxembourg french +104 it_IT Italian - Italy italian +105 nl_BE Dutch - Belgium dutch +106 no_NO Norwegian - Norway norwegian +107 sv_FI Swedish - Finland swedish +108 zh_HK Chinese - Hong Kong SAR english +109 el_GR Greek - Greece greek +110 rm_CH Romansh - Switzerland english +show locales like '%spanish%'; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'like '%spanish%'' at line 1 +show locales where description like '%spanish%'; +Id Name Description Error_Message_Language +17 es_ES Spanish - Spain spanish +81 es_AR Spanish - Argentina spanish +82 es_BO Spanish - Bolivia spanish +83 es_CL Spanish - Chile spanish +84 es_CO Spanish - Columbia spanish +85 es_CR Spanish - Costa Rica spanish +86 es_DO Spanish - Dominican Republic spanish +87 es_EC Spanish - Ecuador spanish +88 es_GT Spanish - Guatemala spanish +89 es_HN Spanish - Honduras spanish +90 es_MX Spanish - Mexico spanish +91 es_NI Spanish - Nicaragua spanish +92 es_PA Spanish - Panama spanish +93 es_PE Spanish - Peru spanish +94 es_PR Spanish - Puerto Rico spanish +95 es_PY Spanish - Paraguay spanish +96 es_SV Spanish - El Salvador spanish +97 es_US Spanish - United States spanish +98 es_UY Spanish - Uruguay spanish +99 es_VE Spanish - Venezuela spanish +flush locales; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'locales' at line 1 diff --git a/mysql-test/suite/plugins/r/max_password_errors_auth_named_pipe.result b/mysql-test/suite/plugins/r/max_password_errors_auth_named_pipe.result new file mode 100644 index 00000000..82d464e3 --- /dev/null +++ b/mysql-test/suite/plugins/r/max_password_errors_auth_named_pipe.result @@ -0,0 +1,12 @@ +set @old_max_password_errors=@@max_password_errors; +create user nosuchuser identified with 'named_pipe'; +set global max_password_errors=1; +connect(localhost,nosuchuser,,test,MASTER_PORT,MASTER_SOCKET); +connect pipe_con,localhost,nosuchuser,,,,,PIPE; +ERROR 28000: Access denied for user 'nosuchuser'@'localhost' +connect(localhost,nosuchuser,,test,MASTER_PORT,MASTER_SOCKET); +connect pipe_con,localhost,nosuchuser,,,,,PIPE; +ERROR 28000: Access denied for user 'nosuchuser'@'localhost' +DROP USER nosuchuser; +FLUSH PRIVILEGES; +set global max_password_errors=@old_max_password_errors; diff --git a/mysql-test/suite/plugins/r/max_password_errors_auth_socket.result b/mysql-test/suite/plugins/r/max_password_errors_auth_socket.result new file mode 100644 index 00000000..eb7cb641 --- /dev/null +++ b/mysql-test/suite/plugins/r/max_password_errors_auth_socket.result @@ -0,0 +1,12 @@ +set @old_max_password_errors=@@max_password_errors; +create user nosuchuser identified with 'unix_socket'; +set global max_password_errors=1; +connect(localhost,nosuchuser,,test,MASTER_PORT,MASTER_SOCKET); +connect pipe_con,localhost,nosuchuser; +ERROR 28000: Access denied for user 'nosuchuser'@'localhost' +connect(localhost,nosuchuser,,test,MASTER_PORT,MASTER_SOCKET); +connect pipe_con,localhost,nosuchuser; +ERROR 28000: Access denied for user 'nosuchuser'@'localhost' +DROP USER nosuchuser; +FLUSH PRIVILEGES; +set global max_password_errors=@old_max_password_errors; diff --git a/mysql-test/suite/plugins/r/multiauth,aix.rdiff b/mysql-test/suite/plugins/r/multiauth,aix.rdiff new file mode 100644 index 00000000..0a2570cb --- /dev/null +++ b/mysql-test/suite/plugins/r/multiauth,aix.rdiff @@ -0,0 +1,14 @@ +diff --git a/mysql-test/suite/plugins/r/multiauth.result b/mysql-test/suite/plugins/r/multiauth.result +index aed46ac8964..24bb0a24f03 100644 +--- a/mysql-test/suite/plugins/r/multiauth.result ++++ b/mysql-test/suite/plugins/r/multiauth.result +@@ -181,7 +181,8 @@ show create user mysqltest1; + CREATE USER for mysqltest1@% + CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' + # no plugin = failure +-mysqltest: Could not open connection 'default': 1045 Plugin client_ed25519 could not be loaded: <PLUGINDIR>/no/client_ed25519.so: cannot open shared object file: No such file or directory ++mysqltest: Could not open connection 'default': 1045 Plugin client_ed25519 could not be loaded: Could not load module <PLUGINDIR>/no/client_ed25519.so. ++System error: No such file or directory + alter user mysqltest1 identified via ed25519 as password("good") OR mysql_native_password as password("works"); + show create user mysqltest1; + CREATE USER for mysqltest1@% diff --git a/mysql-test/suite/plugins/r/multiauth.result b/mysql-test/suite/plugins/r/multiauth.result new file mode 100644 index 00000000..8ae45d1f --- /dev/null +++ b/mysql-test/suite/plugins/r/multiauth.result @@ -0,0 +1,209 @@ +install soname 'auth_ed25519'; +create user 'USER' identified via unix_socket OR mysql_native_password as password("GOOD"); +create user mysqltest1 identified via unix_socket OR mysql_native_password as password("good"); +show create user mysqltest1; +CREATE USER for mysqltest1@% +CREATE USER `mysqltest1`@`%` IDENTIFIED VIA unix_socket OR mysql_native_password USING '*8409037B3E362D6DAE24C8E667F4D3B66716144E' +# name match = ok +select user(), current_user(), database(); +user() current_user() database() +USER@localhost USER@% test +# name does not match, password good = ok +select user(), current_user(), database(); +user() current_user() database() +mysqltest1@localhost mysqltest1@% test +# name does not match, password bad = failure +mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES) +drop user 'USER', mysqltest1; +create user 'USER' identified via mysql_native_password as password("GOOD") OR unix_socket; +create user mysqltest1 identified via mysql_native_password as password("good") OR unix_socket; +show create user mysqltest1; +CREATE USER for mysqltest1@% +CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING '*8409037B3E362D6DAE24C8E667F4D3B66716144E' OR unix_socket +# name match = ok +select user(), current_user(), database(); +user() current_user() database() +USER@localhost USER@% test +# name does not match, password good = ok +select user(), current_user(), database(); +user() current_user() database() +mysqltest1@localhost mysqltest1@% test +# name does not match, password bad = failure +mysqltest: Could not open connection 'default': 1698 Access denied for user 'mysqltest1'@'localhost' +drop user 'USER', mysqltest1; +create user 'USER' identified via unix_socket OR ed25519 as password("GOOD"); +create user mysqltest1 identified via unix_socket OR ed25519 as password("good"); +show create user mysqltest1; +CREATE USER for mysqltest1@% +CREATE USER `mysqltest1`@`%` IDENTIFIED VIA unix_socket OR ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' +# name match = ok +select user(), current_user(), database(); +user() current_user() database() +USER@localhost USER@% test +# name does not match, password good = ok +select user(), current_user(), database(); +user() current_user() database() +mysqltest1@localhost mysqltest1@% test +# name does not match, password bad = failure +mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES) +drop user 'USER', mysqltest1; +create user 'USER' identified via ed25519 as password("GOOD") OR unix_socket; +create user mysqltest1 identified via ed25519 as password("good") OR unix_socket; +show create user mysqltest1; +CREATE USER for mysqltest1@% +CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR unix_socket +# name match = ok +select user(), current_user(), database(); +user() current_user() database() +USER@localhost USER@% test +# name does not match, password good = ok +select user(), current_user(), database(); +user() current_user() database() +mysqltest1@localhost mysqltest1@% test +# name does not match, password bad = failure +mysqltest: Could not open connection 'default': 1698 Access denied for user 'mysqltest1'@'localhost' +drop user 'USER', mysqltest1; +create user 'USER' identified via ed25519 as password("GOOD") OR unix_socket OR mysql_native_password as password("works"); +create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works"); +show create user mysqltest1; +CREATE USER for mysqltest1@% +CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR unix_socket OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460' +# name match = ok +select user(), current_user(), database(); +user() current_user() database() +USER@localhost USER@% test +# name does not match, password good = ok +select user(), current_user(), database(); +user() current_user() database() +mysqltest1@localhost mysqltest1@% test +# name does not match, second password works = ok +select user(), current_user(), database(); +user() current_user() database() +mysqltest1@localhost mysqltest1@% test +# name does not match, password bad = failure +mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES) +drop user 'USER', mysqltest1; +create user mysqltest1 identified via mysql_native_password as password("good") OR mysql_native_password as password("works"); +show create user mysqltest1; +CREATE USER for mysqltest1@% +CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING '*8409037B3E362D6DAE24C8E667F4D3B66716144E' OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460' +# password good = ok +select user(), current_user(), database(); +user() current_user() database() +mysqltest1@localhost mysqltest1@% test +# second password works = ok +select user(), current_user(), database(); +user() current_user() database() +mysqltest1@localhost mysqltest1@% test +# password bad = failure +mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES) +drop user mysqltest1; +create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works"); +show grants for mysqltest1; +Grants for mysqltest1@% +GRANT USAGE ON *.* TO `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR unix_socket OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460' +select json_detailed(priv) from mysql.global_priv where user='mysqltest1'; +json_detailed(priv) +{ + "access": 0, + "version_id": VERSION_ID, + "plugin": "mysql_native_password", + "authentication_string": "*7D8C3DF236D9163B6C274A9D47704BC496988460", + "auth_or": + [ + + { + "plugin": "ed25519", + "authentication_string": "F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc" + }, + + { + "plugin": "unix_socket" + }, + + { + } + ], + "password_last_changed": # +} +select password,plugin,authentication_string from mysql.user where user='mysqltest1'; +Password plugin authentication_string +*7D8C3DF236D9163B6C274A9D47704BC496988460 mysql_native_password *7D8C3DF236D9163B6C274A9D47704BC496988460 +flush privileges; +show create user mysqltest1; +CREATE USER for mysqltest1@% +CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR unix_socket OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460' +set password for mysqltest1 = password('foobar'); +show create user mysqltest1; +CREATE USER for mysqltest1@% +CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'qv2mG6HWCuy32Slb5xhV4THStewNz2VINVPbgk+XAJ8' OR unix_socket OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460' +alter user mysqltest1 identified via unix_socket OR mysql_native_password as password("some"); +show create user mysqltest1; +CREATE USER for mysqltest1@% +CREATE USER `mysqltest1`@`%` IDENTIFIED VIA unix_socket OR mysql_native_password USING '*BFE3F4604CFD21E6595080A261D92EF0183B5971' +set password for mysqltest1 = password('foobar'); +show create user mysqltest1; +CREATE USER for mysqltest1@% +CREATE USER `mysqltest1`@`%` IDENTIFIED VIA unix_socket OR mysql_native_password USING '*9B500343BC52E2911172EB52AE5CF4847604C6E5' +alter user mysqltest1 identified via unix_socket; +set password for mysqltest1 = password('bla'); +ERROR HY000: SET PASSWORD is ignored for users authenticating via unix_socket plugin +alter user mysqltest1 identified via mysql_native_password as password("some") or unix_socket; +show create user mysqltest1; +CREATE USER for mysqltest1@% +CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING '*BFE3F4604CFD21E6595080A261D92EF0183B5971' OR unix_socket +drop user mysqltest1; +# switching from mysql.global_priv to mysql.user +create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works"); +ERROR HY000: Column count of mysql.user is wrong. Expected 3, found 47. Created with MariaDB XX.YY.ZZ, now running XX.YY.ZZ. Please use mariadb-upgrade to fix this error +# switching back from mysql.user to mysql.global_priv +create user 'USER' identified via mysql_native_password as '1234567890123456789012345678901234567890a' OR unix_socket; +create user mysqltest1 identified via mysql_native_password as '1234567890123456789012345678901234567890a' OR unix_socket; +update mysql.global_priv set priv=replace(priv, '1234567890123456789012345678901234567890a', 'invalid password'); +flush privileges; +show create user mysqltest1; +CREATE USER for mysqltest1@% +CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING 'invalid password' OR unix_socket +# name match = ok +select user(), current_user(), database(); +user() current_user() database() +USER@localhost USER@% test +# name does not match = failure +mysqltest: Could not open connection 'default': 1698 Access denied for user 'mysqltest1'@'localhost' +# SET PASSWORD helps +set password for mysqltest1 = password('bla'); +select user(), current_user(), database(); +user() current_user() database() +mysqltest1@localhost mysqltest1@% test +drop user 'USER', mysqltest1; +create user mysqltest1 identified via ed25519 as password("good"); +show create user mysqltest1; +CREATE USER for mysqltest1@% +CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' +# no plugin = failure +mysqltest: Could not open connection 'default': 1045 Plugin client_ed25519 could not be loaded: <PLUGINDIR>/no/client_ed25519.so: cannot open shared object file: No such file or directory +alter user mysqltest1 identified via ed25519 as password("good") OR mysql_native_password as password("works"); +show create user mysqltest1; +CREATE USER for mysqltest1@% +CREATE USER `mysqltest1`@`%` IDENTIFIED VIA ed25519 USING 'F4aF8bw7130VaRbdLCl4f/P/wkjDmgJXwWvpJ5gmsZc' OR mysql_native_password USING '*7D8C3DF236D9163B6C274A9D47704BC496988460' +# no plugin = failure +mysqltest: Could not open connection 'default': 1045 Access denied for user 'mysqltest1'@'localhost' (using password: YES) +# no plugin, second password works = ok +select user(), current_user(), database(); +user() current_user() database() +mysqltest1@localhost mysqltest1@% test +drop user mysqltest1; +uninstall soname 'auth_ed25519'; +create user mysqltest1 identified via mysql_native_password as password("good") OR unix_socket; +show create user mysqltest1; +CREATE USER for mysqltest1@% +CREATE USER `mysqltest1`@`%` IDENTIFIED VIA mysql_native_password USING '*8409037B3E362D6DAE24C8E667F4D3B66716144E' OR unix_socket +alter user mysqltest1 identified via mysql_native_password as password("better"); +show create user mysqltest1; +CREATE USER for mysqltest1@% +CREATE USER `mysqltest1`@`%` IDENTIFIED BY PASSWORD '*6E6CABC9967AB586F009616BA69DAC2953849C88' +flush privileges; +show create user mysqltest1; +CREATE USER for mysqltest1@% +CREATE USER `mysqltest1`@`%` IDENTIFIED BY PASSWORD '*6E6CABC9967AB586F009616BA69DAC2953849C88' +drop user mysqltest1; diff --git a/mysql-test/suite/plugins/r/pam.result b/mysql-test/suite/plugins/r/pam.result new file mode 100644 index 00000000..40075245 --- /dev/null +++ b/mysql-test/suite/plugins/r/pam.result @@ -0,0 +1,65 @@ +install plugin pam soname 'auth_pam.so'; +create user test_pam identified via pam using 'mariadb_mtr'; +create user pam_test; +grant proxy on pam_test to test_pam; +# +# athentication is successful, challenge/pin are ok +# note that current_user() differs from user() +# +Challenge input first. +Enter: ************************* +Now, the magic number! +PIN: 9225 +select user(), current_user(), database(); +user() current_user() database() +test_pam@localhost pam_test@% test +# +# athentication is unsuccessful +# +Challenge input first. +Enter: ************************* +Now, the magic number! +PIN: 9224 +# +# athentication is unsuccessful +# +Challenge input first. +Enter: **************** +Now, the magic number! +PIN: 616 +# +# athentication is successful +# +Now, the magic number! +PIN: 9212 +select user(), current_user(), database(); +user() current_user() database() +test_pam@localhost pam_test@% test +# +# athentication is unsuccessful +# +Now, the magic number! +PIN: 9212 +drop user test_pam; +drop user pam_test; +create user PAM_TEST identified via pam using 'mariadb_mtr'; +# +# athentication is unsuccessful +# +Challenge input first. +Enter: ************************* +Now, the magic number! +PIN: 9225 +set global pam_winbind_workaround=1; +# +# athentication is successful +# +Challenge input first. +Enter: ************************* +Now, the magic number! +PIN: 9225 +select user(), current_user(), database(); +user() current_user() database() +PAM_TEST@localhost PAM_TEST@% test +drop user PAM_TEST; +uninstall plugin pam; diff --git a/mysql-test/suite/plugins/r/pam_cleartext.result b/mysql-test/suite/plugins/r/pam_cleartext.result new file mode 100644 index 00000000..07c379a4 --- /dev/null +++ b/mysql-test/suite/plugins/r/pam_cleartext.result @@ -0,0 +1,21 @@ +install plugin pam soname 'auth_pam.so'; +create user test_pam identified via pam using 'mariadb_mtr'; +create user pam_test; +grant proxy on pam_test to test_pam; +show variables like 'pam_use_%'; +Variable_name Value +pam_use_cleartext_plugin ON +# +# same test as in pam.test now fails +# +# +# success +# +user() current_user() database() +test_pam@localhost pam_test@% NULL +# +# failure +# +drop user test_pam; +drop user pam_test; +uninstall plugin pam; diff --git a/mysql-test/suite/plugins/r/pam_v1.result b/mysql-test/suite/plugins/r/pam_v1.result new file mode 100644 index 00000000..66776e3b --- /dev/null +++ b/mysql-test/suite/plugins/r/pam_v1.result @@ -0,0 +1,38 @@ +install plugin pam soname 'auth_pam_v1.so'; +create user test_pam identified via pam using 'mariadb_mtr'; +create user pam_test; +grant proxy on pam_test to test_pam; +# +# athentication is successful, challenge/pin are ok +# note that current_user() differs from user() +# +Challenge input first. +Enter: ************************* +Now, the magic number! +PIN: 9225 +select user(), current_user(), database(); +user() current_user() database() +test_pam@localhost pam_test@% test +# +# athentication is unsuccessful +# +Challenge input first. +Enter: ************************* +Now, the magic number! +PIN: 9224 +# +# athentication is successful +# +Now, the magic number! +PIN: 9212 +select user(), current_user(), database(); +user() current_user() database() +test_pam@localhost pam_test@% test +# +# athentication is unsuccessful +# +Now, the magic number! +PIN: 9212 +drop user test_pam; +drop user pam_test; +uninstall plugin pam; diff --git a/mysql-test/suite/plugins/r/processlist.result b/mysql-test/suite/plugins/r/processlist.result new file mode 100644 index 00000000..c27534d9 --- /dev/null +++ b/mysql-test/suite/plugins/r/processlist.result @@ -0,0 +1,9 @@ +create table t1 (a int) engine=innodb; +start transaction; +insert t1 values (1); +connect con2,localhost,root; +state from show engine innodb status, must be empty + +disconnect con2; +connection default; +drop table t1; diff --git a/mysql-test/suite/plugins/r/qc_info.result b/mysql-test/suite/plugins/r/qc_info.result new file mode 100644 index 00000000..41073359 --- /dev/null +++ b/mysql-test/suite/plugins/r/qc_info.result @@ -0,0 +1,59 @@ +set @save_query_cache_size=@@global.query_cache_size; +set global query_cache_type=ON; +set local query_cache_type=ON; +set global query_cache_size=1355776; +create table t1 (a int not null); +insert into t1 values (1),(2),(3); +select * from t1; +a +1 +2 +3 +select statement_schema, statement_text, result_blocks_count, result_blocks_size from information_schema.query_cache_info; +statement_schema statement_text result_blocks_count result_blocks_size +test select * from t1 1 512 +select @@time_zone into @time_zone; +select @@default_week_format into @default_week_format; +select @@character_set_client into @character_set_client; +select @@character_set_results into @character_set_results; +select @@sql_mode into @sql_mode; +select @@div_precision_increment into @div_precision_increment; +select @@lc_time_names into @lc_time_names; +select @@max_sort_length into @max_sort_length; +select @@autocommit into @autocommit; +select @@group_concat_max_len into @group_concat_max_len; +select Name into @new_time_zone from mysql.time_zone_name limit 1; +set time_zone=@new_time_zone,default_week_format=4,character_set_client='binary',character_set_results='utf32',collation_connection='utf32_bin',sql_mode='STRICT_ALL_TABLES',div_precision_increment=7,lc_time_names='ar_SD',autocommit=0, group_concat_max_len=513, max_sort_length=1011; +select * from t1; +set time_zone= @time_zone, default_week_format= @default_week_format, character_set_client= @character_set_client,character_set_results= @character_set_results, sql_mode= @sql_mode, div_precision_increment= @div_precision_increment, lc_time_names= @lc_time_names, autocommit= @autocommit, group_concat_max_len= @group_concat_max_len, max_sort_length= @max_sort_length; +select * from information_schema.query_cache_info; +STATEMENT_SCHEMA STATEMENT_TEXT RESULT_BLOCKS_COUNT RESULT_BLOCKS_SIZE RESULT_BLOCKS_SIZE_USED LIMIT MAX_SORT_LENGTH GROUP_CONCAT_MAX_LENGTH CHARACTER_SET_CLIENT CHARACTER_SET_RESULT COLLATION TIMEZONE DEFAULT_WEEK_FORMAT DIV_PRECISION_INCREMENT SQL_MODE LC_TIME_NAMES CLIENT_LONG_FLAG CLIENT_PROTOCOL_41 PROTOCOL_TYPE MORE_RESULTS_EXISTS IN_TRANS AUTOCOMMIT PACKET_NUMBER HITS +test select * from t1 1 512 # -1 1011 513 binary utf32 utf32_bin Europe/Moscow 4 7 STRICT_ALL_TABLES ar_SD 1 1 # 0 0 0 # 0 +test select * from t1 1 512 # -1 1024 1048576 latin1 latin1 latin1_swedish_ci SYSTEM 0 4 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION en_US 1 1 # 0 0 1 # 0 +reset query cache; +select * from t1; +a +1 +2 +3 +select * from t1; +a +1 +2 +3 +select hits, statement_text from information_schema.query_cache_info; +hits statement_text +1 select * from t1 +drop table t1; +select statement_schema, statement_text, result_blocks_count, result_blocks_size from information_schema.query_cache_info; +statement_schema statement_text result_blocks_count result_blocks_size +set global query_cache_size = 0; +select * from information_schema.query_cache_info; +STATEMENT_SCHEMA STATEMENT_TEXT RESULT_BLOCKS_COUNT RESULT_BLOCKS_SIZE RESULT_BLOCKS_SIZE_USED LIMIT MAX_SORT_LENGTH GROUP_CONCAT_MAX_LENGTH CHARACTER_SET_CLIENT CHARACTER_SET_RESULT COLLATION TIMEZONE DEFAULT_WEEK_FORMAT DIV_PRECISION_INCREMENT SQL_MODE LC_TIME_NAMES CLIENT_LONG_FLAG CLIENT_PROTOCOL_41 PROTOCOL_TYPE MORE_RESULTS_EXISTS IN_TRANS AUTOCOMMIT PACKET_NUMBER HITS +set global query_cache_size= default; +set global query_cache_type=default; +show query_cache_info; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'query_cache_info' at line 1 +flush query_cache_info; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'query_cache_info' at line 1 +set @@global.query_cache_size=@save_query_cache_size; diff --git a/mysql-test/suite/plugins/r/qc_info_priv.result b/mysql-test/suite/plugins/r/qc_info_priv.result new file mode 100644 index 00000000..78ea78a3 --- /dev/null +++ b/mysql-test/suite/plugins/r/qc_info_priv.result @@ -0,0 +1,48 @@ +set @save_query_cache_size=@@global.query_cache_size; +set global query_cache_type=ON; +set local query_cache_type=ON; +set global query_cache_size=1355776; +create table t1 (a int not null); +insert into t1 values (1),(2),(3); +select * from t1; +a +1 +2 +3 +select statement_schema, statement_text, result_blocks_count, result_blocks_size from information_schema.query_cache_info; +statement_schema statement_text result_blocks_count result_blocks_size +test select * from t1 1 512 +select @@time_zone into @time_zone; +select @@default_week_format into @default_week_format; +select @@character_set_client into @character_set_client; +select @@character_set_results into @character_set_results; +select @@sql_mode into @sql_mode; +select @@div_precision_increment into @div_precision_increment; +select @@lc_time_names into @lc_time_names; +select @@max_sort_length into @max_sort_length; +select @@autocommit into @autocommit; +select @@group_concat_max_len into @group_concat_max_len; +select Name into @new_time_zone from mysql.time_zone_name limit 1; +set time_zone=@new_time_zone,default_week_format=4,character_set_client='binary',character_set_results='utf32',collation_connection='utf32_bin',sql_mode='STRICT_ALL_TABLES',div_precision_increment=7,lc_time_names='ar_SD',autocommit=0, group_concat_max_len=513, max_sort_length=1011; +select * from t1; +set time_zone= @time_zone, default_week_format= @default_week_format, character_set_client= @character_set_client,character_set_results= @character_set_results, sql_mode= @sql_mode, div_precision_increment= @div_precision_increment, lc_time_names= @lc_time_names, autocommit= @autocommit, group_concat_max_len= @group_concat_max_len, max_sort_length= @max_sort_length; +select * from information_schema.query_cache_info; +STATEMENT_SCHEMA STATEMENT_TEXT RESULT_BLOCKS_COUNT RESULT_BLOCKS_SIZE RESULT_BLOCKS_SIZE_USED LIMIT MAX_SORT_LENGTH GROUP_CONCAT_MAX_LENGTH CHARACTER_SET_CLIENT CHARACTER_SET_RESULT COLLATION TIMEZONE DEFAULT_WEEK_FORMAT DIV_PRECISION_INCREMENT SQL_MODE LC_TIME_NAMES CLIENT_LONG_FLAG CLIENT_PROTOCOL_41 PROTOCOL_TYPE MORE_RESULTS_EXISTS IN_TRANS AUTOCOMMIT PACKET_NUMBER HITS +test select * from t1 1 512 # -1 1011 513 binary utf32 utf32_bin Europe/Moscow 4 7 STRICT_ALL_TABLES ar_SD 1 1 # 0 0 0 # 0 +test select * from t1 1 512 # -1 1024 1048576 latin1 latin1 latin1_swedish_ci SYSTEM 0 4 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION en_US 1 1 # 0 0 1 # 0 +create user mysqltest; +connect conn1,localhost,mysqltest,,; +connection conn1; +select a from t1; +a +1 +2 +3 +select count(*) from information_schema.query_cache_info; +count(*) +0 +connection default; +drop user mysqltest; +drop table t1; +set @@global.query_cache_size=@save_query_cache_size; +set global query_cache_type=default; diff --git a/mysql-test/suite/plugins/r/server_audit.result b/mysql-test/suite/plugins/r/server_audit.result new file mode 100644 index 00000000..40c07805 --- /dev/null +++ b/mysql-test/suite/plugins/r/server_audit.result @@ -0,0 +1,519 @@ +install plugin server_audit soname 'server_audit'; +show variables like 'server_audit%'; +Variable_name Value +server_audit_events +server_audit_excl_users +server_audit_file_path server_audit.log +server_audit_file_rotate_now OFF +server_audit_file_rotate_size 1000000 +server_audit_file_rotations 9 +server_audit_incl_users +server_audit_logging OFF +server_audit_mode 0 +server_audit_output_type file +server_audit_query_log_limit 1024 +server_audit_syslog_facility LOG_USER +server_audit_syslog_ident mysql-server_auditing +server_audit_syslog_info +server_audit_syslog_priority LOG_INFO +set global server_audit_file_path=null; +set global server_audit_incl_users=null; +set global server_audit_file_path='server_audit.log'; +set global server_audit_output_type=file; +set global server_audit_logging=on; +set global server_audit_incl_users= repeat("'root',", 10000); +ERROR 42000: Variable 'server_audit_incl_users' can't be set to the value of ''root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','...' +show variables like 'server_audit_incl_users'; +Variable_name Value +server_audit_incl_users +set global server_audit_excl_users= repeat("'root',", 10000); +ERROR 42000: Variable 'server_audit_excl_users' can't be set to the value of ''root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','root','...' +show variables like 'server_audit_excl_users'; +Variable_name Value +server_audit_excl_users +connect con1,localhost,root,,mysql; +disconnect con1; +connect(localhost,no_such_user,,mysql,MASTER_PORT,MASTER_SOCKET); +connect con1,localhost,no_such_user,,mysql; +ERROR 28000: Access denied for user 'no_such_user'@'localhost' (using password: NO) +connection default; +set global server_audit_incl_users='odin, dva, tri'; +create table t1 (id int); +set global server_audit_incl_users='odin, root, dva, tri'; +create table t2 (id int); +set global server_audit_excl_users='odin, dva, tri'; +Warnings: +Warning 1 User 'odin' is in the server_audit_incl_users, so wasn't added. +Warning 1 User 'dva' is in the server_audit_incl_users, so wasn't added. +Warning 1 User 'tri' is in the server_audit_incl_users, so wasn't added. +insert into t1 values (1), (2); +select * from t1; +id +1 +2 +set global server_audit_incl_users='odin, root, dva, tri'; +insert into t2 values (1), (2); +select * from t2; +id +1 +2 +alter table t1 rename renamed_t1; +set global server_audit_events='connect,query'; +select 1, +2, +# comment +3; +1 2 3 +1 2 3 +insert into t2 values (1), (2); +select * from t2; +id +1 +2 +1 +2 +select * from t_doesnt_exist; +ERROR 42S02: Table 'test.t_doesnt_exist' doesn't exist +syntax_error_query; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'syntax_error_query' at line 1 +drop table renamed_t1, t2; +show variables like 'server_audit%'; +Variable_name Value +server_audit_events CONNECT,QUERY +server_audit_excl_users +server_audit_file_path server_audit.log +server_audit_file_rotate_now OFF +server_audit_file_rotate_size 1000000 +server_audit_file_rotations 9 +server_audit_incl_users odin, root, dva, tri +server_audit_logging ON +server_audit_mode 0 +server_audit_output_type file +server_audit_query_log_limit 1024 +server_audit_syslog_facility LOG_USER +server_audit_syslog_ident mysql-server_auditing +server_audit_syslog_info +server_audit_syslog_priority LOG_INFO +set global server_audit_mode=1; +set global server_audit_events=''; +create database sa_db; +connect con1,localhost,root,,test; +create table t1 (id2 int); +insert into t1 values (1), (2); +select * from t1; +id2 +1 +2 +drop table t1; +use sa_db; +create table sa_t1(id int); +insert into sa_t1 values (1), (2); +drop table sa_t1; +drop database sa_db; +disconnect con1; +connection default; +create database sa_db; +use sa_db; +CREATE USER u1 IDENTIFIED BY 'pwd-123'; +GRANT ALL ON sa_db TO u2 IDENTIFIED BY "pwd-321"; +SET PASSWORD FOR u1 = PASSWORD('pwd 098'); +CREATE USER u3 IDENTIFIED BY ''; +ALTER USER u3 IDENTIFIED BY 'pwd-456'; +drop user u1, u2, u3; +set global server_audit_events='query_ddl'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +id +1 +2 +select 2; +2 +2 +(select 2); +2 +2 +/*! select 2*/; +2 +2 +/*comment*/ select 2; +2 +2 +drop table t1; +create procedure pr1() insert into test.t1 values ("foo", 42); +create function fn1(i int) returns int deterministic return i+1; +drop procedure pr1; +drop function fn1; +set global server_audit_events='query_ddl,query_dml'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +id +1 +2 +select 2; +2 +2 +drop table t1; +set global server_audit_events='query_dml'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +id +1 +2 +select 2; +2 +2 +(select 2); +2 +2 +/*! select 2*/; +2 +2 +/*comment*/ select 2; +2 +2 +drop table t1; +set global server_audit_events='query_dcl'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +id +1 +2 +CREATE USER u1 IDENTIFIED BY 'pwd-123'; +GRANT ALL ON sa_db TO u2 IDENTIFIED BY "pwd-321"; +SET PASSWORD +# comment +FOR u1 = PASSWORD('pwd 098'); +SET PASSWORD FOR u1=<secret>; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '<secret>' at line 1 +CREATE USER u3 IDENTIFIED BY ''; +drop user u1, u2, u3; +select 2; +2 +2 +(select 2); +2 +2 +/*! select 2*/; +2 +2 +/*comment*/ select 2; +2 +2 +drop table t1; +set global server_audit_events='query_dml_no_select'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +id +1 +2 +select 2; +2 +2 +drop table t1; +create procedure pr1() insert into test.t1 values ("foo", 42); +create function fn1(i int) returns int deterministic return i+1; +drop procedure pr1; +drop function fn1; +create procedure pr1() insert into test.t1 values ("foo", 42); +create function fn1(i int) returns int deterministic return i+1; +drop procedure pr1; +drop function fn1; +set global server_audit_events='table'; +set global server_audit_incl_users='user1'; +create user user1@localhost; +grant all on sa_db.* to user1@localhost; +connect cn1,localhost,user1,,sa_db; +connection cn1; +create table t1(id int) engine=myisam; +insert delayed into t1 values (1); +connection default; +# Waiting until INSERT DELAYED thread does the insert. +drop table t1; +set global server_audit_logging= off; +set global server_audit_incl_users='root'; +set global server_audit_logging= on; +disconnect cn1; +drop user user1@localhost; +set global server_audit_events=''; +set global server_audit_incl_users='root, plug_dest'; +CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; +CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; +connect(localhost,plug,plug_dest,test,MYSQL_PORT,MYSQL_SOCK); +connect plug_con,localhost,plug,plug_dest; +ERROR 28000: Access denied for user 'plug'@'localhost' (using password: YES) +GRANT PROXY ON plug_dest TO plug; +connect plug_con,localhost,plug,plug_dest; +connection plug_con; +select USER(),CURRENT_USER(); +USER() CURRENT_USER() +plug@localhost plug_dest@% +connection default; +disconnect plug_con; +DROP USER plug; +DROP USER plug_dest; +set global server_audit_query_log_limit= 15; +select (1), (2), (3), (4); +1 2 3 4 +1 2 3 4 +select 'A', 'B', 'C', 'D'; +A B C D +A B C D +set global server_audit_query_log_limit= 1024; +drop database sa_db; +set global server_audit_file_path='.'; +show status like 'server_audit_current_log'; +Variable_name Value +Server_audit_current_log HOME_DIR/server_audit.log +set global server_audit_file_path=''; +show status like 'server_audit_current_log'; +Variable_name Value +Server_audit_current_log server_audit.log +set global server_audit_file_path=' '; +show status like 'server_audit_current_log'; +Variable_name Value +Server_audit_current_log server_audit.log +set global server_audit_file_path='nonexisting_dir/'; +Warnings: +Warning 1 SERVER AUDIT plugin can't create file 'nonexisting_dir/'. +show status like 'server_audit_current_log'; +Variable_name Value +Server_audit_current_log server_audit.log +show variables like 'server_audit%'; +Variable_name Value +server_audit_events +server_audit_excl_users +server_audit_file_path +server_audit_file_rotate_now OFF +server_audit_file_rotate_size 1000000 +server_audit_file_rotations 9 +server_audit_incl_users root, plug_dest +server_audit_logging ON +server_audit_mode 1 +server_audit_output_type file +server_audit_query_log_limit 1024 +server_audit_syslog_facility LOG_USER +server_audit_syslog_ident mysql-server_auditing +server_audit_syslog_info +server_audit_syslog_priority LOG_INFO +uninstall plugin server_audit; +Warnings: +Warning 1620 Plugin is busy and will be uninstalled on shutdown +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_logging=on',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_incl_users= repeat("\'root\',", 10000)',ID +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'show variables like \'server_audit_incl_users\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_excl_users= repeat("\'root\',", 10000)',ID +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'show variables like \'server_audit_excl_users\'',0 +TIME,HOSTNAME,root,localhost,ID,0,CONNECT,mysql,,0 +TIME,HOSTNAME,root,localhost,ID,0,DISCONNECT,mysql,,0 +TIME,HOSTNAME,no_such_user,localhost,ID,0,FAILED_CONNECT,,,ID +TIME,HOSTNAME,no_such_user,localhost,ID,0,DISCONNECT,,,0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_incl_users=\'odin, dva, tri\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_incl_users=\'odin, root, dva, tri\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,CREATE,test,t2, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'create table t2 (id int)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_excl_users=\'odin, dva, tri\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'SHOW WARNINGS',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,test,t1, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,table_stats, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,column_stats, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,index_stats, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'insert into t1 values (1), (2)',0 +TIME,HOSTNAME,root,localhost,ID,ID,READ,test,t1, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'select * from t1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_incl_users=\'odin, root, dva, tri\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,test,t2, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,table_stats, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,column_stats, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,index_stats, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'insert into t2 values (1), (2)',0 +TIME,HOSTNAME,root,localhost,ID,ID,READ,test,t2, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'select * from t2',0 +TIME,HOSTNAME,root,localhost,ID,ID,ALTER,test,t1, +TIME,HOSTNAME,root,localhost,ID,ID,RENAME,test,t1|test.renamed_t1, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,table_stats, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,column_stats, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,index_stats, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'alter table t1 rename renamed_t1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_events=\'connect,query\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'select 1,\n2,\n# comment\n3',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'insert into t2 values (1), (2)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'select * from t2',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'select * from t_doesnt_exist',ID +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'syntax_error_query',ID +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'drop table renamed_t1, t2',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'show variables like \'server_audit%\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_mode=1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_events=\'\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'create database sa_db',0 +TIME,HOSTNAME,root,localhost,ID,0,CONNECT,test,,0 +TIME,HOSTNAME,root,localhost,ID,ID,CREATE,test,t1, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'create table t1 (id2 int)',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,test,t1, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,table_stats, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,column_stats, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,index_stats, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'insert into t1 values (1), (2)',0 +TIME,HOSTNAME,root,localhost,ID,ID,READ,test,t1, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'select * from t1',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,table_stats, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,column_stats, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,index_stats, +TIME,HOSTNAME,root,localhost,ID,ID,DROP,test,t1, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'drop table t1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'use sa_db',0 +TIME,HOSTNAME,root,localhost,ID,ID,CREATE,sa_db,sa_t1, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'create table sa_t1(id int)',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,sa_db,sa_t1, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,table_stats, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,column_stats, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,index_stats, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'insert into sa_t1 values (1), (2)',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,table_stats, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,column_stats, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,index_stats, +TIME,HOSTNAME,root,localhost,ID,ID,DROP,sa_db,sa_t1, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'drop table sa_t1',0 +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,proc, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proc, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,event, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'drop database sa_db',0 +TIME,HOSTNAME,root,localhost,ID,0,DISCONNECT,,,0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'create database sa_db',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'use sa_db',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,columns_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,procs_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,roles_mapping, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'CREATE USER u1 IDENTIFIED BY *****',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'GRANT ALL ON sa_db TO u2 IDENTIFIED BY *****',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'SET PASSWORD FOR u1 = PASSWORD(*****)',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,columns_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,procs_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,roles_mapping, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'CREATE USER u3 IDENTIFIED BY *****',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'ALTER USER u3 IDENTIFIED BY *****',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,columns_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,procs_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,roles_mapping, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'drop user u1, u2, u3',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'create table t1(id int)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'drop table t1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'create procedure pr1() insert into test.t1 values ("foo", 42)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'create function fn1(i int) returns int deterministic return i+1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'drop procedure pr1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'drop function fn1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'create table t1(id int)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'insert into t1 values (1), (2)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'select * from t1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'select 2',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'drop table t1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'insert into t1 values (1), (2)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'select * from t1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'select 2',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'(select 2)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'/*! select 2*/',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'/*comment*/ select 2',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'CREATE USER u1 IDENTIFIED BY *****',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'GRANT ALL ON sa_db TO u2 IDENTIFIED BY *****',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'SET PASSWORD \n# comment\nFOR u1 = PASSWORD(*****)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'SET PASSWORD FOR u1=<secret>',ID +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'CREATE USER u3 IDENTIFIED BY *****',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'drop user u1, u2, u3',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'insert into t1 values (1), (2)',0 +TIME,HOSTNAME,user1,localhost,ID,ID,CREATE,sa_db,t1, +TIME,HOSTNAME,user1,localhost,ID,ID,WRITE,sa_db,t1, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_logging= off',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,columns_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,procs_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,roles_mapping, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_events=\'\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_incl_users=\'root, plug_dest\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,columns_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,procs_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,roles_mapping, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'CREATE USER plug IDENTIFIED WITH \'test_plugin_server\' AS \'plug_dest\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,columns_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,procs_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,roles_mapping, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'CREATE USER plug_dest IDENTIFIED BY *****',0 +TIME,HOSTNAME,plug,localhost,ID,0,FAILED_CONNECT,,,ID +TIME,HOSTNAME,plug,localhost,ID,0,DISCONNECT,,,0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'GRANT PROXY ON plug_dest TO plug',0 +TIME,HOSTNAME,plug,localhost,ID,0,CONNECT,test,,0 +TIME,HOSTNAME,plug,localhost,ID,0,PROXY_CONNECT,test,`plug_dest`@`%`,0 +TIME,HOSTNAME,plug,localhost,ID,ID,QUERY,test,'select USER(),CURRENT_USER()',0 +TIME,HOSTNAME,plug,localhost,ID,0,DISCONNECT,test,,0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,columns_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,procs_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,roles_mapping, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'DROP USER plug',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,columns_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,procs_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,roles_mapping, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'DROP USER plug_dest',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global serv',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'select (1), (2)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'select \'A\', ',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_query_log_limit= 1024',0 +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,proc, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proc, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,event, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'drop database sa_db',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_file_path=\'.\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_file_path=\'.\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'show status like \'server_audit_current_log\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_file_path=\'\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_file_path=\'\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'show status like \'server_audit_current_log\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_file_path=\' \'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_file_path=\' \'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'show status like \'server_audit_current_log\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_file_path=\'nonexisting_dir/\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_file_path=\'nonexisting_dir/\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'SHOW WARNINGS',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'show status like \'server_audit_current_log\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'show variables like \'server_audit%\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,plugin, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'uninstall plugin server_audit',0 diff --git a/mysql-test/suite/plugins/r/show_all_plugins.result b/mysql-test/suite/plugins/r/show_all_plugins.result new file mode 100644 index 00000000..3bdaf39d --- /dev/null +++ b/mysql-test/suite/plugins/r/show_all_plugins.result @@ -0,0 +1,36 @@ +flush status; +show status like '%libraries%'; +Variable_name Value +Opened_plugin_libraries 0 +select * from information_schema.all_plugins where plugin_library='ha_example.so'; +PLUGIN_NAME PLUGIN_VERSION PLUGIN_STATUS PLUGIN_TYPE PLUGIN_TYPE_VERSION PLUGIN_LIBRARY PLUGIN_LIBRARY_VERSION PLUGIN_AUTHOR PLUGIN_DESCRIPTION PLUGIN_LICENSE LOAD_OPTION PLUGIN_MATURITY PLUGIN_AUTH_VERSION +EXAMPLE 0.1 NOT INSTALLED STORAGE ENGINE MYSQL_VERSION_ID ha_example.so 1.14 Brian Aker, MySQL AB Example storage engine GPL OFF Experimental 0.1 +UNUSABLE 3.14 NOT INSTALLED DAEMON MYSQL_VERSION_ID ha_example.so 1.14 Sergei Golubchik Unusable Daemon GPL OFF Experimental 3.14.15.926 +show status like '%libraries%'; +Variable_name Value +Opened_plugin_libraries 1 +show plugins soname 'ha_example.so'; +Name Status Type Library License +EXAMPLE NOT INSTALLED STORAGE ENGINE ha_example.so GPL +UNUSABLE NOT INSTALLED DAEMON ha_example.so GPL +show status like '%libraries%'; +Variable_name Value +Opened_plugin_libraries 2 +show plugins soname like '%example%'; +Name Status Type Library License +EXAMPLE NOT INSTALLED STORAGE ENGINE ha_example.so GPL +UNUSABLE NOT INSTALLED DAEMON ha_example.so GPL +daemon_example NOT INSTALLED DAEMON libdaemon_example.so GPL +example_key_management NOT INSTALLED ENCRYPTION example_key_management.so GPL +three_attempts NOT INSTALLED AUTHENTICATION dialog_examples.so GPL +two_questions NOT INSTALLED AUTHENTICATION dialog_examples.so GPL +show status like '%libraries%'; +Variable_name Value +Opened_plugin_libraries 7 +show plugins soname where library = 'ha_example.so'; +Name Status Type Library License +EXAMPLE NOT INSTALLED STORAGE ENGINE ha_example.so GPL +UNUSABLE NOT INSTALLED DAEMON ha_example.so GPL +select variable_value > 10 from information_schema.global_status where variable_name like '%libraries%'; +variable_value > 10 +1 diff --git a/mysql-test/suite/plugins/r/simple_password_check.result b/mysql-test/suite/plugins/r/simple_password_check.result new file mode 100644 index 00000000..f8f56bc8 --- /dev/null +++ b/mysql-test/suite/plugins/r/simple_password_check.result @@ -0,0 +1,167 @@ +install soname "simple_password_check"; +select * from information_schema.plugins where plugin_name='simple_password_check'; +PLUGIN_NAME simple_password_check +PLUGIN_VERSION 1.0 +PLUGIN_STATUS ACTIVE +PLUGIN_TYPE PASSWORD VALIDATION +PLUGIN_TYPE_VERSION 1.0 +PLUGIN_LIBRARY simple_password_check.so +PLUGIN_LIBRARY_VERSION 1.14 +PLUGIN_AUTHOR Sergei Golubchik +PLUGIN_DESCRIPTION Simple password strength checks +PLUGIN_LICENSE GPL +LOAD_OPTION ON +PLUGIN_MATURITY Stable +PLUGIN_AUTH_VERSION 1.0 +select * from information_schema.system_variables where variable_name like 'simple_password_check%' order by 1; +VARIABLE_NAME SIMPLE_PASSWORD_CHECK_DIGITS +SESSION_VALUE NULL +GLOBAL_VALUE 1 +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE 1 +VARIABLE_SCOPE GLOBAL +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT Minimal required number of digits +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 1000 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED +GLOBAL_VALUE_PATH NULL +VARIABLE_NAME SIMPLE_PASSWORD_CHECK_LETTERS_SAME_CASE +SESSION_VALUE NULL +GLOBAL_VALUE 1 +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE 1 +VARIABLE_SCOPE GLOBAL +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT Minimal required number of letters of the same letter case.This limit is applied separately to upper-case and lower-case letters +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 1000 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED +GLOBAL_VALUE_PATH NULL +VARIABLE_NAME SIMPLE_PASSWORD_CHECK_MINIMAL_LENGTH +SESSION_VALUE NULL +GLOBAL_VALUE 8 +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE 8 +VARIABLE_SCOPE GLOBAL +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT Minimal required password length +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 1000 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED +GLOBAL_VALUE_PATH NULL +VARIABLE_NAME SIMPLE_PASSWORD_CHECK_OTHER_CHARACTERS +SESSION_VALUE NULL +GLOBAL_VALUE 1 +GLOBAL_VALUE_ORIGIN COMPILE-TIME +DEFAULT_VALUE 1 +VARIABLE_SCOPE GLOBAL +VARIABLE_TYPE INT UNSIGNED +VARIABLE_COMMENT Minimal required number of other (not letters or digits) characters +NUMERIC_MIN_VALUE 0 +NUMERIC_MAX_VALUE 1000 +NUMERIC_BLOCK_SIZE 1 +ENUM_VALUE_LIST NULL +READ_ONLY NO +COMMAND_LINE_ARGUMENT REQUIRED +GLOBAL_VALUE_PATH NULL +create user foo1 identified by 'pwd'; +ERROR HY000: Your password does not satisfy the current policy requirements +create user foo1; +ERROR HY000: Your password does not satisfy the current policy requirements +grant select on *.* to foo1 identified by 'pwd'; +ERROR HY000: Your password does not satisfy the current policy requirements +grant select on *.* to `FooBar1!` identified by 'FooBar1!'; +ERROR HY000: Your password does not satisfy the current policy requirements +grant select on *.* to `BarFoo1!` identified by 'FooBar1!'; +drop user `BarFoo1!`; +create user foo1 identified by 'aA.12345'; +grant select on *.* to foo1; +drop user foo1; +set global simple_password_check_digits=3; +set global simple_password_check_letters_same_case=3; +Warnings: +Warning 1292 Adjusted the value of simple_password_check_minimal_length from 8 to 10 +set global simple_password_check_other_characters=3; +Warnings: +Warning 1292 Adjusted the value of simple_password_check_minimal_length from 10 to 12 +show variables like 'simple_password_check_%'; +Variable_name Value +simple_password_check_digits 3 +simple_password_check_letters_same_case 3 +simple_password_check_minimal_length 12 +simple_password_check_other_characters 3 +create user foo1 identified by '123:qwe:ASD!'; +drop user foo1; +create user foo1 identified by '-23:qwe:ASD!'; +ERROR HY000: Your password does not satisfy the current policy requirements +create user foo1 identified by '123:4we:ASD!'; +ERROR HY000: Your password does not satisfy the current policy requirements +create user foo1 identified by '123:qwe:4SD!'; +ERROR HY000: Your password does not satisfy the current policy requirements +create user foo1 identified by '123:qwe:ASD4'; +ERROR HY000: Your password does not satisfy the current policy requirements +create user foo1 identified by '123:qwe:ASD!'; +set password for foo1 = password('qwe:-23:ASD!'); +ERROR HY000: Your password does not satisfy the current policy requirements +set password for foo1 = old_password('4we:123:ASD!'); +ERROR HY000: Your password does not satisfy the current policy requirements +set password for foo1 = password('qwe:123:4SD!'); +ERROR HY000: Your password does not satisfy the current policy requirements +set password for foo1 = old_password('qwe:123:ASD4'); +ERROR HY000: Your password does not satisfy the current policy requirements +set password for foo1 = password('qwe:123:ASD!'); +select @@strict_password_validation; +@@strict_password_validation +1 +set password for foo1 = ''; +ERROR HY000: Your password does not satisfy the current policy requirements +set password for foo1 = '2222222222222222'; +ERROR HY000: The MariaDB server is running with the --strict-password-validation option so it cannot execute this statement +set password for foo1 = '11111111111111111111111111111111111111111'; +ERROR HY000: The MariaDB server is running with the --strict-password-validation option so it cannot execute this statement +create user foo2 identified by password '11111111111111111111111111111111111111111'; +ERROR HY000: The MariaDB server is running with the --strict-password-validation option so it cannot execute this statement +grant select on *.* to foo2 identified by password '2222222222222222'; +ERROR HY000: The MariaDB server is running with the --strict-password-validation option so it cannot execute this statement +create user foo2 identified with mysql_native_password using '11111111111111111111111111111111111111111'; +ERROR HY000: The MariaDB server is running with the --strict-password-validation option so it cannot execute this statement +grant select on *.* to foo2 identified with mysql_old_password using '2222222222222222'; +ERROR HY000: The MariaDB server is running with the --strict-password-validation option so it cannot execute this statement +create user foo2 identified with mysql_native_password using ''; +ERROR HY000: Your password does not satisfy the current policy requirements +grant select on *.* to foo2 identified with mysql_old_password using ''; +ERROR HY000: Your password does not satisfy the current policy requirements +grant select on *.* to foo2 identified with mysql_old_password; +ERROR HY000: Your password does not satisfy the current policy requirements +update mysql.global_priv set priv=json_set(priv, '$.authentication_string', 'xxx') where user='foo1'; +set global strict_password_validation=0; +set password for foo1 = ''; +ERROR HY000: Your password does not satisfy the current policy requirements +set password for foo1 = '2222222222222222'; +set password for foo1 = '11111111111111111111111111111111111111111'; +create user foo2 identified by password '11111111111111111111111111111111111111111'; +drop user foo2; +grant select on *.* to foo2 identified by password '2222222222222222'; +drop user foo2; +create user foo2 identified with mysql_native_password using '11111111111111111111111111111111111111111'; +drop user foo2; +grant select on *.* to foo2 identified with mysql_old_password using '2222222222222222'; +drop user foo2; +set global strict_password_validation=1; +drop user foo1; +create role r1; +drop role r1; +flush privileges; +uninstall plugin simple_password_check; +create user foo1 identified by 'pwd'; +drop user foo1; diff --git a/mysql-test/suite/plugins/r/sql_error_log.result b/mysql-test/suite/plugins/r/sql_error_log.result new file mode 100644 index 00000000..98dfe037 --- /dev/null +++ b/mysql-test/suite/plugins/r/sql_error_log.result @@ -0,0 +1,57 @@ +drop procedure if exists test_error; +drop table if exists t1; +install plugin SQL_ERROR_LOG soname 'sql_errlog'; +show variables like 'sql_error_log%'; +Variable_name Value +sql_error_log_filename sql_errors.log +sql_error_log_rate 1 +sql_error_log_rotate OFF +sql_error_log_rotations 9 +sql_error_log_size_limit 1000000 +set global sql_error_log_rate=1; +select * from t_doesnt_exist; +ERROR 42S02: Table 'test.t_doesnt_exist' doesn't exist +syntax_error_query; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'syntax_error_query' at line 1 +CREATE PROCEDURE test_error() +BEGIN +DECLARE CONTINUE HANDLER +FOR 1146 +BEGIN +RESIGNAL SQLSTATE '40000' SET +MYSQL_ERRNO = 1000, +MESSAGE_TEXT = 'new message'; +END; +SELECT `c` FROM `temptab`; +END| +CALL test_error(); +ERROR 40000: new message +drop procedure test_error; +SET SQL_MODE = STRICT_ALL_TABLES; +create table t1(id int); +insert into t1 values ('aa'); +ERROR 22007: Incorrect integer value: 'aa' for column `test`.`t1`.`id` at row 1 +SET SQL_MODE = ''; +drop table t1; +SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'not_exists' AND TABLE_NAME = 'not_exists'; +TABLE_NAME +CREATE procedure e1() +BEGIN +START TRANSACTION; +INSERT INTO test.non_exists VALUES (0,0,0) /* e1 */; +COMMIT; +END| +CALL e1(); +ERROR 42S02: Table 'test.non_exists' doesn't exist +DROP PROCEDURE e1; +uninstall plugin SQL_ERROR_LOG; +Warnings: +Warning 1620 Plugin is busy and will be uninstalled on shutdown +TIME HOSTNAME ERROR 1146: Table 'test.t_doesnt_exist' doesn't exist : select * from t_doesnt_exist +TIME HOSTNAME ERROR 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'syntax_error_query' at line 1 : syntax_error_query +TIME HOSTNAME ERROR 1146: Table 'test.temptab' doesn't exist : SELECT `c` FROM `temptab` +TIME HOSTNAME ERROR 1000: new message : RESIGNAL SQLSTATE '40000' SET +MYSQL_ERRNO = 1000, +MESSAGE_TEXT = 'new message' +TIME HOSTNAME ERROR 1366: Incorrect integer value: 'aa' for column `test`.`t1`.`id` at row 1 : insert into t1 values ('aa') +TIME HOSTNAME ERROR 1146: Table 'test.non_exists' doesn't exist : INSERT INTO test.non_exists VALUES (0,0,0) /* e1 */ diff --git a/mysql-test/suite/plugins/r/test_sql_service.result b/mysql-test/suite/plugins/r/test_sql_service.result new file mode 100644 index 00000000..af414d6c --- /dev/null +++ b/mysql-test/suite/plugins/r/test_sql_service.result @@ -0,0 +1,8 @@ +install plugin test_sql_service soname 'test_sql_service'; +set global test_sql_service_run_test= 1; +show status like 'test_sql_service%'; +Variable_name Value +Test_sql_service_passed 0 +uninstall plugin test_sql_service; +Warnings: +Warning 1620 Plugin is busy and will be uninstalled on shutdown diff --git a/mysql-test/suite/plugins/r/thread_pool_server_audit.result b/mysql-test/suite/plugins/r/thread_pool_server_audit.result new file mode 100644 index 00000000..8c6ece53 --- /dev/null +++ b/mysql-test/suite/plugins/r/thread_pool_server_audit.result @@ -0,0 +1,392 @@ +install plugin server_audit soname 'server_audit'; +show variables like 'server_audit%'; +Variable_name Value +server_audit_events +server_audit_excl_users +server_audit_file_path server_audit.log +server_audit_file_rotate_now OFF +server_audit_file_rotate_size 1000000 +server_audit_file_rotations 9 +server_audit_incl_users +server_audit_logging OFF +server_audit_mode 0 +server_audit_output_type file +server_audit_query_log_limit 1024 +server_audit_syslog_facility LOG_USER +server_audit_syslog_ident mysql-server_auditing +server_audit_syslog_info +server_audit_syslog_priority LOG_INFO +set global server_audit_file_path=null; +set global server_audit_incl_users=null; +set global server_audit_file_path='server_audit.log'; +set global server_audit_output_type=file; +set global server_audit_logging=on; +connect con1,localhost,root,,mysql; +connection default; +disconnect con1; +connect(localhost,no_such_user,,mysql,MASTER_PORT,MASTER_SOCKET); +connect con1,localhost,no_such_user,,mysql; +ERROR 28000: Access denied for user 'no_such_user'@'localhost' (using password: NO) +connection default; +set global server_audit_incl_users='odin, dva, tri'; +create table t1 (id int); +set global server_audit_incl_users='odin, root, dva, tri'; +create table t2 (id int); +set global server_audit_excl_users='odin, dva, tri'; +Warnings: +Warning 1 User 'odin' is in the server_audit_incl_users, so wasn't added. +Warning 1 User 'dva' is in the server_audit_incl_users, so wasn't added. +Warning 1 User 'tri' is in the server_audit_incl_users, so wasn't added. +insert into t1 values (1), (2); +select * from t1; +id +1 +2 +set global server_audit_incl_users='odin, root, dva, tri'; +insert into t2 values (1), (2); +select * from t2; +id +1 +2 +alter table t1 rename renamed_t1; +set global server_audit_events='connect,query'; +select 1, +2, +# comment +3; +1 2 3 +1 2 3 +insert into t2 values (1), (2); +select * from t2; +id +1 +2 +1 +2 +select * from t_doesnt_exist; +ERROR 42S02: Table 'test.t_doesnt_exist' doesn't exist +syntax_error_query; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'syntax_error_query' at line 1 +drop table renamed_t1, t2; +show variables like 'server_audit%'; +Variable_name Value +server_audit_events CONNECT,QUERY +server_audit_excl_users +server_audit_file_path server_audit.log +server_audit_file_rotate_now OFF +server_audit_file_rotate_size 1000000 +server_audit_file_rotations 9 +server_audit_incl_users odin, root, dva, tri +server_audit_logging ON +server_audit_mode 0 +server_audit_output_type file +server_audit_query_log_limit 1024 +server_audit_syslog_facility LOG_USER +server_audit_syslog_ident mysql-server_auditing +server_audit_syslog_info +server_audit_syslog_priority LOG_INFO +set global server_audit_mode=1; +set global server_audit_events=''; +create database sa_db; +connect con1,localhost,root,,test; +connection con1; +create table t1 (id2 int); +insert into t1 values (1), (2); +select * from t1; +id2 +1 +2 +drop table t1; +use sa_db; +create table sa_t1(id int); +insert into sa_t1 values (1), (2); +drop table sa_t1; +drop database sa_db; +connection default; +disconnect con1; +create database sa_db; +use sa_db; +CREATE USER u1 IDENTIFIED BY 'pwd-123'; +GRANT ALL ON sa_db TO u2 IDENTIFIED BY "pwd-321"; +SET PASSWORD FOR u1 = PASSWORD('pwd 098'); +CREATE USER u3 IDENTIFIED BY ''; +drop user u1, u2, u3; +set global server_audit_events='query_ddl'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +id +1 +2 +select 2; +2 +2 +(select 2); +2 +2 +/*! select 2*/; +2 +2 +/*comment*/ select 2; +2 +2 +drop table t1; +set global server_audit_events='query_ddl,query_dml'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +id +1 +2 +select 2; +2 +2 +drop table t1; +set global server_audit_events='query_dml'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +id +1 +2 +select 2; +2 +2 +(select 2); +2 +2 +/*! select 2*/; +2 +2 +/*comment*/ select 2; +2 +2 +drop table t1; +set global server_audit_events='query_dcl'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +id +1 +2 +CREATE USER u1 IDENTIFIED BY 'pwd-123'; +GRANT ALL ON sa_db TO u2 IDENTIFIED BY "pwd-321"; +SET PASSWORD +# comment +FOR u1 = PASSWORD('pwd 098'); +SET PASSWORD FOR u1=<secret>; +ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '<secret>' at line 1 +CREATE USER u3 IDENTIFIED BY ''; +drop user u1, u2, u3; +select 2; +2 +2 +(select 2); +2 +2 +/*! select 2*/; +2 +2 +/*comment*/ select 2; +2 +2 +drop table t1; +set global server_audit_events=''; +set global server_audit_query_log_limit= 15; +select (1), (2), (3), (4); +1 2 3 4 +1 2 3 4 +select 'A', 'B', 'C', 'D'; +A B C D +A B C D +set global server_audit_query_log_limit= 1024; +drop database sa_db; +set global server_audit_file_path='.'; +show status like 'server_audit_current_log'; +Variable_name Value +Server_audit_current_log HOME_DIR/server_audit.log +set global server_audit_file_path=''; +show status like 'server_audit_current_log'; +Variable_name Value +Server_audit_current_log server_audit.log +set global server_audit_file_path=' '; +show status like 'server_audit_current_log'; +Variable_name Value +Server_audit_current_log server_audit.log +set global server_audit_file_path='nonexisting_dir/'; +Warnings: +Warning 1 SERVER AUDIT plugin can't create file 'nonexisting_dir/'. +show status like 'server_audit_current_log'; +Variable_name Value +Server_audit_current_log server_audit.log +show variables like 'server_audit%'; +Variable_name Value +server_audit_events +server_audit_excl_users +server_audit_file_path +server_audit_file_rotate_now OFF +server_audit_file_rotate_size 1000000 +server_audit_file_rotations 9 +server_audit_incl_users odin, root, dva, tri +server_audit_logging ON +server_audit_mode 1 +server_audit_output_type file +server_audit_query_log_limit 1024 +server_audit_syslog_facility LOG_USER +server_audit_syslog_ident mysql-server_auditing +server_audit_syslog_info +server_audit_syslog_priority LOG_INFO +uninstall plugin server_audit; +Warnings: +Warning 1620 Plugin is busy and will be uninstalled on shutdown +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_logging=on',0 +TIME,HOSTNAME,root,localhost,ID,0,CONNECT,mysql,,0 +TIME,HOSTNAME,root,localhost,ID,0,DISCONNECT,mysql,,0 +TIME,HOSTNAME,no_such_user,localhost,ID,0,FAILED_CONNECT,,,ID +TIME,HOSTNAME,no_such_user,localhost,ID,0,DISCONNECT,,,0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_incl_users=\'odin, dva, tri\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_incl_users=\'odin, root, dva, tri\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,CREATE,test,t2, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'create table t2 (id int)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_excl_users=\'odin, dva, tri\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'SHOW WARNINGS',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,test,t1, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,table_stats, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,column_stats, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,index_stats, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'insert into t1 values (1), (2)',0 +TIME,HOSTNAME,root,localhost,ID,ID,READ,test,t1, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'select * from t1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_incl_users=\'odin, root, dva, tri\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,test,t2, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,table_stats, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,column_stats, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,index_stats, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'insert into t2 values (1), (2)',0 +TIME,HOSTNAME,root,localhost,ID,ID,READ,test,t2, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'select * from t2',0 +TIME,HOSTNAME,root,localhost,ID,ID,ALTER,test,t1, +TIME,HOSTNAME,root,localhost,ID,ID,RENAME,test,t1|test.renamed_t1, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,table_stats, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,column_stats, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,index_stats, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'alter table t1 rename renamed_t1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_events=\'connect,query\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'select 1,\n2,\n# comment\n3',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'insert into t2 values (1), (2)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'select * from t2',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'select * from t_doesnt_exist',ID +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'syntax_error_query',ID +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'drop table renamed_t1, t2',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'show variables like \'server_audit%\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_mode=1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'set global server_audit_events=\'\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'create database sa_db',0 +TIME,HOSTNAME,root,localhost,ID,0,CONNECT,test,,0 +TIME,HOSTNAME,root,localhost,ID,ID,CREATE,test,t1, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'create table t1 (id2 int)',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,test,t1, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,table_stats, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,column_stats, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,index_stats, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'insert into t1 values (1), (2)',0 +TIME,HOSTNAME,root,localhost,ID,ID,READ,test,t1, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'select * from t1',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,table_stats, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,column_stats, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,index_stats, +TIME,HOSTNAME,root,localhost,ID,ID,DROP,test,t1, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'drop table t1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'use sa_db',0 +TIME,HOSTNAME,root,localhost,ID,ID,CREATE,sa_db,sa_t1, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'create table sa_t1(id int)',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,sa_db,sa_t1, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,table_stats, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,column_stats, +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,index_stats, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'insert into sa_t1 values (1), (2)',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,table_stats, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,column_stats, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,index_stats, +TIME,HOSTNAME,root,localhost,ID,ID,DROP,sa_db,sa_t1, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'drop table sa_t1',0 +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,proc, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proc, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,event, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'drop database sa_db',0 +TIME,HOSTNAME,root,localhost,ID,0,DISCONNECT,,,0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,test,'create database sa_db',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'use sa_db',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,columns_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,procs_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,roles_mapping, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'CREATE USER u1 IDENTIFIED BY *****',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'GRANT ALL ON sa_db TO u2 IDENTIFIED BY *****',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'SET PASSWORD FOR u1 = PASSWORD(*****)',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,columns_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,procs_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,roles_mapping, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'CREATE USER u3 IDENTIFIED BY *****',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,db, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,tables_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,columns_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,procs_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proxies_priv, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,roles_mapping, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,global_priv, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'drop user u1, u2, u3',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'create table t1(id int)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'drop table t1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'create table t1(id int)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'insert into t1 values (1), (2)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'select * from t1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'select 2',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'drop table t1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'insert into t1 values (1), (2)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'select * from t1',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'select 2',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'(select 2)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'/*! select 2*/',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'/*comment*/ select 2',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'CREATE USER u1 IDENTIFIED BY *****',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'GRANT ALL ON sa_db TO u2 IDENTIFIED BY *****',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'SET PASSWORD\n# comment\nFOR u1 = PASSWORD(*****)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'SET PASSWORD FOR u1=<secret>',ID +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'CREATE USER u3 IDENTIFIED BY *****',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'drop user u1, u2, u3',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_events=\'\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global serv',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'select (1), (2)',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'select \'A\', ',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_query_log_limit= 1024',0 +TIME,HOSTNAME,root,localhost,ID,ID,READ,mysql,proc, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,proc, +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,event, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'drop database sa_db',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_file_path=\'.\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_file_path=\'.\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'show status like \'server_audit_current_log\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_file_path=\'\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_file_path=\'\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'show status like \'server_audit_current_log\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_file_path=\' \'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_file_path=\' \'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'show status like \'server_audit_current_log\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_file_path=\'nonexisting_dir/\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'set global server_audit_file_path=\'nonexisting_dir/\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'SHOW WARNINGS',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'show status like \'server_audit_current_log\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'show variables like \'server_audit%\'',0 +TIME,HOSTNAME,root,localhost,ID,ID,WRITE,mysql,plugin, +TIME,HOSTNAME,root,localhost,ID,ID,QUERY,sa_db,'uninstall plugin server_audit',0 diff --git a/mysql-test/suite/plugins/r/two_password_validations.result b/mysql-test/suite/plugins/r/two_password_validations.result new file mode 100644 index 00000000..dc6bab3c --- /dev/null +++ b/mysql-test/suite/plugins/r/two_password_validations.result @@ -0,0 +1,21 @@ +install soname "simple_password_check"; +grant select on *.* to Fff_fff1 identified by '1fff_ffF'; +drop user Fff_fff1; +install soname "cracklib_password_check"; +grant select on *.* to foobar identified by 'q$%^&*R1234ty'; +drop user foobar; +grant select on *.* to Fff_fff1 identified by '1fff_ffF'; +ERROR HY000: Your password does not satisfy the current policy requirements +show warnings; +Level Code Message +Warning 1819 cracklib: it does not contain enough DIFFERENT characters +Error 1819 Your password does not satisfy the current policy requirements +grant select on *.* to foobar identified by 'q-%^&*rty'; +ERROR HY000: Your password does not satisfy the current policy requirements +show warnings; +Level Code Message +Error 1819 Your password does not satisfy the current policy requirements +uninstall plugin simple_password_check; +grant select on *.* to foobar identified by 'q-%^&*rty'; +drop user foobar; +uninstall plugin cracklib_password_check; diff --git a/mysql-test/suite/plugins/r/unix_socket.result b/mysql-test/suite/plugins/r/unix_socket.result new file mode 100644 index 00000000..b663d341 --- /dev/null +++ b/mysql-test/suite/plugins/r/unix_socket.result @@ -0,0 +1,29 @@ +# +# with named user +# +create user 'USER' identified via unix_socket; +# +# name match = ok +# +select user(), current_user(), database(); +user() current_user() database() +USER@localhost USER@% test +# +# name does not match = failure +# +drop user 'USER'; +# +# and now with anonymous user +# +grant SELECT ON test.* TO '' identified via unix_socket; +# +# name match = ok +# +select user(), current_user(), database(); +user() current_user() database() +USER@localhost @% test +# +# name does not match = failure +# +delete from mysql.user where user=''; +FLUSH PRIVILEGES; diff --git a/mysql-test/suite/plugins/suite.pm b/mysql-test/suite/plugins/suite.pm new file mode 100644 index 00000000..359a5409 --- /dev/null +++ b/mysql-test/suite/plugins/suite.pm @@ -0,0 +1,33 @@ +package My::Suite::Plugins; + +use My::Platform; + +@ISA = qw(My::Suite); + +if (-d '../sql' && !&::using_extern()) { + my $src = "$::bindir/plugin/auth_pam/auth_pam_tool"; + my $dst = "$::plugindir/auth_pam_tool_dir/auth_pam_tool"; + ::mkpath( "$::plugindir/auth_pam_tool_dir"); + eval { symlink $src, $dst } or ::copy $src, $dst; +} + +sub cassandra_running() { + return 0 if IS_WINDOWS; + system 'echo show version | cqlsh -3 2>/dev/null >/dev/null'; + return $? == 0; +} + +sub skip_combinations { + my %skip; + $skip{'t/pam_init.inc'} = 'No pam setup for mtr' + unless -e '/etc/pam.d/mariadb_mtr'; + $skip{'t/pam_init.inc'} = 'Not run as user owning auth_pam_tool_dir' + unless -o $::plugindir . '/auth_pam_tool_dir'; + $skip{'t/cassandra.test'} = 'Cassandra is not running' + unless cassandra_running(); + $skip{'t/cassandra_qcache.test'} = $skip{'t/cassandra.test'}; + %skip; +} + +bless { }; + diff --git a/mysql-test/suite/plugins/t/audit_null.test b/mysql-test/suite/plugins/t/audit_null.test new file mode 100644 index 00000000..63f5f51e --- /dev/null +++ b/mysql-test/suite/plugins/t/audit_null.test @@ -0,0 +1,61 @@ + +--source include/not_embedded.inc + +if (!$ADT_NULL_SO) { + skip No NULL_AUDIT plugin; +} + +set @old_global_general_log=@@global.general_log; +set global general_log=OFF; + +create user testuser@localhost; +grant select on *.* to testuser@localhost; + +--disable_ps_protocol +install plugin audit_null soname 'adt_null'; + +select 1; +--error 1054 +select foobar; + +show status like 'audit_null%'; + +create procedure au1(x char(16)) select concat("test1", x); +call au1("-12"); + +show status like 'audit_null%'; + +create table t1 (a int); +insert t1 values (1), (2); +select * from t1; +rename table t1 to t2; +alter table t2 add column b int; + +create definer=testuser@localhost view v1 as select t2.a+1, t2_copy.a+2 from t2, t2 as t2_copy; +select * from v1; +drop view v1; + +# temp table generates no audit events +create temporary table t2 (a date); +insert t2 values ('2020-10-09'); +select * from t2; +alter table t2 add column b int; # MDEV-4565 ALTER on a temporary table generates an audit event +drop table t2; + +# internal temp table generates no audit events +explain select distinct * from t2; +select distinct * from t2; + +drop table t2; + +uninstall plugin audit_null; +--enable_ps_protocol + +drop procedure au1; +drop user testuser@localhost; +set global general_log=@old_global_general_log; + +let $MYSQLD_DATADIR= `SELECT @@datadir`; +--replace_regex /::1// /127.0.0.1// +cat_file $MYSQLD_DATADIR/audit_null_tables.log; + diff --git a/mysql-test/suite/plugins/t/audit_null_debug.test b/mysql-test/suite/plugins/t/audit_null_debug.test new file mode 100644 index 00000000..52c50f13 --- /dev/null +++ b/mysql-test/suite/plugins/t/audit_null_debug.test @@ -0,0 +1,36 @@ +--source include/have_debug.inc +--source include/not_embedded.inc + +if (!$ADT_NULL_SO) { + skip No NULL_AUDIT plugin; +} + +alter table mysql.plugin engine=myisam; + +set @old_dbug=@@debug_dbug; +call mtr.add_suppression("Index for table.*mysql.plugin.MYI"); +call mtr.add_suppression("Index for table 'plugin' is corrupt; try to repair it"); + +# +# MySQL BUG#14485479 - INSTALL AUDIT PLUGIN HANGS IF WE TRY TO DISABLE AND ENABLED DURING DDL OPERATION +# (a.k.a. audit event caused by the table access during audit plugin initialization) +# +SET debug_dbug='+d,myisam_pretend_crashed_table_on_usage'; +--replace_result \\ / +--error 126 +install plugin audit_null soname 'adt_null'; +SET debug_dbug=@old_dbug; + +install plugin audit_null soname 'adt_null'; +SET debug_dbug='+d,myisam_pretend_crashed_table_on_usage'; +--replace_result \\ / +--error 126 +uninstall plugin audit_null; +SET debug_dbug=@old_dbug; + +uninstall plugin audit_null; +--error ER_SP_DOES_NOT_EXIST +uninstall plugin audit_null; + +delete from mysql.plugin where name='audit_null'; +alter table mysql.plugin engine=aria; diff --git a/mysql-test/suite/plugins/t/auth_ed25519.test b/mysql-test/suite/plugins/t/auth_ed25519.test new file mode 100644 index 00000000..8e0bdd1d --- /dev/null +++ b/mysql-test/suite/plugins/t/auth_ed25519.test @@ -0,0 +1,57 @@ +# +# MDEV-12160 Modern alternative to the SHA1 authentication plugin +# +source include/not_embedded.inc; +if (!$AUTH_ED25519_SO) { + skip No auth_ed25519 plugin; +} + +replace_result dll so; +eval create function ed25519_password returns string soname "$AUTH_ED25519_SO"; +error ER_CANT_INITIALIZE_UDF; +select ed25519_password(); +error ER_CANT_INITIALIZE_UDF; +select ed25519_password(1); +error ER_CANT_INITIALIZE_UDF; +select ed25519_password("foo", "bar"); +error ER_CANT_INITIALIZE_UDF; +select ed25519_password("foo"); + +install soname 'auth_ed25519'; +select ed25519_password("foo"); +select ed25519_password("foobar"); +select ed25519_password("foo bar"); +select ed25519_password(NULL); + +replace_result dll so; +query_vertical select * from information_schema.plugins where plugin_name='ed25519'; +let $pwd=`select ed25519_password("secret")`; +eval create user test1@localhost identified via ed25519 using '$pwd'; +show grants for test1@localhost; +drop user test1@localhost; +--error ER_PASSWD_LENGTH +create user test1@localhost identified via ed25519 using 'foo'; +--error ER_PASSWD_LENGTH +create user test1@localhost identified via ed25519 using '>>>1234567890123456789012345678901234567890'; +create user test1@localhost identified via ed25519 using password('foo'); +show grants for test1@localhost; +select ed25519_password('foo'); +set password for test1@localhost = password('bar'); +show create user test1@localhost; +select ed25519_password('bar'); +eval set password for test1@localhost = '$pwd'; +show create user test1@localhost; + +replace_result $MASTER_MYPORT PORT $MASTER_MYSOCK SOCKET; +error ER_ACCESS_DENIED_ERROR; +connect con1, localhost, test1, public; +connect con1, localhost, test1, secret; +select current_user(); +disconnect con1; +connection default; + +drop user test1@localhost; +uninstall plugin ed25519; +error ER_CANT_INITIALIZE_UDF; +select ed25519_password("foo"); +drop function ed25519_password; diff --git a/mysql-test/suite/plugins/t/auth_v0100.test b/mysql-test/suite/plugins/t/auth_v0100.test new file mode 100644 index 00000000..5d0f6f40 --- /dev/null +++ b/mysql-test/suite/plugins/t/auth_v0100.test @@ -0,0 +1,28 @@ +--source include/not_embedded.inc + +if (!$AUTH_0X0100_SO) { + skip No auth_0x0100 plugin; +} + +install soname 'auth_0x0100'; + +select plugin_name, plugin_type_version from information_schema.plugins where +plugin_type='authentication' and plugin_library is not null order by plugin_name; + +create user tt identified via auth_0x0100; +create user zzzzzzzzzzzzzzzz; +grant select on test.* to zzzzzzzzzzzzzzzz; + +--replace_result $MASTER_MYSOCK MASTER_MYSOCK $MASTER_MYPORT MASTER_MYPORT +--error ER_ACCESS_DENIED_ERROR +connect (c0,localhost,tt); + +grant proxy on zzzzzzzzzzzzzzzz to tt; +connect (c1,localhost,tt); +connection c1; + +--query_vertical select user(), current_user(), @@external_user +connection default; +drop user tt; +drop user zzzzzzzzzzzzzzzz; +uninstall plugin auth_0x0100; diff --git a/mysql-test/suite/plugins/t/binlog-simple_plugin_check.test b/mysql-test/suite/plugins/t/binlog-simple_plugin_check.test new file mode 100644 index 00000000..773dafe8 --- /dev/null +++ b/mysql-test/suite/plugins/t/binlog-simple_plugin_check.test @@ -0,0 +1,31 @@ +--source include/not_embedded.inc +--source include/have_binlog_format_statement.inc + +if (!$SIMPLE_PASSWORD_CHECK_SO) { + skip No SIMPLE_PASSWORD_CHECK plugin; +} + +INSTALL SONAME "simple_password_check"; +SELECT PLUGIN_NAME FROM INFORMATION_SCHEMA.PLUGINS +WHERE PLUGIN_NAME='simple_password_check'; + + +--echo # +--echo # MDEV-14031 Password policy causes replication failure +--echo # + +--disable_query_log +RESET MASTER; # get rid of previous tests binlog +--enable_query_log + +CREATE USER user1@localhost IDENTIFIED BY 'BsG9#9.cem#!85'; + +--error ER_NOT_VALID_PASSWORD +CREATE USER user2@localhost IDENTIFIED BY 'bsg9#d.cem#!85'; + +DROP USER user1@localhost; + +--let $binlog_file = LAST +source include/show_binlog_events.inc; + +UNINSTALL PLUGIN simple_password_check; diff --git a/mysql-test/suite/plugins/t/cassandra.opt b/mysql-test/suite/plugins/t/cassandra.opt new file mode 100644 index 00000000..76639e7d --- /dev/null +++ b/mysql-test/suite/plugins/t/cassandra.opt @@ -0,0 +1 @@ +--plugin-load-add=$HA_CASSANDRA_SO --loose-cassandra=on diff --git a/mysql-test/suite/plugins/t/cassandra.test b/mysql-test/suite/plugins/t/cassandra.test new file mode 100644 index 00000000..b12de78e --- /dev/null +++ b/mysql-test/suite/plugins/t/cassandra.test @@ -0,0 +1,758 @@ +# +# Tests for cassandra storage engine +# +if (`SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.ENGINES WHERE engine = 'cassandra' AND support IN ('YES', 'DEFAULT', 'ENABLED')`) +{ + --skip Test requires Cassandra. +} + +--disable_warnings +drop table if exists t0, t1; +--enable_warnings + +--echo # +--echo # Check variables and status counters +--echo # +show status like 'cassandra%'; +show variables like 'cassandra%'; + +--echo # +--echo # Test various errors on table creation. +--echo # +--error ER_REQUIRES_PRIMARY_KEY +create table t1 (a int) engine=cassandra + thrift_host='localhost' keyspace='foo' column_family='colfam'; + +--error ER_CONNECT_TO_FOREIGN_DATA_SOURCE +create table t1 (a int primary key, b int) engine=cassandra + thrift_host='localhost' keyspace='foo' column_family='colfam'; + +--error ER_CONNECT_TO_FOREIGN_DATA_SOURCE +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 ER_CONNECT_TO_FOREIGN_DATA_SOURCE +create table t1 (rowkey char(10) primary key, column1 char(10)) engine=cassandra + thrift_host='localhost' keyspace='no_such_keyspace' column_family='colfam'; + +# No column family specified +--error ER_CONNECT_TO_FOREIGN_DATA_SOURCE +create table t1 (rowkey char(10) primary key, column1 char(10)) engine=cassandra + thrift_host='localhost' keyspace='no_such_keyspace'; + +############################################################################ +## Cassandra initialization +############################################################################ + +# Step 1: remove the keyspace that could be left over from the previous test +--remove_files_wildcard $MYSQLTEST_VARDIR cassandra_test_cleanup.cql +--write_file $MYSQLTEST_VARDIR/cassandra_test_cleanup.cql +drop keyspace mariadbtest2; +EOF +--error 0,1,2 +--system cqlsh -3 -f $MYSQLTEST_VARDIR/cassandra_test_cleanup.cql + +# Step 2: create new keyspace and test column families +--remove_files_wildcard $MYSQLTEST_VARDIR cassandra_test_init.cql +--write_file $MYSQLTEST_VARDIR/cassandra_test_init.cql + +CREATE KEYSPACE mariadbtest2 + WITH strategy_class = 'org.apache.cassandra.locator.SimpleStrategy' + AND strategy_options:replication_factor='1'; + +USE mariadbtest2; +create columnfamily cf1 ( pk varchar primary key, data1 varchar, data2 bigint); + +create columnfamily cf2 (rowkey bigint primary key, a bigint); + +create columnfamily cf3 (rowkey bigint primary key, intcol int); + +create columnfamily cf4 (rowkey bigint primary key, datecol timestamp); + +create columnfamily cf5 (rowkey bigint primary key, uuidcol uuid); + +create columnfamily cf6 (rowkey uuid primary key, col1 int); + +create columnfamily cf7 (rowkey int primary key, boolcol boolean); + +create columnfamily cf8 (rowkey varchar primary key, countercol counter); +update cf8 set countercol=countercol+1 where rowkey='cnt1'; +update cf8 set countercol=countercol+100 where rowkey='cnt2'; + +create columnfamily cf9 (rowkey varchar primary key, varint_col varint); +insert into cf9 (rowkey, varint_col) values ('val-01', 1); +insert into cf9 (rowkey, varint_col) values ('val-0x123456', 1193046); +insert into cf9 (rowkey, varint_col) values ('val-0x12345678', 305419896); + +create columnfamily cf11 (rowkey varchar primary key, decimal_col decimal); +insert into cf11 (rowkey, decimal_col) values ('val_0.5', 0.5); +insert into cf11 (rowkey, decimal_col) values ('val_1.5', 1.5); +insert into cf11 (rowkey, decimal_col) values ('val_1234', 1234); + +create columnfamily cf12 (rowkey varchar primary key, decimal_col decimal); + +create columnfamily cf13 (rowkey int primary key, b blob); + +create columnfamily cf14 ( rowkey int primary key, a int ); + +EOF +--error 0,1,2 +--system cqlsh -3 -f $MYSQLTEST_VARDIR/cassandra_test_init.cql + + +# Step 3: Cassandra's CQL doesn't allow certain kinds of queries. Run them in +# CLI +--remove_files_wildcard $MYSQLTEST_VARDIR cassandra_test_init.cli +--write_file $MYSQLTEST_VARDIR/cassandra_test_init.cli +use mariadbtest2; +CREATE COLUMN FAMILY cf10 + WITH comparator = UTF8Type + AND key_validation_class=UTF8Type + AND default_validation_class = UTF8Type; + +CREATE COLUMN FAMILY cfd1 + WITH comparator = UTF8Type + AND key_validation_class=UTF8Type + AND default_validation_class = UTF8Type; +SET cfd1['1']['very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_very_long_name']='1'; + +CREATE COLUMN FAMILY cfd2 + WITH comparator = UTF8Type + AND key_validation_class=Int32Type + AND default_validation_class = UTF8Type; + +EOF + +--error 0,1,2 +--system cassandra-cli -f $MYSQLTEST_VARDIR/cassandra_test_init.cli + +############################################################################ +## Cassandra initialization ends +############################################################################ + +--echo # Now, create a table for real and insert data +create table t1 (pk varchar(36) primary key, data1 varchar(60), data2 bigint) engine=cassandra + thrift_host='localhost' keyspace='mariadbtest2' column_family='cf1'; + +--echo # Just in case there were left-overs from previous: +delete from t1; +select * from t1; + +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; + +explain +select * from t1 where pk='rowkey11'; +select * from t1 where pk='rowkey11'; + +# Deletion functions weirdly: it sets all columns to NULL +# but when If I do this in cassandra-cli: +# +# del cf1[ascii('rowkey10')] +# +# Subsequent 'list cf1' command also gives +# +# RowKey: rowkey10 +# +# without any columns. +# +# CQL seems to simply ignore all "incomplete" records. + +delete from t1 where pk='rowkey11'; +select * from t1; + +delete from t1; +select * from t1; + +--echo # +--echo # A query with filesort (check that table_flags() & HA_REC_NOT_IN_SEQ, +--echo # also check ::rnd_pos() +--echo # +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; + +delete from t1; +drop table t1; + +--echo # +--echo # MDEV-476: Cassandra: Server crashes in calculate_key_len on DELETE with ORDER BY +--echo # +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; + +--echo # +--echo # Batched INSERT +--echo # +show variables like 'cassandra_insert_batch_size'; +show status like 'cassandra_row_insert%'; +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 (1,1),(2,2); +DELETE FROM t1 ORDER BY a LIMIT 1; + +DROP TABLE t1; +show status like 'cassandra_row_insert%'; + +--echo # FLUSH STATUS doesn't work for our variables, just like with InnoDB. +flush status; +show status like 'cassandra_row_insert%'; + +--echo # +--echo # Batched Key Access +--echo # + +--echo # Control variable (we are not yet able to make use of MRR's buffer) +show variables like 'cassandra_multi%'; + +--echo # MRR-related status variables: +show status like 'cassandra_multi%'; + +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; + +select * from t1 A, t1 B where B.rowkey=A.a; +show status like 'cassandra_multi%'; + +# The following INSERTs are really UPDATEs +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; +show status like 'cassandra_multi%'; + +delete from t1; +drop table t1; + +--echo # +--echo # MDEV-480: TRUNCATE TABLE on a Cassandra table does not remove rows +--echo # +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; +drop table t1; + +--echo # +--echo # MDEV-494, part #1: phantom row for big full-scan selects +--echo # +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; +select count(*) from t1 where a=12345; + +delete from t1; +drop table t1; +drop table t0; + +--echo # 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; + +--echo # +--echo # Try accessing column family w/o explicitly defined columns +--echo # +--error ER_INTERNAL_ERROR +CREATE TABLE t1 (my_primary_key varchar(10) PRIMARY KEY) ENGINE=CASSANDRA + thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf10'; + +CREATE TABLE t1 (rowkey varchar(10) PRIMARY KEY) ENGINE=CASSANDRA + thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf10'; + +DROP TABLE t1; + +--echo # +--echo # Timestamp datatype support +--echo # +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; +delete from t2; + +--echo # 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; +delete from t2; + +--echo # +--echo # (no MDEV#) Check that insert counters work correctly +--echo # +create table t0 (a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +let $start_inserts=`select variable_value from information_schema.SESSION_STATUS + where variable_name ='Cassandra_row_inserts'`; +let $start_insert_batches=`select variable_value from information_schema.SESSION_STATUS + where variable_name ='Cassandra_row_insert_batches'`; + +set cassandra_insert_batch_size=10; +insert into t2 select A.a+10*B.a, now() from t0 A, t0 B; + +--disable_query_log +eval select + (select variable_value - $start_inserts from information_schema.SESSION_STATUS + where variable_name ='Cassandra_row_inserts') + AS 'inserts', + (select variable_value - $start_insert_batches from information_schema.SESSION_STATUS + where variable_name ='Cassandra_row_insert_batches') + AS 'insert_batches'; +--enable_query_log + +let $start_inserts=`select variable_value from information_schema.SESSION_STATUS + where variable_name ='Cassandra_row_inserts'`; +let $start_insert_batches=`select variable_value from information_schema.SESSION_STATUS + where variable_name ='Cassandra_row_insert_batches'`; + +set cassandra_insert_batch_size=1; +insert into t2 select A.a+10*B.a+100, now() from t0 A, t0 B; + +--disable_query_log +eval select + (select variable_value - $start_inserts from information_schema.SESSION_STATUS + where variable_name ='Cassandra_row_inserts') + AS 'inserts', + (select variable_value - $start_insert_batches from information_schema.SESSION_STATUS + where variable_name ='Cassandra_row_insert_batches') + AS 'insert_batches'; +--enable_query_log + +delete from t2; +drop table t2; +drop table t0; + +--echo # +--echo # UUID datatype support +--echo # +#create columnfamily cf5 (rowkey bigint primary key, uuidcol uuid); +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'); + +--error ER_WARN_DATA_OUT_OF_RANGE +insert into t2 values(2,'not-an-uuid'); + +--error ER_WARN_DATA_OUT_OF_RANGE +insert into t2 values(3,'9b5658dc-f32f-11e1=94cd-f46d046e9f09'); + +--error ER_WARN_DATA_OUT_OF_RANGE +insert into t2 values(4,'9b5658dc-fzzf-11e1-94cd-f46d046e9f09'); + +--error ER_WARN_DATA_OUT_OF_RANGE +insert into t2 values + (5,'9b5658dc-f11f-11e1-94cd-f46d046e9f09'), + (6,'9b5658dc-f11f011e1-94cd-f46d046e9f09'); + +select * from t2; + +delete from t2; +drop table t2; + +# create columnfamily cf6 (rowkey uuid primary key, col1 int); +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); + +--error ER_WARN_DATA_OUT_OF_RANGE +insert into t2 values('not-an-uuid', 563); + +select * from t2; +delete from t2; +drop table t2; + + +--echo # +--echo # boolean datatype support +--echo # +# create columnfamily cf7 (rowkey int primary key, boolcol boolean); +CREATE TABLE t2 (rowkey int PRIMARY KEY, boolcol bool) ENGINE=CASSANDRA + thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf7'; +insert into t2 values (0, 0); +insert into t2 values (1, 1); +select * from t2; +delete from t2; +drop table t2; + + +--echo # +--echo # Counter datatype support (read-only) +--echo # +# create columnfamily cf8 (rowkey int primary key, countercol counter); +CREATE TABLE t2 (rowkey varchar(32) PRIMARY KEY, countercol bigint) ENGINE=CASSANDRA + thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf8'; +select * from t2; +drop table t2; + +--echo # +--echo # Check that @@cassandra_default_thrift_host works +--echo # +show variables like 'cassandra_default_thrift_host'; +set @tmp=@@cassandra_default_thrift_host; +--error ER_GLOBAL_VARIABLE +set cassandra_default_thrift_host='localhost'; +set global cassandra_default_thrift_host='localhost'; + +--echo # 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; +drop table t2; + +set global cassandra_default_thrift_host=@tmp; + +--echo # +--echo # Consistency settings +--echo # +show variables like 'cassandra_%consistency'; +set @tmp=@@cassandra_write_consistency; + +--echo # 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; + +--echo # +--echo # varint datatype support +--echo # +# create columnfamily cf9 (rowkey varchar primary key, varint_col varint); +CREATE TABLE t2 (rowkey varchar(32) PRIMARY KEY, varint_col varbinary(32)) ENGINE=CASSANDRA + thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf9'; +--sorted_result +select rowkey, hex(varint_col) from t2; +drop table t2; + +--echo # now, let's check what happens when MariaDB's column is not wide enough: +CREATE TABLE t2 (rowkey varchar(32) PRIMARY KEY, varint_col varbinary(2)) ENGINE=CASSANDRA + thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf9'; +--sorted_result +--error ER_INTERNAL_ERROR +select rowkey, hex(varint_col) from t2; +drop table t2; + +--echo # +--echo # Decimal datatype support +--echo # +CREATE TABLE t2 (rowkey varchar(32) PRIMARY KEY, decimal_col varbinary(32)) ENGINE=CASSANDRA + thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf11'; +select rowkey, hex(decimal_col) from t2; +drop table t2; + +--echo # +--echo # Mapping TIMESTAMP -> int64 +--echo # +set @save_tz= @@time_zone; +set time_zone='UTC'; +CREATE TABLE t2 (rowkey bigint PRIMARY KEY, datecol timestamp) ENGINE=CASSANDRA + thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf4'; +insert into t2 values (1, '2012-08-29 01:23:45'); +INSERT INTO t2 VALUES (10,'2012-08-29 01:23:46'); +drop table t2; + +CREATE TABLE t2 (rowkey bigint PRIMARY KEY, datecol bigint) ENGINE=CASSANDRA + thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf4'; +select * from t2; +delete from t2; +drop table t2; +set time_zone=@save_tz; + +--echo # +--echo # Check whether changing parameters with ALTER TABLE works. +--echo # +CREATE TABLE t2 (rowkey varchar(32) PRIMARY KEY, decimal_col varbinary(32)) ENGINE=CASSANDRA + thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf11'; + +#--error ER_INTERNAL_ERROR +#alter table t2 column_family='cf9'; + +drop table t2; + +CREATE TABLE t2 (rowkey varchar(32) PRIMARY KEY, decimal_col varbinary(32)) ENGINE=CASSANDRA + thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf11'; + +let $c1= `select variable_value from information_schema.global_status + where variable_name like 'cassandra_row_inserts'`; +alter table t2 column_family='cf12'; +let $c2= `select variable_value from information_schema.global_status + where variable_name like 'cassandra_row_inserts'`; + +--disable_query_log +eval select ($c2 - $c1) as 'Writes made during ALTER TABLE'; +--enable_query_log + +drop table t2; + +--echo # +--echo # UPDATE command support +--echo # +create table t1 (pk varchar(36) primary key, data1 varchar(60), data2 bigint) engine=cassandra + thrift_host='localhost' keyspace='mariadbtest2' column_family='cf1'; + +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; + +update t1 set data1='updated-1' where pk='rowkey11'; +select * from t1; +update t1 set pk='new-rowkey12' where pk='rowkey12'; +select * from t1; + +delete from t1; +drop table t1; + +--echo # +--echo # Dynamic columns support +--echo # +CREATE TABLE t2 (rowkey bigint PRIMARY KEY, uuidcol blob DYNAMIC_COLUMN_STORAGE=1) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf5'; +drop table t2; + +--echo #error: dynamic column is not a blob +--error ER_WRONG_FIELD_SPEC +CREATE TABLE t2 (rowkey bigint PRIMARY KEY, uuidcol char(36) DYNAMIC_COLUMN_STORAGE=1) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf5'; + +--echo #error: double dynamic column +--error ER_WRONG_FIELD_SPEC +CREATE TABLE t2 (rowkey bigint PRIMARY KEY, uuidcol blob DYNAMIC_COLUMN_STORAGE=1, textcol blob DYNAMIC_COLUMN_STORAGE=1) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf5'; + +--echo # +--echo # Dynamic column read +--echo # +#prepare data +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,'9b5658dc-f32f-11e1-94cd-f46d046e9f0a'); +drop table t2; + +#test dynamic column read +CREATE TABLE t2 (rowkey bigint PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf5'; +select rowkey, column_list(dyn), column_get(dyn, 'uuidcol' as char) from t2; +drop table t2; + +#cleanup data +CREATE TABLE t2 (rowkey bigint PRIMARY KEY, uuidcol char(36)) ENGINE=CASSANDRA + thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf5'; +delete from t2; +drop table t2; + +--echo # +--echo # Dynamic column insert +--echo # +CREATE TABLE t2 (rowkey bigint PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf5'; +insert into t2 values (1, column_create("dyn1", 1, "dyn2", "two")); +select rowkey, column_json(dyn) from t2; +delete from t2; +drop table t2; +--echo # bigint +CREATE TABLE t1 (rowkey bigint PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf2'; +insert into t1 values (1, column_create("dyn1", 1, "dyn2", "two", 'a', 254324)); +insert into t1 values (2, column_create("dyn1", 1, "dyn2", "two", 'a', 2543)); +select rowkey, column_json(dyn) from t1; +delete from t1; +drop table t1; +--echo # int +CREATE TABLE t1 (rowkey bigint PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf3'; +insert into t1 values (1, column_create("dyn1", 1, "dyn2", "two", 'intcol', 254324)); +insert into t1 values (2, column_create("dyn1", 1, "dyn2", "two", 'intcol', 2543)); +select rowkey, column_json(dyn) from t1; +delete from t1; +drop table t1; +--echo # timestamp +CREATE TABLE t1 (rowkey bigint PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf4'; +insert into t1 values (1, column_create("dyn1", 1, "dyn2", "two", 'datecol', 254324)); +insert into t1 values (2, column_create("dyn1", 1, "dyn2", "two", 'datecol', 2543)); +select rowkey, column_json(dyn) from t1; +delete from t1; +drop table t1; +--echo # boolean +CREATE TABLE t1 (rowkey int PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cf7'; +insert into t1 values (1, column_create("dyn1", 1, "dyn2", "two", 'boolcol', 254324)); +insert into t1 values (2, column_create("dyn1", 1, "dyn2", "two", 'boolcol', 0)); +select rowkey, column_json(dyn) from t1; +select rowkey, column_json(dyn) from t1; +update t1 set dyn=column_add(dyn, "dyn2", null, "dyn3", "3"); +select rowkey, column_json(dyn) from t1; +update t1 set dyn=column_add(dyn, "dyn1", null) where rowkey= 1; +select rowkey, column_json(dyn) from t1; +update t1 set dyn=column_add(dyn, "dyn3", null, "a", "ddd"); +select rowkey, column_json(dyn) from t1; +update t1 set dyn=column_add(dyn, "12345678901234", "ddd"); +select rowkey, column_json(dyn) from t1; +update t1 set dyn=column_add(dyn, "12345678901234", null); +select rowkey, column_json(dyn) from t1; +update t1 set dyn=column_add(dyn, 'boolcol', null) where rowkey= 2; +select rowkey, column_json(dyn) from t1; +update t1 set rowkey= 3, dyn=column_add(dyn, "dyn1", null, 'boolcol', 0) where rowkey= 2; +select rowkey, column_json(dyn) from t1; +delete from t1; +drop table t1; + +CREATE TABLE t1 (rowkey varchar(10) PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cfd1'; +--error ER_INTERNAL_ERROR +select * from t1; +drop table t1; + +# MDEV-560 +CREATE TABLE t1 (rowkey int PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) +ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cfd2'; +DELETE FROM t1; +insert into t1 values (1, column_create("dyn", 1)); +select rowkey, column_list(dyn) from t1; +# Cleanup +delete from t1; +DROP TABLE t1; + +# MDEV-561 (incorrect format data to dynamic column) +CREATE TABLE t1 (rowkey int PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) +ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cfd2'; +--error ER_DYN_COL_WRONG_FORMAT +insert into t1 values (1,'9b5658dc-f32f-11e1-94cd-f46d046e9f0a'); +delete from t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-565: Server crashes in ha_cassandra::write_row on +--echo # inserting NULL into a dynamic column +--echo # +CREATE TABLE t1 (rowkey int PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) +ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cfd2'; +insert into t1 values (1, NULL); +delete from t1; +DROP TABLE t1; + +--echo # +--echo # strange side effect of Cassandra - remiving all columns of primary +--echo # key removes all row. +--echo # +CREATE TABLE t1 (rowkey int PRIMARY KEY, dyn blob DYNAMIC_COLUMN_STORAGE=yes) +ENGINE=CASSANDRA thrift_host='localhost' keyspace='mariadbtest2' column_family = 'cfd2'; +INSERT INTO t1 VALUES(2,column_create("ab","ab")); +select rowkey, column_json(dyn) from t1; +UPDATE t1 set dyn=NULL; +select rowkey, column_json(dyn) from t1; +INSERT INTO t1 VALUES(2,column_create("ab","ab")); +select rowkey, column_json(dyn) from t1; +UPDATE t1 set dyn=""; +select rowkey, column_json(dyn) from t1; +delete from t1; +DROP TABLE t1; + +--echo # +--echo # MDEV-4005 #Server crashes on creating a Cassandra table +--echo # with a mix of static and dynamic columns +--echo # +--disable_warnings +DROP TABLE IF EXISTS t1, t2; +--enable_warnings + +--remove_files_wildcard $MYSQLTEST_VARDIR cassandra_test_cleanup.cql +--write_file $MYSQLTEST_VARDIR/cassandra_test_cleanup.cql +drop keyspace bug; +EOF +--error 0,1,2 +--system cqlsh -3 -f $MYSQLTEST_VARDIR/cassandra_test_cleanup.cql + +--remove_files_wildcard $MYSQLTEST_VARDIR cassandra_test_init.cql +--write_file $MYSQLTEST_VARDIR/cassandra_test_init.cql + +CREATE KEYSPACE bug + WITH strategy_class = 'org.apache.cassandra.locator.SimpleStrategy' + AND strategy_options:replication_factor='1'; + +USE bug; +create columnfamily cf1 ( pk int primary key, col_int int, a bigint ); +EOF + +--system cqlsh -3 -f $MYSQLTEST_VARDIR/cassandra_test_init.cql + + +CREATE TABLE t1 ( + pk int primary key, + col_int int, + dyncol blob DYNAMIC_COLUMN_STORAGE=yes +) ENGINE=cassandra keyspace='bug' thrift_host = '127.0.0.1' column_family='cf1'; + +drop table t1; + + +--echo # +--echo # MDEV-4000: Mapping between Cassandra blob (BytesType) and MySQL BLOB does not work +--echo # + +create table t1 (rowkey int primary key, b blob ) ENGINE=CASSANDRA thrift_host = '127.0.0.1' `keyspace`='mariadbtest2' `column_family`='cf13'; + +insert into t1 values (1, 'fooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo-bar'); +insert into t1 values (2, 'qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq-baz'); + +select * from t1; +drop table t1; + +--echo # +--echo # MDEV-4001: Cassandra: server crashes in ha_cassandra::end_bulk_insert on INSERT .. SELECT with a non-existing column +--echo # +create table t1 (rowkey int primary key, a int) ENGINE=cassandra thrift_host='127.0.0.1' keyspace='mariadbtest2' column_family='cf14'; +--error ER_BAD_FIELD_ERROR +insert into t1 (a) select b from t1; +drop table t1; + +############################################################################ +## Cassandra cleanup +############################################################################ +--disable_parsing +drop columnfamily cf1; +drop columnfamily cf2; +drop columnfamily cf3; +drop columnfamily cf4; +drop columnfamily cf5; +drop columnfamily cf6; +drop columnfamily cf7; + +drop columnfamily cf13; +drop columnfamily cf14; +--enable_parsing +############################################################################ +## Cassandra cleanup ends +############################################################################ + diff --git a/mysql-test/suite/plugins/t/cassandra_qcache.opt b/mysql-test/suite/plugins/t/cassandra_qcache.opt new file mode 100644 index 00000000..76639e7d --- /dev/null +++ b/mysql-test/suite/plugins/t/cassandra_qcache.opt @@ -0,0 +1 @@ +--plugin-load-add=$HA_CASSANDRA_SO --loose-cassandra=on diff --git a/mysql-test/suite/plugins/t/cassandra_qcache.test b/mysql-test/suite/plugins/t/cassandra_qcache.test new file mode 100644 index 00000000..5ccce461 --- /dev/null +++ b/mysql-test/suite/plugins/t/cassandra_qcache.test @@ -0,0 +1,59 @@ +# +# Tests for cassandra storage engine +# +-- source include/have_query_cache.inc + +if (`SELECT COUNT(*) = 0 FROM INFORMATION_SCHEMA.ENGINES WHERE engine = 'cassandra' AND support IN ('YES', 'DEFAULT', 'ENABLED')`) +{ + --skip Test requires Cassandra. +} + +--disable_warnings +drop table if exists t1, t2; +--enable_warnings + +############################################################################ +## Cassandra initialization +############################################################################ + +# Step 1: remove the keyspace that could be left over from the previous test +--remove_files_wildcard $MYSQLTEST_VARDIR cassandra_test_cleanup.cql +--write_file $MYSQLTEST_VARDIR/cassandra_test_cleanup.cql +drop keyspace mariadbtest2; +EOF +--error 0,1,2 +--system cqlsh -3 -f $MYSQLTEST_VARDIR/cassandra_test_cleanup.cql + +# Step 2: create new keyspace and test column families +--remove_files_wildcard $MYSQLTEST_VARDIR cassandra_test_init.cql +--write_file $MYSQLTEST_VARDIR/cassandra_test_init.cql + +CREATE KEYSPACE mariadbtest2 + WITH strategy_class = 'org.apache.cassandra.locator.SimpleStrategy' + AND strategy_options:replication_factor='1'; + +USE mariadbtest2; +create columnfamily cf1 ( rowkey int primary key, a int ); +EOF + +--error 0,1,2 +--system cqlsh -3 -f $MYSQLTEST_VARDIR/cassandra_test_init.cql + +############################################################################ +## The test itsef: +############################################################################ + +create table t1 (rowkey int primary key, a int) engine=cassandra + thrift_host='localhost' keyspace='mariadbtest2' column_family='cf1'; +create table t2 like t1; + +set global query_cache_size=1024*1024; + +select * from t1; +insert into t2 values (1,1); + +select * from t1; +select sql_no_cache * from t1; + +drop table t1,t2; +set global QUERY_CACHE_SIZE=0; diff --git a/mysql-test/suite/plugins/t/cassandra_reinstall.test b/mysql-test/suite/plugins/t/cassandra_reinstall.test new file mode 100644 index 00000000..95e4f7b6 --- /dev/null +++ b/mysql-test/suite/plugins/t/cassandra_reinstall.test @@ -0,0 +1,16 @@ +# +# MDEV-4403 Attempting to use cassandra storage engine causes "service 'my_snprintf_service' interface version mismatch" +# +if (!$HA_CASSANDRA_SO) { + skip No Cassandra engine; +} + +install soname 'ha_cassandra'; +select plugin_name,plugin_status,plugin_library from information_schema.plugins where plugin_name = 'cassandra'; +uninstall plugin cassandra; +select plugin_name,plugin_status,plugin_library from information_schema.plugins where plugin_name = 'cassandra'; +install soname 'ha_cassandra'; +select plugin_name,plugin_status,plugin_library from information_schema.plugins where plugin_name = 'cassandra'; +uninstall plugin cassandra; +select plugin_name,plugin_status,plugin_library from information_schema.plugins where plugin_name = 'cassandra'; + diff --git a/mysql-test/suite/plugins/t/cracklib_password_check.test b/mysql-test/suite/plugins/t/cracklib_password_check.test new file mode 100644 index 00000000..89b53b65 --- /dev/null +++ b/mysql-test/suite/plugins/t/cracklib_password_check.test @@ -0,0 +1,45 @@ +--source include/not_embedded.inc + +if (!$CRACKLIB_PASSWORD_CHECK_SO) { + skip No CRACKLIB_PASSWORD_CHECK plugin; +} + +install soname "cracklib_password_check"; + +--vertical_results +--replace_result .dll .so +select * from information_schema.plugins where plugin_name='cracklib_password_check'; +--horizontal_results + +--error ER_NOT_VALID_PASSWORD +grant select on *.* to foocar identified by 'foocar'; +show warnings; + +--error ER_NOT_VALID_PASSWORD +grant select on *.* to foocar identified by 'racoof'; +show warnings; + +--error ER_NOT_VALID_PASSWORD +grant select on *.* to foo@barbar identified by 'barbar'; +show warnings; + +--error ER_NOT_VALID_PASSWORD +grant select on *.* to foobar identified by 'qwerty'; +show warnings; + +grant select on *.* to foobar identified by 'q$%^&*rty'; +drop user foobar; + +--echo # +--echo # MDEV-9851: CREATE USER w/o IDENTIFIED BY clause causes crash +--echo # when using cracklib plugin +--echo # + +--error ER_NOT_VALID_PASSWORD +create user 'newuser'@'localhost'; + +uninstall plugin cracklib_password_check; + +create user foo1 identified by 'pwd'; +drop user foo1; + diff --git a/mysql-test/suite/plugins/t/dialog.test b/mysql-test/suite/plugins/t/dialog.test new file mode 100644 index 00000000..1dd52cfd --- /dev/null +++ b/mysql-test/suite/plugins/t/dialog.test @@ -0,0 +1,52 @@ +# +# test for the client "dialog" plugin +# + +--source include/not_embedded.inc + +if (!$DIALOG_SO) { + skip No dialog auth plugin; +} +if (!$DIALOG_EXAMPLES_SO) { + skip No dialog auth plugin; +} + +--replace_result .dll .so +eval install plugin three_attempts soname '$DIALOG_EXAMPLES_SO'; +create user test_dialog identified via three_attempts using 'SECRET'; + +--write_file $MYSQLTEST_VARDIR/tmp/dialog_good.txt +foo +1234 +SECRET +select user(), current_user(); +EOF + +--write_file $MYSQLTEST_VARDIR/tmp/dialog_bad.txt +foo +1234 +wrong +SECRET +EOF + +--echo # +--echo # -pSECRET is picked up, no questions asked. +--echo # +--exec echo "select user(), current_user();"|$MYSQL_TEST -u test_dialog -pSECRET + +--echo # +--echo # without -p. up to three questions are asked on the stdin. +--echo # athentication is successful, the correct pasword is on the third line +--echo # +--exec $MYSQL_TEST -u test_dialog < $MYSQLTEST_VARDIR/tmp/dialog_good.txt + +--echo # +--echo # athentication is unsuccessful, first three lines are all wrong +--echo # +--error 1 +--exec $MYSQL_TEST -u test_dialog < $MYSQLTEST_VARDIR/tmp/dialog_bad.txt + +--remove_file $MYSQLTEST_VARDIR/tmp/dialog_good.txt +--remove_file $MYSQLTEST_VARDIR/tmp/dialog_bad.txt +drop user test_dialog; +uninstall plugin three_attempts; diff --git a/mysql-test/suite/plugins/t/false_dupes-6543.test b/mysql-test/suite/plugins/t/false_dupes-6543.test new file mode 100644 index 00000000..ebdbe00e --- /dev/null +++ b/mysql-test/suite/plugins/t/false_dupes-6543.test @@ -0,0 +1,18 @@ +# +# MDEV-6543 Crash if enable 'federatedx' when 'federated' plugin already enabled, and vice-versa +# +if(!$HA_FEDERATED_SO) { + skip Needs ha_federated.so; +} +if(!$HA_FEDERATEDX_SO) { + skip Needs ha_federatedx.so; +} + +install soname 'ha_federated'; +# note: no error below! install soname ignores already loaded plugins +install soname 'ha_federated'; +# note: an error here, even though plugin name is the same! +--error ER_UDF_EXISTS +install soname 'ha_federatedx'; +uninstall soname 'ha_federated'; + diff --git a/mysql-test/suite/plugins/t/feedback_plugin_install.opt b/mysql-test/suite/plugins/t/feedback_plugin_install.opt new file mode 100644 index 00000000..a711ae94 --- /dev/null +++ b/mysql-test/suite/plugins/t/feedback_plugin_install.opt @@ -0,0 +1 @@ +--loose-feedback diff --git a/mysql-test/suite/plugins/t/feedback_plugin_install.test b/mysql-test/suite/plugins/t/feedback_plugin_install.test new file mode 100644 index 00000000..559dcebf --- /dev/null +++ b/mysql-test/suite/plugins/t/feedback_plugin_install.test @@ -0,0 +1,17 @@ +--source include/not_embedded.inc + +if (`select length('$FEEDBACK_SO') = 0`) { + skip No feedback plugin; +} + +--replace_regex /\.dll/.so/ +eval install plugin feedback soname '$FEEDBACK_SO'; +select plugin_status from information_schema.plugins where plugin_name='feedback'; +--replace_result https http +--sorted_result +select * from information_schema.feedback where variable_name like 'feed%' + and variable_name not like '%_uid' + and variable_name not like '%debug%'; + +uninstall plugin feedback; + diff --git a/mysql-test/suite/plugins/t/feedback_plugin_load.opt b/mysql-test/suite/plugins/t/feedback_plugin_load.opt new file mode 100644 index 00000000..6bf7594c --- /dev/null +++ b/mysql-test/suite/plugins/t/feedback_plugin_load.opt @@ -0,0 +1,2 @@ +--loose-feedback +--plugin-load-add=$FEEDBACK_SO diff --git a/mysql-test/suite/plugins/t/feedback_plugin_load.test b/mysql-test/suite/plugins/t/feedback_plugin_load.test new file mode 100644 index 00000000..ecf678eb --- /dev/null +++ b/mysql-test/suite/plugins/t/feedback_plugin_load.test @@ -0,0 +1,44 @@ +if (`select count(*) = 0 from information_schema.plugins where plugin_name = 'feedback' and plugin_status='active'`) +{ + --skip Feedback plugin is not active +} +if (`select @@feedback_url = ""`) +{ + --skip Feedback plugin is not active +} + +select plugin_status from information_schema.plugins where plugin_name='feedback'; + +# Every SELECT from INFORMATION_SCHEMA.FEEDBACK increases the value of 'FEEDBACK used'. +# We cannot record the actual value, because the test can be executed more than once, +# but we can check that the value indeed increases as expected. +# There is still a room for some race condition, e.g. if at the very moment +# between first SELECT to store the value and the next SELECT to check that it increases, +# the feedback plugin is activated. But the probability of it is close to 0, +# so lets get back to it if it ever happens. + +# Lets say the plugin was used X times before this SELECT +SELECT variable_value INTO @feedback_used FROM information_schema.feedback where variable_name = 'FEEDBACK used'; + +# Now $feedback_used == X+1, and 'FEEDBACK used' is also X+1. And variable_value is increased again when we run the next SELECT +SELECT variable_value = @feedback_used + 1 as 'MUST BE 1' FROM information_schema.feedback where variable_name = 'FEEDBACK used'; + +# Now when we are happy with 'FEEDBACK used', we can check everything else + +--replace_result https http +--sorted_result +select * from information_schema.feedback where variable_name like 'feed%' + and variable_name not like '%_uid' and variable_name not like 'FEEDBACK used' + and variable_name not like '%debug%'; + +# Embedded server does not use the table mysqld.user and thus +# does not automatically use utf8mb4 on startup. Use it manually. +--disable_query_log +if (`SELECT VERSION() LIKE '%embedded%'`) +{ + create temporary table t1 (a json); +} +--enable_query_log +SELECT VARIABLE_VALUE>0, VARIABLE_NAME FROM INFORMATION_SCHEMA.FEEDBACK +WHERE VARIABLE_NAME LIKE 'Collation used %' +ORDER BY VARIABLE_NAME; diff --git a/mysql-test/suite/plugins/t/feedback_plugin_send.test b/mysql-test/suite/plugins/t/feedback_plugin_send.test new file mode 100644 index 00000000..b28f9d4c --- /dev/null +++ b/mysql-test/suite/plugins/t/feedback_plugin_send.test @@ -0,0 +1,43 @@ +# Restart of server does not work for embedded. +--source include/not_embedded.inc + +source feedback_plugin_load.test; + +if (!$MTR_FEEDBACK_PLUGIN) { + skip MTR_FEEDBACK_PLUGIN is not set; +} + +# +# Yep. The plugin waits 5 minutes before sending anything, +# and there's no way to force it to send anything sooner. +# Let's wait, and hope that mtr is started with --parallel and +# is doing some work in other workers. +# + +sleep 100; +set global sql_mode=ONLY_FULL_GROUP_BY; +sleep 210; + + +# The test expects that the plugin will send a report at least 2 times, +# now (5 min after loading) and on server shutdown which happens below. +# Since we have already waited for 5 min, let's be generous +# and make sure the server has enough time to shut down properly. +# We won't lose anything if the shutdown is fast, but if it's slow, the plugin +# will still be able to finish the job and write about it in the error log. + +--let $shutdown_timeout= 60 +source include/restart_mysqld.inc; + +replace_result https http 2 6; +perl; + $log_error= $ENV{'MYSQLTEST_VARDIR'} . '/log/mysqld.1.err'; + open(LOG, '<', $log_error) or die "open(< $log_error): $!"; + + %logg=(); + while ($_=<LOG>) { + $logg{$&}++ if /feedback plugin:.*/; + } + print "$logg{$_}: $_\n" for sort keys %logg; + close LOG; +EOF diff --git a/mysql-test/suite/plugins/t/fulltext_plugin.test b/mysql-test/suite/plugins/t/fulltext_plugin.test new file mode 100644 index 00000000..e9b4343e --- /dev/null +++ b/mysql-test/suite/plugins/t/fulltext_plugin.test @@ -0,0 +1,17 @@ +--source include/have_simple_parser.inc + +# +# BUG#39746 - Debug flag breaks struct definition (server crash) +# +--replace_result .dll .so +eval INSTALL PLUGIN simple_parser SONAME '$MYPLUGLIB_SO'; +CREATE TABLE t1(a TEXT, b TEXT, FULLTEXT(a) WITH PARSER simple_parser); +ALTER TABLE t1 ADD FULLTEXT(b) WITH PARSER simple_parser; +DROP TABLE t1; +UNINSTALL PLUGIN simple_parser; + +# +# Bug #69682 - mysqld crashes after uninstall of plugin with "first" status var +# +show status like 'a%status'; + diff --git a/mysql-test/suite/plugins/t/locales.opt b/mysql-test/suite/plugins/t/locales.opt new file mode 100644 index 00000000..21f53ca3 --- /dev/null +++ b/mysql-test/suite/plugins/t/locales.opt @@ -0,0 +1,3 @@ +--loose-locale +--plugin-load-add=$LOCALES_SO + diff --git a/mysql-test/suite/plugins/t/locales.test b/mysql-test/suite/plugins/t/locales.test new file mode 100644 index 00000000..a3afe750 --- /dev/null +++ b/mysql-test/suite/plugins/t/locales.test @@ -0,0 +1,13 @@ +if (`select count(*) = 0 from information_schema.plugins where plugin_name = 'locales' and plugin_status='active'`) +{ + --skip LOCALES plugin is not active +} + +select * from information_schema.locales; +show locales; +--error ER_PARSE_ERROR +show locales like '%spanish%'; +show locales where description like '%spanish%'; +--error ER_PARSE_ERROR +flush locales; + diff --git a/mysql-test/suite/plugins/t/max_password_errors_auth_named_pipe.opt b/mysql-test/suite/plugins/t/max_password_errors_auth_named_pipe.opt new file mode 100644 index 00000000..52bf94f3 --- /dev/null +++ b/mysql-test/suite/plugins/t/max_password_errors_auth_named_pipe.opt @@ -0,0 +1 @@ +--loose-enable-named-pipe --plugin-load=$AUTH_NAMED_PIPE_SO diff --git a/mysql-test/suite/plugins/t/max_password_errors_auth_named_pipe.test b/mysql-test/suite/plugins/t/max_password_errors_auth_named_pipe.test new file mode 100644 index 00000000..79aeb7d3 --- /dev/null +++ b/mysql-test/suite/plugins/t/max_password_errors_auth_named_pipe.test @@ -0,0 +1,22 @@ +# Tests that max_password_errors has no effect on login errors with +# passwordless plugins (Windows version / auth_named_pipe) + +--source include/not_embedded.inc +--source include/have_auth_named_pipe.inc +if (`SELECT '$USERNAME' = 'nosuchuser'`) { + skip skipped for nosuchuser; +} +set @old_max_password_errors=@@max_password_errors; +create user nosuchuser identified with 'named_pipe'; + +set global max_password_errors=1; +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +error ER_ACCESS_DENIED_NO_PASSWORD_ERROR; +connect(pipe_con,localhost,nosuchuser,,,,,PIPE); +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +error ER_ACCESS_DENIED_NO_PASSWORD_ERROR; +connect(pipe_con,localhost,nosuchuser,,,,,PIPE); + +DROP USER nosuchuser; +FLUSH PRIVILEGES; +set global max_password_errors=@old_max_password_errors; diff --git a/mysql-test/suite/plugins/t/max_password_errors_auth_socket.opt b/mysql-test/suite/plugins/t/max_password_errors_auth_socket.opt new file mode 100644 index 00000000..91bb73e3 --- /dev/null +++ b/mysql-test/suite/plugins/t/max_password_errors_auth_socket.opt @@ -0,0 +1 @@ +--loose-enable-named-pipe --plugin-load=$AUTH_SOCKET_SO diff --git a/mysql-test/suite/plugins/t/max_password_errors_auth_socket.test b/mysql-test/suite/plugins/t/max_password_errors_auth_socket.test new file mode 100644 index 00000000..495a68a0 --- /dev/null +++ b/mysql-test/suite/plugins/t/max_password_errors_auth_socket.test @@ -0,0 +1,23 @@ +# Tests that max_password_errors has no effect on login errors with +# passwordless plugins (Unix version / auth_unix_socket) + +--source include/not_embedded.inc +--source include/have_unix_socket.inc + +if (`SELECT '$USER' = 'nosuchuser'`) { + skip USER is nosuchuser; +} +set @old_max_password_errors=@@max_password_errors; +create user nosuchuser identified with 'unix_socket'; + +set global max_password_errors=1; +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +error ER_ACCESS_DENIED_NO_PASSWORD_ERROR; +connect(pipe_con,localhost,nosuchuser); +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +error ER_ACCESS_DENIED_NO_PASSWORD_ERROR; +connect(pipe_con,localhost,nosuchuser); + +DROP USER nosuchuser; +FLUSH PRIVILEGES; +set global max_password_errors=@old_max_password_errors; diff --git a/mysql-test/suite/plugins/t/multiauth.test b/mysql-test/suite/plugins/t/multiauth.test new file mode 100644 index 00000000..f0eea21e --- /dev/null +++ b/mysql-test/suite/plugins/t/multiauth.test @@ -0,0 +1,214 @@ +--source include/not_ubsan.inc +--source include/platform.inc + +let $REGEX_VERSION_ID=/$mysql_get_server_version/VERSION_ID/; +let $REGEX_PASSWORD_LAST_CHANGED=/password_last_changed": [0-9]*/password_last_changed": #/; +let $REGEX_GLOBAL_PRIV=$REGEX_PASSWORD_LAST_CHANGED $REGEX_VERSION_ID; + +# +# MDEV-11340 Allow multiple alternative authentication methods for the same user +# +--source include/have_unix_socket.inc +if (`SELECT '$USER' = 'mysqltest1'`) { + skip USER is mysqltest1; +} +if (!$AUTH_ED25519_SO) { + skip No auth_ed25519 plugin; +} + +--let $plugindir=`SELECT @@global.plugin_dir` +install soname 'auth_ed25519'; + +--let $try_auth=$MYSQL_TEST < $MYSQLTEST_VARDIR/tmp/peercred_test.txt 2>&1 + +--write_file $MYSQLTEST_VARDIR/tmp/peercred_test.txt +--let $replace1=$USER@localhost +--let $replace2=$USER@% +--replace_result $replace1 "USER@localhost" $replace2 "USER@%" +select user(), current_user(), database(); +EOF + +--let $creplace=create user '$USER' +--let $dreplace=drop user '$USER' + +# +# socket,password +# +--replace_result $creplace "create user 'USER'" +eval $creplace identified via unix_socket OR mysql_native_password as password("GOOD"); +create user mysqltest1 identified via unix_socket OR mysql_native_password as password("good"); +show create user mysqltest1; +--echo # name match = ok +--exec $try_auth -u $USER +--echo # name does not match, password good = ok +--exec $try_auth -u mysqltest1 -pgood +--echo # name does not match, password bad = failure +--error 1 +--exec $try_auth -u mysqltest1 -pbad +--replace_result $dreplace "drop user 'USER'" +eval $dreplace, mysqltest1; + +# +# password,socket +# +--replace_result $creplace "create user 'USER'" +eval $creplace identified via mysql_native_password as password("GOOD") OR unix_socket; +create user mysqltest1 identified via mysql_native_password as password("good") OR unix_socket; +show create user mysqltest1; +--echo # name match = ok +--exec $try_auth -u $USER +--echo # name does not match, password good = ok +--exec $try_auth -u mysqltest1 -pgood +--echo # name does not match, password bad = failure +--error 1 +--exec $try_auth -u mysqltest1 -pbad +--replace_result $dreplace "drop user 'USER'" +eval $dreplace, mysqltest1; + +# +# socket,ed25519 +# +--replace_result $creplace "create user 'USER'" +eval $creplace identified via unix_socket OR ed25519 as password("GOOD"); +create user mysqltest1 identified via unix_socket OR ed25519 as password("good"); +show create user mysqltest1; +--echo # name match = ok +--exec $try_auth -u $USER +--echo # name does not match, password good = ok +--exec $try_auth -u mysqltest1 -pgood +--echo # name does not match, password bad = failure +--error 1 +--exec $try_auth -u mysqltest1 -pbad +--replace_result $dreplace "drop user 'USER'" +eval $dreplace, mysqltest1; + +# +# ed25519,socket +# +--replace_result $creplace "create user 'USER'" +eval $creplace identified via ed25519 as password("GOOD") OR unix_socket; +create user mysqltest1 identified via ed25519 as password("good") OR unix_socket; +show create user mysqltest1; +--echo # name match = ok +--exec $try_auth -u $USER +--echo # name does not match, password good = ok +--exec $try_auth -u mysqltest1 -pgood +--echo # name does not match, password bad = failure +--error 1 +--exec $try_auth -u mysqltest1 -pbad +--replace_result $dreplace "drop user 'USER'" +eval $dreplace, mysqltest1; + +# +# ed25519,socket,password +# +--replace_result $creplace "create user 'USER'" +eval $creplace identified via ed25519 as password("GOOD") OR unix_socket OR mysql_native_password as password("works"); +create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works"); +show create user mysqltest1; +--echo # name match = ok +--exec $try_auth -u $USER +--echo # name does not match, password good = ok +--exec $try_auth -u mysqltest1 -pgood +--echo # name does not match, second password works = ok +--exec $try_auth -u mysqltest1 -pworks +--echo # name does not match, password bad = failure +--error 1 +--exec $try_auth -u mysqltest1 -pbad +--replace_result $dreplace "drop user 'USER'" +eval $dreplace, mysqltest1; + +# +# password,password +# +create user mysqltest1 identified via mysql_native_password as password("good") OR mysql_native_password as password("works"); +show create user mysqltest1; +--echo # password good = ok +--exec $try_auth -u mysqltest1 -pgood +--echo # second password works = ok +--exec $try_auth -u mysqltest1 -pworks +--echo # password bad = failure +--error 1 +--exec $try_auth -u mysqltest1 -pbad +drop user mysqltest1; + +# +# show grants, flush privileges, set password, alter user +# +create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works"); +show grants for mysqltest1; +--replace_regex $REGEX_GLOBAL_PRIV +select json_detailed(priv) from mysql.global_priv where user='mysqltest1'; +select password,plugin,authentication_string from mysql.user where user='mysqltest1'; +flush privileges; +show create user mysqltest1; +set password for mysqltest1 = password('foobar'); +show create user mysqltest1; +alter user mysqltest1 identified via unix_socket OR mysql_native_password as password("some"); +show create user mysqltest1; +set password for mysqltest1 = password('foobar'); +show create user mysqltest1; +alter user mysqltest1 identified via unix_socket; +--error ER_SET_PASSWORD_AUTH_PLUGIN +set password for mysqltest1 = password('bla'); +alter user mysqltest1 identified via mysql_native_password as password("some") or unix_socket; +show create user mysqltest1; +drop user mysqltest1; + +--source include/switch_to_mysql_user.inc +--replace_regex /\d{6}/XX.YY.ZZ/ +--error ER_COL_COUNT_DOESNT_MATCH_PLEASE_UPDATE +create user mysqltest1 identified via ed25519 as password("good") OR unix_socket OR mysql_native_password as password("works"); +--source include/switch_to_mysql_global_priv.inc + +# +# invalid password,socket +# +--replace_result $creplace "create user 'USER'" +eval $creplace identified via mysql_native_password as '1234567890123456789012345678901234567890a' OR unix_socket; +create user mysqltest1 identified via mysql_native_password as '1234567890123456789012345678901234567890a' OR unix_socket; +update mysql.global_priv set priv=replace(priv, '1234567890123456789012345678901234567890a', 'invalid password'); +flush privileges; +show create user mysqltest1; +--echo # name match = ok +--exec $try_auth -u $USER +--echo # name does not match = failure +--error 1 +--exec $try_auth -u mysqltest1 +--echo # SET PASSWORD helps +set password for mysqltest1 = password('bla'); +--exec $try_auth -u mysqltest1 -pbla +--replace_result $dreplace "drop user 'USER'" +eval $dreplace, mysqltest1; + +# +# missing client-side plugin +# +create user mysqltest1 identified via ed25519 as password("good"); +show create user mysqltest1; +--echo # no plugin = failure +--replace_result $plugindir <PLUGINDIR> +--error 1 +--exec $try_auth -u mysqltest1 -pgood --plugin-dir=$plugindir/no +alter user mysqltest1 identified via ed25519 as password("good") OR mysql_native_password as password("works"); +show create user mysqltest1; +--echo # no plugin = failure +--error 1 +--exec $try_auth -u mysqltest1 -pgood --plugin-dir=$plugindir/no +--echo # no plugin, second password works = ok +--exec $try_auth -u mysqltest1 -pworks --plugin-dir=$plugindir/no +drop user mysqltest1; + +uninstall soname 'auth_ed25519'; +--remove_file $MYSQLTEST_VARDIR/tmp/peercred_test.txt + +# +# MDEV-21928 ALTER USER doesn't remove excess authentication plugins from mysql.global_priv +# +create user mysqltest1 identified via mysql_native_password as password("good") OR unix_socket; +show create user mysqltest1; +alter user mysqltest1 identified via mysql_native_password as password("better"); +show create user mysqltest1; +flush privileges; +show create user mysqltest1; +drop user mysqltest1; diff --git a/mysql-test/suite/plugins/t/pam.test b/mysql-test/suite/plugins/t/pam.test new file mode 100644 index 00000000..2611f25e --- /dev/null +++ b/mysql-test/suite/plugins/t/pam.test @@ -0,0 +1,81 @@ +let $PAM_PLUGIN_VERSION= $AUTH_PAM_SO; +--source pam_init.inc + +--write_file $MYSQLTEST_VARDIR/tmp/pam_good.txt +not very secret challenge +9225 +select user(), current_user(), database(); +EOF + +--write_file $MYSQLTEST_VARDIR/tmp/pam_bad.txt +not very secret challenge +9224 +select user(), current_user(), database(); +EOF + +--write_file $MYSQLTEST_VARDIR/tmp/pam_ugly.txt +crash pam module +616 +select user(), current_user(), database(); +EOF + +--write_file $MYSQLTEST_VARDIR/tmp/pam_good2.txt +9212 +select user(), current_user(), database(); +EOF + +--echo # +--echo # athentication is successful, challenge/pin are ok +--echo # note that current_user() differs from user() +--echo # +--exec $MYSQL_TEST -u test_pam < $MYSQLTEST_VARDIR/tmp/pam_good.txt + +--echo # +--echo # athentication is unsuccessful +--echo # +--error 1 +--exec $MYSQL_TEST -u test_pam < $MYSQLTEST_VARDIR/tmp/pam_bad.txt + +--echo # +--echo # athentication is unsuccessful +--echo # +--error 1 +--exec $MYSQL_TEST -u test_pam < $MYSQLTEST_VARDIR/tmp/pam_ugly.txt + +--echo # +--echo # athentication is successful +--echo # +--error 0 +--exec $MYSQL_TEST -u test_pam -pgoodpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt + +--echo # +--echo # athentication is unsuccessful +--echo # +--error 1 +--exec $MYSQL_TEST -u test_pam -pbadpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt + +drop user test_pam; +drop user pam_test; +create user PAM_TEST identified via pam using 'mariadb_mtr'; + +--echo # +--echo # athentication is unsuccessful +--echo # +--error 1 +--exec $MYSQL_TEST -u PAM_TEST < $MYSQLTEST_VARDIR/tmp/pam_good.txt + +set global pam_winbind_workaround=1; +--echo # +--echo # athentication is successful +--echo # +--exec $MYSQL_TEST -u PAM_TEST < $MYSQLTEST_VARDIR/tmp/pam_good.txt + +--remove_file $MYSQLTEST_VARDIR/tmp/pam_good.txt +--remove_file $MYSQLTEST_VARDIR/tmp/pam_good2.txt +--remove_file $MYSQLTEST_VARDIR/tmp/pam_bad.txt +--remove_file $MYSQLTEST_VARDIR/tmp/pam_ugly.txt +drop user PAM_TEST; + +let $count_sessions= 1; +--source include/wait_until_count_sessions.inc +uninstall plugin pam; diff --git a/mysql-test/suite/plugins/t/pam_cleartext.opt b/mysql-test/suite/plugins/t/pam_cleartext.opt new file mode 100644 index 00000000..aa270885 --- /dev/null +++ b/mysql-test/suite/plugins/t/pam_cleartext.opt @@ -0,0 +1 @@ +--loose-pam-use-cleartext-plugin diff --git a/mysql-test/suite/plugins/t/pam_cleartext.test b/mysql-test/suite/plugins/t/pam_cleartext.test new file mode 100644 index 00000000..0824ee8f --- /dev/null +++ b/mysql-test/suite/plugins/t/pam_cleartext.test @@ -0,0 +1,37 @@ +let $PAM_PLUGIN_VERSION= $AUTH_PAM_SO; +--source pam_init.inc + +show variables like 'pam_use_%'; + +--write_file $MYSQLTEST_VARDIR/tmp/pam_good.txt +not very secret challenge +9225 +select user(), current_user(), database(); +EOF + +--echo # +--echo # same test as in pam.test now fails +--echo # +--error 1 +--exec $MYSQL_TEST -u test_pam < $MYSQLTEST_VARDIR/tmp/pam_good.txt +--error 1 +--exec $MYSQL_TEST -u test_pam -p'something' < $MYSQLTEST_VARDIR/tmp/pam_good.txt + +--remove_file $MYSQLTEST_VARDIR/tmp/pam_good.txt + +--echo # +--echo # success +--echo # +--exec $MYSQL -u test_pam -p'cleartext good' -e 'select user(), current_user(), database()' + +--echo # +--echo # failure +--echo # +--error 1 +--exec $MYSQL -u test_pam -p'cleartext bad' -e 'select user(), current_user(), database()' + +drop user test_pam; +drop user pam_test; +let $count_sessions= 1; +--source include/wait_until_count_sessions.inc +uninstall plugin pam; diff --git a/mysql-test/suite/plugins/t/pam_init.inc b/mysql-test/suite/plugins/t/pam_init.inc new file mode 100644 index 00000000..131b787f --- /dev/null +++ b/mysql-test/suite/plugins/t/pam_init.inc @@ -0,0 +1,14 @@ + +--source include/not_embedded.inc + +if (!$PAM_PLUGIN_VERSION) { + skip No pam auth plugin; +} + +eval install plugin pam soname '$PAM_PLUGIN_VERSION'; +create user test_pam identified via pam using 'mariadb_mtr'; +create user pam_test; +grant proxy on pam_test to test_pam; + +let $plugindir=`SELECT @@global.plugin_dir`; + diff --git a/mysql-test/suite/plugins/t/pam_v1.test b/mysql-test/suite/plugins/t/pam_v1.test new file mode 100644 index 00000000..75425116 --- /dev/null +++ b/mysql-test/suite/plugins/t/pam_v1.test @@ -0,0 +1,52 @@ +let $PAM_PLUGIN_VERSION= $AUTH_PAM_V1_SO; +--source pam_init.inc + +--write_file $MYSQLTEST_VARDIR/tmp/pam_good.txt +not very secret challenge +9225 +select user(), current_user(), database(); +EOF + +--write_file $MYSQLTEST_VARDIR/tmp/pam_bad.txt +not very secret challenge +9224 +select user(), current_user(), database(); +EOF + +--write_file $MYSQLTEST_VARDIR/tmp/pam_good2.txt +9212 +select user(), current_user(), database(); +EOF + +--echo # +--echo # athentication is successful, challenge/pin are ok +--echo # note that current_user() differs from user() +--echo # +--exec $MYSQL_TEST -u test_pam < $MYSQLTEST_VARDIR/tmp/pam_good.txt + +--echo # +--echo # athentication is unsuccessful +--echo # +--error 1 +--exec $MYSQL_TEST -u test_pam < $MYSQLTEST_VARDIR/tmp/pam_bad.txt + +--echo # +--echo # athentication is successful +--echo # +--error 0 +--exec $MYSQL_TEST -u test_pam -pgoodpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt + +--echo # +--echo # athentication is unsuccessful +--echo # +--error 1 +--exec $MYSQL_TEST -u test_pam -pbadpassword < $MYSQLTEST_VARDIR/tmp/pam_good2.txt + +--remove_file $MYSQLTEST_VARDIR/tmp/pam_good.txt +--remove_file $MYSQLTEST_VARDIR/tmp/pam_good2.txt +--remove_file $MYSQLTEST_VARDIR/tmp/pam_bad.txt +drop user test_pam; +drop user pam_test; +let $count_sessions= 1; +--source include/wait_until_count_sessions.inc +uninstall plugin pam; diff --git a/mysql-test/suite/plugins/t/processlist.test b/mysql-test/suite/plugins/t/processlist.test new file mode 100644 index 00000000..39b715b8 --- /dev/null +++ b/mysql-test/suite/plugins/t/processlist.test @@ -0,0 +1,20 @@ +# +# MDEV-15359 Thread stay in "cleaning up" status after finishing +# +source include/have_innodb.inc; + +create table t1 (a int) engine=innodb; +start transaction; +insert t1 values (1); +let id=`select connection_id()`; +connect con2,localhost,root; +let $wait_condition=select state='' from information_schema.processlist where id = $id; +--source include/wait_condition.inc +replace_regex /\"/-/; #" +let s=`show engine innodb status`; +disable_query_log; +eval select regexp_replace("$s", '(?s)^.*MySQL thread id $id,.*root([^\n]*)\n.*', '\\\\1') as `state from show engine innodb status, must be empty`; +enable_query_log; +disconnect con2; +connection default; +drop table t1; diff --git a/mysql-test/suite/plugins/t/qc_info.test b/mysql-test/suite/plugins/t/qc_info.test new file mode 100644 index 00000000..8803e907 --- /dev/null +++ b/mysql-test/suite/plugins/t/qc_info.test @@ -0,0 +1,26 @@ +set @save_query_cache_size=@@global.query_cache_size; +--source include/default_charset.inc +--source qc_info_init.inc + +# test that hits are correctly incremented +reset query cache; +select * from t1; +select * from t1; +select hits, statement_text from information_schema.query_cache_info; + +drop table t1; +# the query was invalidated +select statement_schema, statement_text, result_blocks_count, result_blocks_size from information_schema.query_cache_info; + +set global query_cache_size = 0; +select * from information_schema.query_cache_info; + +set global query_cache_size= default; +set global query_cache_type=default; + +--error ER_PARSE_ERROR +show query_cache_info; +--error ER_PARSE_ERROR +flush query_cache_info; + +set @@global.query_cache_size=@save_query_cache_size; diff --git a/mysql-test/suite/plugins/t/qc_info_init.inc b/mysql-test/suite/plugins/t/qc_info_init.inc new file mode 100644 index 00000000..c3f700f0 --- /dev/null +++ b/mysql-test/suite/plugins/t/qc_info_init.inc @@ -0,0 +1,34 @@ +if (`select count(*) = 0 from information_schema.plugins where plugin_name = 'query_cache_info' and plugin_status='active'`) +{ + --skip QUERY_CACHE_INFO plugin is not active +} + +set global query_cache_type=ON; +set local query_cache_type=ON; +set global query_cache_size=1355776; + +create table t1 (a int not null); +insert into t1 values (1),(2),(3); +select * from t1; +select statement_schema, statement_text, result_blocks_count, result_blocks_size from information_schema.query_cache_info; + +select @@time_zone into @time_zone; +select @@default_week_format into @default_week_format; +select @@character_set_client into @character_set_client; +select @@character_set_results into @character_set_results; +select @@sql_mode into @sql_mode; +select @@div_precision_increment into @div_precision_increment; +select @@lc_time_names into @lc_time_names; +select @@max_sort_length into @max_sort_length; +select @@autocommit into @autocommit; +select @@group_concat_max_len into @group_concat_max_len; +select Name into @new_time_zone from mysql.time_zone_name limit 1; +set time_zone=@new_time_zone,default_week_format=4,character_set_client='binary',character_set_results='utf32',collation_connection='utf32_bin',sql_mode='STRICT_ALL_TABLES',div_precision_increment=7,lc_time_names='ar_SD',autocommit=0, group_concat_max_len=513, max_sort_length=1011; +--disable_result_log +select * from t1; +--enable_result_log +set time_zone= @time_zone, default_week_format= @default_week_format, character_set_client= @character_set_client,character_set_results= @character_set_results, sql_mode= @sql_mode, div_precision_increment= @div_precision_increment, lc_time_names= @lc_time_names, autocommit= @autocommit, group_concat_max_len= @group_concat_max_len, max_sort_length= @max_sort_length; + +--sorted_result +--replace_column 5 # 19 # 23 # +select * from information_schema.query_cache_info; diff --git a/mysql-test/suite/plugins/t/qc_info_init.opt b/mysql-test/suite/plugins/t/qc_info_init.opt new file mode 100644 index 00000000..53b4ff31 --- /dev/null +++ b/mysql-test/suite/plugins/t/qc_info_init.opt @@ -0,0 +1,2 @@ +--loose-query_cache_info +--plugin-load-add=$QUERY_CACHE_INFO_SO diff --git a/mysql-test/suite/plugins/t/qc_info_priv.test b/mysql-test/suite/plugins/t/qc_info_priv.test new file mode 100644 index 00000000..59258731 --- /dev/null +++ b/mysql-test/suite/plugins/t/qc_info_priv.test @@ -0,0 +1,16 @@ +set @save_query_cache_size=@@global.query_cache_size; +--source include/not_embedded.inc +--source qc_info_init.inc + +# try an unprivileged user +create user mysqltest; +connect (conn1,localhost,mysqltest,,); +connection conn1; +select a from t1; +select count(*) from information_schema.query_cache_info; +connection default; +drop user mysqltest; +drop table t1; + +set @@global.query_cache_size=@save_query_cache_size; +set global query_cache_type=default; diff --git a/mysql-test/suite/plugins/t/server_audit.opt b/mysql-test/suite/plugins/t/server_audit.opt new file mode 100644 index 00000000..089ed94b --- /dev/null +++ b/mysql-test/suite/plugins/t/server_audit.opt @@ -0,0 +1,2 @@ +--thread_handling='one-thread-per-connection' + diff --git a/mysql-test/suite/plugins/t/server_audit.test b/mysql-test/suite/plugins/t/server_audit.test new file mode 100644 index 00000000..01da4975 --- /dev/null +++ b/mysql-test/suite/plugins/t/server_audit.test @@ -0,0 +1,235 @@ +--source include/have_plugin_auth.inc +--source include/not_embedded.inc + +if (!$SERVER_AUDIT_SO) { + skip No SERVER_AUDIT plugin; +} + +# An unfortunate wait for check-testcase.test to complete disconnect. +let count_sessions= 1; +source include/wait_until_count_sessions.inc; + +let $MYSQLD_DATADIR= `SELECT @@datadir`; +let SEARCH_FILE= $MYSQLD_DATADIR/server_audit.log; + +install plugin server_audit soname 'server_audit'; + +show variables like 'server_audit%'; +set global server_audit_file_path=null; +set global server_audit_incl_users=null; +set global server_audit_file_path='server_audit.log'; +set global server_audit_output_type=file; +set global server_audit_logging=on; + +--error ER_WRONG_VALUE_FOR_VAR +set global server_audit_incl_users= repeat("'root',", 10000); +show variables like 'server_audit_incl_users'; +--error ER_WRONG_VALUE_FOR_VAR +set global server_audit_excl_users= repeat("'root',", 10000); +show variables like 'server_audit_excl_users'; +let SEARCH_COUNT= 5; +source include/wait_for_line_count_in_file.inc; + +connect (con1,localhost,root,,mysql); +disconnect con1; +let SEARCH_COUNT= 7; +source include/wait_for_line_count_in_file.inc; + +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error ER_ACCESS_DENIED_ERROR +connect (con1,localhost,no_such_user,,mysql); +let SEARCH_COUNT= 9; +source include/wait_for_line_count_in_file.inc; + +connection default; +set global server_audit_incl_users='odin, dva, tri'; +create table t1 (id int); +set global server_audit_incl_users='odin, root, dva, tri'; +create table t2 (id int); +set global server_audit_excl_users='odin, dva, tri'; +insert into t1 values (1), (2); +select * from t1; +set global server_audit_incl_users='odin, root, dva, tri'; +insert into t2 values (1), (2); +select * from t2; +alter table t1 rename renamed_t1; +set global server_audit_events='connect,query'; +select 1, + 2, +# comment + 3; +insert into t2 values (1), (2); +select * from t2; +--disable_ps_protocol +--error ER_NO_SUCH_TABLE +select * from t_doesnt_exist; +--enable_ps_protocol +--error 1064 +syntax_error_query; +drop table renamed_t1, t2; +show variables like 'server_audit%'; +set global server_audit_mode=1; +set global server_audit_events=''; +create database sa_db; +let SEARCH_COUNT= 47; +source include/wait_for_line_count_in_file.inc; + +connect (con1,localhost,root,,test); +create table t1 (id2 int); +insert into t1 values (1), (2); +select * from t1; +drop table t1; +use sa_db; +create table sa_t1(id int); +insert into sa_t1 values (1), (2); +drop table sa_t1; +drop database sa_db; +disconnect con1; +let SEARCH_COUNT= 80; +source include/wait_for_line_count_in_file.inc; + +connection default; +create database sa_db; +use sa_db; +CREATE USER u1 IDENTIFIED BY 'pwd-123'; +GRANT ALL ON sa_db TO u2 IDENTIFIED BY "pwd-321"; +SET PASSWORD FOR u1 = PASSWORD('pwd 098'); +CREATE USER u3 IDENTIFIED BY ''; +ALTER USER u3 IDENTIFIED BY 'pwd-456'; +drop user u1, u2, u3; + +set global server_audit_events='query_ddl'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +select 2; +(select 2); +/*! select 2*/; +/*comment*/ select 2; +drop table t1; + +create procedure pr1() insert into test.t1 values ("foo", 42); +create function fn1(i int) returns int deterministic return i+1; +drop procedure pr1; +drop function fn1; + +set global server_audit_events='query_ddl,query_dml'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +select 2; +drop table t1; +set global server_audit_events='query_dml'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +select 2; +(select 2); +/*! select 2*/; +/*comment*/ select 2; +drop table t1; +set global server_audit_events='query_dcl'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +CREATE USER u1 IDENTIFIED BY 'pwd-123'; +GRANT ALL ON sa_db TO u2 IDENTIFIED BY "pwd-321"; +SET PASSWORD +# comment +FOR u1 = PASSWORD('pwd 098'); +--error 1064 +SET PASSWORD FOR u1=<secret>; +CREATE USER u3 IDENTIFIED BY ''; +drop user u1, u2, u3; +select 2; +(select 2); +/*! select 2*/; +/*comment*/ select 2; +drop table t1; +set global server_audit_events='query_dml_no_select'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +select 2; +drop table t1; +create procedure pr1() insert into test.t1 values ("foo", 42); +create function fn1(i int) returns int deterministic return i+1; +drop procedure pr1; +drop function fn1; + +create procedure pr1() insert into test.t1 values ("foo", 42); +create function fn1(i int) returns int deterministic return i+1; +drop procedure pr1; +drop function fn1; + +set global server_audit_events='table'; +set global server_audit_incl_users='user1'; + +create user user1@localhost; +grant all on sa_db.* to user1@localhost; + +connect (cn1,localhost,user1,,sa_db); +connection cn1; + +create table t1(id int) engine=myisam; +insert delayed into t1 values (1); +connection default; +--echo # Waiting until INSERT DELAYED thread does the insert. +let $wait_condition= SELECT COUNT(*) = 1 FROM t1; +--source include/wait_condition.inc +drop table t1; + +set global server_audit_logging= off; +set global server_audit_incl_users='root'; +set global server_audit_logging= on; +disconnect cn1; +let $count_sessions=1; +source include/wait_until_count_sessions.inc; + +drop user user1@localhost; + +set global server_audit_events=''; +set global server_audit_incl_users='root, plug_dest'; + +CREATE USER plug IDENTIFIED WITH 'test_plugin_server' AS 'plug_dest'; +CREATE USER plug_dest IDENTIFIED BY 'plug_dest_passwd'; +--sleep 2 +--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK +--error ER_ACCESS_DENIED_ERROR : this should fail : no grant +connect(plug_con,localhost,plug,plug_dest); +--sleep 2 +GRANT PROXY ON plug_dest TO plug; +--sleep 2 +connect(plug_con,localhost,plug,plug_dest); +connection plug_con; +select USER(),CURRENT_USER(); +connection default; +disconnect plug_con; +--sleep 2 +--sleep 2 +DROP USER plug; +DROP USER plug_dest; + +set global server_audit_query_log_limit= 15; +select (1), (2), (3), (4); +select 'A', 'B', 'C', 'D'; +set global server_audit_query_log_limit= 1024; +drop database sa_db; + +set global server_audit_file_path='.'; +--replace_regex /\.[\\\/]/HOME_DIR\// +show status like 'server_audit_current_log'; +set global server_audit_file_path=''; +show status like 'server_audit_current_log'; +set global server_audit_file_path=' '; +show status like 'server_audit_current_log'; +set global server_audit_file_path='nonexisting_dir/'; +show status like 'server_audit_current_log'; +show variables like 'server_audit%'; +uninstall plugin server_audit; + +# replace the timestamp and the hostname with constant values +--replace_regex /[0-9]* [0-9][0-9]:[0-9][0-9]:[0-9][0-9]\,[^,]*\,/TIME,HOSTNAME,/ /\,[1-9][0-9]*\,/,1,/ /\,[1-9][0-9]*/,ID/ +cat_file $MYSQLD_DATADIR/server_audit.log; +remove_file $MYSQLD_DATADIR/server_audit.log; + diff --git a/mysql-test/suite/plugins/t/show_all_plugins.test b/mysql-test/suite/plugins/t/show_all_plugins.test new file mode 100644 index 00000000..f8f37597 --- /dev/null +++ b/mysql-test/suite/plugins/t/show_all_plugins.test @@ -0,0 +1,30 @@ +if (!$DIALOG_EXAMPLES_SO) { skip requires dialog_examples.so; } +if (!$HA_EXAMPLE_SO) { skip requires ha_examples.so; } +if (!$LIBDAEMON_EXAMPLE_SO) { skip requires libdaemon_examples.so; } +if (!$UDF_EXAMPLE_SO) { skip requires udf_example.so; } +if (!$EXAMPLE_KEY_MANAGEMENT_SO) { skip requires example_key_management.so; } +if (`SELECT VERSION() LIKE '%embedded%'`) { skip Disabled for embedded until MDEV-8664 is resolved; } + +flush status; +show status like '%libraries%'; +#--sorted_result +#select * from information_schema.all_plugins; +#show status like '%libraries%'; +--replace_result .dll .so +--replace_column 5 MYSQL_VERSION_ID +eval select * from information_schema.all_plugins where plugin_library='$HA_EXAMPLE_SO'; +show status like '%libraries%'; +--sorted_result +--replace_result .dll .so +eval show plugins soname '$HA_EXAMPLE_SO'; +show status like '%libraries%'; +--sorted_result +--replace_result .dll .so +show plugins soname like '%example%'; +--replace_result 8 7 +show status like '%libraries%'; +--sorted_result +--replace_result .dll .so +eval show plugins soname where library = '$HA_EXAMPLE_SO'; +select variable_value > 10 from information_schema.global_status where variable_name like '%libraries%'; + diff --git a/mysql-test/suite/plugins/t/simple_password_check.test b/mysql-test/suite/plugins/t/simple_password_check.test new file mode 100644 index 00000000..b7d631ab --- /dev/null +++ b/mysql-test/suite/plugins/t/simple_password_check.test @@ -0,0 +1,124 @@ +--source include/not_embedded.inc + +if (!$SIMPLE_PASSWORD_CHECK_SO) { + skip No SIMPLE_PASSWORD_CHECK plugin; +} + +install soname "simple_password_check"; + +--vertical_results +--replace_result .dll .so +select * from information_schema.plugins where plugin_name='simple_password_check'; + +select * from information_schema.system_variables where variable_name like 'simple_password_check%' order by 1; +--horizontal_results + +--error ER_NOT_VALID_PASSWORD +create user foo1 identified by 'pwd'; + +# Create user with no password. +--error ER_NOT_VALID_PASSWORD +create user foo1; + +--error ER_NOT_VALID_PASSWORD +grant select on *.* to foo1 identified by 'pwd'; + +--error ER_NOT_VALID_PASSWORD +grant select on *.* to `FooBar1!` identified by 'FooBar1!'; + +grant select on *.* to `BarFoo1!` identified by 'FooBar1!'; +drop user `BarFoo1!`; + +create user foo1 identified by 'aA.12345'; +grant select on *.* to foo1; +drop user foo1; + +set global simple_password_check_digits=3; +set global simple_password_check_letters_same_case=3; +set global simple_password_check_other_characters=3; +show variables like 'simple_password_check_%'; + +create user foo1 identified by '123:qwe:ASD!'; +drop user foo1; + +--error ER_NOT_VALID_PASSWORD +create user foo1 identified by '-23:qwe:ASD!'; + +--error ER_NOT_VALID_PASSWORD +create user foo1 identified by '123:4we:ASD!'; + +--error ER_NOT_VALID_PASSWORD +create user foo1 identified by '123:qwe:4SD!'; + +--error ER_NOT_VALID_PASSWORD +create user foo1 identified by '123:qwe:ASD4'; + +create user foo1 identified by '123:qwe:ASD!'; +--error ER_NOT_VALID_PASSWORD +set password for foo1 = password('qwe:-23:ASD!'); +--error ER_NOT_VALID_PASSWORD +set password for foo1 = old_password('4we:123:ASD!'); +--error ER_NOT_VALID_PASSWORD +set password for foo1 = password('qwe:123:4SD!'); +--error ER_NOT_VALID_PASSWORD +set password for foo1 = old_password('qwe:123:ASD4'); +set password for foo1 = password('qwe:123:ASD!'); + +# now, strict_password_validation +select @@strict_password_validation; + +--error ER_NOT_VALID_PASSWORD +set password for foo1 = ''; +--error ER_OPTION_PREVENTS_STATEMENT +set password for foo1 = '2222222222222222'; +--error ER_OPTION_PREVENTS_STATEMENT +set password for foo1 = '11111111111111111111111111111111111111111'; +--error ER_OPTION_PREVENTS_STATEMENT +create user foo2 identified by password '11111111111111111111111111111111111111111'; +--error ER_OPTION_PREVENTS_STATEMENT +grant select on *.* to foo2 identified by password '2222222222222222'; +--error ER_OPTION_PREVENTS_STATEMENT +create user foo2 identified with mysql_native_password using '11111111111111111111111111111111111111111'; +--error ER_OPTION_PREVENTS_STATEMENT +grant select on *.* to foo2 identified with mysql_old_password using '2222222222222222'; +--error ER_NOT_VALID_PASSWORD +create user foo2 identified with mysql_native_password using ''; +--error ER_NOT_VALID_PASSWORD +grant select on *.* to foo2 identified with mysql_old_password using ''; +--error ER_NOT_VALID_PASSWORD +grant select on *.* to foo2 identified with mysql_old_password; + +# direct updates are not protected +update mysql.global_priv set priv=json_set(priv, '$.authentication_string', 'xxx') where user='foo1'; + +set global strict_password_validation=0; + +--error ER_NOT_VALID_PASSWORD +set password for foo1 = ''; +set password for foo1 = '2222222222222222'; +set password for foo1 = '11111111111111111111111111111111111111111'; +create user foo2 identified by password '11111111111111111111111111111111111111111'; +drop user foo2; +grant select on *.* to foo2 identified by password '2222222222222222'; +drop user foo2; +create user foo2 identified with mysql_native_password using '11111111111111111111111111111111111111111'; +drop user foo2; +grant select on *.* to foo2 identified with mysql_old_password using '2222222222222222'; +drop user foo2; + +set global strict_password_validation=1; +drop user foo1; + +# +# MDEV-9940 CREATE ROLE blocked by password validation plugin +# +create role r1; +drop role r1; + +flush privileges; + +uninstall plugin simple_password_check; + +create user foo1 identified by 'pwd'; +drop user foo1; + diff --git a/mysql-test/suite/plugins/t/sql_error_log.test b/mysql-test/suite/plugins/t/sql_error_log.test new file mode 100644 index 00000000..6c83e965 --- /dev/null +++ b/mysql-test/suite/plugins/t/sql_error_log.test @@ -0,0 +1,75 @@ + +--source include/not_embedded.inc + +if (!$SQL_ERRLOG_SO) { + skip No SQL_ERROR_LOG plugin; +} + +--disable_warnings +drop procedure if exists test_error; +drop table if exists t1; +--enable_warnings + +install plugin SQL_ERROR_LOG soname 'sql_errlog'; + +show variables like 'sql_error_log%'; +set global sql_error_log_rate=1; +--error ER_NO_SUCH_TABLE +select * from t_doesnt_exist; +--error 1064 +syntax_error_query; + +delimiter |; + +CREATE PROCEDURE test_error() +BEGIN +DECLARE CONTINUE HANDLER +FOR 1146 +BEGIN +RESIGNAL SQLSTATE '40000' SET +MYSQL_ERRNO = 1000, +MESSAGE_TEXT = 'new message'; +END; +SELECT `c` FROM `temptab`; +END| + +delimiter ;| + +--error 1000 +CALL test_error(); +drop procedure test_error; + +SET SQL_MODE = STRICT_ALL_TABLES; +create table t1(id int); +--error 1366 +insert into t1 values ('aa'); +SET SQL_MODE = ''; +drop table t1; + +SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'not_exists' AND TABLE_NAME = 'not_exists'; + +# +# MDEV-6421 SQL_ERROR_LOG doesn't log comments in Events +# actually testing SP call is enough for that. + +DELIMITER |; + +CREATE procedure e1() +BEGIN + START TRANSACTION; + INSERT INTO test.non_exists VALUES (0,0,0) /* e1 */; + COMMIT; +END| + +DELIMITER ;| +--error ER_NO_SUCH_TABLE +CALL e1(); +DROP PROCEDURE e1; + +uninstall plugin SQL_ERROR_LOG; + +let $MYSQLD_DATADIR= `SELECT @@datadir`; +# replace the timestamp and the hostname with constant values +--replace_regex /[1-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9] [ 0-9][0-9]:[0-9][0-9]:[0-9][0-9] [^E]*/TIME HOSTNAME / +cat_file $MYSQLD_DATADIR/sql_errors.log; + diff --git a/mysql-test/suite/plugins/t/test_sql_service.opt b/mysql-test/suite/plugins/t/test_sql_service.opt new file mode 100644 index 00000000..089ed94b --- /dev/null +++ b/mysql-test/suite/plugins/t/test_sql_service.opt @@ -0,0 +1,2 @@ +--thread_handling='one-thread-per-connection' + diff --git a/mysql-test/suite/plugins/t/test_sql_service.test b/mysql-test/suite/plugins/t/test_sql_service.test new file mode 100644 index 00000000..3384b523 --- /dev/null +++ b/mysql-test/suite/plugins/t/test_sql_service.test @@ -0,0 +1,18 @@ + +--source include/not_embedded.inc + +if (!$TEST_SQL_SERVICE_SO) { + skip No TEST_SQL_SERVICE plugin; +} + +# An unfortunate wait for check-testcase.test to complete disconnect. +let count_sessions= 1; +source include/wait_until_count_sessions.inc; + +install plugin test_sql_service soname 'test_sql_service'; + +set global test_sql_service_run_test= 1; +show status like 'test_sql_service%'; + +uninstall plugin test_sql_service; + diff --git a/mysql-test/suite/plugins/t/thread_pool_server_audit.opt b/mysql-test/suite/plugins/t/thread_pool_server_audit.opt new file mode 100644 index 00000000..30953d0c --- /dev/null +++ b/mysql-test/suite/plugins/t/thread_pool_server_audit.opt @@ -0,0 +1,2 @@ +--thread_handling=pool-of-threads + diff --git a/mysql-test/suite/plugins/t/thread_pool_server_audit.test b/mysql-test/suite/plugins/t/thread_pool_server_audit.test new file mode 100644 index 00000000..7347d8f9 --- /dev/null +++ b/mysql-test/suite/plugins/t/thread_pool_server_audit.test @@ -0,0 +1,149 @@ +--source include/not_embedded.inc +--source include/have_pool_of_threads.inc + +if (!$SERVER_AUDIT_SO) { + skip No SERVER_AUDIT plugin; +} + +install plugin server_audit soname 'server_audit'; + +show variables like 'server_audit%'; +set global server_audit_file_path=null; +set global server_audit_incl_users=null; +set global server_audit_file_path='server_audit.log'; +set global server_audit_output_type=file; +set global server_audit_logging=on; +--sleep 2 +connect (con1,localhost,root,,mysql); +connection default; +disconnect con1; +--sleep 2 +--sleep 2 +--replace_result $MASTER_MYSOCK MASTER_SOCKET $MASTER_MYPORT MASTER_PORT +--error ER_ACCESS_DENIED_ERROR +connect (con1,localhost,no_such_user,,mysql); +connection default; +--sleep 2 +set global server_audit_incl_users='odin, dva, tri'; +create table t1 (id int); +set global server_audit_incl_users='odin, root, dva, tri'; +create table t2 (id int); +set global server_audit_excl_users='odin, dva, tri'; +insert into t1 values (1), (2); +select * from t1; +set global server_audit_incl_users='odin, root, dva, tri'; +insert into t2 values (1), (2); +select * from t2; +alter table t1 rename renamed_t1; +set global server_audit_events='connect,query'; +select 1, + 2, +# comment + 3; +insert into t2 values (1), (2); +select * from t2; +--disable_ps_protocol +--error ER_NO_SUCH_TABLE +select * from t_doesnt_exist; +--enable_ps_protocol +--error 1064 +syntax_error_query; +drop table renamed_t1, t2; +show variables like 'server_audit%'; +set global server_audit_mode=1; +set global server_audit_events=''; +create database sa_db; +--sleep 2 +connect (con1,localhost,root,,test); +connection con1; +--sleep 2 +--sleep 2 +create table t1 (id2 int); +insert into t1 values (1), (2); +select * from t1; +drop table t1; +use sa_db; +create table sa_t1(id int); +insert into sa_t1 values (1), (2); +drop table sa_t1; +drop database sa_db; +connection default; +disconnect con1; +--sleep 2 +--sleep 2 +create database sa_db; +use sa_db; +CREATE USER u1 IDENTIFIED BY 'pwd-123'; +GRANT ALL ON sa_db TO u2 IDENTIFIED BY "pwd-321"; +SET PASSWORD FOR u1 = PASSWORD('pwd 098'); +CREATE USER u3 IDENTIFIED BY ''; +drop user u1, u2, u3; + +set global server_audit_events='query_ddl'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +select 2; +(select 2); +/*! select 2*/; +/*comment*/ select 2; +drop table t1; +set global server_audit_events='query_ddl,query_dml'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +select 2; +drop table t1; +set global server_audit_events='query_dml'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +select 2; +(select 2); +/*! select 2*/; +/*comment*/ select 2; +drop table t1; +set global server_audit_events='query_dcl'; +create table t1(id int); +insert into t1 values (1), (2); +select * from t1; +CREATE USER u1 IDENTIFIED BY 'pwd-123'; +GRANT ALL ON sa_db TO u2 IDENTIFIED BY "pwd-321"; +SET PASSWORD +# comment +FOR u1 = PASSWORD('pwd 098'); +--error 1064 +SET PASSWORD FOR u1=<secret>; +CREATE USER u3 IDENTIFIED BY ''; +drop user u1, u2, u3; +select 2; +(select 2); +/*! select 2*/; +/*comment*/ select 2; +drop table t1; +set global server_audit_events=''; + +set global server_audit_query_log_limit= 15; +select (1), (2), (3), (4); +select 'A', 'B', 'C', 'D'; +set global server_audit_query_log_limit= 1024; +drop database sa_db; + +set global server_audit_file_path='.'; +--replace_regex /\.[\\\/]/HOME_DIR\// +show status like 'server_audit_current_log'; +set global server_audit_file_path=''; +show status like 'server_audit_current_log'; +set global server_audit_file_path=' '; +show status like 'server_audit_current_log'; +set global server_audit_file_path='nonexisting_dir/'; +show status like 'server_audit_current_log'; +show variables like 'server_audit%'; +uninstall plugin server_audit; + +let $MYSQLD_DATADIR= `SELECT @@datadir`; +# replace the timestamp and the hostname with constant values +--replace_regex /[0-9]* [0-9][0-9]:[0-9][0-9]:[0-9][0-9]\,[^,]*\,/TIME,HOSTNAME,/ /\,[1-9][0-9]*\,/,1,/ /\,[1-9][0-9]*/,ID/ +cat_file $MYSQLD_DATADIR/server_audit.log; +remove_file $MYSQLD_DATADIR/server_audit.log; + diff --git a/mysql-test/suite/plugins/t/two_password_validations.test b/mysql-test/suite/plugins/t/two_password_validations.test new file mode 100644 index 00000000..89a5c7ae --- /dev/null +++ b/mysql-test/suite/plugins/t/two_password_validations.test @@ -0,0 +1,38 @@ +--source include/not_embedded.inc + +if (!$CRACKLIB_PASSWORD_CHECK_SO) { + skip No CRACKLIB_PASSWORD_CHECK plugin; +} + +if (!$SIMPLE_PASSWORD_CHECK_SO) { + skip No SIMPLE_PASSWORD_CHECK plugin; +} + +let $only_simple=grant select on *.* to Fff_fff1 identified by '1fff_ffF'; +let $only_crack=grant select on *.* to foobar identified by 'q-%^&*rty'; + +install soname "simple_password_check"; + +eval $only_simple; +drop user Fff_fff1; + +install soname "cracklib_password_check"; + +grant select on *.* to foobar identified by 'q$%^&*R1234ty'; +drop user foobar; + +--error ER_NOT_VALID_PASSWORD +eval $only_simple; +show warnings; + +--error ER_NOT_VALID_PASSWORD +eval $only_crack; +show warnings; + +uninstall plugin simple_password_check; + +eval $only_crack; +drop user foobar; + +uninstall plugin cracklib_password_check; + diff --git a/mysql-test/suite/plugins/t/unix_socket.test b/mysql-test/suite/plugins/t/unix_socket.test new file mode 100644 index 00000000..be2afb0c --- /dev/null +++ b/mysql-test/suite/plugins/t/unix_socket.test @@ -0,0 +1,51 @@ +--source include/have_unix_socket.inc + +--echo # +--echo # with named user +--echo # + +--let $replace=create user '$USER' +--replace_result $replace "create user 'USER'" +eval create user '$USER' identified via unix_socket; + +--write_file $MYSQLTEST_VARDIR/tmp/peercred_test.txt +--let $replace1=$USER@localhost +--let $replace2=$USER@% +--replace_result $replace1 "USER@localhost" $replace2 "USER@%" +select user(), current_user(), database(); +EOF + +--echo # +--echo # name match = ok +--echo # +--exec $MYSQL_TEST -u $USER < $MYSQLTEST_VARDIR/tmp/peercred_test.txt + +--echo # +--echo # name does not match = failure +--echo # +--error 1 +--exec $MYSQL_TEST -u foobar < $MYSQLTEST_VARDIR/tmp/peercred_test.txt + +--let $replace=drop user '$USER' +--replace_result $replace "drop user 'USER'" +eval drop user '$USER'; + +--echo # +--echo # and now with anonymous user +--echo # +grant SELECT ON test.* TO '' identified via unix_socket; +--echo # +--echo # name match = ok +--echo # +--exec $MYSQL_TEST -u $USER < $MYSQLTEST_VARDIR/tmp/peercred_test.txt + +--echo # +--echo # name does not match = failure +--echo # +--error 1 +--exec $MYSQL_TEST -u foobar < $MYSQLTEST_VARDIR/tmp/peercred_test.txt + +# restoring mysql.user to the original state. +delete from mysql.user where user=''; +FLUSH PRIVILEGES; +--remove_file $MYSQLTEST_VARDIR/tmp/peercred_test.txt |