Changing MySQL Character Set - 29 Jan 2013 12:19
Recently came across a requirement where I had to change the character set of all the databases that were hosted in our MySQL instance(s). Its very common for people to start using the defaults of MySQL till the point they realize they are unable to insert any UTF8 chars and that they need to convert the existing tables from latin1 to UTF8. Because of my newness to databases I really didn't know what to do and how to do it, so started to search the web for it. After a few other useful posts I found this from Ryan Lowe on Percona Blog. That blog post explains different methods for converting the character set & why some might not be the preferred ones. It also has a script that Ryan wrote to convert all tables to a given character set for a given database using the "alter modify" (you can read about the benefits of this approach in the same blog post) approach, which I found pretty useful. So, I sourced and started to use it. I got hit by two problems:
- Foreign Keys restrict referred tables from being renamed, hence they are not converted.
- From the original script's todo list it seems Ryan had plans to handle this.
- As we use InnoDB, Primary and Unique key constraints can't have a column which is more than 255 chars long.
- It seems in MySQL 5.1 (not sure about the other versions) one can't have a key more than 767 bytes long when using InnoDB storage engine. So, 256 * 3 (no. of bytes MySQL uses to store UTF8 chars) = 768 won't work.
- One would see an error like "Specified key was too long; max key length is 767 bytes"
To solve the first problem I modified the script (attached) to do the following:
- Discover Foreign Key constraints at runtime.
- Remove discovered Foreign Key constraints before converting table.
- Add all discovered Foreign Key constraints back after converting all the tables.
- Converting all the tables before adding the FK constraints back is a must because otherwise referred and referring columns' data types might not match and FK addition may fail.
Note: The modified script removed forking children because the parent process was anyways 'wait'ing on children inside the loop, hence not parallelising things. It shouldn't be hard to add forking though.
And AFAIK there are quite a few solutions to the second problem:
- Simplest & probably the cleanest one is to reduce the column size if you can.
- Other one is to use partial content of the column for constraints in question. The validity and effectiveness of this solution depends on several factors including:
- Column content
- Use cases for the column/key in question
- It turns out MySQL version 5.5.14 has introduced a new configuration param called "innodb_large_prefix" which increases this limit to 3072 bytes but one has to use either COMPRESSED or DYNAMIC row format for that to work. Read here for details on both the row formats.
We chose the first one as we found that it was simply the best solution in our case because reducing the column size in our case wouldn't require too many changes on the application side. - Comments: 0
CloudStack One Box Deployment (Advanced Networking) - 02 Sep 2012 15:28
Hello Folks, its cloud time rather private cloud time. As I will be working on both public and private clouds a lot in near future, and as I prefer diving deeper into technologies I thought of starting with deploying, using, managing and developing CloudStack. CloudStack is one of the more mature open source IaaS software offering that makes designing, deploying and maintaining your own IaaS a breeze. Please visit CloudStack for more information. So, now we need to dive deeper into CloudStack but not all of us have access to the resources required to do just that, some of us don't even have access to two networked boxes, so what to do. One option is to use DevCloud which is a XCP based CloudStack deployment in a virtual appliance. And its a real good option for developers but it wasn't enough for me because of a couple of reasons, firstly spawning too many VMs will make the DevCloud VM run real slow (almost unresponsive) even if you give it a real good amount of memory to run, secondly it is deployed using basic networking. Anyways, here's a pictorial explanation of what I did using just one server (running ubuntu precise) and it is running really nicely with advanced networking configured. For most of us this explanation should be enough and for the rest I will post step-by-step instructions soon.
Just one note, I used Google DNS for external DNS and hosted a local pdns-recursor server for the internal DNS for the cloud.- Comments: 1
Quick Script to create Universal Binaries - 31 Aug 2012 07:35
One of my friends asked me to help with creating a single binary which can run on both x86 and x86_64 linux platforms. I wrote this quick and small script to just do that given you have compiled binaries for both the platforms. It's pretty much self explanatory. And this can be easily extended (in the correct way) to support more architectures and handle things a little more generic way. Here's the 20 minutes of work:
#!/bin/bash if [ -z "$1" -o -z "$2" -o -z "$3" ]; then echo "Usage: $0 <wrapper_file_name> <x86_bin_file> <x86_64_bin_file>" exit 1 fi X86_BIN=$2 X86_64_BIN=$3 WRAPPER_FILE=$1 if [ ! -f $X86_BIN ];then echo "X86 binary file \"$X86_BIN\" not found!" exit 1 fi if [ ! -f $X86_64_BIN ];then echo "X86_64 binary file \"$X86_64_BIN\" not found!" exit 1 fi if [ -f $WRAPPER_FILE ];then ANS=n echo -n "Wrapper file \"$WRAPPER_FILE\" exists, do you want to overwrite it (y/N)?" read ANS if [ "x$ANS" = "xn" ];then exit 1 fi fi X86_BIN_SIZE=`wc -c $X86_BIN | cut -d" " -f1` X86_64_BIN_SIZE=`wc -c $X86_64_BIN | cut -d" " -f1` SCRIPT="#!/bin/bash\n X86_SIZE=$X86_BIN_SIZE\n X86_64_SIZE=$X86_64_BIN_SIZE\n TOTAL_SIZE=\$((\$X86_SIZE+\$X86_64_SIZE))\n ARCH=\$(uname -m)\n EXEC_FILE=\$(mktemp)\n if (echo \"i386 i486 i586 i686\" | grep -q \$ARCH);then\n cat \$0 | tail -c\$TOTAL_SIZE | head -c-"\$X86_64_SIZE" > \$EXEC_FILE\n elif (echo \"x86_64\" | grep -q \$ARCH);then\n cat \$0 | tail -c\$X86_64_SIZE > \$EXEC_FILE\n else\n echo \"Unhandled machine architecture \$ARCH found, exiting...\"\n exit 1\n fi\n chmod +x \$EXEC_FILE\n \$EXEC_FILE \$@\n EXIT_CODE=\$? rm -f \$EXEC_FILE\n exit \$EXIT_CODE \n " echo -e "$SCRIPT" > $WRAPPER_FILE cat $X86_BIN >> $WRAPPER_FILE cat $X86_64_BIN >> $WRAPPER_FILE echo "DONE!"
Hello World! - 27 Aug 2012 20:37
Hello, this is my yet another attempt to have blog which I would keep updating at regular intervals. Let's see how it goes. - Comments: 0