Why does it take 5 hours for the final release and verification of the expected one-hour release?

Why does it take 5 hours for the final release and verification of the expected one-hour release?

This is a simple story of data production and import. The original story should be like this: Data Sorting --> test and verification --> production release --> production verification, and then each generation, therefore, this should have been a plain story, but it has actually become the following plot: data Sorting --> test and verification --> production release --> production verification --> Verification Failed (the expected data is not imported) --> troubleshooting --> solving the problem --> production release --> production verification --> verifying the problem (most of the data is correct, and a few of the data is incorrect) --> troubleshooting (the cause was not found at the time, but the exception was determined to be related to the original production exception data) --> Delete abnormal data SQL writing --> Test validation --> production release --> production validation --> re-import and delete some data (this time, the exception data is directly excluded, not included in the import scope) --> ask the lead for correction of some abnormal data --> modify SQL preparation --> test and verification --> production release --> modify the corresponding data import --> production validation!

Do you think it's over now? NO, how can this end of the story? Because this batch of Data imports have other services, you still need to execute this part of the business. After final confirmation, you can go back to different families, the database data is corrected, but because the program uses Redis, the exception data is still in Redis, so we also need to delete this part of the exception data in Redis. Fortunately, this part of the program has been processed, the direct deletion does not result in abnormal program functions. This release is now complete, but it is already am. This is a tragic story ......

First, we need to introduce the imported pig, a pre-written and released to the Production Storage process. In addition, the pig is in the MySql scenario. The rough code is as follows:

 1 DROP PROCEDURE IF EXISTS `usp_SadEvent`; 2 DELIMITER $$ 3 CREATE PROCEDURE `usp_SadEvent` 4 ( 5 IN identityNo VARCHAR(20), 6 IN uName VARCHAR(15), 7 IN cAmount LONG 8 ) 9 label_at_start:10 BEGIN11 12 SELECT @uid := id FROM `user`13 WHERE identity_no=identityNo AND NAME=uName;14 15 IF @uid IS NULL THEN16     select identityNo,uName,0 ret;17   LEAVE label_at_start;18 END IF;19    update account set balance=balance+cAmount where uid=@uid;20     select identityNo,uName,1 ret;21 END label_at_start$$22 DELIMITER ;

First of all, what is the fuck execution failure problem, the results of calling this stored procedure are actually returned ret = 0 failed !!

Fortunately, when I first wrote the stored procedure, I considered the results query and it was easier to find out why the returned uName is garbled? In this case, the intuition is that there is a problem with the database encoding.

The problem has been found out. How can we fix it? The normal story is, of course, to change the database's default encoding to utf8, but after the encoding is changed, Mysql must be restarted, can I restart the production environment if you want to restart it? Okay. Fortunately, the mysql Stored Procedure supports specifying encoding and modifying the stored procedure. In the uName section, specify the encoding set as utf8 (although the default Character Set of the database is latin1, however, utf8 is specified by default when the table is created. As a result, the default sequence set is not found in the production environment)

IN uName VARCHAR(15) character set utf8,

After modification, execute the SQL statement for batch calling the stored procedure, which is roughly as follows:

Call usp_SadEvent ('123', 'zhang san', 123131231313123132); # The database has call usp_SadEvent ('123', 'Li si', 3000); # The database has no Li Si, or the database name is LITH.

It is particularly noted that the first database contains the corresponding data, and the second database does not find the user data. The execution result shows the amount of the second "Li Si, it was added to Michael Jacob. What is this!

In fact, the problem lies in the default declared parameter of mysql. You only need to add a sentence below the above call statement.

Call usp_SadEvent ('123', 'zhang san', 123131231313123132); # The database has call usp_SadEvent ('123', 'Li si', 3000); # The database has no select @ uid;

The execution result clearly tells you that @ uid has a value and the value is the uid of Michael. Well, I didn't expect Mysql

SELECT @uid := id 

This implicit parameter declaration method will be valid throughout the conversation, so you can test it correctly only by changing it to the following explicit declaration.

declare u_id long;select `id` into u_id FROM `user`

The final modified stored procedure should be as follows:

DROP PROCEDURE IF EXISTS `usp_SadEvent`;DELIMITER $$CREATE PROCEDURE `usp_SadEvent`(IN identityNo VARCHAR(20),IN uName VARCHAR(15) character set utf8,IN cAmount LONG)label_at_start:BEGINdeclare u_id long;select `id` into u_id FROM `user`WHERE identity_no=identityNo AND NAME=uName;IF u_id IS NULL THEN    select identityNo,uName,0 ret;  LEAVE label_at_start;END IF;   update account set balance=balance+cAmount where uid=u_id;    select identityNo,uName,1 ret;END label_at_start$$DELIMITER ;

Ah, it seems very easy to describe the problem afterwards. It is quite difficult to troubleshoot these problems ......