Fork me on GitHub
Critical: Update to 2.1.8, released 15 Jan.

Vanilla Doesn't Work Well in MySQL Strict Mode

edited December 2011 in Feedback
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'
Tagged:

Comments

Sign In or Register to comment.