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.JDBCDiskCachePoolAccess;
30 import org.apache.jcs.auxiliary.disk.jdbc.TableState;
31
32 /**
33 * The MySQL Table Optimizer can optimize MySQL tables. It knows how to optimize for MySQL datbases
34 * in particular and how to repair the table if it is corrupted in the process.
35 * <p>
36 * We will probably be able to abstract out a generic optimizer interface from this class in the
37 * future.
38 * <p>
39 * @author Aaron Smuts
40 */
41 public class MySQLTableOptimizer
42 {
43 /** The logger */
44 private final static Log log = LogFactory.getLog( MySQLTableOptimizer.class );
45
46 /** The pool */
47 private JDBCDiskCachePoolAccess poolAccess = null;
48
49 /** The name of the table. */
50 private String tableName = null;
51
52 /** optimizing, etc. */
53 private TableState tableState;
54
55 /**
56 * This constructs an optimizer with the disk cacn properties.
57 * <p>
58 * @param attributes
59 * @param tableState We mark the table status as optimizing when this is happening.
60 * @param poolAccess access to the database
61 */
62 public MySQLTableOptimizer( MySQLDiskCacheAttributes attributes, TableState tableState,
63 JDBCDiskCachePoolAccess poolAccess )
64 {
65 setTableName( attributes.getTableName() );
66
67 this.tableState = tableState;
68
69 this.poolAccess = poolAccess;
70 }
71
72 /**
73 * A scheduler will call this method. When it is called the table state is marked as optimizing.
74 * TODO we need to verify that no deletions are running before we call optimize. We should wait
75 * if a deletion is in progress.
76 * <p>
77 * This restores when there is an optimization error. The error output looks like this:
78 *
79 * <pre>
80 * mysql> optimize table JCS_STORE_FLIGHT_OPTION_ITINERARY;
81 * +---------------------------------------------+----------+----------+---------------------+
82 * | Table | Op | Msg_type | Msg_text |
83 * +---------------------------------------------+----------+----------+---------------------+
84 * | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | optimize | error | 2 when fixing table |
85 * | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | optimize | status | Operation failed |
86 * +---------------------------------------------+----------+----------+---------------------+
87 * 2 rows in set (51.78 sec)
88 * </pre>
89 *
90 * A successful repair response looks like this:
91 *
92 * <pre>
93 * mysql> REPAIR TABLE JCS_STORE_FLIGHT_OPTION_ITINERARY;
94 * +---------------------------------------------+--------+----------+----------------------------------------------+
95 * | Table | Op | Msg_type | Msg_text |
96 * +---------------------------------------------+--------+----------+----------------------------------------------+
97 * | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | repair | error | 2 when fixing table |
98 * | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | repair | warning | Number of rows changed from 131276 to 260461 |
99 * | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | repair | status | OK |
100 * +---------------------------------------------+--------+----------+----------------------------------------------+
101 * 3 rows in set (3 min 5.94 sec)
102 * </pre>
103 *
104 * A successful optimization looks like this:
105 *
106 * <pre>
107 * mysql> optimize table JCS_STORE_DEFAULT;
108 * +-----------------------------+----------+----------+----------+
109 * | Table | Op | Msg_type | Msg_text |
110 * +-----------------------------+----------+----------+----------+
111 * | jcs_cache.JCS_STORE_DEFAULT | optimize | status | OK |
112 * +-----------------------------+----------+----------+----------+
113 * 1 row in set (1.10 sec)
114 * </pre>
115 * @return true if it worked
116 */
117 public boolean optimizeTable()
118 {
119 long start = System.currentTimeMillis();
120 boolean success = false;
121
122 if ( tableState.getState() == TableState.OPTIMIZATION_RUNNING )
123 {
124 log
125 .warn( "Skipping optimization. Optimize was called, but the table state indicates that an optimization is currently running." );
126 return false;
127 }
128
129 try
130 {
131 tableState.setState( TableState.OPTIMIZATION_RUNNING );
132 if ( log.isInfoEnabled() )
133 {
134 log.info( "Optimizing table [" + this.getTableName() + "]" );
135 }
136
137 Connection con;
138 try
139 {
140 con = poolAccess.getConnection();
141 }
142 catch ( SQLException e )
143 {
144 log.error( "Problem getting connection.", e );
145 return false;
146 }
147
148 try
149 {
150 // TEST
151 Statement sStatement = null;
152 try
153 {
154 sStatement = con.createStatement();
155
156 ResultSet rs = sStatement.executeQuery( "optimize table " + this.getTableName() );
157
158 // first row is error, then status
159 // if there is only one row in the result set, everything
160 // should be fine.
161 // This may be mysql version specific.
162 if ( rs.next() )
163 {
164 String status = rs.getString( "Msg_type" );
165 String message = rs.getString( "Msg_text" );
166
167 if ( log.isInfoEnabled() )
168 {
169 log.info( "Message Type: " + status );
170 log.info( "Message: " + message );
171 }
172
173 if ( "error".equals( status ) )
174 {
175 log.warn( "Optimization was in erorr. Will attempt to repair the table. Message: "
176 + message );
177
178 // try to repair the table.
179 success = repairTable( sStatement );
180 }
181 else
182 {
183 success = true;
184 }
185 }
186
187 // log the table status
188 String statusString = getTableStatus( sStatement );
189 if ( log.isInfoEnabled() )
190 {
191 log.info( "Table status after optimizing table [" + this.getTableName() + "]\n" + statusString );
192 }
193 }
194 catch ( SQLException e )
195 {
196 log.error( "Problem optimizing table [" + this.getTableName() + "]", e );
197 return false;
198 }
199 finally
200 {
201 if (sStatement != null)
202 {
203 try
204 {
205 sStatement.close();
206 }
207 catch ( SQLException e )
208 {
209 log.error( "Problem closing statement.", e );
210 }
211 }
212 }
213 }
214 finally
215 {
216 try
217 {
218 con.close();
219 }
220 catch ( SQLException e )
221 {
222 log.error( "Problem closing connection.", e );
223 }
224 }
225 }
226 finally
227 {
228 tableState.setState( TableState.FREE );
229
230 long end = System.currentTimeMillis();
231 if ( log.isInfoEnabled() )
232 {
233 log.info( "Optimization of table [" + this.getTableName() + "] took " + ( end - start ) + " ms." );
234 }
235 }
236
237 return success;
238 }
239
240 /**
241 * This calls show table status and returns the result as a String.
242 * <p>
243 * @param sStatement
244 * @return String
245 * @throws SQLException
246 */
247 protected String getTableStatus( Statement sStatement )
248 throws SQLException
249 {
250 ResultSet statusResultSet = sStatement.executeQuery( "show table status" );
251 StringBuffer statusString = new StringBuffer();
252 int numColumns = statusResultSet.getMetaData().getColumnCount();
253 while ( statusResultSet.next() )
254 {
255 statusString.append( "\n" );
256 for ( int i = 1; i <= numColumns; i++ )
257 {
258 statusString.append( statusResultSet.getMetaData().getColumnLabel( i ) + " ["
259 + statusResultSet.getString( i ) + "] | " );
260 }
261 }
262 return statusString.toString();
263 }
264
265 /**
266 * This is called if the optimizatio is in error.
267 * <p>
268 * It looks for "OK" in response. If it find "OK" as a message in any result set row, it returns
269 * true. Otherwise we assume that the repair failed.
270 * <p>
271 * @param sStatement
272 * @return true if successful
273 * @throws SQLException
274 */
275 protected boolean repairTable( Statement sStatement )
276 throws SQLException
277 {
278 boolean success = false;
279
280 // if( message != null && message.indexOf( ) )
281 ResultSet repairResult = sStatement.executeQuery( "repair table " + this.getTableName() );
282 StringBuffer repairString = new StringBuffer();
283 int numColumns = repairResult.getMetaData().getColumnCount();
284 while ( repairResult.next() )
285 {
286 for ( int i = 1; i <= numColumns; i++ )
287 {
288 repairString.append( repairResult.getMetaData().getColumnLabel( i ) + " [" + repairResult.getString( i )
289 + "] | " );
290 }
291
292 String message = repairResult.getString( "Msg_text" );
293 if ( "OK".equals( message ) )
294 {
295 success = true;
296 }
297 }
298 if ( log.isInfoEnabled() )
299 {
300 log.info( repairString );
301 }
302
303 if ( !success )
304 {
305 log.warn( "Failed to repair the table. " + repairString );
306 }
307 return success;
308 }
309
310 /**
311 * @param tableName The tableName to set.
312 */
313 public void setTableName( String tableName )
314 {
315 this.tableName = tableName;
316 }
317
318 /**
319 * @return Returns the tableName.
320 */
321 public String getTableName()
322 {
323 return tableName;
324 }
325 }