error illegal mix of collations

17 years 9 months ago #15912 by calibal
error illegal mix of collations was created by calibal
hi at all, i have an error on the plugin's panel:
[code:1]Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT).....[/code:1]

but i don't have any latin1_swedish_ci tables in my database, they are all in UTF8_unicode_ci

my root folder of phpbb is in:
mysite.com/CMS/frm/
where CMS is the root of joomla, and declared as CMS/frm/ in the plugin 1rst panel.

when i would go to advanced feature i have this error.

for informations the complete one is:
[code:1]SQL errorDB function failed with error number 1267
Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' SQL=SELECT count(*) as total FROM (jos_users as j INNER JOIN jos_comprofiler as cb ON j.id = cb.id) INNER JOIN phpbb_users as p ON cb.cb_phpbbid = p.user_id WHERE cb.approved='1' AND p.user_active='1' AND j.block='0' AND (j.username = p.username) AND (j.password = p.user_password) ORDER by j.id
SQL =
SELECT count(*) as total
FROM (jos_users as j INNER JOIN jos_comprofiler as cb ON j.id = cb.id)
INNER JOIN phpbb_users as p ON cb.cb_phpbbid = p.user_id
WHERE cb.approved='1' AND p.user_active='1' AND j.block='0' AND (j.username = p.username) AND (j.password = p.user_password)
ORDER by j.id[/code:1]

then i could not impotrt/export anything, and could not syncrhonise then.
All ideas are welcome ;)

(i don't know very weel joomla, but i know phpbb if it could help ;) )

Please Log in to join the conversation.

17 years 9 months ago #15915 by geeffland
Replied by geeffland on topic Re:error illegal mix of collations
calibal,

Not sure on your error but make sure that the forum directory does not end with /

Greg

CB3PD Developer - CB Connector (formerly phpBB Connector) plugin

Please Log in to join the conversation.

17 years 9 months ago #15951 by calibal
Replied by calibal on topic Re:error illegal mix of collations
hi ;)
thanks for your answer :D after some test

i have changed from cms/forum/ to forum/
and now the eroor is:
[code:1]SQL errorDB function failed with error number 1146
Table 'mytable.users' doesn't exist SQL=SELECT count( * ) as total_record FROM users WHERE user_active = '1'
SQL =
SELECT count( * ) as total_record FROM users WHERE user_active = '1'[/code:1]

if i try to put cms/forum i have the same illegal mix error :(

Please Log in to join the conversation.

17 years 9 months ago #16035 by geeffland
Replied by geeffland on topic Re:error illegal mix of collations
From this error message it it not finding the forum directory (common.php file)... notice that the SQL indicates from users... it should have read the prefix from the config.php file

Keep in mind that 1.0.2 locates the forum directory based on the server variable "DOCUMENT_ROOT"... which if set correctly should be the directory that is for your root website (and not the location of Joomla)

www.yoursite.com = var/usr/local/web
(most likely some other directory)

So if you installed Joomla into a subdirectory "cms" and installed the forum as a subdirectory of Joomla in "forum" your forum directory should be "cms/forum" (without the quotes)... note that their is no preceeding or trailing /

Hope this helps,
Greg

CB3PD Developer - CB Connector (formerly phpBB Connector) plugin

Please Log in to join the conversation.

17 years 9 months ago #16036 by calibal
Replied by calibal on topic Re:error illegal mix of collations
yes then when my settings are good i all on the following error:
[code:1]SQL errorDB function failed with error number 1267

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' SQL=SELECT count(*) as total FROM (jos_users as j INNER JOIN jos_comprofiler as cb ON j.id = cb.id) INNER JOIN phpbb_users as p ON cb.cb_phpbbid = p.user_id WHERE cb.approved='1' AND p.user_active='1' AND j.block='0' AND (j.username = p.username) AND (j.password = p.user_password) ORDER by j.id[/code:1]

Please Log in to join the conversation.

17 years 9 months ago #16039 by geeffland
Replied by geeffland on topic Re:error illegal mix of collations
Not sure where the swedish thing comes from (unless MySql default)...

What version of MySQL are you using 4.x.x or 5.x.x ?

Also on my install (english) the followign fields in the jos_users template are marked as "latin1_swedish_ci)

name
username
email
password
usertype
activation
params
(basically any varchar or text fields)

Do you have any fields which are mixed collation?

My guess that the jos_users and phpbb_users tables have different collation defined for the username field... can you verify that?

Thanks,
Greg

CB3PD Developer - CB Connector (formerly phpBB Connector) plugin

Please Log in to join the conversation.

Moderators: beatnantgeefflandmartijn189krileon
Time to create page: 0.251 seconds

Facebook Twitter LinkedIn