MySQL error during install of Vanilla 2.6

Sealord

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

Linc

Disable MySQL's Strict Mode to proceed.

Sealord

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

mschiff

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.

mschiff

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

mschiff

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}

mschiff

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_J

@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

mschiff

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.

mschiff

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 (
)

mschiff

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.

Sealord

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_J

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

Sealord

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