Monday, 26 April 2010

If InnoDB Then lower_case_table_names Equals 1

In MySQL system variable lower_case_table_names controls if identifiers are case sensitive. The default setting is 0 if the OS file system is case sensitive, if not 1 - there some exceptions when it comes to Mac OS. However the documentation recommends lower_case_table_names = 1 when using the InnoDB engine.


"Exception: If you are using InnoDB tables, you should set lower_case_table_names to 1 on all platforms to force names to be converted to lower case."


http://mysql2.mirrors-r-us.net/doc/refman/5.1/en/identifier-case-sensitivity.html


UPDATE: As it is on 15/06/2010 this parameter cannot be changed on Amazon RDS instances. It is set to lower_case_table_names=0 it would seem.

Sunday, 18 April 2010

Installing/Upgrade an official release of Django on Windows

Google App Engine SDK 1.3 includes Django 0.96 however App Engine itself supports 1.1 so if you wish to develop against Django 1.1 you will have to manually install it as follows.
  1. Delete your Django site-package from your Python site-packages folder (typically <python-install-dir>/lib/site-packages).
  2. Download the latest release from our download page.
  3. Untar the downloaded file (e.g. tar xzvf Django-NNN.tar.gz, where NNN is the version number of the latest release). You can download the command-line tool bsdtar to do this, or you can use a GUI-based tool such as 7-zip.
  4. Change into the directory created in step 2 (e.g. cd Django-NNN).
  5. If you're using Linux, Mac OS X or some other flavor of Unix, enter the command sudo python setup.py install at the shell prompt. If you're using Windows, start up a command shell with administrator privileges and run the command setup.py install.

Wednesday, 7 April 2010

Amazon RDS - Importing a database using the MySQL Administrator tool

Using the backup and restore feature of the MySQL Administrator Tool does seem to work against Amazon RDS instances. I've done a basic test importing a very small database containing a handful of tables (including a blob fields), a trigger, and indexes. However I came across the following error initially. 


"Error while executing this query:DROP TRIGGER /*!50030 IF EXISTS */ `your_trigger_name`;
The server has returned this error message:You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
MySQL Error."


Started going down the SUPER privilege route as most people would I would imagine. I discovered very quickly that you cannot change user privileges on Amazon RDS instances. Looking at Amazons recommendations for importing data I found they recommend switching off automated backup and binary logging, during import procedure, to improve import performance and reduce storage requirements - full details at 
Amazon RDS Customer Data Import Guide for MySQL 


It worked like a charm!


HOWTO: Disbale Automatic Backup and Binary Logging in Amazon RDS


C:\>rds-modify-db-instance <your_DbInstanceIdentifier> --backup-retention-period 0 --apply-immediately


This will apply the change immediately meaning the database will be unavailable while it applies and reboots the instance. This took about 5min on an empty small instance.


C:\>rds-modify-db-instance <your_DbInstanceIdentifier> --backup-retention-period 0


This will apply the change during the next schedules maintenance window


C:\>rds-describe-db-instances --headers


To check if the change has been applied.


Don't forget to switch on automatic backup after the import procedure is complete by changing the backup retention period to a value greater than 0.


You need the Amazon RDS Command Line Toolkit installed for the above command to work.