{"action":"closed","issue":{"url":"https://api.github.com/repos/owncloud/music/issues/358","labels_url":"https://api.github.com/repos/owncloud/music/issues/358/labels{/name}","comments_url":"https://api.github.com/repos/owncloud/music/issues/358/comments","events_url":"https://api.github.com/repos/owncloud/music/issues/358/events","html_url":"https://github.com/owncloud/music/issues/358","id":64292134,"number":358,"title":"SQLSTATE[HY000]: General error: 1093 You can't specify target table 'oc_music_albums' for update in FROM clause","user":{"login":"hmuus","id":6908165,"avatar_url":"https://avatars.githubusercontent.com/u/6908165?v=3","gravatar_id":"","url":"https://api.github.com/users/hmuus","html_url":"https://github.com/hmuus","followers_url":"https://api.github.com/users/hmuus/followers","following_url":"https://api.github.com/users/hmuus/following{/other_user}","gists_url":"https://api.github.com/users/hmuus/gists{/gist_id}","starred_url":"https://api.github.com/users/hmuus/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hmuus/subscriptions","organizations_url":"https://api.github.com/users/hmuus/orgs","repos_url":"https://api.github.com/users/hmuus/repos","events_url":"https://api.github.com/users/hmuus/events{/privacy}","received_events_url":"https://api.github.com/users/hmuus/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/owncloud/music/labels/4%20-%20To%20Review","name":"4 - To Review","color":"CCCCCC"},{"url":"https://api.github.com/repos/owncloud/music/labels/Bug","name":"Bug","color":"fc2929"}],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":5,"created_at":"2015-03-25T15:03:22Z","updated_at":"2015-07-10T13:00:21Z","closed_at":"2015-07-10T13:00:21Z","body":"### Steps to reproduce\r\n1. install music 0.3.5\r\n\r\n### Expected behaviour\r\ncron cycle should not deliver errors\r\n\r\n### Actual behaviour\r\neach cron cycle throws error\r\n\r\n### Server configuration\r\n**Operating system**: Debian 7 latest, all updates\r\n\r\n**Web server:** apache\r\n\r\n**Database:** mysql\r\n\r\n**PHP version:** 5 latest\r\n\r\n**ownCloud version:** 8 latest\r\n\r\n**Updated from an older ownCloud or fresh install:** updated from 7 latest\r\n\r\n**List of activated apps:** standard plus music\r\n\r\n**The content of config/config.php:**\r\n\r\n```\r\n<?php\r\n$CONFIG = array (\r\n  'instanceid' => 'instance id here',\r\n  'passwordsalt' => 'salt here',\r\n  'secret' => 'secret here',\r\n  'trusted_domains' => \r\n  array (\r\n    0 => 'full domain hostname here',\r\n  ),\r\n  'datadirectory' => '/var/www/path here/data',\r\n  'overwrite.cli.url' => 'https://full domain hostname here/path here',\r\n  'dbtype' => 'mysql',\r\n  'version' => '8.0.2.0',\r\n  'installed' => true,\r\n  'forcessl' => true,\r\n  'forceSSLforSubdomains' => true,\r\n  'mail_from_address' => 'sender name here',\r\n  'mail_smtpmode' => 'smtp',\r\n  'mail_domain' => 'domain name here',\r\n  'theme' => '',\r\n  'maintenance' => false,\r\n  'dbname' => 'dbname here',\r\n  'dbhost' => '127.0.0.1',\r\n  'dbuser' => 'user with root access here',\r\n  'dbpassword' => 'password here',\r\n  'mail_smtphost' => 'ip here',\r\n  'ldapIgnoreNamingRules' => false,\r\n);\r\n```\r\n\r\n**Are you using external storage, if yes which one:** local\r\n\r\n**Are you using encryption:** no\r\n\r\n**Are you using an external user-backend, if yes which one:** none\r\n\r\n### Client configuration\r\n**Browser:** various\r\n\r\n**Operating system:** various\r\n\r\n### Logs\r\n\r\n#### ownCloud log (data/owncloud.log)\r\n```\r\nsnip start SQLSTATE[HY000]: General error: 1093 You can't specify target table 'oc_music_albums' for update in FROM clause snip end\r\n```\r\n\r\n### Reason\r\n\r\napps/music/utility/helper.php sql statements throw errors each cron cycle:\r\n\r\nSQLSTATE[HY000]: General error: 1093 You can't specify target table 'oc_music_albums' for update in FROM clause\r\n\r\ndue to contained SQL commands\r\n'UPDATE `*PREFIX*music_albums` SET `cover_file_id` = NULL WHERE `cover_file_id` IS NOT NULL AND `cover_file_id` IN (SELECT `cover_file_id` FROM `*PREFIX*music_albums` LEFT JOIN `*PREFIX*filecache` ON `cover_file_id`=`fileid` WHERE `fileid` IS NULL);'\r\nand\r\n'DELETE FROM `*PREFIX*music_tracks` WHERE `file_id` IN (SELECT `file_id` FROM `*PREFIX*music_tracks` LEFT JOIN `*PREFIX*filecache` ON `file_id`=`fileid` WHERE `fileid` IS NULL);'\r\n\r\n### Proposal\r\n\r\n replace by\r\n\r\n```\r\n<?php\r\n\r\n/**\r\n * ownCloud - Music app\r\n *\r\n * This file is licensed under the Affero General Public License version 3 or\r\n * later. See the COPYING file.\r\n *\r\n * @author Morris Jobke <hey@morrisjobke.de>\r\n * @copyright Morris Jobke 2014\r\n *\r\n * update 25.March 2015\r\n * hmuus\r\n */\r\n\r\nnamespace OCA\\Music\\Utility;\r\n\r\nuse \\OCA\\Music\\AppFramework\\Core\\Db;\r\n\r\n\r\nclass Helper {\r\n\r\n\tprivate $db;\r\n\r\n\tpublic function __construct(Db $db){\r\n\t\t$this->db = $db;\r\n\t}\r\n\r\n\t/**\r\n\t * Removes orphaned data from the database\r\n\t */\r\n\tpublic function cleanUp() {\r\n\t\t$sqls = array(\r\n/*SQLSTATE[HY000]: General error: 1093 You can't specify target table for update in FROM clause\r\n\t\t\t'UPDATE `*PREFIX*music_albums` SET `cover_file_id` = NULL WHERE `cover_file_id` IS NOT NULL AND `cover_file_id` IN (SELECT `cover_file_id` FROM `*PREFIX*music_albums` LEFT JOIN `*PREFIX*filecache` ON `cover_file_id`=`fileid` WHERE `fileid` IS NULL);',\r\n*/\t\t\t'CREATE TABLE IF NOT EXISTS `*PREFIX*music_albums_cleanup` AS \r\nSELECT `cover_file_id` FROM `*PREFIX*music_albums` LEFT JOIN `*PREFIX*filecache` ON `cover_file_id`=`fileid` WHERE `fileid` IS NULL;',\r\n\t\t\t'UPDATE `*PREFIX*music_albums` SET `cover_file_id` = NULL WHERE `cover_file_id` IS NOT NULL AND `cover_file_id` IN (SELECT `cover_file_id` FROM `*PREFIX*music_albums_cleanup`);',\r\n\t\t\t'DROP TABLE `*PREFIX*music_albums_cleanup`;',\r\n/*SQLSTATE[HY000]: General error: 1093 You can't specify target table for update in FROM clause\r\n\t\t\t'DELETE FROM `*PREFIX*music_tracks` WHERE `file_id` IN (SELECT `file_id` FROM `*PREFIX*music_tracks` LEFT JOIN `*PREFIX*filecache` ON `file_id`=`fileid` WHERE `fileid` IS NULL);',\r\n*/\t\t\t'CREATE TABLE IF NOT EXISTS `*PREFIX*music_tracks_cleanup` AS \r\nSELECT `file_id` FROM `*PREFIX*music_tracks` LEFT JOIN `*PREFIX*filecache` ON `file_id`=`fileid` WHERE `fileid` IS NULL;',\r\n\t\t\t'DELETE FROM `*PREFIX*music_tracks` WHERE `file_id` IN (SELECT `file_id` FROM `*PREFIX*music_tracks_cleanup`);',\r\n\t\t\t'DROP TABLE `*PREFIX*music_tracks_cleanup`;',\r\n\t\t\t'DELETE FROM `*PREFIX*music_albums` WHERE `id` NOT IN (SELECT `album_id` FROM `*PREFIX*music_tracks` GROUP BY `album_id`);',\r\n\t\t\t'DELETE FROM `*PREFIX*music_album_artists` WHERE `album_id` NOT IN (SELECT `id` FROM `*PREFIX*music_albums` GROUP BY `id`);',\r\n\t\t\t'DELETE FROM `*PREFIX*music_artists` WHERE `id` NOT IN (SELECT `artist_id` FROM `*PREFIX*music_album_artists` GROUP BY `artist_id`);'\r\n\t\t);\r\n\r\n\t\tforeach ($sqls as $sql) {\r\n\t\t\t$query = $this->db->prepareQuery($sql);\r\n\t\t\t$query->execute();\r\n\t\t}\r\n\t}\r\n}\r\n```\r\n\r\n### Afterwards behaviour\r\nNo more likely errors in log\r\n"}}