Fork me on GitHub
Current releases are 2.1.4 (30 Oct 2014) and 2.0.18.13 (5 Aug 2014)

Ready for 2.1? Find out if your plugins are compatible. 2.0 will no longer be updated after Dec 2014.

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.