SQL Error when registering new user

Newtex

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

Newtex

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`

Newtex

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.

whu606

Which forum software are you importing from?

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

Newtex

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.

x00

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

x00

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.

whu606

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.

Newtex

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.