Eric Guo's blog.cloud-mes.com

Hoping writing JS, Ruby & Rails and Go article, but fallback to DevOps note

Bug or Correct Behavior but Very Miss Leading LEFT JOIN in MySQL 8

• Permalink

Today I found a missing rows in MySQL 8 problem in production, which SQL LEFT JOIN should be added at join condition instead of where.

Missing data version:

SELECT SUM(markettotal) markettotal

FROM `SUB_COMPANY_REAL_RECEIVE`

LEFT JOIN ORG_REPORT_DEPT_ORDER on ORG_REPORT_DEPT_ORDER.编号 = SUB_COMPANY_REAL_RECEIVE.deptcode_sum

WHERE `SUB_COMPANY_REAL_RECEIVE`.`realdate` BETWEEN '2020-01-01' AND '2020-06-30'

AND (ORG_REPORT_DEPT_ORDER.开始时间 <= '2020-06-11')

AND (ORG_REPORT_DEPT_ORDER.结束时间 IS NULL OR ORG_REPORT_DEPT_ORDER.结束时间 >= '2020-06-11')

AND `SUB_COMPANY_REAL_RECEIVE`.`orgcode_sum` IN ('H000109', '000109')

Correct version:

SELECT sum(markettotal)

FROM `SUB_COMPANY_REAL_RECEIVE`

LEFT JOIN ORG_REPORT_DEPT_ORDER ON SUB_COMPANY_REAL_RECEIVE.deptcode = ORG_REPORT_DEPT_ORDER.编号

AND ((ORG_REPORT_DEPT_ORDER.开始时间 <= '2020-06-11') AND (ORG_REPORT_DEPT_ORDER.结束时间 IS NULL OR ORG_REPORT_DEPT_ORDER.结束时间 >= '2020-06-11'))

WHERE `SUB_COMPANY_REAL_RECEIVE`.`realdate` BETWEEN '2020-01-01' AND '2020-06-30'

  AND `SUB_COMPANY_REAL_RECEIVE`.`orgcode_sum` IN ('H000109')

New T3 Micro EC2 Box on Amazon Linux Install Log

• Permalink

Time is pass so faster, another 3 years passed after my previous T2 micro installed.

Now have to install another new box with price $134 in Tokyo AWS.

Install postgresql 11

yum update

amazon-linux-extras install postgresql11 epel

yum install -y postgresql-server postgresql-devel

/usr/bin/postgresql-setup --initdb

systemctl enable postgresql

systemctl start postgresql

sudo -u postgres -i psql -c 'SELECT version();'

Install nginx

amazon-linux-extras install nginx1

yum install nginx

systemctl enable nginx

systemctl start nginx

Install node.js 14 & yarn

curl --silent --location https://rpm.nodesource.com/setup_14.x | bash -

yum install -y gcc-c++ make

yum install -y nodejs

curl -sL https://dl.yarnpkg.com/rpm/yarn.repo | tee /etc/yum.repos.d/yarn.repo

yum install yarn

Install dependencies required by rbenv and ruby 2.7.1

yum install openssl-devel readline-devel zlib-devel gdbm-devel

Fix Yum Update Postgresql12 to v12.3 Require LLVM-toolset-7-clang >= 4.0.1 Dependency Problem

• Permalink

Simple running yum update on a CensOS 7 machine which installing the official postgresql v12 will get below problem:

---> Package postgresql12-devel.x86_64 0:12.3-1PGDG.rhel7 will be an update

--> Processing Dependency: llvm-toolset-7-clang >= 4.0.1 for package: postgresql12-devel-12.3-1PGDG.rhel7.x86_64

--> Finished Dependency Resolution

Error: Package: postgresql12-devel-12.3-1PGDG.rhel7.x86_64 (pgdg12)

           Requires: llvm-toolset-7-clang >= 4.0.1

 You could try using --skip-broken to work around the problem

 You could try running: rpm -Va --nofiles --nodigest

You can install CentOS SCLo RH repository and install llvm-toolset-7-clang to resolve it.

yum install centos-release-scl-rh

yum install llvm-toolset-7-clang

Deploy Another Rails 6 App to Existing CentOS 7 Server

• Permalink

Prepare the execution files & account

adduser sccsa_web

sudo su - sccsa_web

mkdir .ssh

chmod 700 .ssh

vi .ssh/authorized_keys

chmod 600 .ssh/authorized_keys

Install rbenv and ruby-build

cd # as a sccsa_web

git clone https://github.com/sstephenson/rbenv.git .rbenv

cd ~/.rbenv && src/configure && make -C src

echo 'export PATH="$HOME/.rbenv/bin:$PATH"' >> ~/.bash_profile

~/.rbenv/bin/rbenv init

# As an rbenv plugin

mkdir -p "$(rbenv root)"/plugins

# git clone https://github.com/rbenv/ruby-build.git "$(rbenv root)"/plugins/ruby-build

git clone https://gitee.com/Eric-Guo/ruby-build.git "$(rbenv root)"/plugins/ruby-build

# git clone https://github.com/andorchen/rbenv-china-mirror.git "$(rbenv root)"/plugins/rbenv-china-mirror

git clone https://gitee.com/Eric-Guo/rbenv-china-mirror.git "$(rbenv root)"/plugins/rbenv-china-mirror

Install Ruby 3.0.1

rbenv install -l

rbenv install 3.0.1

rbenv global 3.0.1

rbenv shell 3.0.1

echo 'eval "$(rbenv init -)"' >> ~/.bash_profile

echo "gem: --no-document" > ~/.gemrc

# gem install bundler --default -v "1.17.3"

gem install bundler

bundle config build.pg --with-pg-config=/usr/pgsql-13/bin/pg_config

Fix permission for CentOS

sudo mkdir /var/www

cd /var/www

sudo mkdir sccsa_web

sudo chown sccsa_web:sccsa_web sccsa_web/

Fix Rake 11.3 and Jekyll 0.12.1 Warning Under Ruby 2.7.1

• Permalink

After upgrade ruby to 2.7.1, I found my old octopress give below two warning:

/usr/local/lib/ruby/gems/2.7.0/gems/rake-11.3.0/lib/rake/application.rb:378: warning: deprecated Object#=~ is called on Proc; it always returns nil

Just need change application.rb:378 as below to fix it.

-        opt.select { |o| o =~ /^-/ }.map(&:downcase).sort.reverse

+        opt.select { |o| o.is_a?(String) && o =~ /^-/ }.map(&:downcase).sort.reverse

Another warning is:

/usr/local/lib/ruby/gems/2.7.0/gems/jekyll-0.12.1/lib/jekyll/post.rb:140: warning: URI.escape is obsolete

Just change as below:

-          "categories" => categories.map { |c| URI.escape(c) }.join('/'),

+          "categories" => categories.map { |c| URI.encode_www_form_component(c) }.join('/'),

Fix Puma Failed to Restart in Ruby 2.7.1

• Permalink

After install ruby 2.7.1, found capistrano deploy via puma having below error when restart:

/home/web_site/.rbenv/versions/2.7.1/bin/bundle:23:in `load': cannot load such file -- /home/web_site/.rbenv/versions/2.7.1/lib/ruby/gems/2.7.0/gems/bundler-2.1.4/libexec/bundle (LoadError)

After checking long time found it's just not install the bundler 1.17.3 as pumactl relay on it.

rbenv uninstall 2.6.5

rbenv global 2.7.1

rbenv shell 2.7.1

gem install bundler --default -v "1.17.3"

gem install bundler

Notes, still need after capistrano-bundler release 2.0.

Install Snell-server in CentOS 7

• Permalink

Prepare the execution files & account

adduser snell

sudo su - snell

mkdir .ssh

chmod 700 .ssh

vi .ssh/authorized_keys

chmod 600 .ssh/authorized_keys

# see other version at https://kb.nssurge.com/surge-knowledge-base/release-notes/snell

wget https://dl.nssurge.com/snell/snell-server-v5.0.1-linux-amd64.zip

unzip snell-server-v5.0.1-linux-amd64.zip

mkdir snell

mv snell-server snell

rm snell-server-v5.0.1-linux-amd64.zip

cd snell

./snell-server # generate conf file

Configure the system auto start

vi /etc/systemd/system/snell.service

[Unit]

Description=Snell Proxy Service

After=network.target

 
[Service]

Type=simple

User=snell

Group=snell

LimitNOFILE=32768

ExecStart=/home/snell/snell/snell-server -c /home/snell/snell/snell-server.conf

 
[Install]

WantedBy=multi-user.target

Add port

sudo firewall-cmd --zone=public --add-port=11666/tcp

sudo firewall-cmd --zone=public --add-port=11666/tcp --permanent

sudo firewall-cmd --zone=public --add-port=11666/udp

sudo firewall-cmd --zone=public --add-port=11666/udp --permanent

sudo firewall-cmd --reload

Restart or run below

systemctl daemon-reload

systemctl start snell

systemctl status snell

systemctl restart snell

systemctl enable snell

cat /home/snell/snell/snell-server.conf

Ubuntu 18.04 Ruby on Rails Installation Log

• Permalink

Enable the Firewall

ufw app list

ufw allow ssh

ufw enable

ufw status

Install node.js

apt install nodejs

apt install npm

curl -sL https://deb.nodesource.com/setup_10.x -o nodesource_setup.sh

bash nodesource_setup.sh

apt-get install -y nodejs

curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | sudo apt-key add -

echo "deb https://dl.yarnpkg.com/debian/ stable main" | sudo tee /etc/apt/sources.list.d/yarn.list

apt update

apt install yarn

Install rbenv

apt update

apt install autoconf bison build-essential libssl-dev libyaml-dev libreadline6-dev zlib1g-dev libncurses5-dev libffi-dev libgdbm5 libgdbm-dev

apt install git

logout # as ubuntu

git clone https://github.com/rbenv/rbenv.git ~/.rbenv

echo 'export PATH="$HOME/.rbenv/bin:$PATH"' >> ~/.bashrc

echo 'eval "$(rbenv init -)"' >> ~/.bashrc

source ~/.bashrc

type rbenv

git clone https://github.com/rbenv/ruby-build.git ~/.rbenv/plugins/ruby-build

git clone https://github.com/andorchen/rbenv-china-mirror.git "$(rbenv root)"/plugins/rbenv-china-mirror

rbenv install -l

Install ruby 2.6.5

rbenv install 2.6.5

rbenv global 2.6.5

echo "gem: --no-document" > ~/.gemrc

gem install bundler

gem install rails

rbenv rehash

Install postgresql

apt-get install postgresql

apt-get install postgresql-server-dev-all

sudo su - postgres

createuser ubuntu --pwprompt

psql

ALTER ROLE ubuntu LOGIN;

CREATE DATABASE cam_price_prod WITH ENCODING='UTF8' OWNER=ubuntu;

Install nginx

apt-get install nginx

Generate ssh key

ssh-keygen

sudo mkdir /var/www

cd /var/www

sudo mkdir cam_price

sudo chown ubuntu:ubuntu cam_price/

Install HTTPS

Largely following certbot guide

apt-get update

apt-get install software-properties-common

add-apt-repository universe

add-apt-repository ppa:certbot/certbot

apt-get update

apt-get install certbot python-certbot-nginx