Solved Database problems after migration from Kunena 5 to 6.3.2

Hi there,

i just updated two sites from Joomla 3.1 to Joomla 5.1.1. One one site all went well on the other side i exprerience odd behaviours which i cant fix.

i wanted to count Kunena stats in the backend since it seemed the Counts of Topics wasnt correct.

I got this error message in return:

0 Unknown fetch type '8'

So i tried diagnostic which gave me the following error:

1267 Illegal mix of collations (utf8mb4_0900_ai_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='

In the database there seems to be a mix of both coalitions but it seems on the site where all is working fine its exact the same coalitions settings..

Any ideas?

Thanks in advance!

K.

Database collation check: The collation of your table fields are correct
        

Joomla! SEF: Enabled | Joomla! SEF rewrite: Enabled | FTP layer: Disabled |
       

This message contains confidential information

htaccess: Exists | PHP environment: Max execution time: 240 seconds | Max execution memory: 256M | Max file upload: 

Kunena menu details:

Warning: Spoiler!

ID	Name	Menutype	Link	Path	In trash
1211	Forum	kunenamenu	view=home&defaultmenu=1213	forum	No
1212	Index	kunenamenu	view=category&layout=list	forum/index	No
1213	Aktuell	kunenamenu	view=topics&mode=replies	forum/aktuell	No
1214	Ungelesen	kunenamenu	view=topics&layout=unread	forum/ungelesen	No
1215	Neues Thema	kunenamenu	view=topic&layout=create	forum/neuesthema	No
1216	Ohne Antwort	kunenamenu	view=topics&mode=noreplies	forum/ohneantwort	No
1217	Meine Themen	kunenamenu	view=topics&layout=user&mode=default	forum/meinethemen	No
1218	Profil	kunenamenu	view=user	forum/profil	No
1219	Hilfe	kunenamenu	view=misc	forum/hilfe	No
1220	Suche	kunenamenu	view=search	forum/suche	No
1221	Forum	mainmenu	Itemid=1211	kunena-2024-06-26	No

Joomla default template details : YOOtheme | author: YOOtheme | version: 4.4.4 | creationdate: June 2024

Kunena default template details : NTS Kay | author: Nice Theme Store Team | version: 6.2.5 | creationdate: 2024-06-22

Kunena template params:

Warning: Spoiler!

DefaultCategoryicon
profileIconset	default
DefaultIconset	default
editorIconset	default
bootstrap	1
borderless	1
tooltips	1
SubjectLengthMessage	80
fullactions	1
quick	0
displayMenu	1
displayDropdownMenu	1
displayDropdownContent	1
displayModule	1
displayBreadcrumb	1
displayAnnouncement	1
displayFooter	1
avatarPosition	left
formRecover	0
labels	0
whoisonlineName	default
avatarType	rounded
topicicontype	fa
fontawesome	1
socialshare	0
socialsharetag
socialtheme	classic
optional_username	0
writeaccess	0
localstorage	0
kBrandColor	#2563eb
kLinkColor	#2563eb
kLinkHoverColor	#2563eb
kTabColor	#333333
kTabHoverColor	#2563eb
kTabActiveColor	#2563eb
kHeaderBg	#222222
kHeaderBorder	#2563eb
kHeaderColor	#ffffff
kHeaderDescColor	#ffffff
kHeaderLinkColor	#ffffff
kHeaderLinkHoverColor	#10b981
kCatIconBg	#2563eb
kCatIconColor	#ffffff
kCatIconHoverBg	#1d4ed8
kCatIconHoverColor	#ffffff
kTopicIconColor	#9ca3af
kTopicIconHoverColor	#2563eb
IconColorNew	#48a348
editorType	ckeditor
Bold	1
Italic	1
Underline	1
Strike	1
Subscript	1
Superscript	1
RemoveFormat	1
BulletedList	1
NumberedList	1
Blockquote	1
JustifyLeft	1
JustifyCenter	1
JustifyRight	1
JustifyBlock	1
Link_Unlink	1
Image	1
Smiley	1
Confidential	1
Hidetext	1
Spoiler	1
Code	1
Ebay	1
Twitter	1
Instagram	1
Soundcloud	1
FontSize	1
TextColor	1
Maximize	1
Map	1
Video	1
editorButtons
nameskinckeditor
ckeditorcustomprefixconfigfile
avatarSizeX	40
avatarSizeY	90
avatarSizeXThumb	36
avatarSizeYThumb	36
avatarSizeXWelcome	72
avatarSizeYWelcome	72
avatarSizeXList	36
avatarSizeYList	36
avatarSizeXPost	144
avatarSizeYPost	144
avatarSizeXProfile	200
avatarSizeYProfile	200
X_Social	1
templatebyText
templatebyName
templatebyLink

Kunena version detailed: Kunena 6.3.2 | 2024-06-14 [ Git Repository ]
        | Kunena detailed configuration:

Warning: Spoiler!

Kunena config settings:
boardOffline	1
enableRss	0
threadsPerPage	20
messagesPerPage	10
messagesPerPageSearch	15
showHistory	1
historyLimit	6
showNew	1
disableEmoticons	0
template	nts_kay
showAnnouncement	1
avatarOnCategory	0
showChildCatIcon	1
rteWidth	450
rteHeight	300
enableForumJump	1
reportMsg	1
username	1
askEmail	0
showEmail	0
showUserStats	1
showKarma	0
userEdit	1
userEditTime	0
userEditTimeGrace	600
editMarkup	1
allowSubscriptions	1
subscriptionsChecked	0
allowFavorites	1
maxSig	300
regOnly	0
pubWrite	0
floodProtection	60
mailModerators	0
mailAdministrators	0
captcha	0
mailFull	1
allowAvatarUpload	1
allowAvatarGallery	1
avatarQuality	80
avatarSize	32000
imageHeight	1200
imageWidth	1800
imageSize	32000
fileTypes	txt,rtf,pdf,zip,tar.gz,tgz,tar.bz2
fileSize	10000
showRanking	1
rankImages	1
userlistRows	30
userlistOnline	1
userlistAvatar	1
userlistPosts	1
userlistKarma	1
userlistEmail	0
userlistJoinDate	1
userlistLastVisitDate	1
userlistUserHits	1
latestCategory	0
showStats	1
showWhoIsOnline	1
showGenStats	1
showPopUserStats	1
popUserCount	10
showPopSubjectStats	1
popSubjectCount	20
showSpoilerTag	1
showVideoTag	1
showEbayTag	1
trimLongUrls	1
trimLongUrlsFront	40
trimLongUrlsBack	20
autoEmbedYoutube	1
autoEmbedEbay	1
ebayLanguageCode	en-us
sessionTimeOut	1800
highlightCode	0
rssType	topic
rssTimeLimit	1 month
rssLimit	100
rssIncludedCategories
rssExcludedCategories
rssSpecification	rss2.0
rssAllowHtml	1
rssAuthorFormat	name
rssAuthorInTitle	1
rssWordCount	0
rssOldTitles	1
rssCache	900
defaultPage	recent
defaultSort	asc
sef	1
showImgForGuest	1
showFileForGuest	0
pollNbOptions	20
pollAllowVoteOne	1
pollEnabled	1
popPollsCount	20
showPopPollStats	1
pollTimeBtVotes	00:15:00
pollNbVotesByUser	100
pollResultsUserslist	1
allowUserEditPoll	0
maxPersonalText	50
orderingSystem	mesid
postDateFormat	datetime
postDateFormatHover	datetime
hideIp	1
imageTypes	jpg,jpeg,gif,png
checkMimeTypes	0
imageMimeTypes	image/jpeg,image/jpg,image/gif,image/png
imageQuality	50
thumbHeight	32
thumbWidth	32
hideUserProfileInfo	put_empty
boxGhostMessage	0
userDeleteMessage	0
latestCategoryIn	1
topicIcons	1
debug	0
catsAutoSubscribed	0
showBannedReason	0
showThankYou	1
showPopThankYouStats	1
popThanksCount	20
modSeeDeleted	1
bbcodeImgSecure	text
listCatShowModerators	1
lightbox	1
showListTime	0
showSessionType	2
showSessionStartTime	1800
userlistAllowed	0
userlistCountUsers	1
enableThreadedLayouts	0
categorySubscriptions	post
topicSubscriptions	every
pubProfile	0
thankYouMax	10
emailRecipientCount	0
emailRecipientPrivacy	bcc
captchaPostLimit	1
imageUpload	registered
fileUpload	registered
topicLayout	flat
timeToCreatePage	0
showImgFilesManageProfile	1
holdNewUsersPosts	0
holdGuestPosts	0
attachmentLimit	40
pickupCategory	0
articleDisplay	intro
sendEmails	1
fallbackEnglish	1
cache	0
cacheTime	60
ipTracking	0
rssFeedBurnerUrl
autoLink	1
accessComponent	0
statsLinkAllowed	0
superAdminUserlist	0
attachmentProtection	0
categoryIcons	1
avatarCrop	0
userReport	1
searchTime	all
teaser	0
ebayLanguage	77
ebayCertId
allowChangeSubject	1
maxLinks	5
readOnly	0
ratingEnabled	0
urlSubjectTopic	1
logModeration	1
attachStart	0
attachEnd	14
attachmentUtf8	1
autoEmbedSoundcloud	1
emailHeader	/media/kunena/email/hero-wide.png
userStatus	1
signature	1
personal	1
social	1
plainEmail	0
moderatorPermDelete	1
avatarTypes	gif,jpeg,jpg,png
smartLinking	0
defaultAvatar	nophoto.png
defaultAvatarSmall	s_nophoto.png
quickReply	1
avatarEdit	0
activeMenuItem
mainMenuId
homeId
indexId
moderatorsId
topicListId
miscId
profileId
searchId
custom_id
avatarType	1
sefRedirect	1
allowEditPoll	1
useSystemEmails	0
autoEmbedInstagram	1
disableRe	0
email_sender_name
display_filename_attachment	0
new_users_prevent_post_url_images	1
minimal_user_posts_add_url_image	2
utmSource	0
emailHeaderSizeY	560
emailHeaderSizeX	560
moderator_id
profiler	0
pickup_category
privateMessage	1
datePickerFormat	mm/dd/yyyy
sendMailUserBanned	0
mailBodyUserBanned
catimagepath	category_images
maxsubject	250
allow_user_edit_poll	0
show_imgfiles_manage_profile	1
legacy_urls	0
avatarresizemethod	1
ebay_cert_id
moderator_permdelete	1
recaptcha_publickey
recaptcha_privatekey
recaptcha_theme	white
keywords	0
userkeywords	0

| Kunena integration settings:

Warning: Spoiler!

Kunena - Finder Disabled
 Kunena - AltaUserPoints Disabled
 Kunena - Community Builder Disabled
 Kunena - Easyblog Disabled
 Kunena - Easyprofile Disabled
 Kunena - Easysocial Disabled
 Kunena - Gravatar Disabled
 Kunena - JomSocial Disabled
 Kunena - Joomla Enabled:  access=1  login=1  
 Kunena - Kunena Enabled:  avatar=1  profile=1  
 Kunena - UddeIm Disabled

| Joomla! detailed language files installed:

Warning: Spoiler!

Joomla! languages installed:
en-GB	English (United Kingdom)
de-DE	German (Germany)

Third-party components: UddeIm 5.4

Third-party SEF components: None

Plugins: None

Modules: None

Is this configuration report from the working page? No collation error is displayed in it.

Install Kunena on the broken site over it again and then go directly to the Kunena dashboard. This should adjust the collation.

Hiho rich,

thank you for your quick reply!
Yes its from the working page.

i just solved the problem by going into phpMyAdmin and change the coalition of the kunena columms from utf8mb4_0900_ai_ci to utf8mb4_unicode_ci at the following tables:

kunena_categories
kunena_topics
kunena_messages
kunena_messages_text

After doing that it looks like working flawlessly. I was able to recount the stats and the stats are now correct. Also i was able to run diagnostic tool and fix broken elements.

Weird tho in the other forum the above mentioned tables still are set to utf8mb4_0900_ai_ci and work flawlesly...

Kind regards,
K.

This problem can remain undetected for a long time until, for example, an emoji is inserted into a post. If this is not allowed with this collation, this error occurs ( see also here ).