From 45f39c6d1f2adc318d521bd9eb75d80f0750fdb8 Mon Sep 17 00:00:00 2001 From: Joas Schilling Date: Sat, 18 Feb 2012 12:16:21 +0100 Subject: [ticket/10605] Delete orphan private messages on update PHPBB3-10605 --- phpBB/install/database_update.php | 42 +++++++++++++++++++++++++++++++++++++++ 1 file changed, 42 insertions(+) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index a1b7dcd47f..b6298ca651 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -2024,6 +2024,48 @@ function change_database_data(&$no_updates, $version) // No changes from 3.0.10-RC3 to 3.0.10 case '3.0.10-RC3': break; + + // Changes from 3.0.10 to 3.0.11-RC1 + case '3.0.10': + // Delete orphan private messages + $batch_size = 500; + + $sql_array = array( + 'SELECT' => 'p.msg_id', + 'FROM' => array( + PRIVMSGS_TABLE => 'p', + ), + 'LEFT_JOIN' => array( + array( + 'FROM' => array(PRIVMSGS_TO_TABLE, 't'), + 'ON' => 'p.msg_id = t.msg_id', + ), + ), + 'WHERE' => 't.user_id IS NULL'; + $sql = $db->sql_build_query('SELECT', $sql_array); + + do + { + $result = $db->sql_query_limit($sql, $batch_size); + + $delete_pms = array(); + while ($row = $db->sql_fetchrow($result)) + { + $delete_pms[] = (int) $row['msg_id']; + } + $db->sql_freeresult($result); + + if (!empty($delete_pms)) + { + $sql = 'DELETE FROM ' . PRIVMSGS_TABLE . ' + WHERE ' . $db->sql_in_set('msg_id', $delete_pms); + $db->sql_query($sql); + } + } + while (sizeof($delete_pms) == $batch_size); + + $no_updates = false; + break; } } -- cgit v1.2.1 From 03e382f99c61c12655998ac8bd798fca7a34c099 Mon Sep 17 00:00:00 2001 From: Oleg Pudeyev Date: Thu, 5 Apr 2012 17:34:37 -0400 Subject: [ticket/10605] Fix syntax error in database updater. PHPBB3-10605 --- phpBB/install/database_update.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index b6298ca651..6088a58fc4 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -2041,7 +2041,7 @@ function change_database_data(&$no_updates, $version) 'ON' => 'p.msg_id = t.msg_id', ), ), - 'WHERE' => 't.user_id IS NULL'; + 'WHERE' => 't.user_id IS NULL'); $sql = $db->sql_build_query('SELECT', $sql_array); do -- cgit v1.2.1 From de1e343c5b408da09448b3150cfec2de75afd04a Mon Sep 17 00:00:00 2001 From: Oleg Pudeyev Date: Thu, 5 Apr 2012 17:34:54 -0400 Subject: [ticket/10605] Add a section for updating from 3.0.10 to schema updates. Without it data updates do not appear to be run. PHPBB3-10605 --- phpBB/install/database_update.php | 2 ++ 1 file changed, 2 insertions(+) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 6088a58fc4..116e6ea9a6 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -993,6 +993,8 @@ function database_update_info() '3.0.10-RC2' => array(), // No changes from 3.0.10-RC3 to 3.0.10 '3.0.10-RC3' => array(), + // No changes from 3.0.10 to 3.0.11-RC1 + '3.0.10' => array(), /** @todo DROP LOGIN_ATTEMPT_TABLE.attempt_id in 3.0.11-RC1 */ ); -- cgit v1.2.1 From 2b2286a46c56b8a9e5444fd06eff263f880e0c78 Mon Sep 17 00:00:00 2001 From: Oleg Pudeyev Date: Thu, 5 Apr 2012 17:35:27 -0400 Subject: [ticket/10605] Fix left join usage. Previously this produced broken SQL: SELECT p.msg_id FROM phpbb_privmsgs p LEFT JOIN 0 phpbb_privmsgs_to ON (p.msg_id = t.msg_id) WHERE t.user_id IS NULL LIMIT 500 OFFSET 0 PHPBB3-10605 --- phpBB/install/database_update.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 116e6ea9a6..0737061887 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -2039,7 +2039,7 @@ function change_database_data(&$no_updates, $version) ), 'LEFT_JOIN' => array( array( - 'FROM' => array(PRIVMSGS_TO_TABLE, 't'), + 'FROM' => array(PRIVMSGS_TO_TABLE => 't'), 'ON' => 'p.msg_id = t.msg_id', ), ), -- cgit v1.2.1 From f71a9d369c84ec938408a2134b09ea259f6cbbab Mon Sep 17 00:00:00 2001 From: Andreas Fischer Date: Mon, 14 May 2012 00:34:42 +0200 Subject: [ticket/10605] Put end of array on its own line because start of array is too. PHPBB3-10605 --- phpBB/install/database_update.php | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 0737061887..2a361aae26 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -2043,7 +2043,8 @@ function change_database_data(&$no_updates, $version) 'ON' => 'p.msg_id = t.msg_id', ), ), - 'WHERE' => 't.user_id IS NULL'); + 'WHERE' => 't.user_id IS NULL', + ); $sql = $db->sql_build_query('SELECT', $sql_array); do -- cgit v1.2.1 From 95e1d4e9db8d2174c8ee5c6bb35be06abc57e3cf Mon Sep 17 00:00:00 2001 From: Andreas Fischer Date: Mon, 14 May 2012 00:36:18 +0200 Subject: [ticket/10605] Use database updater function _sql() instead of $db->sql_query() PHPBB3-10605 --- phpBB/install/database_update.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'phpBB/install/database_update.php') diff --git a/phpBB/install/database_update.php b/phpBB/install/database_update.php index 2a361aae26..95a0282878 100644 --- a/phpBB/install/database_update.php +++ b/phpBB/install/database_update.php @@ -2062,7 +2062,7 @@ function change_database_data(&$no_updates, $version) { $sql = 'DELETE FROM ' . PRIVMSGS_TABLE . ' WHERE ' . $db->sql_in_set('msg_id', $delete_pms); - $db->sql_query($sql); + _sql($sql, $errored, $error_ary); } } while (sizeof($delete_pms) == $batch_size); -- cgit v1.2.1