Default collation "Convert data" is bugged in the HeidiSQL Client

XIAIX's profile image XIAIX posted 11 months ago in General Permalink

Description

When I click the "Convert data" checkbox and hit "Save", I would expect that the Collation on the fields would change .... NOT the Default.

ansgar's profile image ansgar posted 10 months ago Permalink

Please watch out for the ALTER TABLE ... COLLATE new-collation query in the log panel. It's long ago since I implemented that, and it could be the approach is no longer correct due to changes in MySQL or MariaDB.

XIAIX's profile image XIAIX posted 10 months ago Permalink
[08:48:09.087] ALTER TABLE `cards`
    COLLATE='latin1_general_ci',
    CONVERT TO CHARSET latin1;
[08:48:09.117] SELECT `DEFAULT_COLLATION_NAME` FROM `information_schema`.`SCHEMATA` WHERE `SCHEMA_NAME`='alcazar';
[08:48:09.119] SHOW TABLE STATUS FROM `alcazar`;
[08:48:09.121] SHOW FUNCTION STATUS WHERE `Db`='alcazar';
[08:48:09.122] SHOW PROCEDURE STATUS WHERE `Db`='alcazar';
[08:48:09.124] SHOW TRIGGERS FROM `alcazar`;
[08:48:09.126] SELECT *, EVENT_SCHEMA AS `Db`, EVENT_NAME AS `Name` FROM information_schema.`EVENTS` WHERE `EVENT_SCHEMA`='alcazar';
[08:48:09.156] SELECT * FROM `information_schema`.`COLUMNS` WHERE TABLE_SCHEMA='alcazar' AND TABLE_NAME='cards' ORDER BY ORDINAL_POSITION;
[08:48:09.170] SHOW INDEXES FROM `cards` FROM `alcazar`;
[08:48:09.174] SELECT * FROM information_schema.REFERENTIAL_CONSTRAINTS WHERE   CONSTRAINT_SCHEMA='alcazar'   AND TABLE_NAME='cards'   AND REFERENCED_TABLE_NAME IS NOT NULL;
[08:48:09.177] SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE   TABLE_SCHEMA='alcazar'   AND TABLE_NAME='cards'   AND REFERENCED_TABLE_NAME IS NOT NULL;
[08:48:09.212] SHOW CREATE TABLE `alcazar`.`cards`;
[08:48:09.214] SELECT tc.CONSTRAINT_NAME, cc.CHECK_CLAUSE FROM `information_schema`.`CHECK_CONSTRAINTS` AS cc, `information_schema`.`TABLE_CONSTRAINTS` AS tc WHERE tc.CONSTRAINT_SCHEMA='alcazar' AND tc.TABLE_NAME='cards' AND tc.CONSTRAINT_TYPE='CHECK' AND tc.CONSTRAINT_SCHEMA=cc.CONSTRAINT_SCHEMA AND tc.CONSTRAINT_NAME=cc.CONSTRAINT_NAME;
/* [08:48:09.243] Entering session "local" */
[08:48:09.298] SHOW CREATE TABLE `alcazar`.`cards`;

Description

XIAIX's profile image XIAIX posted 10 months ago Permalink

Sorry for puking the log as I'm not used to the formatting of your forums... it's not a WISIWYG (TinyMCE) editor

¯\_(ツ)_/¯

XIAIX's profile image XIAIX posted 10 months ago Permalink

And there's why :)..

According to: https://dev.mysql.com/doc/refman/8.0/en/charset-charsets.html

Description

XIAIX's profile image XIAIX posted 10 months ago Permalink

After reading https://dev.mysql.com/doc/refman/8.0/en/charset-we-sets.html, I have determined that "cp1252 West European" a.k.a. latin1_swedish_ci is what I want. Primarily English (USA) consumers with the English language, I think this is the best option for me. It appears that latin1_general_ci isn't a thing anymore, at least in MySQL version 8.0+

It's up to you if you update Heidi, but I'd like to thank you for explaining where the issue is. :)

Please login to leave a reply, or register at first.