Hi, upgraded to 3.4 and attempting to close books for a financial year. Popup shows that transaction could not be saved and gnucash.trace contains the following: * 10:17:49 ERROR <gnc.backend.dbi> [error_handler()] DBI error: 1292: Incorrect datetime value: '1970-01-01 00:00:00' for column 'reconcile_date' at row 1 * 10:17:49 ERROR <gnc.backend.dbi> [GncDbiSqlConnection::execute_nonselect_statement()] Error executing SQL INSERT INTO splits(guid,tx_guid,account_guid,memo,action,reconcile_state,reconcile_date,value_num,value_denom,quantity_num,quantity_denom) VALUES('0d61f7cf57324e48ac27f6e756c42871','64a9728590134fa692c0754be096c9d7','e3217bd9475b63c50d2dd517f8b6547a','','','n','1970-01-01 00:00:00',25596,100,25596,100) * 10:17:49 ERROR <gnc.backend.sql> [GncSqlBackend::execute_nonselect_statement()] SQL error: INSERT INTO splits(guid,tx_guid,account_guid,memo,action,reconcile_state,reconcile_date,value_num,value_denom,quantity_num,quantity_denom) VALUES('0d61f7cf57324e48ac27f6e756c42871','64a9728590134fa692c0754be096c9d7','e3217bd9475b63c50d2dd517f8b6547a','','','n','1970-01-01 00:00:00',25596,100,25596,100) * 10:17:49 ERROR <gnc.engine> [commit_err()] commit error: 17 when the same query was attempted in mysql directly, it also failed but changing the time from 00:00:00 to 22:00:00 worked. I am working out in the CET timezone so I'm assuming that some conversion is pushing this back to 1969-12-31 23:00:00 which is not acceptable by mysql's timestamp. I haven't marked this down as a blocker because I found a workaround by switching off all 'reconcile' options in the preferences. Otherwise, great work.
The workaround does not work. I was working on a file-based backup when I tried it out so I've had to put the importance up on this. I'll see if I can get it to think I'm working out of UTC and take it from there. Cheers
If you're really using GnuCash 3.4 the date fields should all be DATETIME not TIMESTAMP. There's a scrub routine that's supposed to fix that for you, but you can do it manually, e.g. ALTER TABLE transactions CHANGE date_entered DATETIME CHANGE date_posted DATETIME; The other timestamp fields are prices.date, splits.reconciled_date, slots.timespec_cal, entry.date, entry.date_entered, invoices.date_opened, invoices.date_posted, order.date_opened, and order.date_posted. That will get you going but we need to figure out why the scrub didn't fix your database automatically. What version of GnuCash did you use to create the database? Please paste the results of SELECT name FROM slots WHERE obj_guid = (SELECT guid_val FROM slots WHERE name = 'features');
Hi John, thanks for the immediate help. Converted your reply to the following SQL, executed it and was able to close of 5 years without issue. ALTER TABLE entries MODIFY COLUMN date DATETIME; ALTER TABLE entries MODIFY COLUMN date_entered DATETIME; ALTER TABLE invoices MODIFY COLUMN date_opened DATETIME; ALTER TABLE invoices MODIFY COLUMN date_posted DATETIME; ALTER TABLE orders MODIFY COLUMN date_opened DATETIME; ALTER TABLE orders MODIFY COLUMN date_closed DATETIME; ALTER TABLE prices MODIFY COLUMN date DATETIME; ALTER TABLE slots MODIFY COLUMN timespec_val DATETIME; ALTER TABLE splits MODIFY COLUMN reconcile_date DATETIME; ALTER TABLE transactions MODIFY COLUMN enter_date DATETIME; // Changed entry to entries // Changed order to orders // Changed orders.date_posted to date_closed (because I don't have a date_posted // Changed date_entered to enter_date Not sure what version I created the database in but my logs date to about May 2016. I've been on gnucash from 2003 and moved from XML to mysql very early on so I wouldn't be surprised if there is some real old stuff there. I only upgraded lately because of issues I had with unrealised gains/losses which weren't working properly and putting my TB out by millions of Euros. I executed the query you requested and no rows were returned!
This turned out to be a missed version change for the splits table resulting in a TIMESPEC column remaining. It's been fixed.
