Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Try Vanilla Forums Cloud product
Vanilla 2.6.1 is here with critical security patches. One of them has been publicly disclosed.

MySQL error during install of Vanilla 2.6

I get this error when I hit continue button in the Version 2.6 Installer

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Admin = ?' at line 4

MySQL version is 8.0.11.0

Comments

  • LincLinc Director of Development Detroit Vanilla Staff
    edited June 3

    Disable MySQL's Strict Mode to proceed.

  • Strict Mode is/was disabled, no success with Vanilla 2.6
    Vanilla 2.5.2 works on my server.

  • Clean installation of Vanilla 2.6 on Windows/IIS, exact same problem. Strict Mode is disabled (sql-mode = "NO_ENGINE_SUBSTITUTION", is this OK?). MySQL version is 8.0. I also have Debug=true in my config.php; not sure if it matters at this point. Total noob here; any suggestion would be greatly appreciated.

  • I tried the Vanilla 2.5.2 Installer and I get the exact same error.

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Admin = ?' at line 4

  • I enabled debug and got this:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Admin = ?' at line 4
    ## C:\vanilla-core-2-6\library\database\class.database.php(421)
    #0 [internal function]: gdn_ErrorHandler(256, 'You have an err...', 'C:\vanilla-core...', 421, Array)
    #1 C:\vanilla-core-2-6\library\database\class.database.php(421): trigger_error('You have an err...', 256)
    #2 C:\vanilla-core-2-6\library\database\class.sqldriver.php(1684): Gdn_Database->query('select *\nfrom G...', Array, Array)
    #3 C:\vanilla-core-2-6\library\database\class.sqldriver.php(902): Gdn_SQLDriver->query('select *\nfrom G...')
    #4 C:\vanilla-core-2-6\applications\dashboard\settings\structure.php(138): Gdn_SQLDriver->getWhere('User', Array)
    #5 C:\vanilla-core-2-6\applications\dashboard\controllers\class.setupcontroller.php(251): include('C:\vanilla-core...')
    #6 C:\vanilla-core-2-6\applications\dashboard\controllers\class.setupcontroller.php(89): SetupController->configure()
    #7 C:\vanilla-core-2-6\library\core\class.dispatcher.php(845): SetupController->index()
    #8 C:\vanilla-core-2-6\library\core\class.dispatcher.php(274): Gdn_Dispatcher->dispatchController(Object(Gdn_Request), Array)
    #9 C:\vanilla-core-2-6\index.php(30): Gdn_Dispatcher->dispatch()
    #10 {main}

  • I found that this line in applications\dashboard\settings\structure.php was failing:

    $SystemUserID = Gdn::sql()->getWhere('User', ['Name' => 'System', 'Admin' => 2])->value('UserID');
    

    So I commented out some code in that file, knowing that there was no system user yet:

    if (!$SystemUserID) {
        // Try and find a system user.
    //    $SystemUserID = Gdn::sql()->getWhere('User', ['Name' => 'System', 'Admin' => 2])->value('UserID');
    //    if ($SystemUserID) {
    //        saveToConfig('Garden.SystemUserID', $SystemUserID);
    //    } else {
            // Create a new one if we couldn't find one.
            Gdn::userModel()->getSystemUserID();
    //    }
    }
    

    This allowed me to get a little further (404, addons not found, etc). The installation had already created two database tables before my change, but the gdn_user table was empty. After the change, several users were added to that table, including my system user.

    The question is, what in that statement made it throw an error? And will other statements have the same problem? It's connecting to the database, and many statements succeed. What is different about that one? Is it somehow incompatible with some new default setting in MySQL 8.0.11.0 where other statements are not? Is the new version throwing errors instead of warnings or something like that? Any insight would be appreciated.

  • R_JR_J Cheerleader & Troubleshooter Munich Moderator

    @mschiff said:
    I found that this line in applications\dashboard\settings\structure.php was failing:

    $SystemUserID = Gdn::sql()->getWhere('User', ['Name' => 'System', 'Admin' => 2])->value('UserID');
    

    So I commented out some code in that file, knowing that there was no system user yet:

    Could you try changing it to

    ... 'Admin' => '2'...

    I would also assume that it is a Strict Mode problem

  • Thanks for your reply. Unfortunately, your suggestion didn't work. I got the exact same error after surrounding the 2 in single quotes. (I did this in 5.2.2, which I was trying as a fallback, but the code at that point is exactly the same.)

    As far as Strict Mode, it is disabled. I even changed sql-mode to sql-mode = "" in my MySQL my.ini, to no avail.

  • A similar error popped up, as I feared. I attempted to change my system user password. This is what I got:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Admin, Password from GDN_User `User` where UserID = ?' at line 1
    The error occurred on or near: C:\vanilla-core-2-6\library\database\class.database.php

    417:                 if (!$message) {
    418:                     $message = $ex->getMessage();
    419:                 }
    420: 
    421:                 trigger_error($message, E_USER_ERROR);
    422:             }
    423: 
    424:         }
    425: 
    

    Backtrace:

    C:\vanilla-core-2-6\library\database\class.database.phpPHP::gdn_ErrorHandler();
    [C:\vanilla-core-2-6\library\database\class.database.php:421] PHP::trigger_error();
    [C:\vanilla-core-2-6\library\database\class.sqldriver.php:1684] Gdn_Database->query();
    [C:\vanilla-core-2-6\library\database\class.sqldriver.php:629] Gdn_SQLDriver->query();
    [C:\vanilla-core-2-6\applications\dashboard\models\class.usermodel.php:3365] Gdn_SQLDriver->get();
    [C:\vanilla-core-2-6\library\core\functions.validation.php:203] UserModel->validateCredentials();
    [C:\vanilla-core-2-6\library\core\class.validation.php:643] PHP::validateOldPassword();
    [C:\vanilla-core-2-6\applications\dashboard\models\class.usermodel.php:3311] Gdn_Validation->validate();
    [C:\vanilla-core-2-6\applications\dashboard\models\class.usermodel.php:2163] UserModel->validate();
    [C:\vanilla-core-2-6\library\core\class.form.php:2736] UserModel->save();
    [C:\vanilla-core-2-6\applications\dashboard\controllers\class.profilecontroller.php:713] Gdn_Form->save();
    [C:\vanilla-core-2-6\library\core\class.dispatcher.php:845] ProfileController->password();
    [C:\vanilla-core-2-6\library\core\class.dispatcher.php:274] Gdn_Dispatcher->dispatchController();
    [C:\vanilla-core-2-6\index.php:29] Gdn_Dispatcher->dispatch();
    

    Variables in local scope:

    [sql] 'select UserID, Name, Attributes, Admin, Password
    from GDN_User `User`
    where UserID = :UserID'
    [inputParameters] array (
      ':UserID' => 2,
    )
    [options] array (
      'Type' => 'select',
      'Slave' => NULL,
      'ReturnType' => 'DataSet',
    )
    [returnType] 'DataSet'
    [tries] 2
    [try] 0
    [pDO] array (
    )
    [pDOStatement] false
    [state] '42000'
    [code] 1064
    [message] 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near \'Admin, Password
    from GDN_User `User`
    where UserID = ?\' at line 1'
    [ex] array (
    )
    
  • OK, I gave up on MySQL. I installed MariaDB instead, rerun the Vanilla installation, and it went smoothly. No more SQL errors. Only the known issue with the vanilla and conversations addons remains.

    The SQL problem is probably caused by one of the new defaults in MySQL 8. I don't have the time (nor the inclination right now) to try and figure out which one. MariaDB 10.3 works for me.

    Thanks for all your help.

    R_J
  • Maybe it has something to do with this one:

    Admin is a reserved keyword in MySQL8
    https://dev.mysql.com/doc/refman/8.0/en/keywords.html

    Admin became nonreserved in next version 8.0.12

    I have also changed to MariaDB now!

    R_JmschiffBleistivtLinc
  • R_JR_J Cheerleader & Troubleshooter Munich Moderator

    Good finding! I've created an issue on GitHub based on that.

    BleistivtLinc
  • MySQL 8.0.12 is released and Vanilla 2.6 installation works now with this version.

    R_JLinc
Sign In or Register to comment.