MyBB Community Forums

MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
1366 - Incorrect integer value: '' for column 'canmanagecomments' at row 1
Query:
UPDATE mybb_usergroups SET `type`=1, `title`='Guests', `description`='The default group that all visitors are assigned to unless they\'re logged in.', `namestyle`='{username}', `usertitle`='Unregistered', `stars`=0, `starimage`='', `image`='', `isbannedgroup`=0, `canview`=1, `canviewthreads`=1, `canviewprofiles`=1, `candlattachments`=1, `canviewboardclosed`=0, `canpostthreads`=0, `canpostreplys`=0, `canpostattachments`=0, `canratethreads`=0, `modposts`=0, `modthreads`=0, `mod_edit_posts`=0, `modattachments`=0, `caneditposts`=0, `candeleteposts`=0, `candeletethreads`=0, `caneditattachments`=0, `canviewdeletionnotice`=0, `canpostpolls`=0, `canvotepolls`=0, `canundovotes`=0, `canusepms`=0, `cansendpms`=0, `cantrackpms`=0, `candenypmreceipts`=0, `pmquota`=0, `maxpmrecipients`=5, `cansendemail`=0, `cansendemailoverride`=0, `maxemails`=5, `emailfloodtime`=5, `canviewmemberlist`=1, `canviewcalendar`=1, `canaddevents`=0, `canbypasseventmod`=0, `canmoderateevents`=0, `canviewonline`=1, `canviewwolinvis`=0, `canviewonlineips`=0, `cancp`=0, `issupermod`=0, `cansearch`=1, `canusercp`=0, `canuploadavatars`=0, `canchangename`=0, `canbereported`=0, `canchangewebsite`=0, `showforumteam`=0, `usereputationsystem`=0, `cangivereputations`=0, `candeletereputations`=0, `reputationpower`=0, `maxreputationsday`=0, `maxreputationsperuser`=0, `maxreputationsperthread`=0, `attachquota`=0, `cancustomtitle`=0, `canwarnusers`=0, `canreceivewarnings`=0, `maxwarningsday`=0, `canmodcp`=0, `showinbirthdaylist`=0, `canoverridepm`=0, `canusesig`=0, `canusesigxposts`=0, `signofollow`=0, `edittimelimit`=0, `maxposts`=0, `showmemberlist`=0, `canmanageannounce`=0, `canmanagemodqueue`=0, `canmanagereportedcontent`=0, `canviewmodlogs`=0, `caneditprofiles`=0, `canbanusers`=0, `canviewwarnlogs`=0, `canuseipsearch`=0, `canmanagecomments`='', `cansendcomments`='', `caneditowncomments`='', `candeleteowncomments`='', `commentsperday`=0 WHERE gid='1'
Please contact the MyBB Group for technical support.

This came up when I tried to add the Download Attachment permission for user group: Guest.

What is happening, and how can i fix this?

MyBB 1.8.14 latest.

---

Ok i found the solution myself.
This indicates your SQL is running in "strict" mode.

FIX:
Run>%programdata%>MySQL>MySQL Server 5.7 (or whatever ur using).
Open file "my.ini"

You'll see this:
# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Remove the "Strict" orange so it looks like this:
# Set the SQL mode to strict
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

SAVE.

Go into Services > find the running SQL instance ur using > Restart Service

Solved.

For anyone with no access to MySQL is going to need to directly update the php files.

Im getting a similar error ive contacted my webhost they have disabled strict mode, according to them... but the error is still happening. but prior to this fix when id post it would give the mysql error and still post a thread link on the forum. when you clicked the thread link youd get a mysql error. Trying to make a new thread, reply, make new account, all result in this error. 

The host now says strict mode is off but heres my error, any clue on how to fix this error among the other similar mysql errors im getting above?

(2020-01-01, 05:41 PM)vegathechosen Wrote: [ -> ]Im getting a similar error ive contacted my webhost they have disabled strict mode, according to them... but the error is still happening. but prior to this fix when id post it would give the mysql error and still post a thread link on the forum. when you clicked the thread link youd get a mysql error. Trying to make a new thread, reply, make new account, all result in this error. 

The host now says strict mode is off but heres my error, any clue on how to fix this error among the other similar mysql errors im getting above?

Do you have phpMyAdmin installed in your server?

Run following queries to make sure if the strict mode is not set:

SHOW GLOBAL VARIABLES LIKE 'sql_mode';
SHOW SESSION VARIABLES LIKE 'sql_mode';

---

By the way, OP's problem does can be resolved by setting MySQL's strict mode off, but canmanagecomments is not a native MyBB data field in table users. It might be added by some plugin.

just tossing a couple links that might help
https://community.mybb.com/thread-179331.html https://community.mybb.com/thread-114524.html

Thanks for the help all, issue persists but i did run the queries as suggested and this is what popped up.


Your SQL query has been executed successfully.

Your SQL query has been executed successfully.

SHOW GLOBAL VARIABLES LIKE 'sql_mode'


SHOW SESSION VARIABLES LIKE 'sql_mode'



sql_mode NO_ENGINE_SUBSTITUTION
sql_mode NO_ENGINE_SUBSTITUTION

For the first attached preview I recall posthash being dropped some versions ago, is not that the case?

If so, you are probably using old files (core or plugin) or plugins incompatible with your core version.

Second issue should be possible to fix on some ways, one being by setting MySQL's strict mode off as Noyle suggested above.

(2020-01-01, 05:41 PM)vegathechosen Wrote: [ -> ]The host now says strict mode is off but heres my error, any clue on how to fix this error among the other similar mysql errors im getting above?

Sorry I didn't see the attachments clearly, was thinking they were the same as you've posted in the install & upgrade section.

In the first screenshot, I don't quite know how MyBB 1.6 handles the posthash field. Read Omar G. for more info.

In the second screenshot, that twitter field should have been added by a plugin or a manual edit to database, meaning that the error is not caused by MyBB natively.

(2020-01-02, 03:17 AM)Omar G. Wrote: [ -> ]For the first attached preview I recall posthash being dropped some versions ago, is not that the case?

If so, you are probably using old files (core or plugin) or plugins incompatible with your core version.

Second issue should be possible to fix on some ways, one being by setting MySQL's strict mode off as Noyle suggested above.

He is using MyBB 1.6 IIRC.

I'm not sure we can help OP with the MyBB 1.6 version.

Oh, right, he is using 1.6. That being the case I would suggest to update to 1.8 if that is possible for your case.

I attempted to update to 1.8.22 and it hung immediately on the 1st step and would not continue.