Serverless, AWS Lambda, PYODBC, UNIXODBC, Docker, Oracle InstantClient, Python

When is a server not a server? I don't really know, but I wanted to automate rote DBA tasks using python and lambda without a server. If you want to do this, first you must create a server. Oh well...

Create your own EC2 of the same type as the AWS Lambda public images - free tier is just fine. It needs to look like lambda serverless servers https://docs.aws.amazon.com/lambda/latest/dg/current-supported-versions.html

Servers (even serverless servers that are servers when they're not) use drivers. Download the oracle instant client libraries for linux x86-64 for basic and ODBC and put them in our home dir

https://www.oracle.com/technetwork/database/database-technologies/instant-client/downloads/index.html

wget ftp://ftp.unixodbc.org/pub/unixODBC/unixODBC-2.3.5.tar.gz

You need docker and the docker daemon:

sudo yum install docker.x86_64
sudo usermod -a -G docker ec2-user   (you might not be ec2 user)

Start the daemon

sudo yum install git
git clone https://github.com/tianon/cgroupfs-mount
sudo mv cgroupfs-mount/cgroupfs-mount /usr/local/bin/
sudo /usr/local/bin/cgroupfs-mount
sudo dockerd &

We now need/want to use Serverless. I guess at this point you might think is Lambda already serverless why do I need serverless? Because it makes the process a bit easier is the short answer as you will see further down.

curl -o- https://raw.githubusercontent.com/creationix/nvm/v0.32.0/install.sh | bash
. ~/.nvm/nvm.sh

nvm -ls-remote

Check out what versions are there, I like to pick the second last LTS to be safe-ish

nvm install v8.12.0

Now we're kindof going to do stuff in here a little bit (https://read.iopipe.com/the-right-way-to-do-serverless-in-python-e99535574454 ) What a great article - Read it or this wont make sense.

npm install -g serverless
mkdir ~/my-serverless-driver-project
cd ~/my-serverless-driver-project
sls create -n my-serverless-driver-project -t aws-python
sls deploy

You'll get the following as it's a new instance

"Serverless Error ---------------------------------------
  ServerlessError: AWS provider credentials not found. Learn how to set up AWS provider credentials in our docs here: <http://bit.ly/aws-creds-setup>.
"

Do an AWS configure to set yourself up https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-getting-started.html

sls invoke -f hello

OK Basics are done, good on you. You can also see why serverless is sortof handy for setting some stuff on for you already if you go into the console - you can see it uploads files etc without any hassals.

vim httprequest.py
"
import os
import pyodbc
def handler(event, context):
    connection = pyodbc.connect('DRIVER={Oracle12};DBQ=some.server.com.au:1521/mydb;Uid=jared;Pwd=mypass')
    st='OK'
    #st=os.popen('ldd -v libsqora.so.18.1').read()
    #st=os.popen('/var/task/bin/dltest /var/task/libsqora.so.18.1').read()
    for root, dirs, files in os.walk("."):
        for filename in files:
            st = st+ filename + '\n'
    return {"content": st}
"

Note above I have left in troubleshooting code if there are library issues. This may be very important.

update the functions section of your serverless.yml

"
functions:
  hello:
    handler: handler.hello
  httprequest:
    handler: httprequest.handler"

Install more stuff...!

These are required to build python libs

sudo yum install gcc
sudo yum install gcc-c++

Let's get some drivers shall we? This was the hardest part to get right

tar xvzf unixODBC-2.3.5.tar.gz
cd unixODBC-2.3.5
./configure  --sysconfdir=/var/task  --disable-gui --disable-drivers --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE  --prefix=/home
sudo make install
cp -R /home/bin/ /home/ec2-user/my-serverless-driver-project/
#cp -R /home/include/ /home/ec2-user/my-serverless-driver-project/
cp  /home/lib/* /home/ec2-user/my-serverless-driver-project/
cd /home/ec2-user/my-serverless-driver-project/
(yes this is silly but I'm using it just to pip install python)

We can create pyodbc if we get some odbc dev drivers - these odbc drivers arent required for the lambda

sudo yum install unixODBC-devel
sudo pip install pyodbc
cp /usr/local/lib64/python2.7/site-packages/pyodbc.so /home/ec2-user/my-serverless-driver-project/

Let's get some more drivers... Oracle this time. Dear readers are there smaller drivers I can use???

cd ~
unzip instantclient-basic-linux.x64-18.3.0.0.0dbru.zip
unzip instantclient-odbc-linux.x64-18.3.0.0.0dbru.zip

cp instantclient_18_3/* /home/ec2-user/my-serverless-driver-project/

Delete a bunch to keep the lambda under the file limit

rm /home/ec2-user/my-serverless-driver-project/liboramysql18.so
rm /home/ec2-user/my-serverless-driver-project/libclntsh.so
rm /home/ec2-user/my-serverless-driver-project/ucp.jar
rm /home/ec2-user/my-serverless-driver-project/libocci.so

A system driver needs to be copied accross

cp /lib64/libaio.so.1 /home/ec2-user/my-serverless-driver-project/

UnixODBC needs to know where your driver is

vim /home/ec2-user/my-serverless-driver-project/odbcinst.ini
[Oracle12]
Description     = Oracle ODBC driver for Oracle 12c
Driver          = /var/task/libsqora.so.18.1
Setup           =
FileUsage       =
CPTimeout       =
CPReuse         =

sls deploy
sls invoke -f httprequest

(Or if you're feeling lazy sls deploy && sls invoke -f httprequest )

As you could imagine I didn't nail this the first time around, so take a look at the hashed out commands to see if the libraries are there inside lambda. I am really shaking my head a bit about the implementation of UnixODBC and the sheer size of the Oracle drivers that make it painful in the lambda world. I also highly recommend using serverless-python-requirements but it won't work for this example due to DB drivers. Perhaps there's a way to get that working with docker.

Autohotkey image search and click not working

Those of your out there may have had issues with the ImageSearch command in Autohotkey not lining up with MouseMove coordinates FoundX and FoundY

This is an issue with Autohotkey but after some persistence (of choosing a plethora of different coordinate modes and other settings) it seems it's related to different resolution settings.

The workaround is to change the screen resolution to a lower setting and change it back again once completed. This aligns the image coordinates to the mousemove coordinates in Autohotkey.

#z::

ChangeResolution(1024,768)

settitlematchmode 2

Sleep, 2000

Run https://somesite.com

Sleep, 4000

CoordMode Pixel, Relative

ImageSearch, FoundX, FoundY, 0, 0, 1000, 1000, *50 C:\Users\uid\Documents\testfiles\c.bmp

if ErrorLevel = 2

    MsgBox Could not conduct the search.

else if ErrorLevel = 1

    MsgBox Icon could not be found on the screen.

else

MouseMove, %FoundX%, %FoundY% 

Click

MsgBox   %FoundX%, %FoundY% 

ChangeResolution(2160,1440)

Return

ChangeResolution(w,h) {

  VarSetCapacity(dM,156,0)

  NumPut(156,dM,36)

  NumPut(0x5c0000,dM,40)

  NumPut(w,dM,108)

  NumPut(h,dM,112)

  DllCall( "ChangeDisplaySettingsA", UInt,&dM, UInt,0 )

}

skyteam ace 125 removing cat

Before I start I must say that this is not a fun job. If you can buy a replacement header for a good price - do it now!

As shipping to Australia is too expensive, here's how to do it.

Tools required. You will also need an angle grinder, metal cutting disc, power drill with a very long drill bit and a bench vice.

Remove muffler bolt. 14mm wrench.

Remove the header bolts. 10mm.

Cut here or further up to be closer to the cat - not that it will help you remove it. If you want to use the existing exhaust and weld it later I would recommend doing it the slow way with a hacksaw to get a perfect cut. Metal looks thick enough to be mig welded with gasless or even arc welded with an inverter only.

Here's what's to be removed. It will take a great deal of time as it is densely packed metal that is glued in. Drill towards the sides to remove the glue. Drill in the middle to make it less dense.

You have removed the terrible, terrible cat and deserve a beer. Install your new exhaust or weld the existing exhaust back. If you plan to slip on a new exhaust ensure you grind down all the welds on the outer part of the pipe as a slip on must have a very flat surface.

You will most likely need to adjust the idle and mix.

Adjust the idle as pictured and the fuel mix screw which is screw with a grip underneath the carb outlet.

This will take some time and some care. Only make small adjustments (1 hour out of 12). Tighten to increase, loosen to decrease. Wait a minute after each adjustment. If you are using the stock muffler and cat it may no longer like slow idles.

It is best to start with the idle screw at the bottom of the carb all the way in (lean). And to keep your idle at a moderate pace. Slowly increase the fuel mix screw as you slowly decrease the idle until it happily idles just above stalling. This is the minimum for it to run, but at this point I prefer to increase the fuel mix so it runs richer on idle to stay warm and prevent stalling at lights. I also prefer to increase the idle for the same reason.

Enjoy the new performance.

skyteam ace 125 carb rejet

Installing the two jets for the carb on the skyteam ace apparently improves warm up and performance. I believe it also makes the mix richer but that's yet to be confirmed.

I only recommend doing this if you are replacing the stock muffler and removing the cat else it may run too rich when idling.

Using the OORacing jets http://www.ooracing.com/store/index.php?act=viewProd&productId=4315 these are the steps to rejet the carb. The job is easily done if the exhaust gas recycling gear has been removed.

Tools required. You will want a screwdriver with multiple heads as the carb uses finer heads and the fairings large heads. It is worth noting that you are dealing with brass screws that are fragile and do not like over-tightening or poor treatment.

Turn your fuel tap to off. Put the carb drain hose into a bucket and unscrew the drain bolt and the fuel will empty. Not much fuel.

Removing the right fairing. Once the bolt is removed, pull hard to get the fairing out of the rubbers.

Unscrew the hose clamp and remove from carb. Apply force but be careful not to bend the hose clamp which is fairly flimsy.

10mm wrench to remove the carb bolts. When re-installing make sure these are very tight.

With a lot of wrestling the carb will come out if you twist and pull it down. The air intake hose will fight you all the way but take it slow and it will come out. Remove the 3 bolts at the bottom of the carb.

The two jets are pictured below (Holding the pilot jet). Remove both. Re-install with care and do not over-tighten. Be very careful not to bend the float as these parts are highly sensitive.

Take the screw from the main jet. Probably best to do this in a vice with wood instead of the pliers as you can (as I did) scuff the housing. Install the new screw.

Do the same with the pilot jet.

Adjust the idle as pictured and the fuel mix screw which is screw with a grip underneath the carb outlet.

This will take some time and some care. Only make small adjustments (1 hour out of 12). Tighten to increase, loosen to decrease. Wait a minute after each adjustment. If you are using the stock muffler and cat it may no longer like slow idles.

It is best to start with the idle screw at the bottom of the carb all the way in (lean). And to keep your idle at a moderate pace. Slowly increase the fuel mix screw as you slowly decrease the idle until it happily idles just above stalling. This is the minimum for it to run, but at this point I prefer to increase the fuel mix so it runs richer on idle to stay warm and prevent stalling at lights. I also prefer to increase the idle for the same reason.

Skyteam ace 125 emission control

Removing the restrictive emission control from the skyteam ace should improve performance and tidy up the look of the engine. Let's not worry about the environment at 125cc.

Tools required plus the OORacing 'EGR Cylinder head exhaust emission blanking plate'

Remove the tang that holds the pipe to the air intake by hand. Remove pipe.

Use small pliers to remove this copper adapter.

0.22 inch threaded screw or bolt will fit nicely into it's place. May be imperial?

Screw in with small pliers.

Remove emission outlet pipe with a kex key.

8mm ring spanner to remove the mixer part of the emission control device. The metal tangs that hold it in place are flimsy and may bend if you apply too much pressure. Once unbolted, you can pull the entire device out - the outlet pipe on the other side of the bike will simply slip out with its sleeve.

Keep it for reattaching for roadworthy for resale.

8mm ring spanner for the OOracing 'EGR Cylinder head exhaust emission blanking plate'. Do not over-tighten.

Much neater. Perhaps faster?

Skyteam ace 125 bar end mirrors

Original mirrors were a bit large and clunky. They also offer poor visibility.

Tools required.

Drill out bar end plastic and rubber.

Push in metal bar end stoppers. These should move fairly easily but may need to be hammered in.

Bar end mirrors can point up or down. No right way, just what looks good.

On the throttle side there must be at least a 2mm gap. This will ensure the throttle rubber does not get caught when twisted.

Tighten and adjust - you're ready to roll.

Unzip a bunch of DOCX word docs in windows cmd to make it easy to grep

This may seem like a strange thing to do, but you might want to use grep on a bunch of windows docx files to search for content easily... instead of using the windows file indexer..

@echo off
cd "C:\someFolderFullOfDocs"
for /r . %%G IN (*.docx) DO (
 echo %%G
 echo %%~nxGdir
 mkdir %%~nxGdir
 cd %%~nxGdir
 "C:\Program Files\java\jdk1.5.0_22\bin\jar" xf "%%G"
 cd "C:\someFolderFullOfDocs"
 )