Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

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

ivanhoeivanhoe New
edited January 2012 in Vanilla 2.0 - 2.8

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



Best Answer

  • x00x00 MVP
    Answer ✓

    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.

    grep is your friend.

Answers

  • 422422 Developer MVP

    Have u got pdo extension installed?

    Check your php.ini if not speak with your host

    There was an error rendering this rich post.

  • ToddTodd Chief Product Officer Vanilla Staff

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

  • 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?

  • ToddTodd Chief Product Officer Vanilla Staff

    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;
    
  • 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.

  • 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?

  • 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.

  • ToddTodd Chief Product Officer Vanilla Staff

    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
                                                                   );
    
  • x00x00 MVP
    Answer ✓

    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.

    grep is your friend.

  • 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:

    1. 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
      );

    2. 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?

  • KelxKelx New
    edited 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

Sign In or Register to comment.