MyBB Community Forums

Hello

recently I purchased a better server with 32 GB ram and octacore processor running centos 7 and mysql 5.5 , and changed my website (including a mybb forum) to that one.

The mysql in the new server went for occasional overload, having upto 700% CPU usage and that results in system freeze.

I created a new my.cnf file with the following parameters

bind-address = 127.0.0.1

default-storage-engine         = MyISAM
# MyISAM #
key-buffer-size                = 10G
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max-allowed-packet             = 16M
max-connect-errors             = 1000000
sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_I$
sysdate-is-now                 = 1
innodb                         = FORCE
innodb-strict-mode             = 1

# BINARY LOGGING #
log-bin                        = /var/lib/mysql/mysql-bin
expire-logs-days               = 14
sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 32M
max-heap-table-size            = 32M
query-cache-type               = 0
query-cache-size               = 0
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 65535
table-definition-cache         = 4096
table-open-cache               = 4096

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 32M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 8M

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

But mybb shown the following error for thread display pages

MyBB has experienced an internal SQL error and cannot continue.

SQL Error:
    1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
Query:
    SELECT COUNT(tid) as threads FROM mybb_threads WHERE fid = '4' AND (lastpost >= '1415384991' OR sticky=1) AND visible='1' ORDER BY lastpost DESC

Any suggestions ? where I messed up ?

regards

Bijumk

Please be careful when setting SQL modes, they affect the behavior of MySQL in a way MyBB doesn't support.

sql-mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_I$

This line is incomplete however the error is caused by mode ONLY_FULL_GROUP_BY.

Thanks stefan for the reply

Actually I'm not a mysql geek, It was the output file of the my.cnf configuration wizard available at percona.com.

Thank you very much for your support

bijumk

StefanT

bijumk