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

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

看了下我这里的5.7环境，相应的表DDL是这样的，并没指定fixed格式

1. [root@db160] [mysql]>show create table proxies_priv\G
   
2. *************************** 1. row ***************************
   
3.        Table: proxies_priv
   
4. Create Table: CREATE TABLE `proxies_priv` (
   
5.   `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
   
6.   `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
   
7.   `Proxied_host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
   
8.   `Proxied_user` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
   
9.   `With_grant` tinyint(1) NOT NULL DEFAULT '0',
   
10.   `Grantor` char(93) COLLATE utf8_bin NOT NULL DEFAULT '',
    
11.   `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
12.   PRIMARY KEY (`Host`,`User`,`Proxied_host`,`Proxied_user`),
    
13.   KEY `Grantor` (`Grantor`)
    
14. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User proxy privileges'

复制代码

所以说，大概率还是你之前的版本升级没做好，没把row_format也给升级了

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

sky凌乱 发表于 2023-3-22 16:35
/usr/local/mysql/bin/mysql_upgrade -s -f -uroot -p
Enter password:
The --upgrade-system-tables op ...

1、确认版本号

1. /usr/local/mysql/bin/mysql_upgrade --version

复制代码

2、执行完mysql_upgrade后，确认新的表DDL

1. show create table proxies_priv\G

复制代码

麻烦再提供下

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)

sky凌乱 发表于 2023-3-22 16:46
[root@platform-redis data]# /usr/local/mysql/bin/mysql_upgrade --version
mysql_upgrade  Ver 2.0 Di ...

我用MySQL 5.7.10版本模拟场景

1. [root@db160] [mysql]>\s
   
2. --------------
   
3. mysql  Ver 8.0.25-16 for Linux on x86_64 (GreatSQL (GPL), Release 16, Revision 8bb0e5af297)
   
4. 
   
5. Connection id:          2
   
6. Current database:       mysql
   
7. Current user:           root@localhost
   
8. SSL:                    Not in use
   
9. Current pager:          stdout
   
10. Using outfile:          ''
    
11. Using delimiter:        ;
    
12. Server version:         5.7.10-log MySQL Community Server (GPL)

复制代码

查看这个表DDL

1. [root@db160] [mysql]>show create table proxies_priv\G
   
2. *************************** 1. row ***************************
   
3.        Table: proxies_priv
   
4. Create Table: CREATE TABLE `proxies_priv` (
   
5.   `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
   
6.   `User` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
   
7.   `Proxied_host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
   
8.   `Proxied_user` char(32) COLLATE utf8_bin NOT NULL DEFAULT '',
   
9.   `With_grant` tinyint(1) NOT NULL DEFAULT '0',
   
10.   `Grantor` char(77) COLLATE utf8_bin NOT NULL DEFAULT '',
    
11.   `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
12.   PRIMARY KEY (`Host`,`User`,`Proxied_host`,`Proxied_user`),
    
13.   KEY `Grantor` (`Grantor`)
    
14. ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='User proxy privileges'

复制代码

也没加上 row_format=fixed 这个约束。

如果想要手动修改，也会报错

1. [root@db160] [mysql]>alter table proxies_priv row_format=fixed;
   
2. ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'Timestamp' at row 1
   
3. [root@db160] [mysql]>alter table proxies_priv row_format=fixed, engine=myisam;
   
4. ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'Timestamp' at row 1

复制代码

有点诡异了，搞不清楚你这个情况咋来的，可能这个实例是以前5.5、5.6版本直接升上来的？

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

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