Moodle

The database-session code will be fully rewritten in 2.0, unfortunately the current session code using adodb has several known problems

the adodb session code is not used anymore in 2.0 - thanks for the report

Petr, is there any actual problem with just implementing this fix in 1.9? (removing the binary cast)

It seems to work for a lot of people using 1.9.

Works in 1.8 with no issues as well. Can it be fixed for those that will need to use 1.8 for a bit longer?

I hope people don't mind that I extend the "affects version/s" to include 1.8 as well. Instead of just listing out 1.8, I listed 1.8.x ~ 1.9.x just so that when people use JIRA's filter they could still find this ticket - NOT that I'm trying to make this bug looks really important/scary across many versions! Martin or Petr if I should just list "affects version/s" as 1.8 and 1.9 in the future (for generic 1.8.x and 1.9.x bugs), please let me know. Thanks!

the problem here is that it just "seems" to work, but in fact it does not work much
When people use db sessions in 1.9 or earlier they sometimes get weird errors and various seemingly unrelated problems, I do think we should not recommend db sessions at all yet

Oh boy... Petr when you said that "it just seems to work but in fact it does not work much," are you implying that there are still a lot of potential problems with dbsessons? Could you please elaborate this a little bit more? So if moodle core team will not recommend db sessions at all yet, is there any work-around or alternatives for people who are running large/busy 1.9.x sites (or sites built on cluster of servers)? Thanks!

By the way I did notice the MDL-17754 and have added myself to the watch list. I have also quickly updated the moodle doc sessions to reflect your comments. Please feel free to edit this page:

http://docs.moodle.org/en/sessions

Oh boy...
It is not my fault, I just discovered the problem and tried to fix it in HEAD. Frankly I do not know how to solve it on 1.9.x sites.

All our code expects sessions to be locked properly - see http://php.net/manual/en/function.session-write-close.php If it is not locked it can not work as expected because concurrent http requests start overriding session or undoing session changes.

Long ago somebody implemented db session support in Moodle using incomplete session code from adodb, at that time we did not store that much critical data in session, but now we cache user access data, group access, role switching and many other critical data there. I can not tell you exactly what and when may go wrong, it may or may not work.

I can not stop anybody using db sessions, all I say is that it should not be supported because it is not reliable and we can not fix it in 1.9.x. Even in 2.0 it might be a big problem to implement it somehow for Oracle and MSSQL and it may be slow/expensive for pgsql adn mysql.

more info:
http://www.mysqlperformanceblog.com/2007/03/27/php-sessions-files-vs-database-based/

We could just remove the binary, but I would personally prefer if somebody found out why it was added there originally, maybe it has some purpose

We we experiencing outrageous CPU usage on our database server for one of our busier sites and I noticed that queries to the mdl_sessions2 table were frequently waiting locks to clear which led me here. It also led me to this page:

– QUOTE –
http://dev.mysql.com/doc/refman/5.0/en/charset-binary-op.html

OK but the problem is that with binary, the index cannot be used :
mysql> explain select prod_name, prod_id from products where binary prod_name = 'Hype Beau';

The solution is to apply the binary operator to the constant, then the index is used :
mysql> select prod_name, prod_id from products where prod_name = binary 'Hype Beau';
– END QUOTE –

This particular mdl_sessions2 table had about 10K rows, so not having indexes was killing us.

I did a find & replace of '$binary sesskey = ' to 'sesskey = $binary ' in /lib/adodb/session/adodb_session.php and /lib/adodb/session/adodb_session2.php and our DB server's CPU usage dropped from an average of about 100-160% to about 20-30%.

For those trying to fix this for earlier versions of Moodle, I first made a backup copy of adodb_session.php and adodb_session2.php. Then the following command will change the instances in the file:

sed -i 's/$binary sesskey = /sesskey = $binary /g' adodb-session.php adodb-session2.php

Hi people,

We originally used file based sessions stored in our moodledata folder on our SAN. We found that when there were high periods of activity (8000+ connections within an hour say) the file system struggled to read and write to the disk, probably coupled with indexing the amount of files in the folder so we switched to database stored sessions.

This just moved the problem to a different arena as we found the sessions2 table locking to process the amount of activity during periods of high load causing the CPU on our database server to go nuts.

Following the information posted by SFSU (Taylor Judd), we've changed the files below and found a significant performance increase, just in case this helps anyone else supporting a large user base!

in /lib/adodb/session/adodb-session.php (and adodb-session2.php in the same directory)

replace

$binary = $conn->dataProvider === 'mysql' ? '/*! BINARY */' : '';

to

$binary = $conn->dataProvider === 'mysql' ? '' : '';

Hello,

I did like Greg Milsted suggested: Changed the source of /lib/adodb/session/adodb-session.php and dodb-session2.php

from

$binary = $conn->dataProvider === 'mysql' ? '/*! BINARY */' : '';

to

$binary = $conn->dataProvider === 'mysql' ? '' : '';

OW! performance is better now!
i add 2 new index in mdl_sessions2 to improve performace too.
thanks

I think we have just discovered some nasty duplicates most probably caused by the lack of locking in DB sessions in 1.9 during the 2.0 upgrade.
I personally think that there is no reason to attempt to fix this because the db sessions should not be recommended for any production sites running Moodle < 2.0
Sorry for the bad news...

My tests have not revealed this to be a problem when using MySQL 5.5 with the InnoDB storage engine. Is row-level locking likely to help here?