MySQL Collation Error

Occasionally I come across databases written by other developers who have a complete mix of character sets, and where the database character set does not match the character set of fields within a table. This causes all sorts of nasty errors when trying to use CONCAT or CONCAT_WS. Errors like:

Illegal mix of collations for operation ‘concat_ws’)

The most common cause is that the database type does not match the field or table types.

1. Identify the character set that the fields within the table use with the following sql script:

table_name = “yourtablenamehere”;


2. Identify the character set that the database uses:

schema_name = “solution9ssu”;


These should be the same, however a very common difference is that some developers create the database using utf-8 and the fields imported are in latin1_swedish_ci. This causes the collation error.

To solve: alter the character encoding of each table. NB. This can cause all manner of issues, so take a full backup first!

ALTER TABLE yourtablename CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

MySQL ibtmp1 file grows exponentially

Recently we have had several servers run out of hard drive space causing inaccessibility and crashing. On further investigation the hard drive was full so many operations were complaining. The hard drive was being consumed by a very large file within the /var/lib/mysql directory called ibtmp1.

ibtmp1 is a temporary working file for MySQL. When you turn the MySQL server off and on again the file is deleted and the server runs again without issue until the file becomes too large again.

After some quick research we found that adding or editing a few settings in the MySQL configuration allows you to limit the size of this file and prevent this issue from reoccurring.

Open the mysqld.cnf file. The location of our mysqld.cnf was in /etc/mysql/mysql.conf.d/mysqld.conf. According to the file you can also copy this configuration to /etc/mysql/my.cnf. Our my.cnf just had an include to the mysqld.cnf file.

The file should have some sections like:


There should be a section for InnoDB settings. If not, it is fine to add these configuration settings at the bottom of the file.

Here is the configuration variable:

innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:1G

This value can be tweaked based on your server setup and needs but these are the settings we used for our server to set the file to start at 12 MB and limit it to 1 GB.

While setting this we also set a few other variables for optimisation:


SQL Nationalities Table (SQL format)

In the same way that getting a list of countries of the world can be tedious when developing a web-based system or website, nationality is one of those drop-down list boxes that is very time-consuming to populate behind the scenes. In many cases most developers resort to the copy/paste from Wikipedia method. So after doing that, I decided to export the SQL so that you don’t have to!

CREATE TABLE `settingsNationalities`
`NationalityID` int(11) NOT NULL AUTO_INCREMENT,
`Nationality` varchar(200) DEFAULT NULL,
PRIMARY KEY (`NationalityID`)

and then run the insert query below: (take care that the single quotes don’t get changed into back-ticks meaning that the ones with spaces won’t run)

INSERT INTO ‘settingsNationalities’ VALUES (1,’Afghan’),(2,’Albanian’),(3,’Algerian’),(4,’American’),(5,’Andorran’),(6,’Angolan’),(7,’Antiguans’),(8,’Argentinean’),(9,’Armenian’),(10,’Australian’),(11,’Austrian’),(12,’Azerbaijani’),(13,’Bahamian’),(14,’Bahraini’),(15,’Bangladeshi’),(16,’Barbadian’),(17,’Barbudans’),(18,’Batswana’),(19,’Belarusian’),(20,’Belgian’),(21,’Belizean’),(22,’Beninese’),(23,’Bhutanese’),(24,’Bolivian’),(25,’Bosnian’),(26,’Brazilian’),(27,’British’),(28,’Bruneian’),(29,’Bulgarian’),(30,’Burkinabe’),(31,’Burmese’),(32,’Burundian’),(33,’Cambodian’),(34,’Cameroonian’),(35,’Canadian’),(36,’Cape Verdean’),(37,’Central African’),(38,’Chadian’),(39,’Chilean’),(40,’Chinese’),(41,’Colombian’),(42,’Comoran’),(43,’Congolese’),(44,’Congolese’),(45,’Costa Rican’),(46,’Croatian’),(47,’Cuban’),(48,’Cypriot’),(49,’Czech’),(50,’Danish’),(51,’Djibouti’),(52,’Dominican’),(53,’Dominican’),(54,’Dutch’),(55,’Dutchman’),(56,’Dutchwoman’),(57,’East Timorese’),(58,’Ecuadorean’),(59,’Egyptian’),(60,’Emirian’),(61,’Equatorial Guinean’),(62,’Eritrean’),(63,’Estonian’),(64,’Ethiopian’),(65,’Fijian’),(66,’Filipino’),(67,’Finnish’),(68,’French’),(69,’Gabonese’),(70,’Gambian’),(71,’Georgian’),(72,’German’),(73,’Ghanaian’),(74,’Greek’),(75,’Grenadian’),(76,’Guatemalan’),(77,’Guinea-Bissauan’),(78,’Guinean’),(79,’Guyanese’),(80,’Haitian’),(81,’Herzegovinian’),(82,’Honduran’),(83,’Hungarian’),(84,’I-Kiribati’),(85,’Icelander’),(86,’Indian’),(87,’Indonesian’),(88,’Iranian’),(89,’Iraqi’),(90,’Irish’),(91,’Irish’),(92,’Israeli’),(93,’Italian’),(94,’Ivorian’),(95,’Jamaican’),(96,’Japanese’),(97,’Jordanian’),(98,’Kazakhstani’),(99,’Kenyan’),(100,’Kittian and Nevisian’),(101,’Kuwaiti’),(102,’Kyrgyz’),(103,’Laotian’),(104,’Latvian’),(105,’Lebanese’),(106,’Liberian’),(107,’Libyan’),(108,’Liechtensteiner’),(109,’Lithuanian’),(110,’Luxembourger’),(111,’Macedonian’),(112,’Malagasy’),(113,’Malawian’),(114,’Malaysian’),(115,’Maldivan’),(116,’Malian’),(117,’Maltese’),(118,’Marshallese’),(119,’Mauritanian’),(120,’Mauritian’),(121,’Mexican’),(122,’Micronesian’),(123,’Moldovan’),(124,’Monacan’),(125,’Mongolian’),(126,’Moroccan’),(127,’Mosotho’),(128,’Motswana’),(129,’Mozambican’),(130,’Namibian’),(131,’Nauruan’),(132,’Nepalese’),(133,’Netherlander’),(134,’New Zealander’),(135,’Ni-Vanuatu’),(136,’Nicaraguan’),(137,’Nigerian’),(138,’Nigerien’),(139,’North Korean’),(140,’Northern Irish’),(141,’Norwegian’),(142,’Omani’),(143,’Pakistani’),(144,’Palauan’),(145,’Panamanian’),(146,’Papua New Guinean’),(147,’Paraguayan’),(148,’Peruvian’),(149,’Polish’),(150,’Portuguese’),(151,’Qatari’),(152,’Romanian’),(153,’Russian’),(154,’Rwandan’),(155,’Saint Lucian’),(156,’Salvadoran’),(157,’Samoan’),(158,’San Marinese’),(159,’Sao Tomean’),(160,’Saudi’),(161,’Scottish’),(162,’Senegalese’),(163,’Serbian’),(164,’Seychellois’),(165,’Sierra Leonean’),(166,’Singaporean’),(167,’Slovakian’),(168,’Slovenian’),(169,’Solomon Islander’),(170,’Somali’),(171,’South African’),(172,’South Korean’),(173,’Spanish’),(174,’Sri Lankan’),(175,’Sudanese’),(176,’Surinamer’),(177,’Swazi’),(178,’Swedish’),(179,’Swiss’),(180,’Syrian’),(181,’Taiwanese’),(182,’Tajik’),(183,’Tanzanian’),(184,’Thai’),(185,’Togolese’),(186,’Tongan’),(187,’Trinidadian or Tobagonian’),(188,’Tunisian’),(189,’Turkish’),(190,’Tuvaluan’),(191,’Ugandan’),(192,’Ukrainian’),(193,’Uruguayan’),(194,’Uzbekistani’),(195,’Venezuelan’),(196,’Vietnamese’),(197,’Welsh’),(198,’Welsh’),(199,’Yemenite’),(200,’Zambian’),(201,’Zimbabwean’);

Setting up WordPress on EC2 in 10 minutes

This tutorial does a complete walk-through on the basis of you using Mac Os to set up a new EC2 WordPress site. Once you get practiced, you can do this in minutes!

Log in to your EC2 Management Console (

Select a new  instance of Amazon Linux AMI (Free tier eligible)

Screen Shot 2015-11-19 at 13.35.35

Just leave everything un-changed on the next screen and press on the Next: Configure Instance Details button (NOT the Review and launch button)

Leave everything on the next screen un-changed too, and press Next: Add Storage

Again, don’t change anything and press Next Tag Instance

Give your new Server a name (name of the wordpress site/company?) then press Next: Configure Security Group

Add two new rules to allow http and https through the firewall

Screen Shot 2015-11-19 at 13.38.50

Click on the blue Review and Launch button

Press on Launch

Create a new key pair and give it a name (something easy to type)

Screen Shot 2015-11-19 at 13.40.19

download the Key Pair File file (from this point onwards referred to as nameOfKeyPair.pem)

remove the .txt extension

Open terminal and navigate to downloads folder (usually cd /users/yourYourName/Downloads/ )

chmod 600 nameOfKeyPair.pem

ssh-keygen -f nameOfKeyPair.pem -e -m per >

chmod 400 nameOfKeyPair.pem

ssh -i name.pem [email protected]

You will be connected to the new instance

Type the following, pressing Enter after every line

sudo yum update

sudo su

yum install httpd

service httpd start

yum install php php-mysql

yum install mysql-server

service mysqld start

mysqladmin -uroot create myblog


Press Enter (no existing password)
Enter new password (keep a note of this, you need to enter it into the wordpress config in a couple of steps time)

cd /var/www/html


tar -xzvf latest.tar.gz

mv wordpress myblog

cd myblog

mv wp-config-sample.php wp-config.php

vi wp-config.php

—press i for insert—

Make the following changes to the wordpress config file

Database: myblog
user: root
password: what you set it as during the security setup previously

press Esc

press wq to exit editor and save the changes

service https restart

browse to the new site and start configuring by typing http://yourinstanceipaddress/myblog

MAMP Pro – Access MySQL from another machine

To access MySQL that has been installed as part of MAMP:

1.  Click on FileEdit Template and then MySQLmy.cnfScreen Shot 2014-03-18 at 14.55.06

2. On line 37 (normally) you will see the bind-address = (localhost) by default. Change this to:

Screen Shot 2014-03-18 at 14.55.50

3. Open phpMyAdmin on the local machine, click on the Users tab and then Add user

Screen Shot 2014-03-18 at 14.58.19

4. Enter details of the username and password. On the Database for user section choose Grant all privileges on wildcard name. Check all privileges for the user. (Ensure that Grant is ticked under Administration)

Screen Shot 2014-03-18 at 14.59.30

Click on Add user.

5. Restart the MAMP servers.