Discount left empty leads to error at save

Support
1

If I leave the item discount empty, the system does not fill it 0 and fails as this is a required field. How can I set the default value of item discount to 0 in some template and which one?
thanks

extract of the log ...
DEBUG - 2015-12-29 11:20:53 --> File loaded: application/modules/settings/models/mdl_settings.php
DEBUG - 2015-12-29 11:20:53 --> Model Class Initialized
DEBUG - 2015-12-29 11:20:53 --> Language file loaded: language/english/ip_lang.php
DEBUG - 2015-12-29 11:20:53 --> Language file loaded: language/english/form_validation_lang.php
DEBUG - 2015-12-29 11:20:53 --> Language file loaded: language/english/custom_lang.php
DEBUG - 2015-12-29 11:20:53 --> Helper loaded: language_helper
DEBUG - 2015-12-29 11:20:53 --> File loaded: application/controllers/../modules/layout/controllers/layout.php
DEBUG - 2015-12-29 11:20:53 --> Layout MX_Controller Initialized
DEBUG - 2015-12-29 11:20:53 --> File loaded: application/modules/quotes/models/mdl_quote_items.php
DEBUG - 2015-12-29 11:20:53 --> Model Class Initialized
DEBUG - 2015-12-29 11:20:53 --> File loaded: application/modules/quotes/models/mdl_quotes.php
DEBUG - 2015-12-29 11:20:53 --> Model Class Initialized
DEBUG - 2015-12-29 11:20:53 --> File loaded: application/modules/item_lookups/models/mdl_item_lookups.php
DEBUG - 2015-12-29 11:20:53 --> Model Class Initialized
DEBUG - 2015-12-29 11:20:53 --> Encrypt Class Initialized
DEBUG - 2015-12-29 11:20:53 --> DB Transaction Failure
ERROR - 2015-12-29 11:20:53 --> Query error: Incorrect decimal value: '' for column 'item_discount_amount' at row 1
DEBUG - 2015-12-29 11:20:53 --> Language file loaded: language/english/db_lang.php

attached, the picture after adding an item, the discount field is empty by default io set to 0.00

discount_empty.png1550×109 13.3 KB

2

The discount field is not required and there should be no error if you leave it blank.
Which type of error is displayed?

3

tail of the error log above, complains about the decimal, when I enter 0 I do not have issues anymore.
It works with empty cell in your demo site, is it a different version of IP (mine is 1.4.4)?

4

This problem occurs if the MySQL server has strict mode enabled.

To turn it off follow this guide: https://support.realtyna.com/index.php?/Knowledgebase/Article/View/535/39/how-can-i-turn-off-mysql-strict-mode

1 Like
5

I added the line to /usr/local/mysql/my.ini

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

as it was not there and keep having the problem

DEBUG - 2015-12-29 16:16:57 --> Language file loaded: language/english/ip_lang.php
DEBUG - 2015-12-29 16:16:57 --> Language file loaded: language/english/form_validation_lang.php
DEBUG - 2015-12-29 16:16:57 --> Language file loaded: language/english/custom_lang.php
DEBUG - 2015-12-29 16:16:57 --> Helper loaded: language_helper
DEBUG - 2015-12-29 16:16:57 --> File loaded: application/controllers/../modules/layout/controllers/layout.php
DEBUG - 2015-12-29 16:16:57 --> Layout MX_Controller Initialized
DEBUG - 2015-12-29 16:16:57 --> File loaded: application/modules/quotes/models/mdl_quote_items.php
DEBUG - 2015-12-29 16:16:57 --> Model Class Initialized
DEBUG - 2015-12-29 16:16:57 --> File loaded: application/modules/quotes/models/mdl_quotes.php
DEBUG - 2015-12-29 16:16:57 --> Model Class Initialized
DEBUG - 2015-12-29 16:16:57 --> File loaded: application/modules/item_lookups/models/mdl_item_lookups.php
DEBUG - 2015-12-29 16:16:57 --> Model Class Initialized
DEBUG - 2015-12-29 16:16:57 --> Encrypt Class Initialized
DEBUG - 2015-12-29 16:16:57 --> DB Transaction Failure
ERROR - 2015-12-29 16:16:57 --> Query error: Incorrect decimal value: '' for column 'item_discount_amount' at row 1
DEBUG - 2015-12-29 16:16:57 --> Language file loaded: language/english/db_lang.php
gbw-l-m0172:logs root#

mysql> SELECT @@SESSION.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode                                                                                                                        |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

empty the variable

mysql> SET global.sql_mode= '';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'global.sql_mode= ''' at line 1
mysql> SET @@global.sql_mode= '';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

Emptying the global variable in mysql worked for the current session but gets repopulated after restarting the mysql daemon

I finally found that there was a second my.cnf in /Library/WebServer/Documents/web-settings/my.cnf
adding the line to that one worked

(help found at http://stanlemon.net/2013/03/23/mysql-strict-mode-on-os-x/ + sudo find / -name my.cnf)

I set it in the [mysqld] section as:

sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

instead of the default installation:
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

whatever this will change in other databases … :scream:

‘sweat swet’

PS: this also fixed the related problem Create new quote does but remains in the create page

7

@Kovah: However, since Mysql 5.7.X is now more strict by default, it may be nicer to change the ‘not null’ status of optional fields in the different IP tables where all fields are currently set to not_null and lead to input enforcement at runtime.

As example, the table structure for ip_invoice_items (see item_discount_amount)