[Solved] any category update generates BONK : Incorrect integer value: '' for column 'Archived'*

ivanhoe · January 2012

I have successfully installed Vanilla 2.0.18 on a W2003 server running IIS6. Any time I want to update a category, I get a BONK. Details follow:

<?xml version="1.0" encoding="utf-8"?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

Fatal Error

Fatal Error in Gdn_Database.Query();

Incorrect integer value: '' for column 'Archived' at row 1

update GDN_Category Category set 
 `UrlCode` = :UrlCode,
 `Archived` = :Archived,
 `Name` = :Name,
 `Description` = :Description,
 `UpdateUserID` = :UpdateUserID,
 `DateUpdated` = :DateUpdated,
 `AllowDiscussions` = :AllowDiscussions
where CategoryID = :CategoryID

The error occurred on or near: C:\forums\community\library\database\class.database.php

276:

277:          if (!is_object($PDOStatement)) {

278:             trigger_error(ErrorMessage('PDO Statement failed to prepare', $this->ClassName, 'Query', $this->GetPDOErrorMessage($this->Connection()->errorInfo())), E_USER_ERROR);

279:          } else if ($PDOStatement->execute($InputParameters) === FALSE) {

280:             trigger_error(ErrorMessage($this->GetPDOErrorMessage($PDOStatement->errorInfo()), $this->ClassName, 'Query', $Sql), E_USER_ERROR);

281:          }

282:       } else {

283:          $PDOStatement = $this->Connection()->query($Sql);

284:       }

Backtrace:

C:\forums\community\library\database\class.database.phpPHP::Gdn_ErrorHandler();

[C:\forums\community\library\database\class.database.php:280] PHP::trigger_error();

[C:\forums\community\library\database\class.sqldriver.php:1650] Gdn_Database->Query();

[C:\forums\community\library\database\class.sqldriver.php:1619] Gdn_SQLDriver->Query();

[C:\forums\community\library\core\class.model.php:276] Gdn_SQLDriver->Put();

[C:\forums\community\applications\vanilla\models\class.categorymodel.php:885] Gdn_Model->Update();

[C:\forums\community\library\core\class.form.php:1742] CategoryModel->Save();

[C:\forums\community\applications\vanilla\controllers\class.settingscontroller.php:401] Gdn_Form->Save();

[C:\forums\community\applications\vanilla\controllers\class.settingscontroller.php:401] SettingsController->EditCategory();

[C:\forums\community\library\core\class.dispatcher.php:322] PHP::call_user_func_array();

[C:\forums\community\index.php:53] Gdn_Dispatcher->Dispatch();

Variables in local scope:

[Sql] 'update GDN_Category Category set 
 `UrlCode` = :UrlCode,
 `Archived` = :Archived,
 `Name` = :Name,
 `Description` = :Description,
 `UpdateUserID` = :UpdateUserID,
 `DateUpdated` = :DateUpdated,
 `AllowDiscussions` = :AllowDiscussions
where CategoryID = :CategoryID'

[InputParameters] array (
  ':UrlCode' => 'general',
  ':Archived' => false,
  ':Name' => 'Général',
  ':Description' => 'Discussions générales',
  ':UpdateUserID' => '1',
  ':DateUpdated' => '2012-01-03 22:17:07',
  ':AllowDiscussions' => '1',
  ':CategoryID' => '1',
)

[Options] array (
  'ReturnType' => NULL,
)

[ReturnType] NULL

[PDOStatement] array (
  'queryString' => 'update GDN_Category Category set 
 `UrlCode` = :UrlCode,
 `Archived` = :Archived,
 `Name` = :Name,
 `Description` = :Description,
 `UpdateUserID` = :UpdateUserID,
 `DateUpdated` = :DateUpdated,
 `AllowDiscussions` = :AllowDiscussions
where CategoryID = :CategoryID',
)

Need Help?

If you are a user of this website, you can report this message to a website administrator.

If you are an administrator of this website, you can get help at the Vanilla Community Forums.

Additional information for support personnel:

Application: Vanilla
Application Version: 2.0.18.1
PHP Version: 5.3.8
Operating System: WINNT
Server Software: Microsoft-IIS/6.0
Referer: http://forum.lynxonline.com/community/index.php?p=/vanilla/settings/editcategory/1
User Agent: Mozilla/5.0 (Windows NT 5.1) AppleWebKit/535.7 (KHTML, like Gecko) Chrome/16.0.912.63 Safari/535.7
Request Uri: /community/index.php?p=/vanilla/settings/editcategory/1
Controller: Gdn_Database
Method: Query

x00 · March 2012

Todd said:
You can try setting that command in your config.

$Configuration['Database']['ConnectionOptions']                = array(
                                                                  12 => FALSE, //PDO::ATTR_PERSISTENT => FALSE,
                                                                  1000 => TRUE, // PDO::MYSQL_ATTR_USE_BUFFERED_QUERY is missing in some php installations
                                                                  1002 => "set names 'utf8'; set sql_mode='TRADITIONAL'" // PDO::MYSQL_ATTR_INIT_COMMAND is missing in PHP 5.3, so I use the actual value "1002" instead
                                                               );

just a correction it should be

1002 => "set names 'utf8', sql_mode='TRADITIONAL'

multi statements are not permitted for MYSQL_ATTR_INIT_COMMAND as a security precaution.

422 · January 2012

Have u got pdo extension installed?

Check your php.ini if not speak with your host

Todd · January 2012

I think your MySQL is in strict mode. Get it out of that baloney.

ivanhoe · January 2012

Yes indeed, our MySQL server is configured with:
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION".
But it is already used by different production applications in Java which require STRICT MODE, so I can't change that.
Do you know if I can change MySQL mode for Vanilla (or PHP ?) only, and how ?
If not, I can still install another MySQL server on the same machine, but it will need to listen to another port than the standard 3306. Is it possible to configure the MySQL port used by Vanilla, and how?

Todd · January 2012

Vanilla needs non-strict mode, yes.

You can specify the port in the host name seperated by a colon like localhost:3306 or you can add a config key in config.php like:

$Configuration['Database']['Port'] = 3306;

ivanhoe · January 2012

I set up another MySQL service on the same server with no strict mode and everything works fine. Thanks. However, I think the documentation should be updated, as strict mode is the default in MySQL Server Windows installer.

x00 · January 2012

@Todd

http://stackoverflow.com/questions/6975351/php-pdo-how-can-i-set-sql-mode-while-using-pdo

ivanhoe · January 2012

is this a suggestion to change the code rather than setting up another MySQL instance? If yes, where should I put this code, and are you sure it will only affects Vanilla and not other Java applications running on the same server in strict mode?

williamjay · March 2012

MySQL has Global and Session settings for each Instance, so definitely you can SET the server_mode variable for each session separately, without creating a new Server Instance.

Todd · March 2012

You can try setting that command in your config.

$Configuration['Database']['ConnectionOptions']                = array(
                                                                  12 => FALSE, //PDO::ATTR_PERSISTENT => FALSE,
                                                                  1000 => TRUE, // PDO::MYSQL_ATTR_USE_BUFFERED_QUERY is missing in some php installations
                                                                  1002 => "set names 'utf8'; set sql_mode='TRADITIONAL'" // PDO::MYSQL_ATTR_INIT_COMMAND is missing in PHP 5.3, so I use the actual value "1002" instead
                                                               );

x00 · March 2012

Todd said:
You can try setting that command in your config.

$Configuration['Database']['ConnectionOptions']                = array(
                                                                  12 => FALSE, //PDO::ATTR_PERSISTENT => FALSE,
                                                                  1000 => TRUE, // PDO::MYSQL_ATTR_USE_BUFFERED_QUERY is missing in some php installations
                                                                  1002 => "set names 'utf8'; set sql_mode='TRADITIONAL'" // PDO::MYSQL_ATTR_INIT_COMMAND is missing in PHP 5.3, so I use the actual value "1002" instead
                                                               );

just a correction it should be

1002 => "set names 'utf8', sql_mode='TRADITIONAL'

multi statements are not permitted for MYSQL_ATTR_INIT_COMMAND as a security precaution.

cocomang · January 2014

I have just installed a new version of vanilla and I have exactly the same issue.

The only difference is the version:

Application: Vanilla
Application Version: 2.0.18.10
PHP Version: 5.3.24
Operating System: WINNT
Server Software: Microsoft-IIS/7.5
Referer: http://localhost/vanilla/index.php?p=/vanilla/settings/editcategory/10
User Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:26.0) Gecko/20100101 Firefox/26.0
Request Uri: /vanilla/index.php?p=/vanilla/settings/editcategory/10
Controller: Gdn_Database
Method: Query

I have tried all the solutions above:

I have modified the conf\config.php

$Configuration['Database']['ConnectionOptions'] = array(
12 => FALSE, //PDO::ATTR_PERSISTENT => FALSE,
1000 => TRUE, // PDO::MYSQL_ATTR_USE_BUFFERED_QUERY is missing in some php installations
1002 => "set names 'utf8', sql_mode='TRADITIONAL'" // PDO::MYSQL_ATTR_INIT_COMMAND is missing in PHP 5.3, so I use the actual value "1002" instead
);
I have created a file my.ini in c:\Program Files\MySQL\MySQL Server 5.6 with the content:
[mysqld]
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Of course I've restarted the sql server, but the modifications have no effect !

Any clue?

Kelx · April 2021

MySQL has had a strict mode since 5.1, but in 5.7 it became the default. If you've spun up a server recently and all of a sudden your apps have broken, this may be the source.

You have to had the root privileges.

Edit a config file /etc/mysql/my.cnf OR search for my.cnf file

Under [mysqld] section put the line.

[mysqld]
sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Restart MySQL: /etc/init.d/mysql restart