Miscellaneous Tricks in Mysql Backup Correction

中文摘要：
Validate Mysql Database Backup这篇文章中，我生成了count_all_DB1.xls，count_all_DB2.xls，把备份后新旧数据库记录数不一致的表找了出来，一共38张表。Analyze Problems in Mysql DB Backup分步复现restore过程，找出了问题所在。Correct Conflicts During Mysql DB Backup保留了first_update_time最大的记录，用折中的方式解决了问题。中间各种幺蛾子肯定很多的，这篇文章就来谈一谈。幺蛾子见得多了，也就久病成良医，逢人也能说一句“我告诉你，我是身经百战见得多了”。
关键词：Reserved keywords,Messy characters,Visualizer,recource manager,RegEx, Scintilla’s definitions of words, rename, Memory bank, mstsc, Truncated incorrect DOUBLE value

1.Reserved keywords

describe table, then use the columns returned to query records. But mysql keeps reminding that there’s error near the column “usage”, the reason is that usage is one of the reserved words of mysql,

2.Messy words when selecting into

Use tee to write the returned table into a txt instead.

3.Visualizer

sqlyog, workbench, DB visualizer, Navicat are good options.

4.Which tells the real RAM occupation

When I operate on the clunky think center, it sometimes happens that the computer get stuck at certain point. I start up the task manager, it says the memory is completely occupied. Interestingly, there is another place revealing the occupation of memory, called resource monitor. Which should we believe?
In task manager, there’s a counter named Total Physical Memory reserved by an individual process, this provides us the common memory information. In resource mointor, there are a lot more simiar counter recording memery information, including committed, working set, shared, private, etc.
The Total physical memory reserved by individual processes in Task Manager and the Used Physical Memory in Resource Monitor are the same. They both show the total memory that are being consumed by all of the processes. There is a slight difference between the data of Task Manager and Resource Monitor because Task Manageralso includes the nonpaged and paged pool. ¹
Right click one process, select turn to detailed information, it will jump to the detailed information tab in task manager, and you can see for a particular process, Total physical memory reserved by individual process is slightly larger than Memorty (private) in detailed information. And Memorty (private) in detailed information is the same as Private (KB) in resource monitor.
As for counters in resource manager, they comform to this equation: working set = shared +private. Commited is about virtual memory, can’t be fitted into this equation.

5.RegEx

Very powerful tool for batch editing. It will save you a lot of time when editing many sql in similar structure. RegEx meaning wlll vary on different APPs or platforms. Examples below are from notepad++.
To know the meanings of the symbols, refer to the Notepad++.UserManual.

Function of () may be hard to understand. “(” marks the start of a region for tagging a match; so what’s inside ( ) you can use in “replace with” using \1, \2 etc. “)” marks the end of a tagged region.
Function of \n: Where n is 1 through 9 refers to the first through ninth tagged region when replacing. For example, if
the search string was Fred([1-9])XXX and the replace string was Sam\1YYY, when applied to Fred2XXX this would generate Sam2YYY.

Here’s some illustration: If you are writing some pages with one kind of script, which defines = to stand for the rank of title or heading, the more the pairs of = are, the lower of its rank is, and you wanna make all headings one “level higher”, so a H2 becomes a H1 etc. You could use “find and replace” box, activate the regex mode, type ^=(=) in the input field, replace it with \1, then let =(=)$ be replaced by \1, so the =s around the headings will reduce by one pair, that is the heading’s rank become one rank lower than it previously was, eg. heading 2 becomes heading 3, etc.
²
In notepad doc, there’s another definition requiring introspection:\<. This matches the start of a word using Scintilla’s definitions of words. How to understand Scintilla’s definitions of words? Here’s a explanation from scintilla.org :Words are contiguous sequences of characters from a particular set of characters. 4 categories define words: word, whitespace, punctuation, and line ends with each category having a role in word functions. Double clicking selects the word at that point, which may be a sequence of word, punctuation, or whitespace bytes. That is, a word is seperated by word, whitespace, punctuation, or line ends. You should tell the difference between word ends and line ends when using regex.

6.Batched renaming

There are surely times when you need to rename a bunch of files, which is the most tedious task if you rename one by one. Here you can use cmd command rename to give your files new names, just remember pick the right encoding in notepad++ (ANSI), or there will be messy characters spreading the cmd screen. Please refer to the MS DOS doc for precise usage.

7.Memory bank collapse

I said that the old server clunks when in service, and it was out of order in the morning at 20171123, when I pressed the start button, it bursted into a sharp alarm sound. I had to turn myself to a hardware engineer to fix the problem.
First free the charges on your body by touching something metal. Mainboard has different alarms corresponding with different situations, and different kinds of CMOS produced by various manufacturers have different alarm modes, AMIBIOS Beep Code Troubleshooting is what we need because our machine’s CMOS is ami aptio.
The alarm (1 Long Beep + 3 Short Beeps) tells us the RAM crashed. The regular method is to change the memory bank, but when I pulled the memory bank out of the sink, and insert them back several times, then the computer is running again. Don’t know why. The hell of it, as long as it runs again!

8.Copying files onto remote desktop

This technique is simple, in which mstsc.exe plays a crucial role, I take advantage of it just to simplify the copying of files between two computers.
There are a lot of teaching materials on the internet. Change settings in the controlling panel, set up an account, if you can’t copy files onto the target machine, run the task manager on the target machine, close rdpclip (it is used to control file copying between computers), then start it up again.

9.Truncated incorrect DOUBLE value

When I executed the following codes:

# borrow_list_wz***
tee e:/SelectDupliLog/SelectDupliLog_borrow_list_wz***.txt;
select * from
(
(
(select * from DB1.borrow_list_wz*** where loan_id = 756129 order by first_update_time desc limit 1) union all
(select * from DB1.borrow_list_wz*** where loan_id = 756132 order by first_update_time desc limit 1) union all
(select * from DB1.borrow_list_wz*** where loan_id = 756133 order by first_update_time desc limit 1) union all
...
(select * from DB1.borrow_list_wz*** where loan_id = 756377 order by first_update_time desc limit 1) union all
(select * from DB1.borrow_list_wz*** where loan_id = 776634 order by first_update_time desc limit 1) 
) as SelectDupli_borrow_list_wz***
)
into outfile 'e:/SelectDupli_borrow_list_wz***.txt';
notee;

There are 65535 warnings freaking me out, it even excesses the upper limit of max_error_count. The warnings are all Truncated incorrect DOUBLE value: ‘a1743’ and alike. Stack Overflow³ tells me that “This message means you’re trying to compare a number and a string in a WHERE or ON clause”, まさか …… the loan_id 756129 is defined as a string when the wz*** table was created? Probably yes. Because If we change 756129 in SelectDupli.sql script to like ‘% 756129 ’, the warnings then goes away.

Reference

---

1. https://answers.microsoft.com/en-us/windows/forum/windows_10-performance/what-is-the-difference-between-total-physical/e39e339a-8ddb-4cc9-a8d5-7667a34b2c8b ↩︎

2. Notepad++.UserManual ↩︎

3. https://stackoverflow.com/questions/16068993/error-code-1292-truncated-incorrect-double-value-mysql ↩︎