From 3e48d2eee1d2af42aa777fd5e461fa570762732e Mon Sep 17 00:00:00 2001 From: alecpl Date: Fri, 21 Nov 2008 18:19:30 +0000 Subject: - Increase speed of session destroy and garbage clean up - Fix session timeout when DB server got clock skew (#1485490) --- CHANGELOG | 2 ++ SQL/mssql.initial.sql | 3 ++ SQL/mysql.initial.sql | 3 +- SQL/mysql.update.sql | 3 ++ SQL/mysql5.initial.sql | 3 +- SQL/postgres.initial.sql | 1 + SQL/postgres.update.sql | 6 ++-- SQL/sqlite.initial.sql | 1 + SQL/sqlite.update.sql | 4 +++ program/include/rcube_imap.php | 6 ++-- program/include/session.inc | 64 ++++++++++++++++++++++-------------------- 11 files changed, 58 insertions(+), 38 deletions(-) diff --git a/CHANGELOG b/CHANGELOG index de8b879fc..c5e14ed60 100644 --- a/CHANGELOG +++ b/CHANGELOG @@ -4,6 +4,8 @@ CHANGELOG RoundCube Webmail 2008/11/21 (alec) ---------- - Fix 'cache' table cleanup on session destroy (#1485516) +- Increase speed of session destroy and garbage clean up +- Fix session timeout when DB server got clock skew (#1485490) 2008/11/19 (alec) ---------- diff --git a/SQL/mssql.initial.sql b/SQL/mssql.initial.sql index f3b5d5ca7..fc60a4d8d 100644 --- a/SQL/mssql.initial.sql +++ b/SQL/mssql.initial.sql @@ -196,6 +196,9 @@ ALTER TABLE [dbo].[session] ADD CONSTRAINT [DF_session_ip] DEFAULT ('') FOR [ip] GO + CREATE INDEX [IX_session_changed] ON [dbo].[session]([changed]) ON [PRIMARY] +GO + ALTER TABLE [dbo].[users] ADD CONSTRAINT [DF_users_username] DEFAULT ('') FOR [username], CONSTRAINT [DF_users_mail_host] DEFAULT ('') FOR [mail_host], diff --git a/SQL/mysql.initial.sql b/SQL/mysql.initial.sql index c71894257..07f127cce 100644 --- a/SQL/mysql.initial.sql +++ b/SQL/mysql.initial.sql @@ -73,7 +73,8 @@ CREATE TABLE `session` ( `changed` datetime NOT NULL default '0000-00-00 00:00:00', `ip` VARCHAR(40) NOT NULL default '', `vars` text NOT NULL, - PRIMARY KEY (`sess_id`) + PRIMARY KEY (`sess_id`), + INDEX `changed_index` (`changed`) ); -- -------------------------------------------------------- diff --git a/SQL/mysql.update.sql b/SQL/mysql.update.sql index f88a6c4db..a45ace1fd 100644 --- a/SQL/mysql.update.sql +++ b/SQL/mysql.update.sql @@ -38,3 +38,6 @@ ALTER TABLE `cache` REFERENCES `session`(`sess_id`) ON DELETE CASCADE ON UPDATE CASCADE; + +ALTER TABLE `session` + ADD INDEX `changed_index` (`changed`); diff --git a/SQL/mysql5.initial.sql b/SQL/mysql5.initial.sql index e01f2a6b1..7d11288e3 100644 --- a/SQL/mysql5.initial.sql +++ b/SQL/mysql5.initial.sql @@ -14,7 +14,8 @@ CREATE TABLE `session` ( `changed` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `ip` varchar(40) NOT NULL, `vars` text NOT NULL, - PRIMARY KEY(`sess_id`) + PRIMARY KEY(`sess_id`), + INDEX `changed_index` (`changed`) ) TYPE=INNODB CHARACTER SET utf8 COLLATE utf8_general_ci; diff --git a/SQL/postgres.initial.sql b/SQL/postgres.initial.sql index e53caa187..128b484d7 100644 --- a/SQL/postgres.initial.sql +++ b/SQL/postgres.initial.sql @@ -42,6 +42,7 @@ CREATE TABLE "session" ( vars text NOT NULL ); +CREATE INDEX session_changed_idx ON session (changed); -- diff --git a/SQL/postgres.update.sql b/SQL/postgres.update.sql index fdd7f0f5e..f9cd431b6 100644 --- a/SQL/postgres.update.sql +++ b/SQL/postgres.update.sql @@ -25,5 +25,7 @@ CREATE INDEX messages_created_idx ON messages (created); -- Updates from version 0.2-beta ALTER TABLE cache DROP CONSTRAINT cache_session_id_fkey; -ALTER TABLE cache ADD FOREIGN KEY (session_id) REFERENCES session(sess_id) ON DELETE CASCADE ON UPDATE CASCADE; -CREATE INDEX cache_session_id_idx ON "cache" (session_id); +ALTER TABLE cache ADD FOREIGN KEY (session_id) REFERENCES session (sess_id) ON DELETE CASCADE ON UPDATE CASCADE; + +CREATE INDEX cache_session_id_idx ON cache (session_id); +CREATE INDEX session_changed_idx ON session (changed); diff --git a/SQL/sqlite.initial.sql b/SQL/sqlite.initial.sql index 4b2c30faa..d09539d6f 100644 --- a/SQL/sqlite.initial.sql +++ b/SQL/sqlite.initial.sql @@ -97,6 +97,7 @@ CREATE TABLE session ( vars text NOT NULL ); +CREATE INDEX ix_session_changed ON session (changed); -- -------------------------------------------------------- diff --git a/SQL/sqlite.update.sql b/SQL/sqlite.update.sql index accf64a73..26dc00f4f 100644 --- a/SQL/sqlite.update.sql +++ b/SQL/sqlite.update.sql @@ -29,3 +29,7 @@ CREATE INDEX ix_users_alias ON users(alias); -- Updates from version 0.2-alpha CREATE INDEX ix_messages_created ON messages (created); + +-- Updates from version 0.2-beta + +CREATE INDEX ix_session_changed ON session (changed); diff --git a/program/include/rcube_imap.php b/program/include/rcube_imap.php index d8845cc74..c3d599180 100644 --- a/program/include/rcube_imap.php +++ b/program/include/rcube_imap.php @@ -2146,7 +2146,7 @@ class rcube_imap { $this->db->query( "UPDATE ".get_table_name('cache')." - SET created=".$this->db->now().", data=?, session_id=? + SET created=". $this->db->fromunixtime(time()).", data=?, session_id=? WHERE user_id=? AND cache_key=?", $data, @@ -2160,7 +2160,7 @@ class rcube_imap $this->db->query( "INSERT INTO ".get_table_name('cache')." (created, user_id, cache_key, data, session_id) - VALUES (".$this->db->now().", ?, ?, ?, ?)", + VALUES (".$this->db->fromunixtime(time()).", ?, ?, ?, ?)", $_SESSION['user_id'], $key, $data, @@ -2374,7 +2374,7 @@ class rcube_imap $this->db->query( "INSERT INTO ".get_table_name('messages')." (user_id, del, cache_key, created, idx, uid, subject, ".$this->db->quoteIdentifier('from').", ".$this->db->quoteIdentifier('to').", cc, date, size, headers, structure) - VALUES (?, 0, ?, ".$this->db->now().", ?, ?, ?, ?, ?, ?, ".$this->db->fromunixtime($headers->timestamp).", ?, ?, ?)", + VALUES (?, 0, ?, ".$this->db->fromunixtime(time()).", ?, ?, ?, ?, ?, ?, ".$this->db->fromunixtime($headers->timestamp).", ?, ?, ?)", $_SESSION['user_id'], $key, $index, diff --git a/program/include/session.inc b/program/include/session.inc index d6486a43a..21d22ab28 100644 --- a/program/include/session.inc +++ b/program/include/session.inc @@ -75,10 +75,12 @@ function rcube_sess_write($key, $vars) WHERE sess_id=?", $key); + $now = $DB->fromunixtime(time()); + if ($DB->num_rows($sql_result)) { $DB->query( "UPDATE " . get_table_name('session') . " - SET vars=?, changed=" . $DB->now() . " + SET vars=?, changed= " . $now . " WHERE sess_id=?", $vars, $key); @@ -87,7 +89,7 @@ function rcube_sess_write($key, $vars) $DB->query( "INSERT INTO " . get_table_name('session') . " (sess_id, vars, ip, created, changed) - VALUES (?, ?, ?, ".$DB->now().", ".$DB->now().")", + VALUES (?, ?, ?, " . $now . ", " . $now .")", $key, $vars, (string)$_SERVER['REMOTE_ADDR']); @@ -107,12 +109,13 @@ function rcube_sess_destroy($key) return false; } + $DB->query("DELETE FROM " . get_table_name('session') . " WHERE sess_id=?", $key); + // delete session entries in cache table - if ($rcmail->config->get('enable_caching')) { + // on databases wthout foreign keys + if ($rcmail->config->get('enable_caching') && $DB->db_provider != 'pgsql') { $DB->query("DELETE FROM " . get_table_name('cache') . " WHERE session_id=?", $key); } - - $DB->query("DELETE FROM " . get_table_name('session') . " WHERE sess_id=?", $key); return true; } @@ -128,40 +131,39 @@ function rcube_sess_gc($maxlifetime) return false; } + $now = $DB->fromunixtime(time() - $maxlifetime); + + // delete session entries in cache table if ($rcmail->config->get('enable_caching')) { - // get all expired sessions - $sql_result = $DB->query( - "SELECT sess_id - FROM " . get_table_name('session') . " - WHERE " . $DB->unixtimestamp($DB->now())."-".$DB->unixtimestamp('changed') . " > ?", - $maxlifetime); - - $exp_sessions = array(); - while ($sql_arr = $DB->fetch_assoc($sql_result)) { - $exp_sessions[] = $sql_arr['sess_id']; - } - if (sizeof($exp_sessions)) { - $exp_sessions = "'" . join("','", $exp_sessions) . "'"; - // delete session cache records - $DB->query("DELETE FROM " . get_table_name('cache') . " - WHERE session_id IN (" . $exp_sessions . ")"); + // on databases wthout foreign keys... + if($DB->db_provider != 'pgsql') { - // delete session records - $DB->query("DELETE FROM " . get_table_name('session') . " - WHERE sess_id IN (" . $exp_sessions . ")"); - } + // get all expired sessions + $sql_result = $DB->query( + "SELECT sess_id FROM " . get_table_name('session') . " + WHERE changed < " . $now); + $exp_sessions = array(); + while ($sql_arr = $DB->fetch_assoc($sql_result)) { + $exp_sessions[] = $sql_arr['sess_id']; + } + + if (sizeof($exp_sessions)) { + $exp_sessions = "'" . join("','", $exp_sessions) . "'"; + // delete session cache records + $DB->query("DELETE FROM " . get_table_name('cache') . " + WHERE session_id IN (" . $exp_sessions . ")"); + } + } // also run message cache GC rcmail_message_cache_gc(); - - } else { - // just delete all expired sessions - $DB->query("DELETE FROM " . get_table_name('session') . " - WHERE " . $DB->unixtimestamp($DB->now())."-".$DB->unixtimestamp('changed') . " > ?", - $maxlifetime); } + // just delete all expired sessions + $DB->query("DELETE FROM " . get_table_name('session') . " + WHERE changed < " . $now); + rcmail_temp_gc(); return true; -- cgit v1.2.3