Error 23052 when creating manufacturing order

[slotic]

Bug

When I create a manufacturing order from a BOM which has only one entry the error 23502 pops up and no manufacturing order was created. The exact message is:
ERROR: 23502: null value in column 'tms' violates not-null constraint DETAIL: Failing row contains (4, , 1, null, 75, 6, null, null, null, 2020-07-26 21:24:33, null, null, 1, null, null, null, null, 0, 198, null, null, 2, null). SCHEMA NAME: public TABLE NAME: llx_mrp_mo COLUMN NAME: tms LOCATION: ExecConstraints, execMain.c:2017

Environment

• Version: 12.0.1
• OS: Ubuntu 16.04
• Web server: Apache
• PHP: PHP 7.2
• Database: postgresql

Expected and actual behavior

expected a new MO is created - actually nothing

Steps to reproduce the behavior

[Verbose description]

Attached files (Screenshots, screencasts, dolibarr.log, debugging informations…)

[Files]
20200726_Mo.pdf

[slotic]

dolibarr log for this issue

2020-07-27 22:19:57 DEBUG 192.168.1.137 Mo::createCommon create
2020-07-27 22:19:57 DEBUG 192.168.1.137 sql=INSERT INTO llx_mrp_mo (ref, fk_bom, fk_product, qty, label, fk_soc, fk_project, fk_warehouse, note_public, note_private, date_creation, tms, date_valid, fk_user_creat, fk_user_modif, date_start_planned, date_end_planned, import_key, model_pdf, status) VALUES ('', 2, 198, 75, NULL, NULL, NULL, 6, NULL, NULL, '2020-07-27 22:19:57', NULL, NULL, 1, NULL, '2020-05-05 14:00:00', '2020-05-05 14:05:00', NULL, NULL, 0)
2020-07-27 22:19:57 ERR 192.168.1.137 DoliDBPgsql::query SQL Error message: ERROR: 23502: null value in column "tms" violates not-null constraint
DETAIL: Failing row contains (5, , 1, null, 75, 6, null, null, null, 2020-07-27 22:19:57, null, null, 1, null, null, null, null, 0, 198, 2020-05-05 14:00:00, 2020-05-05 14:05:00, 2, null).
SCHEMA NAME: public
TABLE NAME: llx_mrp_mo
COLUMN NAME: tms
LOCATION: ExecConstraints, execMain.c:2017 (DB_ERROR_23502)

[ksar-ksar]

Hi,

Could check the definition of the field tms on the llx_mrp_mo table ?
It seems that the NOT NULL is active and it shouldn't.

https://github.com/Dolibarr/dolibarr/blob/develop/htdocs/install/mysql/tables/llx_mrp_mo.sql#L30

[eldy]

Try to run the following command on your postgres database:

CREATE TRIGGER update_customer_modtime BEFORE UPDATE ON llx_mrp_mo FOR EACH ROW EXECUTE PROCEDURE update_modified_column_tms();
CREATE TRIGGER update_customer_modtime BEFORE UPDATE ON llx_mrp_mo_extrafields FOR EACH ROW EXECUTE PROCEDURE update_modified_column_tms();
CREATE TRIGGER update_customer_modtime BEFORE UPDATE ON llx_mrp_production FOR EACH ROW EXECUTE PROCEDURE update_modified_column_tms();

Then restart to create a MO

[slotic]

Hello, still doesn't work, this issue should have not been closed before the author's feedback i guess... Anyway, triggers have been well created (double checked with pgadmin) but same error, same message, same dolibarr log. Not an expert but the trigger is set up to be ran when a row is updated.. but in this case the table is empty, brand new and there is nothing updated, BUT created... seems not abnormal that the trigger is not executed. And like the column tms has a not null constraint it fails
I'm going to remove temporarily the not null constraint to see what happens..

[slotic]

Then, i've changed the tms definition from 'NOT NULL' to nothing and the mo creation worked fine. And changed back from the definition from nothing to 'NOT NULL', i've made a modification and it worked fine again
Question 1: Is it normal that all the tms columns are set to 'NOT NULL' in my databases ? It doesn't come from me so it's from the scripts or it's something automatic from Postgresql
Question 2 : Never had such problem when all the tms colums are NOT NULL as i wrote previously, so why with this table ?
Remark : My assumption about the fact that the trigger works only for an updated row and not a created row is perhaps not stupid
Thanks in advance for your comments because your solution is not fully useable

[slotic]

To be complete, setting back NOT NULL once the MO is created works fine, no problem to modifiy, but still impossible to create a new one. Then i'm obliged to keep tms with NO 'NOT NULL' constraint in order to go ahead until the cause of the problem is really identified