Posted on

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:

SELECT
COLUMN_NAME,
CHARACTER_SET_NAME,
COLLATION_NAME
FROM
information_schema.`COLUMNS`
WHERE
table_name = “yourtablenamehere”;

 

2. Identify the character set that the database uses:

SELECT
default_character_set_name
FROM
information_schema.SCHEMATA
WHERE
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;

Posted on

Setting up the LAMP Stack from scratch on Ubuntu

When migrating servers to AWS, its great to be able to set them up easily and quickly. This is effectively a lovely cheat-sheet. By running the scripts below you can have a full LAMP Stack running on an EC2 instance running Ubuntu in less than 5 minutes.

/* Set locale to UK - V IMPORTANT!! */
sudo locale-gen en_GB.UTF-8

/* Run update and upgrades */
sudo apt-get update
sudo apt-get upgrade

/* Install Apache 2 */
sudo apt-get install apache2

/* Server internal firewall config */
sudo ufw allow OpenSSH
sudo ufw allow in "Apache Full"
sudo ufw enable

/* Install MySQL */
sudo apt-get install mysql-server

mysql_secure_installation
(No,No,Yes,Yes,Yes,Yes)

/* Install php and associated plugins as needed */
sudo apt-get install php libapache2-mod-php php-mcrypt php-mysql
sudo apt-get install php-curl php-gd php-mbstring php-mcrypt php-xml php-xmlrpc

/* Restart services */
sudo systemctl restart apache2

/* Update Apache2 config to allow .htaccess files and modified file permissions */
sudo nano /etc/apache2/apache2.conf

<Directory /var/www/html/>
AllowOverride (Change None to All)
</Directory>

/* Turn on rewrite rules for Apache2 */
sudo a2enmod rewrite
sudo systemctl restart apache2

/* Update permissions so that FTP can write to the web root folder */
sudo chown -R ubuntu:www-data /var/www/html

/* Reboot server */
sudo reboot

Posted on

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:

[mysqld_safe]
[mysqld]

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:

innodb_buffer_pool_size=2048M
innodb_log_file_size=25M
innodb_log_buffer_size=80M

Posted on

MAMP Pro – Access MySQL from another machine

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

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

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

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.

Posted on

Server upgrade

I was finding that the server hosting my blog was running slowly, so the very nice people at Brinkster upgraded the site to a new faster server last night – to great effect 🙂

The blog now has a new faster MySQL database server as well – shame they wont support running it from Microsoft SQL Server – would work much better under load!