Hanging out in the #mysql channel on Freenode the other night, some members were talking about database normalization. I wasn’t sure what that term meant, so I followed the links that were pasted to find out more.  What I discovered was that these ‘normalization rules’ are very practical guidelines for schema creation that I’ve been using for years without knowing what they were called!

In the following article I start looking at the actual rules, and why they can be helpful!

Read the rest of this entry »

I’m not sure what caused this error in the first place, as it cropped on a server I don’t admin..

ERROR 1146 (42S02): Table ‘mysql.servers’ doesn’t exist

This cropped up on Server version: 5.1.36 MySQL Community Server (GPL) by Remi, installed on CentOS whenever I would execute the ‘FLUSH PRIVILEGES’ command.

The solution?  Try this.

USE mysql;
 
CREATE TABLE `servers` (
`Server_name` char(64) NOT NULL,
`Host` char(64) NOT NULL,
`Db` char(64) NOT NULL,
`Username` char(64) NOT NULL,
`Password` char(64) NOT NULL,
`Port` int(4) DEFAULT NULL,
`Socket` char(64) DEFAULT NULL,
`Wrapper` char(64) NOT NULL,
`Owner` char(64) NOT NULL,
PRIMARY KEY (`Server_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='MySQL Foreign Servers table';

Apparently this issue arises sometimes with an incomplete MySQL upgrade.  Beyond just installing new binaries you’ll often need to run scripts/mysql_fix_privilege_tables to ensure the ‘mysql’ database contains all the needed stuff.

This week as I was migrating my Wordpress installs to a new host, I noticed that all of my posts contained funny ‘Â’ characters where newlines should be.   Why was this happening?  How did I fix it?

Read the rest of this entry »

A few days ago I made a post explaining the basics of foreign keys in MySQL. Today I’d like to flesh out the subject by going a bit deeper into their usage.

Read the rest of this entry »

Foreign keys in MySQL can be a little scary if you’ve never used them before.  They do provide some great benefits however and should be a tool in any DBA’s arsenal.  In this article I go through the basics of Foreign Keys in MySQL with examples!

Read the rest of this entry »