Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Repair Tables

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      Repair Tables

      MySQL Repair Table Statement

      There can be scenarios where tables in databases can become corrupted due to various reasons such as hardware failures, software bugs, or unexpected server crashes. When this situation happens, we cannot be able to access or manipulate the data in those tables because of data inconsistencies or errors.
      In such situations, to repair those currupted tables, we use the MySQL REPAIR TABLE statement. This statement works for only certain engines such as MyISAM, etc.

      Syntax

      Following is the syntax of MySQL REPAIR TABLE Statement 
      REPAIR [NO_WRITE_TO_BINLOG | LOCAL]
         TABLE tbl_name [, tbl_name] ...
         [QUICK] [EXTENDED] [USE_FRM]

      Example

      Let us start by creating a table named CUSTOMERS using the following query 
      CREATE TABLE CUSTOMERS (
         ID INT AUTO_INCREMENT,
         NAME VARCHAR(20) NOT NULL,
         AGE INT NOT NULL,
         ADDRESS CHAR (25),
         SALARY DECIMAL (18, 2),
         PRIMARY KEY (ID)
      );
      Here, we are inserting 7 records into the above created table using the below INSERT statement 
      INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
      (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
      (2, 'Khilan', 25, 'Delhi', 1500.00 ),
      (3, 'Kaushik', 23, 'Kota', 2000.00 ),
      (4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
      (5, 'Hardik', 27, 'Bhopal', 8500.00 ),
      (6, 'Komal', 22, 'Hyderabad', 4500.00 ),
      (7, 'Muffy', 24, 'Indore', 10000.00 );
      Assume the above created table is corrupted and we are using the REPAIR TABLE statement to repair it.
      REPAIR TABLE CUSTOMERS;
      The above query displays an error as: "The storage engine for the table doesn't support repair" because the REPAIR TABLE statement won't work with the default InnoDB engine.
      Table
      Op
      Msg_type
      Msg_text
      tutorials.customers
      repair
      note
      The storage engine for the table doesn't support repair
      To repair the table, We need to change the table's engine to MyISAM because it supports the REPAIR TABLE statement.
      ALTER TABLE CUSTOMERS ENGINE = MyISAM;
      Now, to repair the CUSTOMERS table, execute the following query 
      REPAIR TABLE CUSTOMERS;

      Output

      We can see in the output below, it says OK which indicates that the table CUSTOMERS is in good condition, and there are no issues or corruption.
      Table
      Op
      Msg_type
      Msg_text
      tutorials.customers
      repair
      status
      OK

      Repairing multiple tables

      In MySQL, we can also repair multiple tables and get the results using the REPAIR TABLE Statement. To do this, we just need to list the names of the tables we want to repair, separating them with commas.

      Example

      Let us create three different tables with the names Test1, Test2, and Test3 using the following CREATE TABLE statements 
      CREATE TABLE Test1(ID INT, Name VARCHAR(255));
      CREATE TABLE Test2(ID INT, Name VARCHAR(255));
      CREATE TABLE Test3(ID INT, Name VARCHAR(255));
      Assume the above three tables are corrupted. Change the engine of these tables to MyISAM to repair them with REPAIR TABLE statement 
      ALTER TABLE Test1 ENGINE = MyISAM;
      ALTER TABLE Test2 ENGINE = MyISAM;
      ALTER TABLE Test3 ENGINE = MyISAM;
      Now, to repair these tables, execute the following query 
      REPAIR TABLE Test1, Test2, Test3;
      As we can see in the output below, all three tables are in good condition, and there are no issues or corruption.
      Table
      Op
      Msg_type
      Msg_text
      tutorials.test1
      repair
      status
      OK
      tutorials.test2
      repair
      status
      OK
      tutorials.test3
      repair
      status
      OK

      Repair Table Options

      We have various optional clauses to use with REPAIR TABLE such as QUICK, EXTENDED, and, USE_FRM clause. Let us discuss them one by one with suitable examples.

      QUICK Clause

      The QUICK clause is the is the default and it is most commonly used with REPAIR TABLE. If you specify the QUICK clause, MySQL will repair the table without re-creating it.
      Example
      In the following example, we are using the QUICK clause with the REPAIR TABLE statement to repair the CUSTOMERS table.
      REPAIR TABLE CUSTOMERS QUICK;
      Output
      Executing the query above will produce the following output
      Table
      Op
      Msg_type
      Msg_text
      tutorials.customers
      repair
      status
      OK

      EXTENDED Clause

      If we specify the EXTENDED clause, MySQL not only repairs the table but also rebuilds the index and optimizes the table structure.
      Note: The EXTENDED clause is a more time-consuming compared to QUICK clause.
      Example
      In the following example, we are using the EXTENDED clause with the REPAIR TABLE statement to repair the CUSTOMERS table.
      REPAIR TABLE CUSTOMERS EXTENDED;
      Output
      Executing the query above will produce the following output
      Table
      Op
      Msg_type
      Msg_text
      tutorials.customers
      repair
      status
      OK

      USE_FRM clause

      We can use the USE_FRM clause, in case the MYI index file is missing. If you provide this clause the .NYI file will be recreated using information from the data dictionary
      Example
      Here, we are using the USE_FRM clause with the REPAIR TABLE statement to repair the CUSTOMERS table.
      REPAIR TABLE CUSTOMERS USE_FRM;
      Output
      Executing the query above will produce the following output
      Table
      Op
      Msg_type
      Msg_text
      tutorials.CUSTOMERS
      repair
      warning
      Number of rows changed from 0 to 7
      tutorials.customers
      repair
      status
      OK

      Repairing table Using a Client Program

      Besides repairing a table in a MySQL database with a MySQL query, we can also use a client program to perform the REPAIR TABLE operation.

      Syntax

      Following are the syntaxes to repair a table in various programming languages
      PHPNodeJSJavaPython
      To repair a table in a MySQL Database through a PHP program, we need to execute the Repair Table statement using the mysqli function query() as 
      $sql="Repair TABLE table_names";
      $mysqli->query($sql);

      Example

      Following are the programs
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $dbname = 'TUTORIALS';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
      
      if ($mysqli->connect_errno) {
          printf("Connect failed: %s", $mysqli->connect_error);
          exit();
      }
      // printf('Connected successfully.');
      
      $sql = " REPAIR TABLE SalesSummary ";
      if ($mysqli->query($sql)) {
          printf(" Table repair successfully.");
      }
      if ($mysqli->errno) {
          printf("table could not be repaired .", $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as follows 
      Table repair successfully.