Blog Documentation Book a Demo
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

Vanilla Doesn't Work Well in MySQL Strict Mode

schketschket
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:
«1

Comments

  • hbfhbf MVP
    what is the fix for this?

    Vanilla Wiki, Tastes Great! 31,000 viewers can't be wrong. || Plugin Development Explained

  • LincLinc Admin
    Thanks @schket, I filed a bug report https://github.com/vanillaforums/Garden/issues/1218
  • hbfhbf MVP
    what is the fix for this?
    the quick fix is to remove STRICT_TRANS_TABLES from the mysql config.

    Vanilla Wiki, Tastes Great! 31,000 viewers can't be wrong. || Plugin Development Explained

  • UnderDogUnderDog MVP
    I filed a bug report
    Thanks Lincoln, I wish more users would do that with their problems.

    There was an error rendering this rich post.

  • rscottrscott
    edited December 2011

    Do you guys really write code against a non-strict MySql db? Isn't that a bit hackish and dangerous (for this reason alone?)

  • ToddTodd Vanilla Staff

    We really do write code against non-strict MySql. It is the way MySQL seems to be installed by default.

  • rscottrscott

    Sorry for hijacking this convo btw - I've had many many issues and once I found this and turned off strict, they all went away. I had issues ranging from activating themes to setting up roles and users to setting custom category access. My MySQL defaulted to strict. Seems like programming against strict would cut out issues that you could potentially prevent. Not to mention making your SQL more compliant for when/if you ever determine to support MSSQL or other DB servers.

    Anyway, sorry for the mini-rant. :)

  • ToddTodd Vanilla Staff
    edited December 2011

    I take your point and I think that the two MySQL modes are something we didn't know too much about when developing Vanilla. We had installed Vanilla on a tonne of different setups and webhosts without them having MySQL strict too. I was made aware of the mode maybe sometime this summer and I tried going through the app a bit, but you know how things get a little too far gone once a significant amount of programming has occurred.

    I think the thing is php itself is sort of like an unstrict MySQL. What I mean is:

    '' == FALSE == 0 == NULL
'Anything' == TRUE == 1

    To me, this is a good thing because at some point most data on the web is coming in as a string. I've learned to love the way php works, even though it drove me crazy as a 'hacker' language when I first started with it. Working with unstrict MySQL sort of dovetails into php naturally this way.

    So there you go, this is how I stopped worrying and learned to love unstrict MySQL.

  • IvanSukharevIvanSukharev

    Hi all. I am new here.
    And i was made next fix.

    open file
    applications/dashboard/controllers/class.profilecontroller.php

    find line 769, or
    public function Save($FormPostValues, $Settings = FALSE) {

    add new line after {
    insert next code
    $FormPostValues['Banned'] = (integer)($FormPostValues['Banned']);

    maybe this is not good solution, but it works for me.

  • dan33410dan33410 New

    Hi guys. After a few hours of working on this, I still can't figure out the work around. Anyone have any suggestions? All of the suggestions I have found in the community don't seem to work for me.

  • GaryFunkGaryFunk ✭✭

    The fix is easy. In MySQL.ini

    find:

    sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

    change it to:

    sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

  • dan33410dan33410 New

    Thanks Gary, I tried that and it is still throwing that error. I can't register anyone.

  • GaryFunkGaryFunk ✭✭

    Did you restart the MySQL service?

  • dan33410dan33410 New
    edited January 2013

    Yes Gary. It is really perplexing. I thought for sure that would have been it.

  • phill895phill895 New

    I had the same problem, and have tried this fix and its worked, and its also fixed a problem i was having with one of the other moduals as well

  • cocomangcocomang New

    I just want to start a new forum and I was wondering if vanilla could fit my needs. I was trying to manage user and give moderator rights (in dashboard /manage users) and I get this error:
    Incorrect integer value: '' for column 'Banned' at row 1.

    I read that the solution is to set sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
    but I am new to mysql, how and where can I do that?

    I have vanilla 2-0-18-10 and mysql 5.6.14
    By the way It's quite disappointing to see that the problem is here since November 2011 and it's not solved yet.

  • LincLinc Admin

    It's not a "problem" slated to be fixed. We don't support strict mode.

  • peregrineperegrine MVP
    edited January 2014

    I have vanilla 2-0-18-10 and mysql 5.6.14 By the way It's quite disappointing to see that the problem is here since November 2011 and it's not solved yet.

    Actually kind of disappointing to see you disappointed when it is solved!!!!! You just have to read and follow instructions.

    but there is a solution if you read the comment closely. and then you google mysql.ini to learn about google and mysql and mysql.ini and setting mysql parameters, which is not vanilla.

    I am new to mysql

    there is a mysql forum as well.

    http://vanillaforums.org/discussion/comment/173675/#Comment_173675

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • cocomangcocomang New

    How could you say it is solved ?
    A solution is proposed here in this thread, but it's not explained how to do it. :\
    I have no idea how to set sql-mode in mysql.ini as there is no mysql.ini or my.ini file anywhere on my computer.

    I suggest that the correct solution for this problem could be one of:

    1. stop putting empty strings in integer values
    2. check if sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" is set at installation and indicate how to solve it if it's not the case
    3. propose support for an other database

    Unfortunately you don't get my point. I am currently evaluating which forum software I will use between vanilla, phpbb, mybb, smf for my new forum. I have just installed fresh version of mysql and each of the forum software I have just mention and I am checking all the basic features I need.

    A very basic feature like giving moderators rights does not work with a fresh installation of vanilla.

  • cocomangcocomang New

    FYI

    I found a real fix that works without playing with mysql config here: https://github.com/iignatov/Garden/commit/b65498d61795a6922f2cb1b4b2cf27000efccf82

Sign In or Register to comment.