1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
|
-- source include/mysql_upgrade_preparation.inc
-- source include/have_working_dns.inc
-- source include/have_innodb.inc
-- source include/have_partition.inc
set sql_mode="";
#
# Basic test that we can run mysql_upgrde and that it finds the
# expected binaries it uses.
#
--echo Run mysql_upgrade once
--exec $MYSQL_UPGRADE --force 2>&1
# It should have created a file in the MySQL Servers datadir
let $MYSQLD_DATADIR= `select @@datadir`;
file_exists $MYSQLD_DATADIR/mysql_upgrade_info;
--echo Run it again - should say already completed
--replace_regex /upgraded to [^\n].*/upgraded to VERSION./ /again for [^\n]*/again for VERSION./
--exec $MYSQL_UPGRADE 2>&1
# It should have created a file in the MySQL Servers datadir
file_exists $MYSQLD_DATADIR/mysql_upgrade_info;
--echo Force should run it regardless of whether it has been run before
--exec $MYSQL_UPGRADE --force 2>&1
# It should have created a file in the MySQL Servers datadir
file_exists $MYSQLD_DATADIR/mysql_upgrade_info;
#
# Bug #25452 mysql_upgrade access denied.
#
# Password protect a root account and run mysql_upgrade
CREATE USER mysqltest1@'%' IDENTIFIED by 'sakila';
GRANT ALL ON *.* TO mysqltest1@'%';
--echo Run mysql_upgrade with password protected account
--exec $MYSQL_UPGRADE --force --user=mysqltest1 --password=sakila 2>&1
DROP USER mysqltest1@'%';
#
# check that we get proper error messages if wrong user
--error 1
--exec $MYSQL_UPGRADE --force --user=mysqltest1 --password=sakila 2>&1
#
# Bug #26639 mysql_upgrade exits successfully even if external command failed
#
--echo Run mysql_upgrade with a non existing server socket
--replace_result $MYSQLTEST_VARDIR var
--replace_regex /.*mysqlcheck.*: Got/mysqlcheck: Got/ /\([0-9|-]*\)/(errno)/
--error 1
# NC: Added --skip-version-check, as the version check would fail when
# mysql_upgrade tries to get the server version.
--exec $MYSQL_UPGRADE --verbose --force --host=not_existing_host --skip-version-check 2>&1
#
# Bug #28401 mysql_upgrade Failed with STRICT_ALL_TABLES, ANSI_QUOTES and NO_ZERO_DATE
#
# The SQL commands used by mysql_upgrade are written to be run
# with sql_mode set to '' - thus the scripts should change sql_mode
# for the session to make sure the SQL is legal.
# Test by setting sql_mode before running mysql_upgrade
set GLOBAL sql_mode='STRICT_ALL_TABLES,ANSI_QUOTES,NO_ZERO_DATE';
--exec $MYSQL_UPGRADE --force 2>&1
eval set GLOBAL sql_mode=default;
--echo #
--echo # Bug #41569 mysql_upgrade (ver 5.1) add 3 fields to mysql.proc table
--echo # but does not set values.
--echo #
# Create a stored procedure and set the fields in question to null.
# When running mysql_upgrade, a warning should be written.
CREATE PROCEDURE testproc() BEGIN END;
UPDATE mysql.proc SET character_set_client = NULL WHERE name LIKE 'testproc';
UPDATE mysql.proc SET collation_connection = NULL WHERE name LIKE 'testproc';
UPDATE mysql.proc SET db_collation = NULL WHERE name LIKE 'testproc';
--exec $MYSQL_UPGRADE --force 2> $MYSQLTEST_VARDIR/tmp/41569.txt
CALL testproc();
DROP PROCEDURE testproc;
--cat_file $MYSQLTEST_VARDIR/tmp/41569.txt
--remove_file $MYSQLTEST_VARDIR/tmp/41569.txt
--echo #
--echo # Bug #53613: mysql_upgrade incorrectly revokes
--echo # TRIGGER privilege on given table
--echo #
GRANT USAGE ON *.* TO 'user3'@'%';
GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%';
--echo Run mysql_upgrade with all privileges on a user
--exec $MYSQL_UPGRADE --force 2>&1
SHOW GRANTS FOR 'user3'@'%';
DROP USER 'user3'@'%';
--echo # End of 5.1 tests
#
# Test the --upgrade-system-tables option
#
--replace_result $MYSQLTEST_VARDIR var
--exec $MYSQL_UPGRADE --force --upgrade-system-tables
--echo #
--echo # Bug#11827359 60223: MYSQL_UPGRADE PROBLEM WITH OPTION
--echo # SKIP-WRITE-BINLOG
--echo #
let $MYSQLD_DATADIR= `select @@datadir`;
--echo # Droping the previously created mysql_upgrade_info file..
--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
--echo # Running mysql_upgrade with --skip-write-binlog..
--replace_regex /[^ ]*mysql_upgrade_info/...mysql_upgrade_info/
--exec $MYSQL_UPGRADE --skip-write-binlog
# mysql_upgrade must have created mysql_upgrade_info file,
# so the following command should never fail.
--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
--echo #
--echo # Bug #21489398: MYSQL_UPGRADE: FATAL ERROR: UPGRADE FAILED - IMPROVE ERROR
--echo #
--echo Run mysql_upgrade with unauthorized access
--error 1
--exec $MYSQL_UPGRADE --skip-verbose --user=root --password=wrong_password 2>&1
--replace_regex /.*mysqlcheck.*: Got/mysqlcheck: Got/ /\([0-9|-]*\)/(errno)/
--error 1
--exec $MYSQL_UPGRADE --skip-verbose --skip-version-check --user=root --password=wrong_password 2>&1
--echo #
--echo # MDEV-4332 Increase username length from 16 characters
--echo # MDEV-6068, MDEV-6178 mysql_upgrade breaks databases with long user names
--echo #
connection default;
GRANT SELECT ON mysql.* TO very_long_user_name_number_1;
GRANT SELECT ON mysql.* TO very_long_user_name_number_2;
GRANT ALL ON *.* TO even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost WITH GRANT OPTION;
--change_user even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length
GRANT INSERT ON mysql.user TO very_long_user_name_number_1;
GRANT INSERT ON mysql.user TO very_long_user_name_number_2;
GRANT UPDATE (User) ON mysql.db TO very_long_user_name_number_1;
GRANT UPDATE (User) ON mysql.db TO very_long_user_name_number_2;
CREATE PROCEDURE test.pr() BEGIN END;
--change_user root
--replace_result $MYSQLTEST_VARDIR var
--exec $MYSQL_UPGRADE --force 2>&1
SELECT definer FROM mysql.proc WHERE db = 'test' AND name = 'pr';
SELECT grantor FROM mysql.tables_priv WHERE db = 'mysql' AND table_name = 'user';
DROP USER very_long_user_name_number_1, very_long_user_name_number_2, even_longer_user_name_number_3_to_test_the_grantor_and_definer_field_length@localhost;
DROP PROCEDURE test.pr;
#
# MDEV-13274 mysql_upgrade fails if dbname+tablename+partioname > 64 chars
#
use test;
create table extralongname_extralongname_extralongname_extralongname_ext (
id int(10) unsigned not null,
created_date date not null,
created timestamp not null,
primary key (created,id,created_date)
) engine=innodb stats_persistent=1 default charset=latin1
partition by range (year(created_date))
subpartition by hash (month(created_date))
subpartitions 2 (
partition p2007 values less than (2008),
partition p2008 values less than (2009)
);
--exec $MYSQL_UPGRADE --skip-verbose --force 2>&1
select length(table_name) from mysql.innodb_table_stats;
drop table extralongname_extralongname_extralongname_extralongname_ext;
--echo # End of 10.0 tests
set sql_mode=default;
#
# Enforce storage engine option should not effect mysql_upgrade
#
--echo # Droping the previously created mysql_upgrade_info file..
--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
create table test.t1(a int) engine=MyISAM;
--echo # Trying to enforce InnoDB for all tables
SET GLOBAL enforce_storage_engine=InnoDB;
--replace_result $MYSQLTEST_VARDIR var
--exec $MYSQL_UPGRADE --force 2>&1
--echo # Should return 2
SELECT count(*) FROM information_schema.tables where ENGINE="InnoDB";
SHOW CREATE TABLE test.t1;
DROP TABLE test.t1;
# mysql_upgrade must have created mysql_upgrade_info file,
# so the following command should never fail.
--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
SET GLOBAL enforce_storage_engine=NULL;
--echo # End of 10.1 tests
--echo #
--echo # MDEV-23201 'Roles' are created incorrectly on an install that was previously MySQL 5.7
--echo #
--echo #
--exec $MYSQL_DUMP --result-file $MYSQLTEST_VARDIR/tmp/user.sql mysql user
DROP VIEW mysql.user;
--copy_file std_data/mysql57user.frm $MYSQLD_DATADIR/mysql/user.frm
--copy_file std_data/mysql57user.MYI $MYSQLD_DATADIR/mysql/user.MYI
--copy_file std_data/mysql57user.MYD $MYSQLD_DATADIR/mysql/user.MYD
FLUSH TABLES mysql.user;
FLUSH PRIVILEGES;
SHOW CREATE TABLE mysql.user;
--replace_result $MYSQLTEST_VARDIR var
--exec $MYSQL_UPGRADE --force 2>&1
SHOW CREATE TABLE mysql.user;
CREATE ROLE `aRole`;
SET ROLE `aRole`;
FLUSH PRIVILEGES;
SET ROLE `aRole`;
SELECT `User`, `is_role` FROM `mysql`.`user`;
DROP ROLE aRole;
--echo #
--echo # MDEV-24122 Fix previously MySQL-5.7 data directories that upgraded prior to MDEV-23201
--echo #
--source include/switch_to_mysql_user.inc
drop view mysql.user_bak;
drop table mysql.user;
truncate table mysql.tables_priv;
--copy_file std_data/mysql57user.frm $MYSQLD_DATADIR/mysql/user.frm
--copy_file std_data/mysql57user.MYI $MYSQLD_DATADIR/mysql/user.MYI
--copy_file std_data/mysql57user.MYD $MYSQLD_DATADIR/mysql/user.MYD
FLUSH TABLES mysql.user;
# What prior to MDEV-23201 would of done:
ALTER TABLE mysql.user ADD is_role enum('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;
ALTER TABLE mysql.user ADD default_role char(80) binary DEFAULT '' NOT NULL;
ALTER TABLE mysql.user ADD max_statement_time decimal(12,6) DEFAULT 0 NOT NULL;
FLUSH PRIVILEGES;
--exec $MYSQL_UPGRADE --force 2>&1
SHOW CREATE TABLE mysql.user;
CREATE ROLE `aRole`;
SET DEFAULT ROLE aRole;
SHOW GRANTS;
SET DEFAULT ROLE NONE;
SHOW GRANTS;
DROP ROLE `aRole`;
--exec $MYSQL mysql < $MYSQLTEST_VARDIR/tmp/user.sql
FLUSH PRIVILEGES;
--echo #
--echo # MDEV-27279: mariadb_upgrade add --check-if-upgrade-is-needed
--echo #
--error 1
--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --silent
--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB /
--error 1
--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed
--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / /'mysql.* as:[^\n]*/'mysql' as: mysql/
--error 1
--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --verbose
--echo #
--echo # MDEV-27279: mariadb_upgrade check-if-upgrade absence is do it
--echo #
--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
--replace_regex /[^ ]*mysql_upgrade_info/...mysql_upgrade_info/
--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed
--replace_regex /'mysql.* as:[^\n]*/'mysql' as: mysql/ /open .* Assuming/open XXX. Assuming/ /[^ ]*mysql_upgrade_info/...mysql_upgrade_info/
--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --verbose
--echo #
--echo # MDEV-27279: mariadb_upgrade check-if-upgrade with minor version change
--echo #
# take 3rd number of version and change to 0
let DATADIR= $MYSQLD_DATADIR;
perl;
my $ver= $ENV{'MYSQL_SERVER_VERSION'} or die "MYSQL_SERVER_VERSION not set";
my $file= $ENV{'DATADIR'} or die "MYSQLD_DATADIR not set";
$ver =~ s/^(\d*)\.(\d*).(\d*)(.*)/$1.$2.0$4/;
open(FILE, ">$file/mysql_upgrade_info") or die "Failed to open $file";
print FILE "$ver\n";
close(FILE);
EOF
--error 1
--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --silent
--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / /'mysql.* as:[^\n]*/'mysql' as: mysql/
--error 1
--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --verbose
--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB /
--exec $MYSQL_UPGRADE
--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
--echo #
--echo # MDEV-27279: mariadb_upgrade check-if-upgrade with major version change
--echo #
# take 2rd number of version and change to 0
let DATADIR= $MYSQLD_DATADIR;
perl;
my $ver= $ENV{'MYSQL_SERVER_VERSION'} or die "MYSQL_SERVER_VERSION not set";
my $file= $ENV{'DATADIR'} or die "MYSQLD_DATADIR not set";
$ver =~ s/^(\d*)\.(\d*).(\d*)(.*)/$1.0.$3$4/;
open(FILE, ">$file/mysql_upgrade_info") or die "Failed to open $file";
print FILE "$ver\n";
close(FILE);
EOF
--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --silent
--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB /
--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed
--replace_regex /\d\d\.\d*\.\d*[^ .\n]*/MariaDB / /'mysql.* as:[^\n]*/'mysql' as: mysql/
--exec $MYSQL_UPGRADE --check-if-upgrade-is-needed --verbose
--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
drop table mysql.global_priv;
rename table mysql.global_priv_bak to mysql.global_priv;
--echo # End of 10.2 tests
--echo #
--echo # Ensure that mysql_upgrade correctly sets truncate_versioning_priv
--echo # on upgrade from 10.2
--echo #
--source include/switch_to_mysql_user.inc
drop view mysql.user_bak;
CREATE USER 'user3'@'%';
GRANT USAGE ON *.* TO 'user3'@'%';
GRANT ALL PRIVILEGES ON `roelt`.`test2` TO 'user3'@'%';
alter table mysql.user drop column Delete_history_priv;
alter table mysql.db drop column Delete_history_priv;
--source include/restart_mysqld.inc
--echo Run mysql_upgrade with all privileges on a user
--exec $MYSQL_UPGRADE --force --silent 2>&1
--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
flush privileges;
SHOW GRANTS FOR 'user3'@'%';
DROP USER 'user3'@'%';
update mysql.db set Delete_history_priv='Y' where db like 'test%';
drop table mysql.global_priv;
rename table mysql.global_priv_bak to mysql.global_priv;
--echo # End of 10.3 tests
--source include/switch_to_mysql_user.inc
drop view mysql.user_bak;
create user 'user3'@'localhost' identified with mysql_native_password as password('a_password');
show create user user3@localhost;
update mysql.user set password=authentication_string, authentication_string='' where user='user3';
select password,plugin,authentication_string from mysql.user where user='user3';
flush privileges;
show create user user3@localhost;
connect con1,localhost,user3,a_password;
select current_user();
disconnect con1;
connection default;
--echo # mysql_upgrade --force --silent 2>&1
--exec $MYSQL_UPGRADE --force --silent 2>&1
--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
show create user user3@localhost;
connect con1,localhost,user3,a_password;
select current_user();
disconnect con1;
connection default;
drop user user3@localhost;
drop table mysql.global_priv;
rename table mysql.global_priv_bak to mysql.global_priv;
#
# MDEV-18275 Live upgrade from 5.5 does not work: InnoDB stat tables are used before creation
#
--source include/switch_to_mysql_user.inc
drop view mysql.user_bak;
drop table mysql.innodb_index_stats, mysql.innodb_table_stats;
--echo # mysql_upgrade --force --silent 2>&1
--exec $MYSQL_UPGRADE --force --silent 2>&1
--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
drop table mysql.global_priv;
rename table mysql.global_priv_bak to mysql.global_priv;
#
# MDEV-21244 mysql_upgrade creating empty global_priv table
#
--source include/switch_to_mysql_user.inc
drop view mysql.user_bak;
alter table mysql.user change authentication_string auth_string text collate utf8_bin not null;
--echo # mysql_upgrade --force --silent 2>&1
--exec $MYSQL_UPGRADE --force --silent 2>&1
--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
select count(*) from mysql.global_priv;
drop table mysql.global_priv;
rename table mysql.global_priv_bak to mysql.global_priv;
#
# MDEV-26363 Former mysql-5.7 tables have password_last_changed to 0
# on MariaDB updates, resulting in mysql_upgrade leaving them
# with password expired.
#
--echo #
--echo # Ensure that mysql_upgrade accounted for 0 password_last_changed
--echo # and doesn't PASSWORD EXPIRE a user account because < 10.4 zeroed it.
--echo #
--source include/switch_to_mysql_user.inc
drop view mysql.user_bak;
drop table mysql.user;
truncate table mysql.tables_priv;
--copy_file std_data/mysql57user.frm $MYSQLD_DATADIR/mysql/user.frm
--copy_file std_data/mysql57user.MYI $MYSQLD_DATADIR/mysql/user.MYI
--copy_file std_data/mysql57user.MYD $MYSQLD_DATADIR/mysql/user.MYD
FLUSH TABLES mysql.user;
FLUSH PRIVILEGES;
CREATE USER mariadb_102;
# manually set the value like <10.4 previously did for testing mysql_upgrade.
UPDATE mysql.user SET password_last_changed=0 WHERE user='mariadb_102';
FLUSH PRIVILEGES;
--exec $MYSQL_UPGRADE --force 2>&1
# Should not have "PASSWORD EXPIRED"
SHOW CREATE USER mariadb_102;
connect con1,localhost,mariadb_102;
select current_user();
disconnect con1;
connection default;
drop table mysql.global_priv;
rename table mysql.global_priv_bak to mysql.global_priv;
--remove_file $MYSQLD_DATADIR/mysql_upgrade_info
--echo # End of 10.4 tests
|