FreeTDS与unixODBC并发连接错误排查与规避策略

{01000} [unixODBC][FreeTDS][SQL Server]Unable to open socket
SQLDriverConnect: {08001} [unixODBC][FreeTDS][SQL Server]Unable to connect to data source

; /etc/odbc.ini

[MYDB_DSN1]
Description = My SQL Server Database Connection 1
Driver = FreeTDS
Server = your_sql_server_ip_or_hostname
Port = 1433
Database = your_database_name
TDS_Version = 8.0 ; 根据SQL Server版本调整，例如7.0, 8.0, 7.1等

[MYDB_DSN2]
Description = My SQL Server Database Connection 2
Driver = FreeTDS
Server = your_sql_server_ip_or_hostname
Port = 1433
Database = your_database_name
TDS_Version = 8.0

[MYDB_DSN3]
Description = My SQL Server Database Connection 3
Driver = FreeTDS
Server = your_sql_server_ip_or_hostname
Port = 1433
Database = your_database_name
TDS_Version = 8.0

[MYDB_DSN4]
Description = My SQL Server Database Connection 4
Driver = FreeTDS
Server = your_sql_server_ip_or_hostname
Port = 1433
Database = your_database_name
TDS_Version = 8.0

[MYDB_DSN5]
Description = My SQL Server Database Connection 5
Driver = FreeTDS
Server = your_sql_server_ip_or_hostname
Port = 1433
Database = your_database_name
TDS_Version = 8.0

[MYDB_DSN6]
Description = My SQL Server Database Connection 6
Driver = FreeTDS
Server = your_sql_server_ip_or_hostname
Port = 1433
Database = your_database_name
TDS_Version = 8.0

package main

import (
    "database/sql"
    _ "github.com/alexbrainman/odbc" // 导入ODBC驱动
    "fmt"
    "log"
    "sync"
    "time"
)

// DSN配置数组
var dsnNames = []string{
    "MYDB_DSN1",
    "MYDB_DSN2",
    "MYDB_DSN3",
    "MYDB_DSN4",
    "MYDB_DSN5",
    "MYDB_DSN6",
}

// 简单的连接池管理结构
type DSNConnectionPool struct {
    dbs []*sql.DB
    mu  sync.Mutex
    idx int // 用于轮询选择DSN
}

// 初始化连接池
func NewDSNConnectionPool(user, password string) (*DSNConnectionPool, error) {
    pool := &DSNConnectionPool{
        dbs: make([]*sql.DB, len(dsnNames)),
        idx: 0,
    }

    for i, dsnName := range dsnNames {
        connStr := fmt.Sprintf("DSN=%s;UID=%s;PWD=%s", dsnName, user, password)
        db, err := sql.Open("odbc", connStr)
        if err != nil {
            return nil, fmt.Errorf("failed to open DSN %s: %w", dsnName, err)
        }
        // 设置连接池参数，例如最大空闲连接数和最大打开连接数
        db.SetMaxIdleConns(5) // 根据实际情况调整
        db.SetMaxOpenConns(10) // 每个DSN对应的连接数
        db.SetConnMaxLifetime(5 * time.Minute) // 连接最大生命周期

        // 尝试ping以验证连接
        if err = db.Ping(); err != nil {
            db.Close()
            return nil, fmt.Errorf("failed to ping DSN %s: %w", dsnName, err)
        }
        pool.dbs[i] = db
        log.Printf("Successfully connected to DSN: %s", dsnName)
    }
    return pool, nil
}

// 获取一个数据库连接
// 实际应用中可能需要更复杂的负载均衡逻辑
func (p *DSNConnectionPool) GetDB() *sql.DB {
    p.mu.Lock()
    defer p.mu.Unlock()

    db := p.dbs[p.idx]
    p.idx = (p.idx + 1) % len(p.dbs) // 轮询下一个DSN
    return db
}

// 关闭所有连接
func (p *DSNConnectionPool) Close() {
    for _, db := range p.dbs {
        if db != nil {
            db.Close()
        }
    }
    log.Println("All DSN connections closed.")
}

func main() {
    // 替换为你的数据库用户名和密码
    dbUser := "your_db_user"
    dbPass := "your_db_password"

    pool, err := NewDSNConnectionPool(dbUser, dbPass)
    if err != nil {
        log.Fatalf("Failed to initialize DSN connection pool: %v", err)
    }
    defer pool.Close()

    // 模拟并发请求
    var wg sync.WaitGroup
    numRequests := 100 // 假设有100个并发请求

    log.Printf("Simulating %d concurrent requests...", numRequests)
    for i := 0; i < numRequests; i++ {
        wg.Add(1)
        go func(reqID int) {
            defer wg.Done()
            db := pool.GetDB() // 从池中获取一个DB连接

            // 这里执行你的数据库操作
            // 例如：
            var result string
            query := "SELECT GETDATE()" // 示例查询
            err := db.QueryRow(query).Scan(&result)
            if err != nil {
                log.Printf("Request %d: Error executing query: %v", reqID, err)
                return
            }
            // log.Printf("Request %d: Query result: %s", reqID, result)
        }(i)
    }
    wg.Wait()
    log.Println("All simulated requests completed.")
}