diff options
| author | Florian Pritz <bluewind@xinu.at> | 2016-02-26 22:56:30 +0100 |
|---|---|---|
| committer | Florian Pritz <bluewind@xinu.at> | 2016-02-26 22:56:30 +0100 |
| commit | e061538e2092a3c95ea416fbfc00a6e045bffdcf (patch) | |
| tree | 250b187bbd89fd44fbccc2c4e8f8af36ad304899 | |
| parent | 1133e2c31ae03ddd9e37d86b36ef3c7fc763d050 (diff) | |
Fix MYSQL error in migration 14/15
Error was: You can't specify target table 'testsuite_prefix_file_storage' for update in FROM clause
The new code is ported from the existing postgres migration.
Signed-off-by: Florian Pritz <bluewind@xinu.at>
| -rw-r--r-- | application/migrations/014_deduplicate_file_storage.php | 20 | ||||
| -rw-r--r-- | application/migrations/015_actually_deduplicate_file_storage.php | 20 |
2 files changed, 16 insertions, 24 deletions
diff --git a/application/migrations/014_deduplicate_file_storage.php b/application/migrations/014_deduplicate_file_storage.php index 96c041c27..bd96c56d8 100644 --- a/application/migrations/014_deduplicate_file_storage.php +++ b/application/migrations/014_deduplicate_file_storage.php @@ -81,18 +81,14 @@ class Migration_deduplicate_file_storage extends CI_Migration { // XXX: This query also exists in migration 15 $this->db->query(' - DELETE `'.$prefix.'file_storage` - FROM `'.$prefix.'file_storage` - LEFT OUTER JOIN `'.$prefix.'files` ON `'.$prefix.'files`.file_storage_id = `'.$prefix.'file_storage`.id - WHERE `'.$prefix.'file_storage`.id NOT IN ( - SELECT min(x.id) - FROM ( - SELECT fs.id, fs.hash - FROM `'.$prefix.'file_storage` fs - ) x - GROUP BY x.hash - ) - AND `'.$prefix.'files`.id IS NULL + DELETE fs + FROM `'.$prefix.'file_storage` fs, `'.$prefix.'file_storage` fs2 + WHERE fs.hash = fs2.hash + AND fs.id > fs2.id + AND fs.id NOT IN ( + SELECT file_storage_id + FROM `'.$prefix.'files` f + ); '); } diff --git a/application/migrations/015_actually_deduplicate_file_storage.php b/application/migrations/015_actually_deduplicate_file_storage.php index 267afc1fe..76ff2d6b9 100644 --- a/application/migrations/015_actually_deduplicate_file_storage.php +++ b/application/migrations/015_actually_deduplicate_file_storage.php @@ -12,18 +12,14 @@ class Migration_actually_deduplicate_file_storage extends CI_Migration { } else { // XXX: This query also exists in migration 14 $this->db->query(' - DELETE `'.$prefix.'file_storage` - FROM `'.$prefix.'file_storage` - LEFT OUTER JOIN `'.$prefix.'files` ON `'.$prefix.'files`.file_storage_id = `'.$prefix.'file_storage`.id - WHERE `'.$prefix.'file_storage`.id NOT IN ( - SELECT min(x.id) - FROM ( - SELECT fs.id, fs.hash - FROM `'.$prefix.'file_storage` fs - ) x - GROUP BY x.hash - ) - AND `'.$prefix.'files`.id IS NULL + DELETE fs + FROM `'.$prefix.'file_storage` fs, `'.$prefix.'file_storage` fs2 + WHERE fs.hash = fs2.hash + AND fs.id > fs2.id + AND fs.id NOT IN ( + SELECT file_storage_id + FROM `'.$prefix.'files` f + ); '); } |