go-gorm/mysql

GORM mysql driver

GORM MySQL Driver

Quick Start

import (
  "gorm.io/driver/mysql"
  "gorm.io/gorm"
)

// https://github.com/go-sql-driver/mysql
dsn := "gorm:gorm@tcp(localhost:9910)/gorm?charset=utf8&parseTime=True&loc=Local"
db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})

Configuration

import (
  "gorm.io/driver/mysql"
  "gorm.io/gorm"
)

var datetimePrecision = 2

db, err := gorm.Open(mysql.New(mysql.Config{
  DSN: "gorm:gorm@tcp(localhost:9910)/gorm?charset=utf8&parseTime=True&loc=Local", // data source name, refer https://github.com/go-sql-driver/mysql#dsn-data-source-name
  DefaultStringSize: 256, // add default size for string fields, by default, will use db type `longtext` for fields without size, not a primary key, no index defined and don't have default values
  DisableDatetimePrecision: true, // disable datetime precision support, which not supported before MySQL 5.6
  DefaultDatetimePrecision: &datetimePrecision, // default datetime precision
  DontSupportRenameIndex: true, // drop & create index when rename index, rename index not supported before MySQL 5.7, MariaDB
  DontSupportRenameColumn: true, // use change when rename column, rename rename not supported before MySQL 8, MariaDB
  SkipInitializeWithVersion: false, // smart configure based on used version
}), &gorm.Config{})

Customized Driver

import (
  _ "example.com/my_mysql_driver"
  "gorm.io/gorm"
  "gorm.io/driver/mysql"
)

db, err := gorm.Open(mysql.New(mysql.Config{
  DriverName: "my_mysql_driver_name",
  DSN: "gorm:gorm@tcp(localhost:9910)/gorm?charset=utf8&parseTime=True&loc=Local", // data source name, refer https://github.com/go-sql-driver/mysql#dsn-data-source-name
})

Checkout https://gorm.io for details.

mysql's Issues

What's different between this repository and gorm

I have used gorm for a few months, and today github recommends me this repository. I'm not sure what's different from gorm. By this repository name mysql I assume this is a light-weight package to operate mysql and Gorm provides a more general way to operate different database.
I'm not sure so I mention this issue:)

v1.0.4 requires gorm.io/[email protected](unknown revision)

Description

go get: gorm.io/driver/[email protected] requires
gorm.io/[email protected]: reading gorm.io/gorm/go.mod at revision v1.20.12: unknown revision v1.20.12

MySQL 8.0 - Deprecate VALUES syntax in INSERT ... ON DUPLICATE KEY UPDATE

Describe the feature

On MySQL 8.0 server the waring message is raised:

1287 'VALUES function' is deprecated and will be removed in a future release. 
Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace 
VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead

More details at MySQL work log

Motivation

I don't know which future release will remove this function but seems reasonable to implement the new supported alias in the driver in case.

Related Issues

Clause.(clause.Returning{}) dosen't work in a delete opertaion

Clause.(clause.Returning{}) dosen't active in a delete opertaion

Version: gorm 1.23.8 and golang 1.18.3

Sorry for my poor English...

I try to use Clause.(clause.Returning{}) to returning the obj which has been deleted. But It doesn't work.

At first, I thought the database dose not support Returning. So I try it by raw sql, it works.

Then, I try to find the problem by reading source code and debug it. Limited by my skill level, I can only find out the final raw sql (generated by gorm) does not contain RETURNING .

Partof callback.go/Execute function

statement.go/AddClause function

But it can be seen from the screenshot that RETURNING is added to db.Statement.Clauses map but it seems like no subsequent code to use it.
I don't know which step is wrong. If anyone could help me take a look, it would be greatly appreciated.

The document you expected this should be explained

Expected answer

Just want got the return successfully.

Ignored database name with table name.

Description

In the below program, for User struct configured table name as test.user using the TableName. However, when I run the DropTable query on User struct, I am getting error Error 1046 (3D000): No database selected. Drop query logged as DROP TABLE IF EXISTS user CASCADE. As you can see, the database name is missing with table name 'user' in the query which is configured in the TableName. Same issue with AlterColumn, RenameColumn, and RenameIndex.

Program

package main

import (
	"fmt"
	"time"

	"gorm.io/driver/mysql"
	"gorm.io/gorm"
)

// Tabler interface for overriding table name.
type Tabler interface {
	TableName() string
}
type User struct {
	ID        uint      // Standard field for the primary key
	Name      string    // A regular string field
}
// TableName overrides the table name.
func (User) TableName() string {
	return "test.user"
}

func main() {
	dsn := "<user>:<password>@tcp(127.0.0.1:3306)/?charset=utf8mb4&parseTime=True&loc=Local"
	db, err := gorm.Open(mysql.Open(dsn), &gorm.Config{})
	if err != nil {
		panic("failed to connect database")
	}
	err = db.Migrator().CreateTable(&User{})
	if err != nil {
		fmt.Print(err)
		return
	}
	fmt.Println("Created the User table")
	err = db.Migrator().DropTable(&User{})
	if err != nil {
		fmt.Print(err)
		return
	}
	fmt.Println("Dropped the User table")
}

Output

% go run droptable/main.go
Created the User table

2024/04/27 02:11:34 /Users/mkchevuri/myprojects/testnotebook/droptable/main.go:42 Error 1046 (3D000): No database selected
[0.269ms] [rows:0] DROP TABLE IF EXISTS `user` CASCADE
Error 1046 (3D000): No database selected%        

Solution

In DropTable, for getting the table name, we are using clause.Table{Name: stmt.Table}). stmt.Table returns table name without database. Instead of stmt.Table, if we use the Migrator.CurrentTable which returns a clause with database name + table name (Which configured through TableName function).

mysql/migrator.go

Line 298 in 9d1b8d6

return tx.Exec("DROP TABLE IF EXISTS ? CASCADE", clause.Table{Name: stmt.Table}).Error

v1.0.3 undefined: gorm.ColumnType

GORM Playground Link

Description

in v1.0.3, during boot up:

../go/pkg/mod/gorm.io/driver/[email protected]/migrator.go:192:65: undefined: gorm.ColumnType
../go/pkg/mod/gorm.io/driver/[email protected]/migrator.go:193:23: undefined: gorm.ColumnType

errors disappeared after downgrading to v1.0.2.

Use with mariadb foreign key error.

models declaration

type BaseModel struct {
	ID        string          `json:"id,omitempty" gorm:"primaryKey" validate:"alpha"`
	CreatedAt *time.Time      `json:"createdAt,omitempty"`
	UpdatedAt *time.Time      `json:"updatedAt,omitempty"`
	DeletedAt *gorm.DeletedAt `json:"deletedAt,omitempty"`
}

type User struct {
	BaseModel
	Name    string `json:"name,omitempty" gorm:"unique;not null" validate:"minLen=5;alpha"`
	Role    *Role  `json:"role,omitempty" gormy:"edge=one"`
	RoleID  string `json:"roleId,omitempty"`
	Group   *Group `json:"group,omitempty" gormy:"edge=one"`
	GroupID string `json:"groupId,omitempty"`
}

type Role struct {
	BaseModel
	Name          string            `json:"name,omitempty" gorm:"unique;not null" validate:"minLen=5;alpha"`
	DeniedActions string            `json:"deniedActions,omitempty" gorm:"unique"`
	DeniedFields  map[string]string `json:"deniedFields,omitempty" gorm:"serializer:json"`
	Users         []User            `json:"users,omitempty" gormy:"edge=many"`
}
type Group struct {
	BaseModel
	Name  string `json:"name,omitempty" gorm:"unique;not null" validate:"minLen=5;alpha"`
	Users []User `json:"users,omitempty" gormy:"edge=many"`
}

Error when creating tables:

mariadb version: mariadb from 11.0.2-MariaDB, client 15.2 for Win64 (AMD64),

Execute ALTER TABLE every time when use AutoMigrate

GORM Playground Link

Description

Since 1.0.3 version, Execute ALTER TABLE id column every time when use AutoMigrate.
Until 1.0.2 version, did not execute duplicate migrate on once migrated column.

2020/12/18 17:01:34 /go/pkg/mod/gorm.io/driver/[email protected]/migrator.go:83 SLOW SQL >= 200ms
[215.581ms] [rows:0] ALTER TABLE `...` MODIFY COLUMN `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT

model setting

type Model struct {
	ID        int       `gorm:"type:INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;primaryKey"`
	...
}

mariadb error

Hi dears, when I upgrade my code to gormv2 and use MySQL v1.5.1, I get syntax error in my db like this:

Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AUTO_INCREMENT NOT NULL,email varchar(255) NOT NULL,password varchar(255) NO' at line 1
I do not have any idea, could you please help me?

Auto Migrate: ERROR: Unknown column 'datetime_precision' in 'field list'

Description

The latest version change the implemention of ColumnType, and the error shown when run auto migrate. it is due to Memsql dose not have datetime_precision in information_schema.columns.

Could we add an option to disable it?

DB: Memsql
Version: 7.1.10

Unique key columns duplicated with MariaDB

I came across the following bug. Sorry for skipping the playground, but the following code should be sufficient for reproducing the bug.

• Gorm version: 1.23.5
• Driver version: 1.3.3
• MariaDB version: 10.7.3

Consider this script:

package main

import (
  "gorm.io/gorm"
  "gorm.io/driver/mysql"
)

type Product struct {
  gorm.Model
  Name string `gorm:"unique"`
}

func main() {
  db, err := gorm.Open(mysql.New(mysql.Config{
  	DriverName: "mysql",
  	DSN: "root:shhh@tcp(127.0.0.1:3306)/gormbug?charset=utf8mb4&parseTime=true&loc=Local&sql_mode=ANSI_QUOTES",
  	DontSupportRenameIndex:  true,
  	DontSupportRenameColumn: true,
 	SkipInitializeWithVersion: false,
  }), &gorm.Config{})
  if err != nil {
    panic("failed to connect database")
  }

  db.AutoMigrate(&Product{})
}

Steps to reproduce

1. Run script (go run main.go)
2. Check DDL (show create table products;)

CREATE TABLE `products` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `name` varchar(191) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `idx_products_deleted_at` (`deleted_at`)

3. Run script again
4. Check DDL again

CREATE TABLE `products` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` datetime(3) DEFAULT NULL,
  `updated_at` datetime(3) DEFAULT NULL,
  `deleted_at` datetime(3) DEFAULT NULL,
  `name` varchar(191) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `name_2` (`name`),
  KEY `idx_products_deleted_at` (`deleted_at`)

As you can see, each time the auto migration runs, a new name_* key statement is added.

GORM Type int Create Field is Bigint

Description

GORM Type int Create Field is Bigint。
it will be disrupts the language itself。So if type is int.SQL field type should is int。

update_at time is not consistent

GORM Playground Link

go-gorm/playground#288

Description

For example:
when we call updates method, user.update_at is "2021-02-26 20:47:16.823720459Z" .
But the UPDATE sql uses "update_at"="2021-02-26 20:47:16.824Z", and SELECT sql uses "update_at"="2021-02-26 20:47:16.823Z"

update_at is not consistent.

I think update_at should be consistent. And it should be no error for this case

How to SetConnMaxLifetime/SetMaxOpenConns/SetMaxIdleConns?

go-sql-driver/mysql provides these methods to control driver behaviours. How can we do this in gorm?

Datetime precision breaks autoUpdateTime:nano

Description

Column defined with

ModTime      int64       `gorm:"column:mod_time;not null;autoUpdateTime:nano;index:,composite:mod_time"`

is populated correctly with nano timestamps in sqlite3 but in mysql values are rounded to ms i.e. you get 1689751262515000000 instead of 1689751262515828345 in mysql column.

Workaround that works for us is to set datetimePrecision = 9 or force NowFunc i.e.

&gorm.Config{
 NowFunc: func() time.Time {
    return time.Now() // Here we use monotonic clock to avoid clock synchronization diffs see https://pkg.go.dev/time#hdr-Monotonic_Clocks
},
})

Adding ;precision:9 nor ;precision=9 field tag to ModTime column definition as above does not work.

Datetime precision logic should not be used probably for autoCreateTime nor autoUpdateTime columns because it defines time resolution on its own.

Add support for SSL

add to Config stuct

UseSSL bool
SSLCertificate string

Open function

func Open(dsn string, SSL bool, Certificate string) gorm.Dialector {
	return &Dialector{Config: &Config{DSN: dsn, UseSSL: SSL, SSLCertificate: Certificate}}
}

else block in Initialize function.

if dialector.UseSSL {
			rootCertPool := x509.NewCertPool()
			pem, err := ioutil.ReadFile(dialector.SSLCertificate)

			if err != nil {
				log.Fatal(err)
			}

			if ok := rootCertPool.AppendCertsFromPEM(pem); !ok {
				log.Fatal("Failed to append PEM.")
			}

			_ = mysql.RegisterTLSConfig("custom", &tls.Config{
				RootCAs:    rootCertPool,
			})
		}

		db.ConnPool, err = sql.Open(dialector.DriverName, dialector.DSN)
		if err != nil {
			return err
		}

support tidb AUTO_RANDOM tag

Describe the feature

support auto_random tag

Motivation

tidb use primary key with AUTO_RANDOM tag to avoid hot spot, but gorm doesn't seem to support it！

Related Issues

https://docs.pingcap.com/zh/tidb/stable/auto-random

mysql 8 do not support datetime with precision default value

GORM Playground Link

go-gorm/playground#410

Description

want add datetime col with default value 'current_timestamp', but got error
"Failed to auto migrate, but got error Error 1067: Invalid default value for 'created_at'"

// User has one `Account` (has one), many `Pets` (has many) and `Toys` (has many - polymorphic)
// He works in a Company (belongs to), he has a Manager (belongs to - single-table), and also managed a Team (has many - single-table)
// He speaks many languages (many to many) and has many friends (many to many - single-table)
// His pet also has one Toy (has one - polymorphic)
type User struct {
	gorm.Model
	Name      string
	Age       uint
	Birthday  *time.Time
	Account   Account
	Pets      []*Pet
	Toys      []Toy `gorm:"polymorphic:Owner"`
	CompanyID *int
	Company   Company
	ManagerID *uint
	Manager   *User
	Team      []User     `gorm:"foreignkey:ManagerID"`
	Languages []Language `gorm:"many2many:UserSpeak"`
	Friends   []*User    `gorm:"many2many:user_friends"`
	Active    bool
	// Timestamp, when user was added.
	CreatedAt time.Time `gorm:"default:current_timestamp"`
}

Column missing AutoIncrement method

Description

Versions:

• gorm.io/gorm v1.23.3
• gorm.io/driver/mysql v1.2.3 // indirect

get this error message

# gorm.io/driver/mysql
/Users/nemo/go/pkg/mod/gorm.io/driver/[email protected]/migrator.go:224:38: cannot use column (variable of type Column) as type gorm.ColumnType in argument to append:
        Column does not implement gorm.ColumnType (missing AutoIncrement method)

if i change version to gorm.io/gorm v1.22.5, it works well. (Another project use this version, and it's works.)

undefined: migrator.ColumnType

We are getting following error after new update:

../../../pkg/mod/gorm.io/driver/[email protected]/migrator.go:175:23: undefined: migrator.ColumnType

undefined: gorm.ColumnType On GORM 1.20.4

Dependabot committed Bump gorm.io/gorm from 1.20.2 to 1.20.4

But @asmeikal Add ColumnType interface On Commit 635dcc9 In GORM

Not Yet Release ON 1.20.4

We Will Get undefined: gorm.ColumnType Error When Building

While implementing the below code shows me an error

Your Question

Getting an error while implementing the code.

[error] failed to initialize database, got error Error 1698: Access denied for user 'root'@'localhost'

The document you expected this should be explained

Expected answer

Connection established successfully

MariaDB better support

Describe the feature

MariaDB support more feature than MySQL (old version at least).

The one I would love to have is the support of RETURNING clause on the Create operation.

I see a detection of MariaDB, so why not allow additional features there?

I'm trying to create or get a row while inserting. The goal is to handle properly concurrent insert than try to create the same data.

So What I do is something like:

	if err = s.DB.Transaction(func(tx *gorm.DB) (err error) {
		if err = tx.Clauses(clause.OnConflict{DoNothing: true}).Create(u).Error; err != nil {
			return
		}

		if u.ID == 0 {
			if err = tx.First(u, "sha1 = ?", u.Sha1).Error; err != nil {
				return
			}
		}

Motivation

I've got 3 tables to update. I need the ID of the first one to create the entry in the over tables. And 2 parallel request can run the same code. I don't want to end up with a duplicate error.

The create SQL should be:

INSERT INTO T (C1, C2) VALUES(V2, V2) ON DUPLICATE KEY UPDATE id=id RETURNING *

Or ID by default. That could be overwritten.

DSN Connector URL with special characters

Hi Gophers, Firstly I will like to thank gorm developers for building this purely fantastic ORM library.
My question is how do I delimit the special characters in the MySQL DSN URL? The password that I'm using contains @ & # symbols which is causing this issue. I have also tried URL encoding the password, but still no effect. Kindly need your help with this, thanks :)

DSN Pattern: <username>:<password>@tcp(<hostname>:<port>)/<database>

I'm following the below documentation
https://gorm.io/docs/connecting_to_the_database.html

Migrator Update Table

GORM Playground Link

go-gorm/playground#1

Description

当expr切换数据库去使用时，expr包含有数据库信息，数据去查询是 table 在这里不应该在指定，指定后报

去掉后正常

The primary key type is not int, but string or uuid

GORM Playground Link

go-gorm/playground#1

Description

// gorm_test.go
package test

import (
	"gorm.io/driver/mysql"
	"gorm.io/gorm"
	"testing"
)

type BaseModel struct {
	CreatedAt int64          `gorm:"autoCreateTime:nano"`
	UpdatedAt int64          `gorm:"autoUpdateTime:nano"`
	DeletedAt gorm.DeletedAt `gorm:"index"`
}

type UUIDModel struct {
	BaseModel
	ID string `gorm:"primarykey;type:varchar(36);not null;uniqueIndex;comment:主键ID;colum:id"`
}

type User struct {
	UUIDModel
	Username string `gorm:"type:varchar(32);not null;unique;comment:用户名"`
	Password string `gorm:"type:varchar(255);not null;comment:密码"`
	Email    string `gorm:"type:varchar(64);not null;unique;comment:邮箱"`
	Phone    string `gorm:"type:varchar(16);not null;unique;comment:手机号"`
	Avatar   string `gorm:"type:varchar(255);not null;comment:头像"`
	Nickname string `gorm:"type:varchar(32);not null;comment:昵称"`
}

const dsn = "root:<password>@tcp(<ip>:<port>)/<db_name>?charset=utf8mb4&parseTime=True&loc=Local"

var db *gorm.DB

func init() {
	conn, err := gorm.Open(mysql.Open(dsn))
	if err != nil {
		panic(err)
	}
	db = conn

	// 初始化表
	//if err = db.AutoMigrate(&User{}); err != nil {
	//	panic(err)
	//}
}

func TestGormP(t *testing.T) {
	uuidPK := "257b218c-5031-45a1-8061-209180499a93"
	var user User
	var sql string
	sql = db.ToSQL(func(tx *gorm.DB) *gorm.DB {
		return tx.Model(&user).First(&user, uuidPK)
	})
	t.Log(sql)

	sql = db.ToSQL(func(tx *gorm.DB) *gorm.DB {
		return tx.Model(&user).First(&User{}, []string{uuidPK})
	})

	t.Log(sql)

	sql = db.ToSQL(func(tx *gorm.DB) *gorm.DB {
		return tx.Model(&user).Delete(&user, uuidPK)
	})

	t.Log(sql)

	sql = db.ToSQL(func(tx *gorm.DB) *gorm.DB {
		return tx.Model(&user).Delete(&user, []string{uuidPK})
	})

	t.Log(sql)
}

控制台输出

    gorm_plus_test.go:54: SELECT * FROM `users` WHERE 257b218c-5031-45a1-8061-209180499a93 AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
    gorm_plus_test.go:60: SELECT * FROM `users` WHERE `users`.`id` = '257b218c-5031-45a1-8061-209180499a93' AND `users`.`deleted_at` IS NULL ORDER BY `users`.`id` LIMIT 1
    gorm_plus_test.go:66: UPDATE `users` SET `deleted_at`='2023-01-16 11:04:36.896' WHERE 257b218c-5031-45a1-8061-209180499a93 AND `users`.`deleted_at` IS NULL
    gorm_plus_test.go:72: UPDATE `users` SET `deleted_at`='2023-01-16 11:04:36.896' WHERE `users`.`id` = '257b218c-5031-45a1-8061-209180499a93' AND `users`.`deleted_at` IS NULL

In this example, you can see that when the First and Delete methods are executed, the sql generation error occurs if the second argument is a uuid, which is not a number string

clause.OnConflict() doesn't generate where condition during UPSERT with mariadb

Your Question

I am having the same issue as go-gorm/gorm#4355 but it looks like it also happens with mariadb.

	db.Clauses(clause.OnConflict{
		Where:     clause.Where{Exprs: []clause.Expression{clause.Gte{Column: "test.modified_by", Value: "VALUES(`test.modified_by`)"}}},
		DoUpdates: clause.AssignmentColumns([]string{"name"}),
		Columns: []clause.Column{clause.Column{Name:"id"}},
		
	}).Create(data)

generates sql without a where

 INSERT INTO `test` (`name`,`created_by`,`modified_by`,`id`) VALUES ('asdadasd','0000-00-00 00:00:00','2021-07-13 19:40:48.91',1),('adasdasd','0000-00-00 00:00:00','2021-07-13 19:40:48.91',2) ON DUPLICATE KEY UPDATE `name`=VALUES(`name`)

The document you expected this should be explained

https://gorm.io/docs/create.html#Upsert-On-Conflict

Expected sql should be

 INSERT INTO `test` (`name`,`created_by`,`modified_by`,`id`) VALUES ('asdadasd','0000-00-00 00:00:00','2021-07-13 19:40:48.91',1),('adasdasd','0000-00-00 00:00:00','2021-07-13 19:40:48.91',2) ON DUPLICATE KEY UPDATE `name`=VALUES(`name`) WHERE modified_by >= VALUES(`modified_by`)

Named Attributes in Migrator

Hi there! I've build an RDS Data API SQL Driver (Aurora Serverless) so I don't have to pay for all the VPC resources necessary to access a cluster directly. The challenge I'm facing now is that the data api's ExecuteStatement only supports named parameters, not ordinal ones; and since the Migrator uses ordinal parameter substitution, migration simply doesn't work.

I have a little time to put towards switching the Migrator in the Mysql dialector over to named parameters, is that something you'd be willing to accept?

Improper handling of bool columns in new Schema lookup for AutoMigrate

#86

Description

Type *bool is causing AutoMigrate to run ALTER TABLE objects MODIFY COLUMN enabled boolean on columns of type tinyint(1) on each run. Changing the schema from boolean to tinyint fixes this problem.

INSERT RETURNING is a MariaDB-10.5.0+ feature and will fail on earlier versions

GORM Playground Link

go-gorm/playground#529

Description

The INSERT RETURNING isn't available to 10.5.0+.

This SO user encountered the problem.

[mysql] 2022/11/21 16:08:30 packets.go:326: busy buffer