How to remove duplicate data with MySQL

Quite often I have found myself wanting to remove some duplicate data from a MySQL table without having to 1) write a script to de-dupe the table or 2) copy the data into a new table with unique indexes.

Well fortunately there is a quick (and dirty?) solution. Say for example you wanted to get rid of duplicate e-mail addresses in a table, then you could do the following:

 ALTER IGNORE TABLE customer ADD UNIQUE (email);

By using the IGNORE keyword, it changes the way ALTER TABLE works so that only the first row with a particular e-mail address is kept, further rows containing that e-mail address are simply dropped.

Do we need a house price crash?

I just got an e-mail from Newsnight; it seems tonight they will be debating whether we need a house price crash in the United Kingdom.

I think the answer to that is:

  • Yes, if we want young people to be able to afford to buy a home.
  • Yes, if the people stuck in rabbit-hutch sized flats want to buy a bigger home.
  • Yes, if we want our economy to be based on producing something other than buy-to-let investors.
  • But crash is quite a harsh word. I think correction is more pertinent. We need the market to return to a state of sanity, where a professional such a policeman or a nurse can afford (on their own salary without government help) something bigger than a 1 bedroom flat.

    To fix things in the longer term…

    We should loosen planning laws to allow the market to create enough supply to meet demand. There is plenty of land available; only 8% of the UK is urban, half that of the Netherlands, and lower than Belgium, West Germany and Denmark.

    We should encourage larger family homes with gardens to be built. Only 2% of the population aspires to live in a flat, and yet half of new homes built are now flats. Houses with gardens are better for bio-diversity than even an open green field.

    Britain has the smallest and oldest houses in Europe, and despite advances in technology recent new builds are even smaller.

    We should scrap shared equity schemes that matters worse by effectively propping up the market with tax payers money. If people stopped buying because they couldn’t afford to, then developers would be forced to lower prices.

    The Bank of England should target money supply growth when they target inflation.

    Banks should not be allowed to lend more than three and a half times income.

    Tesco online should collect plastic bags for recycling

    One thing that really frustrates me is the amount of plastic shopping bags that we accumulate in our house over the course of a month. Unfortunately, Rushmoor council’s recycling facility won’t accept plastic bags in their recycling collection, and since we do all pretty much all our grocery shopping online with Tesco now, I rarely get a chance to go to the Supermarket to return the bags.

    I own a car, so in theory, I could drive out of my way to return them, but it’s hardly an “environmental” solution, and what’s more it completely defeats the point of shopping on-line in the first place…

    However, the solution to this problem is elegantly simple: Tesco (and indeed all the other online grocers) should encourage their drivers to collect used plastic bags from their online customers when they make a delivery. They can then take them back to the store/depot for recycling. They could even go one stage further and collect glass bottles and any other excess packaging that I have purchased from them but was unable to recycle through my blue bin.

    Not only would this assist them with their environmental obligations, but it would also be an added benefit of shopping online with them (especially for lazy people like me!), which would ultimately increase their sales.

    If you also think this is a good idea, then I would encourage you to write to Terry Leahy, (Tesco’s Chief Executive).

    Update

    Soon after submitting this blog post, I found out that Tesco will already accept plastic bags from home delivery customers via their drivers, so well done Tesco! However, I still think they should also accept glass bottles and get their drivers to actively promote this service.

    Ubuntu PXE (network) install with Fiesty Fawn (7.04)

    1) First of all, setup tftpd on the server where you want to host the network boot files:
    
    sudo bash
    apt-get install tftp-hpa tftpd-hpa xinetd
    vim /etc/xinetd.d/tftpd
    
    2) Paste in the following:
    
    ---- BEGIN CUT ----
    
    service tftp
    {
            disable = no
            socket_type = dgram
            protocol = udp
            wait = yes
            user = root
            server = /usr/sbin/in.tftpd
            server_args = -s /tftpboot
    }
    
    ---- END CUT ----
    
    3) Make the tftp user account:
    
    useradd -d /tftpboot tftp
    
    4) Download the latest ubuntu "netboot" files:
    
    lftp -c "open http://archive.ubuntu.com/ubuntu/dists/feisty/main/installer-i386/current/images/; mirror netboot/"
    mv netboot/ /tftpboot/ubuntu7.04
    
    5) Setup your DHCP client to give out the following file via BOOTP
    
    ubuntu7.04/pxelinux.0
    
    

    Managing SSH Keys in Mac OS X

    For some reason that I haven’t yet worked out my OS X SSH Agent has stopped prompting me for a password when I load it up to add my private key. So I wondered what’s involved to manage my ssh keys with the command line instead.

    First up, I had to install my public and private keys in ~/.ssh/

    
    drwx------    5 paul  paul    170 Apr 24 20:32 .
    drwxr-xr-x   40 paul  paul   1360 Apr 18 23:19 ..
    -rwx------    1 paul  paul    951 Oct  6  2006 id_rsa
    -rwx------    1 paul  paul    218 Oct  6  2006 id_rsa.pub
    
    

    Note the folder needs to be owned by your user with 700 permissions.

    Once the keys are in, the actual process of starting an ssh-agent and adding a key is pretty straightforward:

    
    eval `ssh-agent`
    ssh-add ~/.ssh/id_rsa
    
    

    You will be prompted for a password and then that’s it! To confirm your key has been added correctly, you can list all the keys in your agent with:

    
    ssh-add -l
    

    How to fix the backspace key in screen

    I’m running Ubuntu 7.04 on my home server, and for some reason today the backspace key stopped working correctly in screen. Whenever I pressed it, it would give a ‘Wuff Wuff’ error! It turns out that screen was treating it like the delete key instead, and so since there were no characters in front of the cursor, it was giving an error. To fix this however, is relatively straightforward:

    First edit your .bashrc file and add an alias for screen:
    
    $ vim ~/.bashrc
    alias screen='TERM=screen screen'
    
    Then run:
    
    $ source ~/.bashrc
    
    And that's it!