collation/char set for connection, DB, tables for mysql (external)

Use this forum if you have installed hMailServer and want to ask a question related to a production release of hMailServer. Before posting, please read the troubleshooting guide. A large part of all reported issues are already described in detail here.
Post Reply
opto
New user
New user
Posts: 22
Joined: 2009-01-27 10:32

collation/char set for connection, DB, tables for mysql (external)

Post by opto » 2020-01-10 16:37

I am pulling this out of another thread because it might be of interest to others. Did find some experiments with utf8/utf8MB4 in other threads, but no clear statement.

What is the required collation for connection (set names), db, tables for an external mysql and HM 5.6.7?

After dumping the internal mysql4 from very long ago, I see that the db is inconsistent, some tables are latin, some are utf8. I don't know whether HM 5.6.7 would be unhappy if I load that into a recent mysql/Mariadb.

thanks,

Klaus

User avatar
mattg
Moderator
Moderator
Posts: 20554
Joined: 2007-06-14 05:12
Location: 'The Outback' Australia

Re: collation/char set for connection, DB, tables for mysql (external)

Post by mattg » 2020-01-11 01:59

All of my hmailserver tables are
utf8_general_ci

Except where I have expressly changed them to be
utf8mb4_general_ci

And that was for
hm_messages
hm_messages_metadata
hm_senderlog

And the purpose of the change was for umlauts and emojis in subjects and account names
Just 'cause I link to a page and say little else doesn't mean I am not being nice.
https://www.hmailserver.com/documentation

User avatar
Dravion
Senior user
Senior user
Posts: 1613
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: collation/char set for connection, DB, tables for mysql (external)

Post by Dravion » 2020-01-11 04:39

Mattg is right.
hMaiServers Database Setup Functions setting
anything to UTF-8, a least for hMailServer 5.x series.

However:
First hMailServer was in 2003 and the older Releases
did not enforce UTF-8. You should upgrade in small steps as described, not as a giant leap.

opto
New user
New user
Posts: 22
Joined: 2009-01-27 10:32

Re: collation/char set for connection, DB, tables for mysql (external)

Post by opto » 2020-01-11 17:26

@mattg:

thanks for the info. But wouldn't that also need the connection to be set to utf8MB4? (and that is in hmailserver code as set names, which probably is set to utf8, because in a fresh install with external db, all tables in the empty db are utf8?).

So the table might be able to store the mb4, but the connection would not be able to deliver it?

thanks,

Klaus

opto
New user
New user
Posts: 22
Joined: 2009-01-27 10:32

Re: collation/char set for connection, DB, tables for mysql (external)

Post by opto » 2020-01-11 17:35

@Dravion:

I upgraded in very small steps, 5.3.2->5.3.3->5.4->5.5->5.6.7, but that still left everything inconsistent, and HM created mail directories with special characters in it (like üI), which it is not supposed to do.

Now, I installed just 5.6.7, let it create an empty db in mariaDB, and then loaded my data into there. Advantage: tables are now innodb, I converted all latin to utf8 and deleted the tables which no longer exist (was starting from mysql4).

if this works, I will write a short howto. There are some obstacles, like getting many mysql warnings because the dump tries to lock keys which is not possible in innodb.

Klaus

User avatar
Dravion
Senior user
Senior user
Posts: 1613
Joined: 2015-09-26 11:50
Location: Germany
Contact:

Re: collation/char set for connection, DB, tables for mysql (external)

Post by Dravion » 2020-01-11 18:41

opto wrote:
2020-01-11 17:35
@Dravion:

I upgraded in very small steps, 5.3.2->5.3.3->5.4->5.5->5.6.7, but that still left everything inconsistent, and HM created mail directories with special characters in it (like üI), which it is not supposed to do.

Now, I installed just 5.6.7, let it create an empty db in mariaDB, and then loaded my data into there. Advantage: tables are now innodb, I converted all latin to utf8 and deleted the tables which no longer exist (was starting from mysql4).

if this works, I will write a short howto. There are some obstacles, like getting many mysql warnings because the dump tries to lock keys which is not possible in innodb.

Klaus
This would be a great and very appreciated Tutorial (upgrading from MySQL4 to 5.x or 8.0 or latest MariaDB version) :D

Post Reply