运维工作中你遇到过哪些神奇事件？

GreatSQL社区

搜索

[已解决] mysql5.7.37升级到greatSQL8.0.25时报错

1012 16 2023-3-22 14:26
全部回复(16)
只看楼主
yejr 2023-3-22 16:32:01
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也给升级了
回复 采纳

举报

sky凌乱 2023-3-22 16:35:06
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:38:18
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
复制代码


麻烦再提供下
回复 采纳

举报

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

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

举报

sky凌乱 2023-3-22 16:46:05
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:58
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)

  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版本直接升上来的？
回复 采纳

举报

sky凌乱 2023-3-22 17:00:59
yejr 发表于 2023-3-22 16:55
我用MySQL 5.7.10版本模拟场景

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

举报

12
sky凌乱
发消息
互动与交流
社区活动
论坛问答
加入社区
贡献者指南
学习与应用
文章博客
文档资料
视频资料
常见问题
发现社区
社区准则
建议反馈
社区之星

合作电话：010-64087828

社区邮箱：greatsql@greatdb.com

社区公众号
社区小助手
QQ群
友情链接
万里数据库开源中国墨天轮开放原子开源基金会OpenEuler
©2022 GreatSQL All Rights Reserved. ( 京ICP备06057874号 )
GMT+8, 2024-4-26 12:34 , Processed in 0.018384 second(s), 13 queries , Redis On.
快速回复 返回顶部 返回列表