科技資訊 | HanG321 Blog

avoid subquery in mysql update statement

November 29, 2018|MySQL|生活記事, 科技資訊

公司最近有個deployment，要run個SQL patch。係QA 同staging 都冇事，點知一去production run左30mins 都未行完，terminated & rollback. 原來係其中一個update statement 行subquery，where id = (select …) mysql 會run select on each row DataSet 一大，慢到嘔。

CREATE TABLE alarm_backup SELECT * FROM alarm;  

UPDATE alarm a
SET a.status = 'active' , ....... 
WHERE a.id = (
  SELECT al.id 
  FROM alarm_backup ab 
    JOIN device d ON ab.device_id = d.id
    JOIN ......
  WHERE .....
);

1

2

3

4

5

6

7

8

9

10

11

CREATE TABLE alarm_backup SELECT * FROM alarm;

UPDATE alarm a

SET a.status = 'active' , .......

WHERE a.id = (

SELECT al.id

FROM alarm_backup ab

JOIN device d ON ab.device_id = d.id

JOIN ......

WHERE .....

);

其實subquery 係唔需要⋯⋯

CREATE TABLE alarm_backup SELECT * FROM alarm;  

UPDATE alarm a
JOIN device d ON a.device_id = d.id
JOIN ......
SET a.status = 'active' , ....... 
WHERE ..... ；

1

2

3

4

5

6

7

CREATE TABLE alarm_backup SELECT * FROM alarm;

UPDATE alarm a

JOIN device d ON a.device_id = d.id

JOIN ......

SET a.status = 'active' , .......

WHERE ..... ；

故事教訓我地： 1) 放假返黎之後小心點，你唔係到個時既Peer review 都要睇返。 2) 唔好咁容易信你上司，上左神枱技術係會回塘

hacktoberfest

October 6, 2018|git|科技資訊

funny, you can get a free tee shirt when you make 5 pull request in Oct 1-31. This is the 5th year but I only knew it now check out the details in https://hacktoberfest.digitalocean.com/

Google Cloud Summit Sydney 2018 part 1

September 27, 2018|GCP, kubernetes|生活記事, 科技資訊

24 hours 快閃到sydney 訓一晚. 原本打算早點出門, 行過Sydney University 參觀吓先到會場, 可惜落雨都係uber. Keynote 比較大路，冇乜特別，都係話google 投資左好多錢咁啦。GCP 起步遲過AWS, 不過我覺得kubernetes 先係王道，比起vendor lock in AWS 好. 其次我覺得AWS product啲名好麻煩好難記 1st: CI/CD pipelines in the cloud 由source code repo, build & test, artifact management, deliver. 以前係svn + maven + jenkins，而家係github/gitlab + Cloud Build + Container Registey + Spinnaker. 2nd: Kubernetes & Istio: The Efficient …

升級/轉移 WordPress in GCP marketplace

September 4, 2018|GCP, wordpress, 粵文|科技資訊

舊年係GCP用”WordPress Certified by Bitnami”，原來佢地個個Base Image 係Debian 8. 上兩個月收到通知要更新，今個weekend趁住有時間順便一次過upgrade。今次會試下”Nginx WordPress with NGINX and SSL Certified by Bitnami“，睇下用Nginx會唔會快過Apache。 1. New VM 首先登入GCP，左上角Menu > MarketPlace，然後搜尋 WordPress，再揀Nginx WordPress with NGINX and SSL Certified by Bitnami，按Launch on Compute Engine button。因為Free Tier 嘅關係，記住要揀係美國嘅region，同 f1-micro instance. Disk size 30GB 以下就得, 我自己用15GB. info: https://cloud.google.com/compute/pricing#freeusage 2. Backup 等緊個VM create嘅時候，可以就係用緊嘅WordPress 做 Backup。Install “All-in-One WP Migration” plugin, …

Qwiklabs: GCP & Kubernetes

August 28, 2018|GCP, google, Qwiklabs|科技資訊

I was lucky that able to get 1 month free subscriptions on Qwiklabs and finished some of their quest. Good way to get your hand dirty and know how to use these tools. Sadly that promotion ended 31th July. Kubernets is really changing the way of software deployments. Docker was starting to popular 4-5 years …

LikeCoin

August 27, 2018|LikeCoin, Medium|生活記事, 科技資訊

This blog haven’t update for a loooog looooooong time, last article is published at mid October last year. Revisit here, partly because LikeCoin starts a campaign for Mining beta 2, which distributes 30,000 LIKE every day. Mining Beta 1 requires medium, despite I join the subscriptions recently (profile: @hang321), last few weeks I was occupied …

XPEnology DSM 6.1

October 12, 2017|DSM, XPEnology|科技資訊

Infamous Seagate Barracuda 3TB hard disk suddenly dead. Due to physical/raw disk mapping, DSM on ESXi cannot do S.M.A.R.T. test and ESXi does not alert as well…. Anyway, this time forget about ESXi, just install DSM 6.1. Planning to use docker for other applications in future. For information to install, follow 2 links below https://xpenology.com/forum/topic/7973-tutorial-installmigrate-dsm-52-to-61x-juns-loader/ …

Install macOS High Sierra on VMware

July 24, 2017|High Sierra, macOS, VMware|科技資訊

more complicated than I thought, VMware Workstation patch and difficult to create ISO image. 1. macOS High Sierra 10.13 Beta 3 If you don’t have mac, you need to spend some time to find the source. check out https://betas.cmacapps.com/ to find a suitable one. Unzip the dmg file by 7zip, InstallESD.img & BaseSystem.dmg located in “macOS …

upload file on nginx – part 2: MySQL PAM and nginx config

June 21, 2017|CentOS, Linux, nginx|科技資訊, 金融財經

MySQL PAM Config set up database table and column name for username and password # vim /etc/pam.d/nginx

auth required pam_mysql.so user=USERNAME_HERE \
  passwd=PASSWORD_HERE \
  host=HOSTNAME_HERE \
  db=DATABASE_HERE \
  table=TABLE_OR_VIEW_NAME_HERE \
  usercolumn=USERNAME_COLUMN \
  passwdcolumn=PASSWORD_COLUMN \
  crypt=0 sqllog=0
# repeat .... with config above
account required pam_mysql.so user=USERNAME_HERE \  ........    crypt=0 sqllog=0
# alternatively, to external config file
# account required pam_mysql.so config_file=/etc/pam_mysql.conf

1

2

3

4

5

6

7

8

9

10

11

12

auth required pam_mysql.so user=USERNAME_HERE \

passwd=PASSWORD_HERE \

host=HOSTNAME_HERE \

db=DATABASE_HERE \

table=TABLE_OR_VIEW_NAME_HERE \

usercolumn=USERNAME_COLUMN \

passwdcolumn=PASSWORD_COLUMN \

crypt=0 sqllog=0

# repeat .... with config above

account required pam_mysql.so user=USERNAME_HERE \ ........ crypt=0 sqllog=0

# alternatively, to external config file

# account required pam_mysql.so config_file=/etc/pam_mysql.conf

in nginx config (/etc/nginx/nginx or /etc/nginx/conf.d/)

location /restricted {
  auth_pam              "Restricted Zone"
  auth_pam_service_name "nginx"
}

1

2

3

4

location /restricted {

auth_pam "Restricted Zone"

auth_pam_service_name "nginx"

}

Upload module config in nginx config (/etc/nginx/nginx or /etc/nginx/conf.d/)

location /upload{
  auth_pam              "Restricted Zone"
  auth_pam_service_name "nginx"

  upload_pass  /uploadHandler.php;
  # the location where the files are uploaded
  upload_store	/var/tmp/fuploads;
  upload_store_access user:rw group:rw all:r;
  upload_resumable on;
  upload_state_store /var/tmp/fuploads/state ;

  # Set specified fields in request body
  # the field data will be available through the superglobal $_POST[]
  upload_set_form_field "${upload_field_name}_name" $upload_file_name;
  upload_set_form_field "${upload_field_name}_content_type" $upload_content_type;
  upload_set_form_field "${upload_field_name}_path" $upload_tmp_path;

  upload_cleanup 400 404 499 500-505;
}

# pass the PHP scripts to FastCGI server
location ~ \.php$ {
  try_files $uri =404;
	fastcgi_pass unix:/var/run/php-fpm/php-fpm.sock;
	fastcgi_index index.php;
	fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
	include fastcgi_params;
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

location /upload{

auth_pam "Restricted Zone"

auth_pam_service_name "nginx"

upload_pass /uploadHandler.php;

# the location where the files are uploaded

upload_store /var/tmp/fuploads;

upload_store_access user:rw group:rw all:r;

upload_resumable on;

upload_state_store /var/tmp/fuploads/state ;

# Set specified fields in request body

# the field data will be available through the superglobal $_POST[]

upload_set_form_field "${upload_field_name}_name" $upload_file_name;

upload_set_form_field "${upload_field_name}_content_type" $upload_content_type;

upload_set_form_field "${upload_field_name}_path" $upload_tmp_path;

upload_cleanup 400 404 499 500-505;

}

# pass the PHP scripts to FastCGI server

location ~ \.php$ {

try_files $uri =404;

fastcgi_pass unix:/var/run/php-fpm/php-fpm.sock;

fastcgi_index index.php;

fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;

include fastcgi_params;

}

php file upload handler example can be found on https://github.com/blueimp/jQuery-File-Upload/wiki/Uploading-to-nginx-using-the-nginx-upload-module-with-php_handler and https://github.com/blueimp/jQuery-File-Upload/tree/master/server/php

upload file on nginx – part 1: compile dynamic modules

June 20, 2017|CentOS, Linux, nginx|科技資訊, 電腦編程

Recently I was assigned to a task about adding a file upload feature to a existing nginx server on CentOS. Nginx was chosen years ago because it simply handles some static files only. If the file upload was a requirement, using apache is simpler as computation power isn’t a constraint in our case. Background CentOS 7 …

March 2025
M	T	W	T	F	S	S
« Feb
					1	2
3	4	5	6	7	8	9
10	11	12	13	14	15	16
17	18	19	20	21	22	23
24	25	26	27	28	29	30
31

Archives