Database Collation Error

[twmbx]

https://github.com/getherbert/framework/blob/dev/Herbert/Framework/Providers/HerbertServiceProvider.php#L138

Experienced

Syntax error or access violation: 1253 COLLATION 'utf8_unicode_ci' is not valid for CHARACTER SET 'utf8mb4'

error with the current 'utf8_unicode_ci' collation specified in registerEloquent.
Only managed to get plugin to activate by changing it to 'utf8mb4_unicode_ci'

Can this be exposed as a config option to the developer instead of hard coded?

[brucevdkooij]

Ran into this too. Would make sense to change it to the WP global DB_COLLATE.

[brucevdkooij]

@jasonagnew Bummer though. Looks like the default value of DB_COLLATE in wp-config.php may be '' (empty string). So if you don't modify it and try to query the WP database using the capsule connection you'll still get:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1273 Unknown collation: '''

I had to change DB_COLLATE manually to the new default utf8mb4_unicode_ci to get things to work.

I'm not sure what would be The Best Way (TM) to work around this in the framework but I thought I'd let you know.

[tricki]

DB_COLLATE was empty in my installation as well and it took me a while to find this issue.

I think it should at least be in the "Getting Started" docs. Or are there any downsides to explicitly setting DB_COLLATE to utf8mb_unicode_ci?

[rossjcooper]

I ran into this issue too, would definitely be worth adding to the docs

[markdieselcore]

I, too, have this issue. There has to be a better way than changing the hardcoded config file in Wordpress. People won't use plugins built on Herbert if they throw DB errors and we can't expect the average user to modify their hardcoded WP settings for our plugin.

[bayareawebpro]

Suggested Fix:
Use the global $wpdb object to access the collate. This doesn't hit the database as far as I know.

            global $wpdb;
            $database = new Capsule();
            $database->addConnection([
                'driver' => 'mysql',
                'host' => DB_HOST,
                'database' => DB_NAME,
                'username' => DB_USER,
                'password' => DB_PASSWORD,
                'charset' => $wpdb->charset,
                'collation' => $wpdb->collate,
                'prefix' => $wpdb->prefix,
            ]);

[tranphuoctien]

Hi all, to fix it just change charset in file wp-config.php

/** MySQL hostname */
define('DB_HOST', 'localhost');

/** Database Charset to use in creating database tables. */
define('DB_CHARSET', 'utf8');

/** The Database Collate type. Don't change this if in doubt. */
define('DB_COLLATE', '');