I have been trying to start a server that uses vertica to store data. so I passed the database pointer to http handler func. It works initially for a minute or two but doesnt even respond after sometime.
//main func runs perfectly fine
func main() {
//ODBC connection to vertica via DSN
db, err := sql.Open("odbc", "DSN=HPVerticaDSN")
if err != nil {
panic(err)
}
defer db.Close()
if err := db.Ping(); err != nil {
fmt.Printf("ping err: %v", err)
}
//start gin
r := gin.Default()
r.GET("/ping", func(c *gin.Context) {
handler(c, db)
})
r.Run() // listen and serve on 0.0.0.0:8080
}
I am running this server in linux(ubuntu)
//handler func is a http handler for '/ping' get request
func handler(c *gin.Context, db *sql.DB) {
//start transaction
tx, err := db.Begin()
//it is here where the process gets stuck after a while
if err != nil {
log.Printf("tx begin error: %v", err)
return
}
defer func() {
if err != nil {
log.Printf("error sql: %v", err)
tx.Rollback()
return
}
err = tx.Commit()
if err != nil {
log.Printf("commit err: %v", err)
}
}()
fmt.Println("start prepare statment")
stmt, err := tx.Prepare("insert /*+direct*/ into events (id, game_id, created_at) values(?,?,?)")
if err != nil {
log.Printf("Insert prepare statement: %v", err)
c.JSON(500, gin.H{
"message": err.Error(),
})
return
}
fmt.Println("exec prepare statment start...")
res, err := stmt.Exec(uuid.NewV4().String(), uuid.NewV4().String(), time.Now().Format(time.RFC3339))
if err != nil {
fmt.Printf("Insert record: %v", err)
return
}
defer stmt.Close()
fmt.Println("exec prepare statment end")
num, err := res.RowsAffected()
if err != nil {
fmt.Printf("Rows affected err: %v", err)
return
}
if num != 1 {
fmt.Printf("Rows affected: %v, must be 1", num)
err = errors.New("Rows affected must be 1")
return
}
c.JSON(200, gin.H{
"message": "pong",
})
}
I have above GO code http server running. It runs perfectly for first minute or two and after a while if I do a /ping GET request there is no response form the vertica db. It gets stuck at tx, err := db.Begin().
Finally it gives this error:
SQLSetConnectUIntPtrAttr: {HY000} [Vertica][DSI] An error occurred while attempting to retrieve the error message for key 'VPropertySetFailed' and component ID 101: Could not open error message files - Check that "/opt/vertica/opt/vertica/lib64/en-US/VerticaMessages.xml" or "/opt/vertica/opt/vertica/lib64/VerticaMessages_en-US.xml" exists and are accessible. MessageParameters=["could not receive data from server: Connection timed out "]
But if I create or open a connection inside the handler func, so that it creates a new connection on each request then it works fine.
ODBC trace when insert query is successful:
[ODBC][61608][1502281990.419123][SQLSetConnectAttr.c][842]
Exit:[SQL_SUCCESS]
[ODBC][61608][1502282121.643292][SQLSetConnectAttr.c][396]
Entry:
Connection = 0x7f71840008c0
Attribute = SQL_ATTR_AUTOCOMMIT
Value = (nil)
StrLen = -5
[ODBC][61608][1502282121.644409][SQLSetConnectAttr.c][842]
Exit:[SQL_SUCCESS]
[ODBC][61608][1502282121.644794][SQLAllocHandle.c][540]
Entry:
Handle Type = 3
Input Handle = 0x7f71840008c0
[ODBC][61608][1502282121.644996][SQLAllocHandle.c][1085]
Exit:[SQL_SUCCESS]
Output Handle = 0x5564cb6408e0
[ODBC][61608][1502282121.645018][SQLPrepareW.c][165]
Entry:
Statement = 0x5564cb6408e0
SQL = [insert /*+direct*/ into events (.....][length = 147 (SQL_NTS)]
[ODBC][61608][1502282121.649229][SQLPrepareW.c][346]
Exit:[SQL_SUCCESS]
[ODBC][61608][1502282121.649256][SQLNumParams.c][144]
Entry:
Statement = 0x5564cb6408e0
Param Count = 0xc4201d0f0e
[ODBC][61608][1502282121.649266][SQLNumParams.c][231]
Exit:[SQL_SUCCESS]
Count = 0xc4201d0f0e -> 8
[ODBC][61608][1502282121.649274][SQLDescribeParam.c][185]
Entry:
Statement = 0x5564cb6408e0
Parameter Number = 1
SQL Type = 0xc4200ba600
Param Def = 0xc4200ba608
Scale = 0xc4200ba602
Nullable = 0xc4201d0f40
[ODBC][61608][1502282121.649284][SQLDescribeParam.c][341]
Exit:[SQL_SUCCESS]
SQL Type = 0x7ffd05d6ad80
Param Def = 0x7ffd05d6ae70
Scale = 0x7ffd05d6af60
Nullable = 0x7ffd05d6b050
[ODBC][61608][1502282121.649290][SQLDescribeParam.c][185]
Entry:
Statement = 0x5564cb6408e0
Parameter Number = 2
SQL Type = 0xc4200ba630
Param Def = 0xc4200ba638
Scale = 0xc4200ba632
Nullable = 0xc4201d0f40
[ODBC][61608][1502282121.649296][SQLDescribeParam.c][341]
Exit:[SQL_SUCCESS]
SQL Type = 0x7ffd05d6ad80
Param Def = 0x7ffd05d6ae70
Scale = 0x7ffd05d6af60
Nullable = 0x7ffd05d6b050
[ODBC][61608][1502282121.649302][SQLDescribeParam.c][185]
Entry:
Statement = 0x5564cb6408e0
Parameter Number = 3
SQL Type = 0xc4200ba660
Param Def = 0xc4200ba668
Scale = 0xc4200ba662
Nullable = 0xc4201d0f40
[ODBC][61608][1502282121.649308][SQLDescribeParam.c][341]
Exit:[SQL_SUCCESS]
SQL Type = 0x7ffd05d6ad80
Param Def = 0x7ffd05d6ae70
Scale = 0x7ffd05d6af60
Nullable = 0x7ffd05d6b050
[ODBC][61608][1502282121.649313][SQLDescribeParam.c][185]
Entry:
Statement = 0x5564cb6408e0
Parameter Number = 4
SQL Type = 0xc4200ba690
Param Def = 0xc4200ba698
Scale = 0xc4200ba692
Nullable = 0xc4201d0f40
[ODBC][61608][1502282121.649319][SQLDescribeParam.c][341]
Exit:[SQL_SUCCESS]
SQL Type = 0x7ffd05d6ad80
Param Def = 0x7ffd05d6ae70
Scale = 0x7ffd05d6af60
Nullable = 0x7ffd05d6b050
[ODBC][61608][1502282121.649324][SQLDescribeParam.c][185]
Entry:
Statement = 0x5564cb6408e0
Parameter Number = 5
SQL Type = 0xc4200ba6c0
Param Def = 0xc4200ba6c8
Scale = 0xc4200ba6c2
Nullable = 0xc4201d0f40
[ODBC][61608][1502282121.649330][SQLDescribeParam.c][341]
Exit:[SQL_SUCCESS]
SQL Type = 0x7ffd05d6ad80
Param Def = 0x7ffd05d6ae70
Scale = 0x7ffd05d6af60
Nullable = 0x7ffd05d6b050
[ODBC][61608][1502282121.649335][SQLDescribeParam.c][185]
Entry:
Statement = 0x5564cb6408e0
Parameter Number = 6
SQL Type = 0xc4200ba6f0
Param Def = 0xc4200ba6f8
Scale = 0xc4200ba6f2
Nullable = 0xc4201d0f40
[ODBC][61608][1502282121.649341][SQLDescribeParam.c][341]
Exit:[SQL_SUCCESS]
SQL Type = 0x7ffd05d6ad80
Param Def = 0x7ffd05d6ae70
Scale = 0x7ffd05d6af60
Nullable = 0x7ffd05d6b050
[ODBC][61608][1502282121.649347][SQLDescribeParam.c][185]
Entry:
Statement = 0x5564cb6408e0
Parameter Number = 7
SQL Type = 0xc4200ba720
Param Def = 0xc4200ba728
Scale = 0xc4200ba722
Nullable = 0xc4201d0f40
[ODBC][61608][1502282121.649360][SQLDescribeParam.c][341]
Exit:[SQL_SUCCESS]
SQL Type = 0x7ffd05d6ad80
Param Def = 0x7ffd05d6ae70
Scale = 0x7ffd05d6af60
Nullable = 0x7ffd05d6b050
[ODBC][61608][1502282121.649365][SQLDescribeParam.c][185]
Entry:
Statement = 0x5564cb6408e0
Parameter Number = 8
SQL Type = 0xc4200ba750
Param Def = 0xc4200ba758
Scale = 0xc4200ba752
Nullable = 0xc4201d0f40
[ODBC][61608][1502282121.649371][SQLDescribeParam.c][341]
Exit:[SQL_SUCCESS]
SQL Type = 0x7ffd05d6ad80
Param Def = 0x7ffd05d6ae70
Scale = 0x7ffd05d6af60
Nullable = 0x7ffd05d6b050
[ODBC][61608][1502282121.650523][SQLBindParameter.c][217]
Entry:
Statement = 0x5564cb6408e0
Param Number = 1
Param Type = 1
C Type = -8 SQL_C_WCHAR
SQL Type = -8 SQL_WCHAR
Col Def = 36
Scale = 0
Rgb Value = 0xc42004df40
Value Max = 72
StrLen Or Ind = 0xc4200ba628
[ODBC][61608][1502282121.650546][SQLBindParameter.c][422]
Exit:[SQL_SUCCESS]
[ODBC][61608][1502282121.650557][SQLBindParameter.c][217]
Entry:
Statement = 0x5564cb6408e0
Param Number = 2
Param Type = 1
C Type = -8 SQL_C_WCHAR
SQL Type = -9 SQL_WVARCHAR
Col Def = 24
Scale = 0
Rgb Value = 0xc4201df180
Value Max = 48
StrLen Or Ind = 0xc4200ba658
[ODBC][61608][1502282121.650569][SQLBindParameter.c][422]
Exit:[SQL_SUCCESS]
[ODBC][61608][1502282121.650585][SQLBindParameter.c][217]
Entry:
Statement = 0x5564cb6408e0
Param Number = 3
Param Type = 1
C Type = -8 SQL_C_WCHAR
SQL Type = -8 SQL_WCHAR
Col Def = 36
Scale = 0
Rgb Value = 0xc42004df90
Value Max = 72
StrLen Or Ind = 0xc4200ba688
[ODBC][61608][1502282121.650593][SQLBindParameter.c][422]
Exit:[SQL_SUCCESS]
[ODBC][61608][1502282121.650600][SQLBindParameter.c][217]
Entry:
Statement = 0x5564cb6408e0
Param Number = 4
Param Type = 1
C Type = -8 SQL_C_WCHAR
SQL Type = -9 SQL_WVARCHAR
Col Def = 10
Scale = 0
Rgb Value = 0xc4201d3200
Value Max = 20
StrLen Or Ind = 0xc4200ba6b8
[ODBC][61608][1502282121.650607][SQLBindParameter.c][422]
Exit:[SQL_SUCCESS]
[ODBC][61608][1502282121.650614][SQLBindParameter.c][217]
Entry:
Statement = 0x5564cb6408e0
Param Number = 5
Param Type = 1
C Type = 4 SQL_C_LONG
SQL Type = 4 SQL_INTEGER
Col Def = 4
Scale = 0
Rgb Value = 0xc4201d1000
Value Max = 0
StrLen Or Ind = (nil)
[ODBC][61608][1502282121.650627][SQLBindParameter.c][422]
Exit:[SQL_SUCCESS]
[ODBC][61608][1502282121.650636][SQLBindParameter.c][217]
Entry:
Statement = 0x5564cb6408e0
Param Number = 6
Param Type = 1
C Type = -8 SQL_C_WCHAR
SQL Type = -9 SQL_WVARCHAR
Col Def = 11
Scale = 0
Rgb Value = 0xc4201d3240
Value Max = 22
StrLen Or Ind = 0xc4200ba718
[ODBC][61608][1502282121.650642][SQLBindParameter.c][422]
Exit:[SQL_SUCCESS]
[ODBC][61608][1502282121.650650][SQLBindParameter.c][217]
Entry:
Statement = 0x5564cb6408e0
Param Number = 7
Param Type = 1
C Type = -8 SQL_C_WCHAR
SQL Type = 93 SQL_TYPE_TIMESTAMP
Col Def = 20
Scale = 0
Rgb Value = 0xc4201f0180
Value Max = 40
StrLen Or Ind = 0xc4200ba748
[ODBC][61608][1502282121.650656][SQLBindParameter.c][422]
Exit:[SQL_SUCCESS]
[ODBC][61608][1502282121.650663][SQLBindParameter.c][217]
Entry:
Statement = 0x5564cb6408e0
Param Number = 8
Param Type = 1
C Type = -8 SQL_C_WCHAR
SQL Type = 93 SQL_TYPE_TIMESTAMP
Col Def = 20
Scale = 0
Rgb Value = 0xc4201f01b0
Value Max = 40
StrLen Or Ind = 0xc4200ba778
[ODBC][61608][1502282121.650670][SQLBindParameter.c][422]
Exit:[SQL_SUCCESS]
[ODBC][61608][1502282121.650676][SQLExecute.c][187]
Entry:
Statement = 0x5564cb6408e0
[ODBC][61608][1502282121.678544][SQLExecute.c][357]
Exit:[SQL_SUCCESS]
[ODBC][61608][1502282121.678624][SQLRowCount.c][173]
Entry:
Statement = 0x5564cb6408e0
Row Count = 0xc4201d1008
[ODBC][61608][1502282121.678643][SQLRowCount.c][247]
Exit:[SQL_SUCCESS]
Row Count = 0xc4201d1008 -> 1
[ODBC][61608][1502282121.678845][SQLFreeHandle.c][381]
Entry:
Handle Type = 3
Input Handle = 0x5564cb6408e0
[ODBC][61608][1502282121.695732][SQLFreeHandle.c][494]
Exit:[SQL_SUCCESS]
[ODBC][61608][1502282121.695847][SQLEndTran.c][417]
Entry:
Connection = 0x7f71840008c0
Completion Type = 0
[ODBC][61608][1502282121.702404][SQLEndTran.c][566]
Exit:[SQL_SUCCESS]
[ODBC][61608][1502282121.702506][SQLSetConnectAttr.c][396]
Entry:
Connection = 0x7f71840008c0
Attribute = SQL_ATTR_AUTOCOMMIT
Value = 0x1
StrLen = -5
[ODBC][61608][1502282121.702962][SQLSetConnectAttr.c][842]
Exit:[SQL_SUCCESS]
ODBC trace when insert query doesnt respond:
[ODBC][61608][1502282408.929695][SQLSetConnectAttr.c][396]
Entry:
Connection = 0x7f71840008c0
Attribute = SQL_ATTR_AUTOCOMMIT
Value = (nil)
StrLen = -5
