Tag: PHP

Uninformed Upgrades (PHP 5 => 7)

TL;DR: Check the list of what is being updated before you let an OS automatically update its programs.

We have a home automation / MythTV / ZoneMinder server with automatic updates disabled. In the process of updating OpenHAB to OpenHAB2, Scott suggested we update everything else while we’re at it. No big, did a quick “dnf update” … got a gig of packages downloaded, waiting for >1400 packages to install, and rebooted.

PHP could not talk to MySQL. At all. ZoneMinder just threw an error saying we didn’t have the PHP MySQL module installed (it worked half an hour ago, so it is INSTALLED). MythWeb completely failed to load – just a white screen. The quick web view of OpenHAB persistence history threw a class not found error.

I checked to see if the extensions were loaded (use the command “print_r(get_loaded_extensions());” in a PHP page) – huh, a LOT of my modules were missing. But there weren’t any useful errors anywhere indicating why.

I modified the php.ini file to show startup errors.

[root@fedora01 conf.modules.d]# grep display_startup_errors /etc/php.ini
; display_startup_errors
display_startup_errors = On

Oooooh, now there are errors! A lot of them. Not particularly useful, but at least a good clue that this isn’t going to go so well for me:

PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/pdo.so’ – /usr/lib64/php/modules/pdo.so: undefined symbol: zend_ce_exception in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/mysqlnd.so’ – /usr/lib64/php/modules/mysqlnd.so: undefined symbol: zend_hash_str_del in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/bcmath.so’ – /usr/lib64/php/modules/bcmath.so: undefined symbol: _emalloc_16 in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/bz2.so’ – /usr/lib64/php/modules/bz2.so: undefined symbol: zend_fetch_resource2_ex in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/calendar.so’ – /usr/lib64/php/modules/calendar.so: undefined symbol: _emalloc_32 in Unknown on line 0
PHP Warning: PHP Startup: ctype: Unable to initialize module\nModule compiled with module API=20151012\nPHP compiled with module API=20131226\nThese options need to match\n in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/curl.so’ – /usr/lib64/php/modules/curl.so: undefined symbol: zend_list_close in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/dom.so’ – /usr/lib64/php/modules/dom.so: undefined symbol: zend_ce_exception in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/exif.so’ – /usr/lib64/php/modules/exif.so: undefined symbol: zend_hash_str_exists in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/fileinfo.so’ – /usr/lib64/php/modules/fileinfo.so: undefined symbol: zend_list_close in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/ftp.so’ – /usr/lib64/php/modules/ftp.so: undefined symbol: zend_fetch_resource2 in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/gd.so’ – /usr/lib64/php/modules/gd.so: undefined symbol: zend_list_close in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/gettext.so’ – /usr/lib64/php/modules/gettext.so: undefined symbol: zend_parse_arg_str_slow in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/iconv.so’ – /usr/lib64/php/modules/iconv.so: undefined symbol: _zval_get_string_func in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/json.so’ – /usr/lib64/php/modules/json.so: undefined symbol: _emalloc_56 in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/mbstring.so’ – /usr/lib64/php/modules/mbstring.so: undefined symbol: zend_hash_str_del in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/mysqlnd.so’ – /usr/lib64/php/modules/mysqlnd.so: undefined symbol: zend_hash_str_del in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/phar.so’ – /usr/lib64/php/modules/phar.so: undefined symbol: zend_sort in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/posix.so’ – /usr/lib64/php/modules/posix.so: undefined symbol: _zend_hash_str_update in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/shmop.so’ – /usr/lib64/php/modules/shmop.so: undefined symbol: zend_list_close in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/simplexml.so’ – /usr/lib64/php/modules/simplexml.so: undefined symbol: zend_ce_exception in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/sockets.so’ – /usr/lib64/php/modules/sockets.so: undefined symbol: zend_hash_str_del in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/sqlite3.so’ – /usr/lib64/php/modules/sqlite3.so: undefined symbol: zend_ce_exception in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/sysvmsg.so’ – /usr/lib64/php/modules/sysvmsg.so: undefined symbol: _emalloc_64 in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/sysvsem.so’ – /usr/lib64/php/modules/sysvsem.so: undefined symbol: _emalloc_24 in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/sysvshm.so’ – /usr/lib64/php/modules/sysvshm.so: undefined symbol: zend_list_close in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/tidy.so’ – /usr/lib64/php/modules/tidy.so: undefined symbol: _zend_hash_str_update in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/tokenizer.so’ – /usr/lib64/php/modules/tokenizer.so: undefined symbol: _emalloc_large in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/xml.so’ – /usr/lib64/php/modules/xml.so: undefined symbol: _zend_hash_str_add in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/xmlwriter.so’ – /usr/lib64/php/modules/xmlwriter.so: undefined symbol: _emalloc_16 in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/xsl.so’ – /usr/lib64/php/modules/xsl.so: undefined symbol: dom_node_class_entry in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/mysql.so’ – /usr/lib64/php/modules/mysql.so: undefined symbol: mysqlnd_connect in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/mysqli.so’ – /usr/lib64/php/modules/mysqli.so: undefined symbol: zend_ce_exception in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/pdo_mysql.so’ – /usr/lib64/php/modules/pdo_mysql.so: undefined symbol: mysqlnd_allocator in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/pdo_sqlite.so’ – /usr/lib64/php/modules/pdo_sqlite.so: undefined symbol: php_pdo_unregister_driver in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/wddx.so’ – /usr/lib64/php/modules/wddx.so: undefined symbol: zend_list_close in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/xmlreader.so’ – /usr/lib64/php/modules/xmlreader.so: undefined symbol: dom_node_class_entry in Unknown on line 0
PHP Warning: PHP Startup: Unable to load dynamic library ‘/usr/lib64/php/modules/json.so’ – /usr/lib64/php/modules/json.so: undefined symbol: _emalloc_56 in Unknown on line 0

Turns out DNF installed PHP 7, but didn’t do anything to remove the PHP 5 modules from my Apache configuration:

[root@fedora01 tmp]# cd /etc/httpd/modules
[root@fedora01 modules]# grep php *
Binary file libphp5.so matches
Binary file libphp5-zts.so matches
Binary file libphp7.so matches
Binary file libphp7-zts.so matches

[root@fedora01 modules]# mkdir /tmp/oldphp
[root@fedora01 modules]# mv libphp5* /tmp/oldphp

And remove them from the conf.modules.d too (if you just remove the module files but try to load them in the conf.modules.d … Apache will just fail to load. You could remove them from conf.modules.d … but I don’t want a lot of no-longer-used files sitting there to confuse me in a year or two!)

[root@fedora01 modules]# cd /etc/httpd/conf.modules.d/
[root@fedora01 conf.modules.d]# grep php *
10-php.conf: LoadModule php5_module modules/libphp5.so
10-php.conf: LoadModule php5_module modules/libphp5-zts.so
15-php.conf:# Cannot load both php5 and php7 modules
15-php.conf:<IfModule !mod_php5.c>
15-php.conf: LoadModule php7_module modules/libphp7.so
15-php.conf:<IfModule !mod_php5.c>
15-php.conf: LoadModule php7_module modules/libphp7-zts.so

[root@fedora01 conf.modules.d]# mv 10-php.conf /tmp/oldphp/

Then restart Apache without PHP 5:

root@fedora01 conf.modules.d]# service httpd start
Redirecting to /bin/systemctl start httpd.service

Voila, perfectly functioning web sites. And, yeah, I should probably check the list of “what will be updated” when I update a server. Would save HOURS of reading through strace output to find out old versions were still hanging about.

 

PHP 7.0 and MySQL Libraries

At work, we have some servers running unsupported operating systems. New servers are being built, and applications are being migrated from the old servers to the new. I started with a fairly easy scenario – a PHP web site running on Windows 2008 is moving to 2012. The new web server was handed off to me, and I loaded PHP. With PHP 5.6 active support ending at the end of this year, it made sense to install PHP 7. Copied code, tested site. Umm, massive fail.

Way back in PHP 5.5, the ext/mysql stuff (ext\php_mysql.dll for Windows folks) was deprecated. And if you are like me, you had a lot of old code from back when that was the way to connect to a MySQL database. And as your MySQL was upgraded past 4.1, you had the DBA’s setting old_password on your ID so your code continued to work.  But the old mysql libraries have been removed in PHP 7… and you need to use MySQLi or pdo_mysql to communicate with your database now.

Which one? Depends on what you need – I’ve been using PDO because I don’t need a procedural API (MySQLi provides a procedural API, PDO does not). PDO supports a dozen or so database drivers, MySQLi is just MySQL … so I’ll be able to use the same basic code to connect to MySQL, MS SQL, Oracle, and db2 (plus a handful of others that I don’t anticipate actually using, but who knows what the future holds).

I found a site (http://archive.jnrbsn.com/2010/06/mysqli-vs-pdo-benchmarks) where the individual has benchmarked MySQLi and PDO and doesn’t find much difference on INSERT statements but does see a non-negligible difference on prepared and non-prepared SELECT statements. His post is fairly old, so I ran timed tests on my server using our existing data and found PDO was within a couple of milliseconds. Using either library requires some recoding, but it is fairly straightforward and I was using a script to rebuild my script with the new functions. So I have a nice new server with nice new PHP and nice new MySQL queries using PDO … hit the page to test it and I get a generic error. Add a few lines to my code so I get some sensible errors

     error_reporting(E_ALL);
     ini_set('display_errors',1);

 

Voila – umm, this is gonna be a problem:

Next PDOException: SQLSTATE[HY000][2054] The server requested authentication 
method unknown to the client 
in D:\vhtml\PKIHome\IssuedDeviceCerts\index.php:46

Stack trace:

#0
D:\vhtml\PKIHome\IssuedDeviceCerts\index.php(46):
PDO->__construct('mysql:host=acil...', 'uidsuppressed', 'pwdsuppressed',Array)

The ‘server requested authentication method unknown to the client” means that the new PDO and MySQLi (yes, I’ve tried both) cannot use the password as required for the currently running production code. And the library used in the currently running production code cannot use the password as required for PDO or MySQLi. I cannot just convert the code to the new method, drop it on the new server, cut over, and decommission the old box. There are two approaches that can be used:

**************************************************

#1 Recode against a development MySQL database

#2 Get new IDs using the new storage scheme

**************************************************

#1 If you have a development MySQL database, you can add a hosts file entry (or have your OS support team do so) to point your production database host to the development server. The development server should be refreshed with data from the production databases. The existing IDs that use the old password storage schema can be updated with the new password storage scheme (either you provide the current password or a new password can be set). You will then need to update your PHP code to use either PDO or MySQLi. The implementation CRQ to move to your new server then involves (a) having the DBAs update the production user ID to the new password storage scheme, (b) removing the hosts file from your server.

 

Advantage – You don’t need to change to a new user ID in your code.

Disadvantage – anything that uses this ID needs to be updated simultaneously. When the new password storage schema is used on the account, any client requiring the old password storage scheme will fail. If your ID is used for one specific application on one server, then this isn’t a big deal. If you
use the ID to write data from a batch server or middleware platform, and then read the data from a PHP site … you need to recode both to use a library that understands the new password storage scheme.

**************************************************

#2 The other option is to get a new ID created that uses the new password storage scheme and have the same permissions granted for that ID. You can then recode individual pages as they are moved to the new server, and the old ID can be removed when all of the sites using it have been moved.

 

Advantage –You don’t need to move everything at once.

Disadvantage – you are making more changes to your code and replacing all of your user IDs (if you have a MyODBC driver to link an Access table into the database or if you use the MyPHPAdmin site … you’ll need to remember the new account now).

**************************************************

This isn’t a fatal error that prevents the upgrades from being done, but it sure turned into more of an undertaking than I had originally anticipated! If you should happen to use PHP and MySQL using the old libraries and have a PHP 7 installation planned … it really isn’t just copy some files & update some function calls.

 

Tracking Electrical Usage With SmartThings and AeonLabs Home Energy Meters

When we started shopping for solar generation installations, how much electricity we can consume was a challenging question. We were replacing our HVAC system, so “look at the last 12 months of electric bills” wasn’t an approach that would yield valid data. What we needed was a way to see electrical consumption for the next two or three weeks once our new HVAC system was installed.

We purchased several AeonLabs Home Energy Meters (HEM) and have been using them to track our power consumption for almost six months now. The HEM’s are set up in SmartThings & have a SmartApp-HEMLogger “SmartApp” attached to them that posts data to a MySQL table on our server via a web form (myURL needs to be … well, your URL).

Install a quick MySQL server (does not need to be Internet accessible) and a web server / programming language of your choice combination (*does* need to be Internet accessible – we are using Apache and PHP).

Create the database and a table to hold your energy data:

mysql> describe EnergyMonitors;
+-----------+-------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+-------------------+----------------+
| keyID | int(11) | NO | PRI | NULL | auto_increment |
| monitorID | varchar(50) | YES | | NULL | |
| clampID | varchar(50) | YES | | NULL | |
| eventTime | timestamp | NO | | CURRENT_TIMESTAMP | |
| kwatts | double | YES | | NULL | |
| kwhours | double | YES | | NULL | |
+-----------+-------------+------+-----+-------------------+----------------+
6 rows in set (0.00 sec)

Create an ID within your database that has read/write permission to this table. I create another ID that has read-only access (pages displaying data use this ID, the page to post data uses the read/write ID).

We also track temperature — ideally, we’d be able to compare power consumption based on temperature *and* sunlight (we use a lot less power on a cold sunny day than I expect … just don’t know how much less) … but I’m not there yet. Currently, the weather database only holds temperature:

mysql> describe weather;
+--------------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------+------+-----+-------------------+-----------------------------+
| recordedTime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| temperature | int(11) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
+--------------+-----------+------+-----+-------------------+-----------------------------+
3 rows in set (0.01 sec)

Since we brought our Bloomsky online, I use the Bloomsky API to record temperature. Prior to that, I was parsing the XML from NWS’s closest reporting station’s – for Cleveland, that is  http://w1.weather.gov/xml/current_obs/KCLE.xml … there’s probably one near you. I grabbed both observation_time_rfc822 and temp_f in case observations were not updated regularly – the observation time was stored as the recordedTime. The temperature recording script is in cron as an hourly task.

You also need a small web page where SmartThings can post data — in PHP, I have

<?php
if(!$_POST["monitorID"] || !$_POST["clampID"] ){
echo "<html><body>\n";
echo "<form action=\"";
echo $_SERVER['PHP_SELF'];
echo "\" method=\"post\">\n";
echo "Monitor ID: <input type=\"text\" name=\"monitorID\"><br>\n";
echo "Clamp ID: <input type=\"text\" name=\"clampID\"><br>\n";
echo "KW Value: <input type=\"text\" name=\"kwatts\"><br>\n";
echo "KWH Value: <input type=\"text\" name=\"kWHours\"><br>\n";
echo "<input type=\"submit\">\n";
echo "</form>\n";
echo "</body></html>\n";
}
else{
$strMeter = $_POST["monitorID"];
$strClamp = $_POST["clampID"];
$strKWValue = $_POST["kwatts"];
$strKWHValue = $_POST["kWHours"];
print "<pre>Meter: $strMeter\nClamp: $strClamp\nKW: $strKWValue\nKWH: $strKWHValue\n</pre>\n";
$servername = "DatabaseHostname";
$username = "MySQLUID";
$password = 'MySQLPassword';
$dbname = "HomeAutomation";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
if($strKWHValue && $$strKWValue){
$sql = "INSERT INTO EnergyMonitors (monitorID, clampID, kwatts, kwhours) VALUES ('$strMeter', '$strClamp', '$strKWValue', '$strKWHValue')";
}
elseif($strKWHValue){
$sql = "INSERT INTO EnergyMonitors (monitorID, clampID, kwhours) VALUES ('$strMeter', '$strClamp', '$strKWHValue')";
}
else{
$sql = "INSERT INTO EnergyMonitors (monitorID, clampID, kwatts) VALUES ('$strMeter', '$strClamp', '$strKWValue')";
}
if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
}
else {
echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
}
?>

DatabaseHostname, MySQLUID and MySQLPassword need to be yours too. Since posted data is meant to come from a trusted source (a source where I’ve supplied the URL) and in a known good format, these are quick INSERT statements *not* the safest.

Check in your database that you are getting data, then let it run for a few hours. Once you have a little bit of history, you can start viewing your energy usage. Link it into Excel/Access using MyODBC for ad-hoc reporting, write your own code to do exactly what you want, use a generic charting package capable of reading MySQL data …

I am using pChart to display data – the chart I use most frequently is the stacked bar chart for kWH and a line chart for temperature. Here is the PHP code which generates this PNG: energyUsage-StackedBarChart-Flat – again, DatabaseHostname, MySQLUID, and MySQLPassword need to be yours.

StackedBarChart-KWHAndTemp

We no longer have our heat pump, air handler, and heat strips being monitored – but for periods where there is data from the other sources, we had several segments to our energy usage report (“other” is the report from the HEM on our mains MINUS all of the other reporting segments). You can yank all of the non-mains segments (or change them to be whatever sub-segments you are monitoring. The monitor ID comes from the HEM name in SmartThings – since those are user-configured, I have the names hard coded. You *could* hard code the Mains and then use “select distinct” to get a list of all the others and make the code more flexible.).

Short term charts (past 24 hours or so) can render out real-time, but longer term views take a long time to load. Since we’re looking more for trends and totals – being up to the second isn’t critical. I’ve got cron tasks that generate out PNG files of the charts.

Two enhancements for a nothing-doing rainy day – adding authentication to the HEM Logger (SmartThings posts from multiple netblocks, so there isn’t a good way to IP source restrict access to the post data page. Anyone with your URL could post rogue energy usage info into your database – or more likely try to hack your servers.) and add lumen to the weather data / graph displays.