• From: Singapore
  • Registered: 03/12/2015
  • Posts: 59

Topic: MYSQL 8 ERROR (on Ver 2.3.25)

Sorry to open an issue on version 2.3.25.  This issue is related to mysql 8.   One of my 2 installations is 2.3.25 and the other one is 2.4.  We did not upgrade this 2.3.25 to 2.4 because we have some customizations done to this version and we couldn't convert the customization to 2.4.  It has been running well without issues on Centos 7, mysql 5.7 until we upgraded our server to Ubuntu 20.04 and Mysql 8 recently.  I search thru' the forum and I found a similar issue in the forum but without a solution:

https://frontaccounting.com/punbb/post.php?tid=1977

Below is the error similar to the above thread (Note the highlight in Blue and Red):
-------
error code : 1064
error message : 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 ') LIMIT 0, 33' at line 35

SELECT * FROM ((SELECT trans.type, trans.trans_no, trans.reference, supplier.supp_name, trans.supp_reference, trans.tran_date, trans.due_date, supplier.curr_code, (trans.ov_amount + trans.ov_gst + trans.ov_discount) AS TotalAmount, trans.alloc AS Allocated, ((trans.type = 20 OR trans.type = 21) AND trans.due_date < '2022-02-06') AS OverDue, (ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount) - trans.alloc <= 0.004) AS Settled FROM 0_supp_trans as trans, 0_suppliers as supplier WHERE (supplier.supplier_id = trans.supplier_id AND trans.tran_date >= '2022-01-07' AND trans.tran_date <= '2022-02-06' AND trans.ov_amount != 0) UNION (SELECT 25 as type, trans.id as trans_no, trans.reference, supplier.supp_name, po.requisition_no AS supp_reference, delivery_date as tran_date, '' as due_date, supplier.curr_code, '' AS TotalAmount, '' AS Allocated, 0 as OverDue, 1 as Settled FROM 0_grn_batch as trans, 0_suppliers as supplier, 0_purch_orders as po WHERE supplier.supplier_id = trans.supplier_id AND trans.purch_order_no = po.order_no AND trans.delivery_date >= '2022-01-07' AND trans.delivery_date <= '2022-02-06')) as tr) LIMIT 0, 20
--------

After some research and tests, I found 2 issues (highlighted in blue and red in the error message) in the codes but I couldn't resolve it because it does not seem to be straight forward.

I found that the program codes can be run on mysql 5.7 without any problem, but when it runs on mysql 8, it produced a syntax error. 

Issue 1:

I don't understand why a "(" was produced before the supplier.supplier_id.  (refer to the error message which I highlited in blue). 

In this file /purchasing/includes/db/supp_trans_db.inc, this code does not have "(" after WHERE.

function get_sql_for_supplier_inquiry($filter, $after_date, $to_date, $supplier_id=ALL_TEXT)
{
    $date_after = date2sql($after_date);
    $date_to = date2sql($to_date);

    $sql = "SELECT trans.type,
        trans.trans_no,
        trans.reference,
        supplier.supp_name,
        trans.supp_reference,
        trans.tran_date,
        trans.due_date,
        supplier.curr_code,
        (trans.ov_amount + trans.ov_gst  + trans.ov_discount) AS TotalAmount,
        trans.alloc AS Allocated,
        ((trans.type = ".ST_SUPPINVOICE." OR trans.type = ".ST_SUPPCREDIT.") AND trans.due_date < '" . date2sql(Today()) . "') AS OverDue,
        (ABS(trans.ov_amount + trans.ov_gst  + trans.ov_discount) - trans.alloc <= ".FLOAT_COMP_DELTA.") AS Settled
        FROM ".TB_PREF."supp_trans as trans, ".TB_PREF."suppliers as supplier
       
        WHERE supplier.supplier_id = trans.supplier_id <---- it produced a "(" before supplier-supplier_id (don't know why?  See the error code above) and that caused a syntax error behind.
        
        AND trans.tran_date >= '$date_after'
        AND trans.tran_date <= '$date_to'
        AND trans.ov_amount != 0";    // exclude voided transactions
       
Issue 2:

A ")" was produced after the "as tr)".  I couldn't find this ")" in the code.

    if (($filter == '6'))
    {
            $sql = $sql2;
    }
    elseif (!isset($filter) || $filter == ALL_TEXT || $filter == '6') {
        $sql = "SELECT * FROM (($sql) UNION ($sql2)) as tr"; <--- a ")" was produced here after "as tr".
       }

After searching thru' the relevant codes, I couldn't find how these 2 "(" and ")" were produced. If I could find them, I could just modify the codes to fix the problems.  I would appreciate if anyone could help me on this.

I found that there are some changes made to 2.4 on the above file and when it is run on mysql 8, it does not produce such issue.   I attempted to copy those changes to 2.3.25, but yet I couldn't solve the issue.

Thanks!

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,090

Re: MYSQL 8 ERROR (on Ver 2.3.25)

0_grn_batch.delivery_date has a DEFAULT of 0000-00-00 which is acceptable in MySQL 5.x but in MySQL 8, a setting in the my.ini is necessary to override it's rejection.

A dumb solution would be:

SET sql_mode = 'allow_invalid_dates';

Alternatively in /etc/my.cnf, add the following:

sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

and restart MySQL.



References:
https://gokhan.ozar.net/blog/how-to-fix-incorrect-datetime-value-mysql-mariadb/
https://dba.stackexchange.com/questions/20638/dates-changed-to-0000-00-00
https://developpaper.com/problems-with-mysql-8-0-13-setting-date-0000-00-000000/

apmuthu's Website

  • From: Singapore
  • Registered: 03/12/2015
  • Posts: 59

Re: MYSQL 8 ERROR (on Ver 2.3.25)

Hi APMuthu,

Thanks very much for your suggestions. 
I'm not sure the issues I mentioned with the extra ")" and "(" can resolve the issue.

I will give it a try when I set up a test server.   Due to the urgency, we have decided to down grade the OS from ubuntu 20.04 to ubuntu 18.04.  Ubuntu 18.04 comes default with mysql 5.7 and there is no issue.

I will post my findings here once I have a chance to verify it.

/Mark