webERP Forum

Full Version: Fatal Database write error when paying invoice [SOLVED]
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
when I try to save a supplier invoice payment (Suppliers -> Enter a payment to ...) I get the following blocking error and I cannot proceed:

Database Error 1292 : Cannot insert a payment transaction against the supplier because
Incorrect date value: '0000-00-00' for column 'duedate' at row 1
Database SQL Failure : Cannot insert a payment transaction against the supplier using the SQL
INSERT INTO supptrans ( transno, type, supplierno, trandate, inputdate, suppreference, rate, ovamount, transtext ) VALUES ('49', 22,'STEG','2018-09-13','2019-03-23 12-45-08','Bank Transfer','1','-79.90263','HD WD Blue 2TB')
Database Rollback Due to Error Above : Rolling Back Transaction OK
MySQL version 5.7.24


webERP version 4.14.1
Ubuntu Linux 16.04.4
MySQL version 5.7.24

Is anybody experiencing the same issue? Any idea about how to fix it?
This is an issue that has been discussed off and on for over a decade. MySQL can run in different server SQL modes ( one of which is NO_ZERO_IN_DATE. Different installs of MySQL come with different modes set, and some include this NO_ZERO_IN_DATE mode, others don't.

To fix the definition for this particular table simply run the SQL:

ALTER TABLE supptrans MODIFY COLUMN duedate date NOT NULL DEFAULT '2000-01-01';

and it should change the default date. However you will run into this issue elsewhere.

A longer term solution is to read the link on SQL server modes above, and change the default server mode for your installation.

Thank you so much Tim!

I changed the global MYSQL settings and it now works like a charm.

I have long advocated that we define a server mode that we guarantee webERP will on, to end this confusion.

There are other modes that can cause problems. For instance there is one mode where if you try to populate a field with a string longer than the field length the transaction gets aborted, rather than the field being truncated. This can happen where a narrative string is dynamically created with part codes, GL codes etc.

IMO defining a mode that we guarantee webERP will run under will save users a lot of aggravation.

Reference URL's