Solve the problem that some month performance can't be displayed: timestamp\union al\autocommit, etc. usage

Performance department colleagues reflect that some of the staff performance in the query can not be displayed, and gave two work number. All of the two work numbers correspond to the normal approval of performance, but the performance management module cannot be displayed. The performance management uses the paging query, the query statement is:

<!--  Performance Query collection   --><select id= "Querylist" resultmap= "Performanceresultmap"    parametertype= "Com.fx.oa.module.per.performance.api.shared.domain.PerformanceEntity" >select *  from  (Selectperformance.id,f_nd,f_khqj,f_staffname,f_staffid,f_bm,f_zw,f_sqrq,f_zf3,sum (f_zpf1+f_ ZPF2+F_ZPF3+F_ZPF4+F_ZPF5+F_ZPF9+F_ZPF10+F_ZPF11+F_ZPF12+F_ZPF13+F_ZPF17+F_ZPF18+F_ZPF19+F_ZPF20+F_ZPF21) f_ Dqjxfs,sum (F_DF1+F_DF2+F_DF3+F_DF4+F_DF5+F_DF9+F_DF10+F_DF11+F_DF12+F_DF13+F_DF17+F_DF18+F_DF19+F_DF20+F_DF21) f_syfszh,f_txrzjzgname,f_jxzzdf,f_jxdj,f_jxxs,f_zf1,f_zf2,releasedate,zjzg,activityname,        processExecutionId,      dqspr,       userNameandTaskid ,       sortno,  ' normal ' jxlx   from  (select <include refid= "Performanceall_flow_column"/><include refid= " Jx_tabale "/><include reFid= "Glflow"/><include refid= "Select_where"/><include refid= "sql_group_by"/>)   Performance<include refid= "Out_select_where"/> group by id union allselect  Performance.* from  (select  ' 1 ' as id,f_nd,f_khqj,f_staffname,f_staffid,f_bm,f_zw,f_ Sqrq,f_zf3,f_dqjxfs,f_syfszh,f_txrzjzgname,f_jxzzdf,f_jxdj,f_jxxs,f_zf1,f_zf2,releasedate,zjzg,activityname, Processexecutionid,dqspr,usernameandtaskid,sortno,jxlxfromt_per_his_performance performance) Performance  LEFT JOIN tree_node tree ON Performance.f_staffid = tree.id          <include refid= "Out_select_where"/>        <if test= "isadmin !="  and isAdmin  !=  Null "><! [cdata[ and tree.leftid >=  (Select leftid from tree_node treenode  where Treenode.id= #{currentuserstaffid})  and  tree.rightId <=  (Select rightid  from tree_node treenode where treenode.id=#{currentuserstaffid})]]>     </if>         )  performance where   performance.id !=0  <include refid= "Sql_order_by"/></select>

The statement contains a union that is primarily used to query historical performance (historical performance is the same as the performance table structure, but is primarily used to store the performance data that was written on Moss before OA was launched for employee self-enquiry).

The following SQL statements are intercepted:

 select Performance.id from  (Select performance.id,f_nd,f_khqj,f_staffname,f_staffid, F_bm,f_zw1 as f_zw,f_sqrq,f_zf3,f_dqjxfs,f_kpi,f_gs,ifnull (f_df1,0) f_df1,ifnull (f_df2,0) f_df2,ifnull (f_df3,0) F_df3,ifnull (f_df4,0) f_df4,ifnull (f_df5,0) f_df5,ifnull (f_df9,0) f_df9,ifnull (f_df10,0) f_df10,ifnull (f_ df11,0) F_df11,ifnull (f_df12,0) f_df12,ifnull (f_df13,0) f_df13,ifnull (f_df17,0) f_df17,ifnull (f_df18,0) f_df18, Ifnull (f_df19,0) f_df19,ifnull (f_df20,0) f_df20,ifnull (f_df21,0) f_df21,ifnull (f_zpf1,0) f_zpf1,ifnull (f_zpf2,0) f_ Zpf2,ifnull (f_zpf3,0) f_zpf3,ifnull (f_zpf4,0) f_zpf4,ifnull (f_zpf5,0) f_zpf5,ifnull (f_zpf9,0) f_zpf9,ifnull (f_ zpf10,0) F_zpf10,ifnull (f_zpf11,0) f_zpf11,ifnull (f_zpf12,0) f_zpf12,ifnull (f_zpf13,0) f_zpf13,ifnull (f_zpf17,0) f_ Zpf17,ifnull (f_zpf18,0) f_zpf18,ifnull (f_zpf19,0) f_zpf19,ifnull (f_zpf20,0) f_zpf20,ifnull (f_zpf21,0) f_zpf21,f_ Txrzjzgname,f_jxzzdf,f_jxdj,f_jxxs,f_zf1,f_zf2,performance.releasedate,zjzginfo.staffname as zjzg, Case when exec.status= ' 2 '  then  ' End '  when isnull (activityname) =1 then  ' performance fill '  else  Task.activityname end activityname,        exec.id as  processexecutionid,      group_concat (distinct staff.staffname)   As dqspr,      concat (' ['), Group_concat (Distinct concat (' {userName: ', ' "' , Task.createusercode, ' ', ', ', TaskID: ', ' ' ', ' task.taskid, ' ' ', '} '), ')  as userNameandTaskid ,       case       when activityname= ' Start1 '  then 1       when activityname= ' Work behavior Assessment person confirmed '  then 2        when activityname= ' re-fill '  then 3        when activityname= ' Fill in self-evaluation '        then 4  when activiTyname= ' Assessment of people '        then 5 else 6 end sortno, ' normal '  as jxlx from t_dymc_20150211170947  performance left join tree_ Node tree on performance.f_staffid=tree.idleft join t_bpm_process_execution exec  on  performance.id=exec.pkvalue left join t_bpm_process_define process  on process.id=exec.processDefineIdleft join t_bpm_process_task task on  exec.id=task.processexecutionid and task.status= ' 0 ' Left join t_per_staffinfo staff  on task.createusercode=staff.staffcode and staffstatus= ' ZZ ' LEFT JOIN t_per_ Staffinfo zjzg on performance.f_staffid = zjzg.staffid    left  join t_per_staffinfo zjzginfo on zjzg.directleadercode = zjzginfo.staffid  where tree.leftid >=  (select leftid from tree_node treenode where treenode.id=  ' FX004613 ')  and  tree.rightId <=  (select rightid from tree_node  treenode where treenode.id=  ' FX004613 ') group by pkvalue )  performance  where Performance.f_staffName =  ' Yu Pan '  and  performance.id != 0

The statement did not find obvious problems on the daytime.

Compare in the database and find the following issues

1. The rule of ID (UUID) is obviously different from other;

2. ReleaseDate is empty;

   650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/85/28/wKioL1ebFgrSWXr2AAAoPYtrxr4840.png-wh_500x0-wm_3 -wmp_4-s_3026399327.png "title=" 1.png "alt=" Wkiol1ebfgrswxr2aaaopytrxr4840.png-wh_50 "/>
   

   The releasedate is used for the latest update time, as a parallel concurrency control for locks; a post that describes the sign-on concurrency control.

   ReleaseDate is timestamp, The understanding of Timestamp is 1. There is a definite range of presentation time; 2. The equivalent of a trigger, which takes the current time as the data changes; 3.mysql allows for multiple timestamp properties, such as multiple only the first one can update the current time normally;

See:mysql timestamp (timestamp) usage

View the statement that created the table, preserving only the creation of the ReleaseDate

Show CREATE TABLE t_dymc_20150211170947. ' releasedate ' timestamp null DEFAULT null on UPDATE current_timestamp,..

ReleaseDate: First, allow null, second, created by default is empty, third, each update is automatically updated to the current time;

Commit mechanism of querying database

Show engine InnoDB status like '%commit% '

All transactional commits are autocommit, and it is possible, given the possibility that the form table might be locked at the time of approval, that the transaction was not committed successfully, and that the updates to the other tables were successful, so that the code did not have accurate control of the transaction. However, if the idea is accepted, it is impossible to explain the three-time filing of the lock table.

Use the following statement to view the lock condition

Show status like '%lock% '

Look for the lock table, no problems found.

Because the above mainly for releasedate why is null and the creation of ID rules why mutation. Due to repeated confirmation of two problems, and two issues did not cause the process of approval of obvious problems. Therefore, the resolution direction becomes the main query statement.

After further confirmation, the query is a paged query, the results of the number of bars with the total number of bars shown below, always less one, the less should be the problem data, using compare to compare querylist and Querycount, found that the difference between the two four;

1. Querylist uses Union,querycount to use UnionAll when combining process data with historical data. This thought once made me waver in my understanding of the fundamental differences between the two. Actually testing is not union/unionall caused. See: Union and UNION ALL differences

2. Querylist a WHERE id! = 0 The code is written by another colleague who has left it, and it is unclear (perhaps because the ID returned is not its true ID when querying historical data, but it is unified as "0", and this code is inexplicable);

3. Querylist a more order;

4. In Querylist, there are two more characters before a return value "PXTL", and two Chinese characters are not added.

   Test each of the above, and found that only 2 of the problem no longer occurs

After testing, we found that SQL has the following laws

In SQL, when the ID is a string and the first character is a letter, the id! = number 0, when the first letter of the ID is a number, id= the number 0. It is confirmed that all the performance of the 6-7-day-6-8-day completion, the rule of the generation of the ID is obviously different from others; the first character is F, the other first character is 2, so the query is not. The id! has now been =0 take off, after the test problem solved.

One-step query ID beginning non-numeric, the discovery is from June 7 to June 8, the cause of the problem is unclear.

This article is from the "Jiangnan Miner Technology Space" blog, please be sure to keep this source http://jncumter.blog.51cto.com/812546/1831804

Troubleshoot issues that can not be displayed for some months: timestamp\union al\autocommit, etc.