Top Nav

Archive | Databases

Unknown collation: ‘utf8mb4_unicode_ci’

When trying to import MySQL dumps from newer servers to older servers you may get:


The quick and dirty solution is to do a search and replace on the database dump changing “utf8mb4_unicode_ci” and “utf8mb4_unicode_520_ci” to “utf8_unicode_ci”.

This can be accomplished with sed:


PostgreSQL DESCRIBE TABLE Equivalent

In MySQL I always use “DESCRIBE TABLE” or “SHOW CREATE TABLE” to show the schema of a table but I always forget how to do this in PostgreSQL. The PostgreSQL equivalent is:




Change Minimum Word Length In Fulltext Search

By default, MySQL sets the minimum word length in full text search indexes to 4 characters. Here are the steps to lower this limit to 3.

Edit /etc/my.cnf and add

Restart MySQL to apply the change.

Get a list of full text indexes using this query:

Do a repair on each table with a full text index:





MySQL InnoDB File Per Table

By default MySQL 5.5 and prior store all InnoDB tables in a single table space or file. Improved performance and managability can be accheived by using the innodb_file_per_table option to cause MySQL to use a separate file for each table. This option is enabled by default on MySQL 5.6 and later.

To implement:
1. Add option to my.cnf:

2. Change table to use single file:

If you want to covert all databases then dump the entire database and import back into the server.



Mysqldump TRIGGERS Only

Here’s the command to dump the triggers and stored procedures for a database: