Bad Sql Query On Attachements

[hipeq]

Hello,

 

When I click on Catalog -- Attachments I get the following error in my back office:

 

• 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 'virtual ON a.id_attachment = virtual.id_attachment WHERE 1 ORDER ' at line 6

 

I run on a dedicated IIS server, PHP7 and MySQL 5.7  Everything else runs fine on this setup... Any thoughts?

 

Thank you

 

 

[hipeq]

So here is what I did, although I dont think this is the right way to do it

I commented lines 46, 47, 48 and it started working.... I think this may have something to do with MySQL5.7

 

        //$this->_select = 'IFNULL(virtual.products, 0) as products';

        //$this->_join = 'LEFT JOIN (SELECT id_attachment, COUNT(*) as products FROM '._DB_PREFIX_.'product_attachment GROUP BY id_attachment) virtual ON a.id_attachment = virtual.id_attachment';

        //$this->_use_found_rows = false;

[scsiborg]

OK. The reason this is happening is because they are using the reserved keyword "virtual" as the name of derived joined table, you cant do that with later verisons of mysql.

Just rename all instances of virtual to virtualx. A find and replace will help here.

Also where you see "virtual ON", change that to "as virtualx ON"

[David Eschmeyer]

more details for everyone else:

 

/controllers/admin/AdminAttachmentsController.php

 

change 

        $this->_select = 'IFNULL(virtual.products, 0) as products';
        $this->_join = 'LEFT JOIN (SELECT id_attachment, COUNT(*) as products FROM '._DB_PREFIX_.'product_attachment GROUP BY id_attachment) virtual ON a.id_attachment = virtual.id_attachment';

       to

 

        $this->_select = 'IFNULL(virtualx.products, 0) as products';
        $this->_join = 'LEFT JOIN (SELECT id_attachment, COUNT(*) as products FROM '._DB_PREFIX_.'product_attachment GROUP BY id_attachment) virtualx ON a.id_attachment = virtualx.id_attachment';

and further down

                'filter_key' => 'virtual!products',

to:

                'filter_key' => 'virtualx!products',

[Patrick Proot]

Thanks very much work perfectly for 1.6.5

[eec]

Thank you very much, worked perfectly on our shop as well.