View Javadoc

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      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                  // org.gjt.mm.mysql.Driver
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&gt; 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&gt; 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&gt; 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                 // TEST
183                 Statement sStatement = null;
184                 try
185                 {
186                     sStatement = con.createStatement();
187 
188                     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                     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                             // try to repair the table.
211                             success = repairTable( sStatement );
212                         }
213                         else
214                         {
215                             success = true;
216                         }
217                     }
218 
219                     // log the table status
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         // if( message != null && message.indexOf( ) )
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 }