Collation Type Database Error

[BarkyJ]

Hello,

We just got this error through to our admin email, and unsure what it means exactly:



So it looks like a Private Message between Users, however its talking about latin1_swedish_ci vs utf8_general_ci.
We recently went through the whole database and converted it to UTF8 due to other character releated issues, with the support of vBulletin Support, and we thought we had this sorted. What exactly is using latin1_swedish_ci, or utf8_general_ci? as our database tables and default collation type are all set to utf8_unicode_ci, and the config file is set to use utf8 too.

Can someone please assist

Regards

[Trevor Hannant]

If it's spitting that error back then there must be a column that's been missed...

***USE AT YOUR OWN RISK***

I've attached a file which you can edit, upload to your server and run to change all tables to the same. You'll need to add the server (i.e. localhost or other address), username, password and database name into the relevant fields.

**BACKUP YOUR DATABASE FIRST**

Always make sure you've got a FULL backup of your database BEFORE doing anything like this just in case.

***USE AT YOUR OWN RISK***

Did I say that this is at your own risk? This isn't a supported file, it's something I pulled together for a couple of support issues and has worked OK for me - but use at your own risk. I'f you're not comfortable with it, speak to your host to check that all columns as well as tables are set to utf8_general_ci.

[BarkyJ]

Hi Trevor

Thanks for this.
Noted on the risk warnings.

However, you state utf8_general_ci.
This is the confusing part.
When I first raised my ticket a few months ago, I was told to change everything to latin1_swedish_ci. That didnt solve anything. I was then told to change everything to uft8_unicode_ci, and then change the config to use utf8. This solved most things, but there were still problems which characters, which led on to a bug report, and apparent 'fix' in this 5.2.5 release, however that didnt completely solve the issue either - same issue happens on this forum (but that is a different matter to what this topic is about).
Now you are saying to go to utf8_general_ci. Why general over unicode?

Please advise.
Thanks

[Trevor Hannant]

Sorry, was just reading what was in your screenshot when I wrote that - change the value to whatever you wish in that last part of the file - if all your tables are uft8_unicode_ci, then put that in there instead.

[BarkyJ]

Ok thanks Trevor

ill do a backup and test tomorrow or Monday and reply back here with how it went, but I guess it will be hard to know if the problem is solved, until we get another error. They are few and far between.

Thanks

[BarkyJ]

Hi Trevor

OK this has been done, no issues, finished successfully.

Do you know of a method to compare 2 backups, just to see if this actually changed the collation type of anything?

from what I could tell from phpmyadmin, the collation type was already utf8_unicode_ci for all the tables, and as the default for the database. I am no database expert though, so whether there is something higher level, or lower level which may not have been set right, I dont know.

Any ideas?

I took a backup before, and after using mySQLDumper

Thanks

[BarkyJ]

Just using Notepad++ I opened the SQL backup, before the change, and searched for the word 'latin', just to see what it would find.

It came up with this (and a few other examples)

--
-- Create Table `usertextfield`
--

DROP TABLE IF EXISTS `usertextfield`;
CREATE TABLE `usertextfield` (
  `userid` int(10) unsigned NOT NULL DEFAULT '0',
  `subfolders` mediumtext CHARACTER SET latin1,
  `pmfolders` mediumtext CHARACTER SET latin1,
  `buddylist` mediumtext CHARACTER SET latin1,
  `ignorelist` mediumtext CHARACTER SET latin1,
  `signature` mediumtext CHARACTER SET latin1,
  `searchprefs` mediumtext CHARACTER SET latin1,
  `rank` mediumtext CHARACTER SET latin1,
  PRIMARY KEY (`userid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

collation is right, but character set is latin1. is that right, or is that wrong?

[BarkyJ]

Used Ultracompare in text mode, and that did a much better job, comparing the dumps. Viewed as just things which were different, there were alot of things like this:

before:
`title` varchar(255) CHARACTER SET latin1 NOT NULL,

after:
`title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,


or more so:

`method` enum('get','post') CHARACTER SET latin1 NOT NULL DEFAULT 'post',
`status` enum('undone','done','dismissed') CHARACTER SET latin1 NOT NULL DEFAULT 'undone',
`args` mediumtext CHARACTER SET latin1,
`title` varchar(50) CHARACTER SET latin1 NOT NULL DEFAULT '',
`text` mediumtext CHARACTER SET latin1,
`title` varchar(100) CHARACTER SET latin1 NOT NULL DEFAULT '',
`description` mediumtext CHARACTER SET latin1,
`state` enum('public','private','profile') CHARACTER SET latin1 NOT NULL DEFAULT 'public',
`title` varchar(250) CHARACTER SET latin1 NOT NULL DEFAULT '',
`pagetext` mediumtext CHARACTER SET latin1,
`state` enum('visible','moderation') CHARACTER SET latin1 NOT NULL DEFAULT 'visible',
`posthash` varchar(32) CHARACTER SET latin1 NOT NULL DEFAULT '',
`filename` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
`caption` text CHARACTER SET latin1,
`settings` mediumtext CHARACTER SET latin1,
`title` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
`filename` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
`extension` varchar(20) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
`extension` char(20) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
`mimetype` varchar(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
`contenttypes` mediumtext CHARACTER SET latin1,
`title` varchar(100) CHARACTER SET latin1 NOT NULL DEFAULT '',
`avatarpath` varchar(100) CHARACTER SET latin1 NOT NULL DEFAULT '',
`bbcodetag` varchar(191) CHARACTER SET latin1 NOT NULL DEFAULT '',
`bbcodereplacement` mediumtext CHARACTER SET latin1,
`bbcodeexample` varchar(200) CHARACTER SET latin1 NOT NULL DEFAULT '',
`bbcodeexplanation` mediumtext CHARACTER SET latin1,
`title` varchar(100) CHARACTER SET latin1 NOT NULL DEFAULT '',
`buttonimage` varchar(250) CHARACTER SET latin1 NOT NULL DEFAULT '',

vs

`method` enum('get','post') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'post',
`status` enum('undone','done','dismissed') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'undone',
`args` longtext COLLATE utf8_unicode_ci,
`title` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`text` longtext COLLATE utf8_unicode_ci,
`title` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`description` longtext COLLATE utf8_unicode_ci,
`state` enum('public','private','profile') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'public',
`title` varchar(250) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`pagetext` longtext COLLATE utf8_unicode_ci,
`state` enum('visible','moderation') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'visible',
`posthash` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`caption` mediumtext COLLATE utf8_unicode_ci,
`settings` longtext COLLATE utf8_unicode_ci,
`title` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`filename` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`extension` varchar(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`extension` char(20) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`mimetype` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`contenttypes` longtext COLLATE utf8_unicode_ci,
`title` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`avatarpath` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`bbcodetag` varchar(191) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`bbcodereplacement` longtext COLLATE utf8_unicode_ci,
`bbcodeexample` varchar(200) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`bbcodeexplanation` longtext COLLATE utf8_unicode_ci,
`title` varchar(100) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`buttonimage` varchar(250) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',

I assume this is right?

[Trevor Hannant]

The backup will add the collation that was in use at the time of the backup being taken. If the new backups are showing the correct collation through the file then the changes will have worked.