Golang SQL error when trying to create a new user

php editor Xiaoxin encountered a SQL error when using Golang to create a new user. This error may cause users to fail to register successfully and cause trouble to the normal operation of the website. For this problem, we need to analyze the cause of the error and find a solution. This article will introduce several common causes that may cause Golang SQL errors, and provide corresponding solutions to help developers quickly solve this problem and ensure the normal operation of the website.

Question content

When using golang package viper and cobra to create a user, I received in new_user.go this error. The error is as follows:

cannot use result (variable of type sql.result) as error value in return statement: sql.result does not implement error (missing method error)

My code is split into two files that communicate with each other, this is the tree hierarchy:

.
├── makefile
├── readme.md
├── cli
│   ├── config.go
│   ├── db-creds.yaml
│   ├── go.mod
│   ├── go.sum
│   ├── new_user.go
│   └── root.go
├── docker-compose.yaml
├── go.work
└── main.go

To connect to the database, I created a yaml file db-creds.yaml to extract the credentials for config.go. No error pops up here:

config.go File

package cli

import (
    "database/sql"
    "fmt"

    _ "github.com/go-sql-driver/mysql"
    _ "github.com/lib/pq"
    "github.com/spf13/viper"
)

// var dialects = map[string]gorp.dialect{
//  "postgres": gorp.postgresdialect{},
//  "mysql":    gorp.mysqldialect{engine: "innodb", encoding: "utf8"},
// }

// initconfig reads in config file and env variables if set
func initconfig() {
    if cfgfile != "" {
        viper.setconfigfile(cfgfile)
    } else {
        viper.addconfigpath("./")
        viper.setconfigname("db-creds")
        viper.setconfigtype("yaml")
    }

    // if a config file is found, read it in:
    err := viper.readinconfig()
    if err == nil {
        fmt.println("fatal error config file: ", viper.configfileused())
    }
    return
}

func getconnection() *sql.db {

    // make sure we only accept dialects that were compiled in.
    // dialect := viper.getstring("database.dialect")
    // _, exists := dialects[dialect]
    // if !exists {
    //  return nil, "", fmt.errorf("unsupported dialect: %s", dialect)
    // }

    // will want to create another command that will use a mapping
    // to connect to a preset db in the yaml file.
    dsn := fmt.sprintf("%s:%s@%s(%s)?parsetime=true",
        viper.getstring("mysql-5.7-dev.user"),
        viper.getstring("mysql-5.7-dev.password"),
        viper.getstring("mysql-5.7-dev.protocol"),
        viper.getstring("mysql-5.7-dev.address"),
    )
    viper.set("database.datasource", dsn)

    db, err := sql.open("msyql", viper.getstring("database.datasource"))
    if err != nil {
        fmt.errorf("cannot connect to database: %s", err)
    }

    return db
}

The error I put at the top is the error that occurs when I return the result. I'm implementing the flag option for cobra to use -n followed by name to indicate a new user being added.

new_user.go File

package cli

import (
    "log"

    "github.com/sethvargo/go-password/password"
    "github.com/spf13/cobra"
)

var name string

// newCmd represents the new command
var newCmd = &cobra.Command{
    Use:   "new",
    Short: "Create a new a user which will accommodate the individuals user name",
    Long:  `Create a new a user that will randomize a password to the specified user`,
    RunE: func(cmd *cobra.Command, args []string) error {

        db := getConnection()

        superSecretPassword, err := password.Generate(64, 10, 10, false, false)

        result, err := db.Exec("CREATE USER" + name + "'@'%'" + "IDENTIFIED BY" + superSecretPassword)
        if err != nil {
            log.Fatal(err)
        }

        // Will output the secret password combined with the user.
        log.Printf(superSecretPassword)

        return result <---- Error is here
    },
}

func init() {
    rootCmd.AddCommand(newCmd)

    newCmd.Flags().StringVarP(&name, "name", "n", "", "The name of user to be added")
    _ = newCmd.MarkFlagRequired("name")
}

The main purpose of this project is threefold: 1.) Create a new user, 2.) Grant specific permissions to any user 3.) Delete them. That's my ultimate goal. Proceeding one step at a time, I ran into this error. Hope anyone can help me. I'm new to golang, started using it about two weeks ago.

Workaround

go gives you a very clear indication of what's going on. The rune member of cobra expects its callback to return an error (or nil if successful).

Here, you are returning a result, which is not an error, but a specific type returned by the sql query. This is what your function should look like.

RunE: func(cmd *cobra.Command, args []string) error {

    db := getConnection()

    superSecretPassword, err := password.Generate(64, 10, 10, false, false)

    _, err := db.Exec("CREATE USER" + name + "'@'%'" + "IDENTIFIED BY" + superSecretPassword)
    if err != nil {
        // Don't Fatal uselessly, let cobra handle your error the way it
        // was designed to do it.
        return err
    }

    // Will output the secret password combined with the user.
    log.Printf(superSecretPassword)

    // Here is how you should indicate your callback terminated successfully.
    // Error is an interface, so it accepts nil values.
    return nil
}

If you need the results of the db.exec command (which does not appear to be the case), you will need to perform all required processing in the cobra callback, as it is not designed to return values ​​to the main thread.

Error handling

Some bad practices I noticed in my code regarding error handling:

• If a go function returns an error, do not panic or terminate the program if something unexpected happens (as you did with log.fatal). Instead, use that error return to propagate the error to the main thread and let it decide what to do.

• On the other hand, if something goes wrong, don't return results. If it fails, your getconnection function should be able to return an error: func getconnection() (*sql.db, error). You should then handle this error in the rune function instead of just logging it and handling it normally.

The above is the detailed content of Golang SQL error when trying to create a new user. For more information, please follow other related articles on the PHP Chinese website!