Error when connecting to sqlserver database

[ilanni2460]

Use the latest version of docker version 0.5.7, use the following method to connect to the sqlserver database and report an error. ` connections:

• 'sqlserver://saread:26weercbmjYE@10.49.3.80/sh?database=spdb'

`

The error message is as follows: ` sql_exporter | 2024/10/18 17:55:29 maxprocs: Leaving GOMAXPROCS=32: CPU quota undefined sql_exporter | {"build_context":"(go=go1.21.13, platform=linux/amd64, user=, date=, tags=unknown)","caller":"main.go:58","msg":"Starting sql_exporter","ts":"2024-10-18T09:55:29.251726806Z","version_info":"(version=, branch=, revision=e69d565d5ad3b09c002040f3462399e1022de2a3)"} sql_exporter | {"caller":"exporter.go:103","interval":"5m0s","level":"info","msg":"Started periodically execution of job","name":"example","ts":"2024-10-18T09:55:29.252221881Z"} sql_exporter | {"caller":"main.go:81","level":"info","listenAddress":":9237","msg":"Listening","ts":"2024-10-18T09:55:29.252336169Z"} sql_exporter | {"caller":"job.go:408","job":"example","level":"debug","msg":"Starting","ts":"2024-10-18T09:55:29.252356894Z"} sql_exporter | {"Query:":"SET lock_timeout = 1000","caller":"job.go:551","job":"example","level":"debug","msg":"StartupSQL","ts":"2024-10-18T09:55:30.529707916Z"} sql_exporter | panic: mssql: Incorrect syntax near '='. sql_exporter | sql_exporter | goroutine 24 [running]: sql_exporter | github.com/jmoiron/sqlx.MustExec(...) sql_exporter | /src/vendor/github.com/jmoiron/sqlx/sqlx.go:725 sql_exporter | github.com/jmoiron/sqlx.(DB).MustExec(0xc000918730?, {0xc000964048?, 0x4?}, {0x0?, 0xc0000eedb0?, 0xc0000eede0?}) sql_exporter | /src/vendor/github.com/jmoiron/sqlx/sqlx.go:369 +0x45 sql_exporter | main.(connection).connect(0xc00097ae00, 0xc0009740b0) sql_exporter | /src/job.go:552 +0x8e5 sql_exporter | main.(Job).runOnceConnection(0xc0009740b0, 0xc00097ae00, 0xc0009740b0?) sql_exporter | /src/job.go:423 +0xa5 sql_exporter | created by main.(Job).runOnce in goroutine 41 sql_exporter | /src/job.go:474 +0x5d sql_exporter exited with code 0

`

[dewey]

1) I hope that's not your real username / password 2) Can you replicate it in just a simple Go file with sqlx and your connection string? I think it sounds like a faulty connection string and not related to the exporter.

[ilanni2460]

1. I hope that's not your real username / password

   2. Can you replicate it in just a simple Go file with sqlx and your connection string? I think it sounds like a faulty connection string and not related to the exporter.

Thank you very much for your reply. This password is not real. How to use sqlx?

[ilanni2460]

`

package main

import ( "log" _ "github.com/denisenkom/go-mssqldb" // SQL Server driver for Go "github.com/jmoiron/sqlx" )

func main() { // Replace the connection string with your actual database connection details. // Example: "server=localhost;instance=MSSQLSERVER;user id=myuser;password=mypassword;database=mydb;" dbInfo := "server=10.49.3.80;instance=sh;user id=saread;password=26weercbmjYE;database=spdb;"

// Connect to the database.
db, err := sqlx.Connect("sqlserver", dbInfo)
if err != nil {
    log.Fatalf("Error connecting to the database: %v", err)
}
defer db.Close()

// Define the SQL query.
query := `SELECT t_cjob AS job_name FROM ttt5000`

// Execute the query and handle results.
var results []struct {
    JobName string `db:"job_name"`
}
if err := db.Select(&results, query); err != nil {
    log.Fatalf("Error executing the query: %v", err)
}

// Print results.
for _, r := range results {
    log.Printf("Job Name: %s", r.JobName)
}

}

`

I wrote a simple go program and then executed it and reported an error

this is error： 2024/10/18 19:23:41 Error connecting to the database: mssql: login error: Login failed for user 'saread'.

But using the same authentication information, you can log in normally using the navicat client.

[ilanni2460]

` package main

import ( "database/sql" "fmt" "log" "net/http" "time"

"github.com/prometheus/client_golang/prometheus"
"github.com/prometheus/client_golang/prometheus/promauto"
"github.com/prometheus/client_golang/prometheus/promhttp"

_ "github.com/denisenkom/go-mssqldb"

)

func main() { // 初始化 Prometheus Gauge Vec jobStateGauge := promauto.NewGaugeVec(prometheus.GaugeOpts{ Name: "job_exec_state", Help: "Job execution states from the database.", }, []string{"job_name", "cloud_region", "company", "project", "OS", "table_name", "user"}) // 添加 table_name 和 user 标签

// 设置 HTTP 服务器以导出指标
http.Handle("/metrics", promhttp.Handler())

// 启动 HTTP 服务器
go func() {
    if err := http.ListenAndServe(":2112", nil); err != nil {
        log.Fatalf("Could not start metrics server: %v", err)
    }
}()

// 连接字符串，增加端口号1433
connString := "server=10.49.3.80;port=1433;instance=sh;user id=saread;password=26weercbmjYE;database=spdb"

// 连接数据库
db, err := sql.Open("mssql", connString)
if err != nil {
    log.Fatalf("Error creating connection pool: %v", err)
}
defer db.Close()

// 测试连接
if err := db.Ping(); err != nil {
    log.Fatalf("Error pinging database: %v", err)
}
fmt.Println("Successfully connected!")

// 查询语句
query := `
SELECT t_cjob AS job_name, t_jsta AS job_exec_state, t_user AS [user], 'ttt5000' AS table_name
FROM ttt5000;`

// 定义定时任务
ticker := time.NewTicker(15 * time.Second)

for {
    select {
    case <-ticker.C:
        log.Println("Starting new query...")
        // 执行查询
        rows, err := db.Query(query)
        if err != nil {
            log.Fatalf("Error executing query: %v", err)
        }
        defer rows.Close()

        // 清空指标
        jobStateGauge.Reset()

        // 遍历查询结果
        var jobName string
        var jobExecState int // 假设 job_exec_state 是整数类型的字段
        var tableName string
        var user string
        for rows.Next() {
            if err := rows.Scan(&jobName, &jobExecState, &user, &tableName); err != nil { // 调整 Scan 的顺序
                log.Fatalf("Error scanning row: %v", err)
            }
            jobStateGauge.WithLabelValues(jobName, "cloud_online", "MXG", "MXG-INFRA", "windows", tableName, user).Set(float64(jobExecState))
            log.Printf("Set job_name=%s, table_name=%s, user=%s, and job_exec_state=%d\n", jobName, tableName, user, jobExecState)
            fmt.Printf("Job Name: %s, Table Name: %s, User: %s, Job Execution State: %d\n", jobName, tableName, user, jobExecState)
        }

        // 检查遍历过程中是否有错误发生
        if err := rows.Err(); err != nil {
            log.Fatalf("%v", err)
        }

        log.Println("Query completed.")
    }
}

}

`

@dewey

I used this code and it was ok. I could connect to the sqlserver database normally and output metrics normally.

[ilanni2460]

@dewey Hi, can you help me take a look at this issue? I can connect to sqlserver using the code I wrote, such as the code above, but not using justwatchcom/sql_exporter.

[dewey]

Hey, unfortunately I don't have time to debug this. But happy to review a fix in case there's an issue.

[dewey]

Reading your error message it sounds more like an issue with your query than the connection though:

{"caller":"job.go:408","job":"example","level":"debug","msg":"Starting","ts":"2024-10-18T09:55:29.252356894Z"}
sql_exporter | {"Query:":"SET lock_timeout = 1000","caller":"job.go:551","job":"example","level":"debug","msg":"StartupSQL","ts":"2024-10-18T09:55:30.529707916Z"}
sql_exporter | panic: mssql: Incorrect syntax near '='.
sql_exporter |

If you use the SET lock_timeout = 1000 (Does it need a ; at the end?) query in your code snippet does it still work?

[ilanni2460]

Reading your error message it sounds more like an issue with your query than the connection though:

{"caller":"job.go:408","job":"example","level":"debug","msg":"Starting","ts":"2024-10-18T09:55:29.252356894Z"}
sql_exporter | {"Query:":"SET lock_timeout = 1000","caller":"job.go:551","job":"example","level":"debug","msg":"StartupSQL","ts":"2024-10-18T09:55:30.529707916Z"}
sql_exporter | panic: mssql: Incorrect syntax near '='.
sql_exporter |

If you use the SET lock_timeout = 1000 (Does it need a ; at the end?) query in your code snippet does it still work?

Thank you very much for your reply. I will try again as you said.

[ilanni2460]

@dewey I'm very sorry. I tested this and it still doesn't work.

[dewey]

Unfortunately I'll not be able to help you, but if you find an issue and have details on how to fix it I'm gladly accepting contributions.

[ilanni2460]

ok，tks