FrontAccounting forum — Entering invoice for tax exempt supplier results in db error?

Re: Entering invoice for tax exempt supplier results in db error?

null@example.com (itronics) — Fri, 25 Jan 2019 20:47:34 +0000

Ok, I have fixed the issue introducing direct string to numeric data conversion in sql query. This should fix also any problems which could arise in other places where the add_trans_tax_details is called.

Janusz

Re: Entering invoice for tax exempt supplier results in db error?

null@example.com (Braath Waate) — Fri, 25 Jan 2019 13:23:05 +0000

You are right that my suggestion does not work, it caused sales order entry to completely fail.

The problem is not just limited to tax exempt suppliers. STRICT_ALL_TABLES for also caused sales order entry for an order with a tax exempt item to fail as well:

Cannot save trans tax details
error code : 1366
error message : Incorrect integer value: '' for column 'tax_type_id' at row 1
sql that failed was : INSERT INTO 1_trans_tax_details (trans_type, trans_no, tran_date, tax_type_id, rate, ex_rate, included_in_price, net_amount, amount, memo, reg_type) VALUES ('13','6083','2019-01-24','','','1',1,'3.15','0','auto',NULL)

The sql failure is the same as the tax exempt supplier problem:

DATABASE ERROR : Cannot save trans tax details
error code : 1366
error message : Incorrect integer value: '' for column 'tax_type_id' at row 1
sql that failed was : INSERT INTO 0_trans_tax_details (trans_type, trans_no, tran_date, tax_type_id, rate, ex_rate, included_in_price, net_amount, amount, memo, reg_type) VALUES ('20','5','2018-12-31','','','1',0,'10','0','rr8','1')

On my site, these errors are easy to reproduce. On the demo database, I changed DinoSaurius to Tax Exempt. Then I tried to create a direct supplier invoice with a single item.

Re: Entering invoice for tax exempt supplier results in db error?

null@example.com (itronics) — Fri, 25 Jan 2019 09:22:56 +0000

Yes, this seems to be side effect of just introduced strict mode. But the proposed patch can have side effect too. In some countries there is existing tax rate 0% which is not the same as null tax (i.e. tax not applicable).
Where you encountered the problem, and which was the SQL query triggering the error?
Janusz

Re: Entering invoice for tax exempt supplier results in db error?

null@example.com (joe) — Fri, 25 Jan 2019 00:02:29 +0000

Ok, but the 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE' was implemented by Janusz to prevent SQL Injections. As I told we fixed a lot of sideeffect errors, but your error was another one.

I will ask Janusz to do something about this.

/Joe

Re: Entering invoice for tax exempt supplier results in db error?

null@example.com (Braath Waate) — Thu, 24 Jan 2019 23:20:18 +0000

Modifying connect_db_mysqli.inc as notrinos suggests also fixes the problem.

My core is synced to apmuthus latest code. Thus it has the recent commit with SQL_MODE set to STRICT_ALL_TABLES. Thus it appears that this commit causes the problem.

I am using php 5.x on ubuntu 16 and php 7.2 on ubuntu 18.

Re: Entering invoice for tax exempt supplier results in db error?

null@example.com (joe) — Thu, 24 Jan 2019 22:40:01 +0000

@Braaht Waate.

Which version of PHP do you use? And did it fix the problem?

Janusz did a security update due to some report of SQL Injections. We have taken most sideeffect errors but you might have run into another one.

Joe

Re: Entering invoice for tax exempt supplier results in db error?

null@example.com (notrinos) — Thu, 24 Jan 2019 18:01:02 +0000

Hello Braath Waate

I have have not investigate to functions but based on your describe blank value passed to an interger column I guess this is a violation with SQL strict mode.
Try to change line 13 of connect_db_mysqli.inc with the following

define('SQL_MODE', ''); // STRICT_ALL_TABLES,NO_ZERO_IN_DATE ?

and test again to see if the problem disappeared or not.

Entering invoice for tax exempt supplier results in db error?

null@example.com (Braath Waate) — Thu, 24 Jan 2019 17:42:55 +0000

I think it happened after I installed os updates with a mysql update on both Ubuntu 16 and 18, because it used to work fine, but I do muck with my core so maybe it is my own fault, but I couldn't see how I could have caused it. I will try to reproduce this on standard FA when I have a chance. If I can't, then I will delete this post.

Anyway, mysql failed on an insert to trans_tax_details because tax_type_id is blank and tax_type_id is defined as an integer in the table. It also didn't like rate set to null, which is defined as not null in the table.

I fixed the problem in my core:

+++ b/core/taxes/tax_calc.inc
@@ -161,7 +161,7 @@ function get_tax_for_items($items, $prices, $shipping_cost, $tax_group, $tax_inc
                $ret_tax_array[$k]['Net'] = 0;
        }
        
-       $ret_tax_array['exempt'] = array('Value'=>0, 'Net'=>0, 'rate' => null, 'tax_type_id' => '', 'sales_gl_code' => '');
+       $ret_tax_array['exempt'] = array('Value'=>0, 'Net'=>0, 'rate' => 0, 'tax_type_id' => 0, 'sales_gl_code' => '');
        $dec = user_price_dec();
        // loop for all items

I wonder if this is some new feature of mysql and if there is some strictness configuration setting for it? If so, I am sure someone can enlighten me.