search

joomdev / JD-Builder

JD Builder - Experience the Next Generation of Joomla Page Builder.
https://www.joomdev.com/jd-builder
GNU General Public License v3.0
22 stars 16 forks source link

default table collation #6

Open shoulders opened 4 years ago

shoulders commented 4 years ago

the default collations for the databases might need looking at.

for some reason my default database collation was set at latin1_swedish_ci and becasue of this 3 tables were set to this, but the jdbuilder_pages was set to utf8mb4_unicode. can you set explicit table collations for these 3 tables i.e. utf8_unicode_ci or if you must utf8_general_ci

database-collations

chetanmadaan commented 4 years ago

@shoulders we need to keep collation to utf8mb4_unicode_ci since that'll allow the tables to store multibyte data including emoji's, Chinese language characters and others.

Newer version so Joomla's default tables are now utf8mb4_unicode_ci unless I am mistaken. I just checked #__content.

shoulders commented 4 years ago

@chetanmadaan

Newer version so Joomla's default tables are now utf8mb4_unicode_ci unless I am mistaken. I just checked #__content.

This might just be for the core joomla tables. I cant speak directly about joomla but i know wordpress does a test to see if it can use utf8mb4 collation before it installs and if it can does use it, otherwise it defaults back to normal utf8. I would guess joomla 4 would be the same. I dont know about the install mechanism for the Joomla 4 to know whether it sets the collation or that the default collation for the database is used unless specfied in the mysql query. I think joomla 3 used the default databases collation though.

other way around. Keep utf8mb4_unicode_ci for the greater character range/emojis, this is fine.

It is the other tables I am concerned with. They would be better as utf8_unicode_ci or utf8_general_ci as this seems to be a standard collation and I am sure it is what wordpress uses for all of its tables when you do a default install.

as you can guess utf8_unicode_ci is the 3 byte version of utf8mb4_unicode_ci (4 byte)

when people have badly configured servers latin1_swedish_ci is used because this is the default collation. (p.s. I wish database developers would change this in their releases). Setting the collation for these tables prevents the wrong collation being used.

I dont know if you have a preference as to explicity set the collation or not. I personally manually change the tables to utf8_unicode_ci if I find any latin1_swedish_ci ones

chetanmadaan commented 4 years ago

Thanks Jon for the through explanation. I am not an expert on this are and I'll do my research and then get back here.

shoulders commented 4 years ago

some notes i did a while ago, they might help.

https://quantumwarp.com/kb/articles/23-mysql/392-what-mysql-database-collation-should-i-use

chetanmadaan commented 4 years ago

Thanks that's very helpful Jon.

chetanmadaan commented 4 years ago

I read your post Jon and I think we'll keep utf8mb4_unicode_ci for _#__jdbuilderlayouts and _#__jdbuildertemplates and utf8_unicode_ci for the everything else.