View Javadoc
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&gt; 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&gt; 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&gt; 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 }