Internet Strategy Guide

Recently I've started working locally due to the PHPStorm IDE. In order to work locally, I invested in MAMP Pro to make use of their GUI interface for configuring vhosts. I also like to use MySQL Workbench for my database work, unfortunately these 2 items never seemed to work together for me before. After a brief google search I was able to find out how to connect to MAMP's MySQL install to do SQL Development, which is by done by choosing the local socket connection method and using the value /Applications/MAMP/tmp/mysql/mysql.sock for the connection.

After setting that up, I got to thinking, "I'm already using an IDE for code convenience and a GUI for vhost convenience, I should set up Server Administration with MySQL Workbench too!" So after some googling, I found an article to help me set up Server Administration with MAMP. Unfortunately the article only got things partially working and has no way to comment on it to have it corrected. So I'm going to outline the steps. Also I just noticed that the images with the article are updated correctly but it's hard to cut and paste an image of something someone has done so I'll just give you the value pairs

• Configuration File: /Applications/MAMP/tmp/mysql/my.cnf
• Start: /Applications/MAMP/bin/startMysql.sh -
• Stop: /Applications/MAMP/bin/stopMysql.sh -
• Check MySQL Status: ps -xa | grep "/Applications/MAMP/Library/bin/[m]ysqld"

@lig will be talking about mysql 5.5 and scalability this session She is Senior Technical Support Engineer for MySQL.

We will be covering

• semi-synchronous replication
• performance schema
• SIGNAL/RESIGNAL
• more partitioning options
• InnoDB - LOTS of InnoDB (performance and scalability improvements)

In 5.5 InnoDB will be the default!!! WOOT.

Default replication is asynchronous. Meaning master writes to binary log and the slave connects and "pulls" contents of the binary log. Bad thing is if the master crashes, there's no guarantee that a slave has all committed transanction.

Simi-Synchronous Replication is an alternative to asynchronous replication. Midway point between asynchronous and fully syncronous. Master only waits for a slave to receive an event. Don't have to wait for slaves to actually commit.

Performance schema tracks at an extremely low level. Just like Information schema, tables are views or temporary tables. Activation doesn't cause any change in server behavior. This is designed for advanced users.

Think of SIGNAL as an exception, a way to "return" an error. You get exception-handling logic for stored procedures, stored functions, triggers,events and db apps.

RESIGNAL lets you pass error information up. Think of it as a catch. Requres an active handler to execute. Lets you program on your PHP side to catch that very specific handling.

(continue reading...)

I recently ran into a situation where I needed to add a foreign key to a table. This seems easy at first but was actually a 3 step process compared to the couple of lines it takes in a create table sytax.

Short version: when creating your FK column, it helps to have the column definition of the FK match the column definition. In retrospec, this seems like a no-brainer but was the heart of my problem.

So, you want create a FK to an existing table. Lets start by creating our table.

CREATE TABLE IF NOT EXISTS Role(
Id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
Name VARCHAR(255) NOT NULL UNIQUE,
Description TEXT DEFAULT NULL,
PRIMARY KEY(Id)
);

Pretend we pooped data into it and now it's a big pain in the ass to do a drop/create with how we want things.

First we add our column that will be the Foreign Key.

ALTER TABLE Role ADD COLUMN Parent int(11) UNSIGNED DEFAULT NULL;

Next we add an index. Why? because mysql says so and FKs rely on indexes

ALTER TABLE Role ADD INDEX Parent (Parent);

Now we add our constraint.

ALTER TABLE `Role` ADD CONSTRAINT `Parent` FOREIGN KEY(`Parent`) REFERENCES `Role`(`Id`) ON DELETE SET NULL ON UPDATE SET NULL;

For fun, do

ALTER TABLE Role ADD COLUMN Parent int(11) DEFAULT NULL;

for the first step. Everything will look awesome until you get to the the last step. You'll get a wonderful "Errno 150" error and spend a bunch of time googling various key words trying to figure out what went wrong. Well, at least I did.

In most cases, when doing an insert statement I use SET to increase readability. It is also nice because I can make dynamic statements with insert/update. It is because of these two reasons I never saw any value (for lack of a better term that isn't punny) to using VALUES. That is until I ran into a situation where I wanted to have a database level solution as opposed to making an application layer solution. The situation is as follows:
- create an entry in the parent table
- create entries in the child table that depends on the last insert id of the parent table.

Now that I think about it, there may have been a way to do it procedurally in SQL but the first solution I was stuck on how to make my next INSERT statement fulfill my criteria and be readable. This is where VALUES comes in. By using VALUES instead of SET, you can do a multi-row insert statement.

example:

INSERT INTO Resource SET ModuleID=LAST_INSERT_ID(), Name='contact', Description='Contact resource in the default module';
/* multi-row insert */
INSERT INTO Privilege (ResourceID,Name,Description) VALUES
	(LAST_INSERT_ID(),'*','All privileges for this resource'),
	(LAST_INSERT_ID(),'browse','Browse only privilege for this resource'),
	(LAST_INSERT_ID(),'read','Read only privilege for this resource'),
	(LAST_INSERT_ID(),'edit','Edit only privilege for this resource'),
	(LAST_INSERT_ID(),'add','Add only privilege for this resource'),
	(LAST_INSERT_ID(),'delete','Delete only privilege for this resource');

So now, VALUES isn't completely useless after all.

How to find out if a specific column exists:

SHOW COLUMNS FROM TableName WHERE Field LIKE ('ColName')