A few weeks ago, I have faced up a problem with MySQL driver in golang.
(read the Indonesian version here)
The problem is this driver isn’t supported for MySQL Time data type. As long as I use this package, I thought it support all the MySQL data type until this problem appear.
The error says : sql: Scan error on column index 1, name “time_start”: unsupported Scan, storing driver.Value type []uint8 into type *time.Time. After several time of googling, search in stackoverflow and their issue page, I found this comment. They didn’t support for Time data type, cause it little bit different in Go’s time.Time. Let see what’s the code behind this error.
Reproducing error
I have made simple REST API to reproduce this error in this repo. Read the readme how to run it. You can run the code inside “/before” directory and hit the endpoint “/shift”. You will see those error serve in your browser. Take a look at before/main.go in getShift function here.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
func getShift(ctx context.Context) (resShift, error) {
var resData resShift
resData.ShiftData = make([]shiftdata, 0)
query := "select name, time_start, time_end from shift order by id desc"
rows, err := dbConn.QueryContext(ctx, query)
if err != nil {
return resData, err
}
defer func() {
rows.Close()
}()
for rows.Next() {
var t shiftdata
err = rows.Scan(&t.Name, &t.TimeStart, &t.TimeEnd)
if err != nil {
return resData, err
}
resData.ShiftData = append(resData.ShiftData, t)
}
return resData, err
}
|
The query show the list of employee’s shift. At line 118, rows.Scan(&t.Name, &t.TimeStart, &t.TimeEnd), this driver doesn’t allow us to scan MySQL TIME with time.Time. The time_start and time_end column have TIME data type. Look at prepare_shift.sql file.
1
2
3
4
5
6
7
|
CREATE TABLE IF NOT EXISTS `shift` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
`time_start` time NOT NULL,
`time_end` time NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
|
Solution
Then, how I handle it ? Go to inside “/after” directory. Try to run it. Hit the /shift endpoint. What do you see ? It works, right ? It will show like this
1
2
3
4
5
6
7
8
9
10
11
12
13
|
{
"status": 200,
"message": "success",
"data": {
"shift_data": [
{
"name": "Shift 1",
"time_start": "12 Apr 2020 08:00:00",
"time_end": "12 Apr 2020 17:00:00"
}
]
}
}
|
What I do is, scan the TIME column with byte convert to string and convert it to time.Time. Take a look at /after/main.go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
func getShift(ctx context.Context) (resShift, error) {
var resData resShift
resData.ShiftData = make([]shiftdata, 0)
query := "select name, time_start, time_end from shift order by id desc"
rows, err := dbConn.QueryContext(ctx, query)
if err != nil {
return resData, err
}
defer func() {
rows.Close()
}()
for rows.Next() {
var t shiftdata
var timeRaw1 []byte
var timeRaw2 []byte
err = rows.Scan(&t.Name, &timeRaw1, &timeRaw2)
if err != nil {
return resData, err
}
loc, _ := time.LoadLocation(viper.GetString("timezone"))
timeNow := time.Now().In(loc)
t.TimeStart, _ = time.Parse("15:04:05", string(timeRaw1))
t.TimeEnd, _ = time.Parse("15:04:05", string(timeRaw2))
year, month, day := timeNow.Date()
h1, m1, s1 := t.TimeStart.Clock()
h2, m2, s2 := t.TimeEnd.Clock()
t.TimeStartStr = time.Date(year, month, day, h1, m1, s1, 0, loc).Format("02 Jan 2006 15:04:05")
t.TimeEndStr = time.Date(year, month, day, h2, m2, s2, 0, loc).Format("02 Jan 2006 15:04:05")
resData.ShiftData = append(resData.ShiftData, t)
}
return resData, err
}
|
So, that’s my first post. If you still confused or got question from this article, just put your question or feedback in the comment below. I will answer it as fast as I can. Thank you for your attention. See ya.