背景

2024-05-29 11:47:31.300 [destination = haoduo-be , address = /192.168.0.63:3306 , EventParser] ERROR com.alibaba.otter.canal.common.alarm.LogAlarmHandler - destination:haoduo-be[com.alibaba.otter.canal.parse.exception.CanalParseException: java.lang.IllegalArgumentException: unknow type : 255
Caused by: java.lang.IllegalArgumentException: unknow type : 255
	at com.taobao.tddl.dbsync.binlog.event.TableMapLogEvent.<init>(TableMapLogEvent.java:488)
	at com.taobao.tddl.dbsync.binlog.LogDecoder.decode(LogDecoder.java:182)
	at com.taobao.tddl.dbsync.binlog.LogDecoder.decode(LogDecoder.java:111)
	at com.alibaba.otter.canal.parse.inbound.mysql.MysqlMultiStageCoprocessor$SimpleParserStage.onEvent(MysqlMultiStageCoprocessor.java:269)
	at com.alibaba.otter.canal.parse.inbound.mysql.MysqlMultiStageCoprocessor$SimpleParserStage.onEvent(MysqlMultiStageCoprocessor.java:251)
	at com.lmax.disruptor.BatchEventProcessor.processEvents(BatchEventProcessor.java:168)
	at com.lmax.disruptor.BatchEventProcessor.run(BatchEventProcessor.java:125)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:748)

分析

定位问题点位

[zk: 192.168.0.65:2181(CONNECTED) 1] get /otter/canal/destinations/haoduo-be/1001/cursor
{"@type":"com.alibaba.otter.canal.protocol.position.LogPosition","identity":{"slaveId":-1,"sourceAddress":{"address":"192.168.0.63","port":3306}},"postion":{"gtid":"","included":false,"journalName":"binlog.000002","position":54654455,"serverId":1001,"timestamp":1716884851000}}

mysqlbinlog --no-defaults -vv --base64-output=decode-rows /var/lib/mysql/binlog.000002

[root@wocloud-hd-middleware1 bingo2sql]# ./bingo2sql -h=host -P 3306 -u username -p password --start-file='binlog.000002' --stop-file='binlog.000002' --start-pos=54654455
# 54df1cb9-02ed-11ef-b6ee-fa163e8a01ef:52029
UPDATE `user`.`message_log` SET `status`=1 WHERE `id`=59; # 2024-05-28 16:27:31
ERRO[2024-06-03T11:54:25+08:00] binlog解析操作失败                                  file=bingo2sql.go line=187
ERRO[2024-06-03T11:54:25+08:00] 表`course`.`course`缺少列!当前列数:127,binlog的列数128   file=bingo2sql.go line=188

消失的字段

KEY `idx_collocationalCourseIds` ((cast(json_extract(`collocational_course_ids`,_utf8mb4'$[*]') as unsigned array)))

You can create a multi-valued index in a CREATE TABLE, ALTER TABLE, or CREATE INDEX statement. This requires using CAST(... AS ... ARRAY) in the index definition, which casts same-typed scalar values in a JSON array to an SQL data type array. A virtual column is then generated transparently with the values in the SQL data type array; finally, a functional index (also referred to as a virtual index) is created on the virtual column. It is the functional index defined on the virtual column of values from the SQL data type array that forms the multi-valued index.

深入table_map_event

    public TableMapLogEvent(LogHeader header, LogBuffer buffer, FormatDescriptionLogEvent descriptionEvent){

        // 省略非核心代码
        // 解析column_type
        columnCnt = (int) buffer.getPackedLong();
        columnInfo = new ColumnInfo[columnCnt];
        for (int i = 0; i < columnCnt; i++) {
            ColumnInfo info = new ColumnInfo();
            info.type = buffer.getUint8();
            columnInfo[i] = info;
        }

        if (buffer.position() < buffer.limit()) {
            final int fieldSize = (int) buffer.getPackedLong();
            // 这一步在解析metadata
            decodeFields(buffer, fieldSize);
            // 这一步在解析null_bits
            nullBits = buffer.getBitmap(columnCnt);

            for (int i = 0; i < columnCnt; i++) {
                if (nullBits.get(i)) {
                    columnInfo[i].nullable = true;
                }
            }
            /*
             * After null_bits field, there are some new fields for extra
             * metadata.
             *
             * null_bits之后，就是额外的metadata optional metadata fields
             *
             */
            existOptionalMetaData = false;
            List<TableMapLogEvent.Pair> defaultCharsetPairs = null;
            List<Integer> columnCharsets = null;
            while (buffer.hasRemaining()) {
                // optional metadata fields
                int type = buffer.getUint8();
                int len = (int) buffer.getPackedLong();

                switch (type) {
                    case SIGNEDNESS:
                        parse_signedness(buffer, len);
                        break;
                    case DEFAULT_CHARSET:
                        defaultCharsetPairs = parse_default_charset(buffer, len);
                        break;
                    case COLUMN_CHARSET:
                        columnCharsets = parse_column_charset(buffer, len);
                        break;
                    case COLUMN_NAME:
                        // set @@global.binlog_row_metadata='FULL'
                        // 主要是补充列名相关信息
                        existOptionalMetaData = true;
                        parse_column_name(buffer, len);
                        break;
                    case SET_STR_VALUE:
                        parse_set_str_value(buffer, len, true);
                        break;
                    case ENUM_STR_VALUE:
                        parse_set_str_value(buffer, len, false);
                        break;
                    case GEOMETRY_TYPE:
                        parse_geometry_type(buffer, len);
                        break;
                    case SIMPLE_PRIMARY_KEY:
                        parse_simple_pk(buffer, len);
                        break;
                    case PRIMARY_KEY_WITH_PREFIX:
                        parse_pk_with_prefix(buffer, len);
                        break;
                    default:
                        throw new IllegalArgumentException("unknow type : " + type);
                }
            }

            if (existOptionalMetaData) {
                int index = 0;
                int char_col_index = 0;
                for (int i = 0; i < columnCnt; i++) {
                    int cs = -1;
                    int type = getRealType(columnInfo[i].type, columnInfo[i].meta);
                    if (is_character_type(type)) {
                        if (defaultCharsetPairs != null && !defaultCharsetPairs.isEmpty()) {
                            if (index < defaultCharsetPairs.size()
                                && char_col_index == defaultCharsetPairs.get(index).col_index) {
                                cs = defaultCharsetPairs.get(index).col_charset;
                                index++;
                            } else {
                                cs = default_charset;
                            }

                            char_col_index++;
                        } else if (columnCharsets != null) {
                            cs = columnCharsets.get(index);
                            index++;
                        }

                        columnInfo[i].charset = cs;
                    }
                }
            }
        }
    }

  enum Optional_metadata_field_type {
    SIGNEDNESS = 1,  // UNSIGNED flag of numeric columns
    DEFAULT_CHARSET, /* Character set of string columns, optimized to
                        minimize space when many columns have the
                        same charset. */
    COLUMN_CHARSET,  /* Character set of string columns, optimized to
                        minimize space when columns have many
                        different charsets. */
    COLUMN_NAME,
    SET_STR_VALUE,                // String value of SET columns
    ENUM_STR_VALUE,               // String value of ENUM columns
    GEOMETRY_TYPE,                // Real type of geometry columns
    SIMPLE_PRIMARY_KEY,           // Primary key without prefix
    PRIMARY_KEY_WITH_PREFIX,      // Primary key with prefix
    ENUM_AND_SET_DEFAULT_CHARSET, /* Character set of enum and set
                                     columns, optimized to minimize
                                     space when many columns have the
                                     same charset. */
    ENUM_AND_SET_COLUMN_CHARSET,  /* Character set of enum and set
                                     columns, optimized to minimize
                                     space when many columns have the
                                     same charset. */
  };

• 红色部分是column_count + column_type

• 绿色部分是metadata_length + metadata

• 黄色部分是null_bits

    private final void decodeFields(LogBuffer buffer, final int len) {
        final int limit = buffer.limit();

        buffer.limit(len + buffer.position());
        for (int i = 0; i < columnCnt; i++) {
            ColumnInfo info = columnInfo[i];

            switch (info.type) {
                case MYSQL_TYPE_TINY_BLOB:
                case MYSQL_TYPE_BLOB:
                case MYSQL_TYPE_MEDIUM_BLOB:
                case MYSQL_TYPE_LONG_BLOB:
                case MYSQL_TYPE_DOUBLE:
                case MYSQL_TYPE_FLOAT:
                case MYSQL_TYPE_GEOMETRY:
                case MYSQL_TYPE_JSON:
                    /*
                     * These types store a single byte.
                     */
                    info.meta = buffer.getUint8();
                    break;
                case MYSQL_TYPE_SET:
                case MYSQL_TYPE_ENUM:
                    /*
                     * log_event.h : MYSQL_TYPE_SET & MYSQL_TYPE_ENUM : This
                     * enumeration value is only used internally and cannot
                     * exist in a binlog.
                     */
                    logger.warn("This enumeration value is only used internally "
                                + "and cannot exist in a binlog: type=" + info.type);
                    break;
                case MYSQL_TYPE_STRING: {
                    /*
                     * log_event.h : The first byte is always
                     * MYSQL_TYPE_VAR_STRING (i.e., 253). The second byte is the
                     * field size, i.e., the number of bytes in the
                     * representation of size of the string: 3 or 4.
                     */
                    int x = (buffer.getUint8() << 8); // real_type
                    x += buffer.getUint8(); // pack or field length
                    info.meta = x;
                    break;
                }
                case MYSQL_TYPE_BIT:
                    info.meta = buffer.getUint16();
                    break;
                case MYSQL_TYPE_VARCHAR:
                    /*
                     * These types store two bytes.
                     */
                    info.meta = buffer.getUint16();
                    break;
                case MYSQL_TYPE_NEWDECIMAL: {
                    int x = buffer.getUint8() << 8; // precision
                    x += buffer.getUint8(); // decimals
                    info.meta = x;
                    break;
                }
                case MYSQL_TYPE_TIME2:
                case MYSQL_TYPE_DATETIME2:
                case MYSQL_TYPE_TIMESTAMP2: {
                    info.meta = buffer.getUint8();
                    break;
                }
                default:
                    info.meta = 0;
                    break;
            }
        }
        buffer.limit(limit);
    }

<tr> <td>MYSQL_TYPE_TYPED_ARRAY</td><td>15</td> <td>up to 4 bytes</td> <td> - The first byte holds the MySQL type for the elements. - The following 0, 1, 2, or 3 bytes holds the metadata for the MySQL type for the elements. The contents of these bytes depends on the element type, as described in the other rows of this table. </td> </tr>

rows_event.h

mysql

结论

问题原因

unknow type : 后面可能是任意值

1. 可以对解析到的metadata长度和实际的metadata的长度字段做对比，如果不一致就报错

2. 对于不识别的类型也可以直接做报错处理，不能简单归为默认没有metadata

3. 或者是有些metadata其实对于后续binlog解析没有影响，那也可以直接忽略未读完的metadata，继续往后读取（这种方案不太严谨）

回溯修复过程

从库独有的generated column

Replication: With row-based replication in use (binlog_format=ROW), stored generated columns that existed only on the replica were not being updated when the other fields in the row were inserted or updated. These columns are now evaluated when the row image is unpacked, and their value is updated in the row image before it is applied. (Bug #32292413, Bug #101978)

重构

扩展阅读

不可见列

生成列

最小化实验

CREATE TABLE `generated_column_test`  (
  `id` bigint NOT NULL,
  `json_array` json NULL,
  `gen_stored` varchar(255) AS (concat(id, ':stored-generated')) STORED NULL,
  `gen_virtual` varchar(255) AS (concat(id, ':virtual-generated')) VIRTUAL NULL,
  PRIMARY KEY (`id`),
  KEY `idx_array` ((cast(json_extract(`json_array`,_utf8mb4'$[*]') as unsigned array)))
);

insert into generated_column_test(id, json_array) values (1, '[1,2,3]');

1. 只要是generated column，无论是virtual还是stored，都会写入binlog

2. 对于多值索引产生的虚拟列，8.0.18会写入binlog，主要影响的是table_map_event和row_event，也就是下图红色、绿色、黄色以及未标注出来的rows_event里的相关信息

3. 8.0.18 虽然增加了一列，但是在具体的row_event里，并没有具体的列值，只有列描述信息。在列值方面，和8.0.25一模一样（图中两个竖线包围的67个字节）

1. 只有stored列是被存储到聚簇索引中，virtual不会

2. 多值索引列值也没有存储到聚簇索引中，只存在于多值索引页中

3. 多值索引页也就是普通的二级索引页，只是把多值打散了，比如这里的二级索引里会有三条记录，分别是 1-1、2-1、3-1

4. 8.0.18和8.0.25版本下，ibd文件的表现一致

参考

1. binlog 解析(五) Table_map_event

2. 解密MySQL 8.0 multi-valued indexes

3. MySQL 5.7新特性之generated column

4. 010Editor脚本语法入门

5. [原创]宇宙最强 010 Editor 模板开发教程（附带示例）