eMember – DB tables being created with mixed collation

[halmobi]

Hello,

When installing the eMember plugin, the tables are being created with mixed collation. For example:

wp_wp_eMember_membership_tbl – all columns have collation “utf8_general_ci”.

wp_wp_eMember_member_tbl – all columns have collation “latin1_swedish_ci.

Everything is up on a web host (startlogic.com) server.

I install by uploading the plugin zip file using WP plugin admin panel, then activating.

Platform type – Debian

MySQL – 5.0.91-log

Perl – V 5.8.8

PHP – V 5.2.17

WordPress – V 3.2.1

eMember – V 7.8.5

I spoke to my host support, they said the collation is based on where geographically the plugin originates from… yet that would still imply a consistent collation setting across all tables for a given plugin. I looked at the “CREATE TABLE” code in eMember_installer.php, it specifies “DEFAULT CHARSET=utf8;”, does not mention COLLATE, so I am thinking there is a default set somewhere on the server side. Here are the Server variables and settings, maybe the Global values are having an influence.

character set client – utf8

(Global value) – latin1

character set connection – utf8

(Global value) – latin1

character set database – latin1

character set filesystem – binary

character set results – utf8

(Global value) – latin1

character set server – latin1

character set system – utf8

character sets dir – /usr/local/mysql-5.0.91-linux-x86_64-glibc23/share/mysql/charsets/

collation connection – utf8_unicode_ci

(Global value) – latin1_swedish_ci

collation database – latin1_swedish_ci

collation server – latin1_swedish_ci

I also modified wp-config.php to explicitly set

“define(‘DB_COLLATE’, ‘utf8_general_ci’);”.

This did not make a difference, however I am not sure if the WP instance needs to be restarted to make wp-config.php changes take affect?

My tables have no real data yet, and I know how to manually change the collation setting for each column. Here are my specific questions:

1) Is “utf8_general-ci” the appropriate collation setting for all eMember table columns?

2) Is my easiest solution to make the manual changes to the newly created tables?

3) Will future plugin upgrades affect the column collation settings? (yes, I follow the recommended upgrade procedure, including database backups)

4) Do the answers apply to WP eStore v6.5.5, WP Affiliate Platform v5.0.5 & PDF Stamper as well?

Your help and efforts are much appreciated, the plugins are a terrific value!

Thanks in advance,

Peter

[admin]

None of our plugins specify the collation value so that it can take the value you specify on your server side.

I will be able to answer your question better if I know what language you will be using on this site. Please let me know what language you plan to use on this site in question.

[halmobi]

Thanks for your quick response!

You are right, the overall issue is now known to be on my server side. I have had more dialogue with host support, I know how to change the database collation. Now I am trying to get them to explain why the server default is “latin1_swedish_ci”. I live in Arizona, USA so I need education from them on that, so I can avoid having to manually intervene during database management tasks.

I will be using USA English, and so would like to confirm that “utf8_general_ci” is appropriate from your perspective.

Also, can you please confirm that deactivating the plugin in WordPress admin (not deleting the directory files), then deleting the tables in the database, then reactivating the plugin would recreate the tables without changing the plugin directory files?

Thanks! – Peter

[admin]

If you are going to be using “English” then don’t worry about that collation stuff. Both “UTF-8” and/or “Latin” will work just fine.

Yes, if you delete the database tables then reactivate the plugin it will recreate the tables.

[halmobi]

Thanks for all your support and information!

[Author]

Posts