Session logout fix with CodeIgniter framework when dealing with ajax requests and iframes in SPA applications

Recently, we had an issue in our application which is a single page application(SPA). We have all the functionality in a single page and most of the data and view changes happen through ajax requests and iframe page displays.

We are using CodeIgniter framework for this application. So sometimes what happened was the user was logged out while accessing the application and this occurred once a while for 1 or 2 users, not a reproducible case one would say.

Before starting on the case, I would like to specify the steps which CodeIgniter takes for checking session for any request.

Steps:
1. Read session cookie sent from browser.
2. Read session cookie process proceeds with below step checks (If any one step fails below, it results in create one session cookie)
2a. Checks if session cookie is present
2b. Checks if it is a valid session cookie by using it's decryption algorithm check
2c. If  valid session, then check if it is expired
2d. Check for config related options if enabled like check ip, check useragent etc.
2e. If database enabled then check session is present in database

3. If a valid cookie is found, then check if it needs update since its last activity update which is determined by below condition, update database if enabled with updated cookie and send the updated cookie (even if not updated in database) to browser.

($this->userdata['last_activity'] + $this->sess_time_to_update) < $this->now

The problem which occurs is related to step 3 when update of session cookie happens.

There are two versions of CodeIgniter I would like to discuss here for step 3:
1. CodeIgniter Version 2.2.0: Session update happens for all requests that is page reloads and ajax requests.
2. CodeIgniter Version 2.2.1 and above: Session update happens only for page reload requests and not for ajax requests.

So for case 1, if the issue would occur if there are many ajax requests happening from browser at same time and for case 2, it would occur if you are making multiple iframe requests at same time from SPA application.

Note this issue happens only if there are simultaneous requests happening related to respective versions as described above and the server detects a session cookie update case. Given that these are the two conditions which need to be satisfied for the issue to occur, it happens sometimes(very rarely).

Request flow and session cookie setting process flow

Explanation:

Let me explain the issue. I will be using the term requests instead of the respective detailed requests for respective CodeIgniter versions.

So lets say in our SPA application, "A" is the session cookie value stored in the browser and two requests R1 and R2 happen at same time (both will have cookie request value "A") and reach the CodeIgniter server at same time.

Note the session value in database is "A" which must match browser cookie one.

Lets say R1 is first processed by the server. So server checks it is a valid cookie and detects that it needs an update. So it generates a random cookie "B" and updates database session where old session is A. So in database, new value is now "B" and then sends updated cookie value to browser. So now the cookie value in browser is updated from "A" to "B".

After that R2 is then being processed by the server. The request still contains the old cookie session value "A" and same old last updated time. So server detects that it needs an update and generates a random session value "C" and tries to update the value in database where old_value is "A", which is not present any where. So the update does not occur, but it nevertheless sends the update cookie data with "C" to the browser. So now the cookie value in browser is updated from "B" to "C".

So now when you make any new request from the SPA application, it will use cookie session value "C" and while trying to read the session from database, it won't find the value in database(since the update had not been done to "C" and the current value in database is "B") and will destroy your session.

Solution:
The main issue here is with the second request R2, which could not update the value in database but sent an updated cookie value to browser which it should not. So this can be solved by checking if any rows were affected in database and then only sent updated cookie session data to browser. So in this case generated random value "C" would not be sent to browser and new requests would use "B" value which is still present in the browser.

Original code in sess_update() function in Session library:

// Update the session ID and last_activity field in the DB if needed
if ($this->sess_use_database === TRUE)
{
    // set cookie explicitly to only have our session data
    $cookie_data = array();
    foreach (array('session_id','ip_address','user_agent','last_activity') as $val)
    {
        $cookie_data[$val] = $this->userdata[$val];
    }

    $this->CI->db->query($this->CI->db->update_string($this->sess_table_name, array('last_activity' => $this->now, 'session_id' => $new_sessid), array('session_id' => $old_sessid)));
}

// Write the cookie
$this->_set_cookie($cookie_data);

Modified fixed code:

// Update the session ID and last_activity field in the DB if needed
if ($this->sess_use_database === TRUE)
{
    // set cookie explicitly to only have our session data
    $cookie_data = array();
    foreach (array('session_id','ip_address','user_agent','last_activity') as $val)
    {
        $cookie_data[$val] = $this->userdata[$val];
    }

    $this->CI->db->query($this->CI->db->update_string($this->sess_table_name, array('last_activity' => $this->now, 'session_id' => $new_sessid), array('session_id' => $old_sessid)));

    if ($this->CI->db->affected_rows())
    {
        // Write the cookie
        $this->_set_cookie($cookie_data);
    }
}
else
{
    // Write the cookie
    $this->_set_cookie($cookie_data);
}

This finally fixed the issue. Hope it helps :)

MySQL inline query versus stored procedure comparison

Simple query using group clause for 1 million records resulting in final list of 27 records.

First time takes 0.43053775 secs.
Same query through Stored procedure: First time takes 0.43341600 secs.

So in terms of time, first time they are very close.
Profiling comparison for both can be seen in below figure no_cache_comparison.png where left one is simple inline query and right one is stored procedure query.

There are some actions which are extra in the inline query:

1. freeing items
2. logging slow query
3. cleaning up

Running both second time retrieve data from cache taking
0.00048025 secs for simple query and 0.00036625 for stored procedure.

Profiling comparison for both can be seen in below figure cache_comparison.png where left one is simple inline query and right one is stored procedure query.

Here too there are some extra actions for inline query:

1. logging slow query
2. cleaning up

Taking into consideration the approximate equal timing of both the type of queries and that on second time execution both use cache, there is not much difference. But given the fact that stored procedures are compiled and stored, will be efficient for the db.

Also considering the article https://sachinkumaram.wordpress.com/2011/03/01/stored-procedures-advantages-disadvantages/, the advantages outweigh the disadvantages, I think.

Extra References:
http://www.seguetech.com/blog/06/04/Advantage-drawbacks-stored-procedures-processing-data
http://www.linuxjournal.com/article/9652

I would like to know your thoughts on this :)

Importance of MySQL cache

My test environment is:
Ubuntu 14.04 Trusty Tahr
MySQL Server version: 5.5.44-0ubuntu0.14.04.1 (Ubuntu)

MySQL uses sql cache to store results of queries that have been executed so that when the same query is executed again it retrieves the result data set from the cache instead of getting it again from db. So it is faster data access.

It is by default enabled in MySQL.

This is interesting since there is one question we ought to ask here whether we should use it or disable it or just leave it as it is who cares :).

Ok, moving forward today's session goals are:

1.     How useful is MySQL cache?
2.     When to use it and when not to use it?
3.     What to do if you do not want to use it?

There are some catchy areas here too like not all your queries will be stored in cache. For example, a query executed inside a stored function will not be stored in cache. Below doc link from MySQL further details out the specifics.

https://dev.mysql.com/doc/refman/5.5/en/query-cache-operation.html

Starting with our goals:

1. How useful is MySQL cache?

It is very useful indeed as once you have the result data set, if the same query is executed again, the data is now fetched from the cache and it is much faster.

2. When to use it and when not to use it?

As per MySQL doc "If a table changes, all cached queries that use the table become invalid and are removed from the cache." https://dev.mysql.com/doc/refman/5.5/en/query-cache-operation.html .

So if you have executed a query and any content in any of the tables used in the query has changed (whether related or not to the dataset for the query), all the cache entries using those tables will be flushed and executing the same query again will fetch the data from the database.

So is this bad or is fine? Depends :).

If the query you are executing is very often executed lets say 5-6 times every minute and the related tables in database are altered very often lets say at a similar rate 5-6 times a second, so we even if all the queries are stored in cache but are ultimately flushed when related table data is changed, leading to fetching once again from database and storing in cache, but which we are never able to utilize.

So in this case if the table data is too often changed then it is better not to use cache for related queries since using cache adds a certain overhead (13% as stated below) to MySQL.

https://dev.mysql.com/doc/refman/5.5/en/query-cache.html

"If all the queries you are performing are simple (such as selecting a row from a table with one row), but still differ so that the queries cannot be cached, the overhead for having the query cache active is 13%. This could be regarded as the worst case scenario. In real life, queries tend to be much more complicated, so the overhead normally is significantly lower."

Otherwise if the table is less fairly changed and the query fetching the data is more frequent then it is more efficient to use cache.

3. What to do if you do not want to use it?

As discussed above, if you do not want to use it for a query which is not able to benefit the cache storage use SQL_NO_CACHE option while executing the query
which tells MySQL not to store the result set in cache.

Eg. SELECT SQL_NO_CACHE id, name FROM customer;

https://dev.mysql.com/doc/refman/5.5/en/query-cache-in-select.html