go-gorm / mysql Goto Github PK
View Code? Open in Web Editor NEWGORM mysql driver
License: MIT License
GORM mysql driver
License: MIT License
support auto_random
tag
tidb use primary key with AUTO_RANDOM
tag to avoid hot spot, but gorm doesn't seem to support it!
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
}
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.
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
}
}
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.
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:)
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
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`)
https://gorm.io/docs/create.html#Upsert-On-Conflict
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`)
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.
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
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"`
}
mariadb version: mariadb from 11.0.2-MariaDB, client 15.2 for Win64 (AMD64),
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%
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).
Line 298 in 9d1b8d6
// 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
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.
Just want got the return successfully.
The maximum varchar length depends on the charset of the table, but the autodetection doesn't take that into account.
https://dev.mysql.com/doc/refman/8.4/en/column-count-limit.html
Error 1074 (42000): Column length too big for column 'url' (max = 16383); use BLOB or TEXT instead
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?
The INSERT RETURNING
isn't available to 10.5.0+.
This SO user encountered the problem.
We are getting following error after new update:
../../../pkg/mod/gorm.io/driver/[email protected]/migrator.go:175:23: undefined: migrator.ColumnType
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
。
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"`
}
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.
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
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"`
...
}
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
go-sql-driver/mysql
provides these methods to control driver behaviours. How can we do this in gorm?
Getting an error while implementing the code.
[error] failed to initialize database, got error Error 1698: Access denied for user 'root'@'localhost'
Connection established successfully
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
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.
1.建表
CREATE TABLE `mytest_table` (
`column1` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
`column2` char(40) COLLATE utf8mb4_unicode_ci NOT NULL,
`column3` tinyint(3) unsigned NOT NULL DEFAULT '0',
UNIQUE KEY `unikkkk` (`column1`,`column2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Line 365 in c829f6e
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?
Versions:
gorm.io/gorm v1.23.3
gorm.io/driver/mysql v1.2.3
// indirectget 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.)
I came across the following bug. Sorry for skipping the playground, but the following code should be sufficient for reproducing the bug.
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{})
}
go run main.go
)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`)
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.
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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.