v1.5.5d database.php - Incorrect datetime value: 'null'

[nothos]

Hi folks,
I've just updated both of my sites and ran into a problem with the one that has Easy populate installed.

When trying to change something (I was changing a quantity) in an item i would be taken to an error page (which I didn't take a screenshot of in my haste to fix) when pressing save. It was something along the lines of " an error occured - please refresh the page"

My logs were a bit more informative: (and no, my ip address doesn't have x's in it, and that is not my real admin directory)

[31-Dec-2016 04:51:35 UTC] Request URI: /xxxadmin/product.php?cPath=28&product_type=1&pID=1764&action=update_product&page=1, IP address: xxx.xxx.73.154
#1 trigger_error() called at [/home/glassfus/public_html/includes/classes/db/mysql/query_factory.php:167]
#2 queryFactory->show_error() called at [/home/glassfus/public_html/includes/classes/db/mysql/query_factory.php:139]
#3 queryFactory->set_error() called at [/home/glassfus/public_html/includes/classes/db/mysql/query_factory.php:266]
#4 queryFactory->Execute() called at [/home/glassfus/public_html/xxxadmin/includes/functions/database.php:53]
#5 zen_db_perform() called at [/home/glassfus/public_html/xxxadmin/includes/modules/update_product.php:93]
#6 require(/home/glassfus/public_html/xxxadmin/includes/modules/update_product.php) called at [/home/glassfus/public_html/xxxadmin/product.php:62]

[31-Dec-2016 04:51:35 UTC] PHP Fatal error: 1292:Incorrect datetime value: 'null' for column 'products_date_available' at row 1 :: update products set products_quantity = '0', products_type = '1', products_model = 'Fuse-Oth-Brass-200', products_price = '7.4400', products_date_available = 'null', products_weight = '0.1', products_status = '0', products_virtual = '0', products_tax_class_id = '0', manufacturers_id = '0', products_quantity_order_min = '1', products_quantity_order_units = '1', products_priced_by_attribute = '0', product_is_free = '0', product_is_call = '0', products_quantity_mixed = '0', product_is_always_free_shipping = '0', products_qty_box_status = '1', products_quantity_order_max = '0', products_sort_order = '1234', products_discount_type = '0', products_discount_type_from = '0', products_price_sorter = '7.4400', products_image = 'supplies/brassshim.jpg', products_last_modified = now(), master_categories_id = '28' where products_id = '1764' ==> (as called by) /home/glassfus/public_html/xxxadmin/includes/functions/database.php on line 53 <== in /home/glassfus/public_html/includes/classes/db/mysql/query_factory.php on line 167

It seemed to have an issue with 'null' for the date avaliable, and I tried setting a real date in there, but the same error was thrown.

Looking at a beyond compare of my old 1.5.5.b database.php and the new one, the only differences were the copyright date, the version number and two refrences to 'null' which had been changed to 'NULL'

This may be deliberate to cope with new php versions (I'm stuck on 5.6 until I get one more little thing sorted, everything else is ready for 7) or it could just be a cleanup typo.... either way, I'm back being able to edit product info by changing the two lines in the new database .php back to 'null' and I thought this may be handy for someone else.

Cheers
Ash Williamson

[mc12345678]

Which easy populate program revealed this issue?

It is my understanding that part of the reason for the null to NULL change is related to situations where a person's name could be null. An unfortunate thing for computer's and various programs, but ZC has been making changes in the direction to be able to manage the possibility of such a name.

There is also a new "datatype" that has been added for software that takes advantage either attempting or not to handle a string that could expectedly contain the word null as compared to one that shouldn't treat the string any different than any other.

As to this modification that has been independently made, it sounds to me like the problem is in the applicable EP program and not in ZC.

[DrByte]

nothos,

You're correct. It's a bug. The change was made in the interest of bringing the Admin back in line with the non-admin code. But it's a bit premature to do that in v155 without also changing a dozen other product-edit-related files, all of which are often also altered by plugins.
I've reviewed older notes about why this wasn't done to the admin initially, and this is why.
I'm a bit embarrassed that the testing didn't bear this out, but it turns out the testing server had a relaxed MySQL mode enabled for some other tests and hadn't been reset properly, so this got missed.
Sigh. Happy new year!

The quickest fix is to revert the /admin/includes/functions/database.php file to what was in v155b, by changing those 2 'NULL' back to 'null' in zen_db_perform(), lines 26 and 42.

          case 'NULL':

becomes

          case 'null':

[mc12345678]

nothos,

You're correct. It's a bug. The change was made in the interest of bringing the Admin back in line with the non-admin code. But it's a bit premature to do that in v155 without also changing a dozen other product-edit-related files, all of which are often also altered by plugins.
I've reviewed older notes about why this wasn't done to the admin initially, and this is why.
I'm a bit embarrassed that the testing didn't bear this out, but it turns out the testing server had a relaxed MySQL mode enabled for some other tests and hadn't been reset properly, so this got missed.
Sigh. Happy new year!

The quickest fix is to revert the /admin/includes/functions/database.php file to what was in v155b, by changing those 2 'NULL' back to 'null' in zen_db_perform(), lines 26 and 42.

          case 'NULL':

becomes

          case 'null':

So the "error" only occurs when the mysql database is (or is not) set a certain way? I've run both Easypopulate V4 and inserted as well as modified a product each without having a products_date_available and did not have any errors.

Fresh install of ZC 1.5.5.d, default products, Easy Populate V4.0.36.ZC installed, PHP 7.0.14, MySQL 5.6.34-log

[DrByte]

The various admin files for saving product data (update_product.php, etc etc) in the various product types treat an unspecified date as 'null' when sending the query to the database. All those files use lowercase 'null' in their call to zen_db_perform, so if zen_db_perform only recognizes uppercase 'NULL' then an error will be thrown if MySQL is in strict mode (which is the default for modern MySQL versions).

[SPython]

The various admin files for saving product data (update_product.php, etc etc) in the various product types treat an unspecified date as 'null' when sending the query to the database. All those files use lowercase 'null' in their call to zen_db_perform, so if zen_db_perform only recognizes uppercase 'NULL' then an error will be thrown if MySQL is in strict mode (which is the default for modern MySQL versions).

Yep - we have just encountered the same bug. Thanks for the fix.