summaryrefslogtreecommitdiff
path: root/scripts/mysql_system_tables_fix.sql
diff options
context:
space:
mode:
Diffstat (limited to 'scripts/mysql_system_tables_fix.sql')
-rw-r--r--scripts/mysql_system_tables_fix.sql56
1 files changed, 33 insertions, 23 deletions
diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql
index 51c7e544228..5372408fd0d 100644
--- a/scripts/mysql_system_tables_fix.sql
+++ b/scripts/mysql_system_tables_fix.sql
@@ -1,5 +1,5 @@
-- Copyright (C) 2003, 2013 Oracle and/or its affiliates.
--- Copyright (C) 2010, 2015 MariaDB Corporation Ab.
+-- Copyright (C) 2010, 2018 MariaDB Corporation
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
@@ -28,15 +28,24 @@ set sql_mode='';
set storage_engine=MyISAM;
set enforce_storage_engine=NULL;
-ALTER TABLE user add File_priv enum('N','Y') COLLATE utf8_general_ci NOT NULL;
+ALTER TABLE user add File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;
# Detect whether or not we had the Grant_priv column
SET @hadGrantPriv:=0;
SELECT @hadGrantPriv:=1 FROM user WHERE Grant_priv LIKE '%';
-ALTER 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;
-ALTER TABLE host add Grant_priv enum('N','Y') 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;
-ALTER 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;
+ALTER TABLE user add Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
+ add References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
+ add Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
+ add Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;
+ALTER TABLE host add Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
+ add References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
+ add Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
+ add Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;
+ALTER TABLE db add Grant_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
+ add References_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
+ add Index_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
+ add Alter_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;
# Fix privileges for old tables
UPDATE user SET Grant_priv=File_priv,References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_priv WHERE @hadGrantPriv = 0;
@@ -48,11 +57,11 @@ UPDATE host SET References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Cr
# Adding columns needed by GRANT .. REQUIRE (openssl)
ALTER TABLE user
-ADD ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8_general_ci NOT NULL,
+ADD ssl_type enum('','ANY','X509', 'SPECIFIED') DEFAULT '' NOT NULL,
ADD ssl_cipher BLOB NOT NULL,
ADD x509_issuer BLOB NOT NULL,
ADD x509_subject BLOB NOT NULL;
-ALTER TABLE user MODIFY ssl_type enum('','ANY','X509', 'SPECIFIED') NOT NULL;
+ALTER TABLE user MODIFY ssl_type enum('','ANY','X509', 'SPECIFIED') DEFAULT '' NOT NULL;
#
# tables_priv
@@ -63,9 +72,9 @@ ALTER TABLE tables_priv
ALTER TABLE tables_priv
MODIFY Host char(60) NOT NULL default '',
MODIFY Db char(64) NOT NULL default '',
- MODIFY User char(80) NOT NULL default '',
+ MODIFY User char(80) binary NOT NULL default '',
MODIFY Table_name char(64) NOT NULL default '',
- MODIFY Grantor char(141) NOT NULL default '',
+ MODIFY Grantor char(141) COLLATE utf8_bin NOT NULL default '',
ENGINE=MyISAM,
CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
@@ -91,7 +100,7 @@ ALTER TABLE columns_priv
ALTER TABLE columns_priv
MODIFY Host char(60) NOT NULL default '',
MODIFY Db char(64) NOT NULL default '',
- MODIFY User char(80) NOT NULL default '',
+ MODIFY User char(80) binary NOT NULL default '',
MODIFY Table_name char(64) NOT NULL default '',
MODIFY Column_name char(64) NOT NULL default '',
ENGINE=MyISAM,
@@ -162,7 +171,7 @@ alter table func comment='User defined functions';
# and reset all char columns to correct width
ALTER TABLE user
MODIFY Host char(60) NOT NULL default '',
- MODIFY User char(80) NOT NULL default '',
+ MODIFY User char(80) binary NOT NULL default '',
ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
# In MySQL 5.7.6 the Password column is removed. Recreate it to preserve the number
@@ -198,7 +207,7 @@ ALTER TABLE user
ALTER TABLE db
MODIFY Host char(60) NOT NULL default '',
MODIFY Db char(64) NOT NULL default '',
- MODIFY User char(80) NOT NULL default '',
+ MODIFY User char(80) binary NOT NULL default '',
ENGINE=MyISAM, CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin;
ALTER TABLE db
MODIFY Select_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL,
@@ -462,7 +471,7 @@ ALTER TABLE proc MODIFY db
MODIFY definer
char(141) collate utf8_bin DEFAULT '' NOT NULL,
MODIFY comment
- char(64) collate utf8_bin DEFAULT '' NOT NULL;
+ text collate utf8_bin NOT NULL;
ALTER TABLE proc ADD character_set_client
char(32) collate utf8_bin DEFAULT NULL
@@ -540,19 +549,18 @@ ALTER TABLE proc ADD aggregate enum('NONE', 'GROUP') DEFAULT 'NONE' NOT NULL
SET @hadEventPriv := 0;
SELECT @hadEventPriv :=1 FROM user WHERE Event_priv LIKE '%';
-ALTER TABLE user add Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL AFTER Create_user_priv;
+ALTER TABLE user ADD Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL AFTER Create_user_priv;
ALTER TABLE user MODIFY Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL AFTER Create_user_priv;
UPDATE user SET Event_priv=Super_priv WHERE @hadEventPriv = 0;
-ALTER TABLE db add Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL;
+ALTER TABLE db ADD Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL;
ALTER TABLE db MODIFY Event_priv enum('N','Y') character set utf8 DEFAULT 'N' NOT NULL;
#
# EVENT table
#
-ALTER TABLE event DROP PRIMARY KEY;
-ALTER TABLE event ADD PRIMARY KEY(db, name);
+ALTER TABLE event DROP PRIMARY KEY, ADD PRIMARY KEY(db, name);
# Add sql_mode column just in case.
ALTER TABLE event ADD sql_mode set ('IGNORE_BAD_TABLE_OPTIONS') AFTER on_completion;
# Update list of sql_mode values.
@@ -594,8 +602,8 @@ ALTER TABLE event MODIFY sql_mode
) DEFAULT '' NOT NULL AFTER on_completion;
ALTER TABLE event MODIFY name char(64) CHARACTER SET utf8 NOT NULL default '';
-ALTER TABLE event MODIFY COLUMN originator INT UNSIGNED NOT NULL;
ALTER TABLE event ADD COLUMN originator INT UNSIGNED NOT NULL AFTER comment;
+ALTER TABLE event MODIFY COLUMN originator INT UNSIGNED NOT NULL;
ALTER TABLE event MODIFY COLUMN status ENUM('ENABLED','DISABLED','SLAVESIDE_DISABLED') NOT NULL default 'ENABLED';
@@ -674,12 +682,14 @@ ALTER TABLE db modify Delete_history_priv enum('N','Y') COLLATE utf8_general_ci
UPDATE user SET Delete_history_priv = Super_priv WHERE @had_user_delete_history_priv = 0;
-ALTER TABLE user ADD plugin char(64) DEFAULT '', ADD authentication_string TEXT;
+ALTER TABLE user ADD plugin char(64) CHARACTER SET latin1 DEFAULT '' NOT NULL,
+ ADD authentication_string TEXT NOT NULL;
+ALTER TABLE user MODIFY plugin char(64) CHARACTER SET latin1 DEFAULT '' NOT NULL,
+ MODIFY authentication_string TEXT NOT NULL;
ALTER TABLE user ADD password_expired ENUM('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;
ALTER TABLE user ADD is_role enum('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;
ALTER TABLE user ADD default_role char(80) binary DEFAULT '' NOT NULL;
ALTER TABLE user ADD max_statement_time decimal(12,6) DEFAULT 0 NOT NULL;
-ALTER TABLE user MODIFY plugin char(64) CHARACTER SET latin1 DEFAULT '' NOT NULL, MODIFY authentication_string TEXT NOT NULL;
-- Somewhere above, we ran ALTER TABLE user .... CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin.
-- we want password_expired column to have collation utf8_general_ci.
ALTER TABLE user MODIFY password_expired ENUM('N', 'Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;
@@ -722,13 +732,13 @@ set @str=replace(@str, "innodb_index_stats", "innodb_table_stats");
prepare stmt from @str;
execute stmt;
-# update timestamp fields in the innodb stat tables
-set @str="alter table mysql.innodb_index_stats modify last_update timestamp not null default current_timestamp on update current_timestamp";
+# update table_name and timestamp fields in the innodb stat tables
+set @str="alter table mysql.innodb_index_stats modify last_update timestamp not null default current_timestamp on update current_timestamp, modify table_name varchar(199)";
set @str=if(@have_innodb <> 0, @str, "set @dummy = 0");
prepare stmt from @str;
execute stmt;
-set @str="alter table mysql.innodb_table_stats modify last_update timestamp not null default current_timestamp on update current_timestamp";
+set @str="alter table mysql.innodb_table_stats modify last_update timestamp not null default current_timestamp on update current_timestamp, modify table_name varchar(199)";
set @str=if(@have_innodb <> 0, @str, "set @dummy = 0");
prepare stmt from @str;
execute stmt;
View Lorry Controller Status