DBI issue: Incorrect datetime value: '19700101000000'

[John Ralls]

> On Jan 18, 2018, at 10:05 PM, Kristoffel De Geest <kdg1955 at gmail.com> wrote:
> 
> Recently I moved my gnucash data from my old PC (Fedora 26) to my new
> laptop (Fedora 27). The version of gnucash is the same (2.6.18).
> 
> The problem occurs both in the transferred mysql database and in a *new
> created* mysql databases. With XML I haven't any problem. Everything worked
> fine on my old PC,
> 
> In the application I get a message like 'Can't write to the database' (in
> Dutch 'Niet in staat om databank op te slaan').
> I also use the API (in C), and there I get the following error message:
> 
> * 20:55:36  CRIT <gnc.backend.dbi> [mysql_error_fn()] DBI error: 1292:
> Incorrect datetime value: '19700101000000' for column 'timespec_val' at row
> 1
> * 20:55:36  CRIT <gnc.backend.dbi> [conn_execute_nonselect_statement()]
> Error executing SQL INSERT INTO
> slots(obj_guid,name,slot_type,int64_val,string_val,double_val,timespec_val,guid_val,numeric_val_num,numeric_val_denom,gdate_val)
> VALUES('609d0c17759f821c9e11b53999169d4e','notes',4,0,'ABC ...
> XYZ',0,'19700101000000',NULL,0,1,NULL)
> * 20:55:36  CRIT <gnc.backend.sql> [gnc_sql_do_db_operation()] SQL error:
> INSERT INTO
> slots(obj_guid,name,slot_type,int64_val,string_val,double_val,timespec_val,guid_val,numeric_val_num,numeric_val_denom,gdate_val)
> VALUES('609d0c17759f821c9e11b53999169d4e','notes',4,0,'ABC ...
> XYZ',0,'19700101000000',NULL,0,1,NULL)
> * 20:55:36  CRIT <gnc.backend.sql> [save_transaction()] Transaction ABC ...
> XYZ dated 12/15/17 in account BE93001255516567 not saved due to Slots save
> failed. Check trace log for SQL errors.
> ...
> * 20:55:36  CRIT <gnc.engine> xaccAccountGetName: assertion
> 'GNC_IS_ACCOUNT(acc)' failed
> ...
> 
> 
> I get similar errors in the table *'splits'* for the '*reconcile_date*'
> field. Probably also for other tables where a field type *timestamp* occurs.
> In the API I can give some of these fields a value, which offers a
> temporary solution. For the table *'slots'* this is not possible directly
> (kvp interface). I do not see workarounds in the application itself.
> 
> versions :
>    gnucash -> 2.6.18-1
>    dbi -> 0.9.0-9
>    mysql (mariadb) -> 10.9.2-3
> 
> 
> I like to continue with my mysql database, because I have many sql reports.
> 
> Please help?
> 
> Thanks in advance,

See https://bugzilla.gnome.org/show_bug.cgi?id=784623 <https://bugzilla.gnome.org/show_bug.cgi?id=784623>.

Regards,
John Ralls