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;

Adding free SSL to your server using LetsEncrypt

So you have a lovely new website running on your Ubuntu server, but wouldn’t it be nice to have that small padlock to give visitors peace of mind? To have all traffic between your website and the browser encrypted…..but you don’t want to spend days waiting, and a small fortune on an SSL Certificate, then wait for the certification authority to email you, with lengthy confirmations etc. Thankfully, there is a lovely easy way!

All you need to do is to ensure that the domain name (the only part you need to change in the script below, indicated in bold) has a DNS entry pointing to the IP address of the server you are running this on, and hey presto, a free SSL Certificate that automatically renews using a cron task that it adds automatically for you! No more renewals ever again!

NB. When running this, there will be a 10-15 second interruption in the Apache2 web-server as it stops and re-starts, meaning live site visitors at that moment may see an error.
/* Lets Encrypt */
sudo add-apt-repository ppa:certbot/certbot
sudo apt-get update
sudo apt-get install python-certbot-apache

sudo certbot --authenticator standalone --installer apache -d www.yourdomainnamehere.com --pre-hook "systemctl stop apache2" --post-hook "systemctl start apache2"

Follow the on-screen prompts (usually only 2 or 3, and it works seamlessly)!

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

Personality Test Result – ESTP

I’ve just completed a personality test, and I found it shocking just how accurate the result was. I completed the test at https://www.idrlabs.com and was rather surprised at the result. I thought I would be an ENTJ, but after looking at that result, I really understand that I’m not!

E = Extraversion

I = Introversion

S = Sensing

N = iNtuition

T = Thinking

F = Feeling

P = Perception

J = Judgement

Oriented towards action, alert, and crafty, you are hard-headed and adaptable. Flexible, pragmatic, and straightforward, you don’t beat around the bush but tend to get straight to the point. You are quick to size up a situation and quick to arrive at a working solution for addressing whatever problems you may encounter. Your open, shrewd, and agile attitude tends to mean that others sometimes look to you for guidance on what to do. In fact, you also tend to speak of the challenges that lie before you in a way that motivates others to join in and offer their help. Through your relaxed and flexible attitude, and enviable people skills, you are thereby capable of assembling coalitions to do what one man could not. Using any (or at least most) of the means at your disposal to reach your goals, you are persuasive and competitive to a fault and truly an entrepreneurial force to be reckoned with.

https://www.idrlabs.com/estp.php

Interestingly in a similar test, there are some surprising results:

According to a study done by Jeffery J. Mondak, Ph.D., your scores indicate that you are:

  • More likely to favor military intervention as a means of solving foreign policy problems.
  • Less likely to think that international cooperation will solve foreign policy problems.
  • More likely to take responsibility and to take an active interest in your community.
  • More likely to be patriotic and proud of your country.
  • More likely to enjoy romantic fiction than the rest of the population.
  • More likely to have a tolerant attitude towards smoking.
  • More likely to favor mild criminal punishments over harsher ones. – NOOO!
  • More likely to watch TV, read the news, and stay up to date on current events.
  • More likely to mobilize your friends to take part in your own interests.
  • Someone who seems opinionated to others, while being in fact quite open-minded and tolerant of opposing views.
  • More likely to enjoy fitness training and physical exercise.
  • More likely to nurture a few select beliefs that you regard as settled in stone.
  • Less likely to flirt with harm and danger.
  • Less likely to have insurance or to belong to a labor union.

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

Policy Proposal – Broadband

The Practical Party
Making practical policies to make everyones lives that little bit better.

Policy Proposal – Broadband / Internet Connectivity

Minimum broadband speeds
Every household and business will have a legal right to a minimum of 100Mbit internet connectivity, with a 5 year plan to increase this to 1Gbit. There will be a price-cap of £30 per household or business for this connectivity. If BT cannot achieve the FULL roll-out within a 2 year time-scale then a new regional network will be set up to offer 100% wifi coverage on a single network. It will be completely open and free to everyone in the whole country.

Data limits
All data caps both on mobile and fixed line internet connectivity are no longer permitted. There should not be a penalty for using the internet more.

 

NB. As always, we welcome your comments on all policy proposals.

Policy Proposal – Distance Learning

The Practical Party
Making practical policies to make everyones lives that little bit better.

Policy Proposal – Distance Learning

All classrooms will be equipped with webcams and microphones and will be available to the pupils from that classroom so that they can watch along from home in the event of travel disruption or any other cause for being unable to attend school whilst not physically unwell.

 

NB. As always, we welcome your comments on all policy proposals.