近日處理我們公司某個使用 my sql 的專案，已經被雷的幾次，紀錄一下

1.  使用 EF Scaffolding 指令去更新 Model ，有些欄位原本是明確型別 變成 可null型別
     也就是本來是 datetime 變成 datetime?, int 變成 int?
     看起來是一些 view 的欄位會有這些狀況，這時就要去 git revert ，避免build專案又一堆錯誤
2.  使用 IQueryable 語法處理時間要小心，不要在裡面計算時間。
     

DateTime shouldBeGiveCoinDateTime = DateTime.Now.AddDays(-14);

return dbContext.ViewOrderForCoinCronJob.AsNoTracking().Where(x =>

    x.CompletedAt <= shouldBeGiveCoinDateTime

    && x.CreatedAt >= DateTime.Now.AddYears(-1) // -> 這裡轉換mysql指令會出錯

    && x.DeletedAt == null

    && x.CoinAmount > 0

    && x.Status == (byte)ORDER_STATUS.COMPLETED

    && dbContext.PackingMain.Any(y => y.OrderId == x.Id)

    && x.IsGiveCoin != 0

    && !dbContext.CoinLog.Any(y =>

        y.OrderId == x.Id

        && y.StrKey.Contains(COIN_LOG.STRING_KEY_PREFIX.ORDER_ACCUMULATION)

    )

).ToList();

要把時間計算先算出來，再帶入式子

DateTime shouldBeGiveCoinDateTime = DateTime.Now.AddDays(-14);

DateTime createDateStartAt = DateTime.Now.AddYears(-1); // -> 先算出日期

return dbContext.ViewOrderForCoinCronJob.AsNoTracking().Where(x =>

    x.CompletedAt <= shouldBeGiveCoinDateTime

    && x.CreatedAt >= createDateStartAt // -> 帶入算好的日期

    && x.DeletedAt == null

    && x.CoinAmount > 0

    && x.Status == (byte)ORDER_STATUS.COMPLETED

    && dbContext.PackingMain.Any(y => y.OrderId == x.Id)

    && x.IsGiveCoin != 0

    && !dbContext.CoinLog.Any(y =>

        y.OrderId == x.Id

        && y.StrKey.Contains(COIN_LOG.STRING_KEY_PREFIX.ORDER_ACCUMULATION)

    )

).ToList();

 

用以下指令可以對 Ms-SQL Server 查出所有正在執行的 SQL 指令，並且依 執行總時間 反向排序

SELECT      r.scheduler_id as 排程器識別碼,

            status         as 要求的狀態,

            r.session_id   as SPID,

            r.blocking_session_id as BlkBy,

            substring(

                ltrim(q.text),

                r.statement_start_offset/2+1,

                (CASE

                 WHEN r.statement_end_offset = -1

                 THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2

                 ELSE r.statement_end_offset

                 END - r.statement_start_offset)/2)

                 AS [正在執行的 T-SQL 命令],

            r.cpu_time      as [CPU Time(ms)],

            r.start_time    as [開始時間],

            r.total_elapsed_time as [執行總時間],

            r.reads              as [讀取數],

            r.writes             as [寫入數],

            r.logical_reads      as [邏輯讀取數],

            -- q.text, /* 完整的 T-SQL 指令碼 */

            d.name               as [資料庫名稱]

FROM        sys.dm_exec_requests r 

            CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q

            LEFT JOIN sys.databases d ON (r.database_id=d.database_id)

WHERE       r.session_id <> @@SPID

ORDER BY    r.total_elapsed_time desc

由於匯入91訂單，如果是一筆一筆抓，會耗費比較久的時間
下列寫法是，開兩條 thread 平行處理，可以節省約一半時間

public ConcurrentBag<V2SalesOrderGetDataList> ConcurencyGetApp91OrderDetails(List<string> TMCodes, int shopId, ref ConcurrentBag<string> orderErrMsgs)

        {

            #region 平行查詢

            ConcurrentBag<V2SalesOrderGetDataList> result = new ConcurrentBag<V2SalesOrderGetDataList>();

            ConcurrentBag<string> errs = new ConcurrentBag<string>();

            ConcurrentQueue<string> TMCodeQueues = new ConcurrentQueue<string>();

            TMCodes.ForEach(x => TMCodeQueues.Enqueue(x));



            Action searchOrderDetail = () =>

            {

                if (!TMCodeQueues.IsEmpty)

                {

                    string TMCode = string.Empty;



                    while (TMCodeQueues.TryDequeue(out TMCode))

                    {

                        V2SalesOrderGetReqModel reqModel = new V2SalesOrderGetReqModel()

                        {

                            ShopId = shopId,

                            TGCode = null,

                            TMCode = TMCode,

                            TSCode = null,

                        };



                        try

                        {

                            V2SalesOrderGet91API req = new V2SalesOrderGet91API();

                            var resp = req.Execute(reqModel, shopId);



                            if (resp.Status == "Success")

                            {

                                foreach (var l in resp.Data.List)

                                {

                                    result.Add(l);

                                }

                            }

                            else

                            {

                                //主單編號 , 錯誤原因

                                errs.Add(TMCode + " , " + resp.ErrorMessage);

                            }

                        }

                        catch (Exception ex)

                        {

                            errs.Add(TMCode + " , " + ex.Message);

                        }

                    }

                }

            };



            //指派Thread

            Parallel.Invoke(searchOrderDetail, searchOrderDetail);

            #endregion



            //查詢失敗

            orderErrMsgs = errs;



            return result;

        }

這個指令還是會把所有的資料 Select 出來，再更新

原指令:

UPDATE Job Set En_Status = 200 Where En_Status = 100 and LastTouchAt < '2023-05-06 12:34:56'
其中  '2023-05-06 12:34:56' 是 DateTime.Now.AddMinutes(-2) 的結果(Web Server 端的時間扣 2 分鐘)

但，若是改使用 UpdateFromQueryAsync 如下:
var c = await Ds.NewContext.GvContext.Jobs.Where(j => j.En_Status == Cst.Job.Status.Running && j.LastTouchAt < DateTime.Now.AddMinutes(-2))
                .UpdateFromQueryAsync(j => new Ds.Gv.Job { En_Status = Cst.Job.Status.ReStarting });

產生的 SQL 如下:
UPDATE A 
SET A.[En_Status] = @zzz_BatchUpdate_0
FROM [Job] AS A
INNER JOIN ( SELECT [j].[Id], [j].[CancelledAt], [j].[CancelledBy], [j].[En_Status], [j].[EndAt], [j].[Exception], [j].[Filename], [j].[InformationJson], [j].[InitAt], [j].[Is_CheckOnly], [j].[LastTouchAt], [j].[LastTouchMessage], [j].[LoopStartAt], [j].[Name], [j].[ScheduleId], [j].[TotalTouch], [j].[TouchCount]
FROM [Job] AS [j]
WHERE [j].[En_Status] = 100 AND [j].[LastTouchAt] < DATEADD(minute, CAST(-2.0E0 AS int), GETDATE())
           ) AS B ON A.[Id] = B.[Id]

有兩個要注意的地方:
1. 它會先 Select 全欄位，再做更新

2. 它的時間是 DB Server 的現在時間。不是 Web Server 端的時間。


順便記錄一下。若是要執行 Update xx Ser cc = cc + 1 Where ...

EF 可寫為:
var c = await Ds.NewContext.GvContext.Jobs.Where(j => j.En_Status == Cst.Job.Status.Running && j.LastTouchAt < DateTime.Now.AddMinutes(-2))
                .UpdateFromQueryAsync(j => new Ds.Gv.Job { TotalTouch = j.TotalTouch + 1 });

轉換的 SQL 為:
UPDATE A 
SET A.[TotalTouch] = B.[TotalTouch] + 1
FROM [Job] AS A
INNER JOIN ( SELECT [j].[Id], [j].[CancelledAt], [j].[CancelledBy], [j].[En_Status], [j].[EndAt], [j].[Exception], [j].[Filename], [j].[InformationJson], [j].[InitAt], [j].[Is_CheckOnly], [j].[LastTouchAt], [j].[LastTouchMessage], [j].[LoopStartAt], [j].[Name], [j].[ScheduleId], [j].[TotalTouch], [j].[TouchCount]
FROM [Job] AS [j]
WHERE [j].[En_Status] = 100 AND [j].[LastTouchAt] < DATEADD(minute, CAST(-2.0E0 AS int), GETDATE())
           ) AS B ON A.[Id] = B.[Id]

比較方式: 對相同的資料表讀取 1000 次，來比較速度。
資料庫為 MS SQL

這裡做了 4 個 Case:

Case 1: 
使用 Entity Framework，DbContext 為每次 new 一個。

Case 2:
使用 Entity Framework，使用同一個 DbContext。

Case 3:
用 DAO，轉為 Data Table 的格式 (使用 DtFromSql)。

Case 4:
使用 Dapper。

專案啟動時的記憶體使用量約為:  123 MB



測試一:
第一次讀取
Case 1, 時間: 3673.0303 ms, 記憶體: 566 MB

Case 2, 時間:  3889.9379 ms, 記憶體: 304 MB  (這個時間有時會測試到 3000 ms)

Case 3, 時間:  1665.7748 ms, 記憶體: 205 MB

Case 4, 時間:  1639.7211 ms, 記憶體: 193 MB

讀取五次
Case 1, 時間: 1984.6395, 記憶體: 544MB (有發生 GC)


Case 2, 時間:  2367.9792 ms, 記憶體: 627 MB (沒有發生 GC)


Case 3, 時間:  1395.1132 ms, 記憶體: 280 MB (有發生 GC)



Case 4, 時間:  1531.3555 ms, 記憶體: 271 MB


結果: Dapper 看來完勝，速度上和直接使用 DAO 轉進 Data Table 的速度相近。但 Dapper 傳回的結果已經轉換為物件 List， 操作上會更為方便。

Entity Framework 一如預期的較慢，且花費較多的記憶體。但和想像中最大的差異是，使用同一個 DbContext 比 new 1000 個 DbContext  的速度還要慢， 花費的記憶體在 GC 之後，差異不大。

所以不一定要用 DI 注入 DbContext, 在各 function 中一直傳遞 DbContext. 在 Static 的 function 中，可以安心的 new 一個 DbContext 出來使用吧，只是要記得用 using 哦，否則可能會有 connection 沒有關閉的問題。


 

這裡是我測試 Gmail API 和 Google API 憑証的一些記錄。

如果你的目的是要使用 Gmail Api 取代舊的 Gmail SMTP 來發送通知信，建議你先跳到最下方看一下結論。

如果你是想要看一下 Gmail API 和 Google API 憑証的使用方法，可以看一下這篇文章。

1. 在 google cloud platform 建立新的專案.
https://console.cloud.google.com/

 
 



啟用 Gmail API







 



 


 
 



因為我們要透過 OAuth  取得使用者授權，所以要設定使用 OAuth 的同意畫面。

 
指定授權的範圍
 

 
因為剛建立的專案，不會被公開，所以要指定測試使用者
 
如果要給任意使用者，必需經過發布的流程，但準備工作有點麻煩，所以這次就不發布了。
 




建立 OAuth 2.0 用戶端 ID 憑証
 
這裡除了名稱外，還有一個設定重導 Uri 的項目。現在不填寫，但稍後要回來補這個資料。
 
 
 下載 json 之後，命名為 client_secret.json 保留後續使用。


再來就要建立專案了. 用 VS2022 建立一個新專案
  


 




記錄網址, 本測試專案是 https://localhost:44340/ ，請依實際網址為準。



回到 OAuth 2.0 用戶端 ID 的設定頁. 在已授權的重新導向 URI 中填入 https://localhost:44340/Home/AuthReturn (填入的網址依實際專案的狀況，可能會有變化)
 
 

在 VS2022 中，使用 Nuget 安裝套件: (有漏的再麻煩和我說)
Google.Apis.Gmail.v1
Google.Apis.Auth
MimeKit (發送 gmail 時使用)

建立認証用的網址:
建立一個 Action, 用來取得認証用的網址:

        /// <summary>

        /// 取得授權的項目

        /// </summary>

        static string[] Scopes = { GmailService.Scope.GmailSend };



        // 和登入 google 的帳號無關

        // 任意值，若未來有使用者認証，可使用使用者編號或登入帳號。

        string Username = "ABC";



        /// <summary>

        /// 存放 client_secret 和 credential 的地方

        /// </summary>

        string SecretPath = @"D:\project\GmailTest\Data\Secrets";



        /// <summary>

        /// 認証完成後回傳的網址, 必需和 OAuth 2.0 Client Id 中填寫的 "已授權的重新導向 URI" 相同。

        /// </summary>

        string RedirectUri = $"https://localhost:44340/Home/AuthReturn";



        /// <summary>

        /// 取得認証用的網址

        /// </summary>

        /// <returns></returns>

        public async Task<string> GetAuthUrl()

        {

            using (var stream = new FileStream(Path.Combine(SecretPath, "client_secret.json"), FileMode.Open, FileAccess.Read))

            {

                FileDataStore dataStore = null;

                var credentialRoot = Path.Combine(SecretPath, "Credentials");

                if (!Directory.Exists(credentialRoot))

                {

                    Directory.CreateDirectory(credentialRoot);

                }



                //存放 credential 的地方，每個 username 會建立一個目錄。

                string filePath = Path.Combine(credentialRoot, Username);

                dataStore = new FileDataStore(filePath);



                IAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow(new GoogleAuthorizationCodeFlow.Initializer

                {

                    ClientSecrets = GoogleClientSecrets.Load(stream).Secrets,

                    Scopes = Scopes,

                    DataStore = dataStore

                });



                var authResult = await new AuthorizationCodeWebApp(flow, RedirectUri, Username)

                .AuthorizeAsync(Username, CancellationToken.None);



                return authResult.RedirectUri;

            }

        }

執行結果:
 
用 chrome 開啟產生的網址: 


選取任一帳號，如果出現以下錯誤，請回到 "OAuth 同意畫面" 去新增測試使用者


 
因為應用程式尚未發布，所以會看到警告，勇敢的繼續下去


這裡會要求授權使用你的名義發送信件。(這是在程式中取得授權的項目 Scopes 中所指定的)


再繼續之後，會被重導至我們在 redirectUri  指定的網址。因為我們尚未完成，所以會看到錯誤，順便也可以看一下，會帶回哪一些參數。有 state, code, scope，共三個。


順便看一下，google 的套件會在 Credentials 的目錄下幫使用者建立一個目錄，在完成驗証前，會先放一個 System.String-oauth_XXX 的檔案，裡面的值和回傳的 state 是一樣的，這個應該是用來驗証回傳資料的。



接下來我們要新增 Action "AuthReturn" 如下:

        public async Task<string> AuthReturn(AuthorizationCodeResponseUrl authorizationCode)

        {

            string[] scopes = new[] { GmailService.Scope.GmailSend };



            using (var stream = new FileStream(Path.Combine(SecretPath, "client_secret.json"), FileMode.Open, FileAccess.Read))

            {

                //確認 credential 的目錄已建立.

                var credentialRoot = Path.Combine(SecretPath, "Credentials");

                if (!Directory.Exists(credentialRoot))

                {

                    Directory.CreateDirectory(credentialRoot);

                }



                //暫存憑証用目錄

                string tempPath = Path.Combine(credentialRoot, authorizationCode.State);            



                IAuthorizationCodeFlow flow = new GoogleAuthorizationCodeFlow(

                new GoogleAuthorizationCodeFlow.Initializer

                {

                    ClientSecrets = GoogleClientSecrets.Load(stream).Secrets,

                    Scopes = scopes,

                    DataStore = new FileDataStore(tempPath)

                });



                //這個動作應該是要把 code 換成 token

                await flow.ExchangeCodeForTokenAsync(Username, authorizationCode.Code, RedirectUri, CancellationToken.None).ConfigureAwait(false);



                if (!string.IsNullOrWhiteSpace(authorizationCode.State))

                {                

                    string newPath = Path.Combine(credentialRoot, Username);

                    if (tempPath.ToLower() != newPath.ToLower())

                    {

                        if (Directory.Exists(newPath))

                            Directory.Delete(newPath, true);



                        Directory.Move(tempPath, newPath);

                    }

                }



                return "OK";

            }

        }

再跑一次上面的流程，最後回到 AuthReturn

在 D:\project\GmailTest\Data\Secrets\Credentials\ABC 裡面會產生一個檔案: 這個就是我們的 token 了。


看一下裡面的內容, 有 access_token, refresh_token, scope 等等, 用途應該很好猜了.. 不知道各項目的目途也沒有關係。只要有這個 token 就可以了。


refresh_token 的效期請參考以下文件:
https://developers.google.com/identity/protocols/oauth2 。也可以參考下圖, 若是要用 gmail api 來發送通知信(例如連絡我們)，紅色的地方是比較令人困擾的，例如 6 個月以上，沒有人留言，原來留下的 refresh_token 就失效了。使用者必需重新建立一個 refresh_token 。

 
最後來使用 gmail api 發送通知信, 直接看程式碼如下: 在這個過程中遇到最大的問題除了憑証問題之外，另一個問題是編碼。直到最後找到可以用 MimeKit 把 System.Net.Mail.MailMessage 編碼成 Gmail API 的格式才解決。程式碼如下:

        public async Task<bool> SendTestMail()

        {

            var service = await GetGmailService();



            GmailMessage message = new GmailMessage();

            message.Subject = "標題";

            message.Body = $"<h1>內容</h1>";

            message.FromAddress = "bikehsu@gmail.com";

            message.IsHtml = true;

            message.ToRecipients = "bikehsu@gmail.com";

            message.Attachments = new List<Attachment>();



            string filePath = @"C:\Users\bike\Pictures\Vegetable_pumpkin.jpg";    //要附加的檔案

            Attachment attachment1 = new Attachment(filePath);

            message.Attachments.Add(attachment1);



            SendEmail(message, service);

            Console.WriteLine("OK");



            return true;

        }



        async Task<GmailService> GetGmailService()

        {

            UserCredential credential = null;



            var credentialRoot = Path.Combine(SecretPath, "Credentials");

            if (!Directory.Exists(credentialRoot))

            {

                Directory.CreateDirectory(credentialRoot);

            }



            string filePath = Path.Combine(credentialRoot, Username);



            using (var stream = new FileStream(Path.Combine(SecretPath, "client_secret.json"), FileMode.Open, FileAccess.Read))

            {

                credential = await GoogleWebAuthorizationBroker.AuthorizeAsync(

                GoogleClientSecrets.Load(stream).Secrets,

                Scopes,

                Username,

                CancellationToken.None,

                new FileDataStore(filePath));

            }



            var service = new GmailService(new BaseClientService.Initializer()

            {

                HttpClientInitializer = credential,

                ApplicationName = "Send Mail",

            });



            return service;

        }





        public class GmailMessage

        {

            public string FromAddress { get; set; }

            public string ToRecipients { get; set; }



            public string Subject { get; set; }

            public string Body { get; set; }

            public bool IsHtml { get; set; }



            public List<System.Net.Mail.Attachment> Attachments { get; set; }

        }





        public static void SendEmail(GmailMessage email, GmailService service)

        {

            var mailMessage = new System.Net.Mail.MailMessage();

            mailMessage.From = new System.Net.Mail.MailAddress(email.FromAddress);

            mailMessage.To.Add(email.ToRecipients);

            mailMessage.ReplyToList.Add(email.FromAddress);

            mailMessage.Subject = email.Subject;

            mailMessage.Body = email.Body;

            mailMessage.IsBodyHtml = email.IsHtml;



            if (email.Attachments != null)

            {

                foreach (System.Net.Mail.Attachment attachment in email.Attachments)

                {

                    mailMessage.Attachments.Add(attachment);

                }

            }



            var mimeMessage = MimeKit.MimeMessage.CreateFromMailMessage(mailMessage);



            var gmailMessage = new Google.Apis.Gmail.v1.Data.Message

            {

                Raw = Encode(mimeMessage)

            };



            Google.Apis.Gmail.v1.UsersResource.MessagesResource.SendRequest request = service.Users.Messages.Send(gmailMessage, "me");



            request.Execute();

        }



        public static string Encode(MimeMessage mimeMessage)

        {

            using (MemoryStream ms = new MemoryStream())

            {

                mimeMessage.WriteTo(ms);

                return Convert.ToBase64String(ms.GetBuffer())

                    .TrimEnd('=')

                    .Replace('+', '-')

                    .Replace('/', '_');

            }

        }

收到的信件:



結論:
使用 Gmail API 最大的原因是要增加安全性，和舊的 smtp 不同的地方是，使用 gmail api 之後，客戶不需要提供 gmail 的帳號和密碼就可以讓系統使用 gmail 發送信件，不過由於 refresh_token 的效期問題，可能會造成無法發送通知信而沒有任何人發現的情況，整個實用性會變的很低。

另一個還沒有測試的部份是應用程的啟用。這個審核不知道會不很麻煩，不過可以而知的時，整個流程會花更多的時間。

取代的做法: 可能要改用 Amazon 的 SES 來寄信，而且為了避免每個小網站都要跑 SES 的建立流程，準備來寫一個 API 給各網站使用，可以發送簡單的通知信。

以上的程式碼可以在這裡下載: https://github.com/bikehsu/GmailTest

常常會有網站不小心吃了 100 % 的 CPU.

在 appliction pool 的進階設定裡面，可以限定 CPU 使用量哦。

記得設定完 CPU 上限後，要再設定"限制動作"。

KillW3wp: 讓 process 重新啟動，這個有點爆力..
Throttle: 任何時間 CPU 使用量都不要超過限制。
ThrottleUnderLoad: 當系統的 Loading 較高時再限制 CPU 的使用量。



 

以下的程式碼, 直接 alert(this.errorMessages); 會造成 chrome 卡住..
使用  setTimeout 延後 alert 可以解決這個問題. 但必需延後足夠的時間. 已知 200 ms 依然會卡住.

    errorMessages: "",



    failProcess: function (ret) {

        console.log("failProcess start: " + new Date().getSeconds() + "." + new Date().getMilliseconds());

        var json = ret.responseJSON;

        if (json && json.invalidatedPayloads) {

            var errors = json.invalidatedPayloads.filter(function F(x) {

                return x.messages.length > 0

            });



            console.log("bdfore add class: " + new Date().getSeconds() + "." + new Date().getMilliseconds());



            errors.map(function (x) {

                return $("[name='" + x.name + "']").addClass("error");

            });



            console.log("after add class: " + new Date().getSeconds() + "." + new Date().getMilliseconds());



            errorMessages = errors.map(function (x) {

                    return x.messages.join('\r\n');

            }).join('\r\n');



            console.log("afger build errorMessages: " + new Date().getSeconds() + "." + new Date().getMilliseconds());

            console.log(errorMessages);



            //alert(this.errorMessages);



            window.setTimeout(api.alertError, 500);



            console.log("after alert: " + new Date().getSeconds() + "." + new Date().getMilliseconds());

        }



        console.log("failProcess end: " + new Date().getSeconds() + "." + new Date().getMilliseconds());

    },

在 .net framework 的 Web API 專案中.

安裝某些套件時, 會在 Startup.cs 中增加一行:

app.UseWebApi(config);

這個會在 WebApiConfig 的 Register 之後執行. 造成 WebApiConfig.Register 失效.

解決的方法是把 WebApiConfig.Register 的指令全部搬到 Startup.cs 之中, 放在 app.UseWebApi(config) 之後即可...

這大概是為什麼 .Net Core 之後的設定都在 StartUp.cs 裡面的原因..

花了一些時間 : (

1. 安裝 Failover-Clustering

Install-WindowsFeature Failover-Clustering –IncludeManagementTools

2. 建立 Cluster 用的帳號, 並加入管理員群組 (記得要換密碼

net user /add clustadm Sup33P@ssw0Rd!

net localgroup administrators clustadm /add

參考以下四篇文章, 有些地方可能要改一下, 有空來做一份中文版的.


https://www.mssqltips.com/sqlservertip/4951/deploy-a-windows-server-2016-failover-cluster-without-active-directory-part-1/
https://www.mssqltips.com/sqlservertip/4962/deploy-a-windows-server-2016-failover-cluster-without-active-directory--part-2/

https://www.mssqltips.com/sqlservertip/4991/implement-a-sql-server-2016-availability-group-without-active-directory-part-1/
https://www.mssqltips.com/sqlservertip/4996/implement-a-sql-server-2016-availability-group-without-active-directory-part-2/


這個也可以參考, 有很多指令碼, 可以省不少時間:

http://woshub.com/workgroup-failover-cluster-windows-server-2016/