News:

SMF 2.0.19 has been released! Please update. Read more.

Main Menu

errors log database

Started by fonfonsd, July 11, 2022, 11:24:12 AM

Previous topic - Next topic

Kindred

Doug,

From his pictures, we KNOW that, regardless of what the OP SAYS the settings are -- many of those tables are *NOT* collated to utf8_general_ci



fonfonsd...   try this
First BACKUP BACKUP BACKUP BACKUP!!!!!

Then, run this:
SELECT table_name, column_name
FROM   information_schema.columns
WHERE  collation_name LIKE '%latin1%'

Use THIS information to build a list of actions for each table
I did it by hand, creating a list of the following commands
ALTER DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;

and then each line per table
ALTER TABLE tablename CHARACTER SET utf8 COLLATE utf8_general_ci;
ALTER TABLE tablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

do note that
The CONVERT TO technique assumes that the text was correctly stored in some other charset (eg, latin1), and not mangled (such as UTF-8 bytes crammed into latin1 column without conversion to latin1).

Alternatively, you can try this script that someone suggested

SELECT CONCAT(
'ALTER TABLE ',  table_name, ' CHARACTER SET utf8 COLLATE utf8_general_ci;  ',
'ALTER TABLE ',  table_name, ' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  ')
FROM information_schema.TABLES AS T, information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` AS C
WHERE C.collation_name = T.table_collation
AND T.table_schema = 'your_database_name'
AND
(C.CHARACTER_SET_NAME != 'utf8'
OR
C.COLLATION_NAME not like 'utf8%')
   
Capture the output of this and run it.   
Слaва
Украинi

Please do not PM, IM or Email me with support questions.  You will get better and faster responses in the support boards.  Thank you.

"Loki is not evil, although he is certainly not a force for good. Loki is... complicated."

Doug Heffernan

Quote from: Kindred on July 14, 2022, 12:10:19 PMDoug,

From his pictures, we KNOW that, regardless of what the OP SAYS the settings are -- many of those tables are *NOT* collated to utf8_general_ci

The screenshot only showed the columns from the smf_settings table, which were in latin 1. This is the exact same case like @Steve and the op of the topic I posted the link to in my previous post, had.

Anyways, running all those sql queries is not necessary imo because the pma has a build in option that takes care of all this automatically in one go when used. No need to waste time by converting each table separately.


Arantor

Not all versions of phpMyAdmin in use on hosts have that option, some will break it on some hosts, and in reality it's just meant to be doing the exact same thing that Kindred's SQL does, but I'd honestly rather trust *that*.

fonfonsd

Hello and thank you all, I have to leave for 10 days, I won't have a computer. I note your messages and I take care of them when I get back, and I let you know.
Thanks again for your help.

fonfonsd


Steve

Please do not PM me for support.

Advertisement: