%line | %branch | |||||||||
---|---|---|---|---|---|---|---|---|---|---|
org.apache.jcs.auxiliary.disk.jdbc.mysql.MySQLTableOptimizer |
|
|
1 | package org.apache.jcs.auxiliary.disk.jdbc.mysql; |
|
2 | ||
3 | /* |
|
4 | * Licensed to the Apache Software Foundation (ASF) under one |
|
5 | * or more contributor license agreements. See the NOTICE file |
|
6 | * distributed with this work for additional information |
|
7 | * regarding copyright ownership. The ASF licenses this file |
|
8 | * to you under the Apache License, Version 2.0 (the |
|
9 | * "License"); you may not use this file except in compliance |
|
10 | * with the License. You may obtain a copy of the License at |
|
11 | * |
|
12 | * http://www.apache.org/licenses/LICENSE-2.0 |
|
13 | * |
|
14 | * Unless required by applicable law or agreed to in writing, |
|
15 | * software distributed under the License is distributed on an |
|
16 | * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
|
17 | * KIND, either express or implied. See the License for the |
|
18 | * specific language governing permissions and limitations |
|
19 | * under the License. |
|
20 | */ |
|
21 | ||
22 | import java.sql.Connection; |
|
23 | import java.sql.ResultSet; |
|
24 | import java.sql.SQLException; |
|
25 | import java.sql.Statement; |
|
26 | ||
27 | import org.apache.commons.logging.Log; |
|
28 | import org.apache.commons.logging.LogFactory; |
|
29 | import org.apache.jcs.auxiliary.disk.jdbc.JDBCDiskCacheAttributes; |
|
30 | import org.apache.jcs.auxiliary.disk.jdbc.JDBCDiskCachePoolAccess; |
|
31 | import org.apache.jcs.auxiliary.disk.jdbc.TableState; |
|
32 | ||
33 | /** |
|
34 | * The MySQL Table Optimizer can optimize MySQL tables. It knows how to optimize |
|
35 | * for MySQL datbases in particular and how to repari the table if it is |
|
36 | * corrupted in the process. |
|
37 | * <p> |
|
38 | * We will probably be able to abstract out a generic optimizer interface from |
|
39 | * this class in the future. |
|
40 | * <p> |
|
41 | * @author Aaron Smuts |
|
42 | */ |
|
43 | public class MySQLTableOptimizer |
|
44 | { |
|
45 | 14 | private final static Log log = LogFactory.getLog( MySQLTableOptimizer.class ); |
46 | ||
47 | 7 | private JDBCDiskCachePoolAccess poolAccess = null; |
48 | ||
49 | 7 | private String tableName = null; |
50 | ||
51 | private TableState tableState; |
|
52 | ||
53 | /** |
|
54 | * This constructs an optimizer with the disk cacn properties. |
|
55 | * <p> |
|
56 | * @param attributes |
|
57 | * @param tableState |
|
58 | * We mark the table status as optimizing when this is happening. |
|
59 | */ |
|
60 | public MySQLTableOptimizer( MySQLDiskCacheAttributes attributes, TableState tableState ) |
|
61 | 7 | { |
62 | 7 | setTableName( attributes.getTableName() ); |
63 | ||
64 | 7 | this.tableState = tableState; |
65 | /** |
|
66 | * This initializes the pool access. |
|
67 | */ |
|
68 | 7 | initializePoolAccess( attributes ); |
69 | 7 | } |
70 | ||
71 | /** |
|
72 | * Register the driver and create a pool. |
|
73 | * <p> |
|
74 | * @param cattr |
|
75 | */ |
|
76 | protected void initializePoolAccess( JDBCDiskCacheAttributes cattr ) |
|
77 | { |
|
78 | try |
|
79 | { |
|
80 | try |
|
81 | { |
|
82 | // org.gjt.mm.mysql.Driver |
|
83 | 7 | Class.forName( cattr.getDriverClassName() ); |
84 | } |
|
85 | 0 | catch ( ClassNotFoundException e ) |
86 | { |
|
87 | 0 | log.error( "Couldn't find class for driver [" + cattr.getDriverClassName() + "]", e ); |
88 | 7 | } |
89 | ||
90 | 7 | poolAccess = new JDBCDiskCachePoolAccess( cattr.getName() ); |
91 | ||
92 | 7 | poolAccess.setupDriver( cattr.getUrl() + cattr.getDatabase(), cattr.getUserName(), cattr.getPassword(), |
93 | cattr.getMaxActive() ); |
|
94 | } |
|
95 | 0 | catch ( Exception e ) |
96 | { |
|
97 | 0 | log.error( "Problem getting connection.", e ); |
98 | 7 | } |
99 | 7 | } |
100 | ||
101 | /** |
|
102 | * A scheduler will call this method. When it is called the table state is |
|
103 | * marked as optimizing. TODO we need to verify that no deletions are |
|
104 | * running before we call optimize. We should wait if a deletion is in |
|
105 | * progress. |
|
106 | * <p> |
|
107 | * This restores when there is an optimization error. The error output looks |
|
108 | * like this: |
|
109 | * |
|
110 | * <pre> |
|
111 | * mysql> optimize table JCS_STORE_FLIGHT_OPTION_ITINERARY; |
|
112 | * +---------------------------------------------+----------+----------+---------------------+ |
|
113 | * | Table | Op | Msg_type | Msg_text | |
|
114 | * +---------------------------------------------+----------+----------+---------------------+ |
|
115 | * | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | optimize | error | 2 when fixing table | |
|
116 | * | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | optimize | status | Operation failed | |
|
117 | * +---------------------------------------------+----------+----------+---------------------+ |
|
118 | * 2 rows in set (51.78 sec) |
|
119 | * </pre> |
|
120 | * |
|
121 | * A successful repair response looks like this: |
|
122 | * |
|
123 | * <pre> |
|
124 | * mysql> REPAIR TABLE JCS_STORE_FLIGHT_OPTION_ITINERARY; |
|
125 | * +---------------------------------------------+--------+----------+----------------------------------------------+ |
|
126 | * | Table | Op | Msg_type | Msg_text | |
|
127 | * +---------------------------------------------+--------+----------+----------------------------------------------+ |
|
128 | * | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | repair | error | 2 when fixing table | |
|
129 | * | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | repair | warning | Number of rows changed from 131276 to 260461 | |
|
130 | * | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | repair | status | OK | |
|
131 | * +---------------------------------------------+--------+----------+----------------------------------------------+ |
|
132 | * 3 rows in set (3 min 5.94 sec) |
|
133 | * </pre> |
|
134 | * |
|
135 | * A successful optimization looks like this: |
|
136 | * |
|
137 | * <pre> |
|
138 | * mysql> optimize table JCS_STORE_DEFAULT; |
|
139 | * +-----------------------------+----------+----------+----------+ |
|
140 | * | Table | Op | Msg_type | Msg_text | |
|
141 | * +-----------------------------+----------+----------+----------+ |
|
142 | * | jcs_cache.JCS_STORE_DEFAULT | optimize | status | OK | |
|
143 | * +-----------------------------+----------+----------+----------+ |
|
144 | * 1 row in set (1.10 sec) |
|
145 | * </pre> |
|
146 | * |
|
147 | * @return |
|
148 | */ |
|
149 | public boolean optimizeTable() |
|
150 | { |
|
151 | 0 | long start = System.currentTimeMillis(); |
152 | 0 | boolean success = false; |
153 | ||
154 | 0 | if ( tableState.getState() == TableState.OPTIMIZATION_RUNNING ) |
155 | { |
|
156 | 0 | log |
157 | .warn( "Skipping optimization. Optimize was called, but the table state indicates that an optimization is currently running." ); |
|
158 | 0 | return false; |
159 | } |
|
160 | ||
161 | try |
|
162 | { |
|
163 | 0 | tableState.setState( TableState.OPTIMIZATION_RUNNING ); |
164 | 0 | if ( log.isInfoEnabled() ) |
165 | { |
|
166 | 0 | log.debug( "Optimizing table [" + this.getTableName() + "]" ); |
167 | } |
|
168 | ||
169 | Connection con; |
|
170 | try |
|
171 | { |
|
172 | 0 | con = poolAccess.getConnection(); |
173 | } |
|
174 | 0 | catch ( SQLException e ) |
175 | { |
|
176 | 0 | log.error( "Problem getting connection.", e ); |
177 | 0 | return false; |
178 | 0 | } |
179 | ||
180 | try |
|
181 | { |
|
182 | // TEST |
|
183 | 0 | Statement sStatement = null; |
184 | try |
|
185 | { |
|
186 | 0 | sStatement = con.createStatement(); |
187 | ||
188 | 0 | ResultSet rs = sStatement.executeQuery( "optimize table " + this.getTableName() ); |
189 | ||
190 | // first row is error, then status |
|
191 | // if there is only one row in the result set, everything |
|
192 | // should be fine. |
|
193 | // This may be mysql version specific. |
|
194 | 0 | if ( rs.next() ) |
195 | { |
|
196 | 0 | String status = rs.getString( "Msg_type" ); |
197 | 0 | String message = rs.getString( "Msg_text" ); |
198 | ||
199 | 0 | if ( log.isInfoEnabled() ) |
200 | { |
|
201 | 0 | log.info( "Message Type: " + status ); |
202 | 0 | log.info( "Message: " + message ); |
203 | } |
|
204 | ||
205 | 0 | if ( "error".equals( status ) ) |
206 | { |
|
207 | 0 | log.warn( "Optimization was in erorr. Will attempt to repair the table. Message: " |
208 | + message ); |
|
209 | ||
210 | // try to repair the table. |
|
211 | 0 | success = repairTable( sStatement ); |
212 | 0 | } |
213 | else |
|
214 | { |
|
215 | 0 | success = true; |
216 | } |
|
217 | } |
|
218 | ||
219 | // log the table status |
|
220 | 0 | String statusString = getTableStatus( sStatement ); |
221 | 0 | if ( log.isInfoEnabled() ) |
222 | { |
|
223 | 0 | log.info( "Table status after optimizing table [" + this.getTableName() + "]\n" + statusString ); |
224 | } |
|
225 | } |
|
226 | 0 | catch ( SQLException e ) |
227 | { |
|
228 | 0 | log.error( "Problem optimizing table [" + this.getTableName() + "]", e ); |
229 | 0 | return false; |
230 | } |
|
231 | finally |
|
232 | { |
|
233 | 0 | try |
234 | { |
|
235 | 0 | sStatement.close(); |
236 | } |
|
237 | 0 | catch ( SQLException e ) |
238 | { |
|
239 | 0 | log.error( "Problem closing statement.", e ); |
240 | 0 | } |
241 | 0 | } |
242 | } |
|
243 | finally |
|
244 | { |
|
245 | 0 | try |
246 | { |
|
247 | 0 | con.close(); |
248 | } |
|
249 | 0 | catch ( SQLException e ) |
250 | { |
|
251 | 0 | log.error( "Problem closing connection.", e ); |
252 | 0 | } |
253 | 0 | } |
254 | } |
|
255 | finally |
|
256 | { |
|
257 | 0 | tableState.setState( TableState.FREE ); |
258 | ||
259 | 0 | long end = System.currentTimeMillis(); |
260 | 0 | if ( log.isInfoEnabled() ) |
261 | { |
|
262 | 0 | log.info( "Optimization of table [" + this.getTableName() + "] took " + ( end - start ) + " ms." ); |
263 | } |
|
264 | 0 | } |
265 | ||
266 | 0 | return success; |
267 | } |
|
268 | ||
269 | /** |
|
270 | * This calls show table status and returns the result as a String. |
|
271 | * <p> |
|
272 | * @param sStatement |
|
273 | * @return String |
|
274 | * @throws SQLException |
|
275 | */ |
|
276 | protected String getTableStatus( Statement sStatement ) |
|
277 | throws SQLException |
|
278 | { |
|
279 | 0 | ResultSet statusResultSet = sStatement.executeQuery( "show table status" ); |
280 | 0 | StringBuffer statusString = new StringBuffer(); |
281 | 0 | int numColumns = statusResultSet.getMetaData().getColumnCount(); |
282 | 0 | while ( statusResultSet.next() ) |
283 | { |
|
284 | 0 | statusString.append( "\n" ); |
285 | 0 | for ( int i = 1; i <= numColumns; i++ ) |
286 | { |
|
287 | 0 | statusString.append( statusResultSet.getMetaData().getColumnLabel( i ) + " [" |
288 | + statusResultSet.getString( i ) + "] | " ); |
|
289 | } |
|
290 | 0 | } |
291 | 0 | return statusString.toString(); |
292 | } |
|
293 | ||
294 | /** |
|
295 | * This is called if the optimizatio is in error. |
|
296 | * <p> |
|
297 | * It looks for "OK" in response. If it find "OK" as a message in any result |
|
298 | * set row, it returns true. Otherwise we assume that the repair failed. |
|
299 | * <p> |
|
300 | * @param sStatement |
|
301 | * @return true if successful |
|
302 | * @throws SQLException |
|
303 | */ |
|
304 | protected boolean repairTable( Statement sStatement ) |
|
305 | throws SQLException |
|
306 | { |
|
307 | 0 | boolean success = false; |
308 | ||
309 | // if( message != null && message.indexOf( ) ) |
|
310 | 0 | ResultSet repairResult = sStatement.executeQuery( "repair table " + this.getTableName() ); |
311 | 0 | StringBuffer repairString = new StringBuffer(); |
312 | 0 | int numColumns = repairResult.getMetaData().getColumnCount(); |
313 | 0 | while ( repairResult.next() ) |
314 | { |
|
315 | 0 | for ( int i = 1; i <= numColumns; i++ ) |
316 | { |
|
317 | 0 | repairString.append( repairResult.getMetaData().getColumnLabel( i ) + " [" + repairResult.getString( i ) |
318 | + "] | " ); |
|
319 | } |
|
320 | ||
321 | 0 | String message = repairResult.getString( "Msg_text" ); |
322 | 0 | if ( "OK".equals( message ) ) |
323 | { |
|
324 | 0 | success = true; |
325 | } |
|
326 | 0 | } |
327 | 0 | if ( log.isInfoEnabled() ) |
328 | { |
|
329 | 0 | log.info( repairString ); |
330 | } |
|
331 | ||
332 | 0 | if ( !success ) |
333 | { |
|
334 | 0 | log.warn( "Failed to repair the table. " + repairString ); |
335 | } |
|
336 | 0 | return success; |
337 | } |
|
338 | ||
339 | /** |
|
340 | * @param tableName |
|
341 | * The tableName to set. |
|
342 | */ |
|
343 | public void setTableName( String tableName ) |
|
344 | { |
|
345 | 7 | this.tableName = tableName; |
346 | 7 | } |
347 | ||
348 | /** |
|
349 | * @return Returns the tableName. |
|
350 | */ |
|
351 | public String getTableName() |
|
352 | { |
|
353 | 0 | return tableName; |
354 | } |
|
355 | } |
This report is generated by jcoverage, Maven and Maven JCoverage Plugin. |