Blog

Downgrade FreetTDS on Mac to Access Sybase Anywhere 10 using Homebrew

There’s a bug in FreeTDS 4.2 that prevents macs from connecting to older Sybase Anywhere databases.

Brew doesn’t naturally have a downgrade version but here’s a way around it

Following the directions here: https://docs.brew.sh/FAQ#can-i-edit-formulae-myself

  1. edit the ~/.zshrc shell file. (Assuming you’re using zsh as your shell)
  2. add a line

HOMEBREW_NO_INSTALL_FROM_API=1

3. Tap the homebrew core (use force because it’s unneeded for most activities)

brew tap homebrew/core -f

4. Edit the FreeTDS script

brew edit freetds

5. replace it all with the 1.3.x version based on the homebrew forumla commit history and github.
https://github.com/Homebrew/homebrew-core/blob/c20f8bf4168b2ebf3399ef6ef53f3c4b942d61bb/Formula/f/freetds.rb

6. Save the file

7. install the new script

brew install freetds

Tunnel HTTP traffic through a reverse tunnel to a network behind a firewall.

Do you ever find yourself in need to browse the web from a network behind a firewall?

I’m curious if there’s a more direct route, but here’s what I did.

SSH Setup Reverse Tunnel

Borrowed from my post about setting this up for Remote Desktop.

On a LightSail instance on AWS (or your choice of linux servers)

sudo apt update
sudo apt install openssh-server

Edit the sshd_config

sudo vim /etc/ssh/sshd_config

At the bottom. Type “i” for insert then add the following lines

#Allow SSH Tunneling
GatewayPorts=clientspecified

To save: esc then “:wq” (command, wright, quit)

you can check that’s in there by running

cat /etc/ssh/sshd_config | Gate

response:

#GatewayPorts no
GatewayPorts=clientspecified

On your windows computer behind the firewall

Follow instructions from Microsoft to install OpenSSH:
https://learn.microsoft.com/en-us/windows-server/administration/openssh/openssh_install_firstuse?tabs=gui

Add to file ~/.ssh/config
#aws server
Host 123.123.123.123
   User bitnami
   IdentityFile ~/.ssh/id_rsa
   IdentitiesOnly yes
   ServerAliveInterval 60
   ServerAliveCountMax 10

Adding ServerAliveInterval sets the number of seconds the client will wait before sending a keep connection alive

ServerAliveCountMax sets the number of times the client will try to keep connection alive.

you can also add this to the server as well.

vim etc/sshd/ssh_config

add

ClientAliveInterval 60
ClientAliveCountMax 10 #default is 3

Be sure to open up the correct port on your AWS firewall. I suggest restricting traffic on port 12345 only to the ip address of your client so you’re not opening up the port for nefarious actors.

In your terminal enter:

ssh 123.123.123.123 -2 -4 -T -N -C -R 0.0.0.0:12345:127.0.0.1:22

This creates an ssh tunnel, where a client can ssh into port 12345 on the AWS server which tunnels the traffic to your windows server behind the firewall.

Tunnel HTTP through SSH Tunnel with PuTTY

On your client computer, install PuTTY with the msi

https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html

Use the PuTTYgen tool that’s installed to convert the private key to PuTTY’s .ppk format

Open PuTTY,

Host Name (of IP Address): 123.123.123.123 (ip of your aws server)
port: 12345

In the category field go to Connection -> SSH -> Tunnels

in source port: 8080
destination port: localhost:80
Select Dynamic

Click Add

Navigate back to Session

name the configuration, and save, then press open.

Because all traffic coming into 12345 on the aws server is routed to port 22 on the windows server, log in with your username and password for the windows machine. Once logged in, you’ll see the command prompt on your windows server.

Configure Proxy

Firefox is the easiest to set up. From options menu (top right) select Settings

At the very bottom of GENERAL, in the Network Settings section, select Settings

From the radio buttons, select Manural proxy configuration

in SOCKS Host: localhost
Port: 8080

Select Socks v5

Check the box next to Proxy DNS when using SOCKS V5

Test you setup

open google and search for “What is my ip”, The search result includes your IP address, it should be the IP of the WAN (Wide Area Network) of your windows server.

Troubleshoot

OpenSSH has a verbose mode. Add -v to your command for standard amount of verboseness. -vv will add even more info, -vvv will provide even more

Recap

The first tunnel is to forward all traffic from port 12345 on the AWS server to port 22 on the Windows Server

The Second tunnel forwards all traffic on port 80 (http) from your client computer (through the first tunnel), then requests the http traffic from the windows server.

Use SSH (without PuTTY)

In a terminal:

ssh -D 8080 -C -N [email protected] -p 12345

-D ::: Enables dynamic port forwarding, also known as a SOCKS proxy.
8080 is the binding port
-C ::: Enables compression
-N ::: Enables quiet mode
user ::: your windows server username
123.123.123.123 ::: Your AWS server
-p ::: Set ssh port
12345 ::: the port we want to connect to the AWS server so traffic is forwarded to the windows server

Activate Proxy on Mac

Settings -> Network -> (your network connection wifi/ethernet)

Details

From the details screen, select Proxies menu item.

activate SOCKS proxy.
Server: localhost
port: 8080

Auto Restart MySQL on Fail on Bitnami WordPress

On one of my WordPress sites on a Bitnami image on Lightsail, my MySql instance would fail often. This script restarts it when it goes down.

vim /opt/bitnami/mysqlmon.sh


#!/bin/bash

# Check if MySQL is running
if sudo /opt/bitnami/ctlscript.sh status mysql != "mysql already runn
ing";then

echo -e "MySQL Service was down. Restarting now...\n"
sudo /opt/bitnami/ctlscript.sh restart mysql
else
echo -e "MySQL Service is running already. Nothing to do her
e.\n"
fi
sudo crontab -e

* * * * /home/bitnami/mysqlmon.sh > /dev/null 2>&1

Connect RDP through a SSH Tunnel

My desktop is behind a router I don’t have access to, but I wanted to be able to RDP into it.

After some searching I found this article about how to do just that!
https://eviatargerzi.medium.com/how-to-access-rdp-over-ssh-tunnel-c0829631ad44

I already had a instance of lightsail on AWS so I just used it.

ensure openssh-server is installed, if not:

sudo apt update
sudo apt install openssh-server

Edit the sshd_config

sudo vim /etc/ssh/sshd_config

scroll to the bottom. Type “i” for insert then add the following lines

#Allow RDP Tunneling
GatewayPorts=clientspecified

To save: esc then “:wq” (command, wright, quit)

You can check that it’s in there by running

cat /etc/ssh/sshd_config | grep Gate

response:

#GatewayPorts no
GatewayPorts=clientspecified

Configure RDP Host

Eviatar suggests using Plink(Putty Link) on your host computer

Download: https://www.chiark.greenend.org.uk/~sgtatham/putty/latest.html

After installation, you may need to set the path.

You may be able to run

set PATH='C:\Program Files\PuTTY\plink.exe'

Close and reopen command prompt. If that doesn’t work, from the start menu search bar look for “environment variables” and open “Edit the System Environmental Variables”

Click “Environment Variables…” button under the Advanced Tab

Under “User variables” select “Path” then click edit

Click new and add C:\Program Files\PuTTY\plink.exe

Click OK, then restart command prompt and type plink to see if the system found it.

plink <user>@<ip or domain> -i <c:/users/user/.ssh/private_key_from_aws> -P 22 -2 -4 -T -N -C -R 0.0.0.0:12345:127.0.0.1:3389

-i – Set key location
-P – Set port
-2 – Force protocol version
-4 – Force use of IPv4 (and not IPv6)
-T – disable putty from attempting to allocate a pseudo-terminal at the server
-N – Prevents Putty from attempting to start a shell or command on the remote server
-C – Enables compression
-R – forward remote port to local address: Here port 12345 will be forwarded to 3389

This can be simplified using OpenSSH

Follow instructions from microsoft to install:
https://learn.microsoft.com/en-us/windows-server/administration/openssh/openssh_install_firstuse?tabs=gui

  #aws server
  Host 123.123.123.123
    User bitnami
      IdentityFile ~/.ssh/id_rsa
      IdentitiesOnly yes
      ServerAliveInterval 60
      ServerAliveCountMax 10

Adding ServerAliveInterval sets the number of seconds the client will wait before sending a packet to the server to keep connection alive

ServerAliveCountMax sets the number of times the client will try to keep connection alive.

You can also add ClientAliveInterval to etc/sshd/ssh_config on the server

ClientAliveInterval 60
ClientAliveCountMax 10 #default is 3

Be sure to open up 3389 on your server’s firewall

ssh 123.123.123.123 -2 -4 -T -N -C -R 0.0.0.0:12345:127.0.0.1:3389

Install NPM & Node on Windows

NPM documentation suggests installing from NVM since the node installer could cause permission issues down the line.

There’s an nvm port: https://github.com/coreybutler/nvm-windows

Once installed, open a terminal window (or close and reopen)

nvm install node

Search for Environmental Variables in Windows Search. This will open System Settings, click Environmental Variable, Edit “Path”, Create new and browse to your nvm install location for me it was C:\Users\USER\AppData\Roaming\nvm\v19.9.0

Save, then close your terminal window and open it again, so the new paths update.

npm --version


Deploy a Flask App to Lightsail or other VS

sudo apt update 
sudo apt upgrade 
sudo apt-get -y install python3-pip 

If apt can’t find the module add the repository:

sudo add-apt-repository ppa:deadsnakes/ppa
sudo apt-get -y install python3-pip 

Install pipenv

pip3 install pipenv

Clone your repository

git clone https://github.com/account/myproject.git
cd project

Install the environment

pipenv install

I had problems with this and had to uninstall the apt vetrsion of virtualenv

sudo apt remove python3-virtualenv

Look for the green line under Creating virtual environment

Virtualenv location: /home/ubuntu/.local/share/virtualenvs/myproject-qDDIoa4O

Make a note of that location

Configure Gunicorn

pipenv shell
pip install gunicorn
gunicorn --bind 0.0.0.0:5000 'myproject:create_app()'

in Azure and AWS port 5000 is blocked but you can create an ssh tunnel to test to make sure your app is working:

#Local Terminal
ssh -N -L 5000:127.0.0.1:5000 <server ip>

in a browser go to http://127.0.0.1:5000/ and your app should pop up.

ctrl-c to terminate the gunicorn process

exit

to leave the virtual environment

sudo vim  /etc/systemd/system/myproject.service 

[Unit]
Description=Gunicorn instance to serve myproject
After=network.target

[Service]
User=ubuntu
Group=www-data
WorkingDirectory=/home/ubuntu/myprojectdir
Environment=PATH=/home/ubuntu/.local/share/virtualenvs/myproject-8Mk7vn
ExecStart=/home/ubuntu/.local/share/virtualenvs/myproject-8Mk7vntI/bin/gunicorn --workers 3 --bind unix:myproject.sock -m 007 myproject:create_app()

[Install]
WantedBy=multi-user.target

Start and enable service:

sudo systemctl start myproject
sudo systemctl enable myproject

Configure Nginx

If nginx isn’t installed

sudo apt install nginx
sudo vim /etc/nginx/sites-available/myproject

i to insert

server {
    listen 80;
    server_name your_domain www.your_domain;

    location / {
        include proxy_params;
        proxy_pass http://unix:/home/ubuntu/myproject/myproject.sock;
    }
}

esc then :wq to write and quit

Link your site in available to sites-enabled

sudo ln -s /etc/nginx/sites-available/myproject /etc/nginx/sites-enabled

run test

sudo nginx -t

Restart nginx

sudo systemctl restart nginx

Test from your browser that it’s working

Add SSL

https://certbot.eff.org/instructions?ws=nginx&os=ubuntufocal

ensure snap is installed

sudo snap install core; sudo snap refresh core

use snap to install certbot

sudo snap install --classic certbot

link the certbot to bin

sudo ln -s /snap/bin/certbot /usr/bin/certbot

Run certbot

sudo certbot --nginx

follow instructions and you’re set.

Running into Problems

One way to diagnose potential problems is to view the terminal journal of the service

journalctl -u <app name> -n 100

Running old wordpress themes on Lightsail

I do not recommend continuing to use PHP 5.6. It’s full of vulnerabilities, but I needed to buy myself some time with some old WordPress themes that error out on higher version of php.

Stop bitnami services and save the instance as a backup

sudo /opt/bitnami/ctlscript.sh stop
sudo mv /opt/bitnami /opt/bitnami.back

Download and install archived instance (check the Bitnami changelog to find if you need a different version, then just update the numbers accordingly)

cd /tmp
curl -LO https://downloads.bitnami.com/files/stacks/wordpress/4.8.2.php56-0/bitnami-wordpress-4.8.2.php56-0-linux-x64-installer.run

chmod +x bitnami-wordpress-4.8.2.php56-0-linux-x64-installer.run
sudo ./bitnami-wordpress-4.8.2.php56-0-linux-x64-installer.run

for PHP 7 useupl

 curl -LO https://downloads.bitnami.com/files/stacks/wordpress/5.9.1-0/bitnami-wordpress-5.9.1-0-linux-x64-installer.run

The newest version (at writing) using PHP8 is 6.1.1-0 and can be downloaded:

curl -LO https://downloads.bitnami.com/files/stacks/wordpress/6.1.1-0/bitnami-wordpress-6.1.1-0-linux-x64-installer.run

install to /opt/bitnami/

sudo nano /opt/bitnami/apache2/conf/bitnami/bitnami.conf

update document root

DocumentRoot "/opt/bitnami/apache2/htdocs"
  <Directory "/opt/bitnami/apache2/htdocs">

to

 DocumentRoot "/opt/bitnami/apps/wordpress/htdocs"
  <Directory "/opt/bitnami/apps/wordpress/htdocs">

then lower under ssl

<VirtualHost _default_:443>
  DocumentRoot "/opt/bitnami/apps/wordpress/htdocs"  #Edit
  SSLEngine on
SSLCertificateFile "/opt/bitnami/apache2/conf/server.crt"
SSLCertificateKeyFile "/opt/bitnami/apache2/conf/server.key"

  <Directory "/opt/bitnami/apps/wordpress/htdocs"> #Edit

...

at the end add the following to allow for larger PHP uploads. Adjust so your All-in-One WordPress Backup file will upload.

php_value upload_max_filesize 128M
php_value post_max_size 128M
php_value memory_limit 256M
php_value max_execution_time 300
php_value max_input_time 300

sudo nano /opt/bitnami/apps/wordpress/htdocs/wp-config.php

define('WP_SITEURL', 'http://' . $_SERVER['HTTP_HOST'] . '/wordpress');
define('WP_HOME', 'http://' . $_SERVER['HTTP_HOST'] . '/wordpress');

to

define('WP_SITEURL', 'http://' . $_SERVER['HTTP_HOST]);
define('WP_HOME', 'http://' . $_SERVER['HTTP_HOST']);

sudo nano /opt/bitnami/apache2/conf/bitnami/bitnami-apps-prefix.conf

#Include "/opt/bitnami/apps/wordpress/conf/httpd-prefix.conf"  

sudo /opt/bitnami/ctlscript.sh restart apache

go ahead and enter ip address in browser

Update WordPress and plugins

Before using all-in-one wp migration change the a record to the new ip address and set up encryption.

https://docs.bitnami.com/aws/how-to/generate-install-lets-encrypt-ssl/

Install Let’s Encrypt

For bitnami v5.9.1 just run

sudo /opt/bitnami/bncert-tool

For older versions, you’ll have to install the tool first

wget -O bncert-linux-x64.run https://downloads.bitnami.com/files/bncert/latest/bncert-linux-x64.run
sudo mkdir /opt/bitnami/bncert
sudo mv bncert-linux-x64.run /opt/bitnami/bncert/
sudo chmod +x /opt/bitnami/bncert/bncert-linux-x64.run
sudo ln -s /opt/bitnami/bncert/bncert-linux-x64.run /opt/bitnami/bncert-tool

now you can run the Bitnami HTTPS Configuration Tool

sudo /opt/bitnami/bncert-tool

Remove Bitnami Banner

sudo /opt/bitnami/apache2/bnconfig --disable_banner 1

Reverse Order Numbering in Microsoft Word

  1. Select Paragraphs
  2. Under insert, click Table, then Convert Text To Table…
  3. Number of Columns: 1
  4. Separate Text at: Paragraph
  5. Click Ok
  6. Right-click on table -> Insert -> Columns to the Left
  7. Shrink column size by dragging middle boundary toward left
  8. With ONLY left column selected, under Home, click Numbering (numbered list)
  9. Copy all numbers
  10. Open new document, and Paste as “Keep Text Only”
  11. Under Home, click Sort (a -z with an arrow)
  12. Sort by: Paragraphs, Descending
  13. Click ok
  14. Copy Text
  15. In original document, with original numbers selected, under Home, click Numbering(number list) to turn off numbering
  16. Ctrl-v or paste your sorted list of numbers.
  17. Format table by right clicking on table edge and selecting Table Properties

Thanks to Susan Harkins from techrepublic.com for the Original Post

Using IF ELSE in R

Basics:

x <- 5
if (x == 5){ 
   print("x = 5")
   }
#response: "x = 5"

if (x == 6){
   print("x = 5")
}
#no response

If Else

if (x == 5){
   print("x = 5")
 }else{
   print("x doesn't equal 5")

 }
#response "x = 5"

if (x == 6){
  print("x = 6")

}else{
  print("x doesn't equal 6")
}
#response: "x doesn't equal 6"

To make a IF NOT statement add the ! function in the mix

x <- 5
if (!(x == 5)){ 
   print("x does not = 5")
   }
#no response

if (!(x == 6)){ 
   print("x does not = 6")
   }
#response: "x does not = 6"

For numbers you can do the same thing with the not equal sign

x <- 5
if (x != 6){ 
   print("x does not = 6")
   }
#return "x does not = 6"

Make a chain of if statements with if, else if statements. (like python’s elif)

x <- 5
if (x < 5){ 
   print("x is less than 5")
   }else if(x > 5){
     print("x is more than 5")
   }else if(x == 5){
     print ("x is equal to 5")
   }else {
     print ("x is not a number")
   }
#return: "x is equal to 5"

Also look at elseif()

ifelse returns a value with the same shape as test which is filled with elements selected from either yes or no depending on whether the element of test is TRUE or FALSE.

x <- 5
print(ifelse(x==5, 'x is equal to 5', NA))
# "x is equal to 5"

print(ifelse(x==6, 'x is equal to 5', 'x is not equal to 5'))
# "x is not equal to 5"

ifelse(x == 5, print('x is equal to 5'), print('x is not equal to 5'))
# "x is equal to 5"
# "x is equal to 5"

y <- 5
ifelse(y == 5, print('y is equal to 5'), print('y is not equal to 5'))
# "y is not equal to 5"
# "y is not equal to 5"

NOTE: ifelse() strips attributes

https://rdrr.io/r/base/ifelse.html

For Tidyverse look at if_else()

https://dplyr.tidyverse.org/reference/if_else.html

Tapered Stream Lines from NHD Plus HR in ArcGIS Pro

Download NHDPlus HR Data

You can find the Data on the National Map:

https://apps.nationalmap.gov/downloader/

Zoom into the area you need data for, check Hydrography, NHDPlus High Resolution, then HU-4 Subregion (HUC 4 areas) unless you need a smaller area then HU-8.

Click the big blue button Search Products

Download the products you need. Choose the “Zip” version which is the GDB. If you get the z7 version you’ll download the Raster version.

Once downloaded, unzip, then drag the GDB folder to the database folder in the Catalogue panel.

Under Hydrography, drag NHDFlowline to your contents panel, then drag the NHDPlusFlowlineVAA table to contents as well.

On Contents panel, right click on NHDFlowline, under Joins and Relates, click Add Join.

for input Join Field, select NHDPlusID.
For Join Table, select NHDPlusFlowlineVAA then add NHDPlusID to the Join Table Field if it doesn’t auto populate.

Now that the polypath has the data we need, right click NHDFlowline in Contents panel and select Symbology to open its panel.

When I first started working with tapered streams, I would use “Mean Flow Volume,” but I’ve found using the TotalDrainageAreaSqKm creates better results especially for delta areas where rivers split.

In the Symbology panel, select TotalDrainageAreaSqKm then take a look at the Histogram

It looks like there are values in the -9999 (likely meaning a stream segment without information available), then a tiny percentage of streams with the largest area. To fix this, double click on NHDFlowline in Contents panel to open its properties. Select Definition Query, hit the +, and add a new query Where upstreamCumulativeAreaSqKm is greater than 0 AND UpstreamCumulativeAreaSqKm is less than 400.

Now when you pull up the Histogram in Symbology, it will present something much more meaningful.

Set Classes to 5, switch to Manual Interval, then adjust the Histogram breakpoints until it look right.

Now we’ve got to add back those major trunk rivers. Right click on NHDFlowline, click copy, then right click on the map name at the top (unless you renamed it it may be “Map”) select Paste. Rename this feature set by clicking on the words once. Change its name to NHDFlowlineBigRiver

Double click to enter its properties, select Definition Query. Then edit the definition Where TotalDrainageArea is greater or equal to 100 .

Go back to your Symbology panel, create 5 classes between 1.5 and 2pt, then change adjust values in histogram as needed.

Part 2 coming soon.