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

XIAIX's profile image XIAIX posted 1 month 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 1 month 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 1 month 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 1 month 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 1 month 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 1 month 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.