MySQL Bugs: #8514: CURRENT_TIMESTAMP default not respected

Bug #8514	CURRENT_TIMESTAMP default not respected
Submitted:	15 Feb 2005 8:56	Modified:	25 Aug 2005 13:31
Reporter:	Sean Vostinar	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	1.0.4.20163	OS:	Windows (Win 2k)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
When executing the DataTable.Rows.Add or DataRowView.EndEdit methods, the debugger complains of null values in fields with a default of  CURRENT_TIMESTAMP.

How to repeat:
Given a table, `myTable` with a column `UpdateDT` defined as
timestamp NOT NULL default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, or
timestamp NOT NULL default CURRENT_TIMESTAMP, 
the following code demonstrates the error while adding the record as a datarow and a datarowview:

        Dim Conn As MySqlConnection = New MySqlConnection
        Conn.ConnectionString = "..."
        Conn.Open()

        Dim ds As DataSet = New DataSet("Test")
        Dim da As MySqlDataAdapter = New MySqlDataAdapter
        With da
            .SelectCommand = New MySqlCommand("SELECT * FROM myTable", Conn)
            .FillSchema(ds, SchemaType.Source, "myTable")
            .Fill(ds.Tables("myTable"))
            ds.Tables("myTable").PrimaryKey(0).AutoIncrement = True
            .InsertCommand = New MySqlCommandBuilder(da).GetInsertCommand
            .UpdateCommand = New MySqlCommandBuilder(da).GetUpdateCommand
        End With

        Dim tbl As DataTable = ds.Tables("myTable")

' Scenario 1
        Dim dr As DataRow = tbl.NewRow()
        With dr
            .Item("Column") = "Somevalue"
            .EndEdit()
        End With
        tbl.Rows.Add(dr) ' Error occurs here

' Scenario 2
        Dim dv As DataView = New DataView(tbl)
        Dim drv As DataRowView = dv.AddNew
        drv("rcpt") = "rcpt"
        drv.EndEdit() ' Error occurs here
        da.Update(tbl)

Suggested fix:
Enforce this default upon EndEdit or Addition of a new row as detailed.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

This has been fixed, however it uncovered another issue that we currently do not have a fix for.  Currently, we do not support the situation where you define a column as supporting null values but also having a default value that is non-null.  The main problem is that there seems to be no way to detect when someone has changed a data columns value (even if the actual value of the column doesn't change)
row["mycol"] = DBNull.Value should set the column value to null and not use any default value that is assigned to the column.  However, in this case the columns value will be DBNull.Value which is the same as the default.  We are working on a scenario that might fix this but it will take some investigation.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

I have changed this to expected behavior.  My earlier code was a partial solution but later caused problems.  We will try to and provide a better solution in a later release.