SQL Execution Differences

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: sql执行不一样

| username: TiDBer_aZrsY0ca

Hello, in the Java log it shows:

[DEBUG] [┏━━━━━ Debug [yj02Gx.updateYj02] ━━━
┣ SQL:    update yj02 set yj02030='8', yj02056=?, yj02057=? where yj02052=? and yj02030='7'
┣ Parameters: [Zhang San, 2024-01-10 22:45:46.000, 134641514]
┣ Location:  com.****.framework.service.yj02.Yj02ServiceImpl.yj02Cz(Yj02ServiceImpl.java:140)
┣ Time:      56ms
┣ Update:    [4]
┗━━━━━ Debug [yj02Gx.updateYj02] ━━━
] - 2024-01-10 14:56:13.874 [pool-3-thread-1] c.d.f.core.db.beetlsql.ext.DebugExtInterceptor
┏━━━━━ Debug [yj02Gx.updateYj02] ━━━
┣ SQL:    update yj02 set yj02030='8', yj02056=?, yj02057=? where yj02052=? and yj02030='7'
┣ Parameters: [Zhang San, 2024-01-10 22:45:46.000, 134641514]
┣ Location:  com.****.framework.service.yj02.Yj02ServiceImpl.yj02Cz(Yj02ServiceImpl.java:140)
┣ Time:      56ms
┣ Update:    [4]
┗━━━━━ Debug [yj02Gx.updateYj02] ━━━

Executed 4 records.
image

One of the flags here is still the original 7. The Java code has added a transaction. How can this issue be traced? This situation has occurred several times occasionally. The Java code inspection did not find any code that changes this flag to 7.

This issue was discovered after querying the data. How can the execution situation at that time be queried?

Additionally, enabling pump and drainer, can it help to check the situation at that time?

| username: 小龙虾爱大龙虾 | Original post link

Enable database general log analysis

| username: TiDBer_aZrsY0ca | Original post link

If this log is not enabled, what should I do? This situation only occurs occasionally. Can I query if the pump is enabled?

| username: dba远航 | Original post link

Sent the wrong information by mistake.

| username: TiDBer_aZrsY0ca | Original post link

The sentence is fine, the condition changes from 7 to 8. One is in the WHERE clause, the other in the SET clause.

| username: dba远航 | Original post link

Yes, sorry, I misread it.

| username: 路在何chu | Original post link

You can turn this on online, and turn it off once the data is collected.

| username: TiDBer_aZrsY0ca | Original post link

Are there any related blogs or documents? I want to see how to do it.

| username: oceanzhang | Original post link

You can only enable the general log to check.

| username: TiDBer_aZrsY0ca | Original post link

This log is the same as the log in Java; even if it is enabled, the problem cannot be identified.

| username: TiDBer_aZrsY0ca | Original post link

Isn’t this log the same as the one recorded in Java, both being updates? Is there a difference?

| username: wangccsy | Original post link

There are somewhat some differences.

| username: 小龙虾爱大龙虾 | Original post link

Of course, there is a difference. Your Java logs are for Java, while these are database-level logs. Are there still any issues now? Is the data update incorrect after the SQL execution?

| username: oceanzhang | Original post link

May I ask if your Java expert knows how to troubleshoot?

| username: TiDBer_aZrsY0ca | Original post link

Where can I view the logs in Java? It shows that it has been executed.

| username: oceanzhang | Original post link

I still can’t figure out what this is asking.

| username: TiDBer_aZrsY0ca | Original post link

I executed this SQL to change the flag to 8, but in the end, the database still shows one record with the flag as 7. I want to know how it became 7.

| username: Kongdom | Original post link

:thinking: Do you mean that after executing the following statement, not all columns have changed to 8? Could it be that the WHERE condition is not met? For example, if column1 is 7, but column2 and column3 are 8, then the WHERE condition will not match, and thus it will not update.

UPDATE table 
SET column1 = 8, column2 = 8, column3 = 8 
WHERE column1 = 7
AND column2 = 7
AND column3 = 7
| username: TiDBer_aZrsY0ca | Original post link

No, if it were an SQL issue, it would have been resolved already.


The status of these few is all 7, and they all reported executing 4 pieces of data. I just want to know when it changed to 7. I can’t see any issues in the Java business code, so I want to check other logs. Also, I looked at the backup data from last night, and one piece of data is still 7. Without the data from that time, I don’t know what operations were performed that caused this situation. The changed column is of decimal type.

| username: 小龙虾爱大龙虾 | Original post link

So it’s not that the database executed the SQL result incorrectly, but rather you don’t know who changed it to 7?