瀏覽分類：

心得筆記

[MSSQL] SQL Server 的一些眉眉角角 part.1

作者lex.xu
/
11 月 01, 2018
/
心得筆記

目前工作 Database 方面主要都在使用 SQL Server，寫了近兩年的 Stored Procedure 也遇到了許多的問題，在這裡簡單筆記一下。

暫存資料表小知識
* # 或 ## 建立的暫存資料表，可從 tempdb.暫存資料表 找到。
* # 建立的暫存資料表只有該連線可以取用；## 則是全部連線均可使用。
* 連線結束時皆會自動刪除，若程式中有 Connection Pool 來管理連線則不會立刻刪除。
* 父層 Stored Procedure 建立的暫存資料表子層也可以使用；子層 Stored Procedure 建立的暫存資料表父層也可以使用
若開啟交易 (Transaction) 但無提交異動 (Commit) 會發生資料表鎖死 (Table Lock) 的問題，切記要提交異動 (Commit)。
使用主鍵來 UPDATE 為資料鎖 (Row Lock)，若使用其他條件則有可能會升級成資料頁鎖(Page Lock) 或資料表鎖 (Table Lock)。
交易 (Transaction) 具有復原機制 (RollBack)，但實際上資料表中的資料已經被異動且鎖定的關係所以無法取得，可利用 WITH(NOLOCK) 來取得被鎖定的髒資料。

未開啟交易時使用 CURSOR 或 UPDATE FROM 若超過五千筆時可能會造成鎖定與死結，可使用 ROW_NUMBER() 排序資料塞入暫存資料表並使用 WHILE 來一筆一筆更新資料避開鎖定，速度雖慢但能減少鎖死。

SET NOCOUNT ON

DECLARE
    @RowNum         INT,
    @RowCount       INT,
    @Temp_Id        INT

SELECT
    ROW_NUMBER() OVER(ORDER BY [Id]) AS RowNum,
    [Id],
    [Name]
INTO
    #Temp
FROM
    [exfast].[dbo].[TableA] WITH(NOLOCK)

SELECT
    @RowNum = 1,
    @RowCount = (SELECT SUM(1) FROM #Temp)

WHILE(@RowNum <= @RowCount)
BEGIN

    SELECT
        @Temp_Id = [Id]
    FROM
        #Temp
    WHERE
        RowNum = @RowNum

    UPDATE
        [exfast].[dbo].[TableA]
    SET
        [Name] = 'dddd'
    WHERE
        [Id] = @Temp_Id

    SET @RowNum = @RowNum + 1

END

DROP TABLE #Temp

SET NOCOUNT ON

DECLARE

@RowNum INT,

@RowCount INT,

@Temp_Id INT

SELECT

ROW_NUMBER() OVER(ORDER BY [Id]) AS RowNum,

[Id],

[Name]

INTO

#Temp

FROM

[exfast].[dbo].[TableA] WITH(NOLOCK)

SELECT

@RowNum = 1,

@RowCount = (SELECT SUM(1) FROM #Temp)

WHILE(@RowNum <= @RowCount)

BEGIN

SELECT

@Temp_Id = [Id]

FROM

#Temp

WHERE

RowNum = @RowNum

UPDATE

[exfast].[dbo].[TableA]

SET

[Name] = 'dddd'

WHERE

[Id] = @Temp_Id

SET @RowNum = @RowNum + 1

END

DROP TABLE #Temp

高負載情況下使用 UPDATE OUTPUT 的方法來取得資料更新前後的狀態，可減少 SELECT 次數提高效率。

DECLARE  @TempA  TABLE
(
    [INSERTED_Id]       INT,
    [INSERTED_Name]     NVARCHAR(32),
    [DELETED_Id]        INT,
    [DELETED_Name]      NVARCHAR(32)
)

UPDATE
    [exfast.Helper].[dbo].[TableA]
SET
    [Name] = 'qqqq'
OUTPUT
    INSERTED.[Id],
    INSERTED.[Name],
    DELETED.[Id],
    DELETED.[Name]
INTO
    @TempA

SELECT * FROM @TempA

DECLARE @TempA TABLE

(

[INSERTED_Id] INT,

[INSERTED_Name] NVARCHAR(32),

[DELETED_Id] INT,

[DELETED_Name] NVARCHAR(32)

)

UPDATE

[exfast.Helper].[dbo].[TableA]

SET

[Name] = 'qqqq'

OUTPUT

INSERTED.[Id],

INSERTED.[Name],

DELETED.[Id],

DELETED.[Name]

INTO

@TempA

SELECT * FROM @TempA

高負載情況下可能會將隔離層級拉高到 Serializable，這時使用交易會發生相同資料表 SELECT UPDATE 中的 Shared Lock 與 Exclusive Lock 撞車造成 DeadLock，可在 SELECT 加上 WITH(UPDLOCK) 來減少此問題。

-- 故意指定隔離層級模擬撞車的情況
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT * FROM [exfast.Helper].[dbo].[TableA]

WAITFOR DELAY '00:00:10'

UPDATE
    [exfast.Helper].[dbo].[TableA]
SET
    [Name] = 'cccc'
WHERE
    [Id] = 2
    
ROLLBACK

-- 故意指定隔離層級模擬撞車的情況

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN

SELECT * FROM [exfast.Helper].[dbo].[TableA]

WAITFOR DELAY '00:00:10'

UPDATE

[exfast.Helper].[dbo].[TableA]

SET

[Name] = 'cccc'

WHERE

[Id] = 2

ROLLBACK

[C#][LeetCode] 479. Largest Palindrome Product

作者lex.xu
/
10 月 01, 2018
/
心得筆記

題目

Find the largest palindrome made from the product of two n-digit numbers.
Since the result could be very large, you should return the largest palindrome mod 1337.

Example:

Input: 2
Output: 987
Explanation: 99 x 91 = 9009, 9009 % 1337 = 987

Note:
The range of n is [1,8].

解題過程

這題要找出 N 位數相乘後且結果是迴文的數字
例如：N = 2 最大數字等於 99，那就要找出最大 99 最小 10 的兩位數相乘後且結果是迴文的數字。

最初我用一個很笨的方法迴圈慢慢跑，最後當然是直接逾時，後來改成先取最大數字相乘後的前半段數字，再用迴圈去產生迴文並篩選資料，這樣比前面的笨方法快非常多。
閱讀更多

[C#][LeetCode] 494. Target Sum

作者lex.xu
/
9 月 30, 2018
/
心得筆記

題目

You are given a list of non-negative integers, a1, a2, …, an, and a target, S. Now you have 2 symbols + and -. For each integer, you should choose one from + and – as its new symbol.

Find out how many ways to assign symbols to make sum of integers equal to target S.

題目會傳入兩個參數 nums (數字陣列)、S (整數)，限制只能使用兩個運算符號 +、- 改變數字陣列 nums 的數字讓總和等於 S

解題過程

我自己比較少接觸演算法的東西(大學沒學好)，就算去翻解答也看不懂別人在寫什麼東西，後來看了演算法筆記- Backtracking 的圖解後終於理解解答再寫什麼東西

[C#][LeetCode] 515. Find Largest Value in Each Tree Row

作者lex.xu
/
9 月 30, 2018
/
心得筆記

題目

You need to find the largest value in each row of a binary tree.

Example:

<b>Input:</b> 

          1
         / \
        3   2
       / \   \  
      5   3   9 

<b>Output:</b> [1, 3, 9]

<b>Input:</b>

/ \

3 2

/ \ \

5 3 9

<b>Output:</b> [1, 3, 9]

解題過程

這題要找出二元樹中同階層最大的數字，我的作法是先將每層的數字收集起來，最後再一起比較。

答案

Dictionary<int, List<int>> dict = new Dictionary<int, List<int>>();

public IList<int> LargestValues(TreeNode root)
{
    List<int> list = new List<int>();

    if (root == null)
    {
        return list;
    }

    list.Add(root.val);
    FindNum(0, root.left, root.right);
    list.AddRange(dict.Keys.Select(x => dict[x].Max()));

    return list;
}

public void FindNum(int idx, TreeNode left, TreeNode right)
{
    if (left == null && right == null)
    {
        return;
    }

    if (!dict.ContainsKey(idx))
    {
        dict[idx] = new List<int>();
    }

    if (left != null)
    {
        dict[idx].Add(left.val);
        FindNum(idx + 1, left.left, left.right);
    }

    if (right != null)
    {
        dict[idx].Add(right.val);
        FindNum(idx + 1, right.left, right.right);
    }
}

Dictionary<int, List<int>> dict = new Dictionary<int, List<int>>();

public IList<int> LargestValues(TreeNode root)

{

List<int> list = new List<int>();

if (root == null)

{

return list;

}

list.Add(root.val);

FindNum(0, root.left, root.right);

list.AddRange(dict.Keys.Select(x => dict[x].Max()));

return list;

}

public void FindNum(int idx, TreeNode left, TreeNode right)

{

if (left == null && right == null)

{

return;

}

if (!dict.ContainsKey(idx))

{

dict[idx] = new List<int>();

}

if (left != null)

{

dict[idx].Add(left.val);

FindNum(idx + 1, left.left, left.right);

}

if (right != null)

{

dict[idx].Add(right.val);

FindNum(idx + 1, right.left, right.right);

}

[IIS][.NETCore] IIS Reverse Proxy 後 RemoteIpAddress 取值錯誤 !?

作者lex.xu
/
9 月 28, 2018
/
心得筆記

今天試著把 ASP.NET Core 放在 Docker 再利用 IIS Reverse Proxy 來對外時偶然間發現 IP 取值錯誤，利用 Request.HttpContext.Connection.RemoteIpAddress.ToString() 總是取到 Docker Host 的 IP！？

[IIS][GitLab] 利用 IIS Reverse Proxy 將 GitLab 加上 Https

作者lex.xu
/
9 月 26, 2018
/
心得筆記

最近工作上有個需求要將 GitLab 掛上 SSL 憑證，原本就有一台 Windows Server 在管理所有的 SSL 憑證，於是乎就想到可以利用 IIS Reverse Proxy 的方式將憑證集中管理在這台，這樣管理起來也比較方便。

前置作業

首先需要安裝兩個模組

安裝 URL Rewrite 的時候有踩到一個地雷，詳情請見 [IIS] Windows Server 2016 安裝 URL Rewrite 憑證失效

開始設定

建立一個新站台並設定好 SSL 憑證 (由於這邊我只是測試用，所以我自己簽一個自我憑證)
選擇 URL Rewrite
選擇 新增規則 -> 反向 Proxy
選擇 確定 啟用 Proxy
1 的地方輸入 GitLab 的位置，2 的位置輸入欲對外的 Domain
當我以為簡單就完成開啟網頁試試時卻噴錯誤給我看

HTTP 錯誤 500.52 - URL Rewrite Module Error.
當 HTTP 回應的內容經過編碼 ("gzip") 時，無法套用輸出重寫規則。

HTTP 錯誤 500.52 - URL Rewrite Module Error.

當 HTTP 回應的內容經過編碼 ("gzip") 時，無法套用輸出重寫規則。

原因是因為 GitLab 有開啟壓縮，但使用了反向 Proxy 無法將已壓縮的檔案進行修改，所以就噴了這個錯誤

解決方法是在 伺服器變數 做調整
新增兩個變數分別是 HTTP_ACCEPT_ENCODING 與 HTTP_X_ORIGINAL_ACCEPT_ENCODING
編輯輸入規則
新增兩個伺服器變數，伺服器變數名稱選擇剛剛加入的 HTTP_X_ORIGINAL_ACCEPT_ENCODING，值輸入 {HTTP_ACCEPT_ENCODING}
我們已經將 HTTP_ACCEPT_ENCODING 放到 HTTP_X_ORIGINAL_ACCEPT_ENCODING 裡面，原本的值就可以清空清空，但因為 UI 不允許輸入空值，所以這邊我就隨便打個值

如果跟我一樣很在意的話可以去站台底下的 web.config 清空剛剛的值
終於可以開起來囉 ~

顯示調整

雖然已經可以正常開啟網頁了，但網址還是會顯示錯誤

新增兩個伺服器變數 HTTP_X_FORWARDED_HOST 與 HTTP_X_FORWARDED_PROTO
HTTP_X_FORWARDED_HOST 輸入 Domain，HTTP_X_FORWARDED_PROTO 輸入 https
修改 /etc/gitlab/gitlab.rb 加上這段

external_url 'https://gitlab.exfast.me'
nginx['listen_port'] = 80
nginx['listen_https'] = false

external_url 'https://gitlab.exfast.me'

nginx['listen_port'] = 80

nginx['listen_https'] = false

這樣頁面顯示的網址就正常囉，不過 https://gitlab.exfast.me/profile/active_sessions 這邊顯示的 IP會是 IIS 那台的位址，所以還必須調整 /etc/gitlab/gitlab.rb 加上這段

# Each address is added to the the NGINX config as 'set_real_ip_from <address>;'
nginx['real_ip_trusted_addresses'] = [ '127.0.0.1', '192.168.56.106' ]
# other real_ip config options
nginx['real_ip_header'] = 'X-Forwarded-For'
nginx['real_ip_recursive'] = 'on'

# Each address is added to the the NGINX config as 'set_real_ip_from <address>;'

nginx['real_ip_trusted_addresses'] = [ '127.0.0.1', '192.168.56.106' ]

# other real_ip config options

nginx['real_ip_header'] = 'X-Forwarded-For'

nginx['real_ip_recursive'] = 'on'

參考資料

[IIS] Windows Server 2016 安裝 URL Rewrite 憑證失效

作者lex.xu
/
9 月 25, 2018
/
心得筆記

2016 年的時候我筆記過一篇 [IIS] Windows Server 2016 無法安裝 URL Rewrite !?
想不到現在 2018 年了再次安裝 URL Rewrite 時又遇到新問題，微軟爸爸不要亂放地雷好嗎？

我是從這邊下載的這個版本安裝時會出現下面這張圖的狀況

經過千辛萬苦的餵狗後發現是微軟在 2018/09/12 發布的版本帶了時間戳，而這個時間戳卻造成無法安裝的問題，這邊我有找到一個舊版本給大家急救一下點我下載 (密碼：https://blog.exfast.me/)

參考資料：
URL rewrite installation failing due to signature verification failure

[C#][.NETCore] 壓縮檔案 & 解壓縮檔案

作者lex.xu
/
9 月 24, 2018
/
心得筆記

在 .NET Framework 的時候有好用的 DotNetZip 可以快速壓縮&解壓縮檔案
但在寫 .NET Core 的時候發現這個套件不能使用，那只好默默地自己寫了

這邊就用 System.IO.Compression 提供的 ZipArchive 來簡單寫個壓縮 & 解壓縮範例

壓縮檔案：

string rootPath = AppDomain.CurrentDomain.BaseDirectory;
string saveFileName = Path.Combine(rootPath, "test.zip");
string zipFileName = Path.Combine(rootPath, "123.txt");

using (var fs = new FileStream(saveFileName, FileMode.OpenOrCreate))
{
    using (ZipArchive zipArchive = new ZipArchive(fs, ZipArchiveMode.Create))
    {
        string fileName = Path.GetFileName(zipFileName);

        var zipArchiveEntry = zipArchive.CreateEntry(fileName);
        using (var zipStream = zipArchiveEntry.Open())
        {
            byte[] bytes = File.ReadAllBytes(zipFileName);
            zipStream.Write(bytes, 0, bytes.Length);
        }
    }
}

string rootPath = AppDomain.CurrentDomain.BaseDirectory;

string saveFileName = Path.Combine(rootPath, "test.zip");

string zipFileName = Path.Combine(rootPath, "123.txt");

using (var fs = new FileStream(saveFileName, FileMode.OpenOrCreate))

{

using (ZipArchive zipArchive = new ZipArchive(fs, ZipArchiveMode.Create))

{

string fileName = Path.GetFileName(zipFileName);

var zipArchiveEntry = zipArchive.CreateEntry(fileName);

using (var zipStream = zipArchiveEntry.Open())

{

byte[] bytes = File.ReadAllBytes(zipFileName);

zipStream.Write(bytes, 0, bytes.Length);

}

解壓縮檔案：

string rootPath = AppDomain.CurrentDomain.BaseDirectory;
string zipFileName = Path.Combine(rootPath, "test.zip");
string saveFolder = rootPath;

using (var fs = new FileStream(zipFileName, FileMode.Open))
{
    using (ZipArchive zipArchive = new ZipArchive(fs, ZipArchiveMode.Read))
    {
        foreach (ZipArchiveEntry zipArchiveEntry in zipArchive.Entries)
        {
            string temp = Path.Combine(saveFolder, zipArchiveEntry.FullName);
            zipArchiveEntry.ExtractToFile(temp);
        }
    }
}

string rootPath = AppDomain.CurrentDomain.BaseDirectory;

string zipFileName = Path.Combine(rootPath, "test.zip");

string saveFolder = rootPath;

using (var fs = new FileStream(zipFileName, FileMode.Open))

{

using (ZipArchive zipArchive = new ZipArchive(fs, ZipArchiveMode.Read))

{

foreach (ZipArchiveEntry zipArchiveEntry in zipArchive.Entries)

{

string temp = Path.Combine(saveFolder, zipArchiveEntry.FullName);

zipArchiveEntry.ExtractToFile(temp);

}

解壓縮的時候最好可以將 zipArchiveEntry.FullName 再做一層處理，避免產生資安問題 (詳情)

[Docker][Linux] SQL Server 安裝筆記

作者lex.xu
/
9 月 15, 2018
/
心得筆記

環境：Window 7

安裝 Docker Toolbox
新增 Volume

docker volume create vol-mssql

1	docker volume create vol-mssql

建立容器

docker run \
--restart=always \
--name mssql \
--mount "source=vol-mssql,target=/var/opt/mssql" \
-e "ACCEPT_EULA=Y" \
-e "SA_PASSWORD=1OCHWiY9O#RF" \
-e "MSSQL_PID=Express" \
-e "MSSQL_COLLATION=Chinese_Taiwan_Stroke_CI_AS" \
-p 1433:1433 \
-d microsoft/mssql-server-linux:latest

docker run \

--restart=always \

--name mssql \

--mount "source=vol-mssql,target=/var/opt/mssql" \

-e "ACCEPT_EULA=Y" \

-e "SA_PASSWORD=1OCHWiY9O#RF" \

-e "MSSQL_PID=Express" \

-e "MSSQL_COLLATION=Chinese_Taiwan_Stroke_CI_AS" \

-p 1433:1433 \

-d microsoft/mssql-server-linux:latest

更新時區

docker exec -ti mssql bash
apt-get update
apt-get install tzdata -y
dpkg-reconfigure tzdata

docker exec -ti mssql bash

apt-get update

apt-get install tzdata -y

dpkg-reconfigure tzdata

參考資料：

[Docker][NetCore] Win7 可以在 Docker 下 Debug ASP.NET Core 嗎？

作者lex.xu
/
9 月 02, 2018
/
心得筆記

今天心血來潮想試試看可不可以把 ASP.NET Core 部屬在 Docker 裡面 Debug，但我的測試機是 Win7 無法安裝新版 Docker for Windows 於是乎抱著僥倖下載 Docker Toolbox 來試試看
兩者最大差異在於 Docker Toolbox 是運行在 Oracle VM VirtualBox，而 Docker for Windows 則是可以在微軟的虛擬化技術 Hyper-V 底下運行

肉身測試開始

如果想要讓現有專案支援 Docker 的話只要對 專案右鍵 -> 加入 -> Docker Support 強大的 Visual Studio 2017 就會搞定基礎設定

這邊的話要選擇 Linux 上面的 Windows 是給 Docker for Windows 選的
接著 Visual Studio 就會幫你下載與設定一些基本環境

接著直接按 F5 執行偵錯模式
噹噹… 他竟然說我沒有安裝 dotnet sdk

-------------------------------------------------------------------
You may only use the Microsoft .NET Core Debugger (vsdbg) with
Visual Studio Code, Visual Studio or Visual Studio for Mac software
to help you develop and test your applications.
-------------------------------------------------------------------
Did you mean to run dotnet SDK commands? Please install dotnet SDK from:
http://go.microsoft.com/fwlink/?LinkID=798306&clcid=0x409
The target process exited without raising a CoreCLR started event. Ensure that the target process is configured to use .NET Core. This may be expected if the target process did not run on .NET Core.
The program '[24] dotnet' has exited with code 145 (0x91).
'' 程式以返回碼 145 (0x91) 結束。

-------------------------------------------------------------------

You may only use the Microsoft .NET Core Debugger (vsdbg) with

Visual Studio Code, Visual Studio or Visual Studio for Mac software

to help you develop and test your applications.

-------------------------------------------------------------------

Did you mean to run dotnet SDK commands? Please install dotnet SDK from:

http://go.microsoft.com/fwlink/?LinkID=798306&clcid=0x409

The target process exited without raising a CoreCLR started event. Ensure that the target process is configured to use .NET Core. This may be expected if the target process did not run on .NET Core.

The program '[24] dotnet' has exited with code 145 (0x91).

'' 程式以返回碼 145 (0x91) 結束。

翻了很久才翻到一篇文章 Docker debugging for ASP.NET Core application is not working 國外也有個勇者想在 Win7 利用 Docker 來 Debug ASP.NET Core

最後被加上了 Closed - Won't Fix 的 Tag 看來這個問題應該是不會修正了

結論

想要在 Docker 上玩 ASP.NET Core 還是乖乖用 Win10 吧
哪天有空了來試試看在 Win10 安裝 Docker 玩玩看
不知道會不會又有其他地雷引爆