Cool Guy Yo Cool Guy Yo - 6 months ago 8
PHP Question

Database Error:00' for column 'date' at row 1

I have just finished installing anchor-cms but when I go to view the admin page or the homepage I get the following output

Unhandled Exception

Message:

Database Error:00' for column 'date' at row 1


SQL: INSERT INTO `sessions` (`id`, `date`, `data`) values (?, ?, ?)
Location:

/Library/WebServer/Documents/anchor/system/database/connection.php on line 128
Stack Trace:

#0 /Library/WebServer/Documents/anchor/system/database/query.php(254): System\Database\Connection->execute('INSERT INTO `se...', Array)
#1 /Library/WebServer/Documents/anchor/system/session/database.php(42): System\Database\Query->insert(Array)
#2 /Library/WebServer/Documents/anchor/system/session/payload.php(78): System\Session\Database->save(Array, Array, false)
#3 [internal function]: System\Session\Payload->save()
#4 /Library/WebServer/Documents/anchor/system/session.php(58): call_user_func_array(Array, Array)
#5 /Library/WebServer/Documents/anchor/system/bootstrap.php(124): System\Session::__callStatic('save', Array)
#6 /Library/WebServer/Documents/anchor/system/bootstrap.php(124): System\Session::save()
#7 /Library/WebServer/Documents/anchor/index.php(33): require('/Library/WebSer...')
#8 {main}


Here is my connection.php file

<?php namespace System\Database;

/**
* Nano
*
* Lightweight php framework
*
* @package nano
* @author k. wilson
* @link http://madebykieron.co.uk
*/

use System\Config;
use PDO, PDOStatement, PDOException, Exception;

class Connection {

public $pdo, $config, $queries = array();

public function __construct(PDO $pdo, $config) {
$this->pdo = $pdo;
$this->config = $config;
}

public function transaction($callback) {
$this->pdo->beginTransaction();

// After beginning the database transaction, we will call the callback
// so that it can do its database work. If an exception occurs we'll
// rollback the transaction and re-throw back to the developer.
try {
call_user_func($callback);
}
catch(PDOException $e) {
$this->pdo->rollBack();

throw $e;
}

$this->pdo->commit();
}

public function query($sql, $bindings = array()) {
$sql = trim($sql);

list($statement, $result) = $this->execute($sql, $bindings);

// The result we return depends on the type of query executed against the
// database. On SELECT clauses, we will return the result set, for update
// and deletes we will return the affected row count.
if(stripos($sql, 'select') === 0 or stripos($sql, 'show') === 0) {
return $this->fetch($statement, Config::get('database.fetch'));
}
elseif(stripos($sql, 'update') === 0 or stripos($sql, 'delete') === 0) {
return $statement->rowCount();
}
// For insert statements that use the "returning" clause, which is allowed
// by database systems such as Postgres, we need to actually return the
// real query result so the consumer can get the ID.
elseif (stripos($sql, 'insert') === 0 and stripos($sql, 'returning') !== false) {
return $this->fetch($statement, Config::get('database.fetch'));
}
else {
return $result;
}
}

public function first($sql, $bindings = array()) {
list($statement, $result) = $this->execute($sql, $bindings);

if($result) return $statement->fetch(Config::get('database.fetch'));
}

public function column($sql, $bindings = array()) {
list($statement, $result) = $this->execute($sql, $bindings);

if($result) return $statement->fetchColumn();
}

public function type($var) {
if(is_null($var)) {
return PDO::PARAM_NULL;
}

if(is_int($var)) {
return PDO::PARAM_INT;
}

if(is_bool($var)) {
return PDO::PARAM_BOOL;
}

return PDO::PARAM_STR;
}

public function execute($sql, $bindings = array()) {
// Each database operation is wrapped in a try / catch so we can wrap
// any database exceptions in our custom exception class, which will
// set the message to include the SQL and query bindings.
try {
$statement = $this->pdo->prepare($sql);

// bind paramaters by data type
// test key to see if we have to bump the index by one
$zerobased = (strpos(key($bindings), ':') === 0) ? false : true;

foreach($bindings as $index => $bind) {
$key = $zerobased ? ($index + 1) : $index;

$statement->bindValue($key, $bind, $this->type($bind));
}

$start = microtime(true);

$result = $statement->execute();

$this->queries[] = array($statement->queryString, $bindings);
}
// If an exception occurs, we'll pass it into our custom exception
// and set the message to include the SQL and query bindings so
// debugging is much easier on the developer.
catch(PDOException $exception) {
$message = explode(':', $exception->getMessage());

$error = '<strong>Database Error:</strong>' . end($message) . str_repeat("\n", 3) .
'<strong>SQL: </strong>' . $sql;

$exception = new Exception($error, 0, $exception);

throw $exception;
}

return array($statement, $result);
}

protected function fetch($statement, $style) {
// If the fetch style is "class", we'll hydrate an array of PHP
// stdClass objects as generic containers for the query rows,
// otherwise we'll just use the fetch style value.
if($style === PDO::FETCH_CLASS) {
return $statement->fetchAll(PDO::FETCH_CLASS, 'stdClass');
}
else {
return $statement->fetchAll($style);
}
}

}


Second Error

Database Error: Invalid datetime format: 1292 Incorrect datetime value: '2013-02-19T06:47:59+00:00' for column 'date' at row 1

Answer

Disclaimer: technically this is not an answer ;)

Apparently the anchor-CMS uses the ISO 8601-format to store (at least) the session-date, which is apparently not safe to use, especially when the "strict mode" is active!

I think for the moment it's probably the best to disable the strict mode of MySQL. If you don't "own" the SQL-Server you can disable the strict mode for a single session.

You can do that by editing system/database.php starting at line 51. Change the following lines from

if(version_compare(PHP_VERSION, '5.3.6', '<=')) {
    $options[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES ' . $config['charset'];
}

to

if(version_compare(PHP_VERSION, '5.3.6', '<=')) {
    $options[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES ' . $config['charset']
        . ', sql_mode=\'ALLOW_INVALID_DATES\'';
} else {
    $options[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET sql_mode=\'ALLOW_INVALID_DATES\'';
}

I think that should work until anchor-CMS uses the correct dates (I probably make a pull-request for that this evening after work).

Comments