Storing MD5 values

A common occurrence I have noticed in MySQL apps is that MD5 values are stored as 32 byte values rather than 16. Just to ‘rehash’, an MD5 value is a 16 byte hexadecimal value, typically used as a unique fixed-length signature of a string, useful for identifying unique strings or one-way encryption of passwords. The binary representation takes 16 bytes (e.g. BINARY(16)), though a human readable hexadecimal version takes twice as many (e.g. CHAR(32)).

Same goes for IPv4 address, which can be stored as UNSIGNED INT (4 bytes) instead of VARCHAR(16) (16 bytes)

Tip: Storing MD5 values →

WebScaleSQL

WebScaleSQL is a collaboration among engineers from several companies that face similar challenges in running MySQL at scale, and seek greater performance from a database technology tailored for their needs.

Those “several companies” are Facebook, Google, LinkedIn, and Twitter. And the collaboration mentioned is a true collaboration:

For example, to introduce a code change, a WebScaleSQL engineer can propose a change. Then a WebScaleSQL engineer from another company will review the code and provide feedback. If both engineers agree the change makes sense and is functional, it will be pushed into the WebScaleSQL branch for everyone to use.

The engineers over at Facebook have posted a list of improvements that have already made it into WebScaleSQL.

WebScaleSQL: A collaboration to build upon the MySQL upstream →
WebScaleSQL →
WebScaleSQL (GitHub) →

Backup local MySQL Databases

Nice script by Xavez. Put it in a daily cronjob if you like, or extend it to copy the backup to another machine:

#!/bin/bash

# Path to where you want to backup mysql databases.
opath=/Users/username/Sites/Backups/mysql/

# Local mysql details. Make a username with only read access. Allow SELECT, LOCK TABLES.
mysqlhost=127.0.0.1
username=read_only_user
password=read_only_password

# Get current date and temporary directory.
date=$(date "+%Y-%m-%d-%H%M%S")
cpath=$opath/incomplete_back-${date}

	if [ -d $cpath ]
	then
		filler="just some action to prevent syntax error"
	else
		echo Creating $cpath
		mkdir -p $cpath
	fi
	
	#
	# Make backups. Adjust paths to binaries if necessary.
	#
	/usr/local/mysql/bin/mysql -s -r -u${username} -p${password} -e 'show databases' | while read db; do /usr/local/mysql/bin/mysqldump -u${username} -p${password} $db -r ${cpath}/${db}.sql; [[ $? -eq 0 ]] && /usr/bin/gzip ${cpath}/${db}.sql; done
	
	#
	# Symlink to latest backup.
	#
	mv $opath/incomplete_back-$date $opath/back-$date \
	&& rm -f $opath/current \
	&& ln -s back-$date $opath/current

node-orm2 — Node.js Object Relational Mapping

ORM Package for Node.js. Works with MySQL, PostgreSQL and SQLite.

var orm = require('orm');

orm.connect("mysql://username:password@host/database", function (err, db) {
    if (err) throw err;

    var Person = db.define('person', {
        name      : String,
        surname   : String,
        age       : Number,
        male      : Boolean,
        continent : [ 'Europe', 'America', 'Asia', 'Africa', 'Australia', 'Antartica' ], // ENUM type
        photo     : Buffer, // BLOB/BINARY
        data      : Object // JSON encoded
    }, {
        methods: {
            fullName: function () {
                return this.name + ' ' + this.surname;
            }
        },
        validations: {
            age: orm.validators.rangeNumber(18, undefined, 'under-age')
        }
    });

    Person.find({ surname: "Doe" }, function (err, people) {
        // SQL: "SELECT * FROM person WHERE surname = 'Doe'"

        console.log("People found: %d", people.length);
        console.log("First person: %s, age %d", people[0].fullName(), people[0].age);

        people[0].age = 16;
        people[0].save(function (err) {
            // err.msg = 'under-age';
        });
    });
});

Don’t let the alpha tag fool you; I’ve used (and have contributed to) node-orm2 in a project before (along with express, mysql and when — killer combination) and must say it’s really stable.

node-orm2 →

MySQL: Get next AUTO_INCREMENT value from/for table

Note to self: To get the next auto_increment value from a table run this query:

SELECT AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "databaseName"
AND TABLE_NAME = "tableName"

Don’t forget it (again).

Did this help you out? Like what you see?
Consider donating.

I don’t run ads on my blog nor do I do this for profit. A donation however would always put a smile on my face though. Thanks!

☕️ Buy me a Coffee ($3)