Last post 3 hours 24 min ago

Posts last week 191

Average response time last week 1 hour 50 min

All time posts 68187

All time tickets 10530

All time avg. posts per day 20

Helpdesk is open from Monday through Friday CET

Please create an (free) account to post any question in the support area.

Please check the development versions area. Look at the changelog, maybe your specific problem has been resolved already!

All tickets are private and they cannot be viewed by anyone. We have made public only a few tickets that we found helpful, after removing private information from them.

#973 – Illegal mix of collations error when trying to access URL manager

Posted in ‘sh404SEF’

This is a public ticket. Everybody will be able to see its contents. Do not include usernames, passwords or any other sensitive information.

Tuesday, 08 September 2015 14:22 UTC

dmester

When I try and access URL manager, I receive the following error
********************************
An error has occurred.
1267 Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' SQL=select u.*, pg.pageid as pageid, pg.id as pageidid, pg.type as pageidtype, pg.hits as pageidhits, count(a.alias) as aliases from ( select distinct u1.*, count(d.`id`)-1 as duplicates, count(m.`id`) as metas from jos3_sh404sef_urls as d, jos3_sh404sef_urls as u1 left join `jos3_sh404sef_metas` as m on m.newurl=u1.newurl WHERE u1.newurl <> "" AND d.oldurl=u1.oldurl AND u1.rank = 0 group by u1.`id`) as u left join `jos3_sh404sef_aliases` as a on a.newurl = u.newurl left join `jos3_sh404sef_pageids` as pg on pg.newurl = u.newurl group by u.newurl order by `oldurl` ASC
*********************************
I am on version 4.6.0.2718.

Please advise how to fix.

Tuesday, 08 September 2015 14:27 UTC

wb_weeblr

Hi

That's not something that can be fixed in Joomla or PHP. For some reason, the mysql server you're using seems to have created different sh404SEF tables with different collations.
You should make sure all sh404SEF tables (identified by xxxx_sh404sef_xxxx) have he same collations at your mysql server level, for instance using phpmyadmin or similar.

What's the history of the site, joomla and sh404SEF versions? when was sh404SEF first installed, at which version? and was the site moved to a new hosting company recently for instance? or migrated from Joomla 2 to 3 or similar?

Rgds

Tuesday, 08 September 2015 14:45 UTC

dmester

The history of the site is a little unclear. It was originally a Joomla 2.5 site. It was updated to Joomla 3 about a year ago. The site seemed to work well from a user perspective and I don't remember if sh404sef was working from the backend/admin. Today I logged in for the first time in a long time, and updated the site to the latest version of Joomla. When I tried accessing sh404sef URL manager I received an error, so I purchased and upgraded to the latest version (I was on version 4.4.4.1791).

I don't know how to "make sure all sh404SEF tables (identified by xxxx_sh404sef_xxxx) have he same collations at your mysql server level, for instance using phpmyadmin or similar"...

Can you point me to a tutorial on this?

Thanks.

Tuesday, 08 September 2015 14:49 UTC

wb_weeblr

Hi

A bit weird, 4.4.4 is not "that" old, but yes, this message is the sign that one table was created with on e collation, while another was created with another collation.
We don't specify collations, but use the server defaults, so the server default must have changed at some point, or the site moved from one server to another.

These tutorials look good for instance:

https://www.siteground.com/kb/how_can_i_change_my_mysql_database_collation/

https://www.a2hosting.com/kb/cpanel/cpanel-database-features/changing-the-mysql-collation-settings-in-phpmyadmin

Rgds

Tuesday, 08 September 2015 15:20 UTC

dmester

Unfortunately, the links you gave me only showed how to change the collation for new tables being generated, but not for existing tables. I was, however, able to fix the existing tables by issuing the command:
ALTER TABLE jos3_sh404sef_aliases CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

for each of the sh404sef tables...and it seemed to have solved the problem.

The other issue I have is that when I try and access the Aliases manager, I get the following error:

1054 Unknown column 'a.type' in 'where clause' SQL=select a.*, r.oldurl from jos3_sh404sef_aliases as a left join `jos3_sh404sef_urls` as r on r.`newurl` = a.`newurl` WHERE a.type = '0' AND a.newurl != 'index.php?3de69ea13a27d1ead96ff5d7b47efae3' order by alias ASC

This error was there before I fixed the other issue, I just wanted to handle one issue at a time.

Any advice on this new issue?

Tuesday, 08 September 2015 16:31 UTC

wb_weeblr

I just wanted to handle one issue at a time.

And you were very right in doing so.

1054 Unknown column 'a.type' in 'where clause' SQL=select a.*, r.oldurl from jos3_sh404sef_aliases as a left join `jos3_sh404sef_urls` as r on r.`newurl` = a.`newurl` WHERE a.type = '0' AND a.newurl != 'index.php?3de69ea13a27d1ead96ff5d7b47efae3' order by alias ASC

Here is the definition of the table:

"CREATE TABLE IF NOT EXISTS `#__sh404sef_aliases` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
        `newurl` VARCHAR(255) NOT NULL DEFAULT '',
        `alias` VARCHAR(255) NOT NULL DEFAULT '',
        `type` TINYINT(3) NOT NULL DEFAULT '0',
        `hits` INT(11) NOT NULL DEFAULT '0',
        PRIMARY KEY (`id`),
        KEY `newurl` (`newurl`),
        KEY `alias` (`alias`),
        KEY `type` (`type`)
        ) DEFAULT CHARSET=utf8;"

I don't remember when the 'type' column was added, but it was a very long time ago, so much that we don't have code to add it if it's missing.
Can you add the column from the description?

Rgds

Tuesday, 08 September 2015 16:54 UTC

dmester

I looked at the sh404sef columns from another website of mine where everything was working well, and found out that I was "missing" two columns (type and hits) so I added them through phpmyadmin and everything seems to be working well!

If someone had told me this morning that I would be changing the collation on a database and adding two new fields I would have told them they were crazy! Definitely above my pay-grade...

Thanks for your help.

Tuesday, 08 September 2015 17:15 UTC

wb_weeblr

... or you should get a raise ;)

This ticket is closed, therefore read-only. You can no longer reply to it. If you need to provide more information, please open a new ticket and mention this ticket's number.