Working with Bitfields in MySQL

Problem: You are working with an antique data model where someone thought is was a good idea to store true/false data as bits in an integer value.

Bitfields are a kind of composit column which stores many logical values in one physical column as an integer value. It allows you to store many yes/no options in a very small space. When looking at this data you'll see integers but what is really important is the bits that make up that number.

Lets say I want to store event level info in a log table.

CREATE TABLE eventlog(
level INT(11) NOT NULL,
msg varchar(32) NOT NULL
);

bit 1 (0001) = fatal error (int 1)
bit 2 (0010) = warning (int 2)
bit 3 (0100) = error (int  4)
and bit 4 (1000) = user error (int 8)

What if I have both a user error and a warning error? That's int 6.
Or all options? That's int 15.
Or all the level's with a warning logged? That's int 2,3,7 and 15

Querying or filtering on a single bit is hard. It's as difficult to work with as CSV string columns. If this data had been normalized properly, each part would have its own column in the database.

MySQL has a few built in functions for working with bitfield columns:
Check out EXPORT_SET and MAKE_SET

SELECT
msg,
EXPORT_SET(level,1,0,'',4) AS debugBits,
SUBSTRING(EXPORT_SET(level,1,0,'',4),1,1) AS hasFatal,
SUBSTRING(EXPORT_SET(level,1,0,'',4),2,1) AS hasWarning,
SUBSTRING(EXPORT_SET(level,1,0,'',4),3,1) AS hasError,
SUBSTRING(EXPORT_SET(level,1,0,'',4),4,1) AS hasUserError
FROM eventlog;

which results in:

msg debugBits hasFatal hasWarning hasError hasUserError
test1 0000 0 0 0 0
test2 1010 1 0 1 0
test3 0010 0 0 1 0

We're taking the bitfield column, turning the column into strings and pulling the string apart. Notice here that bit 1 is on the far right.
 

What happens if I want to add context to this data. That's what MAKE_SET is for.

SELECT
msg,
MAKE_SET(level,
'fatal',
'warning',
'error',
'userError') AS levelTextCsv
FROM warnings;

which will output your bitfield value as a single CSV column with text for each selected option.

Bitfields aren't ideal. Combining data into a single column is an anti-pattern in SQL. Should you encounter this in the wild, you'll know what to do.

 

Rename a MySQL database

 

Problem: You need to change the name of a mysql database without reloading it.

MySQL used to contain a "RENAME DATABASE" command but it would occasionally hose the database.

RENAME {DATABASE | SCHEMA} db_name TO new_db_name;

This statement was added in MySQL 5.1.7 but was found to be dangerous and was removed in MySQL 5.1.23. … Use of this statement could result in loss of database contents, which is why it was removed. Do not use RENAME DATABASE in earlier versions in which it is present.

So what should you do instead?

If all your tables are physically located on the same disk, you can use "create db" + "rename table" to move tables between databases without reloading them.

-- ensure the char set and collate match the existing database.
SHOW VARIABLES LIKE 'character_set_database';
SHOW VARIABLES LIKE 'collation_database';


CREATE DATABASE `database2` DEFAULT CHARACTER SET = `utf8` DEFAULT COLLATE = `utf8_general_ci`;
RENAME TABLE `database1`.`table1` TO `database2`.`table1`;
RENAME TABLE `database1`.`table2` TO `database2`.`table2`;
RENAME TABLE `database1`.`table3` TO `database2`.`table3`;

Reference: http://dev.mysql.com/doc/refman/5.7/en/rename-table.html
This answer is also posted on stackoverflow.

Make MySQL 5.7+ behave like MySQL 5.6

You've upgraded your environment to MySQL 5.7 and everything is broken.

MySQL 5.7 includes a number of changes to the sql mode which are talked about here: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sql-mode-changes

The old defaults can be set via the sql_mode property on a per-query basis or server wide via the my.cnf file.

To apply the old defaults:

  • Open your my.cnf (usually found at /etc/mysql/my.cnf )
  • Add a line under [mysqld] section. sql_mode=NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION
  • Close your file and restart mysql.

 

Thanks to Ian and Logan from Austin PHP for this solution.

O to Dev in 10 seconds with Ubuntu

The end result is to take a based install of ubuntu 10.x desktop and make it ready for php web development.

These commands download about 500MB of software.

Run it line by line as root or make a bash script out of it.


#!/bin/bash

#All of these steps must be done as root.
if [ "$(whoami)" != 'root' ]; then
echo "This script must be run as root."
exit 1;
fi

#Add the zend repo to apt.
echo "deb http://repos.zend.com/zend-server/deb server non-free" >> /etc/apt/sources.list

#Add zend's signing key to the apt key ring so we can use the zend repo.
wget http://repos.zend.com/deb/zend.key -O- | sudo apt-key add -

#Add yogarine's repo so we can download the latest version of eclipse and the php development tools.
add-apt-repository ppa:yogarine/eclipse/ubuntu

# Update your repo cached software list.
apt-get update

#Install eclipse with php development tools (latest), zend server (apache, php, and php control panel), mysql (cli client and server) in a single command.
apt-get install eclipse-pdt zend-server-ce-php-5.3 php-5.3-extra-extensions-zend-server-ce mysql-server mysql-client phpmyadmin

Post install Tasks:

Reboot your computer.
Eclipse will install openJDK and you must restart to complete the installation.

Visit http://127.0.0.1:10081 to complete the setup of your zend server control panel.

Setup your document root.
I usually make a directory in var for my workspace and point eclipse to that location like so.

cp /var/www/*.php ~/workspace/
sudo rm -f -r /var/www
#Replace $USER with your login name.
sudo ln -s -v ~/home/$USER/workspace/ /var/www

To access the debugger from PDT, add the following get variables to your request:


http://localhost/test/info.php?debug_host=127.0.0.1%2C127.0.0.1&start_debug=1&debug_port=10000&original_url=http%3A%2F%2Flocalhost%2Ftest%2Finfo.php&send_sess_end=1&debug_stop=1&debug_start_session=1&debug_no_cache=1310991085348&debug_session_id=1000

The windows BEEP

Working with mysql from the windows command line always unleashes at some point the loudest sound that can be generated by a sound card.

Well, here is how to turn off that feature.
Taken from:
http://www.slashdotdash.net/2006/08/17/kill-the-annoying-windows-beep-internal-speaker/
Thank you Ben. You’ve saved my ears.

Perform the following steps to disable your machine’s system beep:

1. Right-click My Computer and select Manage.

2. Expand System Tools and select Device Manager.

3. From the View menu, select Show hidden devices.

4. Expand Non-Plug and Play Drivers.

5. Right-click Beep, and select Properties.

6. Select the Drivers tab.

7. Click Stop. You can also change the start-up type to Disabled so the beep service never starts.