A Little Noise

The white board of amusing child statements:

Grandfather Clock: Chime…chime…chime
Iona: Hey, clock! Don’t ding-dong! You’re making me awake!

When I first heard of MySQL Proxy, I thought, “That’s way cool.”

Then I thought, “Neat toy, but I can’t think of anything I’d actually want it for. I can spell ‘SELECT’ without the client correcting me, and I’d just as soon use the built in logs. ”

Now I’m starting to gather ideas that might be practical:
Read the rest of this entry »

“Daddy, I saw a mouse in the kitchen!” calls Iona as she runs into the bedroom.

“Ok, I’ll set a trap for it before we leave for church,” I answer, not really taking my attention off the new programming language I’m learning. We’ve caught two rats in the last couple of days, but with two small children and a dog roaming the house, I don’t like leaving armed traps out in the open.

A few minutes elapse…

“‘Ook, datty! ‘Ook, datty!” babbles Eden, wandering into the bedroom. Her hands are full, as usual. I see she has my luggage padlock in one hand (so that’s where that went!) But what’s that in the other? I don’t recall any stuffed animals that color … boy oh crikey! It’s a rat!

“Put it down put it DOWN PUT IT DOWN” I yell. If that thing’s alive, I don’t want her bit! If it’s dead, I don’t want her flea bit! And in either case, I sure don’t want it dumped in my lap! Of course, my yelling sets her to crying, but not to dropping it, so I have to get it out of her hand, and then haul her to the kitchen to scrub with soap, and then chase the dog off the corpse so I can scoop it into the trash.

Iona explains that the mouse was in the kitchen and Amika was eating it. We don’t have any poison about, and there was no trap set, so I think our timid little Yorkie caught and killed it. Good on ya!

Iona: “I’m fixing your shoe.”
Me: “By fixing, you mean taking all the laces out?”
Iona: “Yes.”

Then she put them back.

Iona: “There. I fixed your shoe.”

Culture is a way of living. Some ways of living have lasted unchanged for thousands of years, and some are products of very recent discoveries. The 8th Annual Renewable Energy Roundup and Green Living Fair in Fredericksburg, Texas, highlighted a number of these, from the ancient art of wattle and daub construction, to the modern photovoltaic panel and low-wattage LED lightbulb.

The fair covered a full city block. We went mostly because of a passing interest in making biodiesel, which appears to be pretty easy to do and costs less than a third of current gas prices. We also visited booths about wind and solar power, collecting drinkable water from rainfall and from dehumidifiers, and building supplies made from all sorts of surprising materials

Considerable industry was driven through simple non-electric means for thousands of years - and I’d call this more attractive than a landscape draped with power poles and electrical lines any day.

We found a method of transportation that uses no oil and has eco-friendly emissions (or at least the originals did, and they’d keep the lawn trimmed and fertilized for you too).

According to these folks, recycling is all fine and dandy, until you begin subjecting criminals to nasty chemicals, at which point it becomes cruel and unusual.

This is the kind of public transportation I’d like to see in my city.

Seating might be a little cramped for some of you 6′+ folks. Guess you shouldn’t have eaten your broccoli when your mother told you to.

Texas is an ideal place to take advantage of the power of the sun. The kids got to make solar ovens out of pizza boxes, plastic wrap and tin foil (in the photo you can just see a couple next to my right hand). It takes a little longer than a microwave to make s’mores with this method, but then again you don’t get to sit around on the grass while waiting on the microwave either.

Since Fredericksburg has a strong German heritage, it was necessary to sample some German beer. That’s a Spaten Oktoberfest, for those of you keeping score at home.

If, like me, you don’t really like beer, you can just purchase a pair of beer goggles instead.

After you’ve spent all day dragging yourself around to all kinds of boring booths talking about renewable energy, it’s nice to have a little down time.

We like to order the catch of the day.

One (or two!) should always use the buddy system.

Keeping it up close and personal.

Occasionally in #mysql the question pops up, “How can I get all the values from a table that satisfy all of multiple conditions?”

Then we beat the questioner with a large stick, until they give an example of what they really mean.

SELECT * FROM quizAnswers;
+-------------+----------+
| studentName | question |
+-------------+----------+
| seekwill    | A        |
| seekwill    | B        |
| seekwill    | C        |
| roxlu       | A        |
| fury        | B        |
| fury        | B        |
+-------------+----------+

Find all the students who have answered both questions ‘A’ and ‘B’.
Read the rest of this entry »

CREATE TABLE secretData (
	secretValue int COMMENT 'If this goes over 5, WWIII will start'
);

CREATE SQL SECURITY DEFINER VIEW censoredData AS
SELECT * FROM secretData WHERE secretValue < 5 WITH CHECK OPTION;

GRANT SELECT, INSERT ON test.censoredData TO 'evilFiend'@'%';

<telephone> ring ring

<sysadmin> “Hello?”

<evilFiend> “I’d like to create an insertable view on some tables I already have rights to. I don’t know just yet what I’ll use for my select statement.”

<sysadmin> “Ok. I’ll set it up so you can do what you’d like.”

CREATE SQL SECURITY INVOKER VIEW evilFiendsView AS SELECT 1;
GRANT SELECT, INSERT, ALTER ON test.evilFiendsView TO 'evilFiend'@'%';

evilFiend connects to the server, while twiddling the end of his handlebar mustache.

ALTER VIEW evilFiendsView AS
SELECT * FROM censoredData WITH LOCAL CHECK OPTION;

INSERT INTO evilFiendsView VALUES (42);

Muhahaha!

Time for another MySQL Community Quiz:

Everybody knows that LAST_INSERT_ID() returns the value most recently created in an auto_increment column. Fewer of us know that you can pass a value to LAST_INSERT_ID(), and that value will be returned for the next call to LAST_INSERT_ID(). For example,

INSERT INTO table (someNonAutoIncrementField) VALUES (LAST_INSERT_ID(42));
SELECT LAST_INSERT_ID();

The INSERT inserts ‘42′ into the table, and the SELECT returns 42.

So, here’s the question: What if we pass an expression to LAST_INSERT_ID and also use an auto_increment field in the same statement?

CREATE TABLE test (id int auto_increment primary key, field int);
INSERT INTO test (id, field) VALUES (NULL, LAST_INSERT_ID(42));
SELECT LAST_INSERT_ID();

Show Answer ▼

mysql> INSERT INTO test (id, field) VALUES (NULL, LAST_INSERT_ID(42));
Query OK, 1 row affected (0.08 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

And for a follow up, does it matter if you swap the insertion around?

INSERT INTO test (field, id) VALUES (LAST_INSERT_ID(42), NULL);
SELECT LAST_INSERT_ID();

Show Answer ▼

mysql> INSERT INTO test (field, id) VALUES (LAST_INSERT_ID(42), NULL);
Query OK, 1 row affected (0.08 sec)

mysql> SELECT LAST_INSERT_ID();
+------------------+
| LAST_INSERT_ID() |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

The manual warns about mixing LAST_INSERT_ID() and LAST_INSERT_ID(expr). This may fall under that same caveat.

First, the setup:

CREATE TABLE `user` (
  `user` varchar(255) DEFAULT NULL,
  `host` varchar(255) DEFAULT NULL,
  `sort` int(11) DEFAULT NULL
);

INSERT INTO `user`
	(`user`, `host`, `sort`)
VALUES
	('','%',8),
	('testUser','%',7),
	('','%localhost',9),
	('testUser','%localhost',5),
	('','%localhost%',10),
	('testUser','%localhost%',6),
	('','localhost',2),
	('testUser','localhost',1),
	('','localhost%',4),
	('testUser','localhost%',3);

Now, the quiz:

SELECT * FROM user ORDER BY ___

Fill in the blank to get the following output. Difficulty: the `sort` field may not appear anywhere in the query.

+----------+-------------+------+
| user     | host        | sort |
+----------+-------------+------+
| testUser | localhost   |    1 |
|          | localhost   |    2 |
| testUser | localhost%  |    3 |
|          | localhost%  |    4 |
| testUser | %localhost  |    5 |
| testUser | %localhost% |    6 |
| testUser | %           |    7 |
|          | %           |    8 |
|          | %localhost  |    9 |
|          | %localhost% |   10 |
+----------+-------------+------+

Culled from the Certification Study Guide:

mysql> SELECT * FROM test;
+----------------+
| data           |
+----------------+
| This is a test |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT UPPER(data) FROM test;
+----------------+
| UPPER(data)    |
+----------------+
| This is a test |
+----------------+
1 row in set (0.03 sec)

How’s that work?

Show Answer ▼

 CREATE TABLE `test` (
   `data` varbinary(255) default NULL
 );