Fatal error: 1048:Column 'value' cannot be null :: insert into sessions (sesskey, ex

[mesnitu]

Well, with the site now online, today I got 3 of this logs.

...nothing happens, but here they are.
I've made a upgrade from 151 to 155.... perhaps its best to truncate this table ? Perhaps some "garbage" is there ?

Thanks

[04-Jun-2016 23:04:24 Europe/Lisbon] 
Request URI: /index.php?main_page=product_bookx_info&cPath=34_119&products_id=1692&zenid=5h5d8rl9dpq0ijuhajjuqbmtm5, IP address: 82.155.133.129
#1  trigger_error() called at [/home/xxxxxx/xxxxxx/includes/classes/db/mysql/query_factory.php:167]
#2  queryFactory->show_error() called at [/home/xxxxxx/xxxxxx/includes/classes/db/mysql/query_factory.php:139]
#3  queryFactory->set_error() called at [/home/xxxxxx/xxxxxx/includes/classes/db/mysql/query_factory.php:266]
#4  queryFactory->Execute() called at [/home/xxxxxx/xxxxxx/includes/functions/sessions.php:69]
#5  _sess_write()
#6  session_write_close() called at [/home/xxxxxx/xxxxxx/includes/application_bottom.php:16]
#7  require(/home/xxxxxx/xxxxxx/includes/application_bottom.php) called at [/xxxxxx/xxxxxx/public_html/index.php:105]

[04-Jun-2016 23:04:24 Europe/Lisbon] 
PHP Fatal error:  1048:Column 'value' cannot be null :: insert into sessions (sesskey, expiry, `value`)
            values ('5h5d8rl9dpq0ijuhajjuqbmtm5', 1465079304, null)
            ON DUPLICATE KEY UPDATE `value`=null, expiry=1465079304 ==> (as called by) /home/xxxxxx/xxxxxx/includes/functions/sessions.php on line 69 <== in /home/xxxxxx/xxxxxx/includes/classes/db/mysql/query_factory.php on line 167

[lat9]

Circling back on this one.

Since a session is not normally created for spiders (under the control of Configuration->Sessions->Prevent Spider Sessions), if a store's SQL mode is set for STRICT_TRANS_TABLES, then the $val passed into the _sess_write function (present in /includes/functions/sessions.php) is going to be null when a spider's session has ended.

Examining the database class' bindVars method, using a string bindType can result in a null value being returned, which is a "bad thing" in this case, since the `value` field can't be null per the sessions database-table's configuration.

  function _sess_write($key, $val) {
    global $db;
    if (!is_object($db)) return;
    $val = base64_encode($val);

    global $SESS_LIFE;
    $expiry = time() + $SESS_LIFE;

    $sql = "insert into " . TABLE_SESSIONS . " (sesskey, expiry, `value`)
            values (:zkey, :zexpiry, :zvalue)
            ON DUPLICATE KEY UPDATE `value`=:zvalue, expiry=:zexpiry";
    $sql = $db->bindVars($sql, ':zkey', $key, 'string');
    $sql = $db->bindVars($sql, ':zexpiry', $expiry, 'integer');
    $sql = $db->bindVars($sql, ':zvalue', $val, 'string');
    $result = $db->Execute($sql);

    return (!empty($result) && !empty($result->resource));
  }

I suggest changing that function to use the stringIgnoreNull type instead, since that bindType will always result in a quoted string:

  function _sess_write($key, $val) {
    global $db;
    if (!is_object($db)) return;
    $val = base64_encode($val);

    global $SESS_LIFE;
    $expiry = time() + $SESS_LIFE;

    $sql = "insert into " . TABLE_SESSIONS . " (sesskey, expiry, `value`)
            values (:zkey, :zexpiry, :zvalue)
            ON DUPLICATE KEY UPDATE `value`=:zvalue, expiry=:zexpiry";
    $sql = $db->bindVars($sql, ':zkey', $key, 'string');
    $sql = $db->bindVars($sql, ':zexpiry', $expiry, 'integer');
    $sql = $db->bindVars($sql, ':zvalue', $val, 'stringIgnoreNull');
    $result = $db->Execute($sql);

    return (!empty($result) && !empty($result->resource));
  }

[Website Rob]

Might want to flesh that out a bit. I tried your code with MySQL in STRICT_TRANS_TABLES mode and got a broken page with the error msg.

var-type undefined: stringIgnoreNull

[mc12345678]

Circling back on this one.

Since a session is not normally created for spiders (under the control of Configuration->Sessions->Prevent Spider Sessions), if a store's SQL mode is set for STRICT_TRANS_TABLES, then the $val passed into the _sess_write function (present in /includes/functions/sessions.php) is going to be null when a spider's session has ended.

Examining the database class' bindVars method, using a string bindType can result in a null value being returned, which is a "bad thing" in this case, since the `value` field can't be null per the sessions database-table's configuration.

  function _sess_write($key, $val) {
    global $db;
    if (!is_object($db)) return;
    $val = base64_encode($val);

    global $SESS_LIFE;
    $expiry = time() + $SESS_LIFE;

    $sql = "insert into " . TABLE_SESSIONS . " (sesskey, expiry, `value`)
            values (:zkey, :zexpiry, :zvalue)
            ON DUPLICATE KEY UPDATE `value`=:zvalue, expiry=:zexpiry";
    $sql = $db->bindVars($sql, ':zkey', $key, 'string');
    $sql = $db->bindVars($sql, ':zexpiry', $expiry, 'integer');
    $sql = $db->bindVars($sql, ':zvalue', $val, 'string');
    $result = $db->Execute($sql);

    return (!empty($result) && !empty($result->resource));
  }

I suggest changing that function to use the stringIgnoreNull type instead, since that bindType will always result in a quoted string:

  function _sess_write($key, $val) {
    global $db;
    if (!is_object($db)) return;
    $val = base64_encode($val);

    global $SESS_LIFE;
    $expiry = time() + $SESS_LIFE;

    $sql = "insert into " . TABLE_SESSIONS . " (sesskey, expiry, `value`)
            values (:zkey, :zexpiry, :zvalue)
            ON DUPLICATE KEY UPDATE `value`=:zvalue, expiry=:zexpiry";
    $sql = $db->bindVars($sql, ':zkey', $key, 'string');
    $sql = $db->bindVars($sql, ':zexpiry', $expiry, 'integer');
    $sql = $db->bindVars($sql, ':zvalue', $val, 'stringIgnoreNull');
    $result = $db->Execute($sql);

    return (!empty($result) && !empty($result->resource));
  }

I've been trying to understand this since I read it several hours ago. I'm guessing that some amount of testing went into it, but I've been having difficulty following the above discussion as I have tried to lay out below even if the solution stops the error that was reported/described.

Not sure if it's the description of how this occurs or the recommended solution that doesn't seem right, but if the $val of null is passed into _sess_write (as a non-string), then base64_encode(null) is going to error and $val is not going to get set, and presumably a different error message would result. If the incoming $val is "null" (as a string) then it would get base64_encoded and therefore the result would not include the preg_match string of NULL because it would be encoded and the storage of that encoded value would not cause an error because it would be a string.

The next thing is that in order for the bindVars function to see $val = "null" would be that the $val passed into the function would have to have been the base64_decode of "null" which is not the same as passing null into the _sess_write function (pointing out that the above describes the condition differently).

Working back from that (the fact that the $val going to bindVars would contain the text of 'NULL'), the val coming into the function would have to be base64_decode('NULL') (instead of just null as described above), which doesn't exactly make sense unless that is some sort of default behavior of the session not having been started.

The other thing though is that if SESSION_FORCE_COOKIE_USE is set to true and a cookie becomes setable, then the session could be started and the whole spiders block thing doesn't do anything.

Why though would it be desired to save a null session value if the session was never started (assuming spiders are ignored and cookie use is not forced to true) and based on the expected action of not storing/maintaining a session and not expecting the session to be restored? Wouldn't the expectation be that both the read and write session code get rewritten to bypass that null condition/situation? In the write function before encoding the value take an action like, if $val == base64_decode('NULL') then return;

and on _sess_read add $value->fields['value'] != 'NULL' to the test of whether to base64_decode the value or not, though that would mean that the entire session got stored as 'NULL' rather than as an encoded series of data pairs using key | value similar to:

securityToken|s:32:"00000000000000000000000000000000";customers_host_address|s:12:"xx.xx.xxx.xx";ip_blocker_pass|b:0;cartID|s:0:"";cart|O:12:"shoppingCart":11:{s:8:"contents";a:0:{}s:5:"total";i:0;s:6:"weight";i:0;s:12:"content_type";b:0;s:18:"free_shipping_item";i:0;s:20:"free_shipping_weight";i:0;s:19:"free_shipping_price";i:0;s:14:"download_count";i:0;s:22:"total_before_discounts";i:0;s:22:"display_debug_messages";b:0;s:23:"flag_duplicate_msgs_set";b:0;}check_valid|s:4:"true";navigation|O:17:"navigationHistory":2:{s:4:"path";a:1:{i:0;a:4:{s:4:"page";s:12:"product_info";s:4:"mode";s:6:"NONSSL";s:3:"get";a:3:{s:5:"cPath";s:1:"0";s:11:"products_id";s:1:"3";s:5:"zenid";s:32:"00000000000000000000000000000000";}s:4:"post";a:0:{}}}s:8:"snapshot";a:4:{s:4:"page";s:12:"product_info";s:4:"mode";s:6:"NONSSL";s:3:"get";a:3:{s:5:"cPath";s:1:"0";s:11:"products_id";s:1:"3";s:5:"zenid";s:32:"00000000000000000000000000000000";}s:4:"post";a:0:{}}}language|s:7:"english";languages_id|s:1:"1";languages_code|s:2:"en";currency|s:3:"USD";today_is|s:10:"2017-01-07";updateExpirations|b:1;session_counter|b:1;customers_ip_address|s:12:"xx.xx.xxx.xx";

I mean, I think I can see where the suggestion of using the ignore null version of bindVars could be a good idea, but I don't see that it was fully/accurately described in the previous message.

Might want to flesh that out a bit. I tried your code with MySQL in STRICT_TRANS_TABLES mode and got a broken page with the error msg.

var-type undefined: stringIgnoreNull

Do/did you have the appropriate version 1.5.5 of ZC installed? The datatype was added in 1.5.5a with preparation to transition to using 'NULL' in zen_db_input, but that was rolled back from version c to version d take 2 which was issued a few days after the first notification of version d.

in includes/classes/db/mysql/query_factory.php there is a section within the function getBindVarValue() related to strings with the following:

      case 'string':
        if (preg_match('/NULL/', $value)) return 'null';
        return '\'' . $this->prepare_input($value) . '\'';
      break;
      case 'stringIgnoreNull':
        return '\'' . $this->prepare_input($value) . '\'';
        break;
      case 'noquotestring':
        return $this->prepare_input($value);
      break;

The first provides the results described as needing to be "undone".

Previously there were just two string related actions:

      case 'string':
        return '\'' . $this->prepare_input($value) . '\'';
      break;
      case 'noquotestring':
        return $this->prepare_input($value);
      break;

[Website Rob]

Here is more info to go by.

Apache 2.4
MySQL 5.6 - Mode: STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION
PHP 5.6.29
Zen Cart 1.5.5

[07-Jan-2017 23:05:19 America/Denver] Request URI: /***-c-24/***-c-24_29/***-p-371.html, IP address: ***
#1  trigger_error() called at [/***/***/public_html/includes/classes/db/mysql/query_factory.php:167]
#2  queryFactory->show_error() called at [/***/***/public_html/includes/classes/db/mysql/query_factory.php:139]
#3  queryFactory->set_error() called at [/***/***/public_html/includes/classes/db/mysql/query_factory.php:266]
#4  queryFactory->Execute() called at [/***/***/public_html/includes/functions/sessions.php:69]
#5  _sess_write()
#6  session_write_close() called at [/***/***/public_html/includes/application_bottom.php:16]
#7  require(/***/***/public_html/includes/application_bottom.php) called at [/***/***/public_html/index.php:105]

[07-Jan-2017 23:05:19 America/Denver] PHP Fatal error:  1048:Column 'value' cannot be null :: insert into zen_sessions (sesskey, expiry, `value`)
            values ('469db6ea8476d9b0150fc3551463e107', 1483856959, null)
            ON DUPLICATE KEY UPDATE `value`=null, expiry=1483856959 ==> (as called by) /***/***/public_html/includes/functions/sessions.php on line 69 <== in /***/***/public_html/includes/classes/db/mysql/query_factory.php on line 167

Although I do believe the above are default files, I'd like to mention that Dual Pricing module has been installed. Not sure which version exactly and it has been modified a bit by other coders.

[mc12345678]

Here is more info to go by.

Apache 2.4
MySQL 5.6 - Mode: STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION
PHP 5.6.29
Zen Cart 1.5.5

[07-Jan-2017 23:05:19 America/Denver] Request URI: /***-c-24/***-c-24_29/***-p-371.html, IP address: ***
#1  trigger_error() called at [/***/***/public_html/includes/classes/db/mysql/query_factory.php:167]
#2  queryFactory->show_error() called at [/***/***/public_html/includes/classes/db/mysql/query_factory.php:139]
#3  queryFactory->set_error() called at [/***/***/public_html/includes/classes/db/mysql/query_factory.php:266]
#4  queryFactory->Execute() called at [/***/***/public_html/includes/functions/sessions.php:69]
#5  _sess_write()
#6  session_write_close() called at [/***/***/public_html/includes/application_bottom.php:16]
#7  require(/***/***/public_html/includes/application_bottom.php) called at [/***/***/public_html/index.php:105]

[07-Jan-2017 23:05:19 America/Denver] PHP Fatal error:  1048:Column 'value' cannot be null :: insert into zen_sessions (sesskey, expiry, `value`)
            values ('469db6ea8476d9b0150fc3551463e107', 1483856959, null)
            ON DUPLICATE KEY UPDATE `value`=null, expiry=1483856959 ==> (as called by) /***/***/public_html/includes/functions/sessions.php on line 69 <== in /***/***/public_html/includes/classes/db/mysql/query_factory.php on line 167

Although I do believe the above are default files, I'd like to mention that Dual Pricing module has been installed. Not sure which version exactly and it has been modified a bit by other coders.

Requester information has been obscured. Please identify if was a "personal" search or if was a spider to the best of your determination.

[Website Rob]

Not sure what you mean by "Requester information has been obscured" as an error msg. is an error msg.

To answer your question though, I can confirm it was a Google Bot.

[lat9]

Might want to flesh that out a bit. I tried your code with MySQL in STRICT_TRANS_TABLES mode and got a broken page with the error msg.

var-type undefined: stringIgnoreNull

That's because you're running ZC 1.5.5; the stringIgnoreNull value was added in Zen Cart 1.5.5a's /includes/classes/db/mysql/query_factory.php. You can safely use the ZC 1.5.5d version of that file on your 1.5.5 store.

[Website Rob]

Would like to point out some confusion though as this thread is about a "cannot be null" error in 1.5.5 and pretty close to what to I was experiencing. Was also not aware of the code change made in 1.5.5a till mentioned by mc12345678. I didn't see a Zen Version version number in your post on page 2 about using the "stringIgnoreNull" so not sure what version you were referring to for the change. Will try what you suggest though and see what results will be.

I also appreciate your help in the other, unrelated matter and glad you got it resolved so quickly.

[lat9]

Would like to point out some confusion though as this thread is about a "cannot be null" error in 1.5.5 and pretty close to what to I was experiencing. Was also not aware of the code change made in 1.5.5a till mentioned by mc12345678. I didn't see a Zen Version version number in your post on page 2 about using the "stringIgnoreNull" so not sure what version you were referring to for the change. Will try what you suggest though and see what results will be.

I also appreciate your help in the other, unrelated matter and glad you got it resolved so quickly.

Rob, the issue is exactly what's being experienced on the site you manage (and you're welcome!).