Error on login/registration with Steam (MySQL 1364)

27 Feb 2017 Edited
#1
This issue is marked as solved
Good day, dear Sire!

When I try to login/register on gsu.echstreme.de (which is my forum, feel free to try it), after putting in my steamguard-code, I end up on the page "http://gsu.echstreme.de/flyover.php?action=register&provider=Steam", showing me this:

"MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
1364 - Field 'usernames' doesn't have a default value
Query:
INSERT INTO mybb_flyover_settings_data (Steam, uid) VALUES ('IDcensored-same-as-below', 4) ON DUPLICATE KEY UPDATE Steam = 'IDcensored-same-as-above'"

The user will be registered, though - even multiple times when I tried. (I created three users with my steam account now).

Would you be so kind to point me in a valid direction for solving that problem?
If you need any accounts or login-data for testing/solving purpose, feel free to contact me via PM or email.

Thank you and have a nice day!
Shade 27 Feb 2017
#2
Thank you for purchasing a Gold subscription and supporting me! I am not at home at the moment but I will help you step-by-step when I am back home.
Glaumy likes this post
Glaumy 27 Feb 2017 Edited
#3
Sure, happy studying!

Btw: When I click on the heart on the lower left of your post (to like as well as to unlike it), it sends me to a new page, just showing this:
{"postId":968,"likeString":"You like this post","templateString":"\n\n\tYou like this post\n<\/span>\n","buttonString":"Unlike"}

Sorry to bring up more work, also, I realize this should be in another section of the forum.
Shade 27 Feb 2017 Edited
#4
Odd that no one reported this earlier. Download the attached file and replace your inc/plugins/flyover.php file with it. Then uninstall the plugin and install it again. If you don't want to lose eventual changes you've made to Flyover's templates and stylesheets, I would recommend saving the theme you are using before doing so and restore it straight after the uninstall/install process.

This *should* solve the issue as it sets an empty default value for the "usernames" property, although it's a blind patch which has not been tested. Let me know how it goes :)

PS: alternatively, you can set an empty default value to the "usernames" field in the _flyover_settings_data table, if you have access to phpMyAdmin or a similar MySQL manager and you are confident enough to edit table fields.
Filename Size Downloads
36.86 KB 1
Glaumy 27 Feb 2017 Edited
#5
Okay...
I changed the default value to NULL (via phpMyAdmin - I could do it via SQL syntax, too, am just too lazy). "Text" fields can not have default values (except for NULL, it seems) in MySQL. Varchars can, though. When I then clicked on "Login with steam", it gave me the error "steam_settings" does not have a default value. (same error as before, just another field).

Then, I downloaded your attached file, added it to the download of Flyover, deactivated the plugin, uninstalled it, uploaded everything again and hit install.
When I then hit the "login with steam" button, I got this:
Direct initialization of this file is not allowed.

Please make sure IN_MYBB is defined.

Now I downloaded Flyover 1.2 completely new, uploaded it where it belongs, uninstalled the plugin, hit "Install and Activate" and instantly got this:
MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
1101 - BLOB/TEXT column 'usernames' can't have a default value
Query:
CREATE TABLE mybb_flyover_settings_data ( uid INT(10) NOT NULL PRIMARY KEY, usernames TEXT NOT NULL DEFAULT '' ) ENGINE=MyISAM CHARACTER SET utf8 COLLATE utf8_general_ci;

Edit: this might somehow be of value, too. Sorry I forgot to add it before:
[Image: TwtK2wb.png]
Shade 27 Feb 2017
#6
Well, I might investigate more on the issue with your PHP and MySQL version then. I am not home again but I will post an update on the issue as soon as I can.
Glaumy 27 Feb 2017 Edited
#7
Sure.

I changed line 210 of inc/plugins/flyover.php to be
usernames VARCHAR(256) NOT NULL DEFAULT ''
. Now it works again, but when I click "login with steam", I get
MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
    1364 - Field 'Steam_settings' doesn't have a default value
Query:
    INSERT INTO mybb_flyover_settings_data (Steam, uid) VALUES ('censored-same-as-below', 8) ON DUPLICATE KEY UPDATE Steam = 'censored-same-as-above'
Now my question is: Why would these fields even need a default value? Should they not be filled? I will switch "Steam_settings" to varchar(256) default '', too and edit this post when it changes anything.


EDIT: That fixed it. while before everytime it registered a new user, no data was filled into the flyover_data table. This allowed multiple registrations with one steamid. I just had to change the username and then it brought me to the errorpage instead of logging me in. Now the data is inserted and I am logged in straight away.

So the solution for you is to change every TEXT to VARCHAR, if a default is needed.

Also, it would be interesting if you could add the feature that a link to the users steam/facebook/whatever page is added to their profile. It should be hidden by default and Users should be able to unhide it or edit it in their profile after registration.

Thanks for your support, I might need some more later. Before, I need to figure out how to get google-login working. :D
And: It seems your earnings bar at the top of this site does not update properly. Before and after my gold subscription it showed 28,2 Yaroos. ;)

Another feedback to the subscription: Thanks for the great plugins. You deserve more than 15 Euros for it, but I totally agree with your policy to keep it affordable. I would be one of those persons that would not be able to afford it easily. And people thinking like me but having more funds can still donate. It is a good solution. :)
Shade likes this post
Shade 27 Feb 2017
#8
Thank you for pointing that out by yourself. This make it a good patch for the upcoming versions. Also thank you for your suggestions and appreciations. I will surely consider them.
Glaumy 27 Feb 2017
#9
Sure, you are welcome, I am happy to help.

I found another problem: Avatars will not be "transported" from steam to MyBB. I even changed the size of the allowed Avatars to 1000px and unlimited in filesize.

I hope it is okay if I keep reporting stuff in this thread. :)
Shade 28 Feb 2017
#10
That's kind of weird. Steam might have changed the endpoint for avatars. I have a Flyover update in mind, but I am still struggling with my uni commitments so it will be hard to push an update soon.
Glaumy likes this post