我使用navicat进行结构同步，对比生成差异的sql语句后，执行部署脚本，试图同步结构，发现执行到某一个语句就会让数据库崩溃，必须手动重启。于是我就开始排查，定位问题定位了老半天。重启数据库后，先用show variables like '%log_error%';找到数据库的错误日志，报错信息如下：
2024-11-01T10:55:40.115637+08:00 39 [ERROR] [MY-000000] [InnoDB] InnoDB: Assertion failure: ut0ut.cc:678:!m_fatal InnoDB: thread 1616 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/8.0/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 02:55:40 UTC - mysqld got exception 0x80000003 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. Attempting to collect some information that could help diagnose the problem. As this is a crash and something is definitely wrong, the information collection process might fail.
key_buffer_size=8388608 read_buffer_size=131072 max_used_connections=92 max_threads=500 thread_count=1 connection_count=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 205176 K bytes of memory Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x22df1c6d040 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... 7ff6402ea6d2 mysqld.exe!??? 7ffc0997da2d ucrtbase.dll!raise() 7ffc0997e901 ucrtbase.dll!abort() 7ff640536a67 mysqld.exe!??? 7ffc00000003 7ffc00000003 194 7ffc099fa510 ucrtbase.dll!_wctype() 7ff640949210 mysqld.exe!??? 80 37 7ffb00000040 7ff6412bc25a mysqld.exe!??? 7ffc00080000 7ff6412bc1c8 mysqld.exe!??? 7ff6412bc59a mysqld.exe!??? 2a6 7ff64113589c mysqld.exe!??? 7ff6412bce18 mysqld.exe!??? 650 14f
Trying to get some variables. Some pointers may be invalid and cause the dump to abort. Query (22dcd1dfbc8): ALTER TABLE `test_db`.`test_device` ADD COLUMN `ext_sn` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '澶栭儴sn' AFTER `custom_config` Connection ID (thread ID): 39 Status: KILL_CONNECTION
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash.
说实话，这报错信息没有太多有效内容，反复定位的过程中，我发现了另一个问题，为什么结构对比有大量的int(0)与int(11)这种差异，而且手动改还不生效。查了一下是原来因为从从8.0.17版本开始，TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT类型的显示宽度将失效（也确实，这些字段理论上，本身长度就是固定的），而我的两个数据库版本不同，一个是8.0.11，另一个是8.0.29:
1
MySQL Server 8.0.17 deprecated the display width for the TINYINT, SMALLINT, MEDIUMINT, INT, and BIGINT data types when the ZEROFILL modifier is not used, and MySQL Server 8.0.19 has removed the display width for those data types from results of SHOW CREATE TABLE, SHOW CREATE FUNCTION, and queries on INFORMATION_SCHEMA.COLUMNS, INFORMATION_SCHEMA.ROUTINES, and INFORMATION_SCHEMA.PARAMETERS (except for the display width for signed TINYINT(1)). This patch adjusts Connector/J to those recent changes of MySQL Server and, as a result, DatabaseMetaData, ParameterMetaData, and ResultSetMetaData now report identical results for all the above-mentioned integer types and also for the FLOAT and DOUBLE data types. (Bug #30477722)
别外把sql语句中的CHARACTER SET utf8 COLLATE utf8_general_ci删掉后，数据库就不会崩溃了。检查了一下数据表的编码格式，果然是因为编码的问题，一个是utf8（等于utf8mb3），另一个是utf8mb4的，是因为编码的问题导致异常。也终于明白了为啥会有澶栭儴这样的乱码字。这下子问题解决了，就是编码的问题，重新调整编码即可。