MySQL IN Operator Not Using Index

After being alerted for elevated cpu on a staging mysql instance, I logged into the mysql console and ran a SHOW PROCESS LIST:

SHOW PROCESSLIST\G
*************************** 1. row ***************************
     Id: 1
   User: my_user
   Host: x.x.x.x:xxx
     db: my_db
Command: Sleep
   Time: 2
  State:
   Info: NULL
*************************** 2. row ***************************
     Id: 3
   User: my_user
   Host: x.x.x.x:xxx
     db: my_db
Command: Query
   Time: 786
  State: Sending data
   Info: SELECT file_id, fs_file_id
FROM my_data
WHERE file_id IN ('123456', 123457);

A query running for 786 seconds quickly caught my eye, my next course of action was to run an EXPLAIN:

EXPLAIN SELECT file_id, fs_file_id FROM my_data WHERE file_id IN ('123456', 123457)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_data
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 131144420
        Extra: Using where
1 row in set (0.00 sec)

The EXPLAIN confirmed my suspicions, this query was not using an index, and ultimately caused a full table scan.

I was expecting to not find an index for the file_id field, but as you can see this field has an index by being a PRIMARY KEY:

SHOW CREATE TABLE my_data\G
*************************** 1. row ***************************
Table: my_data
Create Table: CREATE TABLE `my_data` (
`file_id` int(10) unsigned NOT NULL,
`fs_file_id` binary(16) DEFAULT NULL,
PRIMARY KEY (`file_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

Something I didn’t immediately identify earlier was the mixed types in my IN operator‘s list, they contained both integers and strings:

file_id IN ('123456', 123457)

Once I noticed the mixed type I began debugging, I finally learned the IN operator works great with sets of integers:

EXPLAIN SELECT file_id, fs_file_id FROM my_data WHERE file_id IN (123456, 123457)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_data
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.00 sec)

It even works with sets of strings:

EXPLAIN SELECT file_id, fs_file_id FROM my_data WHERE file_id IN ('123456', '123457')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: my_data
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 2
        Extra: Using where
1 row in set (0.00 sec)

But as we observed from the begining, the IN operator doesn’t act as expected with mixed strings and integers.

Remote Controlled Car using Raspberry Pi and Webcam

Demo

Setup

First thing I tackled was setting up the L293D H-Bridge on the Bread Board.

I found myself referencing the following Diagram a couple times.

Step one is connecting your chip down the center of your board:

From here I connected the 3 power pins to my board’s power rail using a few Jumpers:

A few more Jumpers connect each side of the chip to ground:

Finally I use a couple Wires to connect both sides of my power and ground rails:

Using a little bit of double sided tape I stick my board onto the Car Chassis:

I also wired up each DC motor, and the battery pack to the board:

Next I wired up my Raspberry Pi‘s GPIO pins, connecting them to the L293D.

Once I’ve verified the GPIOs were connected properly, I used a couple rubber bands to strap the Pi, Portable USB Charger (I used a Vans Shoe Charger) and Web Cam to the cassis:

Code

On the Raspberry Pi I’m using Raspbian as the operating system, and installed a couple pieces of software:

* nginx
* Flask
mjpg-streamer
* Rpi.GPIO

My nginx configuration is really basic and looks like this:

server {
 listen 80 default_server;
 listen [::]:80 default_server;

 root /var/www/html;

 location /stream {
   proxy_pass http://localhost:8080/?action=stream;
   proxy_set_header Content-Type "image/jpeg";
 }

 location / {
   proxy_pass http://localhost:8000;
 }

}

The Flask application is a bit janky, but gets the job done:

app.py

import RPi.GPIO as GPIO
from flask import Flask, render_template
from flask import request

GPIO.setmode(GPIO.BCM)

GPIO.setup(14, GPIO.OUT)
GPIO.setup(15, GPIO.OUT)

GPIO.setup(23, GPIO.OUT)
GPIO.setup(24, GPIO.OUT)


app = Flask(__name__)

@app.route("/")
def index():
    return render_template('index.html')

@app.route("/left")
def left():
    method = request.args.get('method')
    if method == 'stop':
        sig = GPIO.LOW
    else:
        sig = GPIO.HIGH

    GPIO.output(23, sig)
    return "OK"

@app.route("/forward")
def forward():
    method = request.args.get('method')
    if method == 'stop':
        sig = GPIO.LOW
    else:
        sig = GPIO.HIGH

    GPIO.output(15, sig)
    GPIO.output(24, sig)
    return "OK"

@app.route("/backward")
def backward():
    method = request.args.get('method')
    if method == 'stop':
        sig = GPIO.LOW
    else:
        sig = GPIO.HIGH

    GPIO.output(14, sig)
    GPIO.output(23, sig)
    return "OK"

@app.route("/right")
def right():
    method = request.args.get('method')
    if method == 'stop':
        sig = GPIO.LOW
    else:
        sig = GPIO.HIGH

    GPIO.output(14, sig)
    return "OK"

if __name__ == "__main__":
    app.run(host='0.0.0.0', port=8000, debug=True)

While the template contains a bit of Javascript to handle button presses:

templates/index.html

<html>

<head>

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous">

<style>
  img {
      margin-top: 25px;
      margin-bottom: 25px;
    }
</style>

<script
  src="https://code.jquery.com/jquery-3.2.1.min.js"
  integrity="sha256-hwg4gsxgFZhOsEEamdOYGBf13FyQuiTwlAQgxVSNgt4="
  crossorigin="anonymous"></script>

<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js" integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa" crossorigin="anonymous"></script>


<script>


// define our fired states to false
var forward_fired = false;
var backward_fired = false;
var left_fired = false;
var right_fired = false;

// keydown event will start motor
document.onkeydown = function() {

    if(event.keyCode == 87) {

        if(!forward_fired && !backward_fired) {
            forward_fired = true;

            button = document.getElementById('up');
            button.className = 'btn btn-success btn-lg disabled';

            console.log('start forward');
            $.get("/forward")
        }
    }

    if(event.keyCode == 83) {
        if(!backward_fired && !forward_fired) {
            backward_fired = true;

            button = document.getElementById('down');
            button.className = 'btn btn-success btn-lg disabled';

            console.log('start backward');
            $.get("/backward")
        }
    }

    if(event.keyCode == 65) {
        if(!left_fired && !right_fired && !backward_fired) {
            left_fired = true;

            button = document.getElementById('left');
            button.className = 'btn btn-success btn-lg disabled';

            console.log('start left');
            $.get("/left")
        }
    }

    if(event.keyCode == 68) {
        if(!right_fired && !left_fired && !backward_fired) {
            right_fired = true;

            button = document.getElementById('right');
            button.className = 'btn btn-success btn-lg disabled';

            console.log('start right');
            $.get("/right")
        }
    }

};

// keyup event will stop motor
document.onkeyup = function() {


    if(event.keyCode == 32) {
        console.log('beep');
        $.get("/beep")
    }


    if(event.keyCode == 87) {
      if(forward_fired) {
        forward_fired = false;

        button = document.getElementById('up');
        button.className = 'btn btn-default btn-lg disabled';

        console.log('stop forward');
        $.get("/forward?method=stop")
      }
    }

    if(event.keyCode == 83) {
      if(backward_fired) {
        backward_fired = false;

        button = document.getElementById('down');
        button.className = 'btn btn-default btn-lg disabled';

        console.log('stop backward');
        $.get("/backward?method=stop")
      }
    }

    if(event.keyCode == 65) {
      if(left_fired) {
        left_fired = false;

        button = document.getElementById('left');
        button.className = 'btn btn-default btn-lg disabled';

        console.log('stop left');
        $.get("/left?method=stop")
      }
    }

    if(event.keyCode == 68) {
      if(right_fired) {
        right_fired = false;

        button = document.getElementById('right');
        button.className = 'btn btn-default btn-lg disabled';

        console.log('stop right');
        $.get("/right?method=stop")
      }
    }

};

</script>

</head>

</body>


<div class="container">
  <div class="row">
    <center>

      <img src="/stream" class="img-thumbnail">

    </center>
  </div>
</div>

<!-- control buttons -->
<div class="container">
  <div class="row">

    <center>

      <button id="left" type="button" class="btn btn-default btn-lg disabled">
        <span class="glyphicon glyphicon-arrow-left" aria-hidden="true"></span>
      </button>

      <button id="up" type="button" class="btn btn-default btn-lg disabled">
        <span class="glyphicon glyphicon-arrow-up" aria-hidden="true"></span>
      </button>

      <button id="down" type="button" class="btn btn-default btn-lg disabled">
        <span class="glyphicon glyphicon-arrow-down" aria-hidden="true"></span>
      </button>

      <button id="right" type="button" class="btn btn-default btn-lg disabled">
        <span class="glyphicon glyphicon-arrow-right" aria-hidden="true"></span>
      </button>

    </center>

  </div>
</div>

</body>

</html>

I haven’t yet configured the Flask and mjpg-streamer processes to startup automatically, so I connect via a shell and start each in a screen session.

Once that is done connect to the running nginx server using your browser, you should be presented with a user interface similar to this:

Use your keyboard to control the car, just like a video game the key W moves forward, S backwards, A left, and D is right.

Component List

Here is a list of each of the components (or comparable) I used during the setup:

Raspberry Pi 3 Model B Motherboard

Official Raspberry Pi 3 Case – Red/White

uxcell White 8.5 x 5.5cm 400 Tie Points 400 Holes Solderless Breadboard

Microsoft LifeCam HD-3000 Webcam – Black (T3H-00011), 720p HD 16:9 Video Chat, Skype Certified

INSMA Motor Smart Robot Car Chassis Kit Speed Encoder Battery Box For Arduino DIY

Adafruit Dual H-Bridge Motor Driver for DC or Steppers – 600mA – L293D [ADA807]

Kuman 120pcs Breadboard Jumper Wires for Arduino Raspberry Pi 3 40pin Male to Female, 40pin Male to Male, 40pin Female to Female Ribbon Cables Kit Multicolored Pack K45

Makerfocus 140pcs Breadboard Board Jumper Cable Wire Kit w Box

Happy Will 200 PCS Breadboard Jumper Wires/Jump Wire Mix Long and Short M/M for Circuit Board

Anker Astro E1 5200mAh Candy bar-Sized Ultra Compact Portable Charger (External Battery Power Bank) with High-Speed Charging PowerIQ Technology (Black)

DIY Farmhouse Bench

Over the weekend I began work on another DIY Farmhouse Bench. For the record this is the second time i’ve used the Home Depot plans for the bench.

This time I decided to go all out and buy a Kreg Jig K4 Pocket Hole System, it made the project much easier, and much cleaner looking!

View post on imgur.com

View post on imgur.com

View post on imgur.com

View post on imgur.com

View post on imgur.com

View post on imgur.com

Python says, Simon’s hipster brother

Many of you may remember playing with a Simon Electronic Memory Game when you were younger, you know something that looks like this:

At it’s core the game is rather simple, the device lights up random colors, and you need to repeat the pattern. Of course it gets harder the longer you play.

I thought it would be fun to build a Simon game using Raspberry Pi and a few electronic components:

I used the following components to assemble the project:

  • Raspberry Pi 3
  • 3x 330 Ohm resistor
  • 3x 1k Ohm resistor
  • White LED
  • Blue LED
  • Red LED
  • Breadboard
  • Assortment of wires

Here is a close up of the bread board and components:

The Raspberry Pi’s GPIO pins are then connected to the bread board,
and a small Python script powers the Simon game:


from RPi import GPIO

from sys import exit
from random import choice
from time import sleep

# define our pins for leds
white = 14
blue = 15
red = 18

# define our pins for buttonss
white_button = 21
blue_button = 20
red_button = 16

# disable warnings
GPIO.setwarnings(False)

# set the board to use broadcom pin numbering
GPIO.setmode(GPIO.BCM)

# setup our LED pins as output
GPIO.setup(white, GPIO.OUT)
GPIO.setup(blue, GPIO.OUT)
GPIO.setup(red, GPIO.OUT)

# setup our buttons as input
GPIO.setup(white_button, GPIO.IN)
GPIO.setup(blue_button, GPIO.IN)
GPIO.setup(red_button, GPIO.IN)

# create empty pattern list for simon says game
pattern = []

# create a list of our choices for simon says game
choices = [white, blue, red]

# starting difficulty based on blink durations
duration = 0.75


def add_color():
    """
    Append a random color to our pattern list
    """

    color = choice(choices)
    pattern.append(color)


def get_button():
    """
    Gets the next button press and returns
    """

    while True:
        if GPIO.input(white_button):
            return white

        if GPIO.input(blue_button):
            return blue

        if GPIO.input(red_button):
            return red

def blink(led, duration):
    """
    Blink a led for duration
    """

    GPIO.output(led, GPIO.HIGH)
    sleep(duration)
    GPIO.output(led, GPIO.LOW)


def blink_pattern(duration):
    """
    Blinks our pattern using duration as waits
    """

    for led in pattern:
        sleep(duration)
        blink(led, duration)


def check_pattern():
    """
    Checks our button presses against pattern
    """

    for led in pattern:    
        if led != get_button():
            return False
        sleep(0.3)  # delay so button press doesn't overlap
    return True


def game_over():
    """
    Game over function
    """

    print 'Pattern Length: {}'.format(len(pattern))
    print '''
       _____          __  __ ______    ______      ________ _____  
      / ____|   /\   |  \/  |  ____|  / __ \ \    / /  ____|  __ \ 
     | |  __   /  \  | \  / | |__    | |  | \ \  / /| |__  | |__) |
     | | |_ | / /\ \ | |\/| |  __|   | |  | |\ \/ / |  __| |  _  / 
     | |__| |/ ____ \| |  | | |____  | |__| | \  /  | |____| | \ \ 
      \_____/_/    \_\_|  |_|______|  \____/   \/   |______|_|  \_\

    '''

    # blink all leds to show game over
    for _ in range(3):
        for c in choices:
            blink(c, duration=0.1)

    exit()


if __name__ == '__main__':

    # populate initial pattern
    add_color()
    add_color()

    while True:

        # blink back pattern
        blink_pattern(duration)

        # check if our inputs were correct, else end game
        if not check_pattern():
            game_over()

        # add a new color to pattern
        add_color()

        # decrease our duration to increase difficulty
        if duration > 0.05:
            duration -= 0.07


Happy Hacking!

Arduino values to Python over Serial

I’ve done a little bit of reading on the ReadAnalogVoltage of Arduino’s home page, and they give a straight forward way to read voltage from an analog pin.

I wanted to take this one step further and send the value over serial, then read it in Python using pySerial.

My setup is very straight forward, I have a Arduino UNO, a bread board, and a battery pack holding 4x AA batteries:

voltage_setup

To start out I want to merely print the voltage value in Arduino Studio to the serial console, my code looks something like this:

void setup() {
  // connect to serial
  Serial.begin(9600);
}

void loop() {

  // read value from analog pin
  int sensorValue = analogRead(A0);
 
  // convert to voltage and print to serial connection
  // https://www.arduino.cc/en/Tutorial/ReadAnalogVoltage
  float voltage = sensorValue * ( 5.0 / 1023.0 );
  Serial.println(voltage);

}

Now that we’ve verified this works, lets make a couple modification to the Arduino code.

Since the value of the analogRead may be over 255 (more than can fit in a single byte), we will need to send two bytes, a high byte, and a low byte. This concept is called most significant byte, and least significant byte.

void setup() {
  // connect to serial
  Serial.begin(9600);
}

void loop() {

  // read value from analog pin
  int sensorValue = analogRead(A0);
 
  // get the high and low byte from value
  byte high = highByte(sensorValue);
  byte low = lowByte(sensorValue);

  // write the high and low byte to serial
  Serial.write(high);
  Serial.write(low);

}

Then on the Python side we can use pySerial to read two bytes, and convert using the formula Arduino gave us.

import serial

# open our serial port at 9600 baud
dev = '/dev/cu.usbmodem1411'
with serial.Serial(dev, 9600, timeout=1) as ser:

  while True:

    # read 2 bytes from our serial connection
    raw = ser.read(size=2)

    if raw:

      # read the high and low byte
      high, low = raw

      # add up our bits from high and low byte
      # to get the final value
      val = ord(high) * 256 + ord(low)

      # print our voltage reading
      # https://www.arduino.cc/en/Tutorial/ReadAnalogVoltage
      print round(val * ( 5.0 / 1023.0), 2)

One thing to take into consideration is, if we do not have voltage sent to the analog pin the result will be random and invalid. You will see this in the video before I connect the battery pack. Keep in mind my battery pack is producing about 5 volts: