Column 'date' cannot be null

[unicorn]

Could you add this patch to the rcube_imap and method add_message_cache? It's solution when incoming spam hasn't valid timestamp.

    	$timestamp = $headers->timestamp;
        	if (!date('Ymd', $timestamp)) {
        		$timestamp = time();
        	}
        	
            $this->db->query(
                "INSERT INTO ".get_table_name('messages').
                " (user_id, del, cache_key, created, idx, uid, subject, ".
                $this->db->quoteIdentifier('from').", ".
                $this->db->quoteIdentifier('to').", ".
                "cc, date, size, headers, structure)".
                " VALUES (?, 0, ?, ".$this->db->now().", ?, ?, ?, ?, ?, ?, ".
                $this->db->fromunixtime($timestamp).", ?, ?, ?)",
                $_SESSION['user_id'],
                $key,
                $index,
                $headers->uid,
                (string)mb_substr($this->db->encode($this->decode_header($headers->subject, true)), 0, 128),
                (string)mb_substr($this->db->encode($this->decode_header($headers->from, true)), 0, 128),
                (string)mb_substr($this->db->encode($this->decode_header($headers->to, true)), 0, 128),
                (string)mb_substr($this->db->encode($this->decode_header($headers->cc, true)), 0, 128),
                (int)$headers->size,
                serialize($this->db->encode(clone $headers)),
                is_object($struct) ? serialize($this->db->encode(clone $struct)) : NULL
			);
        }

[alec]

Invalid Date header shouldn't be an issue. Could you provide imap_debug log and sample message? Additional info about your environment would be useful.

[unicorn]

This e-mail cause MySQL error.

[unicorn]

Replying to alec:

Invalid Date header shouldn't be an issue. Could you provide imap_debug log and sample message? Additional info about your environment would be useful.

I attached the file with content, which can't be inserted to the MySQL table messages. MySQL return Column 'date' cannot be null, when try insert to the table.

[alec]

No error in my environment. Roundcube date parsing function converts the date to 2011-04-04. So, we need imap_debug to make sure what your IMAP server returns to the Roundcube. Additional info like PHP version IMAP server version, etc. would be helpful.

[alec]

You can also check sql_debug to find how exactly looks the query.

[unicorn]

IMAP + Error log

[alec]

Now I see that the problem is with "FROM_UNIXTIME(4110652800)". This function returns NULL for out-of-range values. So, the fix is as follows:

--- rcube_mdb2.old      2011-02-28 17:48:21.000000000 +0100
+++ rcube_mdb2.php      2011-04-06 10:29:01.000000000 +0200
@@ -570,15 +570,7 @@
      */
     function fromunixtime($timestamp)
     {
-        switch($this->db_provider) {
-            case 'mysqli':
-            case 'mysql':
-            case 'sqlite':
-                return sprintf("FROM_UNIXTIME(%d)", $timestamp);
-
-            default:
-                return date("'Y-m-d H:i:s'", $timestamp);
-        }
+        return date("'Y-m-d H:i:s'", $timestamp);
     }

[unicorn]

It seems fixed.

[alec]

Applied in [6491fbdb].