A Little Noise

Blog

  • One in the hand…

    “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!

  • The Shoemaker’s Elves

    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.”

    Shoes

  • The Fair

    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.

    Waterwheel

    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).

    Horse

    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.

    Jail

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

    Train

    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.

    Train car

    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.

    Waiting_smore

    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.

    Beer

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

    Beer goggles

    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.

    Going Down

    We like to order the catch of the day.

    Fun Slide

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

    Sliding Pair

    Keeping it up close and personal.

    Close up

  • Complete Set

    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’.
    (more…)

  • Views and Social Engineering

    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'@'%';

    Evil Fiend

    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!

  • Messing with LAST_INSERT_ID()

    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();


    You get the auto_increment field, at least in this case:

    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();


    Nope.

    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.

  • Access Control Quiz

    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 |
+----------+-------------+------+

  • Capital. Just capital.

    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?

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

    Binary strings are just a list of bytes. They aren’t characters anymore, so there is no upper or lower case. Remember that when choosing between char/varchar/text and binary/varbinary/blob.

  • Deep Cover

    Alpha called today. She had a mission for me.

    I accepted.

    They thought I was crazy.

    The insertion plan was a launch from the VE-79. The cyborg handlers strapped me in.

    It’s no wonder they thought I was crazy. This system was new, and the test pilots had reported negative results.

    Atmospheric re-entry can be particularly rough. Some agents never come back down.

    Captain was emotional. He’s always like that before one of his own goes into the field.

    At impact minus five I deployed the Mission Optional Mandated Standardized Landing Assist Protocol.

    The hard part would be getting past the guards. They don’t stand for any nonsense.

    I took the subterranean route.

    It’s exactly like the training grounds. I can do it with my eyes closed and while holding my breath.

    I spotted my objective. Queen Kama-huna-maca-nana-ooboo, intelligence had informed us, was in grave peril. Her prime minister was plotting an assasination during the annual Feast of the Salamander Moon.

    Even from my position suction-cupped to the ceiling, I could smell the explosive in the national dish, and the fuse was already burning.

    Transcript ends. Duration of the recording remains classified.

  • LineBreak URL

    A bookmarklet to show the URL of the current page, unescaped, and with the ? and & replaced with line breaks. Handy for viewing the key/value pairs in long query strings.

    LineBreak URL