Running updater causes Uncaught PDOException

[mkools]

What are the steps to reproduce this issue?

1. Install Debian 9.x with PHP 7.2 (NGINX with PHP 7.2 FPM).
2. Copy v1.x release of SB++ over the Sourcebans 1.4.11 directory.
3. Run /updater/ to update the 1.4.11 installation to SB++

What happens?

Running the updater throws 2 errors, first is a PHP error:

Warning: Use of undefined constant SB_EMAIL - assumed 'SB_EMAIL' (this will throw an Error in a future version of PHP) in /var/www/html/sbpp/init.php on line 155

Next it's throwing a PDOException:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0 COMMENT '1 - Mute, 2 - Gag', ureason text, PRIMARY KEY (`b' at line 15 in /var/www/html/sbpp/includes/Database.php:70 Stack trace: #0 /var/www/html/sbpp/includes/Database.php(70): PDOStatement->execute() #1 /var/www/html/sbpp/updater/data/475.php(29): Database->execute() #2 /var/www/html/sbpp/updater/Updater.php(75): require_once('/var/www/html/s...') #3 /var/www/html/sbpp/updater/Updater.php(23): Updater->update() #4 /var/www/html/sbpp/updater/index.php(32): Updater->__construct(Object(Database)) #5 {main} thrown in /var/www/html/sbpp/includes/Database.php on line 70

What were you expecting to happen?

I expected the updater to run so I can update my 1.4.11 installation to SB++.

Any logs, error output, etc.?

NGINX logs show this:

2018/05/16 05:37:52 [error] 6419#6419: *116053 FastCGI sent in stderr: "PHP message: PHP Warning: Use of undefined constant SB_EMAIL - assumed 'SB_EMAIL' (this will throw an Error in a future version of PHP) in /var/www/html/sbpp/init.php on line 155
PHP message: PHP Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0 COMMENT '1 - Mute, 2 - Gag',
ureason text,
PRIMARY KEY (`b' at line 15 in /var/www/html/sbpp/includes/Database.php:70
Stack trace:
#0 /var/www/html/sbpp/includes/Database.php(70): PDOStatement->execute()
#1 /var/www/html/sbpp/updater/data/475.php(29): Database->execute()
#2 /var/www/html/sbpp/updater/Updater.php(75): require_once('/var/www/html/s...')
#3 /var/www/html/sbpp/updater/Updater.php(23): Updater->update()
#4 /var/www/html/sbpp/updater/index.php(32): Updater->__construct(Object(Database))
#5 {main}

Any other comments?

Clean installation seems to work out fine, it's just the upgrade that's failing.

What versions of software are you using?

Operating System: Debian 9.4
SourceBans++ Version: Github latest version (v1.x branch).

PHP Version: PHP 7.2.5-1+020180505045740.21+stretch1.gbpca2fa6
MySQL Version: 10.1.26-MariaDB
Link to your project: https://mgftw.com/sbpp/
Link to a phpinfo() output: https://mgftw.com/pi.php

SourceMod Version:
MetaMod Version:

[rumblefrog]

For the first error, you need to define SB_EMAIL within config.php

I need to play around with the second error, because you are upgrading from a such old version

[mkools]

Ok so I fixed it by first upgrading to 1.4.12, then to 1.5.0F and then to the latest version, 1.6.3 and that worked! :)

There's only one issue left, I've noticed that you can only logon through Steam API now, but for some reason that's failing. It takes me back to the Sourcebans page but I'm not logged in as admin. Any idea what could cause that? Let me know if I need to open another issue for that.

Here's the PHP error:

2018/05/16 12:24:03 [error] 12561#12561: *51870 FastCGI sent in stderr: "PHP message: PHP Warning: count(): Parameter must be an array or an object that implements Countable in /var/www/html/sourcebans/steamopenid.php on line 79" while reading response header from upstream, client: 185.107.83.100, server: bans.mgftw.com, request: "GET /steamopenid.php?openid.ns=http%3A%2F%2Fspecs.openid.net%2Fauth%2F2.0&openid.mode=id_res&openid.op_endpoint=https%3A%2F%2Fsteamcommunity.com%2Fopenid%2Flogin&openid.claimed_id=https%3A%2F%2Fsteamcommunity.com%2Fopenid%2Fid%2F76561197994619757&openid.identity=https%3A%2F%2Fsteamcommunity.com%2Fopenid%2Fid%2F76561197994619757&openid.return_to=https%3A%2F%2Fbans.mgftw.com%2Fsteamopenid.php&openid.response_nonce=2018-05-16T12%3A24%3A02Z85JS4M2aXr2a9fgkEgTQbzOoIuY%3D&openid.assoc_handle=1234567890&openid.signed=signed%2Cop_endpoint%2Cclaimed_id%2Cidentity%2Creturn_to%2Cresponse_nonce%2Cassoc_handle&openid.sig=qFqEYBKjvaC1y6ccSMvvs4Quxkg%3D HTTP/1.1", upstream: "fastcgi://unix:/var/run/php/php7.2-fpm.sock:", host: "bans.mgftw.com", referrer: "https://steamcommunity.com/openid/login?openid.ns=http%3A%2F%2Fspecs.openid.net%2Fauth%2F2.0&openid.mode=checkid_setup&openid.return_to=https%3A%2F%2Fbans.mgftw.com%2Fsteamopenid.php&openid.realm=https%3A%2F%2Fbans.mgftw.com&openid.ns.sreg=http%3A%2F%2Fopenid.net%2Fextensions%2Fsreg%2F1.1&openid.claimed_id=http%3A%2F%2Fspecs.openid.net%2Fauth%2F2.0%2Fidentifier_select&openid.identity=http%3A%2F%2Fspecs.openid.net%2Fauth%2F2.0%2Fidentifier_select"

[Groruk]

@mkools you can enable normal login by setting config.enablesteamlogin to 1 in the _settings table in your SourceBans database.

And your error would suggest that this count() hits either a false or null which would mean that the SteamID that tried to login isn't in your database.

[rumblefrog]

Make sure the Steam ID is in the 0's universe

Due to SB's ancient and unrealistic way of converting:

sourcebans-pp/web/steamopenid.php

Lines 58 to 65 in 536d306

	function convert64to32(Database $dbs, $communityID)
	{
	$query = "SELECT CONCAT(\"STEAM_0:\", (CAST(':communityID' AS UNSIGNED) - CAST('76561197960265728' AS UNSIGNED)) % 2, \":\", CAST(((CAST(':communityID' AS UNSIGNED) - CAST('76561197960265728' AS UNSIGNED)) - ((CAST(':communityID' AS UNSIGNED) - CAST('76561197960265728' AS UNSIGNED)) % 2)) / 2 AS UNSIGNED)) AS steam_id";
	$query = str_replace(':communityID', $communityID, $query);
	$dbs->query($query);
	$steamid = $dbs->single();
	return $steamid['steam_id'];
	}

[mkools]

Thanks guys everything works perfect now, indeed changing STEAM_1: to 0 fixed the issue with the Steam logons.

Thanks for all the help!