Error No. [0x01F4] when clicking junk folder

[kallisti5]

For some reason in 1.1 whenever I click the junk folder (imap) it is empty when it contains mail, or i get the dreaded:
"
Service Unavailable
Error No. [0x01F4]
"

The error logs show the following SQL error over and over:

"
[Native code: 1064]
[Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), ?, ?, ?)' at line 3]
 in /var/www/mail/htdocs/program/include/rcube_mdb2.inc on line 265
[09-Jul-2008 09:27:28 -0500] DB Error: _doQuery: [Error message: Could not execute statement]
[Last executed query: PREPARE MDB2_STATEMENT_mysql_7ef2e0df4b33a023e2ca48a69edbdab52b3e77826 FROM 'INSERT INTO messages\n         (user_id, del, cache_key, created, idx, uid, subject, `from`, `to`, cc, date, size, headers, structure)\n         VALUES (?, 0, ?, now(), ?, ?, ?, ?, ?, ?, FROM_UNIXTIME(), ?, ?, ?)']
"


those question marks look bad...


I also see:
"
 [Native code: 1048] [Native message: Column 'idx' cannot be null]  in /var/www/mail/htdocs/program/include/rcube_mdb2.inc on line 529
[20-Jun-2008 16:42:20 -0500] DB Error: MDB2 Error: null value violates not-null constraint Query: _doQuery: [Error message: Could not execute statement] [Last executed query: EXECUTE MDB2_STATEMENT_mysql_7257015ea470c2db69b776934c1f58d127fe511570 USING @0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10]
"

any ideas?

[Makc666]

I think it will be hard to track.
 
I also have near the same...

[Tue Sep  9 13:39:04 2008] [error] PHP Notice:  _doQuery: [Error message: Could not execute statement]\n[Last executed query: PREPARE MDB2_STATEMENT_mysql_8f6dcd5b94093c0aaceaebb5599de63846b4845f6 FROM 'INSERT INTO messages\\n         (user_id, del, cache_key, created, idx, uid, subject, `from`, `to`, cc, date, size, headers, structure)\\n         VALUES (?, 0, ?, now(), ?, ?, ?, ?, ?, ?, FROM_UNIXTIME(), ?, ?, ?)']\n[Native code: 1064]\n[Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), ?, ?, ?)' at line 3]\n in /www/program/include/bugs.inc on line 95

[Tue Sep  9 13:39:49 2008] [error] PHP Notice:  _doQuery: [Error message: Could not execute statement]\n[Last executed query: PREPARE MDB2_STATEMENT_mysql_c9e988f3740ece2d9525ec70461a950f5346da237 FROM 'INSERT INTO messages\\n         (user_id, del, cache_key, created, idx, uid, subject, `from`, `to`, cc, date, size, headers, structure)\\n         VALUES (?, 0, ?, now(), ?, ?, ?, ?, ?, ?, FROM_UNIXTIME(), ?, ?, ?)']\n[Native code: 1064]\n[Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), ?, ?, ?)' at line 3]\n in /www/program/include/bugs.inc on line 95

[Tue Sep  9 13:40:11 2008] [error] PHP Notice:  _doQuery: [Error message: Could not execute statement]\n[Last executed query: PREPARE MDB2_STATEMENT_mysql_86a4851c4e1c064e9dbef78c7a1bc41437132add7 FROM 'INSERT INTO messages\\n         (user_id, del, cache_key, created, idx, uid, subject, `from`, `to`, cc, date, size, headers, structure)\\n         VALUES (?, 0, ?, now(), ?, ?, ?, ?, ?, ?, FROM_UNIXTIME(), ?, ?, ?)']\n[Native code: 1064]\n[Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), ?, ?, ?)' at line 3]\n in /www/program/include/bugs.inc on line 95

[Tue Sep  9 13:40:42 2008] [error] PHP Notice:  _doQuery: [Error message: Could not execute statement]\n[Last executed query: PREPARE MDB2_STATEMENT_mysql_e6b07f044db4874a5a1a9d9fb1e64d4ce9b805cd7 FROM 'INSERT INTO messages\\n         (user_id, del, cache_key, created, idx, uid, subject, `from`, `to`, cc, date, size, headers, structure)\\n         VALUES (?, 0, ?, now(), ?, ?, ?, ?, ?, ?, FROM_UNIXTIME(), ?, ?, ?)']\n[Native code: 1064]\n[Native message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), ?, ?, ?)' at line 3]\n in /www/program/include/bugs.inc on line 95

[Makc666]

As I see the problem is in:

Code: [Select]

program/include/rcube_imap.inc

    else  // insert new record
      {
      $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($headers->timestamp).", ?, ?, ?)",
        $_SESSION['user_id'],
        $key,
        $index,
        $headers->uid,
        (string)substr($this->decode_header($headers->subject, TRUE), 0, 128),
        (string)substr($this->decode_header($headers->from, TRUE), 0, 128),
        (string)substr($this->decode_header($headers->to, TRUE), 0, 128),
        (string)substr($this->decode_header($headers->cc, TRUE), 0, 128),
        (int)$headers->size,
        serialize($headers),
        is_object($struct) ? serialize($struct) : NULL
        );
      }
    }

[Makc666]

The query sent to DB is:
INSERT INTO messages (user_id, del, cache_key, created, idx, uid, subject, `from`, `to`, cc, date, size, headers, structure) VALUES (?, 0, ?, now(), ?, ?, ?, ?, ?, ?, FROM_UNIXTIME(), ?, ?, ?)
 
And the problematic code is:

Code: [Select]

$this->db->fromunixtime($headers->timestamp)

[Makc666]

Finally I got those 3 (three) "bad" emails from mail server that caused that error.
 
Thinking that I have to submit a BUG for them

[Makc666]

And finally found out the reason of the problem:
 
Bad emails have bad YEAR:

Code: [Select]

Date: Wed, 13 Aug [COLOR=red]3609[/COLOR] 15:40:46 -0400
 
If we remove YEAR or change to proper one like:

Code: [Select]

Date: Wed, 13 Aug [COLOR=darkgreen]2008[/COLOR] 15:40:46 -0400
 
The error dissapears.

[rosali]

Yes, submit a ticket @ trac.roundcube.net, please.

[Makc666]

Found out how to fix for 0.1.1
 
Really there is a bug (a misprint in coding)...
 
File:
program/include/rcube_mdb2.inc
 
Code:
  function fromunixtime($timestamp)
    {
    switch($this->db_provider)
      {
      case 'mysqli':
      case 'mysql':
      case 'sqlite':
        return "FROM_UNIXTIME($timestamp)";
 
      default:
        return date("'Y-m-d H:i:s'", $timestamp);
      }
    }
 
Problem line:
return "FROM_UNIXTIME($timestamp)";
 
Must be:
return sprintf("FROM_UNIXTIME(%d)", $timestamp);
 
Saying the truth if we look into file:
program/include/rcube_db.inc
 
We will see that there is the same function there:
  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);
      }
    }
 
But as you can see the "problem" line like in previous function is OK.
 
return sprintf("FROM_UNIXTIME(%d)", $timestamp);

[Makc666]

Yes, submit a ticket @ trac.roundcube.net, please.

Done:
#1485332 (Service Unavailable - Error No. [0x01F4] -> return "FROM_UNIXTIME($timestamp)"