MySQL主从数据库Last_Error: Error ‘Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs’ on query

• DB、Linux • 评论关闭

MySQL 版本: MySQL 5.7.31
MySQL 架构: 主从数据库架构
告警节点: 从数据库
运行场景: 某大型制造企业

             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 1118
                   Last_Error: Error 'Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs' on query. Default database: 'ekp'. Query: 'alter table ekp_ff4cf69105f28480891e add column fd_3ddc5be2f5270e varchar(4000)'

解决办法，修改MySQL配置参数，从新进行数据同步。

[mysqld]
port=3360
bind_address=0.0.0.0
server-id = 128
log_bin = mysql-bin
binlog_cache_size = 4M
binlog_format = mixed
expire_logs_days = 99
relay-log=mysqld-relay-bin
init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4
datadir=/home/mysql
socket=/home/mysql/mysql.sock
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit = 5000
key_buffer_size = 256M
query_cache_size = 64M
thread_cache_size = 64
lower_case_table_names = 1
default_storage_engine = InnoDB
innodb_file_format = Barracuda   # 启用 Barracuda，支持大行
innodb_file_per_table = 1             # 独立表空间，便于管理
innodb_default_row_format = DYNAMIC   # 默认 DYNAMIC 行格式，避免行大小限制
innodb_buffer_pool_size = 2048M       # 增加缓冲池（根据内存调整）
innodb_log_file_size = 512M                # 增加日志文件大小，支持大表变更
innodb_log_buffer_size = 16M             # 日志缓冲区，提升写入性能
innodb_large_prefix = 1                        # MySQL 5.7 需启用，支持大索引前缀
performance_schema = 0
explicit_defaults_for_timestamp
skip-external-locking
skip-name-resolve
max_allowed_packet=2560M
wait_timeout=60000
[client]
port = 3360
socket=/home/mysql/mysql.sock

评论已关闭。