Automatic MySQL Backups

Posted on by
Reply

It’s really easy to set up automatic MySQL backups using mysqldump. First, you need to set up a user with SELECT and LOCK TABLES privileges. In this example the user doesn’t have a password.

CREATE USER 'autobackup'@'localhost';
GRANT SELECT, LOCK TABLES ON *.* TO 'autobackup'@'localhost';

Next create the cron job with ~ $ crontab -e. This job is set to run every day at 5:20am.

20 5 * * * mysqldump --user=autobackup dbname | gzip -c > /var/backups/dbname-`/bin/date +\%Y\%m\%d`.sql.gz

And that’s it – you’re done! This will create a file based on the date, e.g. /var/backups/dbname-20120503.sql.gz

MySQL: Specified key was too long; max key length is 767 bytes

Posted on by
Reply

MySQL has a prefix limitation of 767 bytes in InnoDB, and 1000 bytes in MyISAM. This has never been a problem for me, until I started using UTF-16 as the character set for one of my databases. UTF-16 can use up to 4 bytes per character which means that in an InnoDB table, you can’t have any keys longer than 191 characters. Take this CREATE statement for example:

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL,
  `password` varchar(64) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_8D93D649F85E0677` (`username`),
  UNIQUE KEY `UNIQ_8D93D649E7927C74` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf16 AUTO_INCREMENT=1 ;

This will fail with an error like Specified key was too long; max key length is 767 bytes, because the UNIQUE INDEX on the email field requires at least 1020 bytes (255 * 4).

Unfortunately there is no real solution to this. Your only options are to either reduce the size of the column, use a different character set (like UTF-8), or use a different engine (like MyISAM). In this case I switched the character set to UTF-8 which raised the maximum key length to 255 characters.

Git: Ignore changes to already-tracked files

Posted on by
Reply

There are often times when you want to modify a file but not commit the changes, for example changing the database configuration to run on your local machine.

Adding the file to .gitignore doesn’t work, because the file is already tracked. Luckily, Git will allow you to manually “ignore” changes to a file or directory:

git update-index --assume-unchanged <file>

And if you want to start tracking changes again, you can undo the previous command using:

git update-index --no-assume-unchanged <file>

Easy!

Converting an SVN repository to Git

Posted on by
Reply

Today I found out just how easy it is to convert an SVN repository to Git without losing any commit history. Note that you will need git-svn (apt-get install git-svn on Debian/Ubuntu).

git svn clone http://mysvnrepo.com/my-project my-project
cd my-project
git remote add origin [email protected]:/my-project.git
git push origin master

Et voilà, my-project.git has the full commit history of the my-project SVN repository.

If anybody knows whether SVN branches can be converted to Git branches, please get in touch!

Simple Nested Sets in Doctrine 2

Posted on by
4

Unlike Doctrine 1 with it’s NestedSet behaviour, there is no nested set functionality in the core of Doctrine 2. There are a few extensions available that offer nested set support:

I tried all of these extensions, but none of them felt simple or lightweight enough for my application. What I wanted to do was have a Category entity which could have a tree of sub-categories, e.g: Continue reading →

Writing good code is easy

Posted on by
Reply

Everybody wants to write “good code”, right? So why is it that nearly every time we pick up another developer’s work, our WTF-o-meter goes crazy?

Everybody has a different idea of what “good code” is. Below are a few ways that I believe we can increase the quality of our code and reduce the number of WTFs our code generates.

  • Keep it simple; refactor overly-complex methods…
  • …Or if refactoring isn’t feasible, document complex methods.
  • Use descriptive variable and method names.
  • Follow code conventions.
  • Don’t commit unfinished or broken code.

Most of these are just common sense. The trouble is, we throw good coding practices – and common sense – out the window when we’re under pressure from things like slipping deadlines and scope creep. If you ever find this happening, just remember to write your code as if the person who has to maintain it is a violent psychopath who knows where you live. What would you rather: miss a deadline, or be hacked up into little pieces by an angry developer?

Doctrine 2: Resolving “unknown database type enum requested”

Posted on by
14

I came across this recently while I was developing a module for PyroCMS. Some of the PyroCMS tables contain ENUM columns, which Doctrine doesn’t support. You would think that this wouldn’t be an issue since these tables are not mapped, but apparently when Doctrine builds the schema it includes all tables in the database – even if they are not mapped. This has been reported as an issue, but the Doctrine team has given it a low priority.

The symptom? When using the SchemaTool to create, update, or drop the schema; an exception is thrown:

Fatal error: Uncaught exception 'Doctrine\DBAL\DBALException' with message 'Unknown database type enum requested, Doctrine\DBAL\Platforms\MySqlPlatform may not support it.'

Thankfully, the fix is very easy. There is even a Doctrine Cookbook article about it. All you have to do is register the ENUM type as a Doctrine varchar (string):

/** @var $em \Doctrine\ORM\EntityManager */
$platform = $em->getConnection()->getDatabasePlatform();
$platform->registerDoctrineTypeMapping('enum', 'string');

This fix can be applied to any unsupported data type, for example SET (which is also used in PyroCMS):

$platform->registerDoctrineTypeMapping('set', 'string');

Why you should date a web developer

Posted on by
2

Girls always say to me, “I want a really great boyfriend, where do I find one?” The answer is simple: you should date a web developer! Why, you ask? There are dozens, even hundreds of good reasons why you should date a web developer:

  1. Web developers are good with computers
  2. …

… So what are you waiting for? Go out and grab yourself a hunky web developer!

Useful Git Configuration Items

Posted on by
Reply

Name and Email Address

Each commit you make has your name and email address attached to it. Git will automatically configure these based on your username and hostname, but this information is usually not a good identifier. It is a good idea to set your real name and email address so that your commits can be identified easily.

git config --global user.name "Your Name"
git config --global user.email [email protected]

Global Ignore File

Often there are files or directories that you want to ignore in all your Git projects. These are probably created automatically by your IDE, or ‘junk’ files created by the operating system. Here’s a sample global ignore file (I use PhpStorm, which creates an .idea directory in the root of each project): Continue reading →