[TriLUG] OT: mysql, php5, and Zend bug

[Paul Boyle]

Hi,

My problem is occurring on Linux (openSuSE 13.1), but is probably not specific to Linux. However, I am hoping that some of the knowledgeable people on this list can get me pointed in the right direction.

Punchline: Does anyone know if more recent versions of Zend Framework take care of the bug described. Short description of bug: PDO and prepared statements which use boolean values (or NULL) get translated as empty strings rather than the proper type (e.g. tinyint or decimal). This behavior was accepted in mysql 5.5 but not 5.6.

Detailed Background:
Yesterday, I upgraded my webserver from opensuse 12.2 -> 13.1. The webserver (apache 2.4.6) acts as a front end for a mysql database which keeps track of samples submitted to my facility. The middle layer uses the Zend framework (version 1.11.11). In the course of the upgrade mysql was displaced by MariaDB. That gave me problems, so I backed out the MariaDB software and installed the mysql-* rpms (version 5.6.12) which shipped with opensuse 13.1. I was able to restore my database from a mysqldump backup. However when I tried to add or edit sample entries through the web interface, I would get entries in the apache error log. After some googling. I tracked down the error as this bug:

http://evertpot.com/mysql-bool-behavior-and-php/

I didn't use the fixes suggested in the above referenced article because I am using the Zend Framework and none of the code I use (which was written by a former colleague when I worked at NCSU) makes use of PDO directly. I don't know enough about this to make some intelligent modifications to the code.

I rewrote the part of my applications PHP code which relied on boolean values to use explicit '0' and '1' for 'false' and 'true' states. That took care of the boolean problem. However, the problem shifted to database entries which are initialized as NULL. In the error log I get this entry:

[Wed Apr 23 14:13:35.218999 2014] [php5:error] [pid 8528]
[client 129.100.61.21:38299] PHP Fatal error: Uncaught exception
'PDOException' with message 'SQLSTATE[HY000]: General error:
1366 Incorrect decimal value: '' for column 'charges' at row 1'

The 'charges' field is defined in the table as decimal(10,2), can be Null, and is initialized as NULL. I've done a SELECT statement which shows this field and a number of other fields:

 mysql> select submitdate,charges,temp,scanangle,scantime,rundate from xray where xrayid = 1300707;
+------------+---------+------+-----------+----------+---------+
| submitdate | charges | temp | scanangle | scantime | rundate |
+------------+---------+------+-----------+----------+---------+
| 2014-04-22 | NULL | NULL | NULL | NULL | NULL |
+------------+---------+------+-----------+----------+---------+
1 row in set (0.00 sec)

I guess I could initialize all the NULLs to zeros, but in some cases, e.g. 'rundate', I worry about if that might cause other problems.

I am hoping there is a neater solution, and I am hoping that some other software component like the Zend Framework compensates for this change of behavior in mysql as of version 5.6. Does anyone know if more recent versions of Zend take care of this bug?

Thanks for any guidance or advice. Sorry for the length of this.

Regards,

Paul

--
Paul D. Boyle, Ph. D.
Manager, X-ray Facility
Department of Chemistry
Western University
London, ON N6A 5B7
Canada