From ee89ede59faab3809cc61b7773a99f3e24376fb8 Mon Sep 17 00:00:00 2001 From: Nathan Date: Wed, 2 Oct 2013 14:23:01 -0500 Subject: [ticket/11881] Timezone migration can take a long time PHPBB3-11881 --- phpBB/phpbb/db/migration/data/v310/timezone.php | 16 ++++++++++++++-- 1 file changed, 14 insertions(+), 2 deletions(-) (limited to 'phpBB/phpbb/db/migration') diff --git a/phpBB/phpbb/db/migration/data/v310/timezone.php b/phpBB/phpbb/db/migration/data/v310/timezone.php index dd0c6a2093..8443f7b492 100644 --- a/phpBB/phpbb/db/migration/data/v310/timezone.php +++ b/phpBB/phpbb/db/migration/data/v310/timezone.php @@ -39,16 +39,22 @@ class timezone extends \phpbb\db\migration\migration ); } - public function update_timezones() + public function update_timezones($start) { + $start = (int) $start; + $limit = 1; + $converted_timezones = 0; + // Update user timezones $sql = 'SELECT user_dst, user_timezone FROM ' . $this->table_prefix . 'users GROUP BY user_timezone, user_dst'; - $result = $this->db->sql_query($sql); + $result = $this->db->sql_query_limit($sql, $limit, $start); while ($row = $this->db->sql_fetchrow($result)) { + $converted_timezones++; + $sql = 'UPDATE ' . $this->table_prefix . "users SET user_timezone = '" . $this->db->sql_escape($this->convert_phpbb30_timezone($row['user_timezone'], $row['user_dst'])) . "' WHERE user_timezone = '" . $this->db->sql_escape($row['user_timezone']) . "' @@ -57,6 +63,12 @@ class timezone extends \phpbb\db\migration\migration } $this->db->sql_freeresult($result); + if ($converted_timezones == $limit) + { + // There are still more to convert + return $start + $limit; + } + // Update board default timezone $sql = 'UPDATE ' . $this->table_prefix . "config SET config_value = '" . $this->convert_phpbb30_timezone($this->config['board_timezone'], $this->config['board_dst']) . "' -- cgit v1.2.1 From 9653276ebcd1a5c24302cb8d06f905f90e158e45 Mon Sep 17 00:00:00 2001 From: Nathan Guse Date: Fri, 4 Oct 2013 12:10:27 -0500 Subject: [ticket/11881] Better split the timezone conversion into chunks; add test PHPBB3-11881 --- phpBB/phpbb/db/migration/data/v310/timezone.php | 32 +++++++++++++++---------- 1 file changed, 20 insertions(+), 12 deletions(-) (limited to 'phpBB/phpbb/db/migration') diff --git a/phpBB/phpbb/db/migration/data/v310/timezone.php b/phpBB/phpbb/db/migration/data/v310/timezone.php index 8443f7b492..13e2a5ceb6 100644 --- a/phpBB/phpbb/db/migration/data/v310/timezone.php +++ b/phpBB/phpbb/db/migration/data/v310/timezone.php @@ -42,28 +42,36 @@ class timezone extends \phpbb\db\migration\migration public function update_timezones($start) { $start = (int) $start; - $limit = 1; - $converted_timezones = 0; + $limit = 5000; + $converted = 0; - // Update user timezones - $sql = 'SELECT user_dst, user_timezone + $update_blocks = array(); + + $sql = 'SELECT user_id, user_timezone, user_dst FROM ' . $this->table_prefix . 'users - GROUP BY user_timezone, user_dst'; + ORDER BY user_id ASC'; $result = $this->db->sql_query_limit($sql, $limit, $start); - while ($row = $this->db->sql_fetchrow($result)) { - $converted_timezones++; + $converted++; + + $update_blocks[$row['user_timezone'] . ':' . $row['user_dst']][] = (int) $row['user_id']; + } + $this->db->sql_freeresult($result); + + // Update blocks of users who share the same timezone/dst + foreach ($update_blocks as $timezone => $user_ids) + { + $timezone = explode(':', $timezone); + $converted_timezone = $this->convert_phpbb30_timezone($timezone[0], $timezone[1]); $sql = 'UPDATE ' . $this->table_prefix . "users - SET user_timezone = '" . $this->db->sql_escape($this->convert_phpbb30_timezone($row['user_timezone'], $row['user_dst'])) . "' - WHERE user_timezone = '" . $this->db->sql_escape($row['user_timezone']) . "' - AND user_dst = " . (int) $row['user_dst']; + SET user_timezone = '" . $this->db->sql_escape($converted_timezone) . "' + WHERE " . $this->db->sql_in_set('user_id', $user_ids); $this->sql_query($sql); } - $this->db->sql_freeresult($result); - if ($converted_timezones == $limit) + if ($converted == $limit) { // There are still more to convert return $start + $limit; -- cgit v1.2.1 From cd7d29b90edcf292464beaf3ab814750703f3f17 Mon Sep 17 00:00:00 2001 From: Nathan Guse Date: Fri, 4 Oct 2013 15:04:08 -0500 Subject: [ticket/11881] Make sure user_timezone isn't converted twice PHPBB3-11881 --- phpBB/phpbb/db/migration/data/v310/timezone.php | 7 ++++++- 1 file changed, 6 insertions(+), 1 deletion(-) (limited to 'phpBB/phpbb/db/migration') diff --git a/phpBB/phpbb/db/migration/data/v310/timezone.php b/phpBB/phpbb/db/migration/data/v310/timezone.php index 13e2a5ceb6..6106e179cb 100644 --- a/phpBB/phpbb/db/migration/data/v310/timezone.php +++ b/phpBB/phpbb/db/migration/data/v310/timezone.php @@ -55,7 +55,12 @@ class timezone extends \phpbb\db\migration\migration { $converted++; - $update_blocks[$row['user_timezone'] . ':' . $row['user_dst']][] = (int) $row['user_id']; + // In case this is somehow run twice on a row. + // Otherwise it would just end up as UTC on the second run + if (is_numeric($row['user_timezone'])) + { + $update_blocks[$row['user_timezone'] . ':' . $row['user_dst']][] = (int) $row['user_id']; + } } $this->db->sql_freeresult($result); -- cgit v1.2.1 From efe130bcfc84577294069641621f93f0d9801d99 Mon Sep 17 00:00:00 2001 From: Nathan Guse Date: Fri, 4 Oct 2013 15:04:30 -0500 Subject: [ticket/11881] Limit to 500 PHPBB3-11881 --- phpBB/phpbb/db/migration/data/v310/timezone.php | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'phpBB/phpbb/db/migration') diff --git a/phpBB/phpbb/db/migration/data/v310/timezone.php b/phpBB/phpbb/db/migration/data/v310/timezone.php index 6106e179cb..61f8dc488e 100644 --- a/phpBB/phpbb/db/migration/data/v310/timezone.php +++ b/phpBB/phpbb/db/migration/data/v310/timezone.php @@ -42,7 +42,7 @@ class timezone extends \phpbb\db\migration\migration public function update_timezones($start) { $start = (int) $start; - $limit = 5000; + $limit = 500; $converted = 0; $update_blocks = array(); -- cgit v1.2.1