Repository: ariga/atlas
Pull request #387 (2022-01-10 09:13:29)

sql/mysql: minor changes for AUTO_INCREMENT migration


Patch:
diff --git a/sql/mysql/diff.go b/sql/mysql/diff.go
index bf725a75..af104f24 100644
--- a/sql/mysql/diff.go
+++ b/sql/mysql/diff.go
@@ -35,6 +35,9 @@ func (d *diff) SchemaAttrDiff(from, to *schema.Schema) []schema.Change {
 // TableAttrDiff returns a changeset for migrating table attributes from one state to the other.
 func (d *diff) TableAttrDiff(from, to *schema.Table) []schema.Change {
 	var changes []schema.Change
+	if change := d.autoIncChange(from.Attrs, to.Attrs); change != noChange {
+		changes = append(changes, change)
+	}
 	if change := d.charsetChange(from.Attrs, from.Schema.Attrs, to.Attrs); change != noChange {
 		changes = append(changes, change)
 	}
@@ -161,6 +164,22 @@ func (d *diff) charsetChange(from, top, to []schema.Attr) schema.Change {
 	return noChange
 }
 
+// autoIncChange returns the schema change for changing the AUTO_INCREMENT
+// attribute in case it is not the default.
+func (d *diff) autoIncChange(from, to []schema.Attr) schema.Change {
+	var fromA, toA AutoIncrement
+	// The table is empty and AUTO_INCREMENT was not configured. This can happen
+	// because older versions of MySQL (< 8.0) stored the AUTO_INCREMENT counter
+	// in main memory (not persistent), and the value is reset on process restart.
+	if sqlx.Has(from, &fromA) && sqlx.Has(to, &toA) && fromA.V <= 1 && toA.V > 1 {
+		return &schema.ModifyAttr{
+			From: &fromA,
+			To:   &toA,
+		}
+	}
+	return noChange
+}
+
 // indexCollation returns the index collation from its attribute.
 // The default collation is ascending if no order was specified.
 func indexCollation(attr []schema.Attr) *schema.Collation {
diff --git a/sql/mysql/inspect.go b/sql/mysql/inspect.go
index d1578dcf..78ceea1f 100644
--- a/sql/mysql/inspect.go
+++ b/sql/mysql/inspect.go
@@ -8,6 +8,7 @@ import (
 	"context"
 	"database/sql"
 	"fmt"
+	"regexp"
 	"strconv"
 	"strings"
 
@@ -204,7 +205,7 @@ func (i *inspect) columns(ctx context.Context, t *schema.Table) error {
 	}
 	defer rows.Close()
 	for rows.Next() {
-		if err := i.addColumn(t, rows); err != nil {
+		if err := i.addColumn(ctx, t, rows); err != nil {
 			return fmt.Errorf("mysql: %w", err)
 		}
 	}
@@ -212,7 +213,7 @@ func (i *inspect) columns(ctx context.Context, t *schema.Table) error {
 }
 
 // addColumn scans the current row and adds a new column from it to the table.
-func (i *inspect) addColumn(t *schema.Table, rows *sql.Rows) error {
+func (i *inspect) addColumn(ctx context.Context, t *schema.Table, rows *sql.Rows) error {
 	var name, typ, comment, nullable, key, defaults, extra, charset, collation sql.NullString
 	if err := rows.Scan(&name, &typ, &comment, &nullable, &key, &defaults, &extra, &charset, &collation); err != nil {
 		return err
@@ -229,7 +230,7 @@ func (i *inspect) addColumn(t *schema.Table, rows *sql.Rows) error {
 		return err
 	}
 	c.Type.Type = ct
-	if err := extraAttr(t, c, extra.String); err != nil {
+	if err := i.extraAttr(ctx, t, c, extra.String); err != nil {
 		return err
 	}
 	if sqlx.ValidString(defaults) {
@@ -410,7 +411,7 @@ func (i *inspect) checks(ctx context.Context, t *schema.Table) error {
 func (i *inspect) tableNames(ctx context.Context, schema string, opts *schema.InspectOptions) ([]string, error) {
 	query, args := tablesQuery, []interface{}{schema}
 	if opts != nil && len(opts.Tables) > 0 {
-		query += " AND `TABLE_NAME` IN (" + strings.Repeat("?, ", len(opts.Tables)-1) + "?)"
+		query = fmt.Sprintf(tablesQueryArgs, strings.Repeat("?, ", len(opts.Tables)-1)+"?")
 		for _, s := range opts.Tables {
 			args = append(args, s)
 		}
@@ -426,30 +427,9 @@ func (i *inspect) tableNames(ctx context.Context, schema string, opts *schema.In
 	return names, nil
 }
 
-// parseColumn returns column parts, size and signed-info from a MySQL type.
-func parseColumn(typ string) (parts []string, size int64, unsigned bool, err error) {
-	switch parts = strings.FieldsFunc(typ, func(r rune) bool {
-		return r == '(' || r == ')' || r == ' ' || r == ','
-	}); parts[0] {
-	case TypeTinyInt, TypeSmallInt, TypeMediumInt, TypeInt, TypeBigInt:
-		if attr := parts[len(parts)-1]; attr == "unsigned" || attr == "zerofill" {
-			unsigned = true
-		}
-		if len(parts) > 2 || len(parts) == 2 && !unsigned {
-			size, err = strconv.ParseInt(parts[1], 10, 64)
-		}
-	case TypeBinary, TypeVarBinary, TypeChar, TypeVarchar:
-		size, err = strconv.ParseInt(parts[1], 10, 64)
-	}
-	if err != nil {
-		return nil, 0, false, fmt.Errorf("parse %q to int: %w", parts[1], err)
-	}
-	return parts, size, unsigned, nil
-}
-
 // extraAttr parses the EXTRA column from the INFORMATION_SCHEMA.COLUMNS table
 // and appends its parsed representation to the column.
-func extraAttr(t *schema.Table, c *schema.Column, extra string) error {
+func (i *inspect) extraAttr(ctx context.Context, t *schema.Table, c *schema.Column, extra string) error {
 	switch extra := strings.ToLower(extra); extra {
 	case "", "null": // ignore.
 	case defaultGen:
@@ -457,9 +437,17 @@ func extraAttr(t *schema.Table, c *schema.Column, extra string) error {
 		// and it is handled in Driver.addColumn.
 	case autoIncrement:
 		a := &AutoIncrement{}
-		// Reference to the table attribute if exists, as
-		// there can be only one auto_increment in a table.
-		sqlx.Has(t.Attrs, a)
+		// A table can have only one AUTO_INCREMENT column. If it was returned as NULL
+		// from INFORMATION_SCHEMA, it is due to information_schema_stats_expiry and we
+		// need to extract it from the 'CREATE TABLE' command.
+		if !sqlx.Has(t.Attrs, a) {
+			inc, err := i.loadAutoinc(ctx, t)
+			if err != nil {
+				return err
+			}
+			*a = *inc
+			t.Attrs = append(t.Attrs, a)
+		}
 		c.Attrs = append(c.Attrs, a)
 	case "default_generated on update current_timestamp", "on update current_timestamp",
 		"on update current_timestamp()" /* MariaDB format. */ :
@@ -470,6 +458,38 @@ func extraAttr(t *schema.Table, c *schema.Column, extra string) error {
 	return nil
 }
 
+var reAutoinc = regexp.MustCompile(`(?i)\s+AUTO_INCREMENT\s*=\s*(\d+)\s+`)
+
+// loadAutoinc loads the updated AUTO_INCREMENT from CREATE TABLE.
+func (i *inspect) loadAutoinc(ctx context.Context, t *schema.Table) (*AutoIncrement, error) {
+	c, err := i.createStmt(ctx, t)
+	if err != nil {
+		return nil, err
+	}
+	matches := reAutoinc.FindStringSubmatch(c.S)
+	if len(matches) != 2 {
+		return &AutoIncrement{}, nil
+	}
+	v, err := strconv.ParseInt(matches[1], 10, 64)
+	if err != nil {
+		return nil, err
+	}
+	return &AutoIncrement{V: v}, nil
+}
+
+// createStmt loads the CREATE TABLE statement for the table.
+func (i *inspect) createStmt(ctx context.Context, t *schema.Table) (*CreateStmt, error) {
+	c := &CreateStmt{}
+	if sqlx.Has(t.Attrs, c) {
+		return c, nil
+	}
+	if err := i.QueryRowContext(ctx, Build("SHOW CREATE TABLE").Table(t).String()).Scan(&sql.NullString{}, &c.S); err != nil {
+		return nil, fmt.Errorf("query CREATE TABLE %q: %w", t.Name, err)
+	}
+	t.Attrs = append(t.Attrs, c)
+	return c, nil
+}
+
 // myDefaultExpr returns the correct schema.Expr based on the column attributes for MySQL.
 func (i *inspect) myDefaultExpr(c *schema.Column, x, extra string) schema.Expr {
 	// In MySQL, the DEFAULT_GENERATED indicates the column has an expression default value.
@@ -494,6 +514,27 @@ func (i *inspect) myDefaultExpr(c *schema.Column, x, extra string) schema.Expr {
 	return &schema.Literal{V: quote(x)}
 }
 
+// parseColumn returns column parts, size and signed-info from a MySQL type.
+func parseColumn(typ string) (parts []string, size int64, unsigned bool, err error) {
+	switch parts = strings.FieldsFunc(typ, func(r rune) bool {
+		return r == '(' || r == ')' || r == ' ' || r == ','
+	}); parts[0] {
+	case TypeTinyInt, TypeSmallInt, TypeMediumInt, TypeInt, TypeBigInt:
+		if attr := parts[len(parts)-1]; attr == "unsigned" || attr == "zerofill" {
+			unsigned = true
+		}
+		if len(parts) > 2 || len(parts) == 2 && !unsigned {
+			size, err = strconv.ParseInt(parts[1], 10, 64)
+		}
+	case TypeBinary, TypeVarBinary, TypeChar, TypeVarchar:
+		size, err = strconv.ParseInt(parts[1], 10, 64)
+	}
+	if err != nil {
+		return nil, 0, false, fmt.Errorf("parse %q to int: %w", parts[1], err)
+	}
+	return parts, size, unsigned, nil
+}
+
 // hasNumericDefault reports if the given type has a numeric default value.
 func hasNumericDefault(t schema.Type) bool {
 	switch t.(type) {
@@ -545,6 +586,9 @@ const (
 	// Query to list schema tables.
 	tablesQuery = "SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_TYPE` = 'BASE TABLE' AND `TABLE_SCHEMA` = ? ORDER BY `TABLE_NAME`"
 
+	// Query to list specific schema tables.
+	tablesQueryArgs = "SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_TYPE` = 'BASE TABLE' AND `TABLE_SCHEMA` = ? AND `TABLE_NAME` IN (%s) ORDER BY `TABLE_NAME`"
+
 	// Query to list table columns.
 	columnsQuery = "SELECT `COLUMN_NAME`, `COLUMN_TYPE`, `COLUMN_COMMENT`, `IS_NULLABLE`, `COLUMN_KEY`, `COLUMN_DEFAULT`, `EXTRA`, `CHARACTER_SET_NAME`, `COLLATION_NAME` FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE `TABLE_SCHEMA` = ? AND `TABLE_NAME` = ? ORDER BY `ORDINAL_POSITION`"
 
@@ -645,6 +689,12 @@ type (
 		V string
 	}
 
+	// CreateStmt describes the SQL statement used to create a table.
+	CreateStmt struct {
+		schema.Attr
+		S string
+	}
+
 	// OnUpdate attribute for columns with "ON UPDATE CURRENT_TIMESTAMP" as a default.
 	OnUpdate struct {
 		schema.Attr
diff --git a/sql/mysql/migrate.go b/sql/mysql/migrate.go
index 6612db1f..edd72fb5 100644
--- a/sql/mysql/migrate.go
+++ b/sql/mysql/migrate.go
@@ -69,9 +69,7 @@ func (s *state) plan(changes []schema.Change) error {
 	for _, c := range planned {
 		switch c := c.(type) {
 		case *schema.AddTable:
-			if err := s.addTable(c); err != nil {
-				return err
-			}
+			s.addTable(c)
 		case *schema.DropTable:
 			s.dropTable(c)
 		case *schema.ModifyTable:
@@ -126,7 +124,7 @@ func (s *state) topLevel(changes []schema.Change) []schema.Change {
 
 // addTable builds and appends the migrate.Change
 // for creating a table in a schema.
-func (s *state) addTable(add *schema.AddTable) error {
+func (s *state) addTable(add *schema.AddTable) {
 	b := Build("CREATE TABLE").Table(add.T)
 	if sqlx.Has(add.Extra, &schema.IfNotExists{}) {
 		b.P("IF NOT EXISTS")
@@ -163,16 +161,13 @@ func (s *state) addTable(add *schema.AddTable) error {
 			}
 		}
 	})
-	if err := s.tableAttr(b, add.T.Attrs...); err != nil {
-		return err
-	}
+	s.tableAttr(b, add, add.T.Attrs...)
 	s.append(&migrate.Change{
 		Cmd:     b.String(),
 		Source:  add,
 		Reverse: Build("DROP TABLE").Table(add.T).String(),
 		Comment: fmt.Sprintf("create %q table", add.T.Name),
 	})
-	return nil
 }
 
 // dropTable builds and appends the migrate.Change
@@ -285,18 +280,12 @@ func (s *state) alterTable(t *schema.Table, changes []schema.Change) error {
 			b.P("DROP FOREIGN KEY").Ident(change.F.Symbol)
 			reversible = false
 		case *schema.AddAttr:
-			if err := s.tableAttr(b, change.A); err != nil {
-				errors = append(errors, fmt.Sprintf("add attribute: %s", err.Error()))
-			}
+			s.tableAttr(b, change, change.A)
 			// Unsupported reverse operation.
 			reversible = false
 		case *schema.ModifyAttr:
-			if err := s.tableAttr(b, change.To); err != nil {
-				errors = append(errors, fmt.Sprintf("modify attribute: %s", err.Error()))
-			}
-			if err := s.tableAttr(reverse, change.From); err != nil {
-				errors = append(errors, fmt.Sprintf("reverse modify attribute: %s", err.Error()))
-			}
+			s.tableAttr(b, change, change.To)
+			s.tableAttr(reverse.Comma(), change, change.From)
 		case *schema.AddCheck:
 			s.check(b.P("ADD"), change.C)
 			// Reverse operation is supported if
@@ -387,7 +376,7 @@ func (s *state) column(b *sqlx.Builder, t *schema.Table, c *schema.Column) {
 		case *AutoIncrement:
 			b.P("AUTO_INCREMENT")
 			// Auto increment with value should be configured on table options.
-			if a.V != 0 && !sqlx.Has(t.Attrs, &AutoIncrement{}) {
+			if a.V > 0 && !sqlx.Has(t.Attrs, &AutoIncrement{}) {
 				t.Attrs = append(t.Attrs, a)
 			}
 		default:
@@ -443,16 +432,16 @@ func (s *state) fks(b *sqlx.Builder, fks ...*schema.ForeignKey) {
 
 // tableAttr writes the given table attribute to the SQL
 // statement builder when a table is created or altered.
-func (s *state) tableAttr(b *sqlx.Builder, attrs ...schema.Attr) error {
+func (s *state) tableAttr(b *sqlx.Builder, c schema.Change, attrs ...schema.Attr) {
 	for _, a := range attrs {
 		switch a := a.(type) {
 		case *CreateOptions:
 			b.P(a.V)
 		case *AutoIncrement:
-			if a.V == 0 {
-				return fmt.Errorf("missing value for table option AUTO_INCREMENT")
+			// Update the AUTO_INCREMENT if it is an update change or it is not the default.
+			if _, ok := c.(*schema.ModifyAttr); ok || a.V > 1 {
+				b.P("AUTO_INCREMENT", strconv.FormatInt(a.V, 10))
 			}
-			b.P("AUTO_INCREMENT", strconv.FormatInt(a.V, 10))
 		case *schema.Check:
 			// Ignore CHECK constraints as they are not real attributes,
 			// and handled on CREATE or ALTER.
@@ -464,7 +453,6 @@ func (s *state) tableAttr(b *sqlx.Builder, attrs ...schema.Attr) error {
 			b.P("COMMENT", quote(a.Text))
 		}
 	}
-	return nil
 }
 
 // character returns the table character-set from its attributes


Test patch:
diff --git a/sql/mysql/diff_test.go b/sql/mysql/diff_test.go
index 1f2a7055..6d26eb50 100644
--- a/sql/mysql/diff_test.go
+++ b/sql/mysql/diff_test.go
@@ -49,6 +49,17 @@ func TestDiff_TableDiff(t *testing.T) {
 			to:      &schema.Table{Name: "users"},
 			wantErr: true,
 		},
+		{
+			name: "modify counter",
+			from: &schema.Table{Name: "users", Schema: &schema.Schema{Name: "public"}, Attrs: []schema.Attr{&AutoIncrement{V: 1}}},
+			to:   &schema.Table{Name: "users", Attrs: []schema.Attr{&AutoIncrement{V: 100}}},
+			wantChanges: []schema.Change{
+				&schema.ModifyAttr{
+					From: &AutoIncrement{V: 1},
+					To:   &AutoIncrement{V: 100},
+				},
+			},
+		},
 		{
 			name: "add collation",
 			from: &schema.Table{Name: "users", Schema: &schema.Schema{Name: "public"}},
diff --git a/sql/mysql/inspect_test.go b/sql/mysql/inspect_test.go
index c733cd75..74ef79cd 100644
--- a/sql/mysql/inspect_test.go
+++ b/sql/mysql/inspect_test.go
@@ -71,6 +71,14 @@ func TestDriver_InspectTable(t *testing.T) {
 +--------------------+----------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
 | id                 | bigint(20)           |                      | NO          | PRI        | NULL           | auto_increment | NULL               | NULL           |
 +--------------------+----------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
+`))
+				m.ExpectQuery(sqltest.Escape("SHOW CREATE TABLE `test`.`users`")).
+					WillReturnRows(sqltest.Rows(`
++-------+---------------------------------------------------------------------------------------------------------------------------------------------+
+| Table | Create Table                                                                                                                                |
++-------+---------------------------------------------------------------------------------------------------------------------------------------------+
+| users | CREATE TABLE users (id bigint NOT NULL AUTO_INCREMENT) ENGINE=InnoDB AUTO_INCREMENT=55834574848 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
++-------+---------------------------------------------------------------------------------------------------------------------------------------------+
 `))
 				m.noIndexes()
 				m.noFKs()
@@ -83,11 +91,13 @@ func TestDriver_InspectTable(t *testing.T) {
 					&schema.Collation{V: "utf8mb4_0900_ai_ci"},
 					&schema.Comment{Text: "Comment"},
 					&CreateOptions{V: `COMPRESSION="ZLIB"`},
+					&CreateStmt{S: "CREATE TABLE users (id bigint NOT NULL AUTO_INCREMENT) ENGINE=InnoDB AUTO_INCREMENT=55834574848 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin"},
+					&AutoIncrement{V: 55834574848},
 				}, t.Attrs)
 				require.Len(t.PrimaryKey.Parts, 1)
 				require.True(t.PrimaryKey.Parts[0].C == t.Columns[0])
 				require.EqualValues([]*schema.Column{
-					{Name: "id", Type: &schema.ColumnType{Raw: "bigint(20)", Type: &schema.IntegerType{T: "bigint"}}, Attrs: []schema.Attr{&AutoIncrement{}}},
+					{Name: "id", Type: &schema.ColumnType{Raw: "bigint(20)", Type: &schema.IntegerType{T: "bigint"}}, Attrs: []schema.Attr{&AutoIncrement{V: 55834574848}}},
 				}, t.Columns)
 			},
 		},
@@ -102,7 +112,7 @@ func TestDriver_InspectTable(t *testing.T) {
 +--------------------+------------------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
 | column_name        | column_type                  | column_comment       | is_nullable | column_key | column_default | extra          | character_set_name | collation_name |
 +--------------------+------------------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
-| id                 | bigint(20)                   |                      | NO          | PRI        | NULL           | auto_increment | NULL               | NULL           |
+| id                 | bigint(20)                   |                      | NO          | PRI        | NULL           |                | NULL               | NULL           |
 | v57_tiny           | tinyint(1)                   |                      | NO          |            | NULL           |                | NULL               | NULL           |
 | v57_tiny_unsigned  | tinyint(4) unsigned          |                      | NO          |            | NULL           |                | NULL               | NULL           |
 | v57_small          | smallint(6)                  |                      | NO          |            | NULL           |                | NULL               | NULL           |
@@ -127,7 +137,7 @@ func TestDriver_InspectTable(t *testing.T) {
 				require.Len(t.PrimaryKey.Parts, 1)
 				require.True(t.PrimaryKey.Parts[0].C == t.Columns[0])
 				require.EqualValues([]*schema.Column{
-					{Name: "id", Type: &schema.ColumnType{Raw: "bigint(20)", Type: &schema.IntegerType{T: "bigint"}}, Attrs: []schema.Attr{&AutoIncrement{}}},
+					{Name: "id", Type: &schema.ColumnType{Raw: "bigint(20)", Type: &schema.IntegerType{T: "bigint"}}},
 					{Name: "v57_tiny", Type: &schema.ColumnType{Raw: "tinyint(1)", Type: &schema.BoolType{T: "tinyint"}}},
 					{Name: "v57_tiny_unsigned", Type: &schema.ColumnType{Raw: "tinyint(4) unsigned", Type: &schema.IntegerType{T: "tinyint", Unsigned: true}}},
 					{Name: "v57_small", Type: &schema.ColumnType{Raw: "smallint(6)", Type: &schema.IntegerType{T: "smallint"}}},
@@ -155,7 +165,7 @@ func TestDriver_InspectTable(t *testing.T) {
 +---------------+------------------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
 | column_name   | column_type                  | column_comment       | is_nullable | column_key | column_default | extra          | character_set_name | collation_name |
 +---------------+------------------------------+----------------------+-------------+------------+----------------+----------------+--------------------+----------------+
-| id            | bigint(20)                   |                      | NO          | PRI        | NULL           | auto_increment | NULL               | NULL           |
+| id            | bigint(20)                   |                      | NO          | PRI        | NULL           |                | NULL               | NULL           |
 | tiny_int      | tinyint(1)                   |                      | NO          |            | NULL           |                | NULL               | NULL           |
 | longtext      | longtext                     |                      | NO          |            | NULL           |                | NULL               | NULL           |
 | jsonc         | longtext                     |                      | NO          |            | NULL           |                | NULL               | NULL           |
@@ -180,7 +190,7 @@ func TestDriver_InspectTable(t *testing.T) {
 				require.Len(t.PrimaryKey.Parts, 1)
 				require.True(t.PrimaryKey.Parts[0].C == t.Columns[0])
 				require.EqualValues([]*schema.Column{
-					{Name: "id", Type: &schema.ColumnType{Raw: "bigint(20)", Type: &schema.IntegerType{T: "bigint"}}, Attrs: []schema.Attr{&AutoIncrement{}}},
+					{Name: "id", Type: &schema.ColumnType{Raw: "bigint(20)", Type: &schema.IntegerType{T: "bigint"}}},
 					{Name: "tiny_int", Type: &schema.ColumnType{Raw: "tinyint(1)", Type: &schema.BoolType{T: "tinyint"}}},
 					{Name: "longtext", Type: &schema.ColumnType{Raw: "longtext", Type: &schema.StringType{T: "longtext"}}},
 					{Name: "jsonc", Type: &schema.ColumnType{Raw: "json", Type: &schema.JSONType{T: "json"}}},
@@ -450,7 +460,7 @@ func TestDriver_InspectTable(t *testing.T) {
 +-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
 | COLUMN_NAME | COLUMN_TYPE  | COLUMN_COMMENT | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA          | CHARACTER_SET_NAME | COLLATION_NAME     |
 +-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
-| id          | int          |                | NO          | PRI        | NULL           | auto_increment | NULL               | NULL               |
+| id          | int          |                | NO          | PRI        | NULL           |                | NULL               | NULL               |
 | nickname    | varchar(255) |                | NO          | UNI        | NULL           |                | utf8mb4            | utf8mb4_0900_ai_ci |
 | oid         | int          |                | NO          | MUL        | NULL           |                | NULL               | NULL               |
 | uid         | int          |                | NO          | MUL        | NULL           |                | NULL               | NULL               |
@@ -483,7 +493,7 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Name: "unique_index", Unique: true, Table: t, Attrs: []schema.Attr{&IndexType{T: "BTREE"}}},
 				}
 				columns := []*schema.Column{
-					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&AutoIncrement{}}},
+					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}},
 					{Name: "nickname", Type: &schema.ColumnType{Raw: "varchar(255)", Type: &schema.StringType{T: "varchar", Size: 255}}, Indexes: indexes[0:1], Attrs: []schema.Attr{&schema.Charset{V: "utf8mb4"}, &schema.Collation{V: "utf8mb4_0900_ai_ci"}}},
 					{Name: "oid", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Indexes: indexes[2:]},
 					{Name: "uid", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Indexes: indexes[2:]},
@@ -521,7 +531,7 @@ func TestDriver_InspectTable(t *testing.T) {
 +-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
 | COLUMN_NAME | COLUMN_TYPE  | COLUMN_COMMENT | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA          | CHARACTER_SET_NAME | COLLATION_NAME     |
 +-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
-| id          | int          |                | NO          | PRI        | NULL           | auto_increment | NULL               | NULL               |
+| id          | int          |                | NO          | PRI        | NULL           |                | NULL               | NULL               |
 | oid         | int          |                | NO          | MUL        | NULL           |                | NULL               | NULL               |
 | uid         | int          |                | NO          | MUL        | NULL           |                | NULL               | NULL               |
 +-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
@@ -548,7 +558,7 @@ func TestDriver_InspectTable(t *testing.T) {
 					{Symbol: "self_reference", Table: t, OnUpdate: schema.NoAction, OnDelete: schema.Cascade, RefTable: t},
 				}
 				columns := []*schema.Column{
-					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&AutoIncrement{}}, ForeignKeys: fks[0:1]},
+					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, ForeignKeys: fks[0:1]},
 					{Name: "oid", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, ForeignKeys: fks[0:1]},
 					{Name: "uid", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, ForeignKeys: fks[1:2]},
 				}
@@ -570,7 +580,7 @@ func TestDriver_InspectTable(t *testing.T) {
 +-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
 | COLUMN_NAME | COLUMN_TYPE  | COLUMN_COMMENT | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA          | CHARACTER_SET_NAME | COLLATION_NAME     |
 +-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
-| id          | int          |                | NO          | PRI        | NULL           | auto_increment | NULL               | NULL               |
+| id          | int          |                | NO          | PRI        | NULL           |                | NULL               | NULL               |
 | c1          | int          |                | NO          | MUL        | NULL           |                | NULL               | NULL               |
 +-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
 `))
@@ -591,7 +601,7 @@ func TestDriver_InspectTable(t *testing.T) {
 				require.Equal("users", t.Name)
 				require.Equal("public", t.Schema.Name)
 				columns := []*schema.Column{
-					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&AutoIncrement{}}},
+					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}},
 					{Name: "c1", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}},
 				}
 				require.EqualValues(columns, t.Columns)
@@ -680,7 +690,7 @@ func TestDriver_InspectSchema(t *testing.T) {
 +-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
 | COLUMN_NAME | COLUMN_TYPE  | COLUMN_COMMENT | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA          | CHARACTER_SET_NAME | COLLATION_NAME     |
 +-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
-| id          | int          |                | NO          | PRI        | NULL           | auto_increment | NULL               | NULL               |
+| id          | int          |                | NO          | PRI        | NULL           |                | NULL               | NULL               |
 | spouse_id   | int          |                | YES         | NULL       | NULL           |                | NULL               | NULL               |
 +-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
 		`))
@@ -702,7 +712,7 @@ func TestDriver_InspectSchema(t *testing.T) {
 +-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
 | COLUMN_NAME | COLUMN_TYPE  | COLUMN_COMMENT | IS_NULLABLE | COLUMN_KEY | COLUMN_DEFAULT | EXTRA          | CHARACTER_SET_NAME | COLLATION_NAME     |
 +-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
-| id          | int          |                | NO          | PRI        | NULL           | auto_increment | NULL               | NULL               |
+| id          | int          |                | NO          | PRI        | NULL           |                | NULL               | NULL               |
 | owner_id    | int          |                | YES         | NULL       | NULL           |                | NULL               | NULL               |
 +-------------+--------------+----------------+-------------+------------+----------------+----------------+--------------------+--------------------+
 		`))
@@ -728,7 +738,7 @@ func TestDriver_InspectSchema(t *testing.T) {
 					{Symbol: "spouse_id", Table: users, OnUpdate: schema.NoAction, OnDelete: schema.Cascade, RefTable: users},
 				}
 				userColumns := []*schema.Column{
-					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&AutoIncrement{}}},
+					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}},
 					{Name: "spouse_id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}, Null: true}, ForeignKeys: userFKs},
 				}
 				userFKs[0].Columns = userColumns[1:]
@@ -741,7 +751,7 @@ func TestDriver_InspectSchema(t *testing.T) {
 					{Symbol: "owner_id", Table: pets, OnUpdate: schema.NoAction, OnDelete: schema.Cascade, RefTable: users, RefColumns: userColumns[:1]},
 				}
 				petsColumns := []*schema.Column{
-					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}, Attrs: []schema.Attr{&AutoIncrement{}}},
+					{Name: "id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}}},
 					{Name: "owner_id", Type: &schema.ColumnType{Raw: "int", Type: &schema.IntegerType{T: "int"}, Null: true}, ForeignKeys: petsFKs},
 				}
 				petsFKs[0].Columns = petsColumns[1:]
diff --git a/sql/mysql/migrate_test.go b/sql/mysql/migrate_test.go
index 372d46e8..36190a02 100644
--- a/sql/mysql/migrate_test.go
+++ b/sql/mysql/migrate_test.go
@@ -299,6 +299,10 @@ func TestPlanChanges(t *testing.T) {
 									Attrs: []schema.Attr{&Enforced{}},
 								},
 							},
+							&schema.ModifyAttr{
+								From: &AutoIncrement{V: 1},
+								To:   &AutoIncrement{V: 1000},
+							},
 						},
 					}
 				}(),
@@ -307,8 +311,8 @@ func TestPlanChanges(t *testing.T) {
 				Reversible: true,
 				Changes: []*migrate.Change{
 					{
-						Cmd:     "ALTER TABLE `users` ADD COLUMN `name` varchar(255) NOT NULL, ADD INDEX `id_key` (`id`) COMMENT \"comment\", ADD CONSTRAINT `id_nonzero` CHECK (id > 0) ENFORCED",
-						Reverse: "ALTER TABLE `users` DROP COLUMN `name`, DROP INDEX `id_key`, DROP CHECK `id_nonzero`",
+						Cmd:     "ALTER TABLE `users` ADD COLUMN `name` varchar(255) NOT NULL, ADD INDEX `id_key` (`id`) COMMENT \"comment\", ADD CONSTRAINT `id_nonzero` CHECK (id > 0) ENFORCED, AUTO_INCREMENT 1000",
+						Reverse: "ALTER TABLE `users` DROP COLUMN `name`, DROP INDEX `id_key`, DROP CHECK `id_nonzero`, AUTO_INCREMENT 1",
 					},
 				},
 			},