![(please configure the [header_logo] section in trac.ini)](http://www1.pconline.com.cn/hr/2009/global/images/logo.gif){kind=logo}
Trac Ticket Queries
Table of Contents
In addition to reports, Trac provides support for custom ticket queries, used to display lists of tickets meeting a specified set of criteria.
To configure and execute a custom query, switch to the View Tickets module from the navigation bar, and select the Custom Query link.
Filters
When you first go to the query page the default filter will display tickets relevant to you:
- If logged in then all open tickets it will display open tickets assigned to you.
- If not logged in but you have specified a name or email address in the preferences then it will display all open tickets where your email (or name if email not defined) is in the CC list.
- If not logged and no name/email defined in the preferences then all open issues are displayed.
Current filters can be removed by clicking the button to the left with the minus sign on the label. New filters are added from the pulldown lists at the bottom corners of the filters box ('And' conditions on the left, 'Or' conditions on the right). Filters with either a text box or a pulldown menu of options can be added multiple times to perform an or of the criteria.
You can use the fields just below the filters box to group the results based on a field, or display the full description for each ticket.
Once you've edited your filters click the Update button to refresh your results.
Navigating Tickets
Clicking on one of the query results will take you to that ticket. You can navigate through the results by clicking the Next Ticket or Previous Ticket links just below the main menu bar, or click the Back to Query link to return to the query page.
You can safely edit any of the tickets and continue to navigate through the results using the Next/Previous/Back to Query links after saving your results. When you return to the query any tickets which were edited will be displayed with italicized text. If one of the tickets was edited such that it no longer matches the query criteria the text will also be greyed. Lastly, if a new ticket matching the query criteria has been created, it will be shown in bold.
The query results can be refreshed and cleared of these status indicators by clicking the Update button again.
Saving Queries
Trac allows you to save the query as a named query accessible from the reports module. To save a query ensure that you have Updated the view and then click the Save query button displayed beneath the results. You can also save references to queries in Wiki content, as described below.
Note: one way to easily build queries like the ones below, you can build and test the queries in the Custom report module and when ready - click Save query. This will build the query string for you. All you need to do is remove the extra line breaks.
Using TracLinks
You may want to save some queries so that you can come back to them later. You can do this by making a link to the query from any Wiki page.
[query:status=new|assigned|reopened&version=1.0 Active tickets against 1.0]
Which is displayed as:
This uses a very simple query language to specify the criteria (see Query Language).
Alternatively, you can copy the query string of a query and paste that into the Wiki link, including the leading ? character:
[query:?status=new&status=assigned&status=reopened&group=owner Assigned tickets by owner]
Which is displayed as:
Using the [[TicketQuery]] Macro
The TicketQuery macro lets you display lists of tickets matching certain criteria anywhere you can use WikiFormatting.
Example:
[[TicketQuery(version=0.6|0.7&resolution=duplicate)]]
This is displayed as:
No results
Just like the query: wiki links, the parameter of this macro expects a query string formatted according to the rules of the simple ticket query language.
A more compact representation without the ticket summaries is also available:
[[TicketQuery(version=0.6|0.7&resolution=duplicate, compact)]]
This is displayed as:
No results
Finally, if you wish to receive only the number of defects that match the query, use the count parameter.
[[TicketQuery(version=0.6|0.7&resolution=duplicate, count)]]
This is displayed as:
0
Customizing the table format
You can also customize the columns displayed in the table format (format=table) by using col=<field> - you can specify multiple fields and what order they are displayed by placing pipes (|) between the columns like below:
[[TicketQuery(max=3,status=closed,order=id,desc=1,format=table,col=resolution|summary|owner|reporter)]]
This is displayed as:
Results (1 - 3 of 39)
| Ticket | Resolution | Summary | Owner | Reporter |
|---|---|---|---|---|
| #42 | fixed | nginx写入cookie失效导致IE浏览下下载失败报障处理 | huangxianduan | guogongpu |
| #41 | fixed | mysql服务器假死或者崩溃重启后应用端数据库连接池中的连接问题 | yuanhuoqing | yuanhuoqing |
| #38 | fixed | 下载这边调用快搜接口发现的bug | huangxianduan | chengrongwei |
Full rows
In table format you can also have full rows by using rows=<field> like below:
[[TicketQuery(max=3,status=closed,order=id,desc=1,format=table,col=resolution|summary|owner|reporter,rows=description)]]
This is displayed as:
Results (1 - 3 of 39)
| Ticket | Resolution | Summary | Owner | Reporter |
|---|---|---|---|---|
| #42 | fixed | nginx写入cookie失效导致IE浏览下下载失败报障处理 | huangxianduan | guogongpu |
| Description |
IE浏览下ngixn写入cookie失效报障处理 nginx设置cookie.setExpire(-1),在IE浏览器新开tab的情况下，cookie失效， ftp防盗链检查无cookie后，跳转403，导致下载失败，火狐和Chrome浏览器正常。 网络同事设置cookie时间是900s，即15分钟，验证IE下下载正常。 |
|||
| #41 | fixed | mysql服务器假死或者崩溃重启后应用端数据库连接池中的连接问题 | yuanhuoqing | yuanhuoqing |
| Description |
故障现象：4.30早上6点30左右下载的mysql数据库出现故障重启数据库后，下载应用端日志报错连不上数据库： [04-30 06:47:24.593] {main} Caused by: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
[04-30 06:47:24.593] {main}
[04-30 06:47:24.593] {main} The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
[04-30 06:47:24.593] {main} at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
[04-30 06:47:24.593] {main} at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:381)
[04-30 06:47:24.593] {main} at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:455)
[04-30 06:47:24.593] {main} at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:463)
[04-30 06:47:24.593] {main} at org.springframework.jdbc.core.simple.SimpleJdbcTemplate.query(SimpleJdbcTemplate.java:199)
[04-30 06:47:24.593] {main} at org.gelivable.dao.GeliDao.list(GeliDao.java:243)
[04-30 06:47:24.593] {main} at cn.pconline.pcdlc.util.SysConfig.init(SysConfig.java:74)
[04-30 06:47:24.593] {main} at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
[04-30 06:47:24.593] {main} at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
[04-30 06:47:24.593] {main} at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
[04-30 06:47:24.593] {main} at java.lang.reflect.Method.invoke(Method.java:597)
[04-30 06:47:24.593] {main} at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleElement.invoke(InitDestroyAnnotationBeanPostProcessor.java:340)
[04-30 06:47:24.593] {main} at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor$LifecycleMetadata.invokeInitMethods(InitDestroyAnnotationBeanPostProcessor.java:293)
[04-30 06:47:24.593] {main} at org.springframework.beans.factory.annotation.InitDestroyAnnotationBeanPostProcessor.postProcessBeforeInitialization(InitDestroyAnnotationBeanPostProcessor.java:130)
[04-30 06:47:24.593] {main} ... 45 more
[04-30 06:47:24.593] {main} Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
[04-30 06:47:24.593] {main}
[04-30 06:47:24.593] {main} The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
问题分析：数据库连接池的原理是使用完的连接会丢回到连接池中下次需要使用时重新拿来用，即连接池的连接是没有断开连接关闭的，当数据库端出现故障主动断开了所有连接，而应用从数据库连接池拿到连接还是旧连接，所有使用不了，报这个错。
解决办法： 1、重启reisn重新初始化数据库连接池的连接（只是临时方案，不能根本上解决问题）；
<database> <jndi-name>jdbc/priceparity</jndi-name> <driver type="com.mysql.jdbc.Driver"> <url>jdbc:mysql://192.168.75.100:3306/priceparity?autoReconnect=true&autoReconnectForPools=true&failOverReadOnly=false&useUnicode=true&characterEncoding=GBK </url> <user>priceparity_app</user> <password>priceparity_app</password> </driver> <prepared-statement-cache-size>8</prepared-statement-cache-size> <max-connections>3</max-connections> <max-idle-time>30s</max-idle-time> </database>
后续的一些研究成果: 通过找架构的陈小虎讨论及查找资料并通过测试得出如下结论： 连接池中的连接失效跟数据库的驱动没有关系；
跟连接池实现原理有关。即是我们的应用中用到连接池，如果在数据库端主动断开连接情况下从连接池拿到的连接有可能是失效的，这个就要看我们是怎么配置连接池的连接管理策略（看具体的业务场景）。
<database> <jndi-name>jdbc/priceparity</jndi-name> <driver type="com.mysql.jdbc.Driver"> <url>jdbc:mysql://192.168.75.100:3306/priceparity?autoReconnect=true&autoReconnectForPools=true&failOverReadOnly=false&useUnicode=true&characterEncoding=GBK </url> <user>priceparity_app</user> <password>priceparity_app</password> </driver> <prepared-statement-cache-size>8</prepared-statement-cache-size> <max-connections>3</max-connections> <max-idle-time>30s</max-idle-time> <!--以下为连接池的连接管理策略配置--> <!--设置连接是否要检测--> <ping>true</ping> <!--配置需要检测的连接，设置0s表示从连接池里拿到的每一个连接都要检测下此连接是否可以用，设置大于0s表示从连接池里拿到的连接空闲时间超过该数值时需要检测--> <ping-interval>30s</ping-interval> <!--检测连接时查的数据表配置--> <ping-table>dual</ping-table> <!--检测连接时查询语句配置--> <ping-query>select 1 from dual</ping-query> </database>
（2）Mysql的驱动端的连接策略（一般建议用这个，看具体的业务场景）
mysql JDBC URL格式如下：
（3）dbcp、c3p0等连接池的连接管理策略配置，公司应用很少用，这里不再展开。 |
|||
| #38 | fixed | 下载这边调用快搜接口发现的bug | huangxianduan | chengrongwei |
| Description |
1、首先看逻辑过程 List<MasterKeyword> newKeywords = publishService.getNewKsPublish();
for (MasterKeyword keyword : newKeywords) {
String key = String.valueOf(keyword.getMasterId());
List<String> keywords = keywordMap.get(key);
if (keywords == null) {
keywords = new ArrayList<String>();
keywordMap.put(key, keywords);
}
keywords.add(keyword.getVal());
}
2、接着看具体数据库获取SQL方法 public List<MasterKeyword> getNewKsPublish() {
String sql = "SELECT master_keyword_id FROM dl_master_keyword_list a WHERE EXISTS (SELECT 1 FROM GENERATE_FILE b WHERE a.dl_master_id = b.dl_master_id) ORDER BY a.seq";
return geliDao.list(MasterKeyword.class, sql);
}
3、分析 这里按照 dl_master_keyword_list 中的seq关键字来排序，每一个软件有多个关键字，如果第一个软件例如为1，它的关键字为 1,2,3；然后第二个软件例如为2，它的关键字为1,2,3；这样通过那个SQL排序后，得出的顺序是 软件1关键字1->软件2关键字1-> 软件1关键字2->软件2关键字2-> 软件1关键字3->软件2关键字3; 这样我们开始部分的代码逻辑过程就会出现错乱，会导致软件1的关键字2保存在软件2里面去等等； 4、SQL语句修复 从原来的 SELECT master_keyword_id FROM dl_master_keyword_list a WHERE EXISTS (SELECT 1 FROM GENERATE_FILE b WHERE a.dl_master_id = b.dl_master_id) ORDER BY a.seq 修改后 SELECT master_keyword_id FROM dl_master_keyword_list a WHERE EXISTS (SELECT 1 FROM GENERATE_FILE b WHERE a.dl_master_id = b.dl_master_id) ORDER BY a.dl_master_id,a.seq 5、最后发现上面的SQL执行效率好低，看了数据库，都有好几秒时间；质询DBA之后，可以优化如下： SELECT a.master_keyword_id FROM dl_master_keyword_list a, GENERATE_FILE b WHERE a.dl_master_id = b.dl_master_id ORDER BY a.dl_master_id,a.seq 优化之后，时间缩短到接近0秒； 6、总结如下: 1、exists 在mysql 的世界里好像不怎么好使 2、能够写成连接形式的不要写子查询语句，mysql 的子查询，同样不怎么给力 |
|||
Query Language
query: TracLinks and the [[TicketQuery]] macro both use a mini “query language” for specifying query filters. Basically, the filters are separated by ampersands (&). Each filter then consists of the ticket field name, an operator, and one or more values. More than one value are separated by a pipe (|), meaning that the filter matches any of the values. To include a literal & or | in a value, escape the character with a backslash (\).
The available operators are:
| = | the field content exactly matches one of the values |
| ~= | the field content contains one or more of the values |
| ^= | the field content starts with one of the values |
| $= | the field content ends with one of the values |
All of these operators can also be negated:
| != | the field content matches none of the values |
| !~= | the field content does not contain any of the values |
| !^= | the field content does not start with any of the values |
| !$= | the field content does not end with any of the values |
The date fields created and modified can be constrained by using the = operator and specifying a value containing two dates separated by two dots (..). Either end of the date range can be left empty, meaning that the corresponding end of the range is open. The date parser understands a few natural date specifications like "3 weeks ago", "last month" and "now", as well as Bugzilla-style date specifications like "1d", "2w", "3m" or "4y" for 1 day, 2 weeks, 3 months and 4 years, respectively. Spaces in date specifications can be left out to avoid having to quote the query string.
| created=2007-01-01..2008-01-01 | query tickets created in 2007 |
| created=lastmonth..thismonth | query tickets created during the previous month |
| modified=1weekago.. | query tickets that have been modified in the last week |
| modified=..30daysago | query tickets that have been inactive for the last 30 days |
See also: TracTickets, TracReports, TracGuide
