Archive for the ‘mysql’ Category
Try setting up the Apache Commons DBCP correctly.
You need to set:
- validationQuery to SELECT 1+1
- testOnBorrow to true
That should fix the problem.
Recommended setting:
<property name=”validationQuery” value=”SELECT 1 FROM DUAL” />
<property name=”testOnBorrow” value=”true” />
For Oracle:
validationQuery="select 1 from dual"
For MySQL:
validationQuery="select 1"
Example:
<Resource name="jdbc/confluence" auth="Container" type="javax.sql.DataSource"
username="yourusername"
password="yourpassword"
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/confluence?useUnicode=true&characterEncoding=utf8"
maxActive="15"
maxIdle="7"
defaultTransactionIsolation="READ_COMMITTED"
validationQuery="Select 1"
testOnBorrow="true"/>
|
Refer to http://tomcat.apache.org/tomcat-6.0-doc/jndi-resources-howto.html
search for “validationQuery” and “testOnBorrow”
—————————————————————————————————————-
如果你没有修改过MySQL的配置，缺省情况下，wait_timeout的初始值是28800。
wait_timeout过大有弊端，其体现就是MySQL里大量的SLEEP进程无法及时释放，拖累系统性能，不过也不能把这个指设置的过小，否则你可能会遭遇到“MySQL has gone away”之类的问题，通常来说，我觉得把wait_timeout设置为10是个不错的选择，但某些情况下可能也会出问题，比如说有一个CRON脚本，其中两次SQL查询的间隔时间大于10秒的话，那么这个设置就有问题了（当然，这也不是不能解决的问题，你可以在程序里时不时mysql_ping一下，以便服务器知道你还活着，重新计算wait_timeout时间）：
# vi /etc/my.cnf
[mysqld]
wait_timeout=10
# /etc/init.d/mysql restart
不过这个方法太生硬了，线上服务重启无论如何都应该尽可能避免，看看如何在MySQL命令行里通过SET来设置：
mysql> set global wait_timeout=10;
mysql> show global variables like ‘wait_timeout’;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| wait_timeout | 10 |
+—————————-+——-+
这里一个容易把人搞蒙的地方是如果查询时使用的是show variables的话，会发现设置好像并没有生效，这是因为单纯使用show variables的话就等同于使用的是show session variables，查询的是会话变量，只有使用show global variables，查询的才是全局变量。
网络上很多人都抱怨说他们set global之后使用show variables查询没有发现改变，原因就在于混淆了会话变量和全局变量，如果仅仅想修改会话变量的话，可以使用类似set wait_timeout=10;或者set session wait_timeout=10;这样的语法。
另一个值得注意的是会话变量wait_timeout初始化的问题，这一点在手册里已经明确指出了，我就直接拷贝了：
On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value, depending on the type of client (as defined by the CLIENT_INTERACTIVE connect option to mysql_real_connect()).
MySQL大拿Jeremy Zawodny曾在他的文章Fixing Poor MySQL Default Configuration Values里面列出了几个很恶心的MySQL缺省设置，不过没包含wait_timeout，但我觉得它也应该算一个，每次新装MySQL后最好都记得修改它。
http://stackoverflow.com/questions/15949/javatomcat-dying-database-connection
http://www.iteye.com/topic/569864
How do I limit the number of rows displayed in the results page?
Often a database query will yield hundreds or thousands of rows of data. Trying to display all of them in one webpage could be very slow and resource intensive. Worse, it results in a poor user experience for the person who actually has to scroll through all of this data.
Well-designed websites will return a small subset of the data and provide links to move to the next or last set.
The easiest way to accomplish this is by constraining the number of rows returned from the database query in the first place.
- MySQL uses the ‘LIMIT’ keyword for this.
- PostgreSQL uses the ‘LIMIT’ and ‘OFFSET’ keywords for this.
- Oracle uses the ‘ROWNUM’ keyword. It doesn’t work like LIMIT and OFFSET. You will probably find it strange.
- DB2 uses the ‘ROWNUMBER’ ‘OVER’ combination.
- SQL Server uses the ‘TOP’ keyword, followed by the number of rows from the top of the result.
A typical query in MySQL would look something like:
SELECT * FROM my_table LIMIT 10, 50
|
This will yield rows 11 through 60.
(Ref : http://dev.mysql.com/doc/refman/5.0/en/select.html)
Another way to limit the number of records returned is to use the
java.sql.Statement.setMaxRows(int)
|
Because this is part of the JDBC API, it works for all servers.
It should be noted that with both approaches it is undefined which rows are returned. If you want particular rows returned, you should add an “order …” clause so that the order of rows is defined.
Javaranch has a forum dedicated to JDBC/SQL issues where you can go to read more about formulating SQL statements: JDBC forum
JavaRanch Journal article on paging with JDBC and JSP
Here are some notes from other conversations on pagination:
I. Repeat the query for each new request.
Algorithm
Client requests page 1
Execute the query
Return rows for page 1
Client requests page 2
Execute the query
Skip rows for page 1
Return rows for page 2
Variation
Most RDBMS vendors support returning a specified range of row numbers.
Efficiency varies. May not help much when combined with "order by"
Pro/Con
Pro: Stateless. Nothing is cached between requests.
Pro: Simple. Every request is the same.
Pro: Up to date with any inserts or deletes in the database.
Con: Database and time intensive. Might be repeating large,
expensive queries - if users really do ever page fwd.
Con: Not guaranteed consistent. If other processes are inserting and
deleting rows, paging fwd or bkwd might skip rows. Fwd then Bkwd
might not give the same rows.
Notes We have used this with mainframe requests where even the nastiest
queries were fast and the server had no options for storing state.
We have used row number schemes for client requests, and the highest
key on a page for page forward only action.
II. Hold the query results on the server
Algorithm
Client requests page 1
Execute query
Cache complete result set or just PKeys
Return rows for page 1
Client requests page 2
Get rows from cache or get PKeys from cache, rows from database
Return rows for page 2
Pros/Cons
Pro: Does NOT repeat the query
Pro: Self consistent - fwd then bkwd will give identical results
Pro: Can share cached results if two users have identical requests
Con: Big cache in memory or someplace close to it
Con: Complexity for cache, time-out
III. Hold a scrolling cursor on the server
Algorithm
Client requests page 1
Execute query with a cursor
Fetch & return rows for page 1
Client requests page 2
Fetch & return rows for page 2
Pro/Con
Pro: Does not repeat the query
Pro: Very small state - just a connection & cursor
Pro: Self consistent
Con: More open connections. Might hold open one db connection
per user
Con: What does this do to the db? Doesn't it cache rows not yet
fetched?
Con: Complexity for time-out
IV. Hold results in a temporary table
Algorithm
Client requests page 1
Execute the big query
For each row of results
insert the row into a temp table, keyed on session id
Maybe do this in a stored proc?
Page through the temp table using I, II or III.
Pro/Con
Pro: The big query is only done once
Con: Initial hit time - adds a lot of inserts
Con: Need to clean up that temp table some time
Variation
Select directly into a temp table if your db allows it
am in the middle of moving a lot of my datasources over to the updated Connector/J (JDBC)
One error I have seen a lot in my development enviorment is
view plain print about
1Error Executing Database Query.
2Cannot convert value ‘0000-00-00 00:00:00’ from column xxxx to TIMESTAMP.
This is caused by Java as it cannot handle zero dates (‘0000-00-00 00:00:00’), it trys to do anything it can with them but convert them to the original date :o). To get round this you can set a parameter in the JDBC URL called zeroDateTimeBehavior to convertToNull.
So in your Data Sources page you should have something similar to:
view plain print about
1jdbc:mysql://localhost:3306/mySqlDatabase?zeroDateTimeBehavior=convertToNull
road 