Wednesday, October 01, 2014

CentOS Moodle and SQL Anywhere

So for some reason my place of employment is using a SQL Anywhere database and some time ago someone said, hey let's have this talk to Moodle since the user information is already in there.  
The guy in charge of the technical aspects of our Moodle stuff at the time said, "No!" - He also might have said some other things, but no was definitely the main point.
However, he left, maybe 2 years or so ago.  

Then I got put in charge of Moodle (the back end technical stuff mind you, not the actual courses and content - that's not anything I want to deal with).
I was asked the same thing and decided, "what the hell, I'll try it."

God I wish I hadn't done that.

Anyway, it's been working for a while, basically without issue until about 6 weeks ago and that's when I realized everything I thought I knew was pretty much wrong.

So basically it seems that Moodle doesn't use the native SQL Anywhere PHP module at all. Instead it only uses the native client (in our case on CentOS 6 - used to be CentOS 5) 
Seems it only uses ODBC via ADODB, as best I can tell.

Anyway, I had been going through all this trouble using an older version of PHP to maintain compatibility with the PHP module, now as I know rather needlessly. - I've been wanting to use PHP-FPM, but for some reason couldn't get it working correctly with the module provided by SAP (formerly Sybase).

The other problem I ran into is I couldn't find a newer version of the SQL Anywhere Client and only this one from 2011 which is mostly because I was searching for Sybase and not SAP - I accidently found this newer one and as soon as I installed it all my problems (well a lot of them) went away.

Basically at this point I am just using UnixODBC (or at least the files it creates/uses) along with the Linux Client linked above with some minor tweaks and it kind of works.   Most of the remaining issues are random back end issues, but thankfully other people are fixing those.  

All I can say is if you're thinking about doing this, don't.  
You can, just be aware it is quite a head ache.

I must have spent 6 weeks and countless hours troubleshooting all these seemingly random error message and doing all kinds of crazy things.  

Oh and did I mention we had some 65 vhosts on one server and we were using Apache not nginx.  The server was pretty well spec'd out, 8 cores and 48GB of ram, but it didn't help.  Seems that older Linux client was having some kind of memory leak or the authentication module in moodle talking to it.  Either way the new Linux client for SQL Anywhere made the memory leaks go away, but now I have setup some 56 Virtual machines, (as I discovered some of those vhosts were no longer live/active). They are a bit paltry, 2 cores, 2 GB of Ram and 80 GB of Hard disk space, all running CentOS 6.5, PHP 5.6 Apache 2.2.7 and Moodle 2.7.2+ as of now.  I'm running the SQL Anywhere 12. I am also using the EPEL, remi, CentALT, and RepoForge repositories.

I'd like to eventually switch them all to use PHP-FPM as that is what Moodle seems to recommend.
At this point I don't even have the SQL Anywhere module installed and both enrollment and authentication are set to ODBC and I've run into no real issues. 

At present time these are my only cron entries per server

*/5 * * * * . /root/.bash_profile > /dev/null 2>&1; /usr/bin/php /var/www/html/main/admin/cli/cron.php > /dev/null 2>&1
*/5 * * * * . /root/.bash_profile > /dev/null 2>&1; /usr/bin/php /var/www/html/main/enrol/database/cli/sync.php > /dev/null 2>&1 

I'll explain that shortly.
In /etc/ I have an odbc.ini and an odbcinst.ini the first containing the DSN information and the second the location for the driver

Description = ODBC for Sybase SQL Anywhere 12
Driver = /opt/sqlanywhere12/lib64/
Setup = /opt/sqlanywhere12/lib64/
FileUsage = 1
Trace = yes
TraceFile = /var/log/odbc.log 

Then through trial and error I had to make the following changes/additions to my systems:

Edit web server environment variables to include location of sybase client (For this particular server edit /etc/sysconfig/httpd – see below)
export SQLANY12=/opt/sqlanywhere12
Symlink the odbc and odbcinst files to /usr/bin/.odbc.ini and /usr/bin/.odbcinst.ini respectively – I am not sure if this has to be done every time, it may be specific to our server.
ln -s /etc/odbc.ini /usr/bin/.odbc.ini
ln -s /etc/odncinst.ini /usr/bin/.odbcinist.ini
For good measure I also added the following symlinks
ln -s /etc/odbc.ini /var/www/.odbc.ini
ln -s /etc/odbc.ini /sbin/.odbc.ini
ln -s /etc/odbc.ini /usr/sbin/.odbc.ini
ln -s /etc/odbc.ini /bin/.odbc.ini
This lets apache know where to find the odbc settings on our server
Symlink the file to and in the /opt/sqlanywhere12/lib64 directory
Be sure to be in the /opt/sqlanywhere12/lib64 directory first
ln -s
ln -s
This fixes some php errors
Symlink the file to and in the /opt/sqlanywhere12/lib64 directory
Be sure to be in the /opt/sqlanywhere12/lib64 directory first
ln -s
ln -s
This fixes some php errors

It was also helpful for testing to add

source /opt/sqlanywhere12/bin64/

to /root/.bash_profile

Also running source /opt/sqlanywhere12/bin64/ in the terminal helps.
Open port 2638 on the system firewalls (in our case iptables)
Add the following line to iptables
-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 2638 -j ACCEPT

Something I forgot to mention the last time, was for this to work you have to symlink /opt/sqlanywhere12/bin64/ to the /etc/profile.d/
ln -s /opt/sqlanywhere12/bin64/ /etc/profile.d/
be sure to chmod +x /opt/sqlanywhere12/bin64/ first or else it won’t run!
That adds the client config to every logged on user's path. ( I think)
On the moodle server under Site Administration -> Plugins -> Authentication -> External Database and Site Administration -> Plugins -> Enrolments -> External Database make the host name matches the DSN the Database type ODBC
DBNAme, DBUser and Password should remain blank as the odbc file store this information.
All the other database stuff, as it pertains to the Table names and fields, isn't related to my part of the setup. Someone else figured that out.
Another important step is that SELInux be disabled – it just gets in the way – there is documentation on the CentOS website on how to accomplish this. Now I am sure you can get this working with SELinux enabled, but I was far too lazy to figure that out.
Which reminds me that it is a good idea to have strace installed on any system we do this with, it is an invaluable debugging tool.
E.g. ps auxw | grep sbin/httpd | awk '{print"-p " $2}' | xargs strace 2>&1 | grep ini
The above runs a strace on apache this let me know that apache was looking for the odbc ini files in a different location and made the creation of the symlink necessary and resolved the issue.
Additionally Google is your friend - most of the time the errors you get tend to be specific but if you search for parts of the error messages or know what keywords to include or exclude you can usually always find someone else who had the same problem and how to fix it.