【严重】当修改结构体主键comment时，会意外丢失主键的自增属性 #2444

New Issue

lijunshi · 2024-04-20T05:13:28Z

lijunshi commented

2024-04-20 05:13:28 +00:00

我们的项目使用了xorm，数据库使用的是Mysql。近日发现，某些表格的主键列的自增属性会莫名消失，查看xorm源码后，发现有bug。

测试：

数据库：mysql Ver 8.0.36
xorm：v1.3.9

定义结构体：

type User struct {
	Id         int       `xorm:"pk autoincr 'id'"`
	Name       string    `xorm:"'name' notnull comment('用户昵称')" json:"name"`
}

使用Sync2来同步表结构

package main

import (
	_ "github.com/go-sql-driver/mysql"
	"fmt"
)

var tablesList = map[string]interface{}{
	"user":      &User{},
}

func CreateTables() {
	for tablename, table := range tablesList {
		err := MasterDB.Sync2(table)
		if err != nil {
			fmt.Printf("%s Sync2 err %+v\n", tablename, err)
		}
	}
}

user表不存在时，当进程启动时，xorm会自动创建user表

[xorm] [info]  2024/04/20 13:06:17.386236 PING DATABASE mysql
[xorm] [info]  2024/04/20 13:06:17.410196 [SQL] SELECT `TABLE_NAME`, `ENGINE`, `AUTO_INCREMENT`, `TABLE_COMMENT`, `TABLE_COLLATION` from `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`=? AND (`ENGINE`='MyISAM' OR `ENGINE` = 'InnoDB' OR `ENGINE` = 'TokuDB') [test] - 1.702826ms
[xorm] [info]  2024/04/20 13:06:17.427798 [SQL] CREATE TABLE IF NOT EXISTS `user` (`id` INT PRIMARY KEY AUTO_INCREMENT NOT NULL, `name` VARCHAR(255) NOT NULL COMMENT '用户昵称') DEFAULT CHARSET utf8mb4 [] - 17.521232ms

此时表结构为

-- test.`user` definition

CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL COMMENT '用户昵称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHAR

主键id是自增属性的

当我们给id加一下comment，
结构体变为

type User struct {
	Id         int       `xorm:"pk autoincr 'id' comment('主键')"`
	Name       string    `xorm:"'name' notnull comment('用户昵称')" json:"name"`
}

重新启动进程，xorm触发Alter table指令

[xorm] [info]  2024/04/20 13:09:46.608179 PING DATABASE mysql
[xorm] [info]  2024/04/20 13:09:46.629036 [SQL] SELECT `TABLE_NAME`, `ENGINE`, `AUTO_INCREMENT`, `TABLE_COMMENT`, `TABLE_COLLATION` from `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`=? AND (`ENGINE`='MyISAM' OR `ENGINE` = 'InnoDB' OR `ENGINE` = 'TokuDB') [test] - 5.589337ms
[xorm] [info]  2024/04/20 13:09:46.630330 [SQL] SELECT `COLUMN_NAME`, `IS_NULLABLE`, `COLUMN_DEFAULT`, `COLUMN_TYPE`, `COLUMN_KEY`, `EXTRA`, `COLUMN_COMMENT`, `CHARACTER_MAXIMUM_LENGTH`, (INSTR(VERSION(), 'maria') > 0 && (SUBSTRING_INDEX(VERSION(), '.', 1) > 10 || (SUBSTRING_INDEX(VERSION(), '.', 1) = 10 && (SUBSTRING_INDEX(SUBSTRING(VERSION(), 4), '.', 1) > 2 || (SUBSTRING_INDEX(SUBSTRING(VERSION(), 4), '.', 1) = 2 && SUBSTRING_INDEX(SUBSTRING(VERSION(), 6), '-', 1) >= 7))))) AS NEEDS_QUOTE, `COLLATION_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? ORDER BY `COLUMNS`.ORDINAL_POSITION ASC [test user] - 1.238634ms
[xorm] [info]  2024/04/20 13:09:46.632052 [SQL] SELECT `INDEX_NAME`, `NON_UNIQUE`, `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`STATISTICS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? ORDER BY `SEQ_IN_INDEX` [test user] - 1.606127ms
[xorm] [info]  2024/04/20 13:09:46.661717 [SQL] ALTER TABLE `user` MODIFY COLUMN `id` INT NOT NULL COMMENT '主键' [] - 29.621687ms

注意xorm执行的是

ALTER TABLE `user` MODIFY COLUMN `id` INT NOT NULL COMMENT '主键'

这句话没有带设置AUTO_INCREMENT属性，等于删除了主键的AUTO_INCREMENT！

此时表结构更改为

-- test.`user` definition

CREATE TABLE `user` (
  `id` int NOT NULL COMMENT '主键',
  `name` varchar(255) NOT NULL COMMENT '用户昵称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

正确的修改指令应为

ALTER TABLE `user` MODIFY COLUMN `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键'

源码分析

从源码的角度分析，BUG来自：https://gitea.com/xorm/xorm/src/tag/v1.3.9/dialects/mysql.go#L403

// ModifyColumnSQL returns a SQL to modify SQL
func (db *mysql) ModifyColumnSQL(tableName string, col *schemas.Column) string {
	s, _ := ColumnString(db.dialect, col, false, true)
	if col.Comment != "" {
		s += fmt.Sprintf(" COMMENT '%s'", col.Comment)
	}
	return fmt.Sprintf("ALTER TABLE %s MODIFY COLUMN %s", db.quoter.Quote(tableName), s)
}

ColumnString()返回的SQL string 是不带AUTO_INCREMENT ，至于为什么不带，看看ColumnString的定义：https://gitea.com/xorm/xorm/src/tag/v1.3.9/dialects/dialect.go#L300
其中有一段是这么写的，传进去的includePrimaryKey 是false所以AUTO_INCREMENT 是一定不会写到字符串返回的。

	if includePrimaryKey && col.IsPrimaryKey {
		if _, err := bd.WriteString(" PRIMARY KEY"); err != nil {
			return "", err
		}
		if col.IsAutoIncrement {
			if err := bd.WriteByte(' '); err != nil {
				return "", err
			}
			if _, err := bd.WriteString(dialect.AutoIncrStr()); err != nil {
				return "", err
			}
		}
	}

总的逻辑：当结构体的主键comment跟数据库表的comment不一致时，会触发修改数据库表主键属性的逻辑，调用了ModifyColumnSQL，而生成SQL语句的ColumnString没有将自增属性也写到SQL中，导致最后出来的SQL语句是不带自增属性的

ALTER TABLE `user` MODIFY COLUMN `id` INT NOT NULL COMMENT '主键'

要修复的话，大概就是这么改

// ModifyColumnSQL returns a SQL to modify SQL
func (db *mysql) ModifyColumnSQL(tableName string, col *schemas.Column) string {
	s, _ := ColumnString(db.dialect, col, false, true)
	if col.IsAutoIncrement {
		s += " AUTO_INCREMENT "
	}
	if col.Comment != "" {
		s += fmt.Sprintf(" COMMENT '%s'", col.Comment)
	}
	return fmt.Sprintf("ALTER TABLE %s MODIFY COLUMN %s", db.quoter.Quote(tableName), s)
}

我们的项目使用了xorm，数据库使用的是Mysql。近日发现，某些表格的主键列的自增属性会莫名消失，查看xorm源码后，发现有bug。 # 测试：数据库：mysql Ver 8.0.36 xorm：v1.3.9 定义结构体： ``` type User struct { Id int `xorm:"pk autoincr 'id'"` Name string `xorm:"'name' notnull comment('用户昵称')" json:"name"` } ``` 使用Sync2来同步表结构 ``` package main import ( _ "github.com/go-sql-driver/mysql" "fmt" ) var tablesList = map[string]interface{}{ "user": &User{}, } func CreateTables() { for tablename, table := range tablesList { err := MasterDB.Sync2(table) if err != nil { fmt.Printf("%s Sync2 err %+v\n", tablename, err) } } } ``` user表不存在时，当进程启动时，xorm会自动创建user表 ``` [xorm] [info] 2024/04/20 13:06:17.386236 PING DATABASE mysql [xorm] [info] 2024/04/20 13:06:17.410196 [SQL] SELECT `TABLE_NAME`, `ENGINE`, `AUTO_INCREMENT`, `TABLE_COMMENT`, `TABLE_COLLATION` from `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`=? AND (`ENGINE`='MyISAM' OR `ENGINE` = 'InnoDB' OR `ENGINE` = 'TokuDB') [test] - 1.702826ms [xorm] [info] 2024/04/20 13:06:17.427798 [SQL] CREATE TABLE IF NOT EXISTS `user` (`id` INT PRIMARY KEY AUTO_INCREMENT NOT NULL, `name` VARCHAR(255) NOT NULL COMMENT '用户昵称') DEFAULT CHARSET utf8mb4 [] - 17.521232ms ``` 此时表结构为 ``` -- test.`user` definition CREATE TABLE `user` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) NOT NULL COMMENT '用户昵称', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHAR ``` 主键id是自增属性的当我们给id加一下comment，结构体变为 ``` type User struct { Id int `xorm:"pk autoincr 'id' comment('主键')"` Name string `xorm:"'name' notnull comment('用户昵称')" json:"name"` } ``` 重新启动进程，xorm触发Alter table指令 ``` [xorm] [info] 2024/04/20 13:09:46.608179 PING DATABASE mysql [xorm] [info] 2024/04/20 13:09:46.629036 [SQL] SELECT `TABLE_NAME`, `ENGINE`, `AUTO_INCREMENT`, `TABLE_COMMENT`, `TABLE_COLLATION` from `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA`=? AND (`ENGINE`='MyISAM' OR `ENGINE` = 'InnoDB' OR `ENGINE` = 'TokuDB') [test] - 5.589337ms [xorm] [info] 2024/04/20 13:09:46.630330 [SQL] SELECT `COLUMN_NAME`, `IS_NULLABLE`, `COLUMN_DEFAULT`, `COLUMN_TYPE`, `COLUMN_KEY`, `EXTRA`, `COLUMN_COMMENT`, `CHARACTER_MAXIMUM_LENGTH`, (INSTR(VERSION(), 'maria') > 0 && (SUBSTRING_INDEX(VERSION(), '.', 1) > 10 || (SUBSTRING_INDEX(VERSION(), '.', 1) = 10 && (SUBSTRING_INDEX(SUBSTRING(VERSION(), 4), '.', 1) > 2 || (SUBSTRING_INDEX(SUBSTRING(VERSION(), 4), '.', 1) = 2 && SUBSTRING_INDEX(SUBSTRING(VERSION(), 6), '-', 1) >= 7))))) AS NEEDS_QUOTE, `COLLATION_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? ORDER BY `COLUMNS`.ORDINAL_POSITION ASC [test user] - 1.238634ms [xorm] [info] 2024/04/20 13:09:46.632052 [SQL] SELECT `INDEX_NAME`, `NON_UNIQUE`, `COLUMN_NAME` FROM `INFORMATION_SCHEMA`.`STATISTICS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? ORDER BY `SEQ_IN_INDEX` [test user] - 1.606127ms [xorm] [info] 2024/04/20 13:09:46.661717 [SQL] ALTER TABLE `user` MODIFY COLUMN `id` INT NOT NULL COMMENT '主键' [] - 29.621687ms ``` 注意xorm执行的是 ``` ALTER TABLE `user` MODIFY COLUMN `id` INT NOT NULL COMMENT '主键' ``` **这句话没有带设置AUTO_INCREMENT属性，等于删除了主键的AUTO_INCREMENT！** 此时表结构更改为 ``` -- test.`user` definition CREATE TABLE `user` ( `id` int NOT NULL COMMENT '主键', `name` varchar(255) NOT NULL COMMENT '用户昵称', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; ``` 正确的修改指令应为 ``` ALTER TABLE `user` MODIFY COLUMN `id` INT NOT NULL AUTO_INCREMENT COMMENT '主键' ``` # 源码分析从源码的角度分析，BUG来自：https://gitea.com/xorm/xorm/src/tag/v1.3.9/dialects/mysql.go#L403 ``` // ModifyColumnSQL returns a SQL to modify SQL func (db *mysql) ModifyColumnSQL(tableName string, col *schemas.Column) string { s, _ := ColumnString(db.dialect, col, false, true) if col.Comment != "" { s += fmt.Sprintf(" COMMENT '%s'", col.Comment) } return fmt.Sprintf("ALTER TABLE %s MODIFY COLUMN %s", db.quoter.Quote(tableName), s) } ``` ColumnString()返回的SQL string 是不带AUTO_INCREMENT ，至于为什么不带，看看ColumnString的定义：https://gitea.com/xorm/xorm/src/tag/v1.3.9/dialects/dialect.go#L300 其中有一段是这么写的，传进去的includePrimaryKey 是false所以AUTO_INCREMENT 是一定不会写到字符串返回的。 ``` if includePrimaryKey && col.IsPrimaryKey { if _, err := bd.WriteString(" PRIMARY KEY"); err != nil { return "", err } if col.IsAutoIncrement { if err := bd.WriteByte(' '); err != nil { return "", err } if _, err := bd.WriteString(dialect.AutoIncrStr()); err != nil { return "", err } } } ``` 总的逻辑：当结构体的主键comment跟数据库表的comment不一致时，会触发修改数据库表主键属性的逻辑，调用了ModifyColumnSQL，而生成SQL语句的ColumnString没有将自增属性也写到SQL中，导致最后出来的SQL语句是不带自增属性的 ``` ALTER TABLE `user` MODIFY COLUMN `id` INT NOT NULL COMMENT '主键' ``` 要修复的话，大概就是这么改 ``` // ModifyColumnSQL returns a SQL to modify SQL func (db *mysql) ModifyColumnSQL(tableName string, col *schemas.Column) string { s, _ := ColumnString(db.dialect, col, false, true) if col.IsAutoIncrement { s += " AUTO_INCREMENT " } if col.Comment != "" { s += fmt.Sprintf(" COMMENT '%s'", col.Comment) } return fmt.Sprintf("ALTER TABLE %s MODIFY COLUMN %s", db.quoter.Quote(tableName), s) } ```

lunny added the

need

test

label 2024-04-20 13:49:39 +00:00

lijunshi referenced a pull request that will close this issue

2024-04-20 14:17:38 +00:00

fix: Sync2 will remove AUTO_INCREMENT unexpectly (#2444) #2445

lunny referenced this issue from a commit

2024-04-28 16:12:20 +00:00

fix: Sync2 will remove AUTO_INCREMENT unexpectly (#2444) (#2445)

lunny closed this issue

2024-04-28 16:12:21 +00:00