Database Fault Repair: Locating and Handling Table Corruption and Connection Limits

There are two typical manifestations of database issues: one is query errors (table corruption), and the other is connection failures (connection limit reached). Both can cause site-wide errors, but their handling methods are completely different.

Distinguish between them using a single command:

# Can connect but queries fail → Table corruption
mysql -e "SELECT 1;" && echo "Connection normal"

# Directly reports Too many connections → Connection limit reached

Type 1: Table Corruption

Identification

# Look for clues in the error log
tail -100 /var/log/mysqld.log | grep -iE 'crash|corrupt|repair'

# Check specific tables
mysql -e "CHECK TABLE dbname.tablename;"
mysql -e "CHECK TABLE dbname.tablename FAST QUICK;"

Typical error message:

Table './dbname/tablename' is marked as crashed and should be repaired

Therefore, when encountering table corruption, first confirm if it is MyISAM. If the entire database uses InnoDB but reports corruption, be wary of more serious issues (such as disk or memory faults).

# Check which engine the table uses
mysql -e "SELECT table_name, engine FROM information_schema.tables
          WHERE table_schema='dbname';"

Repair

# Method 1: Online repair (effective for MyISAM)
mysql -e "REPAIR TABLE dbname.tablename;"

# Method 2: Command-line tool (requires stopping service or ensuring table is unused)
myisamchk -r /var/lib/mysql/dbname/tablename.MYI

# For severe corruption
myisamchk -o /var/lib/mysql/dbname/tablename.MYI

# For InnoDB tables, you can try:
mysql -e "ALTER TABLE dbname.tablename ENGINE=InnoDB;"   # Rebuild table

Back up table files before repairing. REPAIR may cause data loss; having a backup provides a fallback.

# Back up table files (most stable after stopping DB or locking tables)
cp -a /var/lib/mysql/dbname/tablename.* /tmp/table-backup/

Type 2: Connection Limit Reached

Identification

ERROR 1040 (08004): Too many connections

# Current connections vs. limit
mysql -e "SHOW VARIABLES LIKE 'max_connections';"
mysql -e "SHOW STATUS LIKE 'Threads_connected';"
mysql -e "SHOW STATUS LIKE 'Max_used_connections';"   # Historical peak, very valuable for reference

Max_used_connections is the most diagnostic metric—it records the historical peak. If the peak is close to the limit, it indeed means resources are running out; if the peak is low but full connection errors occur, it indicates a short-term burst.

Emergency: Connect First

When connections are full, you cannot connect, appearing like a deadlock. However, databases usually reserve a few connections for administrators, so you can still enter using a high-privilege account:

# Connect using admin account (with appropriate privileges)
mysql -u root -p

# Once inside, see who holds the connections
mysql -e "SELECT id, user, host, db, command, time, state
          FROM information_schema.processlist ORDER BY time DESC LIMIT 20G"

Three Causes

# Count connection status distribution (see if Sleep or Query dominates)
mysql -e "SELECT command, COUNT(*) FROM information_schema.processlist
          GROUP BY command ORDER BY 2 DESC;"

# Find connections that have been sleeping for a long time
mysql -e "SELECT id, user, host, time FROM information_schema.processlist
          WHERE command='Sleep' AND time > 300;"

Increasing Connection Limit (and Why It’s Not Enough)

# Temporary effect (lost on restart)
mysql -e "SET GLOBAL max_connections = 500;"

# Permanent: Write to config file
# my.cnf:  max_connections = 500

Increasing the connection limit only alleviates the problem, it does not fix it. If the cause is connection leaks or slow queries, increasing the limit will just fill up again, and since each connection consumes memory, setting it too high may trigger OOM (Out Of Memory).

Correct order: First determine if there are many Sleep or Query states, then address the root cause accordingly. Adjusting parameters is only a temporary measure to buy time.

# Batch kill long-sleeping connections (for emergency use, not a long-term solution)
mysql -Nse "SELECT id FROM information_schema.processlist
            WHERE command='Sleep' AND time > 600;" 
| while read id; do mysql -e "KILL $id"; done

Decision Tree

Database Fault
├─ Cannot Connect (Too many connections)
│  ├─ Enter using admin account (usually reserved connections exist)
│  ├─ Check status distribution
│  │  ├─ Many Sleep + long time → Connection leak, fix code
│  │  ├─ Many Query + long time → Slow query, optimize
│  │  └─ All in normal use      → Genuine high concurrency, scale/pool
│  └─ Temporarily increase max_connections to buy time (not a fix)
└─ Can Connect but Query Errors
   ├─ Check error log for crash/corrupt
   ├─ Confirm with CHECK TABLE
   ├─ Backup table files first
   └─ MyISAM → REPAIR TABLE / myisamchk
      InnoDB → Usually self-heals, otherwise rebuild table

Scenario Deduction: Site Reports Database Connection Failure, Works After Restart Then Fails Again

Symptom: The site大面积 reports “Database Connection Failed”.
Restarting the database service restores it immediately, but it fails again after a while, with intervals getting shorter.

1. First, identify which category the error belongs to → The prompt falls between “Connection Refused” and “Table Missing/Corrupted”.
   These require completely different handling. This step determines whether to adjust connections or repair data.
2. Confirm if the connection limit has been reached → The limit is reached,
   causing all new connections to be rejected. This explains why it works for a while after restart.
3. Find out why these connections are not released → A large number of connections remain idle for a long time,
   pointing to the application layer not closing them correctly.
4. Temporarily raise the limit to an observable level → Increasing the limit buys observation time,
   but this is a tactic, not a solution.
5. Investigate if another error caused by table corruption is involved → Check the tables mentioned in the error,
   to distinguish between connection issues and data issues.
6. Fix the application layer and preserve evidence → Take snapshots to preserve evidence before cleaning up long-idle connections.

# ① First check if connections are full
mysql -e "SHOW STATUS LIKE 'Threads_connected'; SHOW VARIABLES LIKE 'max_connections';"
mysqladmin status 2>/dev/null | head -2

# ② Which connections are idle for a long time
mysql -e "SELECT id,user,host,command,time,state FROM information_schema.processlist
          WHERE command='Sleep' ORDER BY time DESC LIMIT 20;"

# ③ Temporary limit (will be lost on restart, must also change config file)
mysql -e "SET GLOBAL max_connections=500;"
# Also change in config file: max_connections = 500

# ④ Rule out another cause: Table status check
mysqlcheck -c dbname tablename 2>&1 | tail -5

Root Cause: The application layer did not correctly release database connections, causing the connection count to be continuously occupied until the limit was reached.
Restarting only clears the accumulation; since the root cause remains, the interval between failures gets shorter.

Two key points to remember:

• When encountering database errors, first distinguish between “Cannot Connect” and “Table Issues”.
  The former requires adjusting connection limits and fixing app-layer release; the latter requires table repair. They are easily confused but handled differently.
• Increasing the connection limit is only a way to buy time.
  Without fixing the app-layer release issue, raising the limit only delays the next occurrence.

8 Common Pitfalls

1. Restarting the database immediately: Restores service but leads to recurrence and loss of forensic data.
2. Increasing parameters when connections are full: Masks the root cause and may trigger OOM due to memory consumption.
3. Ignoring Max_used_connections: The historical peak indicates whether resources were truly insufficient.
4. Not backing up before REPAIR: May result in data loss.
5. Ignoring storage engines: InnoDB reporting corruption warrants caution for more serious issues.
6. Ignoring slow queries: The root cause of connection accumulation is often slow queries.
7. Batch KILL without conditions: May kill normal business connections currently executing.
8. Old tables never converted to InnoDB: MyISAM has a high corruption probability and lacks transaction support.

Real Customer Feedback (From Cloud Market Reviews and Consultations for Similar Products)

The following excerpts are from real buyer reviews and consultations for similar operations services on Alibaba Cloud / Tencent Cloud / Huawei Cloud Cloud Markets (service providers include our store and peers, provided solely for reference of real feedback, not fictitious tickets from this site):

b****4 Rating: 5.0 Jun 23, 2025 16:57 b****4 Rating: 5.0 Jun 23, 2025 16:56 Jian****Wei Rating: 5.0 Jan 20, 2021 15:03 Excellent technology, very fast! It turned out to be a trojan causing Baidu global hijacking!!!! f****u Rating: 5.0 Jan 02, 2021 20:43 Thanks for assisting in maintaining Yuncai’s normal operation. 1****1 Rating: 5.0 Nov 20, 2020 10:03 Consistently good service 1****1 Rating: 5.0 Nov 17, 2020 09:13 Thanks for the consistently good service. 1****1 Rating: 5.0 Sep 25, 2020 15:42 Very grateful. 1****1 Rating: 5.0 Aug 24, 2020 15:34 Very grateful to your company’s technical staff 1****1 Rating: 5.0 Jul 24, 2020 09:49 Very grateful! 1

10***77 2022-09-19 5.0 Affordable price | Skilled technology | Patient and professional Received filing rectification notice, found this service exactly when needed, handled quickly 10***49 2022-04-12 5.0 On-time delivery | Affordable price | Skilled technology Needed rectification, configuration was fast, not bad