vanillaforums/Garden — Issue #1218: 'Banned' checkbox fails in MySQL strict mode (2.0.18)
Author: lincolnwebs
Created: 2011-11-14 04:16:50+00:00
State: open
Labels: Bug

http://vanillaforums.org/discussion/17730/sql-update-query-fails-when-editing-user

PHP Version 5.2.17, MySQL server 5.5.15, SQL mode set to strict:
STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

On "Manage Users" dashboard page when I modify some user with "Banned" checkbox unchecked, I get the message:
Incorrect integer value: '' for column 'Banned' at row 1|Gdn_Database|
Query|update GDN_User User set
Email = :Email, Name = :Name, ShowEmail = :ShowEmail,
DateUpdated = :DateUpdated, UpdateIPAddress = :UpdateIPAddress,
Banned = :Banned where UserID = :UserID
which obviously means that value bound to :Banned parameter is empty instead of integer, as expected by MySQL in strict mode. When saving user with "Banned" checkbox checked, update works fine (because checkbox value = 1).

Strict mode can be checked out either by adding to my.ini, section [mysqld] the setting:
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
or by issuing the query after connecting to the database:
SET SESSION sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'