1 package org.apache.jcs.auxiliary.disk.jdbc.mysql;
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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 private final static Log log = LogFactory.getLog( MySQLTableOptimizer.class );
46
47 private JDBCDiskCachePoolAccess poolAccess = null;
48
49 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 {
62 setTableName( attributes.getTableName() );
63
64 this.tableState = tableState;
65 /***
66 * This initializes the pool access.
67 */
68 initializePoolAccess( attributes );
69 }
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
83 Class.forName( cattr.getDriverClassName() );
84 }
85 catch ( ClassNotFoundException e )
86 {
87 log.error( "Couldn't find class for driver [" + cattr.getDriverClassName() + "]", e );
88 }
89
90 poolAccess = new JDBCDiskCachePoolAccess( cattr.getName() );
91
92 poolAccess.setupDriver( cattr.getUrl() + cattr.getDatabase(), cattr.getUserName(), cattr.getPassword(),
93 cattr.getMaxActive() );
94 }
95 catch ( Exception e )
96 {
97 log.error( "Problem getting connection.", e );
98 }
99 }
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 long start = System.currentTimeMillis();
152 boolean success = false;
153
154 if ( tableState.getState() == TableState.OPTIMIZATION_RUNNING )
155 {
156 log
157 .warn( "Skipping optimization. Optimize was called, but the table state indicates that an optimization is currently running." );
158 return false;
159 }
160
161 try
162 {
163 tableState.setState( TableState.OPTIMIZATION_RUNNING );
164 if ( log.isInfoEnabled() )
165 {
166 log.debug( "Optimizing table [" + this.getTableName() + "]" );
167 }
168
169 Connection con;
170 try
171 {
172 con = poolAccess.getConnection();
173 }
174 catch ( SQLException e )
175 {
176 log.error( "Problem getting connection.", e );
177 return false;
178 }
179
180 try
181 {
182
183 Statement sStatement = null;
184 try
185 {
186 sStatement = con.createStatement();
187
188 ResultSet rs = sStatement.executeQuery( "optimize table " + this.getTableName() );
189
190
191
192
193
194 if ( rs.next() )
195 {
196 String status = rs.getString( "Msg_type" );
197 String message = rs.getString( "Msg_text" );
198
199 if ( log.isInfoEnabled() )
200 {
201 log.info( "Message Type: " + status );
202 log.info( "Message: " + message );
203 }
204
205 if ( "error".equals( status ) )
206 {
207 log.warn( "Optimization was in erorr. Will attempt to repair the table. Message: "
208 + message );
209
210
211 success = repairTable( sStatement );
212 }
213 else
214 {
215 success = true;
216 }
217 }
218
219
220 String statusString = getTableStatus( sStatement );
221 if ( log.isInfoEnabled() )
222 {
223 log.info( "Table status after optimizing table [" + this.getTableName() + "]\n" + statusString );
224 }
225 }
226 catch ( SQLException e )
227 {
228 log.error( "Problem optimizing table [" + this.getTableName() + "]", e );
229 return false;
230 }
231 finally
232 {
233 try
234 {
235 sStatement.close();
236 }
237 catch ( SQLException e )
238 {
239 log.error( "Problem closing statement.", e );
240 }
241 }
242 }
243 finally
244 {
245 try
246 {
247 con.close();
248 }
249 catch ( SQLException e )
250 {
251 log.error( "Problem closing connection.", e );
252 }
253 }
254 }
255 finally
256 {
257 tableState.setState( TableState.FREE );
258
259 long end = System.currentTimeMillis();
260 if ( log.isInfoEnabled() )
261 {
262 log.info( "Optimization of table [" + this.getTableName() + "] took " + ( end - start ) + " ms." );
263 }
264 }
265
266 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 ResultSet statusResultSet = sStatement.executeQuery( "show table status" );
280 StringBuffer statusString = new StringBuffer();
281 int numColumns = statusResultSet.getMetaData().getColumnCount();
282 while ( statusResultSet.next() )
283 {
284 statusString.append( "\n" );
285 for ( int i = 1; i <= numColumns; i++ )
286 {
287 statusString.append( statusResultSet.getMetaData().getColumnLabel( i ) + " ["
288 + statusResultSet.getString( i ) + "] | " );
289 }
290 }
291 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 boolean success = false;
308
309
310 ResultSet repairResult = sStatement.executeQuery( "repair table " + this.getTableName() );
311 StringBuffer repairString = new StringBuffer();
312 int numColumns = repairResult.getMetaData().getColumnCount();
313 while ( repairResult.next() )
314 {
315 for ( int i = 1; i <= numColumns; i++ )
316 {
317 repairString.append( repairResult.getMetaData().getColumnLabel( i ) + " [" + repairResult.getString( i )
318 + "] | " );
319 }
320
321 String message = repairResult.getString( "Msg_text" );
322 if ( "OK".equals( message ) )
323 {
324 success = true;
325 }
326 }
327 if ( log.isInfoEnabled() )
328 {
329 log.info( repairString );
330 }
331
332 if ( !success )
333 {
334 log.warn( "Failed to repair the table. " + repairString );
335 }
336 return success;
337 }
338
339 /***
340 * @param tableName
341 * The tableName to set.
342 */
343 public void setTableName( String tableName )
344 {
345 this.tableName = tableName;
346 }
347
348 /***
349 * @return Returns the tableName.
350 */
351 public String getTableName()
352 {
353 return tableName;
354 }
355 }