Category: Recipes

Linux (Ubuntu 16.04), PHP and MS SQL

In the many years I’ve been using the traditional LAMP stack, I’ve successfully managed to avoid having anything to do with MS SQL server. Until 2016. This year I’ve had to work quiet a bit with it – administration, backups and, now, scripted queries from Linux with PHP.

I suspect I’m (a) lucky I haven’t had to do this before now; and (b) that Azure seems to have pushed Microsoft into greater Linux based support for MS SQL. The evidence? This open source Mircosoft repository with a MS SQL PHP binary driver for Linux released just a few months ago.

NB: installing the Microsoft PHP driver is different to installing the Microsoft ODBC driver for SQL Server on Linux. These may even be incompatible.

For me, I just took a standard Ubuntu 16.04 install (64bit obviously) with PHP 7.0 and downloaded the latest MS PHP SQL extension (for me, at time of writing, this was 4.0.6. When you untar the Ubuntu16.tar file, copy the .so files to /usr/lib/php/20151012/ and then create a /etc/php/7.0/mods-available/msphpsql.ini file with contents:

extension=php_pdo_sqlsrv_7_nts.so
extension=php_sqlsrv_7_nts.so

Note that the tar also contains two ‘ts’ versions of these files. Trying to use those resulted in errors. Link this for Apache2 / CLI as required. E.g. for PHP CLI:

cd /etc/php/7.0/cli/conf.d/
ln -s ../../mods-available/msphpsql.ini 20-msphpsql.ini

You can confirm it’s working via:

$ php -i | grep sqlsrv
Registered PHP Streams => https, ftps, compress.zlib, php, file, glob, data, http, ftp, sqlsrv, phar
PDO drivers => sqlsrv
pdo_sqlsrv
pdo_sqlsrv support => enabled
pdo_sqlsrv.client_buffer_max_kb_size => 10240 => 10240
pdo_sqlsrv.log_severity => 0 => 0
sqlsrv
sqlsrv support => enabled
sqlsrv.ClientBufferMaxKBSize => 10240 => 10240
sqlsrv.LogSeverity => 0 => 0
sqlsrv.LogSubsystems => 0 => 0
sqlsrv.WarningsReturnAsErrors => On => On

And, finally, for using it, following the the sample scripts from the repository worked a charm.

PHP – Set Array Value from Closure Evaluation

There are times when you need to set a PHP array value from evaluated PHP code. Sometimes, you need to this using variables that won’t conflict with the current scope and with throwaway code that you won’t need again – so a closure is ideal.

Typically, you’ll need to assign the closure to a variable but this will negate the above requirement to not interfere with the current scope.

Here’s a way to do this:

return [
    'key1' => call_user_func( function() {
        $somevar = fn();
        // generate value
        return $calculated_value;
    }),
];

This has proved particularly used in Laravel configuration files.

Debugging NFS Slowness

During patching for the recent GHOST bug, I updated all packages (including kernel) on a Ubuntu 14.04 file server (filer). This filer provided static content (mainly tens of thousands of images) to a number of web servers. You can see the effect in the following load graph from the filer:

Load average on the filer
Load average on the filer

You may notice from the above, that there were actually two issues. The first was solved by upgrading the filer from 14.04 to 14.10 based on a number of online references to symptoms and fixes. About an hour after this upgrade, a new form of NFS slowness manifested and, needless to say, sites that rendered in <1sec were now taking >15secs.

Diagnosing the second issue took a while longer but some tips and utilities include:

  • check /var/log and see if any log files are increasing rapidly;
  • check top and check any processes with high / unusual utilisation;
  • use iostat (apt-get install sysstat) and pay particular attention to any devices with high volumes of transactions per second. In my case it was the root filesystem rather than any of the mounted partitions exported by NFS.
  • use iotop (apt-get install iotop) and note any processes with high utilisation (in my case jbd2/xvda1-8 was at 100% and xvda1-8 is my root partition)

The jbd2 process is the ext4 journaling process. At this point you can evaluate fsck’ing your partition but I wanted to see if I could discover what was happening here. I enabled some debugging via:

# enable tracing:
echo 1 > /sys/kernel/debug/tracing/events/ext4/ext4_sync_file_enter/enable
# wait a couple of seconds and:
cat /sys/kernel/debug/tracing/trace
# and disable tracing:
echo 0 > /sys/kernel/debug/tracing/events/ext4/ext4_sync_file_enter/enable

What I found were lots of:

nfsd-2085  [001] .... 53730942.155573: ext4_sync_file_enter: dev 202,1 ino 276278 parent 149955 datasync 0
nfsd-2071  [001] .... 53730942.158743: ext4_sync_file_enter: dev 202,1 ino 276278 parent 149955 datasync 0
...

where every entry related to the same inode number (276278). We found this via:

find / -inum 276278
/var/lib/nfs/v4recovery

The solution was to stop nfs_kernal_server, remove that directory entirely, add it back and restart the nfs_kernel_server. We got the permissions wrong on the first attempt but this’ll be obvious from dmesg / kernel log messages such as:

kernel: [53731827.778104] NFSD: Failed to remove expired client state directory 8d97cccceb37641d3804a84683a9282a
kernel: [53731827.779204] NFSD: failed to write recovery record (err -13); please check that /var/lib/nfs/v4recovery exists and is writeableNFSD: Failed to remove expired client state directory 8d97cccceb37641d3804a84683a9282a

Nagios Plugin to Check Extreme Networks Devices

Over at INEX we’ve embarked on a forklift upgrade of the primary peering LAN using Extreme Networks Summit x670’s and x460’s. As usual, we need to monitor these 24/7 and we have just written a new Extreme Networks chassis monitoring script which should work with most Extreme devices.

It will check and generate alerts on the following items:

  • a warning if the device was recently rebooted;
  • a warning / critical if any found temperature sensors are in a non-normal state;
  • a warning / critical if any found fans are in a non-normal state;
  • a warning / critical if any found PSUs are in a non-normal state (or missing);
  • a warning / critical if the 5 sec CPU utilisation is above set thresholds;
  • a warning / critical if the memory utilisation is above set thresholds.

You’ll find the script in this Github repository: barryo/nagios-plugins.

A some verbose output follows:

./check_chassis_extreme.php -c  -h 10.11.12.13 -v 
 
CPU: 5sec - 4% 
Last reboot: 4007.7666666667 minutes ago 
Uptime: 2.8 days. 
PSU: 1 - presentOK (Serial: 1429W-xxxxx; Source: ac) 
PSU: 2 - presentOK (Serial: 1429W-xxxxx; Source: ac) 
Fan: 101 - OK (4388 RPM) 
Fan: 102 - OK (9273 RPM) 
Fan: 103 - OK (4428 RPM) 
Fan: 104 - OK (9273 RPM) 
Fan: 105 - OK (4551 RPM) 
Fan: 106 - OK (9452 RPM) 
Temp: 39'C 
Over temp alert: NO 
Memory used in slot 1: 29% 
OK - CPU: 5sec - 4%. Uptime: 2.8 days.  PSUs: 1 - presentOK; 
  2 - presentOK;. Overall system power state: redundant power 
  available. Fans: [101 - OK (4388 RPM)]; [102 - OK (9273 RPM)];
  [103 - OK (4428 RPM)]; [104 - OK (9273 RPM)]; [105 - OK (4551 
  RPM)]; [106 - OK (9452 RPM)];. Temp: 39'C. 
  Memory (slot:usage%): 1:29%.

OS X Built-in tftp Server

Turned out to be very useful during a recent RMA maintenance window:

sudo launchctl load -F /System/Library/LaunchDaemons/tftp.plist
sudo launchctl start com.apple.tftpd

The default tftp file path is /private/tftpboot. [Original source]

You can stop it with:

sudo launchctl unload -F /System/Library/LaunchDaemons/tftp.plist

And, speaking of tftp – there are some interesting projects on GitHub: hooktftp, php-tftpserver and ptftpd.

Cordova, Facebook, Android and no Eclipse

This post relates to creating an Android application with Facebook support (via phonegap-facebook-plugin using Cordova 3.5 and, specifically, without Eclipse. Most existing documentation expects you to spin up Eclipse to link the Facebook libraries – but, we’re using an automated build system with Grunt so that’s not a possibility.

Firstly – the steps to get it working:

  1. Add the Cordova Facebook plugin: 
    cordova plugin add https://github.com/phonegap/phonegap-facebook-plugin \
    --variable APP_ID="1234567890" 
    --variable APP_NAME="Test App"
  2. Edit platforms/android/project.properties and add: 
    android.library.reference.2=FacebookLib

    where n is the next available reference index. Also note the target parameter here as you’ll need it next:

  3. Update the Android project for the plugin: 
    cd platforms/andoird/FacebookLib
android update project --target android-19 -p .

     where android-19 should match the target parameter from above.

  4. Prepare and build your application: 
    cordova prepare android
cordova compile android

Well, there goes my day…

I arrived to work this morning to find that the Heartbleed Bug happened.

This is one of the biggest security issues to crop up in a long time – allowing the data normally protected by TLS/SSL to be compromised. This is the kind of data that normally passes securely between clients and protected websites, email services, instant messaging, etc.

Upgrade all your systems now. This is where my well planned day went.

Be sure to restart all services that use OpenSSL (or reboot your servers). A useful command [source] for this post-upgrade is:

grep -l 'libssl.*deleted' /proc/*/maps | tr -cd 0-9\\n | xargs -r ps u

A useful Python script for testing your web servers can be found in this Gist. NB: it’s not just web servers affected – any services with SSL/TLS may be affected.

Doctrine2 Provider for Laravel 4 Authentication

I’ve just added to the Doctrine2 service provider for Laravel by adding a UserProvider allowing Doctrine2Bridge to provide a driver for Laravel’s authentication system.

Simply put – this allows a Doctrine2 database table stroing users’ usernames and passwords to be used as the backend for Laravel 4 authentication.

Full documentation and examples can be found here. Available on Packagist and forkable on GitHub.

Querying Cisco MST Port Roles via SNMP with OSS_SNMP

OSS_SNMP is a PHP SNMP library written by myself for people who hate SNMP. After a customer migration from PVST to MST (Multiple Spanning Tree), I have added a number of MST functions / MIBs to OSS_SNMP:

During a fairly significant network migration involving breaking / connecting a number of links, I wanted to be able to monitor the MST port role of significant ports at a glance. For this purpose, I wrote the mst-port-roles.php script and have committed it as an example to OSS_SNMP. First, here is what it looks like when run on the command line (with hostnames obfuscated):

MST Port RolesFrom a very simple array of port details at the top of the script, it will poll all switches and for each port print:

  • device and port name;
  • port state and speed;
  • port role for each applicable MST instance.

I run it on bash and use bash colouring. The script is well documented and can easily be repurposed for other networks. You’ll find the source here.

Making a Console Cable for Soekris Boxes

Connecting to a Soekris box from your computer via a console cable is a DTE to DTE connection and so you need to purchase or make up a null modem cable. This page on RS232 serial null modem cable wiring proved invaluable.

With the documentation above, I created a null modem with loop back handshaking cable using two wire-able female DB9-to-RJ45 converters and a standard straight-through network cable following the pin positions in the above link.

To locally connect and loop back  pins  1+4+6 and 7+8, I snipped those wires, striped the ends and just twisted together and covered with electrical tape. Pin 5 is connected to its opposite number and you only then need to ensure that pins 2 and 3 are crossed over.

This worked a charm with my USB serial port. After I tried about a dozen various unlabeled console cables I have lying around that is…