mysql5.7.37升级到greatSQL8.0.25时报错

1、由社区版本的mysql5.7.37升级到greatSQL8.0.25版本
2、首先关闭mysql，set global innodb_fast_shutdown = 0，然后shutdown
3、将mysql5.7.37的二进制安装包替换为greatSQL的二进制文件包
4、添加greatSQL8.0.25需要的升级参数
log_error_verbosity=3
innodb_print_ddl_logs = ON
upgrade = FORCE
default_authentication_plugin=mysql_native_password

5、使用mysqld --user=mysql启动mysql，然后报上述的错误。

下面是报错表的DDL
CREATE TABLE `proxies_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Proxied_host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Proxied_user` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `With_grant` tinyint(1) NOT NULL DEFAULT '0',
  `Grantor` char(93) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Host`,`User`,`Proxied_host`,`Proxied_user`) USING BTREE,
  KEY `Grantor` (`Grantor`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=FIXED COMMENT='User proxy privileges'

yejr 发表于 2023-3-22 15:30
那有可能是你以往升级过程中，几个元数据表没正确升级。
可以在5.7环境下，先跑mysql_upgrade（或者 mysq ...

1、我这边测试了下，社区版本的5.7.37升级到39，然后在升级8.0.25还是报那个错误。但是如果我使用5.7.37的版本重新初始化，在进行升级到8.0.25就不报错了。
2、而且我在升级到5.7.39时，mysql_upgrade系统表没有提示报错，都是显示ok的。

yejr 发表于 2023-3-22 15:58
这个升级前的检查和注意事项文档可以先看看

https://gitee.com/GreatSQL/GreatSQL-Doc/blob/master/user ...

在执行$ /usr/local/GreatSQL-5.7.36-39-Linux-glibc2.28-x86_64/bin/mysql_upgrade -s -f -S/data/GreatSQL/mysql.sock
后升级依旧失败。

通过测试修改语句后发现，是因为ROW_FORMAT=FIXED导致的修改语句报错，将报错的表修改为：ROW_FORMAT =DYNAMIC。为防止后续表也存在这个问题，我将mysq库内的表ROW_FORMAT都修改为DYNAMIC。在重新进行升级8.0时，升级成功。

yejr 发表于 2023-3-22 16:28
报错信息是啥，也贴一下。
另外，row_format支持FIXED格式吗？我咋完全没印象了。。。 ...

/usr/local/mysql/bin/mysql_upgrade -s -f -uroot -p
Enter password:
The --upgrade-system-tables option was used, databases won't be touched.
Checking server version.
Running queries to upgrade MySQL server.
The sys schema is already up to date (version 1.5.2).
Upgrade process completed successfully.
Checking if update is needed.


上面就是执行完升级后的信息，没有报错，但是在升级8.0的时候还是报那个表修改失败的错。
2023-03-22T14:16:22.847644+08:00 5 [ERROR] [MY-013178] [Server] Execution of server-side SQL statement 'ALTER TABLE proxies_priv MODIFY Host char(255) CHARACTER SET ASCII DEFAULT '' NOT NULL, ENGINE=InnoDB; ' failed with error code = 1031, error message = 'Table storage engine for '#sql-45e8_5' doesn't have this option'.

还有就是mysql我查询了一下，proxies_priv这个表DDL中ROW_FORMAT=FIXED

yejr 发表于 2023-3-22 16:32
看了下我这里的5.7环境，相应的表DDL是这样的，并没指定fixed格式

我怀疑是mysql_upgrade这个没有对这块ROW_FORMAT格式的升级，不然我执行多次mysql_upgrade都是提示升级成功。

yejr 发表于 2023-3-22 16:38
1、确认版本号

[root@platform-redis data]# /usr/local/mysql/bin/mysql_upgrade --version
mysql_upgrade  Ver 2.0 Distrib 5.7.37, for linux-glibc2.12 (x86_64)
[root@platform-redis data]#
[root@platform-redis data]# /usr/local/mysql/bin/mysql_upgrade -s -f
Enter password:
The --upgrade-system-tables option was used, databases won't be touched.
Checking server version.
Running queries to upgrade MySQL server.
The sys schema is already up to date (version 1.5.2).
Upgrade process completed successfully.
Checking if update is needed.
[root@platform-redis data]#
[root@platform-redis data]# /usr/local/mysql/bin/mysql
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.37-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

[icity@localhostnone)] >use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
[icity@localhost:mysql] >show create table proxies_priv\G
*************************** 1. row ***************************
       Table: proxies_priv
Create Table: CREATE TABLE `proxies_priv` (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Proxied_host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Proxied_user` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `With_grant` tinyint(1) NOT NULL DEFAULT '0',
  `Grantor` char(93) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`Host`,`User`,`Proxied_host`,`Proxied_user`) USING BTREE,
  KEY `Grantor` (`Grantor`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=FIXED COMMENT='User proxy privileges'
1 row in set (0.00 sec)

yejr 发表于 2023-3-22 16:55
我用MySQL 5.7.10版本模拟场景

没有，我这个确实是由5.7升级的。我们基本上没有再老的环境了