mysql data cannot be queried using go gorm

I use gorm to count qps of mysql, but the query data is always empty. The program did not report any errors, and I could have used similar code to get my own test table data.

My code

type mysqlQueires struct {
    Queries sql.NullString `gorm:"column:queries"`
    Uptime  sql.NullString `gorm:"column:uptime"`
}

type attendees struct {
    Id            sql.NullString `gorm:"column:id"`
    Created_at    sql.NullString `gorm:"column:created_at"`
    Updated_at    sql.NullString `gorm:"column:updated_at"`
    Deleted_at    sql.NullString `gorm:"column:deleted_at"`
    Attendee_user sql.NullString `gorm:"column:attendee_user"`
    Meeting_id    sql.NullString `gorm:"column:meeting_id"`
}


func (n *Node) GetDB() *gorm.DB {
    return n.db.Session(&gorm.Session{})
}

func (n *Node) GetQps() (int64, error) {
    sys := []mysqlQueires{}

    rawQueries := "show global status where variable_name in ('queries','uptime')"
    result := n.GetDB().Raw(rawQueries).Scan(&sys)
    if result.Error != nil {
        xlog.Error("query variable_name error", "get qps", false, "err=", result.Error)
        return -1, result.Error
    }
    if result.RowsAffected == 0 {
        xlog.Error("query variable_name result 0", "get qps", false)
        return -1, fmt.Errorf("query variable_name result 0")
    }

    //
    fmt.Println(sys)
    //result:
    //[{{ false} { false}} {{ false} { false}}]

    var variable sql.NullString
    n.GetDB().Raw("SHOW GLOBAL STATUS LIKE 'Questions'").Scan(&variable)
    fmt.Println(variable)
    //result:
    //sql: expected 2 destination arguments in Scan, not 1
    //{ false}


    var ttt []attendees
    n.GetDB().Raw("select * from attendees").Scan(&ttt)
    fmt.Println(ttt)
    //result:
    //[{{1 true} { false} { false} { false} { false} {1111 true}}]

    return 0, nil
}

I used dsn is "root:rootPwd@tcp(localhost:3306)/test_example". The same command can be used to retrieve data using cmd and DBeaver. May I ask if there is a problem with my mysql configuration or the go code needs to be corrected?

Solution

It seems your mysqlQueries struct properties are not suitable for the output of the show global status command.

Mysql documentation shows result as follows;

+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| Aborted_clients          | 0          |
| Aborted_connects         | 0          |
| Bytes_received           | 155372598  |
| Bytes_sent               | 1176560426 |
| Connections              | 30023      |
| Created_tmp_disk_tables  | 0          |
| Created_tmp_tables       | 8340       |
| Created_tmp_files        | 60         |
...
| Open_tables              | 1          |
| Open_files               | 2          |
| Open_streams             | 0          |
| Opened_tables            | 44600      |
| Questions                | 2026873    |
...
| Table_locks_immediate    | 1920382    |
| Table_locks_waited       | 0          |
| Threads_cached           | 0          |
| Threads_created          | 30022      |
| Threads_connected        | 1          |
| Threads_running          | 1          |
| Uptime                   | 80380      |
+--------------------------+------------+

According to that info, you should change the mysqlQueries struct as follows;

type mysqlQueries struct {
    VariableName string `gorm:"column:variable_name"`
    Value  int `gorm:"column:value"`
}

And while sending that raw show global status query,

    var records []mysqlQueries
    n.GetDB().Raw("SHOW GLOBAL STATUS").Scan(&records)
    fmt.Println(records)

It should map the actual values inside that slice.