Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

MySQL issue with ZERO datetime

Options
  • 06-05-2014 5:04pm
    #1
    MagicMarker
    Banned (with Prison Access) Posts: 32,865 ✭✭✭✭


    Hi Guys,

    Have an issue that I as yet haven't been able to solve. I have an SSIS package that has run daily for the past couple of years without any issues. It imports transactions into a MySQL table from SQL Server 2005 as well as updates existing transactions.

    This is done by combining the source data with the destination data and separating the new from existing transactions, inserting the new while updating the existing, where applicable.

    Trouble is, now when transactions are inserted the transaction_date seems to be defaulting to a zero value, '0000-00-00 00:00:00', which is essentially NULL, as I understand it?

    Can anyone shed any light on this? The SSIS package has not been modified by anyone in any way so I'm stumped. I've included the script to insert new transactions below.

    [PHP] Public Class ScriptMain
    Inherits UserComponent
    Dim connMgr As ConnectionManagerOdbc
    Dim odbcConn As OdbcConnection
    Dim odbcCmd As OdbcCommand
    Dim odbcParam As OdbcParameter

    Public Overrides Sub AcquireConnections(ByVal Transaction As Object)

    Dim connectionString As String
    connectionString = Me.Connections.Conn1.ConnectionString
    odbcConn = New OdbcConnection(connectionString)
    odbcConn.Open()

    End Sub

    Public Overrides Sub PreExecute()
    odbcCmd = New OdbcCommand("INSERT INTO cash.transactions " & _
    "(transaction_id,customer_id,prospect_ref,transaction_date,posted_date,transaction_reference,second_reference,transaction_status,allocated_to,transaction_type,transaction_value,user_name,urn,company_id)" & _
    "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)", odbcConn)
    odbcParam = New OdbcParameter("@transaction_id", OdbcType.BigInt)
    odbcCmd.Parameters.Add(odbcParam)
    odbcParam = New OdbcParameter("@customer_id", OdbcType.Int)
    odbcCmd.Parameters.Add(odbcParam)
    odbcParam = New OdbcParameter("@prospect_ref", OdbcType.Int)
    odbcCmd.Parameters.Add(odbcParam)
    odbcParam = New OdbcParameter("@transaction_date", OdbcType.DateTime)
    odbcCmd.Parameters.Add(odbcParam)
    odbcParam = New OdbcParameter("@posted_date", OdbcType.DateTime)
    odbcCmd.Parameters.Add(odbcParam)
    odbcParam = New OdbcParameter("@transaction_reference", OdbcType.NVarChar, 20)
    odbcCmd.Parameters.Add(odbcParam)
    odbcParam = New OdbcParameter("@second_reference", OdbcType.NVarChar, 20)
    odbcCmd.Parameters.Add(odbcParam)
    odbcParam = New OdbcParameter("@transaction_status", OdbcType.NVarChar, 10)
    odbcCmd.Parameters.Add(odbcParam)
    odbcParam = New OdbcParameter("@allocated_to", OdbcType.NText)
    odbcCmd.Parameters.Add(odbcParam)
    odbcParam = New OdbcParameter("@transaction_type", OdbcType.NVarChar, 30)
    odbcCmd.Parameters.Add(odbcParam)
    odbcParam = New OdbcParameter("@transaction_value", OdbcType.Decimal)
    odbcCmd.Parameters.Add(odbcParam)
    odbcParam = New OdbcParameter("@user_name", OdbcType.NVarChar, 20)
    odbcCmd.Parameters.Add(odbcParam)
    odbcParam = New OdbcParameter("@urn", OdbcType.BigInt)
    odbcCmd.Parameters.Add(odbcParam)
    odbcParam = New OdbcParameter("@company_id", OdbcType.Int)
    odbcCmd.Parameters.Add(odbcParam)

    End Sub

    Public Overrides Sub DataIn_ProcessInputRow(ByVal Row As DataInBuffer)
    With odbcCmd
    If (Row.transactionid_IsNull) Then
    .Parameters("@transaction_id").Value = DBNull.Value
    Else
    .Parameters("@transaction_id").Value = Row.transactionid
    End If
    If (Row.customerid_IsNull) Then
    .Parameters("@customer_id").Value = DBNull.Value
    Else
    .Parameters("@customer_id").Value = Row.customerid
    End If
    If (Row.prospectref_IsNull) Then
    .Parameters("@prospect_ref").Value = DBNull.Value
    Else
    .Parameters("@prospect_ref").Value = Row.prospectref
    End If
    If (Row.transactiondate_IsNull) Then
    .Parameters("@transaction_date").Value = DBNull.Value
    Else
    .Parameters("@transaction_date").Value = Row.transactiondate
    End If
    If (Row.transactiondate_IsNull) Then
    .Parameters("@posted_date").Value = DBNull.Value
    Else
    .Parameters("@posted_date").Value = Row.posteddate
    End If
    If (Row.transactionreference_IsNull) Then
    .Parameters("@transaction_reference").Value = DBNull.Value
    Else
    .Parameters("@transaction_reference").Value = Row.transactionreference
    End If
    If (Row.secondreference_IsNull) Then
    .Parameters("@second_reference").Value = DBNull.Value
    Else
    .Parameters("@second_reference").Value = Row.secondreference
    End If
    If (Row.transactionstatus_IsNull) Then
    .Parameters("@transaction_status").Value = DBNull.Value
    Else
    .Parameters("@transaction_status").Value = Row.transactionstatus
    End If
    If (Row.allocatedto_IsNull) Then
    .Parameters("@allocated_to").Value = DBNull.Value
    Else
    .Parameters("@allocated_to").Value = Row.allocatedto
    End If
    If (Row.transactiontype_IsNull) Then
    .Parameters("@transaction_type").Value = DBNull.Value
    Else
    .Parameters("@transaction_type").Value = Row.transactiontype
    End If
    If (Row.transactionvalue_IsNull) Then
    .Parameters("@transaction_value").Value = DBNull.Value
    Else
    .Parameters("@transaction_value").Value = Row.transactionvalue
    End If
    If (Row.username_IsNull) Then
    .Parameters("@user_name").Value = DBNull.Value
    Else
    .Parameters("@user_name").Value = Row.username
    End If
    If (Row.urn_IsNull) Then
    .Parameters("@urn").Value = DBNull.Value
    Else
    .Parameters("@urn").Value = Row.urn
    End If
    If (Row.companyid_IsNull) Then
    .Parameters("@company_id").Value = DBNull.Value
    Else
    .Parameters("@company_id").Value = Row.companyid
    End If
    .ExecuteNonQuery()
    End With
    End Sub

    Public Overrides Sub ReleaseConnections()
    odbcConn.Close()
    End Sub


    End Class[/PHP]


Comments

  • Options
    #2
    Beano
    Closed Accounts Posts: 3,357 ✭✭✭Beano


    Have you tried adding some logging into the package to check exactly what sql is being generated for the odbccmd object?


  • Options
    #3
    seamus
    Registered Users Posts: 68,317 ✭✭✭✭seamus


    '0000-00-00 00:00:00' != NULL, they're two different values

    That date is basically the same as having a zero in an integer field.

    If the transaction_date field was set to DEFAULT '0000-....' and you weren't specifying that column in the list, that would explain this behaviour - but since you are specifying the column in the statement, then this doesn't apply.

    However, I would look first at the source data to ensure that it's not giving you a zero transaction date. If that's not the case, then I would do as said above and log the statements to see what the ODBC engine is passing into MySQL. MySQL has an option which allows you to log everything. It shouldn't be turned on all the time, but you can switch it on for purposes like this.


  • Options
    #4
    stevenmu
    Moderators, Society & Culture Moderators Posts: 9,689 Mod ✭✭✭✭stevenmu


    It's a while since I did anything in SSIS, but IIRC the Visual Studio edition you use to design the SSIS package will let you debug and step through the package.

    I'd suggest setting up an import with just a handful of transactions if possible, then set some breakpoints and step through the code to examine the values and see what's happening, in particular you'll want to watch the values for Row.transactiondate_IsNull and Row.transactiondate.


  • Options
    #5
    Draco
    Subscribers Posts: 1,911 ✭✭✭Draco


    I've seen that sort of thing happen when the date you try and insert is not in the correct format - MySQL defaults to '0000-00-00 00:00:00' in that case.


  • Options
    #6
    Beano
    Closed Accounts Posts: 3,357 ✭✭✭Beano


    Draco wrote: »
    I've seen that sort of thing happen when the date you try and insert is not in the correct format - MySQL defaults to '0000-00-00 00:00:00' in that case.

    it seems like we could have a winner. That is very odd behaviour indeed.

    from

    http://dev.mysql.com/doc/refman/5.1/en/constraint-invalid-data.html
    MySQL enables you to store certain incorrect date values into DATE and DATETIME columns (such as '2000-02-31' or '2000-02-00'). The idea is that it is not the job of the SQL server to validate dates. If MySQL can store a date value and retrieve exactly the same value, MySQL stores it as given. If the date is totally wrong (outside the server's ability to store it), the special “zero” date value '0000-00-00' is stored in the column instead.


  • Advertisement
  • Options
    #7
    MagicMarker
    Banned (with Prison Access) Posts: 32,865 ✭✭✭✭MagicMarker


    Hi guys, thanks for the responses, I figured out what the issue was this morning.

    Normally the transaction dates would not have a time at source, eg ‘2014-01-02 00:00:00.000’. But recently this has changed for whatever reason, and now transactions do have a time, eg ‘2014-03-10 07:30:53.677', and for some reason the MySQL db doesn't like this particlar time, and decides that any value like this will be ignored and '0000-00-00....' will be used instead.

    My fix was just to truncate the time, which worked grand. Although I suppose that doesn't address the actual issue, more of a work around really. But the time isn't important in this instance anyway.


Advertisement