Typical bug Analysis of Oracle character garbled and data cross-border interview
Preface: As party B, we found two strange bugs in the acceptance stage of party A's customers.
The following is the source of the problem, the root cause of the problem, the solution, how to avoid specific descriptive narrative.
First, bug1:oracle read and write characters garbled.
1. Source of the problemOracle database Listener http://blog.csdn.net/laoyang360/article/details/46524519 need to get the most recently inserted Chinese class characters. Classification by decision. One-step debugging found that each received is garbled.
2. Root cause of problem
Inconsistent encoding format is the result.
3, the way to solveThe first step: View the encoding format of Oracle itself, with the ability to commandSelect Userenv (' language ') from dual; see later. Many common Oracle companies are now using simplified Chinese_china. ZHS16GBK encoded format. The second step: Check the encoding format used by the daemon, the background program reads and writes the Orcle database through OTL.
The environment variable setting putenv () is used to write the
nls_lang=simplified Chinese_china. Al32utf8, also known as the UTF-8 encoding format.
The third step: because the Oracle encoding format has been fixed, only can change the background program to write environment variables in the format of implified Chinese_china. ZHS16GBK encoded format. Fourth Step: Review the encoding format used by the compilation tool VS2010. We know: VC6.0 default ANSI encoding. Instead, the default Unicode encoding is VS2010. Unicode encoding and GBK encoding format are inconsistent and need to be converted . Fifth step: Check for a very long time, finally through the conversion interface UTF82GBK () and Gbk2utf8 () finished format conversion.
that is to say: Read the data from the database, you need to call Gbk2utf8 () to format conversion talent in VS2010 under normal display. Similarly, writing data to the database requires a call to the UTF82GBK () interface for conversion, and talent ensures that writing to Oracle does not show garbled characters. Moreover, the foreground program unified for UTF8 code, unified conversion talent to ensure that no garbled.
As for the relationship between Utf8 and Unicode (in a word, UTF8 is an encoding method for encoding Unicode character sets.) See the discussion: http://www.zhihu.com/question/23374078
4. How to avoid1) The earliest design time, set the database encoding format, the owner of each module in accordance with the unified format for processing. 2) Different compiler causes different encoding format, you need to unify the interface for conversion. The implementation interface can be seen for example: http://blog.csdn.net/p569354158/article/details/6567175
Second, BUG2: overflow, data out of bounds.
1. Source of the problemThe program runs 500 of tasks frequently. The page cannot display the returned data. From the data displayed on the interface, the serial number is 67138,67139. 63140 is not displayed. But the small serial number 69,70 can display the corresponding return data normally.
2. Root cause of problemData cross-border interview. A 2-byte unsigned short storage (maximum range: 0-2 of 16 power-1, or 0-65535) may be taken somewhere in the program. The question is how it was discovered: the first step: to extrapolate data through the numbers 67138, for reasons: 67138>65535.
and 69. 70 less than 65535 are displayed as normal.
The second step: Database storage number and program log number one by one contrast. See: Oracle corresponding database table first inbound ordinal number is 67138 and so on is greater than 65535, but the second time the program returns the inserted result table becomes a 1618 ordinal value, and the sequence number is not pointid, but TaskID or SystemID, That is, a preliminary assertion that a cross-border interview has occurred. Step Three:study why this number needs to be unique in the database.？ and the relevant design staff phone confirmation. Since the late program requirements deviate from the earliest designs, it seems to me that there is absolutely no need to use unique values.
Just make sure that each task is issued with the only one available. Because the unique task number is indexed by TaskID in our program, the node number of Pointid is a level two index.
The program uses the query table as well. A level two index after the first index.
In the database, the sequence of the sequence is used to increment the unique value, and the maximum value is set very large999999999999999999999999999。 and two-table trigger trigger call this sequence. Also said that the frequent task of the program issued tasks, frequently run, weeks or months very easy to more than 65535 of the ordinal value.
In conclusion, the cross-border interview in the Basic judgment procedure causes the display exception.
3, the way to solve1) The situation is anxious: take the simple evasion scheme: Update the sequence sequence current value. The current value has been checked by 67000. Need to return to 1 and start again.
But there is a knowledge that, under Sqldevelop and all the viewing tools, this value cannot be changed manually. Need to be removed after rebuilding.
user must have alter any SEQUENCE ability change SEQUENCE, can alter except start all SEQUENCE, False imagine change start value, must drop SEQUENCE again re-create.
2) Where in the future you need to locate the cross-border access, change the type to at least unsigned int storage.
4. How to avoidDefine the variable of the ordinal type, to consider its source, how to produce the minimum, the maximum value. Then determine which type of variable is defined.
Summary:1. Two bugs are not difficult, but they all need to clarify the idea. Avoid detours.
The first bug is combed to 3.5h, and the second bug is time consuming 5h.
2. The second bug is very weird, and when I run out of 500 tasks, I suddenly don't show the return data. The program basically does not change whatever logic. Thought for a very long time, excluding the effects of non-transmission devices, non-search programs. Finally positioning and 65535 related.
2015-12-13 pm13:55 at home in front of the bed
Ming Yi World
Reprint please indicate source, original address: http://blog.csdn.net/laoyang360/article/details/50282569
Assuming that this article is helpful to you, please click on the ' top ' support, your support is I insist on writing the biggest motivation, thank you!
Typical bug analysis of Oracle character garbled and data cross-border interview
