MyBB Community Forums

I've been using MyBB since v1.2 and I now want to upgrade my forum to v1.8. Only when performing the upgrade locally I'm getting several database errors.

Database error #1

SQL Error:
    1366 - Incorrect integer value: 'no' for column 'isdefault' at row 26
Query:
    ALTER TABLE mybb_settinggroups MODIFY isdefault tinyint(1) NOT NULL default '0' 

It seems that the isdefault field still contained 'no' as value instead of 0. I've updated the values and restarted the upgrade.

Database error #2

SQL Error:
    1366 - Incorrect integer value: 'yes' for column 'candisplaygroup' at row 1
Query:
    ALTER TABLE mybb_usergroups MODIFY isbannedgroup tinyint(1) NOT NULL default '0', MODIFY canview tinyint(1) NOT NULL default '0', MODIFY canviewthreads tinyint(1) NOT NULL default '0', MODIFY canviewprofiles tinyint(1) NOT NULL default '0', MODIFY candlattachments tinyint(1) NOT NULL default '0', MODIFY canviewboardclosed tinyint(1) NOT NULL default '0', MODIFY canpostthreads tinyint(1) NOT NULL default '0', MODIFY canpostreplys tinyint(1) NOT NULL default '0', MODIFY canpostattachments tinyint(1) NOT NULL default '0', MODIFY canratethreads tinyint(1) NOT NULL default '0', MODIFY caneditposts tinyint(1) NOT NULL default '0', MODIFY candeleteposts tinyint(1) NOT NULL default '0', MODIFY candeletethreads tinyint(1) NOT NULL default '0', MODIFY caneditattachments tinyint(1) NOT NULL default '0', MODIFY canpostpolls tinyint(1) NOT NULL default '0', MODIFY canvotepolls tinyint(1) NOT NULL default '0', MODIFY canundovotes tinyint(1) NOT NULL default '0', MODIFY canusepms tinyint(1) NOT NULL default '0', MODIFY cansendpms tinyint(1) NOT NULL default '0', MODIFY cantrackpms tinyint(1) NOT NULL default '0', MODIFY candenypmreceipts tinyint(1) NOT NULL default '0', MODIFY cansendemail tinyint(1) NOT NULL default '0', MODIFY cansendemailoverride tinyint(1) NOT NULL default '0', MODIFY canviewmemberlist tinyint(1) NOT NULL default '0', MODIFY canviewcalendar tinyint(1) NOT NULL default '0', MODIFY canaddevents tinyint(1) NOT NULL default '0', MODIFY canbypasseventmod tinyint(1) NOT NULL default '0', MODIFY canmoderateevents tinyint(1) NOT NULL default '0', MODIFY canviewonline tinyint(1) NOT NULL default '0', MODIFY canviewwolinvis tinyint(1) NOT NULL default '0', MODIFY canviewonlineips tinyint(1) NOT NULL default '0', MODIFY cancp tinyint(1) NOT NULL default '0', MODIFY issupermod tinyint(1) NOT NULL default '0', MODIFY cansearch tinyint(1) NOT NULL default '0', MODIFY canusercp tinyint(1) NOT NULL default '0', MODIFY canuploadavatars tinyint(1) NOT NULL default '0', MODIFY canratemembers tinyint(1) NOT NULL default '0', MODIFY canchangename tinyint(1) NOT NULL default '0', MODIFY canbereported tinyint(1) NOT NULL default '0', MODIFY showforumteam tinyint(1) NOT NULL default '0', MODIFY usereputationsystem tinyint(1) NOT NULL default '0', MODIFY cangivereputations tinyint(1) NOT NULL default '0', MODIFY candisplaygroup tinyint(1) NOT NULL default '0', MODIFY cancustomtitle tinyint(1) NOT NULL default '0', MODIFY canwarnusers tinyint(1) NOT NULL default '0', MODIFY canreceivewarnings tinyint(1) NOT NULL default '0', MODIFY canmodcp tinyint(1) NOT NULL default '0', MODIFY showinbirthdaylist tinyint(1) NOT NULL default '0', MODIFY canoverridepm tinyint(1) NOT NULL default '0', MODIFY canusesig tinyint(1) NOT NULL default '0', MODIFY signofollow tinyint(1) NOT NULL default '0' 

Same issue as #1, but now contains 'yes'. Updated values again and restarted.

Database error #3

SQL Error:
    1406 - Data too long for column 'ip' at row 412
Query:
    ALTER TABLE mybb_sessions MODIFY ip varbinary(16) NOT NULL default '' 

it seems that the sessions table contains IPv6 addresses that have to be removed before the alter table can be executed. If you first change the varbinary to length 40, perform the conversion and change it back to length 16, it would not go wrong ...

As I have updated multiple forums that have been even older than your forum (including community.mybb.com) without such problems I suppose a previous upgrade hasn't been done properly.
Also the upgrade script contains a query that cuts off IP addresses that are too long for the new datatype. I'm not sure why you get error #3 because that's something that worked fine while testing and for everybody else.

About error 1 and 2, I think that might be the case, but I can't remember when the update didn't execute properly.

I've searched for the query that cuts off IP addresses and indeed it's there. Strange that it's not being executed for the sessions table ...

This has been fixed. For error #1 and #2 I've updated the database and when I've upgrade the live forum, it went perfectly fine.

Since, two years later, I just ran into the exact same problems while upgrading from 1.6.13 to 1.8.12, and MyBB seems to have made no progress writing error tolerant upgrade processes in the meantime, here's the list of statements I had to run to finally get the process to end successfully.

Took me a good ten database drops to get there.

update mybb_settinggroups set isdefault = 1 where isdefault = 'yes';
update mybb_settinggroups set isdefault = 0 where isdefault = 'no';
update mybb_usergroups set candisplaygroup = 1 where candisplaygroup = 'yes';
update mybb_usergroups set candisplaygroup = 0 where candisplaygroup = 'no';
update mybb_usergroups set candisplaygroup = 0 where candisplaygroup = '';
update mybb_usergroups set cancustomtitle = 1 where cancustomtitle = 'yes';
update mybb_usergroups set cancustomtitle = 0 where cancustomtitle = 'no';
update mybb_usergroups set cancustomtitle = 0 where cancustomtitle = '';
update mybb_usergroups set attachquota = 0 where attachquota < 0;
update mybb_users set totalpms = 0 where totalpms < 0;
update mybb_users set unreadpms = 0 where unreadpms < 0;
commit;

And no, actually checking whether the column whose type you're going to change contains an invalid value for that would not have been hard.
The MyBB devs just couldn't be arsed.

I just updated from 1.6.5 to 1.8.27 and also had some SQL errors pop up during the update.

First I had to manually modify this line:

update mybb_settinggroups set isdefault = 0 where isdefault = 'no';

and secondly had to manually convert the char set to utf8.

The forum is running again. Only have some minor issues to resolve.