001package org.apache.commons.jcs3.auxiliary.disk.jdbc.mysql; 002 003/* 004 * Licensed to the Apache Software Foundation (ASF) under one 005 * or more contributor license agreements. See the NOTICE file 006 * distributed with this work for additional information 007 * regarding copyright ownership. The ASF licenses this file 008 * to you under the Apache License, Version 2.0 (the 009 * "License"); you may not use this file except in compliance 010 * with the License. You may obtain a copy of the License at 011 * 012 * http://www.apache.org/licenses/LICENSE-2.0 013 * 014 * Unless required by applicable law or agreed to in writing, 015 * software distributed under the License is distributed on an 016 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY 017 * KIND, either express or implied. See the License for the 018 * specific language governing permissions and limitations 019 * under the License. 020 */ 021 022import java.sql.Connection; 023import java.sql.ResultSet; 024import java.sql.SQLException; 025import java.sql.Statement; 026 027import javax.sql.DataSource; 028 029import org.apache.commons.jcs3.auxiliary.disk.jdbc.TableState; 030import org.apache.commons.jcs3.log.Log; 031import org.apache.commons.jcs3.log.LogManager; 032import org.apache.commons.jcs3.utils.timing.ElapsedTimer; 033 034/** 035 * The MySQL Table Optimizer can optimize MySQL tables. It knows how to optimize for MySQL databases 036 * in particular and how to repair the table if it is corrupted in the process. 037 * <p> 038 * We will probably be able to abstract out a generic optimizer interface from this class in the 039 * future. 040 * </p> 041 */ 042public class MySQLTableOptimizer 043{ 044 /** The logger */ 045 private static final Log log = LogManager.getLog( MySQLTableOptimizer.class ); 046 047 /** The data source */ 048 private final DataSource dataSource; 049 050 /** The name of the table. */ 051 private String tableName; 052 053 /** optimizing, etc. */ 054 private final TableState tableState; 055 056 /** 057 * This constructs an optimizer with the disk can properties. 058 * <p> 059 * @param attributes 060 * @param tableState We mark the table status as optimizing when this is happening. 061 * @param dataSource access to the database 062 */ 063 public MySQLTableOptimizer( final MySQLDiskCacheAttributes attributes, final TableState tableState, final DataSource dataSource ) 064 { 065 setTableName( attributes.getTableName() ); 066 067 this.tableState = tableState; 068 this.dataSource = dataSource; 069 } 070 071 /** 072 * A scheduler will call this method. When it is called the table state is marked as optimizing. 073 * TODO we need to verify that no deletions are running before we call optimize. We should wait 074 * if a deletion is in progress. 075 * <p> 076 * This restores when there is an optimization error. The error output looks like this: 077 * 078 * <pre> 079 * mysql> optimize table JCS_STORE_FLIGHT_OPTION_ITINERARY; 080 * +---------------------------------------------+----------+----------+---------------------+ 081 * | Table | Op | Msg_type | Msg_text | 082 * +---------------------------------------------+----------+----------+---------------------+ 083 * | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | optimize | error | 2 when fixing table | 084 * | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | optimize | status | Operation failed | 085 * +---------------------------------------------+----------+----------+---------------------+ 086 * 2 rows in set (51.78 sec) 087 * </pre> 088 * 089 * A successful repair response looks like this: 090 * 091 * <pre> 092 * mysql> REPAIR TABLE JCS_STORE_FLIGHT_OPTION_ITINERARY; 093 * +---------------------------------------------+--------+----------+----------------------------------------------+ 094 * | Table | Op | Msg_type | Msg_text | 095 * +---------------------------------------------+--------+----------+----------------------------------------------+ 096 * | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | repair | error | 2 when fixing table | 097 * | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | repair | warning | Number of rows changed from 131276 to 260461 | 098 * | jcs_cache.JCS_STORE_FLIGHT_OPTION_ITINERARY | repair | status | OK | 099 * +---------------------------------------------+--------+----------+----------------------------------------------+ 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 final ElapsedTimer timer = new ElapsedTimer(); 119 boolean success = false; 120 121 if ( tableState.getState() == TableState.OPTIMIZATION_RUNNING ) 122 { 123 log.warn( "Skipping optimization. Optimize was called, but the " 124 + "table state indicates that an optimization is currently running." ); 125 return false; 126 } 127 128 try 129 { 130 tableState.setState( TableState.OPTIMIZATION_RUNNING ); 131 log.info( "Optimizing table [{0}]", this.getTableName()); 132 133 try (Connection con = dataSource.getConnection()) 134 { 135 // TEST 136 try (Statement sStatement = con.createStatement()) 137 { 138 try (ResultSet rs = sStatement.executeQuery( "optimize table " + this.getTableName() )) 139 { 140 // first row is error, then status 141 // if there is only one row in the result set, everything 142 // should be fine. 143 // This may be mysql version specific. 144 if ( rs.next() ) 145 { 146 final String status = rs.getString( "Msg_type" ); 147 final String message = rs.getString( "Msg_text" ); 148 149 log.info( "Message Type: {0}", status ); 150 log.info( "Message: {0}", message ); 151 152 if ( "error".equals( status ) ) 153 { 154 log.warn( "Optimization was in error. Will attempt " 155 + "to repair the table. Message: {0}", message); 156 157 // try to repair the table. 158 success = repairTable( sStatement ); 159 } 160 else 161 { 162 success = true; 163 } 164 } 165 } 166 167 // log the table status 168 final String statusString = getTableStatus( sStatement ); 169 log.info( "Table status after optimizing table [{0}]: {1}", 170 this.getTableName(), statusString ); 171 } 172 catch ( final SQLException e ) 173 { 174 log.error( "Problem optimizing table [{0}]", 175 this.getTableName(), e ); 176 return false; 177 } 178 } 179 catch ( final SQLException e ) 180 { 181 log.error( "Problem getting connection.", e ); 182 } 183 } 184 finally 185 { 186 tableState.setState( TableState.FREE ); 187 188 log.info( "Optimization of table [{0}] took {1} ms.", 189 this::getTableName, timer::getElapsedTime); 190 } 191 192 return success; 193 } 194 195 /** 196 * This calls show table status and returns the result as a String. 197 * <p> 198 * @param sStatement 199 * @return String 200 * @throws SQLException 201 */ 202 protected String getTableStatus( final Statement sStatement ) 203 throws SQLException 204 { 205 final StringBuilder statusString = new StringBuilder(); 206 try (ResultSet statusResultSet = sStatement.executeQuery( "show table status" )) 207 { 208 final int numColumns = statusResultSet.getMetaData().getColumnCount(); 209 while ( statusResultSet.next() ) 210 { 211 statusString.append( "\n" ); 212 for ( int i = 1; i <= numColumns; i++ ) 213 { 214 statusString.append(statusResultSet.getMetaData().getColumnLabel(i)) 215 .append(" [") 216 .append(statusResultSet.getString(i)) 217 .append("] | "); 218 } 219 } 220 } 221 return statusString.toString(); 222 } 223 224 /** 225 * This is called if the optimization is in error. 226 * <p> 227 * It looks for "OK" in response. If it find "OK" as a message in any result set row, it returns 228 * true. Otherwise we assume that the repair failed. 229 * <p> 230 * @param sStatement 231 * @return true if successful 232 * @throws SQLException 233 */ 234 protected boolean repairTable( final Statement sStatement ) 235 throws SQLException 236 { 237 boolean success = false; 238 239 // if( message != null && message.indexOf( ) ) 240 final StringBuilder repairString = new StringBuilder(); 241 try (ResultSet repairResult = sStatement.executeQuery( "repair table " + this.getTableName())) 242 { 243 final int numColumns = repairResult.getMetaData().getColumnCount(); 244 while ( repairResult.next() ) 245 { 246 for ( int i = 1; i <= numColumns; i++ ) 247 { 248 repairString.append(repairResult.getMetaData().getColumnLabel(i)) 249 .append(" [") 250 .append(repairResult.getString(i)) 251 .append("] | "); 252 } 253 254 final String message = repairResult.getString( "Msg_text" ); 255 if ( "OK".equals( message ) ) 256 { 257 success = true; 258 } 259 } 260 } 261 262 log.info("{0}", repairString); 263 264 if ( !success ) 265 { 266 log.warn( "Failed to repair the table. {0}", repairString ); 267 } 268 return success; 269 } 270 271 /** 272 * @param tableName The tableName to set. 273 */ 274 public void setTableName( final String tableName ) 275 { 276 this.tableName = tableName; 277 } 278 279 /** 280 * @return Returns the tableName. 281 */ 282 public String getTableName() 283 { 284 return tableName; 285 } 286}