| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/C/MariaDB/scripts/ (MariaDB Server Version 8.1-8.4©) Datei vom 1.9.2026 mit Größe 44 kB |
|
Quelle mariadb_system_tables_fix.sql Sprache: SQL |
|||||||||||||||
|
-- Copyright (C) 2003, 2013 Oracle and/or its affiliates. -- Copyright (C) 2010, 2023, 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 -- the Free Software Foundation; version 2 of the License. -- -- This program is distributed in the hope that it will be useful, -- but WITHOUT ANY WARRANTY; without even the implied warranty of -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the -- GNU General Public License for more details. -- -- You should have received a copy of the GNU General Public License -- along with this program; if not, write to the Free Software -- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA # This part converts any old privilege tables to privilege tables suitable # for current version of MariaDB # You can safely ignore all 'Duplicate column' and 'Unknown column' errors # because these just mean that your tables are already up to date. # This script is safe to run even if your tables are already up to date! # Warning message(s) produced for a statement can be printed by explicitly # adding a 'SHOW WARNINGS' after the statement. set sql_mode=''; set sql_safe_updates='OFF'; set default_storage_engine=Aria; set enforce_storage_engine=NULL; set alter_algorithm='DEFAULT'; set use_stat_tables='NEVER'; set path 'CURRENT_SCHEMA'; -- -- Upgrade mysql.column_stats table early because its quite noisy otherwise -- ALTER TABLE column_stats modify min_value varbinary(255) DEFAULT NULL, modify max_value varbinary(255) DEFAULT NULL, modify hist_type enum('SINGLE_PREC_HB','DOUBLE_PREC_HB','JSON_HB'), modify histogram longblob, ENGINE=Aria transactional=0; set @have_innodb= (select count(engine) from information_schema.engines where engine='I # MDEV-21873: 10.2 to 10.3 upgrade doesn't remove semi-sync reference from # mysql.plugin table. # As per suggested fix, check INFORMATION_SCHEMA.PLUGINS # and if semisync plugins aren't there, delete them from mysql.plugin. DELETE FROM mysql.plugin WHERE name="rpl_semi_sync_master" AND NOT EXISTS (SELECT * FROM IN DELETE FROM mysql.plugin WHERE name="rpl_semi_sync_slave" AND NOT EXISTS (SELECT * FROM INF -- -- Ensure that all tables are of type Aria and transactional -- ALTER TABLE user ENGINE=Aria transactional=1; ALTER TABLE db ENGINE=Aria transactional=1; ALTER TABLE func ENGINE=Aria transactional=1; ALTER TABLE procs_priv ENGINE=Aria transactional=1; ALTER TABLE tables_priv ENGINE=Aria transactional=1; ALTER TABLE columns_priv ENGINE=Aria transactional=1; ALTER TABLE roles_mapping ENGINE=Aria transactional=1; ALTER TABLE plugin ENGINE=Aria transactional=1; ALTER TABLE servers ENGINE=Aria transactional=1, CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci; ALTER TABLE time_zone_name ENGINE=Aria transactional=1; ALTER TABLE time_zone ENGINE=Aria transactional=1; ALTER TABLE time_zone_transition ENGINE=Aria transactional=1; ALTER TABLE time_zone_transition_type ENGINE=Aria transactional=1; ALTER TABLE time_zone_leap_second ENGINE=Aria transactional=1; ALTER TABLE proc ENGINE=Aria transactional=1; ALTER TABLE event ENGINE=Aria transactional=1, DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci; ALTER TABLE proxies_priv ENGINE=Aria transactional=1; -- The following tables doesn't have to be transactional ALTER TABLE help_topic ENGINE=Aria transactional=0; ALTER TABLE help_category ENGINE=Aria transactional=0; ALTER TABLE help_relation ENGINE=Aria transactional=0; ALTER TABLE help_keyword ENGINE=Aria transactional=0; ALTER TABLE table_stats ENGINE=Aria transactional=0; ALTER TABLE index_stats ENGINE=Aria transactional=0; ALTER TABLE user add File_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NUL # Detect whether or not we had the Grant_priv column SET @hadGrantPriv:=0; SELECT @hadGrantPriv:=1 FROM user WHERE Grant_priv IS NOT NULL; ALTER TABLE user add Grant_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NU add References_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, add Index_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, add Alter_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL; ALTER TABLE db add Grant_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL add References_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, add Index_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, add Alter_priv enum('N','Y') COLLATE utf8mb3_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_ UPDATE db SET References_priv=Create_priv,Index_priv=Create_priv,Alter_priv=Create_ # # The second alter changes ssl_type to new 4.0.2 format # Adding columns needed by GRANT .. REQUIRE (openssl) ALTER TABLE user 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') DEFAULT '' NOT NULL; # # tables_priv # ALTER TABLE tables_priv ADD KEY Grantor (Grantor); ALTER TABLE tables_priv MODIFY Host char(255) NOT NULL default '', MODIFY Db char(64) NOT NULL default '', MODIFY User char(128) binary NOT NULL default '', MODIFY Table_name char(64) NOT NULL default '', MODIFY Grantor varchar(384) COLLATE utf8mb3_bin NOT NULL default '', ENGINE=Aria, CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin; ALTER TABLE tables_priv MODIFY Column_priv set('Select','Insert','Update','References') COLLATE utf8mb3_general_ci DEFAULT '' NOT NULL, MODIFY Table_priv set('Select','Insert','Update','Delete','Create', 'Drop','Grant','References','Index','Alter', 'Create View','Show view','Trigger','Delete versioning rows') COLLATE utf8mb3_general_ci DEFAULT '' NOT NULL, COMMENT='Table privileges'; # # columns_priv # # # Name change of Type -> Column_priv from MySQL 3.22.12 # ALTER TABLE columns_priv CHANGE Type Column_priv set('Select','Insert','Update','References') COLLATE utf8mb3_general_ci DEFAULT '' NOT NULL; ALTER TABLE columns_priv MODIFY Host char(255) NOT NULL default '', MODIFY Db char(64) NOT NULL default '', MODIFY User char(128) binary NOT NULL default '', MODIFY Table_name char(64) NOT NULL default '', MODIFY Column_name char(64) NOT NULL default '', ENGINE=Aria, CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin, COMMENT='Column privileges'; ALTER TABLE columns_priv MODIFY Column_priv set('Select','Insert','Update','References') COLLATE utf8mb3_general_ci DEFAULT '' NOT NULL; # # Add the new 'type' column to the func table. # ALTER TABLE func add type enum ('function','aggregate') COLLATE utf8mb3_general_ci NOT NUL # # Change the user,db and host tables to current format # # Detect whether we had Show_db_priv SET @hadShowDbPriv:=0; SELECT @hadShowDbPriv:=1 FROM user WHERE Show_db_priv IS NOT NULL; ALTER TABLE user ADD Show_db_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL AFTER Alter ADD Super_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL AFTER Show_db ADD Create_tmp_table_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL A ADD Lock_tables_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL AFTER C ADD Execute_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL AFTER Lock_ ADD Repl_slave_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL AFTER Ex ADD Repl_client_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL AFTER R # Convert privileges so that users have similar privileges as before UPDATE user SET Show_db_priv= Select_priv, Super_priv=Process_priv, Execute_priv=Proce # Add fields that can be used to limit number of questions and connections # for some users. ALTER TABLE user ADD max_questions int(11) NOT NULL DEFAULT 0 AFTER x509_subject, ADD max_updates int(11) unsigned NOT NULL DEFAULT 0 AFTER max_questions, ADD max_connections int(11) unsigned NOT NULL DEFAULT 0 AFTER max_updates; # # Add Create_tmp_table_priv and Lock_tables_priv to db and host # ALTER TABLE db ADD Create_tmp_table_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, ADD Lock_tables_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL; alter table user change max_questions max_questions int(11) unsigned DEFAULT 0 NOT NULL; alter table db comment='Database privileges'; alter table user comment='Users and global privileges'; alter table func comment='User defined functions'; # Convert all tables to UTF-8 with binary collation # and reset all char columns to correct width ALTER TABLE user MODIFY Host char(255) NOT NULL default '', MODIFY User char(128) binary NOT NULL default '', ENGINE=Aria, CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin; ALTER TABLE plugin MODIFY name varchar(64) COLLATE utf8mb3_general_ci NOT NULL DEFAULT '', MODIFY dl varchar(128) COLLATE utf8mb3_general_ci NOT NULL DEFAULT '', CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci; # In MySQL 5.7.6 the Password column is removed. Recreate it to preserve the number # of columns MariaDB expects in the user table. ALTER TABLE user ADD Password char(41) character set latin1 collate latin1_bin NOT NULL default '' AFTER User; # In MySQL the Unix socket authentication plugin has a different name. Thus the # references to it need to be renamed in the user table. Thanks to the WHERE # clauses this applies only to MySQL->MariaDB upgrades and nothing else. UPDATE user SET plugin='unix_socket' WHERE plugin='auth_socket'; DELETE FROM plugin WHERE name='auth_socket'; # Delete plugins that are now inbuilt but might not have been before (MDEV-32043) DELETE plugin FROM information_schema.PLUGINS is_p JOIN plugin ON plugin.name = is_p.PLUGIN_NAME WHERE is_p.PLUGIN_LIBRARY IS NULL; ALTER TABLE user MODIFY Password char(41) character set latin1 collate latin1_bin NOT NULL default '', MODIFY Select_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Insert_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Update_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Delete_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Create_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Drop_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Reload_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Shutdown_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Process_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY File_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Grant_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY References_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Index_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Alter_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Show_db_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Super_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Create_tmp_table_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NU MODIFY Lock_tables_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Execute_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Repl_slave_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Repl_client_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY ssl_type enum('','ANY','X509', 'SPECIFIED') COLLATE utf8mb3_general_ci DEFAULT ' ALTER TABLE db MODIFY Host char(255) NOT NULL default '', MODIFY Db char(64) NOT NULL default '', MODIFY User char(128) binary NOT NULL default '', ENGINE=Aria, CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin; ALTER TABLE db MODIFY Select_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Insert_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Update_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Delete_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Create_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Drop_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Grant_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY References_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Index_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Alter_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL, MODIFY Create_tmp_table_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NU MODIFY Lock_tables_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL; ALTER TABLE func ENGINE=Aria, CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin; ALTER TABLE func MODIFY type enum ('function','aggregate') COLLATE utf8mb3_general_ci NOT NULL; # # Modify log tables. # SET @old_log_state = @@global.general_log; SET GLOBAL general_log = 'OFF'; ALTER TABLE general_log MODIFY event_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMES MODIFY user_host MEDIUMTEXT NOT NULL, MODIFY server_id INTEGER UNSIGNED NOT NULL, MODIFY command_type VARCHAR(64) NOT NULL, MODIFY argument MEDIUMTEXT NOT NULL, MODIFY thread_id BIGINT(21) UNSIGNED NOT NULL; SET GLOBAL general_log = @old_log_state; SET @old_log_state = @@global.log_slow_query; SET GLOBAL log_slow_query = 'OFF'; ALTER TABLE slow_log ADD COLUMN thread_id BIGINT(21) UNSIGNED NOT NULL AFTER sql_text; ALTER TABLE slow_log ADD COLUMN rows_affected BIGINT UNSIGNED NOT NULL AFTER thread_id; ALTER TABLE slow_log MODIFY start_time TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMES MODIFY user_host MEDIUMTEXT NOT NULL, MODIFY query_time TIME(6) NOT NULL, MODIFY lock_time TIME(6) NOT NULL, MODIFY rows_sent BIGINT UNSIGNED NOT NULL, MODIFY rows_examined BIGINT UNSIGNED NOT NULL, MODIFY db VARCHAR(512) NOT NULL, MODIFY last_insert_id INTEGER NOT NULL, MODIFY insert_id INTEGER NOT NULL, MODIFY server_id INTEGER UNSIGNED NOT NULL, MODIFY sql_text MEDIUMTEXT NOT NULL, MODIFY thread_id BIGINT(21) UNSIGNED NOT NULL; SET GLOBAL log_slow_query = @old_log_state; # # Detect whether we had Create_view_priv # SET @hadCreateViewPriv:=0; SELECT @hadCreateViewPriv:=1 FROM user WHERE Create_view_priv IS NOT NULL; # # Create VIEWs privileges (v5.0) # ALTER TABLE db ADD Create_view_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' N ALTER TABLE db MODIFY Create_view_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT ' ALTER TABLE user ADD Create_view_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N ALTER TABLE user MODIFY Create_view_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAUL # # Show VIEWs privileges (v5.0) # ALTER TABLE db ADD Show_view_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT ALTER TABLE db MODIFY Show_view_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' ALTER TABLE user ADD Show_view_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' N ALTER TABLE user MODIFY Show_view_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT ' # # Assign create/show view privileges to people who have create provileges # UPDATE user SET Create_view_priv=Create_priv, Show_view_priv=Create_priv where user<>"" A # # # SET @hadCreateRoutinePriv:=0; SELECT @hadCreateRoutinePriv:=1 FROM user WHERE Create_routine_priv IS NOT NULL; # # Create PROCEDUREs privileges (v5.0) # ALTER TABLE db ADD Create_routine_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT ' ALTER TABLE db MODIFY Create_routine_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAU ALTER TABLE user ADD Create_routine_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAUL ALTER TABLE user MODIFY Create_routine_priv enum('N','Y') COLLATE utf8mb3_general_ci DEF # # Alter PROCEDUREs privileges (v5.0) # ALTER TABLE db ADD Alter_routine_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N ALTER TABLE db MODIFY Alter_routine_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAUL ALTER TABLE user ADD Alter_routine_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT ALTER TABLE user MODIFY Alter_routine_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFA ALTER TABLE db ADD Execute_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NU ALTER TABLE db MODIFY Execute_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NO # # Assign create/alter routine privileges to people who have create privileges # UPDATE user SET Create_routine_priv=Create_priv, Alter_routine_priv=Alter_priv where u UPDATE db SET Create_routine_priv=Create_priv, Alter_routine_priv=Alter_priv, Execute # # Add max_user_connections resource limit # this is signed in MariaDB so that if one sets it's to -1 then the user # can't connect anymore. # ALTER TABLE user ADD max_user_connections int(11) DEFAULT '0' NOT NULL AFTER max_connection ALTER TABLE user MODIFY max_user_connections int(11) DEFAULT '0' NOT NULL AFTER max_connect # # user.Create_user_priv # SET @hadCreateUserPriv:=0; SELECT @hadCreateUserPriv:=1 FROM user WHERE Create_user_priv IS NOT NULL; ALTER TABLE user ADD Create_user_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N ALTER TABLE user MODIFY Create_user_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAUL UPDATE user LEFT JOIN db USING (Host,User) SET Create_user_priv='Y' WHERE @hadCreateUserPriv = 0 AND (user.Grant_priv = 'Y' OR db.Grant_priv = 'Y'); # # procs_priv # ALTER TABLE procs_priv ENGINE=Aria, CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_bin; ALTER TABLE procs_priv MODIFY Proc_priv set('Execute','Alter Routine','Grant','Show Create Routine') COLLATE utf8mb3_general_ci DEFAULT '' NOT NULL; ALTER IGNORE TABLE procs_priv MODIFY Routine_name char(64) COLLATE utf8mb3_general_ci DEFAULT '' NOT NULL; ALTER TABLE procs_priv ADD Routine_type enum('FUNCTION','PROCEDURE') COLLATE utf8mb3_general_ci NOT NULL AFTER Routine_name; ALTER TABLE procs_priv MODIFY Timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP # # proc # # Correct the name fields to not binary, and expand sql_data_access ALTER TABLE proc MODIFY name char(64) DEFAULT '' NOT NULL, MODIFY specific_name char(64) DEFAULT '' NOT NULL, MODIFY sql_data_access enum('CONTAINS_SQL', 'NO_SQL', 'READS_SQL_DATA', 'MODIFIES_SQL_DATA' ) DEFAULT 'CONTAINS_SQL' NOT NULL, MODIFY body longblob NOT NULL, MODIFY returns longblob NOT NULL, MODIFY sql_mode set('REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'IGNORE_BAD_TABLE_OPTIONS', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE', 'NO_ENGINE_SUBSTITUTION', 'PAD_CHAR_TO_FULL_LENGTH', 'EMPTY_STRING_IS_NULL', 'SIMULTANEOUS_ASSIGNMENT', 'TIME_ROUND_FRACTIONAL' ) DEFAULT '' NOT NULL, DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci; # Correct the character set and collation # Reset some fields after the conversion ALTER TABLE proc CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci, MODIFY db char(64) binary DEFAULT '' NOT NULL, MODIFY definer varchar(384) binary DEFAULT '' NOT NULL, MODIFY comment text binary NOT NULL; ALTER TABLE proc ADD character_set_client char(32) collate utf8mb3_bin DEFAULT NULL AFTER comment; ALTER TABLE proc MODIFY character_set_client char(32) collate utf8mb3_bin DEFAULT NULL; ALTER TABLE proc MODIFY type enum('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY') NOT NULL; ALTER TABLE procs_priv MODIFY Routine_type enum('FUNCTION', 'PROCEDURE', 'PACKAGE', 'PACKAGE BODY') NOT NULL; SELECT CASE WHEN COUNT(*) > 0 THEN CONCAT ("WARNING: NULL values of the 'character_set_client' column ('mysql.proc' table) ha ELSE NULL END AS value FROM proc WHERE character_set_client IS NULL; UPDATE proc SET character_set_client = @@character_set_client WHERE character_set_client IS NULL; ALTER TABLE proc ADD collation_connection char(64) collate utf8mb3_bin DEFAULT NULL AFTER character_set_client; ALTER TABLE proc MODIFY collation_connection char(64) collate utf8mb3_bin DEFAULT NULL; SELECT CASE WHEN COUNT(*) > 0 THEN CONCAT ("WARNING: NULL values of the 'collation_connection' column ('mysql.proc' table) ha ELSE NULL END AS value FROM proc WHERE collation_connection IS NULL; UPDATE proc SET collation_connection = @@collation_connection WHERE collation_connection IS NULL; ALTER TABLE proc ADD db_collation char(64) collate utf8mb3_bin DEFAULT NULL AFTER collation_connection; ALTER TABLE proc MODIFY db_collation char(64) collate utf8mb3_bin DEFAULT NULL; SELECT CASE WHEN COUNT(*) > 0 THEN CONCAT ("WARNING: NULL values of the 'db_collation' column ('mysql.proc' table) have been up ELSE NULL END AS value FROM proc WHERE db_collation IS NULL; UPDATE proc AS p SET db_collation = ( SELECT DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = p.db) WHERE db_collation IS NULL; ALTER TABLE proc ADD body_utf8 longblob DEFAULT NULL AFTER db_collation; ALTER TABLE proc MODIFY body_utf8 longblob DEFAULT NULL; # Change comment from char(64) to text ALTER TABLE proc MODIFY comment text collate utf8mb3_bin NOT NULL; # MDEV-7773: Stored Aggregate Functions ALTER TABLE proc ADD aggregate enum('NONE', 'GROUP') DEFAULT 'NONE' NOT NULL AFTER body_utf8; ALTER TABLE proc ADD path text COLLATE utf8mb3_bin DEFAULT 'CURRENT_SCHEMA' NOT NULL AFTER aggregate; ALTER TABLE proc MODIFY path TEXT COLLATE utf8mb3_bin DEFAULT 'CURRENT_SCHEMA' NOT NULL; # Update definer of Add/DropGeometryColumn procedures to 'mariadb.sys' # To consider the scenarios in MDEV-23102, only update the definer when it's 'root' UPDATE proc SET Definer = 'mariadb.sys@localhost' WHERE Definer = 'root@localhost' AND Name UPDATE proc SET Definer = 'mariadb.sys@localhost' WHERE Definer = 'root@localhost' AND Name # # EVENT privilege # SET @hadEventPriv := 0; SELECT @hadEventPriv :=1 FROM user WHERE Event_priv IS NOT NULL; ALTER TABLE user ADD Event_priv enum('N','Y') character set utf8mb3 COLLATE utf8mb3_genera ALTER TABLE user MODIFY Event_priv enum('N','Y') character set utf8mb3 COLLATE utf8mb3_gen UPDATE user SET Event_priv=Super_priv WHERE @hadEventPriv = 0; ALTER TABLE db ADD Event_priv enum('N','Y') character set utf8mb3 COLLATE utf8mb3_general_ ALTER TABLE db MODIFY Event_priv enum('N','Y') character set utf8mb3 COLLATE utf8mb3_gener # # EVENT table # 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. ALTER TABLE event MODIFY sql_mode set('REAL_AS_FLOAT', 'PIPES_AS_CONCAT', 'ANSI_QUOTES', 'IGNORE_SPACE', 'IGNORE_BAD_TABLE_OPTIONS', 'ONLY_FULL_GROUP_BY', 'NO_UNSIGNED_SUBTRACTION', 'NO_DIR_IN_CREATE', 'POSTGRESQL', 'ORACLE', 'MSSQL', 'DB2', 'MAXDB', 'NO_KEY_OPTIONS', 'NO_TABLE_OPTIONS', 'NO_FIELD_OPTIONS', 'MYSQL323', 'MYSQL40', 'ANSI', 'NO_AUTO_VALUE_ON_ZERO', 'NO_BACKSLASH_ESCAPES', 'STRICT_TRANS_TABLES', 'STRICT_ALL_TABLES', 'NO_ZERO_IN_DATE', 'NO_ZERO_DATE', 'INVALID_DATES', 'ERROR_FOR_DIVISION_BY_ZERO', 'TRADITIONAL', 'NO_AUTO_CREATE_USER', 'HIGH_NOT_PRECEDENCE', 'NO_ENGINE_SUBSTITUTION', 'PAD_CHAR_TO_FULL_LENGTH', 'EMPTY_STRING_IS_NULL', 'SIMULTANEOUS_ASSIGNMENT', 'TIME_ROUND_FRACTIONAL' ) DEFAULT '' NOT NULL AFTER on_completion; ALTER TABLE event MODIFY name char(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT ALTER TABLE event MODIFY db CHAR(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL DEFAU ALTER TABLE event MODIFY interval_field enum('YEAR','QUARTER','MONTH','DAY','HOUR', 'MINUTE','WEEK','SECOND','MICROSECOND', 'YEAR_MONTH','DAY_HOUR','DAY_MINUTE', 'DAY_SECOND','HOUR_MINUTE','HOUR_SECOND', 'MINUTE_SECOND','DAY_MICROSECOND','HOUR_MICROSECOND', 'MINUTE_MICROSECOND','SECOND_MICROSECOND' ) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL; ALTER TABLE event MODIFY comment CHAR(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL ALTER TABLE event MODIFY on_completion enum('DROP','PRESERVE') CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL DEFAULT 'DROP'; 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') ALTER TABLE event ADD COLUMN time_zone char(64) CHARACTER SET latin1 COLLATE latin1_swedish NOT NULL DEFAULT 'SYSTEM' AFTER originator; ALTER TABLE event ADD character_set_client char(32) collate utf8mb3_bin DEFAULT NULL AFTER time_zone; ALTER TABLE event MODIFY character_set_client char(32) collate utf8mb3_bin DEFAULT NULL; ALTER TABLE event ADD collation_connection char(64) collate utf8mb3_bin DEFAULT NULL AFTER character_set_client; ALTER TABLE event MODIFY collation_connection char(64) collate utf8mb3_bin DEFAULT NULL; ALTER TABLE event ADD db_collation char(64) collate utf8mb3_bin DEFAULT NULL AFTER collation_connection; ALTER TABLE event MODIFY db_collation char(64) collate utf8mb3_bin DEFAULT NULL; ALTER TABLE event ADD body_utf8 longblob DEFAULT NULL AFTER db_collation; ALTER TABLE event MODIFY body_utf8 longblob DEFAULT NULL; alter table event MODIFY definer varchar(384) collate utf8mb3_bin NOT NULL DEFAULT ''; # Enable event scheduler if the event table was not up to date before. set global event_scheduler=original; # # TRIGGER privilege # SET @hadTriggerPriv := 0; SELECT @hadTriggerPriv :=1 FROM user WHERE Trigger_priv IS NOT NULL; ALTER TABLE user ADD Trigger_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT ALTER TABLE user MODIFY Trigger_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' ALTER TABLE db ADD Trigger_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NU ALTER TABLE db MODIFY Trigger_priv enum('N','Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NO UPDATE user SET Trigger_priv=Super_priv WHERE @hadTriggerPriv = 0; # # user.Create_tablespace_priv # SET @hadCreateTablespacePriv := 0; SELECT @hadCreateTablespacePriv :=1 FROM user WHERE Create_tablespace_priv IS NOT NULL; ALTER TABLE user ADD Create_tablespace_priv enum('N','Y') COLLATE utf8mb3_general_ci DEF ALTER TABLE user MODIFY Create_tablespace_priv enum('N','Y') COLLATE utf8mb3_general_ci UPDATE user SET Create_tablespace_priv = Super_priv WHERE @hadCreateTablespacePriv = 0; # # System versioning # ALTER TABLE user change Truncate_versioning_priv Delete_history_priv enum('N','Y') COLL ALTER TABLE db change Truncate_versioning_priv Delete_history_priv enum('N','Y') COLLAT SET @had_user_delete_history_priv := 0; SELECT @had_user_delete_history_priv :=1 FROM user WHERE Delete_history_priv IS NOT NULL; SET @had_show_create_routine := 0; SELECT @had_show_create_routine:=1 FROM db WHERE Show_create_routine_priv IS NOT NULL; ALTER TABLE user add Delete_history_priv enum('N','Y') COLLATE utf8mb3_general_ci NOT NUL ALTER TABLE user modify Delete_history_priv enum('N','Y') COLLATE utf8mb3_general_ci NOT ALTER TABLE db add Delete_history_priv enum('N','Y') COLLATE utf8mb3_general_ci NOT NULL D ALTER TABLE db modify Delete_history_priv enum('N','Y') COLLATE utf8mb3_general_ci NOT NU UPDATE user SET Delete_history_priv = Super_priv WHERE @had_user_delete_history_priv = 0; ALTER TABLE db ADD Show_create_routine_priv enum('N','Y') COLLATE utf8mb3_general_ci NOT ALTER TABLE db MODIFY Show_create_routine_priv enum('N','Y') COLLATE utf8mb3_general_ci UPDATE db SET Show_create_routine_priv='Y' WHERE @had_show_create_routine=0 AND Drop_priv='Y' AND Index_priv='Y' AND Alter_priv='Y' AND Event_priv='Y' AND Select_priv='Y' AND Insert_priv='Y' AND Update_priv='Y' AND Delete_priv='Y' AND Create_priv='Y' AND Execute_priv='Y' AND Trigger_priv='Y' AND Show_view_priv='Y' AND References_priv='Y' AND Lock_tables_priv='Y' AND Create_view_priv='Y' AND Alter_routine_priv='Y' AND Create_routine_priv='Y' AND Delete_history_priv='Y' AND Create_tmp_table_priv='Y'; ALTER TABLE user ADD plugin char(64) CHARACTER SET latin1 DEFAULT '' NOT NULL AFTER max_user_c ADD authentication_string TEXT NOT NULL AFTER plugin; ALTER TABLE user CHANGE auth_string authentication_string TEXT NOT NULL; ALTER TABLE user ADD password_expired ENUM('N', 'Y') COLLATE utf8mb3_general_ci DEFAULT 'N ALTER TABLE user ADD password_last_changed timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP af ALTER TABLE user ADD password_lifetime smallint unsigned DEFAULT NULL after password_last_ ALTER TABLE user ADD account_locked enum('N', 'Y') COLLATE utf8mb3_general_ci DEFAULT 'N' N ALTER TABLE user ADD is_role enum('N', 'Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL A ALTER TABLE user ADD default_role char(128) binary DEFAULT '' NOT NULL AFTER is_role; ALTER TABLE user ADD max_statement_time decimal(12,6) DEFAULT 0 NOT NULL AFTER default_role -- Somewhere above, we ran ALTER TABLE user .... CONVERT TO CHARACTER SET utf8mb3 COLLATE utf8m -- we want password_expired column to have collation utf8mb3_general_ci. -- Order columns correctly that were not ordered until MDEV-23201 (ff8ffef3e1915d7a9caa07d ALTER TABLE user MODIFY plugin char(64) CHARACTER SET latin1 DEFAULT '' NOT NULL AFTER max_use MODIFY authentication_string TEXT NOT NULL AFTER plugin, MODIFY password_expired ENUM('N', 'Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL AFT MODIFY is_role enum('N', 'Y') COLLATE utf8mb3_general_ci DEFAULT 'N' NOT NULL AFTER passwor MODIFY default_role char(80) binary DEFAULT '' NOT NULL AFTER is_role, MODIFY max_statement_time decimal(12,6) DEFAULT 0 NOT NULL AFTER default_role, -- MDEV-24122 formerly mysql5.7 users may have the following columns password_last_changed -- password_lifetime and account_locked. Ensure they are beyond the end of the user columns -- used by MariaDB. MariaDB-10.4 will use these in the creation of mysql.global_priv. -- password_last_changed has a DEFAULT/ON UPDATE of CURRENT_TIMESTAMP to keep track of -- time until 10.4 added. MODIFY IF EXISTS password_last_changed timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPD MODIFY IF EXISTS password_lifetime smallint unsigned DEFAULT NULL AFTER password_last_cha MODIFY IF EXISTS account_locked enum('N', 'Y') CHARACTER SET utf8mb3 DEFAULT 'N' NOT NULL aft -- Checking for any duplicate hostname and username combination are exists. -- If exits we will throw error. DELIMITER // BEGIN NOT ATOMIC SET @duplicate_hosts=(SELECT count(*) FROM mysql.user GROUP BY user, lower(host) HAVING co IF @duplicate_hosts > 1 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Multiple accounts exist for @user_name, @host_n END IF; END // DELIMITER ; -- Get warnings (if any) SHOW WARNINGS; # Converting the host name to lower case for existing users UPDATE user SET host=LOWER( host ) WHERE LOWER( host ) <> host; DELIMITER // if @have_innodb then # fix bad data when upgrading from unfixed InnoDB (MDEV-13360) delete from innodb_index_stats where length(table_name) > 64; delete from innodb_table_stats where length(table_name) > 64; # update table_name and timestamp fields in the innodb stat tables alter table innodb_index_stats modify last_update timestamp not null default current_time alter table innodb_table_stats modify last_update timestamp not null default current_time alter table innodb_index_stats drop foreign key if exists innodb_index_stats_ibfk_1; end if // DELIMITER ; # MDEV-4332 longer user names, extended by MDEV-24312 to longer again. alter table user modify User char(128) binary not null default ''; alter table db modify User char(128) binary not null default ''; alter table tables_priv modify User char(128) binary not null default ''; alter table columns_priv modify User char(128) binary not null default ''; alter table procs_priv modify User char(128) binary not null default '', modify Host char(255 alter table proc modify definer varchar(384) collate utf8mb3_bin not null default ''; alter table proxies_priv modify User char(128) COLLATE utf8mb3_bin not null default '', modi alter table proxies_priv modify Proxied_user char(128) COLLATE utf8mb3_bin not null defaul alter table proxies_priv modify Grantor varchar(384) COLLATE utf8mb3_bin not null default ' alter table servers modify Username char(128) not null default ''; alter table procs_priv modify Grantor varchar(384) COLLATE utf8mb3_bin not null default ''; alter table tables_priv modify Grantor varchar(384) COLLATE utf8mb3_bin not null default '' # MDEV-33726 longer names from MDEV-24312 extension alter table if exists global_priv modify Host char(255) binary DEFAULT '', modify User char(1 alter table if exists roles_mapping modify Host char(255) binary not null DEFAULT '', modify U # Activate the new, possible modified privilege tables # This should not be needed, but gives us some extra testing that the above # changes was correct flush privileges; -- -- Upgrade help tables -- ALTER TABLE help_category MODIFY url TEXT NOT NULL; ALTER TABLE help_topic MODIFY url TEXT NOT NULL; DELIMITER // IF 'BASE TABLE' = (select table_type from information_schema.tables where table_schema=da CREATE TABLE IF NOT EXISTS global_priv (Host char(255) binary DEFAULT '', User char(128) bina SELECT Host, User, JSON_COMPACT(JSON_OBJECT('access', 1*('Y'=Select_priv)+ 2*('Y'=Insert_priv)+ 4*('Y'=Update_priv)+ 8*('Y'=Delete_priv)+ 16*('Y'=Create_priv)+ 32*('Y'=Drop_priv)+ 64*('Y'=Reload_priv)+ 128*('Y'=Shutdown_priv)+ 256*('Y'=Process_priv)+ 512*('Y'=File_priv)+ 1024*('Y'=Grant_priv)+ 2048*('Y'=References_priv)+ 4096*('Y'=Index_priv)+ 8192*('Y'=Alter_priv)+ 16384*('Y'=Show_db_priv)+ 32768*('Y'=Super_priv)+ 65536*('Y'=Create_tmp_table_priv)+ 131072*('Y'=Lock_tables_priv)+ 262144*('Y'=Execute_priv)+ 524288*('Y'=Repl_slave_priv)+ 1048576*('Y'=Repl_client_priv)+ 2097152*('Y'=Create_view_priv)+ 4194304*('Y'=Show_view_priv)+ 8388608*('Y'=Create_routine_priv)+ 16777216*('Y'=Alter_routine_priv)+ 33554432*('Y'=Create_user_priv)+ 67108864*('Y'=Event_priv)+ 134217728*('Y'=Trigger_priv)+ 268435456*('Y'=Create_tablespace_priv)+ 536870912*('Y'=Delete_history_priv), 'ssl_type', ssl_type-1, 'ssl_cipher', ssl_cipher, 'x509_issuer', x509_issuer, 'x509_subject', x509_subject, 'max_questions', max_questions, 'max_updates', max_updates, 'max_connections', max_connections, 'max_user_connections', max_user_connections, 'max_statement_time', max_statement_time, 'plugin', if(plugin>'',plugin,if(length(password)=16,'mysql_old_password','mysql_n 'authentication_string', if(plugin>'' and authentication_string>'',authentication_str 'password_last_changed', if(password_expired='Y', 0, if(password_last_changed, UNIX_ 'password_lifetime', ifnull(password_lifetime, -1), 'account_locked', 'Y'=account_locked, 'default_role', default_role, 'is_role', 'Y'=is_role)) as Priv FROM user; DROP TABLE user; END IF// IF 1 = (SELECT count(*) FROM information_schema.VIEWS WHERE TABLE_CATALOG = 'def' AND TABLE_SCHEMA = 'mysql' AND TABLE_NAME = 'user' AND (DEFINER = 'root@localhost' OR (DEFINER = 'mariadb.sys@localhost' AND (VIEW_DEFINITION LIKE "%'N' AS `password_expired`%" OR CHARACTER_SET_CLIENT <> 'latin1' OR COLLATION_CONNECTION <> 'latin1_swedish_ci' ) ) ) ) THEN DROP VIEW IF EXISTS mysql.user; END IF// DELIMITER ; # MDEV-22683 - upgrade Host and Owner of servers ALTER TABLE servers MODIFY Host varchar(2048) NOT NULL DEFAULT '', MODIFY Owner varchar(512) NOT NULL DEFAULT ''; # MDEV-34716 Fix mysql.servers socket max length too short ALTER TABLE servers MODIFY Socket char(108) NOT NULL DEFAULT ''; # MDEV-34716 Allow arbitrary options in CREATE SERVER # Check existence of the Options column SET @hadOptions:=0; SELECT @hadOptions:=1 FROM servers WHERE options IS NOT NULL; ALTER TABLE servers ADD Options JSON NOT NULL DEFAULT '{}' CHECK(JSON_VALID(Options)); # Ensure the collation is utf8mb4_bin (default for JSON) ALTER TABLE servers MODIFY Options JSON NOT NULL DEFAULT '{}' CHECK(JSON_VALID(Options)); # If the Options column did not exist, convert to the Options UPDATE servers SET Options = JSON_INSERT(Options, '$.host', Host) WHERE Host <> '' AND @hadOpti UPDATE servers SET Options = JSON_INSERT(Options, '$.database', Db) WHERE Db <> '' AND @hadOpti UPDATE servers SET Options = JSON_INSERT(Options, '$.username', Username) WHERE Username <> ' UPDATE servers SET Options = JSON_INSERT(Options, '$.port', CAST(Port AS VARCHAR(5))) WHER UPDATE servers SET Options = JSON_INSERT(Options, '$.socket', Socket) WHERE Socket <> '' AND @h UPDATE servers SET Options = JSON_INSERT(Options, '$.owner', Owner) WHERE Owner <> '' AND @hadO
¤ Dauer der Verarbeitung: 0.23 Sekunden (vorverarbeitet am 2026-10-08) ¤*© Formatika GbR, Deutschland |
|
||||||||||||||
2026-10-10