[9.4.0] Trying to transfer computers results in SQL Error

[muahdib69]

Describe the bug

I try to transfer various computers (even one fails) from the root entity to another entity.

Page(s) URL

When you execute the transfer.

To reproduce

Steps to reproduce the behavior:

1. Go to Computer list
2. Click on a couple of computers and add them to the transfer list
3. Select complete transfer mode, select the new entity, execute the transfer
4. You get the following error in the sql log

Expected behavior
the computers should have been moved to the new entity

Logs

[2019-02-27 12:05:48] glpisqllog.ERROR: DBmysql::query() in /var/www/glpi/inc/dbmysql.class.php line 177
  *** MySQL query error:
  SQL: DELETE FROM `glpi_documents_items` WHERE `glpi_items_devicecontrols`.`id` IS NULL LEFT JOIN `glpi_items_devicecontrols` ON (`glpi_documents_items`.`items_id` = `glpi_items_devicecontrols`.`id` AND `glpi_documents_items`.`itemtype` = 'Item_DeviceControl')
  Error: 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 'LEFT JOIN `glpi_items_devicecontrols` ON (`glpi_documents_items`.`items_id` = `g' at line 1
  Backtrace :
  inc/dbmysql.class.php:1043                         
  inc/transfer.class.php:931                         DBmysql->delete()
  inc/transfer.class.php:174                         Transfer->simulateTransfer()
  front/transfer.action.php:46                       Transfer->moveItems()
  {"user":"2@shmadsmhpglp00v","mem_usage":"0.000\", 3.07Mio)"} 

Screenshots

If applicable, add screenshots to help explain your problem.

Your GLPI setup (you can find it in Setup > General menu, System tab)

[code]   GLPI 9.4.0 (/glpi => /var/www/glpi) Installation mode: TARBALL
--

Operating system: Linux shmadsmhpglp00v 4.10.0-42-generic #46~16.04.1-Ubuntu SMP Mon Dec 4 15:57:59 UTC 2017 x86_64 PHP 7.0.32-0ubuntu0.16.04.1 apache2handler (Core, PDO, Phar, Reflection, SPL, SimpleXML, Zend OPcache, apache2handler, apcu, 	calendar, ctype, curl, date, dom, exif, fileinfo, filter, ftp, gd, gettext, hash, iconv, imap, json, ldap, libxml, mbstring, 	mysqli, mysqlnd, openssl, pcre, pdo_mysql, posix, readline, session, shmop, sockets, standard, sysvmsg, sysvsem, sysvshm, 	tokenizer, wddx, xml, xmlreader, xmlrpc, xmlwriter, xsl, zlib) Setup: max_execution_time="30" memory_limit="128M" post_max_size="8M" safe_mode="" session.save_handler="files" 	upload_max_filesize="2M"  Software: Apache/2.4.18 (Ubuntu) (Apache/2.4.18 (Ubuntu) Server at inventory.smyhotels.com Port 80) 	Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36 Server Software: (Ubuntu) 	Server Version: 5.7.25-0ubuntu0.16.04.2 	Server SQL Mode:  	Parameters: glpi@localhost/glpi 	Host info: Localhost via UNIX socket 	 mysqli extension is installed ctype extension is installed fileinfo extension is installed json extension is installed mbstring extension is installed iconv extension is installed zlib extension is installed curl extension is installed gd extension is installed simplexml extension is installed xml extension is installed ldap extension is installed imap extension is installed Zend OPcache extension is installed APCu extension is installed xmlrpc extension is installed CAS extension is installed Database version seems correct (5.7.25) - Perfect! /var/log/glpi : OK /etc/glpi/ : OK /var/lib/glpi : OK /var/lib/glpi/_dumps : OK /var/lib/glpi/_sessions : OK /var/lib/glpi/_cron : OK /var/lib/glpi/_graphs : OK /var/lib/glpi/_lock : OK /var/lib/glpi/_plugins : OK /var/lib/glpi/_tmp : OK /var/lib/glpi/_cache : OK /var/lib/glpi/_rss : OK /var/lib/glpi/_uploads : OK /var/lib/glpi/_pictures : OK

htmLawed version 1.2.4 in (/var/www/glpi/lib/htmlawed) phpmailer/phpmailer version 6.0.6 in (/var/www/glpi/vendor/phpmailer/phpmailer/src) simplepie/simplepie version 1.5.2 in (/var/www/glpi/vendor/simplepie/simplepie/library) tecnickcom/tcpdf version 6.2.26 in (/var/www/glpi/vendor/tecnickcom/tcpdf) michelf/php-markdown in (/var/www/glpi/vendor/michelf/php-markdown/Michelf) true/punycode in (/var/www/glpi/vendor/true/punycode/src) iamcal/lib_autolink in (/var/www/glpi/vendor/iamcal/lib_autolink) sabre/vobject in (/var/www/glpi/vendor/sabre/vobject/lib) zendframework/zend-cache in (/var/www/glpi/vendor/zendframework/zend-cache/src) zendframework/zend-console in (/var/www/glpi/vendor/zendframework/zend-console/src) zendframework/zend-i18n in (/var/www/glpi/vendor/zendframework/zend-i18n/src) zendframework/zend-serializer in (/var/www/glpi/vendor/zendframework/zend-serializer/src) monolog/monolog in (/var/www/glpi/vendor/monolog/monolog/src/Monolog) sebastian/diff in (/var/www/glpi/vendor/sebastian/diff/src) elvanto/litemoji in (/var/www/glpi/vendor/elvanto/litemoji/src) symfony/console in (/var/www/glpi/vendor/symfony/console) leafo/scssphp in (/var/www/glpi/vendor/leafo/scssphp/src) phpCas version 1.3.3 in (/usr/share/php)

Not active

Way of sending emails: PHP

 

fusioninventory      Name: FusionInventory                Version: 9.4.0+1.0  State: Enabled

Additional context

Add any other context about the problem here.

[Stoatwblr]

This looks like another subselect case where AS FOO needs to be appended

[trasher]

It does not seems to be a sub-query here.

[muahdib69]

I think the problem is that a WHERE CLAUSE was put before the JOIN.

... WHERE glpi_items_devicecontrols.id IS NULL LEFT JOIN glpi_items_devicecontrols ON (`glpi_ ...

I am pretty sure that is not legal SQL syntax. But I have no idea where this query is prepared

best

[cedric-anne]

Hi,

Can you test #5482 ?

Regards

[muahdib69]

Hello,

I'd love to try the patch, I'd like to download the patch as a unified diff file to apply it but I can't figure out how to do that in github. Can someone explain to me how I can get a unified diff file for this patch?

thanks

[cedric-anne]

Hi,

You can have a patch by adding .patch to URL of PR or commit. In this case: https://github.com/glpi-project/glpi/commit/4c9d46ce6653da2ea211704a2d8d2e1adc268683.patch

Regards

[muahdib69]

Thank you, downloaded and patched, but it still does not work.

the query now is constructed like:

delete FROM glpi_documents_items LEFT JOIN glpi_items_devicecontrols ON (glpi_documents_items.items_id = glpi_items_devicecontrols.id AND glpi_documents_items.itemtype = 'Item_DeviceControl') WHERE glpi_items_devicecontrols.id IS NULL;

but here it says:
https://stackoverflow.com/questions/652770/delete-with-join-in-mysql

that you have to construct the delete from with joins like that, specifying the table where you want data to be deleted, because the tables are no longer unambiguos:

delete glpi_documents_items FROM glpi_documents_items LEFT JOIN glpi_items_devicecontrols ON (glpi_documents_items.items_id = glpi_items_devicecontrols.id AND glpi_documents_items.itemtype = 'Item_DeviceControl') WHERE glpi_items_devicecontrols.id IS NULL;

[muahdib69]

If I execute the query with delete table from table left join it works:

mysql> delete glpi_documents_items FROM glpi_documents_items LEFT JOIN glpi_items_devicecontrols ON (glpi_documents_items.items_id = glpi_items_devicecontrols.id AND glpi_documents_items.itemtype = 'Item_DeviceControl') WHERE glpi_items_devicecontrols.id IS NULL;
Query OK, 0 rows affected (0.00 sec)

[muahdib69]

I changed the line 1214 in the file dbmysql.class.php to:

  $query  = "DELETE " . self::quoteName($table) . " FROM ". self::quoteName($table);

And now it works fine. I tested it also in mysql with a simple statement:

delete table from table;

and it works as expected.