webERP Forum
Fatal Database write error when paying invoice [SOLVED] - Printable Version

+- webERP Forum (http://www.weberp.org/forum)
+-- Forum: webERP Discussion (/forumdisplay.php?fid=1)
+--- Forum: Problems / Bugs? (/forumdisplay.php?fid=8)
+--- Thread: Fatal Database write error when paying invoice [SOLVED] (/showthread.php?tid=8330)



Fatal Database write error when paying invoice [SOLVED] - donnell_512 - 03-24-2019 01:34 AM

Hi,
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?
Thanks


RE: Fatal Database write error when paying invoice - falkoner - 03-24-2019 02:06 AM

This is an issue that has been discussed off and on for over a decade. MySQL can run in different server SQL modes (https://dev.mysql.com/doc/refman/8.0/en/sql-mode.html) 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.

Thanks
Tim


RE: Fatal Database write error when paying invoice - donnell_512 - 03-24-2019 06:22 PM

Thank you so much Tim!

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

Ciao


RE: Fatal Database write error when paying invoice [SOLVED] - falkoner - 03-24-2019 07:56 PM

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.

Tim