[MDEV-21213] mysql_upgrade / mariadb-upgrade error Created: 2019-12-04  Updated: 2022-12-06  Resolved: 2022-12-06

Status: Closed
Project: MariaDB Server
Component/s: Scripts & Clients
Affects Version/s: 10.4.10
Fix Version/s: N/A

Type: Bug Priority: Minor
Reporter: Benjamin Kurschies Assignee: Oleksandr Byelkin
Resolution: Duplicate Votes: 0
Labels: None

Issue Links:
Duplicate
duplicates MDEV-16735 mysql_upgrade failed Closed
Relates
relates to MDEV-21855 Document difference between DEFAULT a... Open

 Description   

to protect our galera cluster (some developers are trying to do nasty things), we set the parameter alter_algorithm=INPLACE
but now when executing the upgrade script we are getting some errors - obviously the tool is trying to modify some system tables in a way that is not compatible with our "alter_algorithm" parameter.
is it possible to make the tool adjust the session before modifying the tables?

set session alter_algorithm=copy;

error codes:

[mysql@rlx-v648 ~]$ mariadb-upgrade 
Phase 1/7: Checking and upgrading mysql database
 
Processing databases
 
mysql
 
mysql.column_stats                                 OK
 
mysql.columns_priv                                 OK
 
mysql.db                                           OK
 
mysql.event                                        OK
 
mysql.func                                         OK
 
mysql.global_priv                                  OK
 
mysql.gtid_slave_pos                               OK
 
mysql.help_category                                OK
 
mysql.help_keyword                                 OK
 
mysql.help_relation                                OK
 
mysql.help_topic                                   OK
 
mysql.index_stats                                  OK
 
mysql.innodb_index_stats                           OK
 
mysql.innodb_table_stats                           OK
 
mysql.plugin                                       OK
 
mysql.proc                                         OK
 
mysql.procs_priv                                   OK
 
mysql.proxies_priv                                 OK
 
mysql.roles_mapping                                OK
 
mysql.server_audit_filters                         OK
 
mysql.server_audit_users                           OK
 
mysql.servers                                      OK
 
mysql.table_stats                                  OK
 
mysql.tables_priv                                  OK
 
mysql.time_zone                                    OK
 
mysql.time_zone_leap_second                        OK
 
mysql.time_zone_name                               OK
 
mysql.time_zone_transition                         OK
 
mysql.time_zone_transition_type                    OK
 
mysql.transaction_registry                         OK
 
mysql.wsrep_cluster                                OK
 
mysql.wsrep_cluster_members                        OK
 
mysql.wsrep_streaming_log                          OK
 
Phase 2/7: Installing used storage engines... Skipped
 
Phase 3/7: Fixing views
 
mysql.user                                         OK
 
Phase 4/7: Running 'mysql_fix_privilege_tables'
 
ERROR 1845 (0A000) at line 26: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 45: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 110: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 128: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 163: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 201: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 215: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 263: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 328: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 464: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 465: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 466: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 467: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 468: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 469: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 470: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 471: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 472: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 473: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 474: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 475: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 476: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 477: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 478: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 479: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 480: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 481: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 482: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 483: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 484: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 485: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY
 
ERROR 1845 (0A000) at line 486: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY



 Comments   
Comment by Hartmut Holzgraefe [ 2020-02-28 ]

I can verify this, with alter_algorithm=INPLACE in the configuration file mysql_upgrade fails as described.

So mysql_upgrade would need to explicit set its alter_algorithm session variable to a compatible value in the future before doing any DDL operations.

Comment by Hartmut Holzgraefe [ 2020-03-03 ]

Suggested fix: add explicit

SET alter_algorithm=COPY;

to the beginning of scripts/mysql_system_tables_fix.sql

In theory it should be DEFAULT, not COPY, but due to MDEV-21885 this does not work as DEFAULT is the magic word for "set this session variable back to its global value" 

diff --git a/scripts/mysql_system_tables_fix.sql b/scripts/mysql_system_tables_fix.sql
 
index 3949cd8f072..4df2668ffc6 100644
 
--- a/scripts/mysql_system_tables_fix.sql
 
+++ b/scripts/mysql_system_tables_fix.sql
 
@@ -27,6 +27,7 @@
 
 set sql_mode='';
 
 set storage_engine=MyISAM;
 
 set enforce_storage_engine=NULL;
 
+set alter_algorithm=COPY;
 
 
 ALTER TABLE user add File_priv enum('N','Y') COLLATE utf8_general_ci DEFAULT 'N' NOT NULL;

Comment by Marko Mäkelä [ 2020-03-13 ]

MDEV-21855 suggests a better fix:

SET alter_algorithm='default';

Alternatively, perhaps using the numeric value 0 works? Before MDEV-13134 introduced this parameter, the parameter was only accessible via the original name old_alter_table, whose compile-time default value is 0.

Comment by Daniel Black [ 2022-12-06 ]

Duplicate MDEV-16735, and has been fixed.

Generated at Thu Feb 08 09:05:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.