Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

SQL Error when registering new user

NewtexNewtex New
edited October 2017 in Vanilla 2.0 - 2.8

Trying out a fresh install of 2.3, and everything seems to have been working fine. However, when I try to register a new user I get this error message:

MySQL Version is
mysql Ver 14.14 Distrib 5.6.16, for debian-linux-gnu (x86_64) using EditLine wrapper

Comments

  • Something is definitely borked. Just by entering the role edit page I get this:

    `Fatal Error in PHP.trigger_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 ' p.Name as Name from GDN_c c left join GDN_p p on c. = p.ID' at line 1
    The error occurred on or near: /var/www/forum.example.com/library/database/class.database.php
    408: // If we get here then the pdo statement prepared properly.
    409: break;
    410:
    411: } catch (Gdn_UserException $uex) {
    412: trigger_error($uex->getMessage(), E_USER_ERROR);
    413: } catch (Exception $ex) {
    414: list($state, $code, $message) = $PDO->errorInfo();
    415:
    416: // If the error code is consistent with a disconnect, attempt to retry
    Backtrace:
    /var/www/forum.example.com/library/database/class.database.phpPHP::Gdn_ErrorHandler();
    [/var/www/forum.example.com/library/database/class.database.php:412] PHP::trigger_error();
    [/var/www/forum.example.com/library/database/class.sqldriver.php:1663] Gdn_Database->query();
    [/var/www/forum.example.com/library/database/class.sqldriver.php:614] Gdn_SQLDriver->query();
    [/var/www/forum.example.com/applications/dashboard/models/class.permissionmodel.php:591] Gdn_SQLDriver->get();
    [/var/www/forum.example.com/applications/dashboard/models/class.permissionmodel.php:707] PermissionModel->getJunctionPermissions();
    [/var/www/forum.example.com/applications/dashboard/models/class.permissionmodel.php:755] PermissionModel->getPermissions();
    [/var/www/forum.example.com/applications/dashboard/controllers/class.rolecontroller.php:145] PermissionModel->getPermissionsEdit();
    [/var/www/forum.example.com/applications/dashboard/controllers/class.rolecontroller.php:145] RoleController->edit();
    [/var/www/forum.example.com/library/core/class.dispatcher.php:326] PHP::call_user_func_array();
    [/var/www/forum.example.com/index.php:44] Gdn_Dispatcher->dispatch();
    Variables in local scope:
    [Sql] 'select distinct p.ID as ID, c., p.Name as Name
    from GDN_c c
    left join GDN_p p on c. = p.ID'
    [InputParameters] array (
    )
    [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 \' p.Name as Name
    from GDN_c c
    left join GDN_p p on c. = p.ID\' at line 1'
    [uex] array (
    )
    Need Help?
    If you are a user of this website, you can report this message to a website administrator.
    If you are an administrator of this website, you can get help at the Vanilla Community Forums.
    Additional information for support personnel:
    Application: Vanilla
    Application Version: 2.3.1
    PHP Version: 5.6.26-0+deb8u1
    Operating System: Linux
    Server Software: Apache/2.4.10 (Debian)
    Referer: http://forum.example.com/dashboard/role
    User Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/61.0.3163.100 Safari/537.36 OPR/48.0.2685.39
    Request Uri: /role/edit/4
    Controller: PHP
    Method: trigger_error`

  • It only happens after importing data from Vanilla Porter 2.3 export. A completely fresh forum doesn't have this problem. So something in the data is causing this.

  • whu606whu606 I'm not a SuperHero; I just like wearing tights... MVP

    Which forum software are you importing from?

    If it is a Vanilla DB you should not be using the porter.

  • It was from Vanilla DB.

    I tried upgrading the forum software previously but got the same SQL error, so I figured a clean DB install would help.

    I got it working now by overwriting the Role and Permission tables with values that exist after clean install. So something in those tables was causing the upgrade/import to break the forum.

  • x00x00 MVP
    edited October 2017

    you shouldn't use the porter at all for upgrading.

    grep is your friend.

  • the porter lack proper schema, roles and permission sensitive to typing. for instance null vs empty string.

    It has left forums wide open before.

    Always run the update after any change.

    grep is your friend.

  • whu606whu606 I'm not a SuperHero; I just like wearing tights... MVP

    See this discussion:

    https://open.vanillaforums.com/discussion/comment/249689#Comment_249689

    Essentially:

    Change conf.php to point to your old DB

    Clear ini files from cache

    Run utility update.

  • I think something was wrong in the old DB. For some reason, that error was accepted in the old installation, but when upgrading it caused fatal issues. Even in the old installation, permissions and user roles were behaving wonky, with "Require users to confirm their email addresses" not working as expected, nor did permissions for members.

    In any case, after doing a clean install, manual data transfer and cleanup of tables, everything seems to be working now.

Sign In or Register to comment.