Bad SQL query after upgrade from 1.6.1 - 1.6.1.6

[Rich Walker]

The upgrade seemed to go smoothy but i now get

 

• Bad SQL query
  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 'invoice_date`, invoice_number` AS `invoice_number`, einote` AS `einote` , ' at line 2

 

when i try access the orders in the admin

 

 

SELECT SQL_CALC_FOUND_ROWS
                                a.`id_order`, `reference`, `total_paid_tax_incl`, `payment`, a.`date_add` AS `date_add`, invoice_date` AS `invoice_date`, invoice_number` AS `invoice_number`, einote` AS `einote`
            ,
        a.id_currency,
        a.id_order AS id_pdf,
        CONCAT(LEFT(c.`firstname`, 1), '. ', c.`lastname`) AS `customer`,
        osl.`name` AS `osname`,
        os.`color`,
        IF((SELECT so.id_order FROM `ps_orders` so WHERE so.id_customer = a.id_customer AND so.id_order < a.id_order LIMIT 1) > 0, 0, 1) as new,
        country_lang.name as cname,
        IF(a.valid, 1, 0) badge_success, current_state, id_order as id_order2
            FROM `ps_orders` a
            
            
        LEFT JOIN `ps_customer` c ON (c.`id_customer` = a.`id_customer`)
        LEFT JOIN `ps_address` address ON address.id_address = a.id_address_delivery
        LEFT JOIN `ps_country` country ON address.id_country = country.id_country
        LEFT JOIN `ps_country_lang` country_lang ON (country.`id_country` = country_lang.`id_country` AND country_lang.`id_lang` = 1)
        LEFT JOIN `ps_order_state` os ON (os.`id_order_state` = a.`current_state`)
        LEFT JOIN `ps_order_state_lang` osl ON (os.`id_order_state` = osl.`id_order_state` AND osl.`id_lang` = 1)
             WHERE 1
            
             ORDER BY a.`id_order` DESC LIMIT 0, 50

 

 

 

at line 791 in file classes/db/Db.php

 

if ($webservice_call && $errno) {

787. $dbg = debug_backtrace();
788. WebserviceRequest::getInstance()->setError(500, '

 '.$this->getMsgError().'. From '.(isset($dbg[3]['class']) ? $dbg[3]['class'] : '').'->'.$dbg[3]['function'].'() Query was : '.$sql, 97);
789. } elseif (_PS_DEBUG_SQL_ && $errno && !defined('PS_INSTALLATION_IN_PROGRESS')) {
790. if ($sql) {
791. throw new PrestaShopDatabaseException($this->getMsgError().'<br /><br /><pre>'.$sql.'</pre>');
792. }
793.
794. throw new PrestaShopDatabaseException($this->getMsgError());
795. }
796. }

 

 

Is this a bug or a problem with my mysql database?

[heavyn]

I have the same Error. I update PS from 1.6.0.14 to 1.6.1.17.

Did you solve it?

[Rich Walker]

No. I dumped prestashop and moved woocommerce

[heavyn]

I tried Rollback, but I get

"textStatus: "error " errorThrown:"Internal Server Error " jqXHR: "

so I have to restore it manually, I hope that it will work.....

Upgrade of Presta is really problematic, better is not do it.

 

I use multi store with 2 shops, so it is not so easy to move to other solution.

[Scully]

The original SQL show in the initial post seems to be malformed. If somebody would upload the correct (wrong) statement I would be willing to investigate. I can also say that our 1.6.1.17 installations work flawlessly in this context.

[bellini13]

The original SQL from the original post in 2016 is invalid for 2 reasons

 

 

1) There are alias columns that have single quote characters, but only 1 of them

 

This is wrong

a.`id_order`, `reference`, `total_paid_tax_incl`, `payment`, a.`date_add` AS `date_add`, invoice_date` AS `invoice_date`, invoice_number` AS `invoice_number`, einote` AS `einote`

This is correct

a.`id_order`, `reference`, `total_paid_tax_incl`, `payment`, a.`date_add` AS `date_add`, invoice_date AS `invoice_date`, invoice_number AS `invoice_number`, einote AS `einote`

2) There is an invalid column name einote in the sql query.  What is einote?

This is wrong

a.`id_order`, `reference`, `total_paid_tax_incl`, `payment`, a.`date_add` AS `date_add`, invoice_date AS `invoice_date`, invoice_number AS `invoice_number`, einote AS `einote`

This is correct

a.`id_order`, `reference`, `total_paid_tax_incl`, `payment`, a.`date_add` AS `date_add`, invoice_date AS `invoice_date`, invoice_number AS `invoice_number`

If I fix those 2 issues, the query then works.

 

You likely have an old module or customization from before the upgrade that is causing this issue.  You'll need to investigate that further, locate the invalid code and remove it before the upgrade occurs...