Top Nav

Archive | Databases

Unknown collation: ‘utf8mb4_unicode_ci’

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

or:

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:

0

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:

 

 

0

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:

 

 

 

0

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.

 

0

Mysqldump TRIGGERS Only

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

 

0