Error: "Incorrect datetime value: '0000-00-00' for column..." on mysql 5.7 and above

[posener]

Issue description

I've inserted a "zero" time.Time object to a table with a DATETIME column, and get Incorrect datetime value: '0000-00-00' for column error.

Example code

The below code fails:

package main

import (
	"database/sql"
	"log"
	"time"

	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, _ := sql.Open("mysql", "root:secret@(127.0.0.1:3306)/test")
	_, err := db.Exec("CREATE TABLE IF NOT EXISTS daty (col DATETIME)")
	if err != nil {
		log.Panicf("Failed create: %s", err)
	}
	_, err = db.Exec("INSERT INTO daty (col) VALUES (?)", time.Time{})
	if err != nil {
		log.Panicf("Failed insert: %s", err)
	}
}

Replacing time.Time{} with time.Now() will cause the code not to fail.

Configuration

*Driver version (or git SHA): 9181e3a (master)
*Go version: go version go1.9 linux/amd64
*Server version: mysql 8.0.3, 5.7.20
*Server OS: fedora 26

[methane]

It's MySQL's spec and there are nothing we can.
You should use only valid datetime with MySQL.

[methane]

It's MySQL's spec and there are nothing we can.
You should use only valid datetime with MySQL.

[posener]

Hi, thanks!
Using TIMESTAMP instead would work?

[methane]

This is not MySQL user forum...

[methane]

I know much about MySQL protocol, but I don't know much about MySQL's behavior.

[ianaz]

Related issue: #346