Eric Guo's blog.cloud-mes.com

in HTML5, JavaScript, Ruby & Rails, Python, and Cloud MES!

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 equinix_video
sudo su - equinix_video
mkdir .ssh
chmod 700 .ssh
vi .ssh/authorized_keys
chmod 600 .ssh/authorized_keys

Install rbenv and ruby-build

cd # as a deployer
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://github.com/andorchen/rbenv-china-mirror.git "$(rbenv root)"/plugins/rbenv-china-mirror

Install Ruby 2.7.1

rbenv install -l
rbenv install 2.7.1
rbenv global 2.7.1
rbenv shell 2.7.1
echo 'eval "$(rbenv init -)"' >> ~/.bash_profile
echo "gem: --no-document" > ~/.gemrc
gem install bundler --default -v "1.17.3"
gem install bundler

Fix permission for CentOS

sudo mkdir /var/www
cd /var/www
sudo mkdir equinix_video
sudo chown equinix_video:equinix_video equinix_video/

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

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
wget https://github.com/surge-networks/snell/releases/download/v2.0.2/snell-server-v2.0.2-linux-amd64.zip
unzip snell-server-v2.0.2-linux-amd64.zip
mkdir snell
mv snell-server snell
rm snell-server-v2.0.2-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

Restart or run below

systemctl daemon-reload
systemctl start snell
systemctl restart snell
systemctl enable snell
cat /home/snell/snell/snell-server.conf