How to Use lumigent Log Explorer

Preface

This article describes how to use lumigent Log Explorer to analyze SQL Server transaction logs through a bug analysis and how to recover and cancel Common Database misoperations.

Bug description

The article module of the website has a scoring function:

• The user selects 1-5 points. After clicking submit, the article's rating times (stars) + 1, total score (startotal) + score, average score (staravg) = new total score/new score count
• The article page displays the average user score and the number of ratings.

In the scoring function, a user scored a maximum of 5 points and a minimum of 1 point. A day suddenly found that the average score of some articles was greater than 5, which is incredible. This problem occurs because of the following reasons:

• The page that implements the scoring logic does not verify whether the scores passed in are valid, and some users directly submit the scores they want to cheat;
• The scoring logic is correct, but the algorithm for calculating the average score is incorrect.

After examination, the scoring module not only verifies the score range, but also verifies the access source. It is impossible for users to modify the average score by submitting the scores they want. There is no problem with the average score algorithm.

This is strange. You can only view the transaction logs of SQL Server first.Lumigent Log ExplorerThe submission time of the article is, and the time for finding the problem is, so

How to Use lumigent Log Explorer

1. Open the main software interface and click "Attach Log File" on the left"

2. In the dialog box on the right, select the address of SQL Server (I have installed lumigent Log Explorer on the database server, so the selected local), 1
Figure 1

3. Click Connect and select the database of the transaction log to be viewed. Note that there are two options: use on-line log and use backup log, here we will popularize SQL Server's transaction log knowledge:

• Transaction logs faithfully record all your operations on the database, including adding, deleting tables, fields, adding, deleting, modifying, and querying data. We manage these operations in the SQL Server Enterprise Manager) you can set the transaction log backup plan in the database maintenance plan and transaction log backup tabs. For example, we set it to one hour for backup.
• If the transaction log we are viewing has not been backed up, choose use on-line log. If the backup file with the suffix TRN (which can be customized by you) has been backed up, select use backup log. If you want to view unbacked and backed up transaction logs, select
• Here I only select user on-line log

  Click attach to load the corresponding logs.

 

4. In filter log records on the left menu, 2
Figure 2

• On the timerange tab, select the above time range, 3
  Figure 3
• The vote belongs to the modification of the table, so only modifyrow, modifycolumns, 4 are selected on the activity tab.
  Figure 4
• On the table tab, select only the article table
  Figure 5

  Click Apply to view all the transaction logs that meet the conditions, 5-1
  Figure 5-1

 

Bug Analysis

After careful query, I found 3 operations on problematic articles, 6, 7, and 8.

Figure 6 shows the article rating. We can see that the total score is 5, the number of ratings is 1, and the average score is 5, which is correct;
Figure 6

Figure 7 shows the modification to the Article. We can see that the average score is changed from 5 to 0, and the number of ratings is changed from 1 to 0. What is the problem?
Figure 7

After thinking, the author just performed the modification operation on the article before the user scores the article.

• First, create an article object with the specified Article ID (actually reading the record with the specified ID from the database)->
• User rating->
• Execute the modification (actually writing the created object to the database, because no score is made when reading the data, the written data is the data before the score)

 

Now I understand that it is too "Inch" in Beijing. There is also a way to solve this problem. You only need to lock the article object with the specified ID when you modify the article, and unlock it after modification, the same is true for scoring operations;

Figure 8 shows that another user scores an article. We can see that the total score is 9, the number of ratings is 1, and the average score is 9.
Figure 8

Here, the truth is: in fact, two users scored the article, because the bug in the article caused the database to record only one.

The question is: Why is the average score returned to zero when I modify an article, the number of scores returned to zero, and the total score not returned to zero? If the total score is zero, there will be no data with an average score greater than 5 when a user votes again.

Suddenly remembered that the database structure of the article had been adjusted once, and the adjustment content was just adding the total score field (startotal), but the data persistence layer did not synchronize the data, so when setting up the article object, if the startotal field is missing, this field is not modified.

So far, the cause and solution of the problem have come out.

Use lumigent Log Explorer to restore (UNDO) transactions and operations

When we accidentally delete or modify data, we can use lumigent Log Explorer to restore only specified transactions.

A transaction is a series of operations on the database, such as update [DBO]. [testtable] Set [passed] = 1, which indicates the table [DBO]. [passed] fields of all records in [testtable] are assigned values. What we see in lumigent Log Explorer is a series of operations with the same transid (5-1 ), right-click any of these operations and select undo transaction to cancel (Restore) the transaction );

Of course, we can also undo (Restore) a transaction operation, which is very simple, just select Undo operation according to the above steps.

After undo transaction or undo operatio is selected, lumigent Log Explorer generates a standard SQL statement document, and executes the content of this document in the query analyzer to undo (Restore) operations in a transaction or transaction.