summaryrefslogtreecommitdiffstats
path: root/mysql-test/main/upgrade_mdev_24363.test
blob: c188d4c7facbd24891de92b64c8268b07816a52a (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
--echo #
--echo # MDEV-24363 10.4: change definition of mysql.user view
--echo # to reflect the correct value from mysql.global_priv
--echo # This change was added because mysql.user view definition
--echo # was already changed when mariadb.sys was introduced, so
--echo # it's decently ok if we change it again to fix mdev-24363

--echo #
--echo # Test that mysql.user password_expired column
--echo # shows the right value as in mysql.global_priv
--echo #
--disable_service_connection
create user gigi@localhost;
show create user gigi@localhost;
select password_expired from mysql.user where user='gigi' and host='localhost';

alter user gigi@localhost password expire;
show create user gigi@localhost;
select password_expired from mysql.user where user='gigi' and host='localhost';

drop user gigi@localhost;
--enable_service_connection

--echo #
--echo # Test that upgrades from 10.4+ versions before this mdev
--echo # correctly drop and recreate the mysql.user view
--echo #

--source include/mysql_upgrade_preparation.inc

use mysql;

# Do all these string operations to replace password_expired definition
# with "'N' as password_expired" and avoid listing in one more test
# all the fields of the user view
set @def = (select view_definition from information_schema.views where table_name='user' and table_schema='mysql');
set @trimmed_def = (select trim(trailing 'from `mysql`.`global_priv`' from @def));
set @newdef = (select concat(@trimmed_def, ", 'N' AS password_expired from mysql.global_priv"));
set @pos = (select instr(@newdef, 'password_expired'));
let $viewdef = `select insert(@newdef, @pos, 3, 'abc')`;

--eval create or replace view user as $viewdef;

create user gigi@localhost;
show create user gigi@localhost;
select password_expired from mysql.user where user='gigi' and host='localhost';

# password should be expired, but mysql.user.password_expired should be 'N'
alter user gigi@localhost password expire;
show create user gigi@localhost;
select password_expired from mysql.user where user='gigi' and host='localhost';

drop user gigi@localhost;

--echo # Run mysql_upgrade
--exec $MYSQL_UPGRADE  2>&1
let $MYSQLD_DATADIR= `select @@datadir`;
--file_exists $MYSQLD_DATADIR/mysql_upgrade_info
--remove_file $MYSQLD_DATADIR/mysql_upgrade_info

create user gigi@localhost;
show create user gigi@localhost;
select password_expired from mysql.user where user='gigi' and host='localhost';

# mysql.user view should've been recreated by mariadb_upgrade, thus
# password_expired should show 'Y'
alter user gigi@localhost password expire;
show create user gigi@localhost;
select password_expired from mysql.user where user='gigi' and host='localhost';

drop user gigi@localhost;