• From: USA
  • Registered: 07/16/2017
  • Posts: 276

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

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.

  • From: Vietnam
  • Registered: 09/28/2016
  • Posts: 349

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

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.

Phuong

notrinos's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

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

@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

  • From: USA
  • Registered: 07/16/2017
  • Posts: 276

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

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.

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

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

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

  • Registered: 12/25/2007
  • Posts: 2,448

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

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

  • From: USA
  • Registered: 07/16/2017
  • Posts: 276

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

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.

  • Registered: 12/25/2007
  • Posts: 2,448

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

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