1-- Copyright (c) 2003, 2018, Oracle and/or its affiliates. All rights reserved. 2-- 3-- This program is free software; you can redistribute it and/or modify 4-- it under the terms of the GNU General Public License, version 2.0, 5-- as published by the Free Software Foundation. 6-- 7-- This program is also distributed with certain software (including 8-- but not limited to OpenSSL) that is licensed under separate terms, 9-- as designated in a particular file or component or in included license 10-- documentation. The authors of MySQL hereby grant you an additional 11-- permission to link the program and your derivative works with the 12-- separately licensed software that they have included with MySQL. 13-- 14-- This program is distributed in the hope that it will be useful, 15-- but WITHOUT ANY WARRANTY; without even the implied warranty of 16-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the 17-- GNU General Public License, version 2.0, for more details. 18-- 19-- You should have received a copy of the GNU General Public License 20-- along with this program; if not, write to the Free Software 21-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA 22 23# This part converts any old privilege tables to privilege tables suitable 24# for current version of MySQL 25 26# You can safely ignore all 'Duplicate column' and 'Unknown column' errors 27# because these just mean that your tables are already up to date. 28# This script is safe to run even if your tables are already up to date! 29 30# Warning message(s) produced for a statement can be printed by explicitly 31# adding a 'SHOW WARNINGS' after the statement. 32 33set sql_mode=''; 34set default_storage_engine=MyISAM; 35 36# Move distributed grant tables to default engine during upgrade, remember 37# which tables was moved so they can be moved back after upgrade 38SET @had_distributed_user = 39 (SELECT COUNT(table_name) FROM information_schema.tables 40 WHERE table_schema = 'mysql' AND table_name = 'user' AND 41 table_type = 'BASE TABLE' AND engine = 'NDBCLUSTER'); 42SET @cmd="ALTER TABLE mysql.user ENGINE=MyISAM"; 43SET @str = IF(@had_distributed_user > 0, @cmd, "SET @dummy = 0"); 44PREPARE stmt FROM @str; 45EXECUTE stmt; 46DROP PREPARE stmt; 47 48SET @had_distributed_db = 49 (SELECT COUNT(table_name) FROM information_schema.tables 50 WHERE table_schema = 'mysql' AND table_name = 'db' AND 51 table_type = 'BASE TABLE' AND engine = 'NDBCLUSTER'); 52SET @cmd="ALTER TABLE mysql.db ENGINE=MyISAM"; 53SET @str = IF(@had_distributed_db > 0, @cmd, "SET @dummy = 0"); 54PREPARE stmt FROM @str; 55EXECUTE stmt; 56DROP PREPARE stmt; 57 58SET @had_distributed_tables_priv = 59 (SELECT COUNT(table_name) FROM information_schema.tables 60 WHERE table_schema = 'mysql' AND table_name = 'tables_priv' AND 61 table_type = 'BASE TABLE' AND engine = 'NDBCLUSTER'); 62SET @cmd="ALTER TABLE mysql.tables_priv ENGINE=MyISAM"; 63SET @str = IF(@had_distributed_tables_priv > 0, @cmd, "SET @dummy = 0"); 64PREPARE stmt FROM @str; 65EXECUTE stmt; 66DROP PREPARE stmt; 67 68SET @had_distributed_columns_priv = 69 (SELECT COUNT(table_name) FROM information_schema.tables 70 WHERE table_schema = 'mysql' AND table_name = 'columns_priv' AND 71 table_type = 'BASE TABLE' AND engine = 'NDBCLUSTER'); 72SET @cmd="ALTER TABLE mysql.columns_priv ENGINE=MyISAM"; 73SET @str = IF(@had_distributed_columns_priv > 0, @cmd, "SET @dummy = 0"); 74PREPARE stmt FROM @str; 75EXECUTE stmt; 76DROP PREPARE stmt; 77 78SET @had_distributed_procs_priv = 79 (SELECT COUNT(table_name) FROM information_schema.tables 80 WHERE table_schema = 'mysql' AND table_name = 'procs_priv' AND 81 table_type = 'BASE TABLE' AND engine = 'NDBCLUSTER'); 82SET @cmd="ALTER TABLE mysql.procs_priv ENGINE=MyISAM"; 83SET @str = IF(@had_distributed_procs_priv > 0, @cmd, "SET @dummy = 0"); 84PREPARE stmt FROM @str; 85EXECUTE stmt; 86DROP PREPARE stmt; 87 88SET @had_distributed_proxies_priv = 89 (SELECT COUNT(table_name) FROM information_schema.tables 90 WHERE table_schema = 'mysql' AND table_name = 'proxies_priv' AND 91 table_type = 'BASE TABLE' AND engine = 'NDBCLUSTER' ); 92SET @cmd="ALTER TABLE mysql.proxies_priv ENGINE=MyISAM"; 93SET @str = IF(@had_distributed_proxies_priv > 0, @cmd, "SET @dummy = 0"); 94PREPARE stmt FROM @str; 95EXECUTE stmt; 96DROP PREPARE stmt; 97 98ALTER TABLE user add File_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL; 99 100# Detect whether or not we had the Grant_priv column 101SET @hadGrantPriv:=0; 102SELECT @hadGrantPriv:=1 FROM user WHERE Grant_priv LIKE '%'; 103 104ALTER TABLE user add Grant_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add References_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Index_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Alter_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL; 105ALTER TABLE db add Grant_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add References_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Index_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL,add Alter_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL; 106 107# Fix privileges for old tables 108UPDATE user SET Grant_priv=File_priv,References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv WHERE @hadGrantPriv = 0; 109UPDATE db SET References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv WHERE @hadGrantPriv = 0; 110 111# 112# The second alter changes ssl_type to new 4.0.2 format 113# Adding columns needed by GRANT .. REQUIRE (openssl) 114 115ALTER TABLE user 116ADD ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci NOT NULL, 117ADD ssl_cipher BLOB NOT NULL, 118ADD x509_issuer BLOB NOT NULL, 119ADD x509_subject BLOB NOT NULL; 120ALTER TABLE user MODIFY ssl_type enum('','ANY','X509', 'SPECIFIED') NOT NULL; 121 122# 123# tables_priv 124# 125ALTER TABLE tables_priv 126 ADD KEY Grantor (Grantor); 127 128ALTER TABLE tables_priv 129 MODIFY Host char(60) NOT NULL default '', 130 MODIFY Db char(64) NOT NULL default '', 131 MODIFY User char(32) NOT NULL default '', 132 MODIFY Table_name char(64) NOT NULL default '', 133 ENGINE=MyISAM, 134 CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; 135 136ALTER TABLE tables_priv 137 MODIFY Column_priv set('Select','Insert','Update','References') 138 COLLATE utf8_general_ci DEFAULT '' NOT NULL, 139 MODIFY Table_priv set('Select','Insert','Update','Delete','Create', 140 'Drop','Grant','References','Index','Alter', 141 'Create View','Show view','Trigger') 142 COLLATE utf8_general_ci DEFAULT '' NOT NULL, 143 COMMENT='Table privileges'; 144 145ALTER TABLE tables_priv 146 MODIFY Grantor char(93) NOT NULL default ''; 147 148# 149# columns_priv 150# 151# 152# Name change of Type -> Column_priv from MySQL 3.22.12 153# 154ALTER TABLE columns_priv 155 CHANGE Type Column_priv set('Select','Insert','Update','References') 156 COLLATE utf8_general_ci DEFAULT '' NOT NULL; 157 158ALTER TABLE columns_priv 159 MODIFY Host char(60) NOT NULL default '', 160 MODIFY Db char(64) NOT NULL default '', 161 MODIFY User char(32) NOT NULL default '', 162 MODIFY Table_name char(64) NOT NULL default '', 163 MODIFY Column_name char(64) NOT NULL default '', 164 ENGINE=MyISAM, 165 CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin, 166 COMMENT='Column privileges'; 167 168ALTER TABLE columns_priv 169 MODIFY Column_priv set('Select','Insert','Update','References') 170 COLLATE utf8_general_ci DEFAULT '' NOT NULL; 171 172# 173# Add the new 'type' column to the func table. 174# 175 176ALTER TABLE func add type enum ('function','aggregate') COLLATE utf8_general_ci NOT NULL; 177 178# 179# Change the user and db tables to current format 180# 181 182# Detect whether we had Show_db_priv 183SET @hadShowDbPriv:=0; 184SELECT @hadShowDbPriv:=1 FROM user WHERE Show_db_priv LIKE '%'; 185 186ALTER TABLE user 187ADD Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_priv, 188ADD Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_db_priv, 189ADD Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Super_priv, 190ADD Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_tmp_table_priv, 191ADD Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Lock_tables_priv, 192ADD Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Execute_priv, 193ADD Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Repl_slave_priv; 194 195# Convert privileges so that users have similar privileges as before 196 197UPDATE user SET Show_db_priv= Select_priv, Super_priv=Process_priv, Execute_priv=Process_priv, Create_tmp_table_priv='Y', Lock_tables_priv='Y', Repl_slave_priv=file_priv, Repl_client_priv=File_priv where user<>"" AND @hadShowDbPriv = 0; 198 199 200# Add fields that can be used to limit number of questions and connections 201# for some users. 202 203ALTER TABLE user 204ADD max_questions int(11) NOT NULL DEFAULT 0 AFTER x509_subject, 205ADD max_updates int(11) unsigned NOT NULL DEFAULT 0 AFTER max_questions, 206ADD max_connections int(11) unsigned NOT NULL DEFAULT 0 AFTER max_updates; 207 208# 209# Update proxies_priv definition. 210# 211ALTER TABLE proxies_priv MODIFY User char(32) binary DEFAULT '' NOT NULL; 212ALTER TABLE proxies_priv MODIFY Proxied_user char(32) binary DEFAULT '' NOT NULL; 213ALTER TABLE proxies_priv MODIFY Grantor char(93) DEFAULT '' NOT NULL; 214 215# 216# Add Create_tmp_table_priv and Lock_tables_priv to db 217# 218 219ALTER TABLE db 220ADD Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 221ADD Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; 222 223alter table user change max_questions max_questions int(11) unsigned DEFAULT 0 NOT NULL; 224 225 226alter table db comment='Database privileges'; 227alter table user comment='Users and global privileges'; 228alter table func comment='User defined functions'; 229 230# Convert all tables to UTF-8 with binary collation 231# and reset all char columns to correct width 232ALTER TABLE user 233 MODIFY Host char(60) NOT NULL default '', 234 MODIFY User char(32) NOT NULL default '', 235 ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; 236ALTER TABLE user 237 MODIFY Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 238 MODIFY Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 239 MODIFY Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 240 MODIFY Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 241 MODIFY Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 242 MODIFY Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 243 MODIFY Reload_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 244 MODIFY Shutdown_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 245 MODIFY Process_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 246 MODIFY File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 247 MODIFY Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 248 MODIFY References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 249 MODIFY Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 250 MODIFY Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 251 MODIFY Show_db_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 252 MODIFY Super_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 253 MODIFY Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 254 MODIFY Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 255 MODIFY Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 256 MODIFY Repl_slave_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 257 MODIFY Repl_client_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 258 MODIFY ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci DEFAULT '' NOT NULL; 259 260ALTER TABLE db 261 MODIFY Host char(60) NOT NULL default '', 262 MODIFY Db char(64) NOT NULL default '', 263 MODIFY User char(32) NOT NULL default '', 264 ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; 265ALTER TABLE db 266 MODIFY Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 267 MODIFY Insert_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 268 MODIFY Update_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 269 MODIFY Delete_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 270 MODIFY Create_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 271 MODIFY Drop_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 272 MODIFY Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 273 MODIFY References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 274 MODIFY Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 275 MODIFY Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 276 MODIFY Create_tmp_table_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL, 277 MODIFY Lock_tables_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; 278 279ALTER TABLE func 280 ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; 281ALTER TABLE func 282 MODIFY type enum ('function','aggregate') COLLATE utf8_general_ci NOT NULL; 283 284# 285# Modify log tables. 286# 287 288SET @old_log_state = @@global.general_log; 289SET GLOBAL general_log = 'OFF'; 290ALTER TABLE general_log 291 MODIFY event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), 292 MODIFY user_host MEDIUMTEXT NOT NULL, 293 MODIFY thread_id INTEGER NOT NULL, 294 MODIFY server_id INTEGER UNSIGNED NOT NULL, 295 MODIFY command_type VARCHAR(64) NOT NULL, 296 MODIFY argument MEDIUMBLOB NOT NULL; 297ALTER TABLE general_log 298 MODIFY thread_id BIGINT(21) UNSIGNED NOT NULL; 299SET GLOBAL general_log = @old_log_state; 300 301SET @old_log_state = @@global.slow_query_log; 302SET GLOBAL slow_query_log = 'OFF'; 303ALTER TABLE slow_log 304 MODIFY start_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), 305 MODIFY user_host MEDIUMTEXT NOT NULL, 306 MODIFY query_time TIME(6) NOT NULL, 307 MODIFY lock_time TIME(6) NOT NULL, 308 MODIFY rows_sent INTEGER NOT NULL, 309 MODIFY rows_examined INTEGER NOT NULL, 310 MODIFY db VARCHAR(512) NOT NULL, 311 MODIFY last_insert_id INTEGER NOT NULL, 312 MODIFY insert_id INTEGER NOT NULL, 313 MODIFY server_id INTEGER UNSIGNED NOT NULL, 314 MODIFY sql_text MEDIUMBLOB NOT NULL; 315ALTER TABLE slow_log 316 ADD COLUMN thread_id INTEGER NOT NULL AFTER sql_text; 317ALTER TABLE slow_log 318 MODIFY thread_id BIGINT(21) UNSIGNED NOT NULL; 319SET GLOBAL slow_query_log = @old_log_state; 320 321ALTER TABLE plugin 322 MODIFY name varchar(64) COLLATE utf8_general_ci NOT NULL DEFAULT '', 323 MODIFY dl varchar(128) COLLATE utf8_general_ci NOT NULL DEFAULT '', 324 CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; 325 326# 327# Detect whether we had Create_view_priv 328# 329SET @hadCreateViewPriv:=0; 330SELECT @hadCreateViewPriv:=1 FROM user WHERE Create_view_priv LIKE '%'; 331 332# 333# Create VIEWs privileges (v5.0) 334# 335ALTER TABLE db ADD Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Lock_tables_priv; 336ALTER TABLE db MODIFY Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Lock_tables_priv; 337 338ALTER TABLE user ADD Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Repl_client_priv; 339ALTER TABLE user MODIFY Create_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Repl_client_priv; 340 341# 342# Show VIEWs privileges (v5.0) 343# 344ALTER TABLE db ADD Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_view_priv; 345ALTER TABLE db MODIFY Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_view_priv; 346 347ALTER TABLE user ADD Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_view_priv; 348ALTER TABLE user MODIFY Show_view_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_view_priv; 349 350# 351# Assign create/show view privileges to people who have create provileges 352# 353UPDATE user SET Create_view_priv=Create_priv, Show_view_priv=Create_priv where user<>"" AND @hadCreateViewPriv = 0; 354 355# 356# 357# 358SET @hadCreateRoutinePriv:=0; 359SELECT @hadCreateRoutinePriv:=1 FROM user WHERE Create_routine_priv LIKE '%'; 360 361# 362# Create PROCEDUREs privileges (v5.0) 363# 364ALTER TABLE db ADD Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_view_priv; 365ALTER TABLE db MODIFY Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_view_priv; 366 367ALTER TABLE user ADD Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_view_priv; 368ALTER TABLE user MODIFY Create_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Show_view_priv; 369 370# 371# Alter PROCEDUREs privileges (v5.0) 372# 373ALTER TABLE db ADD Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_routine_priv; 374ALTER TABLE db MODIFY Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_routine_priv; 375 376ALTER TABLE user ADD Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_routine_priv; 377ALTER TABLE user MODIFY Alter_routine_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Create_routine_priv; 378 379ALTER TABLE db ADD Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv; 380ALTER TABLE db MODIFY Execute_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv; 381 382# 383# Assign create/alter routine privileges to people who have create privileges 384# 385UPDATE user SET Create_routine_priv=Create_priv, Alter_routine_priv=Alter_priv where user<>"" AND @hadCreateRoutinePriv = 0; 386UPDATE db SET Create_routine_priv=Create_priv, Alter_routine_priv=Alter_priv, Execute_priv=Select_priv where user<>"" AND @hadCreateRoutinePriv = 0; 387 388# 389# Add max_user_connections resource limit 390# 391ALTER TABLE user ADD max_user_connections int(11) unsigned DEFAULT '0' NOT NULL AFTER max_connections; 392 393# 394# user.Create_user_priv 395# 396 397SET @hadCreateUserPriv:=0; 398SELECT @hadCreateUserPriv:=1 FROM user WHERE Create_user_priv LIKE '%'; 399 400ALTER TABLE user ADD Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv; 401ALTER TABLE user MODIFY Create_user_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Alter_routine_priv; 402UPDATE user LEFT JOIN db USING (Host,User) SET Create_user_priv='Y' 403 WHERE @hadCreateUserPriv = 0 AND 404 (user.Grant_priv = 'Y' OR db.Grant_priv = 'Y'); 405 406# 407# procs_priv 408# 409 410ALTER TABLE procs_priv 411 MODIFY User char(32) NOT NULL default '', 412 ENGINE=MyISAM, 413 CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin; 414 415ALTER TABLE procs_priv 416 MODIFY Proc_priv set('Execute','Alter Routine','Grant') 417 COLLATE utf8_general_ci DEFAULT '' NOT NULL; 418 419ALTER TABLE procs_priv 420 MODIFY Routine_name char(64) 421 COLLATE utf8_general_ci DEFAULT '' NOT NULL; 422 423ALTER TABLE procs_priv 424 ADD Routine_type enum('FUNCTION','PROCEDURE') 425 COLLATE utf8_general_ci NOT NULL AFTER Routine_name; 426 427ALTER TABLE procs_priv 428 MODIFY Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER Proc_priv; 429 430ALTER TABLE procs_priv 431 MODIFY Grantor char(93) DEFAULT '' NOT NULL; 432# 433# proc 434# 435 436# Correct the name fields to not binary, and expand sql_data_access 437ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL, 438 MODIFY specific_name char(64) DEFAULT '' NOT NULL, 439 MODIFY sql_data_access 440 enum('CONTAINS_SQL', 441 'NO_SQL', 442 'READS_SQL_DATA', 443 'MODIFIES_SQL_DATA' 444 ) DEFAULT 'CONTAINS_SQL' NOT NULL, 445 MODIFY body longblob NOT NULL, 446 MODIFY returns longblob NOT NULL, 447 MODIFY sql_mode 448 set('REAL_AS_FLOAT', 449 'PIPES_AS_CONCAT', 450 'ANSI_QUOTES', 451 'IGNORE_SPACE', 452 'NOT_USED', 453 'ONLY_FULL_GROUP_BY', 454 'NO_UNSIGNED_SUBTRACTION', 455 'NO_DIR_IN_CREATE', 456 'POSTGRESQL', 457 'ORACLE', 458 'MSSQL', 459 'DB2', 460 'MAXDB', 461 'NO_KEY_OPTIONS', 462 'NO_TABLE_OPTIONS', 463 'NO_FIELD_OPTIONS', 464 'MYSQL323', 465 'MYSQL40', 466 'ANSI', 467 'NO_AUTO_VALUE_ON_ZERO', 468 'NO_BACKSLASH_ESCAPES', 469 'STRICT_TRANS_TABLES', 470 'STRICT_ALL_TABLES', 471 'NO_ZERO_IN_DATE', 472 'NO_ZERO_DATE', 473 'INVALID_DATES', 474 'ERROR_FOR_DIVISION_BY_ZERO', 475 'TRADITIONAL', 476 'NO_AUTO_CREATE_USER', 477 'HIGH_NOT_PRECEDENCE', 478 'NO_ENGINE_SUBSTITUTION', 479 'PAD_CHAR_TO_FULL_LENGTH' 480 ) DEFAULT '' NOT NULL, 481 DEFAULT CHARACTER SET utf8; 482 483# Correct the character set and collation 484ALTER TABLE proc CONVERT TO CHARACTER SET utf8; 485# Reset some fields after the conversion and change comment from char(64) to text 486ALTER TABLE proc MODIFY db 487 char(64) collate utf8_bin DEFAULT '' NOT NULL, 488 MODIFY definer 489 char(93) collate utf8_bin DEFAULT '' NOT NULL, 490 MODIFY comment 491 text collate utf8_bin DEFAULT '' NOT NULL; 492 493ALTER TABLE proc ADD character_set_client 494 char(32) collate utf8_bin DEFAULT NULL 495 AFTER comment; 496ALTER TABLE proc MODIFY character_set_client 497 char(32) collate utf8_bin DEFAULT NULL; 498 499SELECT CASE WHEN COUNT(*) > 0 THEN 500CONCAT ("WARNING: NULL values of the 'character_set_client' column ('mysql.proc' table) have been updated with a default value (", @@character_set_client, "). Please verify if necessary.") 501ELSE NULL 502END 503AS value FROM proc WHERE character_set_client IS NULL; 504 505UPDATE proc SET character_set_client = @@character_set_client 506 WHERE character_set_client IS NULL; 507 508ALTER TABLE proc ADD collation_connection 509 char(32) collate utf8_bin DEFAULT NULL 510 AFTER character_set_client; 511ALTER TABLE proc MODIFY collation_connection 512 char(32) collate utf8_bin DEFAULT NULL; 513 514SELECT CASE WHEN COUNT(*) > 0 THEN 515CONCAT ("WARNING: NULL values of the 'collation_connection' column ('mysql.proc' table) have been updated with a default value (", @@collation_connection, "). Please verify if necessary.") 516ELSE NULL 517END 518AS value FROM proc WHERE collation_connection IS NULL; 519 520UPDATE proc SET collation_connection = @@collation_connection 521 WHERE collation_connection IS NULL; 522 523ALTER TABLE proc ADD db_collation 524 char(32) collate utf8_bin DEFAULT NULL 525 AFTER collation_connection; 526ALTER TABLE proc MODIFY db_collation 527 char(32) collate utf8_bin DEFAULT NULL; 528 529SELECT CASE WHEN COUNT(*) > 0 THEN 530CONCAT ("WARNING: NULL values of the 'db_collation' column ('mysql.proc' table) have been updated with default values. Please verify if necessary.") 531ELSE NULL 532END 533AS value FROM proc WHERE db_collation IS NULL; 534 535UPDATE proc AS p SET db_collation = 536 ( SELECT DEFAULT_COLLATION_NAME 537 FROM INFORMATION_SCHEMA.SCHEMATA 538 WHERE SCHEMA_NAME = p.db) 539 WHERE db_collation IS NULL; 540 541ALTER TABLE proc ADD body_utf8 longblob DEFAULT NULL 542 AFTER db_collation; 543ALTER TABLE proc MODIFY body_utf8 longblob DEFAULT NULL; 544 545# 546# EVENT privilege 547# 548SET @hadEventPriv := 0; 549SELECT @hadEventPriv :=1 FROM user WHERE Event_priv LIKE '%'; 550 551ALTER TABLE user add Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL AFTER Create_user_priv; 552ALTER TABLE user MODIFY Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL AFTER Create_user_priv; 553 554UPDATE user SET Event_priv=Super_priv WHERE @hadEventPriv = 0; 555 556ALTER TABLE db add Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL; 557ALTER TABLE db MODIFY Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL; 558 559# 560# EVENT table 561# 562ALTER TABLE event DROP PRIMARY KEY; 563ALTER TABLE event ADD PRIMARY KEY(db, name); 564# Add sql_mode column just in case. 565ALTER TABLE event ADD sql_mode set ('NOT_USED') AFTER on_completion; 566# Update list of sql_mode values. 567ALTER TABLE event MODIFY sql_mode 568 set('REAL_AS_FLOAT', 569 'PIPES_AS_CONCAT', 570 'ANSI_QUOTES', 571 'IGNORE_SPACE', 572 'NOT_USED', 573 'ONLY_FULL_GROUP_BY', 574 'NO_UNSIGNED_SUBTRACTION', 575 'NO_DIR_IN_CREATE', 576 'POSTGRESQL', 577 'ORACLE', 578 'MSSQL', 579 'DB2', 580 'MAXDB', 581 'NO_KEY_OPTIONS', 582 'NO_TABLE_OPTIONS', 583 'NO_FIELD_OPTIONS', 584 'MYSQL323', 585 'MYSQL40', 586 'ANSI', 587 'NO_AUTO_VALUE_ON_ZERO', 588 'NO_BACKSLASH_ESCAPES', 589 'STRICT_TRANS_TABLES', 590 'STRICT_ALL_TABLES', 591 'NO_ZERO_IN_DATE', 592 'NO_ZERO_DATE', 593 'INVALID_DATES', 594 'ERROR_FOR_DIVISION_BY_ZERO', 595 'TRADITIONAL', 596 'NO_AUTO_CREATE_USER', 597 'HIGH_NOT_PRECEDENCE', 598 'NO_ENGINE_SUBSTITUTION', 599 'PAD_CHAR_TO_FULL_LENGTH' 600 ) DEFAULT '' NOT NULL AFTER on_completion; 601ALTER TABLE event MODIFY name char(64) CHARACTER SET utf8 NOT NULL default ''; 602 603ALTER TABLE event MODIFY COLUMN originator INT UNSIGNED NOT NULL; 604ALTER TABLE event ADD COLUMN originator INT UNSIGNED NOT NULL AFTER comment; 605 606ALTER TABLE event MODIFY COLUMN status ENUM('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL default 'ENABLED'; 607 608ALTER TABLE event ADD COLUMN time_zone char(64) CHARACTER SET latin1 609 NOT NULL DEFAULT 'SYSTEM' AFTER originator; 610 611ALTER TABLE event ADD character_set_client 612 char(32) collate utf8_bin DEFAULT NULL 613 AFTER time_zone; 614ALTER TABLE event MODIFY character_set_client 615 char(32) collate utf8_bin DEFAULT NULL; 616 617ALTER TABLE event ADD collation_connection 618 char(32) collate utf8_bin DEFAULT NULL 619 AFTER character_set_client; 620ALTER TABLE event MODIFY collation_connection 621 char(32) collate utf8_bin DEFAULT NULL; 622 623ALTER TABLE event ADD db_collation 624 char(32) collate utf8_bin DEFAULT NULL 625 AFTER collation_connection; 626ALTER TABLE event MODIFY db_collation 627 char(32) collate utf8_bin DEFAULT NULL; 628 629ALTER TABLE event ADD body_utf8 longblob DEFAULT NULL 630 AFTER db_collation; 631ALTER TABLE event MODIFY body_utf8 longblob DEFAULT NULL; 632 633ALTER TABLE event MODIFY definer char(93) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL default ''; 634 635# 636# TRIGGER privilege 637# 638 639SET @hadTriggerPriv := 0; 640SELECT @hadTriggerPriv :=1 FROM user WHERE Trigger_priv LIKE '%'; 641 642ALTER TABLE user ADD Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Event_priv; 643ALTER TABLE user MODIFY Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Event_priv; 644 645ALTER TABLE db ADD Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; 646ALTER TABLE db MODIFY Trigger_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; 647 648UPDATE user SET Trigger_priv=Super_priv WHERE @hadTriggerPriv = 0; 649 650# 651# user.Create_tablespace_priv 652# 653 654SET @hadCreateTablespacePriv := 0; 655SELECT @hadCreateTablespacePriv :=1 FROM user WHERE Create_tablespace_priv LIKE '%'; 656 657ALTER TABLE user ADD Create_tablespace_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Trigger_priv; 658ALTER TABLE user MODIFY Create_tablespace_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL AFTER Trigger_priv; 659 660UPDATE user SET Create_tablespace_priv = Super_priv WHERE @hadCreateTablespacePriv = 0; 661 662-- 663-- Unlike 'performance_schema', the 'mysql' database is reserved already, 664-- so no user procedure is supposed to be there. 665-- 666-- NOTE: until upgrade is finished, stored routines are not available, 667-- because system tables (e.g. mysql.proc) might be not usable. 668-- 669drop procedure if exists mysql.die; 670create procedure mysql.die() signal sqlstate 'HY000' set message_text='Unexpected content found in the performance_schema database.'; 671 672-- 673-- For broken upgrades, SIGNAL the error 674-- 675 676SET @cmd="call mysql.die()"; 677 678SET @str = IF(@broken_pfs > 0, @cmd, 'SET @dummy = 0'); 679PREPARE stmt FROM @str; 680EXECUTE stmt; 681DROP PREPARE stmt; 682 683drop procedure mysql.die; 684 685ALTER TABLE user ADD plugin char(64) DEFAULT 'mysql_native_password' NOT NULL, ADD authentication_string TEXT; 686ALTER TABLE user MODIFY plugin char(64) DEFAULT 'mysql_native_password' NOT NULL; 687UPDATE user SET plugin=IF((length(password) = 41) OR (length(password) = 0), 'mysql_native_password', '') WHERE plugin = ''; 688ALTER TABLE user MODIFY authentication_string TEXT; 689 690-- establish if the field is already there. 691SET @hadPasswordExpired:=0; 692SELECT @hadPasswordExpired:=1 FROM user WHERE password_expired LIKE '%'; 693 694ALTER TABLE user ADD password_expired ENUM('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; 695UPDATE user SET password_expired = 'N' WHERE @hadPasswordExpired=0; 696 697-- need to compensate for the ALTER TABLE user .. CONVERT TO CHARACTER SET above 698ALTER TABLE user MODIFY password_expired ENUM('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; 699 700-- Need to pre-fill mysql.proxies_priv with access for root even when upgrading from 701-- older versions 702 703CREATE TEMPORARY TABLE tmp_proxies_priv LIKE proxies_priv; 704INSERT INTO tmp_proxies_priv VALUES ('localhost', 'root', '', '', TRUE, '', now()); 705INSERT INTO proxies_priv SELECT * FROM tmp_proxies_priv WHERE @had_proxies_priv_table=0; 706DROP TABLE tmp_proxies_priv; 707 708-- Checking for any duplicate hostname and username combination are exists. 709-- If exits we will throw error. 710DROP PROCEDURE IF EXISTS mysql.warn_duplicate_host_names; 711CREATE PROCEDURE mysql.warn_duplicate_host_names() SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Multiple accounts exist for @user_name, @host_name that differ only in Host lettercase; remove all except one of them'; 712SET @cmd='call mysql.warn_duplicate_host_names()'; 713SET @duplicate_hosts=(SELECT count(*) FROM mysql.user GROUP BY user, lower(host) HAVING count(*) > 1 LIMIT 1); 714SET @str=IF(@duplicate_hosts > 1, @cmd, 'SET @dummy=0'); 715 716PREPARE stmt FROM @str; 717EXECUTE stmt; 718-- Get warnings (if any) 719SHOW WARNINGS; 720DROP PREPARE stmt; 721DROP PROCEDURE mysql.warn_duplicate_host_names; 722 723# Convering the host name to lower case for existing users 724UPDATE user SET host=LOWER( host ) WHERE LOWER( host ) <> host; 725 726# 727# mysql.ndb_binlog_index 728# 729# Change type from BIGINT to INT 730ALTER TABLE ndb_binlog_index 731 MODIFY inserts INT UNSIGNED NOT NULL, 732 MODIFY updates INT UNSIGNED NOT NULL, 733 MODIFY deletes INT UNSIGNED NOT NULL, 734 MODIFY schemaops INT UNSIGNED NOT NULL; 735# Add new columns 736ALTER TABLE ndb_binlog_index 737 ADD orig_server_id INT UNSIGNED NOT NULL, 738 ADD orig_epoch BIGINT UNSIGNED NOT NULL, 739 ADD gci INT UNSIGNED NOT NULL; 740# New primary key 741ALTER TABLE ndb_binlog_index 742 DROP PRIMARY KEY, 743 ADD PRIMARY KEY(epoch, orig_server_id, orig_epoch); 744 745-- 746-- Check for accounts with old pre-4.1 passwords and issue a warning 747-- 748 749-- SCRAMBLED_PASSWORD_CHAR_LENGTH_323 = 16 750SET @deprecated_pwds=(SELECT COUNT(*) FROM mysql.user WHERE LENGTH(password) = 16); 751 752-- signal the deprecation error 753DROP PROCEDURE IF EXISTS mysql.warn_pre41_pwd; 754CREATE PROCEDURE mysql.warn_pre41_pwd() SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT='Pre-4.1 password hash found. It is deprecated and will be removed in a future release. Please upgrade it to a new format.'; 755SET @cmd='call mysql.warn_pre41_pwd()'; 756SET @str=IF(@deprecated_pwds > 0, @cmd, 'SET @dummy=0'); 757PREPARE stmt FROM @str; 758EXECUTE stmt; 759-- Get warnings (if any) 760SHOW WARNINGS; 761DROP PREPARE stmt; 762DROP PROCEDURE mysql.warn_pre41_pwd; 763 764-- 765-- Add timestamp and expiry columns 766-- 767 768ALTER TABLE user ADD password_last_changed timestamp NULL; 769UPDATE user SET password_last_changed = CURRENT_TIMESTAMP WHERE plugin in ('mysql_native_password','sha256_password') and password_last_changed is NULL; 770 771ALTER TABLE user ADD password_lifetime smallint unsigned NULL; 772 773-- 774-- Add account_locked column 775-- 776SET @hadAccountLocked:=0; 777SELECT @hadAccountLocked:=1 FROM user WHERE account_locked LIKE '%'; 778 779ALTER TABLE user ADD account_locked ENUM('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; 780UPDATE user SET account_locked = 'N' WHERE @hadAccountLocked=0; 781 782-- need to compensate for the ALTER TABLE user .. CONVERT TO CHARACTER SET above 783ALTER TABLE user MODIFY account_locked ENUM('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL; 784 785-- 786-- Drop password column 787-- 788 789SET @have_password= (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'mysql' 790 AND TABLE_NAME='user' 791 AND column_name='password'); 792SET @str=IF(@have_password <> 0, "UPDATE user SET authentication_string = password where LENGTH(password) > 0 and plugin = 'mysql_native_password'", "SET @dummy = 0"); 793# We have already put mysql_native_password as plugin value in cases where length(PASSWORD) is either 0 or 41. 794PREPARE stmt FROM @str; 795EXECUTE stmt; 796DROP PREPARE stmt; 797SET @str=IF(@have_password <> 0, "ALTER TABLE user DROP password", "SET @dummy = 0"); 798PREPARE stmt FROM @str; 799EXECUTE stmt; 800DROP PREPARE stmt; 801 802# Activate the new, possible modified privilege tables 803# This should not be needed, but gives us some extra testing that the above 804# changes was correct 805 806flush privileges; 807 808ALTER TABLE slave_master_info ADD Ssl_crl TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The file used for the Certificate Revocation List (CRL)'; 809ALTER TABLE slave_master_info ADD Ssl_crlpath TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'The path used for Certificate Revocation List (CRL) files'; 810ALTER TABLE slave_master_info STATS_PERSISTENT=0; 811ALTER TABLE slave_worker_info STATS_PERSISTENT=0; 812ALTER TABLE slave_relay_log_info STATS_PERSISTENT=0; 813ALTER TABLE gtid_executed STATS_PERSISTENT=0; 814 815# 816# From 5.7 onwards, all slave info tables have Channel_Name as a column. 817# This column is needed for multi-source replication 818# 819ALTER TABLE slave_master_info 820 ADD Channel_name CHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT 'The channel on which the slave is connected to a source. Used in Multisource Replication', 821 DROP PRIMARY KEY, 822 ADD PRIMARY KEY(Channel_name); 823 824ALTER TABLE slave_relay_log_info 825 ADD Channel_name CHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT 'The channel on which the slave is connected to a source. Used in Multisource Replication', 826 DROP PRIMARY KEY, 827 ADD PRIMARY KEY(Channel_name); 828 829ALTER TABLE slave_worker_info 830 ADD Channel_name CHAR(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT 'The channel on which the slave is connected to a source. Used in Multisource Replication', 831 DROP PRIMARY KEY, 832 ADD PRIMARY KEY(Channel_name, Id); 833 834# The Tls_version field at slave_master_info should be added after the Channel_name field 835ALTER TABLE slave_master_info ADD Tls_version TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Tls version'; 836 837# If the order of columns Channel_name and Tls_version is wrong, this will correct the order 838# in slave_master_info table. 839ALTER TABLE slave_master_info 840 MODIFY COLUMN Tls_version TEXT CHARACTER SET utf8 COLLATE utf8_bin COMMENT 'Tls version' 841 AFTER Channel_name; 842 843SET @have_innodb= (SELECT COUNT(engine) FROM information_schema.engines WHERE engine='InnoDB' AND support != 'NO'); 844SET @str=IF(@have_innodb <> 0, "ALTER TABLE innodb_table_stats STATS_PERSISTENT=0", "SET @dummy = 0"); 845PREPARE stmt FROM @str; 846EXECUTE stmt; 847DROP PREPARE stmt; 848 849SET @str=IF(@have_innodb <> 0, "ALTER TABLE innodb_index_stats STATS_PERSISTENT=0", "SET @dummy = 0"); 850PREPARE stmt FROM @str; 851EXECUTE stmt; 852DROP PREPARE stmt; 853 854# 855# ndb_binlog_index table 856# 857ALTER TABLE ndb_binlog_index 858 ADD COLUMN next_position BIGINT UNSIGNED NOT NULL; 859ALTER TABLE ndb_binlog_index 860 ADD COLUMN next_file VARCHAR(255) NOT NULL; 861 862-- 863-- Check for non-empty host table and issue a warning 864-- 865 866DROP PROCEDURE IF EXISTS mysql.warn_host_table_nonempty; 867CREATE PROCEDURE mysql.warn_host_table_nonempty() SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT='Table mysql.host is not empty. It is deprecated and will be removed in a future release.'; 868SET @cmd='call mysql.warn_host_table_nonempty()'; 869 870SET @have_host_table=0; 871SET @host_table_nonempty=0; 872SET @have_host_table=(SELECT COUNT(*) FROM information_schema.tables WHERE table_name LIKE 'host' AND table_schema LIKE 'mysql' AND table_type LIKE 'BASE TABLE'); 873 874SET @host_table_nonempty_str=IF(@have_host_table > 0, 'SET @host_table_nonempty=(SELECT COUNT(*) FROM mysql.host)', 'SET @dummy=0'); 875PREPARE stmt FROM @host_table_nonempty_str; 876EXECUTE stmt; 877DROP PREPARE stmt; 878 879SET @str=IF(@host_table_nonempty > 0, @cmd, 'SET @dummy=0'); 880 881PREPARE stmt FROM @str; 882EXECUTE stmt; 883-- Get warnings (if any) 884SHOW WARNINGS; 885DROP PREPARE stmt; 886DROP PROCEDURE mysql.warn_host_table_nonempty; 887 888-- 889-- Upgrade help tables 890-- 891 892ALTER TABLE help_category MODIFY url TEXT NOT NULL; 893ALTER TABLE help_topic MODIFY url TEXT NOT NULL; 894 895-- 896-- Upgrade a table engine from MyISAM to InnoDB for the system tables 897-- help_topic, help_category, help_relation, help_keyword, plugin, servers, 898-- time_zone, time_zone_leap_second, time_zone_name, time_zone_transition, 899-- time_zone_transition_type. 900 901ALTER TABLE help_topic ENGINE=InnoDB STATS_PERSISTENT=0; 902ALTER TABLE help_category ENGINE=InnoDB STATS_PERSISTENT=0; 903ALTER TABLE help_relation ENGINE=InnoDB STATS_PERSISTENT=0; 904ALTER TABLE help_keyword ENGINE=InnoDB STATS_PERSISTENT=0; 905ALTER TABLE plugin ENGINE=InnoDB STATS_PERSISTENT=0; 906ALTER TABLE servers ENGINE=InnoDB STATS_PERSISTENT=0; 907ALTER TABLE time_zone ENGINE=InnoDB STATS_PERSISTENT=0; 908ALTER TABLE time_zone_leap_second ENGINE=InnoDB STATS_PERSISTENT=0; 909ALTER TABLE time_zone_name ENGINE=InnoDB STATS_PERSISTENT=0; 910ALTER TABLE time_zone_transition ENGINE=InnoDB STATS_PERSISTENT=0; 911ALTER TABLE time_zone_transition_type ENGINE=InnoDB STATS_PERSISTENT=0; 912 913# Move any distributed grant tables back to NDB after upgrade 914SET @cmd="ALTER TABLE mysql.user ENGINE=NDB"; 915SET @str = IF(@had_distributed_user > 0, @cmd, "SET @dummy = 0"); 916PREPARE stmt FROM @str; 917EXECUTE stmt; 918DROP PREPARE stmt; 919 920SET @cmd="ALTER TABLE mysql.db ENGINE=NDB"; 921SET @str = IF(@had_distributed_db > 0, @cmd, "SET @dummy = 0"); 922PREPARE stmt FROM @str; 923EXECUTE stmt; 924DROP PREPARE stmt; 925 926SET @cmd="ALTER TABLE mysql.tables_priv ENGINE=NDB"; 927SET @str = IF(@had_distributed_tables_priv > 0, @cmd, "SET @dummy = 0"); 928PREPARE stmt FROM @str; 929EXECUTE stmt; 930DROP PREPARE stmt; 931 932SET @cmd="ALTER TABLE mysql.columns_priv ENGINE=NDB"; 933SET @str = IF(@had_distributed_columns_priv > 0, @cmd, "SET @dummy = 0"); 934PREPARE stmt FROM @str; 935EXECUTE stmt; 936DROP PREPARE stmt; 937 938SET @cmd="ALTER TABLE mysql.procs_priv ENGINE=NDB"; 939SET @str = IF(@had_distributed_procs_priv > 0, @cmd, "SET @dummy = 0"); 940PREPARE stmt FROM @str; 941EXECUTE stmt; 942DROP PREPARE stmt; 943 944SET @cmd="ALTER TABLE mysql.proxies_priv ENGINE=NDB"; 945SET @str = IF(@had_distributed_proxies_priv > 0, @cmd, "SET @dummy = 0"); 946PREPARE stmt FROM @str; 947EXECUTE stmt; 948DROP PREPARE stmt; 949 950-- 951-- MySQL 8.0 adds default_value column to cost tables 952-- In case of downgrade to 5.7, remove these columns 953-- 954 955-- Drop column default_value from mysql.server_cost if it exists 956SET @have_server_cost_default = 957 (SELECT COUNT(column_name) FROM information_schema.columns 958 WHERE table_schema = 'mysql' AND table_name = 'server_cost' AND 959 column_name = 'default_value'); 960SET @cmd="ALTER TABLE mysql.server_cost DROP COLUMN default_value"; 961SET @str = IF(@have_server_cost_default > 0, @cmd, "SET @dummy = 0"); 962PREPARE stmt FROM @str; 963EXECUTE stmt; 964DROP PREPARE stmt; 965 966-- Drop column default_value from mysql.engine_cost if it exists 967SET @have_engine_cost_default = 968 (SELECT COUNT(column_name) FROM information_schema.columns 969 WHERE table_schema = 'mysql' AND table_name = 'engine_cost' AND 970 column_name = 'default_value'); 971SET @cmd="ALTER TABLE mysql.engine_cost DROP COLUMN default_value"; 972SET @str = IF(@have_engine_cost_default > 0, @cmd, "SET @dummy = 0"); 973PREPARE stmt FROM @str; 974EXECUTE stmt; 975DROP PREPARE stmt; 976 977# 978# SQL commands for creating the user in MySQL Server which can be used by the 979# internal server session service 980# Notes: 981# This user is disabled for login 982# This user has super privileges and select privileges into performance schema 983# tables the mysql.user table. 984# 985 986INSERT IGNORE INTO mysql.user VALUES ('localhost','mysql.session','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','','','','',0,0,0,0,'mysql_native_password','*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE','N',CURRENT_TIMESTAMP,NULL,'Y'); 987 988INSERT IGNORE INTO mysql.tables_priv VALUES ('localhost', 'mysql', 'mysql.session', 'user', 'root\@localhost', CURRENT_TIMESTAMP, 'Select', ''); 989 990INSERT IGNORE INTO mysql.db VALUES ('localhost', 'performance_schema', 'mysql.session','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N'); 991 992-- 993-- Update column definition (size and charset) for audit log tables. 994-- 995 996SET @had_audit_log_user = 997 (SELECT COUNT(table_name) FROM information_schema.tables 998 WHERE table_schema = 'mysql' AND table_name = 'audit_log_user' AND 999 table_type = 'BASE TABLE'); 1000SET @cmd="ALTER TABLE mysql.audit_log_user DROP FOREIGN KEY audit_log_user_ibfk_1"; 1001SET @str = IF(@had_audit_log_user > 0, @cmd, "SET @dummy = 0"); 1002PREPARE stmt FROM @str; 1003EXECUTE stmt; 1004DROP PREPARE stmt; 1005 1006SET @had_audit_log_filter = 1007 (SELECT COUNT(table_name) FROM information_schema.tables 1008 WHERE table_schema = 'mysql' AND table_name = 'audit_log_filter' AND 1009 table_type = 'BASE TABLE'); 1010SET @cmd="ALTER TABLE mysql.audit_log_filter ENGINE=InnoDB"; 1011SET @str = IF(@had_audit_log_filter > 0, @cmd, "SET @dummy = 0"); 1012PREPARE stmt FROM @str; 1013EXECUTE stmt; 1014DROP PREPARE stmt; 1015 1016SET @cmd="ALTER TABLE mysql.audit_log_filter CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin"; 1017SET @str = IF(@had_audit_log_filter > 0, @cmd, "SET @dummy = 0"); 1018PREPARE stmt FROM @str; 1019EXECUTE stmt; 1020DROP PREPARE stmt; 1021 1022SET @had_audit_log_user = 1023 (SELECT COUNT(table_name) FROM information_schema.tables 1024 WHERE table_schema = 'mysql' AND table_name = 'audit_log_user' AND 1025 table_type = 'BASE TABLE'); 1026SET @cmd="ALTER TABLE mysql.audit_log_user ENGINE=InnoDB"; 1027SET @str = IF(@had_audit_log_user > 0, @cmd, "SET @dummy = 0"); 1028PREPARE stmt FROM @str; 1029EXECUTE stmt; 1030DROP PREPARE stmt; 1031 1032SET @cmd="ALTER TABLE mysql.audit_log_user CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin"; 1033SET @str = IF(@had_audit_log_user > 0, @cmd, "SET @dummy = 0"); 1034PREPARE stmt FROM @str; 1035EXECUTE stmt; 1036DROP PREPARE stmt; 1037 1038SET @cmd="ALTER TABLE mysql.audit_log_user MODIFY COLUMN USER VARCHAR(32)"; 1039SET @str = IF(@had_audit_log_user > 0, @cmd, "SET @dummy = 0"); 1040PREPARE stmt FROM @str; 1041EXECUTE stmt; 1042DROP PREPARE stmt; 1043 1044SET @cmd="ALTER TABLE mysql.audit_log_user ADD FOREIGN KEY (FILTERNAME) REFERENCES mysql.audit_log_filter(NAME)"; 1045SET @str = IF(@had_audit_log_user > 0, @cmd, "SET @dummy = 0"); 1046PREPARE stmt FROM @str; 1047EXECUTE stmt; 1048DROP PREPARE stmt; 1049 1050FLUSH PRIVILEGES; 1051 1052-- 1053-- Update the column length of 'table_name' column for stats tables. 1054-- 1055 1056alter table mysql.innodb_table_stats modify table_name varchar(199); 1057alter table mysql.innodb_index_stats modify table_name varchar(199); 1058 1059