Install And Use CouchDB With JSON And Map-Reduce

CouchDB is another offspring from the open-source, NoSQL, non-relational databases and is maintained under the Apache Foundation. It differs itself form the likes of MongoDB or Cassandra in that CouchDB is storing data in so called “documents” that are in JSON format, which can be hashes, lists, nested arrays and of course scalar values. This added complexity results in more powerful features, mainly to have a db that is not just a single key/value pair, but it comes at a price of speed reduction.

CouchDB can be a little bit of a pain to install, because it needs a few pre-requisites and they in turn have a few of their own quirks. This outline should help you get CouchDB with all it’s necessities installed. We’ve used MacOS, but you can substitute your OS where applicable.

The CouchDB source code and installer packages are downloadable here. As of this writing, version 0.11.2 is the latest stable version, with 1.0.1 just around the corner. You will also need Spider Monkey, Mozilla’s C implementation of JavaScript.

Installing SpiderMonkey

Once downloaded, extract the tarball and move into the sources folder:

tar -xzvf js-1.8.0-rc1.tar.gz

cd js/src

(more…)

How to find duplicate rows in a MySQL database table

I’ve been asked the question “How can I return duplicate rows only from a MySQL db table” so many times already, that I’ve decided to post it here in a short article.

It is not something intuitive or readily available (at least it seems), but the solution is short and very simple.

While this query:

SELECT DISTINCT column1
FROM table1

gives us all records without the duplicates, this one returns only the duplicate ones:

SELECT DISTINCT column1
FROM table1
GROUP BY column1
HAVING COUNT(column1) > 1

And by increasing the having count, you can retrieve records with multiple occurrences.

MySQL – How To Analyze, Repair and Optimize all Tables

Ever come across a situation, where you’d like to check all tables in a database and have them all repaired and optimized? My guess is yes.

In case you didn’t know, there is a helpful MySQL utility called mysqlcheck, available as of version 3.23.38. It does exactly what we need.

To check all tables in all databases for corruption and errors and also fix them in one go, this is your command:

mysqlcheck -u username -p password  –check –optimize –auto-repair –all-databases

mysqlcheck executes statements like CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE and chooses the best statements for any given operation and storage engine.

Note that the operations complete a lot faster if you can afford to to disable any external services, especially if your database is large.

How to install MySQL Server, PHP and Apache on a Mac

These instructions lead you thru the installation of the latest LAMP Stack on MacOS X Snow Leopard.

1 – Download the installation image from MySQL website here. Then double-click to mount and open the disk image.

2 – Install MySQL Server by double clicking the package “mysql-5.1.*****.pkg” and follow the menu, accepting the default values, unless you want to change something and know exactly what you’re doing.

3 – Install MySQL Startup Item by double-clicking the package “MySQLStartupitem.pkg” and follow the menu.

4 – Install MySQL Preference Pane by double-clicking the file “MySQL.prefPane” and follow the menu. This item will simplify the management of your SQL Server. You can now use the “System Preferences” panel to start and stop the database server.

5 – Enable the php module in your apache config file. You might know that Snow Leopard already ships with Apache 2.2 and PHP 5.3, but it needs a couple of tweaks to make it work smoothly. So, open /etc/apache2/httpd.conf and search for “php5_module”. Remove the comment (#) in front of the line, save and close the file, then restart apache (sudo apachectl restart)

(more…)

Setup MySQL Replication the easy way

1. Configure the Master

We will need to modify a file called my.cnf, which is the main configuration file for mysql. On most systems it’s located in /etc/ or /etc/mysql/ and it contains all important configuration data.

First, let’s ensure that networking is enabled and mysql listens on all, or at least the client’s IP addresses. We also have to tell mysql what file to write the logs to and from which databases to keep logging, so that the Slave can pick up the changes. And finally we need to assign a unique ID to the Master.

All this info is contained within the following lines in your my.cnf file. Please note that the position of these entries can be spread throughout the file, so you might have to search for each of them.

Restart the server. Then log into mysql and create a user with replication privileges:

The next 3 steps is to lock all tables on the database, take a backup and get the replication sequence ID. We’ll use the backup later on the Slave to establish the baseline, and tell it to start replication starting from the sequence ID.

(more…)

Nginx and memcached module

Memcache is traditionally used as a module inside server side scripts, such as PHP, ASP, ColdFusion and others. And it’s doing a terrific job, as long as it’s implemented correctly.

But if we look under the hood of the actual Memcache application, and I’m not talking about the PHP or ASP extension, but rather the executable that’s running as a daemon under linux, for example, it is a rather simple database like application running in memory. Now there are two basic actions that need to be performed to use it, one is writing info into memcache, the other is reading it. In a typical scenario, there are many reads for one write, that’s the whole point, isn’t it. But what if we can isolate the reading from the server side scripts, and let a small high speed module do that for us.

(more…)

Auto Create Thumnails The Easy Way

Ever wondered it there is an easy way to resize your images or quickly create thumbnails from your favorite pictures?
Search no more, the awesome little utility ImageMagick does it for you.

Install it using your package manager, most of them should have it in their repository.

sudo apt-get install imagemagick

Once installed, this command will create tumbnails for all JPG’s in the current directory, 200 pixel wide/high on the longest side. Eg. if your image is in landscape layout, it’ll be 200 pixel wide, in portrait format it’ll be 200 pixel high.

for file in *.jpg ; do convert -resize 200 “$file” t”$file”; done

That’s it. Simply change 200 to the size of your liking, or change the extension if your images are in gif, png or any other image format.

INSERT IF NOT EXISTS in MySql

INSERT IGNORE is the syntax that does mimic INSERT IF NOT EXISTS, as there is no direct command as stated in the title, at least not in the current release of MySql.
The statement INSERT IGNORE (and to some extent REPLACE INTO) does essentially the same thing, inserting a record if that given record does not exists.
See the following samples:

(more…)