Как проверить таблицу sql на ошибки

title	description	author	ms.author	ms.date	ms.service	ms.subservice	ms.topic	f1_keywords	helpviewer_keywords	dev_langs
DBCC CHECKTABLE (Transact-SQL)	DBCC CHECKTABLE checks the integrity of all the pages and structures that make up the table or indexed view.	rwestMSFT	randolphwest	12/05/2022	sql	t-sql	language-reference	CHECKTABLE_TSQL DBCC_CHECKTABLE_TSQL DBCC CHECKTABLE CHECKTABLE	indexed views [SQL Server], DBCC CHECKTABLE page integrity checks [SQL Server] consistency [SQL Server], tables consistency [SQL Server], indexed views DBCC CHECKTABLE statement integrity [SQL Server] low overhead checks table integrity checks [SQL Server]	TSQL

DBCC CHECKTABLE (Transact-SQL)

[!INCLUDE SQL Server SQL Database Azure SQL Managed Instance]

Checks the integrity of all the pages and structures that make up the table or indexed view.

:::image type=»icon» source=»../../includes/media/topic-link-icon.svg» border=»false»::: Transact-SQL syntax conventions

Syntax

DBCC CHECKTABLE
(
    table_name | view_name
    [ , { NOINDEX | index_id }
     | , { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD }
    ]
)
    [ WITH
        { [ ALL_ERRORMSGS ]
          [ , EXTENDED_LOGICAL_CHECKS ]
          [ , NO_INFOMSGS ]
          [ , TABLOCK ]
          [ , ESTIMATEONLY ]
          [ , { PHYSICAL_ONLY | DATA_PURITY } ]
          [ , MAXDOP = number_of_processors ]
        }
    ]

[!INCLUDEsql-server-tsql-previous-offline-documentation]

Arguments

table_name | view_name

The table or indexed view for which to run integrity checks. Table or view names must comply with the rules for identifiers.

NOINDEX

Specifies that intensive checks of nonclustered indexes for user tables shouldn’t be performed. This decreases the overall execution time. NOINDEX doesn’t affect system tables because the integrity checks are always performed on all system table indexes.

index_id

The index identification (ID) number for which to run integrity checks. If index_id is specified, DBCC CHECKTABLE runs integrity checks only on that index, together with the heap or clustered index.

REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD

Specifies that DBCC CHECKTABLE repair the found errors. To use a repair option, the database must be in single-user mode.

• REPAIR_ALLOW_DATA_LOSS

  Tries to repair all reported errors. These repairs can cause some data loss.

• REPAIR_FAST

  Syntax is maintained for backward compatibility only. No repair actions are performed.

• REPAIR_REBUILD

  Performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in nonclustered indexes, and more time-consuming repairs, such as rebuilding an index.

  This argument doesn’t repair errors involving FILESTREAM data.

[!IMPORTANT]
Use the REPAIR options only as a last resort. To repair errors, we recommend restoring from a backup. Repair operations don’t consider any of the constraints that may exist on or between tables. If the specified table is involved in one or more constraints, we recommend running DBCC CHECKCONSTRAINTS after a repair operation. If you must use REPAIR, run DBCC CHECKTABLE without a repair option to find the repair level to use. If you use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKTABLE with this option.

ALL_ERRORMSGS

Displays an unlimited number of errors. All error messages are displayed by default. Specifying or omitting this option has no effect.

EXTENDED_LOGICAL_CHECKS

If the compatibility level is 100, introduced in [!INCLUDEsql2008-md], performs logical consistency checks on an indexed view, XML indexes, and spatial indexes, where present.

For more information, see Performing Logical Consistency Checks on Indexes in the Remarks section later in this article.

NO_INFOMSGS

Suppresses all informational messages.

TABLOCK

Causes DBCC CHECKTABLE to obtain a shared table lock instead of using an internal database snapshot. TABLOCK will cause DBCC CHECKTABLE to run faster on a table under heavy load, but decreases the concurrency available on the table while DBCC CHECKTABLE is running.

ESTIMATEONLY

Displays the estimated amount of tempdb space needed to run DBCC CHECKTABLE with all the other specified options.

PHYSICAL_ONLY

Limits the checking to the integrity of the physical structure of the page, record headers and the physical structure of B-trees. Designed to provide a small overhead check of the physical consistency of the table, this check can also detect torn pages, and common hardware failures that can compromise data. A full run of DBCC CHECKTABLE may take considerably longer than in earlier versions. This behavior occurs because of the following reasons:

• The logical checks are more comprehensive.
• Some of the underlying structures to be checked are more complex.
• Many new checks have been introduced to include the new features.

[!INCLUDE sql-b-tree]

Therefore, using the PHYSICAL_ONLY option may cause a much shorter run-time for DBCC CHECKTABLE on large tables and is therefore recommended for frequent use on production systems. We still recommend that a full run of DBCC CHECKTABLE is performed periodically. The frequency of these runs depends on factors specific to individual businesses and production environments. PHYSICAL_ONLY always implies NO_INFOMSGS and isn’t allowed with any one of the repair options.

[!NOTE]
Specifying PHYSICAL_ONLY causes DBCC CHECKTABLE to skip all checks of FILESTREAM data.

DATA_PURITY

Causes DBCC CHECKTABLE to check the table for column values that aren’t valid or out-of-range. For example, DBCC CHECKTABLE detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with scale or precision values that aren’t valid.

Column-value integrity checks are enabled by default and don’t require the DATA_PURITY option. For databases upgraded from earlier versions of [!INCLUDEssNoVersion], you can use DBCC CHECKTABLE WITH DATA_PURITY to find and correct errors on a specific table; however, column-value checks on the table aren’t enabled by default until DBCC CHECKDB WITH DATA_PURITY has been run error free on the database. After this, DBCC CHECKDB and DBCC CHECKTABLE check column-value integrity by default.

Validation errors reported by this option can’t be fixed by using DBCC repair options. For information about manually correcting these errors, see Knowledge Base article 923247: Troubleshooting DBCC error 2570 in SQL Server 2005 and later versions.

If PHYSICAL_ONLY is specified, column-integrity checks aren’t performed.

MAXDOP

Applies to: [!INCLUDEssSQL14] Service Pack 2 and later versions.

Overrides the max degree of parallelism configuration option of sp_configure for the statement. The MAXDOP can exceed the value configured with sp_configure. If MAXDOP exceeds the value configured with Resource Governor, the Database Engine uses the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP (Transact-SQL). All semantic rules used with the max degree of parallelism configuration option are applicable when you use the MAXDOP query hint. For more information, see Configure the max degree of parallelism Server Configuration Option.

[!NOTE]
If MAXDOP is set to zero then the server chooses the max degree of parallelism.

Remarks

[!NOTE]
To perform DBCC CHECKTABLE on every table in the database, use DBCC CHECKDB.

For the specified table, DBCC CHECKTABLE checks for the following:

• Index, in-row, LOB, and row-overflow data pages are correctly linked.
• Indexes are in their correct sort order.
• Pointers are consistent.
• The data on each page is reasonable, included computed columns.
• Page offsets are reasonable.
• Every row in the base table has a matching row in each nonclustered index, and vice-versa.
• Every row in a partitioned table or index is in the correct partition.
• Link-level consistency between the file system and table when storing varbinary(max) data in the file system using FILESTREAM.

Perform logical consistency checks on indexes

Logical consistency checking on indexes varies according to the compatibility level of the database, as follows:

• If the compatibility level is 100 ([!INCLUDEsql2008-md]) or higher:

  • Unless NOINDEX is specified, DBCC CHECKTABLE performs both physical and logical consistency checks on a single table and on all its nonclustered indexes. However, on XML indexes, spatial indexes, and indexed views only physical consistency checks are performed by default.

  • If WITH EXTENDED_LOGICAL_CHECKS is specified, logical checks are performed on an indexed view, XML indexes, and spatial indexes, where present. By default, physical consistency checks are performed before the logical consistency checks. If NOINDEX is also specified, only the logical checks are performed.

    These logical consistency checks cross check the internal index table of the index object with the user table that it is referencing. To find outlying rows, an internal query is constructed to perform a full intersection of the internal and user tables. Running this query can have a very high effect on performance, and its progress can’t be tracked. Therefore, we recommend that you specify WITH EXTENDED_LOGICAL_CHECKS only if you suspect index issues that are unrelated to physical corruption, or if page-level checksums have been turned off and you suspect column-level hardware corruption.

  • If the index is a filtered index, DBCC CHECKTABLE performs consistency checks to verify that the index entries satisfy the filter predicate.

• Starting with [!INCLUDEsssql16-md], additional checks on persisted computed columns, UDT columns, and filtered indexes won’t run by default to avoid the expensive expression evaluations. This change greatly reduces the duration of CHECKTABLE against databases containing these objects. However, the physical consistency checks of these objects are always completed. Only when EXTENDED_LOGICAL_CHECKS option is specified are the expression evaluations performed, in addition to already present logical checks (indexed view, XML indexes, and spatial indexes) as part of the EXTENDED_LOGICAL_CHECKS option.

• If the compatibility level is 90 ([!INCLUDEssVersion2005]) or less, unless NOINDEX is specified, DBCC CHECKTABLE performs both physical and logical consistency checks on a single table or indexed view and on all its nonclustered and XML indexes. Spatial indexes aren’t supported.

To learn the compatibility level of a database

• View or Change the Compatibility Level of a Database

Internal database snapshot

DBCC CHECKTABLE uses an internal database snapshot to provide the transactional consistency that it must have to perform these checks. For more information, see View the Size of the Sparse File of a Database Snapshot (Transact-SQL) and the DBCC internal database snapshot usage section in DBCC (Transact-SQL).

If a snapshot can’t be created, or TABLOCK is specified, DBCC CHECKTABLE acquires a shared table lock to obtain the required consistency.

[!NOTE]
If DBCC CHECKTABLE is run against tempdb, it must acquire a shared table lock. This is because, for performance reasons, database snapshots are not available on tempdb. This means that the required transactional consistency cannot be obtained.

Check and repair FILESTREAM data

When FILESTREAM is enabled for a database and table, you can optionally store varbinary(max) binary large objects (BLOBs) in the file system. When using DBCC CHECKTABLE on a table that stores BLOBs in the file system, DBCC checks link-level consistency between the file system and database.

For example, if a table contains a varbinary(max) column that uses the FILESTREAM attribute, DBCC CHECKTABLE will check that there is a one-to-one mapping between file system directories and files and table rows, columns, and column values. DBCC CHECKTABLE can repair corruption if you specify the REPAIR_ALLOW_DATA_LOSS option. To repair FILESTREAM corruption, DBCC will delete any table rows that are missing file system data and will delete any directories and files that don’t map to a table row, column, or column value.

Check objects in parallel

By default, DBCC CHECKTABLE performs parallel checking of objects. The degree of parallelism is automatically determined by the query processor. The maximum degree of parallelism is configured in the same manner as that of parallel queries. To restrict the maximum number of processors available for DBCC checking, use sp_configure. For more information, see Configure the max degree of parallelism Server Configuration Option.

Parallel checking can be disabled by using Trace Flag 2528. For more information, see Trace Flags (Transact-SQL).

[!NOTE]
During a DBCC CHECKTABLE operation, the bytes that are stored in a byte-ordered user-defined type column must be equal to the computed serialization of the user-defined type value. If this is not true, the DBCC CHECKTABLE routine will report a consistency error.

[!NOTE]
This feature is not available in every edition of [!INCLUDEssNoVersion]. For more information, see parallel consistency check in the RDBMS manageability section of Editions and supported features of SQL Server 2022.

Understand DBCC error messages

After the DBCC CHECKTABLE command finishes, a message is written to the [!INCLUDEssNoVersion] error log. If the DBCC command successfully executes, the message indicates a successful completion and the amount of time that the command ran. If the DBCC command stops before completing the check because of an error, the message indicates the command was terminated, a state value, and the amount of time the command ran. The following table lists and describes the state values that can be included in the message.

State	Description
0	Error number 8930 was raised. This indicates a metadata corruption that caused the DBCC command to terminate.
1	Error number 8967 was raised. There was an internal DBCC error.
2	A failure occurred during emergency mode database repair.
3	This indicates a metadata corruption that caused the DBCC command to terminate.
4	An assert or access violation was detected.
5	An unknown error occurred that terminated the DBCC command.

Error reporting

A mini-dump file (SQLDUMP<nnnn>.txt) is created in the [!INCLUDEssNoVersion] LOG directory whenever DBCC CHECKTABLE detects a corruption error. When the Feature Usage data collection and Error Reporting features are enabled for the instance of [!INCLUDEssNoVersion], the file is automatically forwarded to [!INCLUDEmsCoName]. The collected data is used to improve [!INCLUDEssNoVersion] functionality.

The dump file contains the results of the DBCC CHECKTABLE command and additional diagnostic output. The file has restricted discretionary access-control lists (DACLs). Access is limited to the [!INCLUDEssNoVersion] service account and members of the sysadmin role. By default, the sysadmin role contains all members of the Windows BUILTINAdministrators group and the local administrator’s group. The DBCC command doesn’t fail if the data collection process fails.

Resolve errors

If DBCC CHECKTABLE reports any errors, we recommend restoring the database from the database backup instead of running REPAIR with one of the REPAIR options. If no backup exists, running REPAIR can correct the errors that are reported. The REPAIR option to use is specified at the end of the list of reported errors. However, that correcting the errors by using the REPAIR_ALLOW_DATA_LOSS option might require that some pages, and therefore data, be deleted.

The repair can be performed under a user transaction to allow the user to roll back the changes that have been made. If repairs are rolled back, the database will still contain errors, and must be restored from a backup. After you have completed all repairs, back up the database.

Result sets

DBCC CHECKTABLE returns the following result set. The same result set is returned if you specify only the table name or any of the options.

DBCC results for 'HumanResources.Employee'.
There are 288 rows in 13 pages for object 'Employee'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC CHECKTABLE returns the following result set if the ESTIMATEONLY option is specified:

Estimated TEMPDB space needed for CHECKTABLES (KB)
--------------------------------------------------
21
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Permissions

User must own the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

Examples

A. Check a specific table

The following example checks the data page integrity of the HumanResources.Employee table in the [!INCLUDEssSampleDBnormal] database.

DBCC CHECKTABLE ('HumanResources.Employee');
GO

B. Perform a low-overhead check of the table

The following example performs a low overhead check of the Employee table in the [!INCLUDEssSampleDBnormal] database.

DBCC CHECKTABLE ('HumanResources.Employee') WITH PHYSICAL_ONLY;
GO

C. Check a specific index

The following example checks a specific index, obtained by accessing sys.indexes.

DECLARE @indid int;
SET @indid = (SELECT index_id
              FROM sys.indexes
              WHERE object_id = OBJECT_ID('Production.Product')
                    AND name = 'AK_Product_Name');
DBCC CHECKTABLE ('Production.Product',@indid);

See also

• DBCC (Transact-SQL)
• DBCC CHECKDB (Transact-SQL)

DBCC CHECKTABLE is used to test the consistency of a table or indexed view. The DBCC CHECKTABLE command performs the same operations as the DBCC CHECKDB command for a table in the database.

You may want to read my article “DBCC CHECKDB Command On SQL Server“.

DBCC CHECKTABLE has several different uses. Let’s examine them all one by one:

Consistency Check:

The following command detects logical and physical errors in the TestTable table in the Test database.

Consistency Check With NOINDEX:

The following query detects errors other than non-clustered indexes in the TestTable table.

Correct Errors With REPAIR_REBUILD:

You can run the DBCC CHECKTABLE command to correct errors in the non-clustereded indexes in the table by using the following command without any loss of data.

The REPAIR_REBUILD operation does not fix any errors that contain filestream data.

Correct Errors With REPAIR_ALLOW_DATA_LOSS:

You can run the DBCC CHECKTABLE command to correct all errors in the table with the risk of data loss by using the following command.

Even if you don’t write ALL_ERRORMSGS at the end of the script, this is the default value. It means to show all errors received for each object.

NO_INFOMSGS at the end of the script means that it does not display informational messages.

NOTE1: After you make sure that the database is suspect, the first thing we need to do before we start the attempt to recover is to get a backup of the suspect database !!!

NOTE2: After running DBCC CHECKTABLE with REPAIR_REBUILD or REPAIR_ALLOW_DATA_LOSS, I recommend that you run the DBCC CHECKCONSTRAINTS command. You can find information about this command in my article “DBCC CHECKCONSTRAINTS Command On SQL Server“.

Logical Consistency Check For Indexed view, XML index, and Spatial Indexes:

By default, the DBCC CHECKTABLE command only tests the physical consistency for indexed view, XML index, and spatial indexes.

You can also use the following command to check the logical consistency for indexed view, XML index, and spatial indexes in tables in the databases with compatibility level SQL Server 2008 and later. You may want to read my article “What is SQL Server Database Compatibility Level and How To Change Database Compatibility Level“.

The DBCC CHECKTABLE command uses internal database snapshot. This means that there is no blocking when you run this command.

You may want to read the article named “What is Database Snapshot On SQL Server”

Consistency Check With Lock:

If you run using the following command, this command puts the shared table lock on the table instead of using the internal database snapshot. You may want to read my article “SQL Server Lock Types“.

Calculate Necessary Space For Consistency Check:

If you use the following command, no consistency check is performed. Only the amount of space required in tempdb is calculated so that a consistency test can be performed, and it produces an output as follows.

Only PHYSICAL Consistency Check:

When you run it using the command below, only physical consistency check is performed. It is not done logically.

If you ask me, it doesn’t make sense to run it using this command. Because it doesn’t control its logical consistency. Of course, they offered us such an option to be used in special cases.

With the PHYSICAL_ONLY command, you cannot perform any REPAIR operation.

DATA_PURITY Option:

When you run it using the command below, it is checked whether the values of the columns in the tables in the database are compatible with the data type of the column. If your database is SQL Server 2005 or higher, the column values are checked automatically and no DATA_PURITY option is required.

For faster completion of DBCC CHECKTABLE, you can increase maxdop (max degree of paralellism) from server configurations before running DBCC CHECKTABLE. If your system is an oltp system and consists entirely of small transactions, increasing the maxdop may slow the performance of the running system. Therefore, after increasing the maxdop, you should inspect the system and undo the change in any performance problem. To correctly set the maxdop value, you can use the article “sp_configure (Server-Level Configurations in SQL Server)“.

DBCC CHECKTABLE For an Index:

If you want, you can run the DBCC CHECKTABLE command for just one index. In the script below, we test the clustered index called PK_TestTable in the TestTable table.

mysqlcheck offers an efficient way to perform maintenance of tables and databases in MySQL. It can check tables, repair them if found corrupt, optimize them and perform combined operations of checking, repairing, and optimizing altogether. mysqlcheck command-line utility in MySQL is intended to be used when the mysqld server is running. It can execute CHECK TABLE, REPAIR TABLE, ANALYZE TABLE and OPTIMIZE TABLE statements in the most convenient way for the user.

In this blog, we explore the different ways of using mysqlcheck command line for checking, repairing, and optimizing tables and databases in MySQL. Start verifying and taking corrective actions for your tables and databases using this guide. 

Table of Contents

• What is MySQL?
  • Business Benefits of Using MySQL
• What is mysqlcheck Client?
  • mysqlcheck Command
• Using mysqlcheck for MySQL Database Tables
  • Part 1: Creating a MySQL Database Back-Up
  • Part 2: Running mysqlcheck Command
    • Running mysqlcheck to Analyze a Table in MySQL Database
    • Running mysqlcheck to Repair Tables in a MySQL Database
    • Running mysqlcheck to Optimize Tables in a MySQL Database
  • Part 3: Using mysqlcheck in Compound Commands
    • Running mysqlcheck to Optimize and Repair Tables in a MySQL Database
  • Part 4: mysqlcheck Command Modifications
• Conclusion

What is MySQL?

MySQL is the world’s most popular Open Source Relational Database Management System (RDBMS) used by all types of Small and Medium-Size Businesses (SMBs) and large enterprises. MySQL was initially developed, marketed, and supported by MySQL AB, a Swedish company but later got acquired by Sun Microsoft Systems (currently known as Oracle Corporation). Just like other typical databases, MySQL can store user/business/customer information in the form of rows and columns in a table. It provides referential integrity between rows and columns of various tables and processes user requests using SQL. 

MySQL holds a highly regarded name in businesses working with databases and Cloud-based Data Warehousing solutions. It’s scalable, reliable, and user-friendly. It also works cross-platform which means that users can run MySQL on Linux and Windows, and restore backups from the other platform. 

Business Benefits of Using MySQL

MySQL is popular all over the world and is used by leading tech giants owing to the following reasons:

Easy to Install and Deploy

Businesses can set up and run SQL queries on their data using MySQL in minutes. MySQL enables them to deliver new applications faster than proprietary databases.

High Speed

It’s a no-brainer that if you are working with large datasets, you wouldn’t want to spend an extensive amount of time working with datasets and tables. Unlike other databases, MySQL is comparatively faster and can query information from large datasets helping in business intelligence activities.

Read more on the top 10 MySQL ETL tools for your business here. 

Industry Standards

Whether you are a developer who is required for rapid development of software or a freelancer who seeks to work with databases, MySQL has been in use for over 20 years and you can be sure of using MySQL as a fully integrated transaction-safe, ACID-compliant database.

Reliability and High Availability

MySQL has a well-established reputation for reliability among its 5 million user base. In addition to reliability, MySQL Cluster gives 99.999 percent availability.

Multiple Platform Support

MySQL can be used on 20 platforms including Linux, Solaris, AIX, HP-UX, Windows, and Mac OS X. This provides organizations with complete flexibility in delivering a solution on the platform of their choice.

What is mysqlcheck Client?

mysqlcheck is a table maintenance program to check, repair, optimize, or analyze multiple tables from the command line. It works with InnoDB, MyISAM, and ARCHIVE tables and provides three levels of checking:

• Table-specific
• Database-specific
• All databases

mysqlcheck table client for MySQL consists of four SQL statements to perform the table maintenance action:

• CHECK TABLE, 
• REPAIR TABLE, 
• ANALYZE TABLE, and 
• OPTIMIZE TABLE

While performing table repair or analysis, it is important to bear in mind that table maintenance operations like mysqlcheck can become time-consuming processes especially when you have a large number of entries in your tables. If you use the –databases or –all-databases option to process all tables in one or more databases, a mysqlcheck call might take you a long time to complete. 

Note: For performing mysqlcheck operation, you must run the mysqld server. This means that you do not have to stop the server to perform table maintenance. mysqlcheck is different from myisamchk and aria_chk utilities that don’t require the server to be running.

mysqlcheck Command

There are three ways to use mysqlcheck command-line tool:

./client/mysqlcheck [OPTIONS] database [tables]
./client/mysqlcheck [OPTIONS] --databases DB1 [DB2 DB3...]
./client/mysqlcheck [OPTIONS] --all-databases

Using mysqlcheck for MySQL Database Tables

Important Note: Before we proceed with table check, repair, or analysis operations, it’s best advised to create a backup of your table(s), for some circumstances might lead to a potential data loss. 

As a result, we’ve split this tutorial into four sections for your convenience:

• Part 1: Creating a MySQL Database Back-Up
• Part 2: Running mysqlcheck Command
• Part 3: Using mysqlcheck in Compound Commands
• Part 4: mysqlcheck Command Modifications

Part 1: Creating a MySQL Database Back-Up

To create a backup of all your existing MySQL databases, follow these steps:

Step 1: Log in to your MySQL server using Secure Shell (SSH).

Step 2: Stop MySQL server using the appropriate command based on your Linux distribution:

For CentOS and Fedora, type:

service mysqld stop

For Debian and Ubuntu, type:

service mysql stop

Step 3: Input the following command to copy all of your databases to a directory name based on the current time.

cp -rfv /var/lib/mysql /var/lib/mysql$(date +%s)

Step 4: Restart MySQL server with the command appropriate for your Linux distribution:

For CentOS and Fedora, type:

service mysqld start

For Debian and Ubuntu, type:

service mysql start

Part 2: Running mysqlcheck Command

To use the mysqlcheck table command and check tables, follow these steps:

Step 1: As an administrator, change your directory to MySQL Data Directory.

cd /var/lib/mysql

Step 2: Now type in the mysqlcheck command to check for an existing table in a database. In our example, we are checking for a table called “email” under the database “customers”.

$ mysqlcheck -c customers email
customers.email                         OK

Notice our result. If a table passes the check, mysqlcheck displays OK for the table.

Not only this, mysqlcheck command can be used to CHECK (-c, -m, -C), REPAIR (-r), ANALYZE (-a), or OPTIMIZE (-o) table within your databases. The -c, -r, -a, and -o options work exclusively.

Some of the options (like -e (–extended) or -q (–quick)) can also be used at the same time but not all options are supported by all storage engines.

Running mysqlcheck to Analyze a Table in MySQL Database

As an example, the following command analyzes the “email” table within our “customers” database:

$ mysqlcheck -a customers email
customers.email                         OK

Running mysqlcheck to Repair Tables in a MySQL Database

The following command repairs all tables in the “customers” and “leads” databases:

$ mysqlcheck -r --databases customers leads

Note: If you see a note stating: The storage engine for the table doesn’t support repair it means that you are doing REPAIR on an InnoDB.

Running mysqlcheck to Optimize Tables in a MySQL Database

The following mysqlcheck database command optimizes all tables in all your MySQL databases.

$ mysqlcheck -o --all-databases

For user reference, this is a table showcasing the most used options for the mysqlcheck database command.

Option	Description
-c, –check	Check the tables for errors
-a, –analyze	Analyze the tables
-o, –optimize	Optimize the tables
-r, –repair	Perform a repair that can fix almost anything except unique keys that are not unique
–auto-repair	If a checked table is corrupted, automatically fix it
-A, –all-databases	Check all tables in all databases. This is the same as –databases with all databases selected
-B, –databases	Process all tables in the named databases. With this option, all name arguments are regarded as database names, not as table names
–tables	Overrides the –databases or -B option such that all name arguments following the option are regarded as table names
-g, –check-upgrade	Check tables for version-dependent changes. It may be used with –auto-repair to correct tables requiring version-dependent updates
–compress	Compress all information sent between client and server
–debug-info	Print debugging information, memory, and CPU statistics when the program exits
-e, -–extended	Check and repair tables
-q, –quick	The fastest method of checking
-?, –help	Display a help message and exit

A more extensive list of other mysqlcheck database command options can be found on this page.

Likewise, these mysqlcheck table/database options can be combined together to perform a joint operation. Have a look at how this can be performed in the following section.

Part 3: Using mysqlcheck in Compound Commands

The mysqlcheck command-line utility can be extended for giving compound commands. For instance, let’s assume a case where there is a need to repair and optimize the “email” table from our previously stated “customer” database. 

mysqlcheck table command options like  -c (check), -r (repair), -a (analyze), and -o (optimize) options work exclusively and can be used concurrently in the same mysqlcheck command.

Running mysqlcheck to Optimize and Repair Tables in a MySQL Database

The following mysqlcheck command checks, optimizes, and auto-repairs all corrupted tables in the “customer” database. The auto repair option automatically fixes a checked table if found corrupted.

$ mysqlcheck --auto-repair -o customers

And the following mysqlcheck command optimizes and auto-repairs all tables in all your MySQL databases.

$ mysqlcheck --auto-repair -o --all-databases

Part 4: mysqlcheck Command Modifications

The command mysqlcheck can be altered, changing its default behavior from checking tables (–check) to repairing or optimizing tables. This can be done by changing the binary “check” and replacing it with “repair”, or “analyze”, or “optimize”.

These are the commands you get after substitution:

Command	Meaning
mysqlrepair	The default option is –repair
mysqlanalyze	The default option is –analyze
mysqloptimize	The default option is –optimize

All these commands when invoked would perform the same operation as mysqlcheck -[option] when used. 

If you would like to learn about MySQL database export command-line utility, see our blog on MySQL Export Database Command Line: 3 Easy Methods. If you would like to know more about MySQL Analytics tools, visit our other informative blog here- MySQL Analytics Tools: A Quick Guide.

Conclusion

mysqlcheck table/database commands are ideal for automated optimizations of MySQL databases and tables. The more your database expands and the number of tables increases, the more likely it is that your tables/databases will encounter mistakes from time to time. In those circumstances, mysqlcheck can be a lifesaver.

Managing growing databases and the number of associated processes like business analytics isn’t an easy job. We find numerous business teams trapped and trying to figure out how to examine their data in a timely and efficient manner. One way of doing this is migrating your data to a Cloud-based Data Warehouse. But what if we told you that you can manage all your source connections from a single place? 

Our tool Hevo with its strong integration with 100+ Sources & BI tools such as MySQL, PostgreSQL, MS SQL Server, MySQL compatible Aurora, Google Cloud SQL for MySQL allows you to not only export data from sources & load data in the destinations, but also transform & enrich your data, & make it analysis-ready so that you can focus only on your key business needs and perform insightful analysis using BI tools. 

Visit our Website to Explore Hevo

Hevo lets you migrate your data from your favorite applications to any Data Warehouse of your choice like Amazon Redshift, Snowflake, Google BigQuery, or Firebolt within minutes with just a few clicks.

Want to take Hevo for a spin? Sign Up here for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also check Hevo’s pricing plans on this page. 

Share your most frequently used MySQL commands, as well as those for your tables and databases. Suggest to us any more MySQL topics you’d like us to cover in the comments below. We’d appreciate hearing from you.