Why GOLANG MySQL Driver Return "bad connection" in UPDATE & DELETE SQL?

i'm getting Stuck as TRY my first CRUD with MySQL (5.7) using GO lang (1.18). CREATE & READ SQL not return the error, but in UPDATE & DELETE return the error. I was searching and looks like about bad or wrong configuration for connection...but i dont know how to trace it.

Driver MySQL: github.com/go-sql-driver/mysql v1.6.0

My Repo: https://github.com/ramadoiranedar/GO_REST_API

The Error is:

[mysql] 2022/09/22 00:04:44 connection.go:299: invalid connection
driver: bad connection

And i have functions SQL look likes this:

• CONNECTION

func NewDB() *sql.DB {
	db, err := sql.Open("mysql", "MY_USERNAME:MY_PASSWORD@tcp(localhost:3306)/go_restful_api")
	helper.PanicIfError(err)

	db.SetMaxIdleConns(5)
	db.SetMaxIdleConns(20)
	db.SetConnMaxLifetime(60 * time.Minute)
	db.SetConnMaxIdleTime(10 * time.Minute)

	return db
}

• UPDATE

func (repository *CategoryRepositoryImpl) Update(ctx context.Context, tx *sql.Tx, category domain.Category) domain.Category {
   SQL := "update category set name = ? where id = ?"
   _, err := tx.ExecContext(ctx, SQL, category.Name, category.Id)
   helper.PanicIfError(err)
   return category
}

• DELETE

func (repository *CategoryRepositoryImpl) Delete(ctx context.Context, tx *sql.Tx, category domain.Category) {
   SQL := "delete from category where id = ?"
   _, err := tx.ExecContext(ctx, SQL, category.Name, category.Id)
   helper.PanicIfError(err)
}

• CREATE

func (repository *CategoryRepositoryImpl) Create(ctx context.Context, tx *sql.Tx, category domain.Category) domain.Category {
SQL := "insert into category (name) values (?)"
   result, err := tx.ExecContext(ctx, SQL, category.Name)
   helper.PanicIfError(err)

   id, err := result.LastInsertId()
   if err != nil {
	   panic(err)
   }
   category.Id = int(id)
		
   return category
}

• READ

func (repository *CategoryRepositoryImpl) FindAll(ctx context.Context, tx *sql.Tx) []domain.Category {
	SQL := "select id, name from category"
	rows, err := tx.QueryContext(ctx, SQL)
	helper.PanicIfError(err)

	var categories []domain.Category
	for rows.Next() {
		category := domain.Category{}
		err := rows.Scan(
			&category.Id,
			&category.Name,
		)
		helper.PanicIfError(err)
		categories = append(categories, category)
	}
	return categories
}

func (repository *CategoryRepositoryImpl) FindById(ctx context.Context, tx *sql.Tx, categoryId int) (domain.Category, error) {
	SQL := "select id, name from category where id = ?"
	rows, err := tx.QueryContext(ctx, SQL, categoryId)
	helper.PanicIfError(err)

	category := domain.Category{}
	if rows.Next() {
		err := rows.Scan(&category.Id, &category.Name)
		helper.PanicIfError(err)
		return category, nil
	} else {
		return category, errors.New("CATEGORY IS NOT FOUND")
	}
}

Long story short: you are using database transactions where you should not.

Keep them for group of writing operations that must succeed.

I would suggest you to redefine your repository and its implementation. The whole idea of splitting layers is to be independant of a repository implementation and to be able to switch from mysql to mongo to postgres, or w/e.

//category_controller.go
type CategoryRepository interface {
	Create(ctx context.Context, category domain.Category) domain.Category
	Update(ctx context.Context, category domain.Category) domain.Category
	Delete(ctx context.Context, category domain.Category)
	FindById(ctx context.Context, categoryId int) (domain.Category, error)
	FindAll(ctx context.Context) []domain.Category
}
//category_repository_impl.go
type CategoryRepositoryImpl struct {
	db *sql.DB 
}

func NewCategoryRepository(db *sql.DB) CategoryRepository {
	return &CategoryRepositoryImpl{db: db}
}

Please see following gists with modified version of your code files:
https://gist.github.com/audrenbdb/94660f707a206d385c42f64ceb93a4aa