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