Unexpected query error using gorm.io/driver/clickhouse when UseTransaction = true

[JustSamuel]

Running the example of the README gives a 101 Unexpected query when running multiple migrations with UseTransaction = true.

package main

import (
	"github.com/go-gormigrate/gormigrate/v2"
	"github.com/google/uuid"
	"gorm.io/driver/clickhouse"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
	"log"
)

func main() {
	db, err := gorm.Open(clickhouse.Open("clickhouse://clickhouse:9000"), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info),
	})
	if err != nil {
		log.Fatal(err)
	}

	m := gormigrate.New(db, &gormigrate.Options{
		TableName:                 "migrations",
		IDColumnName:              "id",
		IDColumnSize:              255,
		UseTransaction:            true,
		ValidateUnknownMigrations: false,
	}, []*gormigrate.Migration{{
		// create `users` table
		ID: "201608301400",
		Migrate: func(tx *gorm.DB) error {
			// it's a good pratice to copy the struct inside the function,
			// so side effects are prevented if the original struct changes during the time
			type user struct {
				ID   uuid.UUID `gorm:"type:UUID;primaryKey;uniqueIndex"`
				Name string
			}
			return tx.Migrator().CreateTable(&user{})
		},
		Rollback: func(tx *gorm.DB) error {
			return tx.Migrator().DropTable("users")
		},
	}, {
		// add `age` column to `users` table
		ID: "201608301415",
		Migrate: func(tx *gorm.DB) error {
			// when table already exists, define only columns that are about to change
			type user struct {
				Age int
			}
			return tx.Migrator().AddColumn(&user{}, "Age")
		},
		Rollback: func(tx *gorm.DB) error {
			type user struct {
				Age int
			}
			return db.Migrator().DropColumn(&user{}, "Age")
		},
	}, {
		// create `organizations` table where users belong to
		ID: "201608301430",
		Migrate: func(tx *gorm.DB) error {
			type organization struct {
				ID      uuid.UUID `gorm:"type:UUID;primaryKey;uniqueIndex"`
				Name    string
				Address string
			}
			if err := tx.Migrator().CreateTable(&organization{}); err != nil {
				return err
			}
			type user struct {
				OrganizationID uuid.UUID `gorm:"type:UUID"`
			}
			return tx.Migrator().AddColumn(&user{}, "OrganizationID")
		},
		Rollback: func(tx *gorm.DB) error {
			type user struct {
				OrganizationID uuid.UUID `gorm:"type:UUID"`
			}
			if err := db.Migrator().DropColumn(&user{}, "OrganizationID"); err != nil {
				return err
			}
			return tx.Migrator().DropTable("organizations")
		},
	}})

	if err = m.Migrate(); err != nil {
		log.Fatalf("Migration failed: %v", err)
	}
	log.Println("Migration did run successfully")
}

The error received is an code: 101, message: Unexpected packet Query received from client after the first migration.
The last function called before the commit is the insertMigration function. Slightly tweaking this has different results.

For example, the following works without error:

func (g *Gormigrate) insertMigration(id string) error {
	record := map[string]interface{}{g.options.IDColumnName: id}
	return g.tx.Create(record).Error
}

But even something like return g.tx.Table("migrations").Create(record).Error fails.

Funnily enough, the following does work, most likely because the transaction is committed earlier.

func (g *Gormigrate) insertMigration(id string) error {
	record := map[string]interface{}{g.options.IDColumnName: id}
	return g.db.Table(g.options.TableName).Create(record).Error
}

System

• go 1.21.0
• gorm.io/driver/clickhouse v0.5.1
• gorm.io/gorm v1.25.5
• clickhouse-server:23.9-alpine

[avakarev]

Hi, @JustSamuel, sorry for not reacting on your issue yet you opened couple of months ago. I'll try to test clickhouse with your code example next weekend, potentially it's also worth to add clickhouse container to integration test suite.

[avakarev]

Sorry, didn't manage it yet.