MySQL inserts duplicate records despite unique index

Okay, I thought I knew quite a bit about databases, so I did not expect any surprises when I added a unique index to my MySQL table. For those who do not know what a unique index is:

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. – MySQL manual

After adding the index the table looked like this:

CREATE TABLE `table` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  UNIQUE KEY `a_b_c` (`a`,`b`,`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

And I was trying to insert or increment values in it like this:

INSERT INTO `table` (a,b,c) VALUES (1,NULL,3),(1,NULL,3) ON DUPLICATE KEY UPDATE c=c+VALUES(c);

I expected to get the following result:

mysql> select * from `table`;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | NULL |    6 |
+------+------+------+
1 row in set (0.00 sec)

But it did not do that! I got this result instead:

mysql> select * from `table`;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | NULL |    3 |
|    1 | NULL |    3 |
+------+------+------+
2 rows in set (0.00 sec)

I was clueless until I read the documentation of MySQL a little better:

This constraint does not apply to NULL values except for the BDB storage engine. For other engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL. – MySQL manual

Especially the part where it says: “does not apply to NULL values” makes things much, much clearer. 🙂 Note that Microsoft SQL Server behaves different (they way I expected). So keep this is mind when using a unique index in MySQL, because I certainly did not expect this behavior!

Share

Install Adminer manually on Ubuntu 14.04

adminerAs I wrote over two years ago, Adminer is a very good alternative to PHPMyAdmin. I often find myself looking up that old post, because I frequently install, recommend or update Adminer. After using this software for several years, I am now convinced that it is (has become) much better than PHPMyAdmin. Especially since the new user interface of PHPMyAdmin has become worse. Adminer has progressed a lot and is at version 4.1.0 today. I simply love version 4 and I use it almost daily. The top 3 reasons (for me) to choose it are:
  1. Very clear and consistent user interface
  2. It automatically adds foreign keys
  3. You can easily reorder columns in a table

I think that once you give it a (serious) try, you will never want to use PHPMyAdmin (or any other database management tool) again… ever.

Install Adminer from the repository

It is also great that Adminer is now part of the standard Ubuntu repositories. This means that you can install it with “sudo apt-get install adminer”. However, I do not recommend this. The version of Adminer in the repository is version 3.3.3-1. And it is a very active project with great improvements in every version. Also, upgrading does not hurt, since it handles its dependencies very flexible. In my experience you can run the latest version on any recent Linux without any compatibility issues.

Install Adminer manually

Here are the commands you need for installation (on a Debian based system) that runs Apache 2.4, like Ubuntu 14.04:

sudo mkdir /usr/share/adminer
sudo wget "http://www.adminer.org/latest.php" -O /usr/share/adminer/latest.php
sudo ln -s /usr/share/adminer/latest.php /usr/share/adminer/adminer.php
echo "Alias /adminer.php /usr/share/adminer/adminer.php" | sudo tee /etc/apache2/conf-available/adminer.conf
sudo a2enconf adminer.conf
sudo service apache2 restart

Updating and uninstalling

This is the one-liner for updating Adminer:

sudo wget "http://www.adminer.org/latest.php" -O /usr/share/adminer/latest.php

And these are the commands needed for uninstallation:

sudo a2disconf adminer.conf
sudo service apache2 restart
sudo rm /etc/apache2/conf-available/adminer.conf
sudo rm -Rf /usr/share/adminer

If you know of any tool that is as good as Adminer, then let us know in the comments.

Share

Daily MySQL off-site backups using logrotate and rsync

For daily MySQL off-site backups I was looking for a simple approach. Scott Miller’s minimal, but concise post on MySQL Backups With Logrotate  lead me to the unusual choice of logrotate to backup MySQL. What Scott was not doing, and I did need, was off-site backup. For this I had set up rsync access to the machine with public key authentication.

After setting up the daily rsync with some help from this post of Uwe Hermann I found out that the logrotate files were all transfered every time rsync ran, so not only the new ones. This turned out to be due to the default naming scheme of logrotate (appending sequential numbers). Fortunately I found a post by Eric Thern titled logrotate tweaks to enhance rsync backups that explains that one can use the “dateext” option to make the logrotate files “rsync friendly”.

The last thing I had to find out is when the logrotate task was fired to choose a good time for my cronjob that fired the rsync. I found a post on serverfault that showed me how to find out when the daily cron tasks were started. It turned out they were started at 6:25 AM on my Debian based machine. I scheduled the cronjob on the remote server to rsync at 9:00 AM to be sure the backup was finished.

Instructions

I will go through all the files I modified on the production server and the (off-site) backup server. On the production server I created the file “/etc/logrotate.d/mysql-bkup”:

/home/maurits/bak/project.sql.gz {
daily
dateext
rotate 8
nocompress
create
postrotate
mysqldump -uproject -pproject project > /home/maurits/bak/project.sql --single-transaction
gzip -9f /home/maurits/bak/project.sql
chown maurits:maurits /home/maurits/bak/project.sql.gz
chmod 640 /home/maurits/bak/project.sql.gz
endscript
}

As you can see the logrotate is run daily, with the dateext option for better rsync efficiency. It keeps 8 backups (7 days) and does not apply compression (we do that manually). The create option will make sure new files are created with the same permissions and owner as the previous ones. We finish with the “postrotate” script that does a mysqldump, gzip and then sets a proper owner and permissions. To test run the script you can execute:

sudo logrotate -f /etc/logrotate.d/mysql-bkup

After running it we can check the created files:

maurits@nuc:~$ ls bak -al
total 18192
drwxr-xr-x  2 maurits maurits    4096 Jun 22 15:17 .
drwxr-xr-x 10 maurits maurits    4096 Jun 22 14:54 ..
-rw-r-----  1 maurits maurits 9291115 Jun 22 15:17 project.sql.gz
-rw-r-----  1 maurits maurits 9291114 Jun 22 15:06 project.sql.gz-20130622

Now we login to the (off-site) backup server. Here we need to configure the cronjob to retrieve the logrotated MySQL backups using rsync. We add the cronjob by editing the crontab with:

maurits@nuc:~$ crontab -e

Then we add the rsync command to the crontab. Mine looks like this:

# m h  dom mon dow   command
0 9 * * * rsync -a --delete-during www.project.nl:bak ~

As you can see we configured that at minute 0 of hour 9 on every day of the month, of every month, on any day of the week we run the rsync command. This is all there is to it. If you have any questions, remarks or improvements, please use the comments section below.

Share

UTF-8 in PHP and MySQL under Ubuntu 12.04

UTF-8 is the de facto standard character set for PHP websites and there are but a few reasons not to use UTF-8 (utf_general_ci) as the default MySQL database collation. However, anyone arguing that UTF-16 is a better standard would probably be right, but because UTF-8 is more popular, nobody cares. Unfortunately, the guys at Ubuntu (or upstream at Debian, PHP and MySQL) still have some strange defaults configured in their software, as follows:

  1. PHP connects explicitly to MySQL with an “Latin 1” character set unless you send the “set names utf8” query.
  2. Apache does not specify a character set by default (nor does PHP), letting the browser determine which character set is used.
  3. MySQL sets the “latin1” as default character set and “latin1_swedish_ci” as default collation (for string comparison).

This is a longstanding issue. The reason for these western/Swedish defaults is that MySQL AB has a Swedish origin. Now that MySQL is the world’s most popular web database, and has been bought by Oracle (based in California/US), it seems like a strange choice. These days you would expect the following defaults:

  1. PHP connects to the server and uses the character set of the server, unless specified.
  2. Apache should assume all text content to be UTF-8 encoded.
  3. MySQL should have UTF-8 as the default character set and “utf_general_ci” as the default collation.

It is easy to make Apache/MySQL/PHP (under Ubuntu 12.04) behave the way you like. First we add the character set to Apache:

sudo echo "AddDefaultCharset utf-8" >  /etc/apache2/conf.d/utf8.conf

Now for MySQL, we open “/etc/mysql/my.cnf” and under the “[mysqld]” section we add the following 3 lines:

[mysqld]
...
character-set-server=utf8
collation-server=utf8_general_ci
init-connect='SET NAMES utf8'

For a default of UTF-8 in the MySQL command line client (optional) you must add the following line in the “/etc/mysql/my.cnf” file under the “[client]” section:

[client]
...
default-character-set=utf8

Now restart the Apache and MySQL servers with the following commands:

sudo service mysql restart
sudo service apache2 restart

This is really all you have to do on a default Ubuntu 12.04. To check whether or not everything works correctly put the following “utf8.php” file on your website:

<?php
mysql_connect('localhost', 'username', 'password');
mysql_select_db('database');
$re = mysql_query('SHOW VARIABLES LIKE "c%";')or die(mysql_error());
while ($r = mysql_fetch_assoc($re))
{ echo $r&#91;"Variable_name"&#93;.': '.$r&#91;"Value"&#93;; echo "<br />";
}

The output should be:

character_set_client: utf8
character_set_connection: utf8
character_set_database: utf8
character_set_filesystem: binary
character_set_results: utf8
character_set_server: utf8
character_set_system: utf8
character_sets_dir: /usr/share/mysql/charsets/
collation_connection: utf8_general_ci
collation_database: utf8_general_ci
collation_server: utf8_general_ci
completion_type: NO_CHAIN
concurrent_insert: AUTO
connect_timeout: 10

Let me know if you still have any trouble making it work. Good luck!

Share

Fast Symfony2 served on a stable Xubuntu

Ingredients:

Introduction:

This recipe is for people who want to install symfony2 quickly with all dependencies, without having to read the excellent documentation that can be found here:

  1. The quick tour
  2. The book
  3. The cookbook

This recipe can be used by linux users or by windows users that run linux in a virtual machine.

This will get you:

  • A working Symfony2 installation
  • Pretty URL’s (using mod_rewrite)
  • Optimized execution speed (using php5-apc)
  • Configured timezone settings and internationalization support (using php5-intl)
  • Run as a specific user and not as www-data (using apache2-mpm-itk)
  • Created a database and installed a database management tool (phpmyadmin).

The instructions are meant for a development platform. If you use these instructions on a production platform the least you should do is choose a more secure password. NB: You also might want to run Ubuntu Server instead of Xubuntu in such a case.

This will NOT get you:

  • A debugging environment (using XDebug)
  • An PHP IDE with debugging integration (Eclipse PDT)

For serious web development you might want the above: a full-featured IDE and a step-by-step debugger. This will be discussed in another post.

Instructions:

The lines below with a “-” are actions and the lines with a “$” are commands:


- Download and install Virtualbox
- Download xubuntu iso
- Configure a new Virtual Machine with:
  OS set to: Linux/Ubuntu 64
  4 cores (assuming you have 8 cores)
  2048 MB RAM (assuming you have at least 4096)
  20 GB VDI dynamic disk
- Select the Xubuntu iso file in the first run wizard
- Install Xubuntu by following the steps
- Install additional drivers (guest additions) and reboot
- Open up a web browser
- Go to http://symfony.com/download and click download
- Save "Symfony_Standard_Vendors_2.x.xx.tgz" and unpack it
- Move the "Symfony" folder to your home directory and rename it to "public_html"
$ sudo apt-get install lamp-server^ php-apc php5-intl php5-sqlite phpmyadmin
 choose a mysql root password
 choose to configure phpmyadmin for apache2
$ sudo apt-get install apache2-mpm-itk
$ sudo a2enmod rewrite
$ sudo nano /etc/php5/apache2/php.ini
 NB: use Ctrl-W Ctrl-R to search and replace
 search: ;date.timezone =
 replace: date.timezone = Europe/Amsterdam
 NB: enter your local timezone if you are not in Europe/Amsterdam
 (see http://en.wikipedia.org/wiki/List_of_tz_database_time_zones)
 search: short_open_tag = On
 replace: short_open_tag = Off
$ pwd
 NB: note down the output, as this is the path of your home folder (in my case: /home/maurits)
$ id -un
 NB: note down the output, as this is your user name (in my case: maurits)
$ id -gn
 NB: note down the output, as this is your group name (in my case: maurits)
$ sudo nano /etc/apache2/sites-enabled/000-default
 NB: use Ctrl-W Ctrl-R to search and replace
 search: /var/www
 replace: {YOUR_HOME_FOLDER}/public_html/web
 NB: you have to enter the home folder path you found from the pwd command instead of {YOUR_HOME_FOLDER}
$ sudo nano /etc/apache2/sites-enabled/000-default
 NB: add the following line under the DocumentRoot
 AssignUserId {YOUR_USER_NAME} {YOUR_GROUP_NAME}
 NB: you have to enter your user name and group name instead of {YOUR_USER_NAME} {YOUR_GROUP_NAME}
$ sudo service apache2 restart
- Open web browser and go to http://127.0.0.1/phpmyadmin
- Login with root user and chosen root password
- Click "Privileges"
- Click "Add a new User"
- Enter/choose:
 username: symfony
 host: localhost
 password: symfony
 retype: symfony
- Select "Create database with same name and grant all privileges"
- Click "Create User"
- Open a web browser and go to http://127.0.0.1/config.php
- verify that symfony does not complain about anything
- click "Configure your Symfony Application online"
- Enter/choose:
 Driver: MySQL (PDO)
 Host: localhost
 Name: symfony
 User: symfony
 Password: symfony
 Password again: symfony
- Let symfony generate a "Global Secret"
- Choose "Next Step"
- Symfony should say: Your distribution is configured!
- Click "Go to the Welcome page"
- Click "Run the Demo"
- Play around with the demo to see that everything works

Bonus instructions:

Some nice tips on how to customize your Xubuntu:

That is it for today, have fun!

Share