Incorrect datetime value column timestamp

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
infinitep
Posts: 17
Joined: Wed 08 Jul 2009 16:31

Incorrect datetime value column timestamp

Post by infinitep » Wed 08 Jul 2009 21:55

Ok I tried another simple test app using 5.20.33 trial. I get the following error;


[MySqlException (0x80004005): Incorrect datetime value: '00010101000000' for column 'timestamp' at row 1]
Devart.Data.MySql.ah.o() +270
Devart.Data.MySql.ah.c() +200
Devart.Data.MySql.t.a(ad[]& A_0, Int32& A_1) +134
Devart.Data.MySql.t.a(Byte[] A_0, Int32 A_1, Boolean A_2) +106
Devart.Data.MySql.a3.e() +188
Devart.Data.MySql.a3.o() +89
Devart.Data.MySql.MySqlCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3) +1376
Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior) +310
System.Data.Common.DbCommand.ExecuteReader() +12
Devart.Common.DbCommandBase.ExecuteNonQuery() +64
Devart.Data.Linq.Provider.DataProvider.a(String A_0, IList`1 A_1, IDbCommand& A_2) +537
Devart.Data.Linq.v.b(MetaType A_0, q A_1, Boolean A_2) +2541
Devart.Data.Linq.x.a(h A_0, Object A_1, ModifiedMemberInfo[] A_2, Boolean A_3) +423
Devart.Data.Linq.j.a(x A_0, h A_1, Boolean A_2) +143
Devart.Data.Linq.j.a(DataContext A_0, ConflictMode A_1) +2581
Devart.Data.Linq.j.b(DataContext A_0, ConflictMode A_1) +49
Devart.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) +412
Devart.Data.Linq.DataContext.SubmitChanges() +30
CN.Index.btnSubmit_Click(Object sender, EventArgs e)
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111


using the following code



protected void btnSubmit_Click(object sender, EventArgs e)
{
using (projectdatacontext dc = new projectdatacontext())
{
account a = new account();
a.email = txtEmail.Text;
dc.accounts.InsertOnSubmit(a);
dc.SubmitChanges();
}
}



The account table has a timestamp field with "CURRENT_TIMESTAMP" as the default value NOT NULL.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Fri 10 Jul 2009 11:45

Could you please send me (support * devart * com) the script of the account table and the generated model?

infinitep
Posts: 17
Joined: Wed 08 Jul 2009 16:31

Post by infinitep » Wed 25 Nov 2009 10:48

This be my next problem, hopefully I can get dotconnect working with my project soon, it offers so much potential.

Incorrect datetime value: '00010101000000' for column 'Timestamp' at row 1
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: Devart.Data.MySql.MySqlException: Incorrect datetime value: '00010101000000' for column 'Timestamp' at row 1

[MySqlException (0x80004005): Incorrect datetime value: '00010101000000' for column 'Timestamp' at row 1]
Devart.Data.MySql.bk.q() +270
Devart.Data.MySql.bk.c() +200
Devart.Data.MySql.v.a(ah[]& A_0, Int32& A_1) +134
Devart.Data.MySql.v.a(Byte[] A_0, Int32 A_1, Boolean A_2) +106
Devart.Data.MySql.a3.e() +169
Devart.Data.MySql.a3.o() +89
Devart.Data.MySql.MySqlCommand.InternalExecute(CommandBehavior behavior, IDisposable stmt, Int32 startRecord, Int32 maxRecords) +1418
Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior) +749
System.Data.Common.DbCommand.ExecuteReader() +12
Devart.Common.DbCommandBase.ExecuteNonQuery() +64
Devart.Data.Linq.Provider.DataProvider.a(String A_0, IList`1 A_1, IDbCommand& A_2) +547

[LinqCommandExecutionException: Error on executing DbCommand.]
Devart.Data.Linq.LinqCommandExecutionException.CanThrowLinqCommandExecutionException(String message, Exception e) +79
Devart.Data.Linq.Provider.DataProvider.a(String A_0, IList`1 A_1, IDbCommand& A_2) +890
Devart.Data.Linq.v.a(MetaType A_0, q A_1, Object A_2, ModifiedMemberInfo[] A_3, Boolean A_4) +3210
Devart.Data.Linq.f.a(b A_0, Object A_1, ModifiedMemberInfo[] A_2, Boolean A_3) +534
Devart.Data.Linq.k.a(f A_0, b A_1, Boolean A_2) +143
Devart.Data.Linq.k.a(DataContext A_0, ConflictMode A_1) +2581
Devart.Data.Linq.k.b(DataContext A_0, ConflictMode A_1) +49
Devart.Data.Linq.DataContext.SubmitChanges(ConflictMode failureMode) +412
Devart.Data.Linq.DataContext.SubmitChanges() +30






----

I will make a sample app to try and replicate this.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Wed 25 Nov 2009 16:41

This looks like a problem with minimal DateTime value.
MySQL does not accept the date with value less than '1970-01-01 01:00:00',
and the default DateTime minimal value is '0001-01-01-00:00:00'.
Also please make sure that you have set the Auto Generated Value property to true
for the db-generated Timestamp column, if it is the case.

infinitep
Posts: 17
Joined: Wed 08 Jul 2009 16:31

Post by infinitep » Thu 26 Nov 2009 03:07

Auto generated is set. As for the minimal datetime value, the row has a default of "CURRENT_TIMESTAMP"?????

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 26 Nov 2009 09:41

I have made a simple insert test with timestamp-typed version column and everything went ok.
Could you please make a small test project illustrating the problem and send it to me?

infinitep
Posts: 17
Joined: Wed 08 Jul 2009 16:31

Post by infinitep » Thu 26 Nov 2009 12:02

Some more info I have found.

1) The problem only occurs when I have a timestamp field with the data of "0000-00-00 00:00'. According to the mysql documentation 'With strict mode disabled, invalid dates such as '2004-04-31' are converted to '0000-00-00' and a warning is generated.' Is it possible that dotconnect is converting my "0000-00-00 00:00:00' to '0001-01-01 00:00:00' instead?

2) If I add a valid date to the data of the timestamp field the error does not occur, however the current timestamp is not inserted and the data remains the same. This can be seen in the SQL generated. Should the sql say update @timestamp set @timestamp = CURRENT_TIMESTAMP not the current data of the field?

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Fri 27 Nov 2009 10:59

What scenario are you using to update the timestamp field?
Is it trigger or default value way?
Also, please check that you have set the AutoSync property to Always and the Is Version property to true,
if this is a Version field.

infinitep
Posts: 17
Joined: Wed 08 Jul 2009 16:31

Post by infinitep » Tue 13 Apr 2010 17:55

new feature in latest release to select all fields in model is not working and is actually made it worst.

If i use the find in model and select all it is not allowing me to edit and now I must edit every single field where before I could select multiple fields that were the same type and edit the properties once.

AndreyR
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 15 Apr 2010 12:33

Thank you for your activity.
This is a Visual Stdio integration issue, it is not reproducible in the standalone Entity Developer.
We are invetigating the problem, I'll let you know about the results.

Post Reply