TencentDB for MySQL
- Release Notes and Announcements
- Announcements
- Product Introduction
- Database Architecture
- Database Instance
- Kernel Features
- Kernel Version Release Notes
- Functionality Features
- Performance Features
- Parallel Query
- Security Features
- Stability Features
- Purchase Guide
- Getting Started
- Connecting to MySQL Instance
- Database Audit
- Audit Rule Template
- MySQL Cluster Edition
- Maintenance Management Instance
- Operation Guide
- Instance Management and Maintenance
- Instance Upgrade
- CPU Elastic Expansion
- Read-Only/Disaster Recovery Instances
- Database Proxy
- Database Proxy Kernel Features
- Database Proxy Management
- Automatic Read/Write Separation Feature
- Connection Pool Feature
- Other Features
- Account Management
- Database Management Center (DMC)
- Parameter Configuration
- Network and Security
- Backup and Rollback
- Restoring Database from Backup File
- Monitoring and Alarms
- Operation Logs
- Practical Tutorial
- Calling MySQL APIs in Python
- White Paper
- Performance White Paper
- Performance Test Report
- Troubleshooting
- Connections
- API Documentation
- Data Import APIs
- Rollback APIs
- Parameter APIs
- Making API Requests
- Instance APIs
- Database Proxy APIs
- Database Audit APIs
- Security APIs
- Account APIs
- Backup APIs
- Database APIs
- Monitoring APIs
- Log-related API
- FAQs
- Backup
- Connection and Login
- Instance Upgrade
- Performance and Memory
- Service Agreement
- Reference
Troubleshooting
Last updated: 2022-06-30 10:37:41
The number of tables in a single instance exceeds 1 million
When the number of tables in a single instance exceeds 1 million, it may cause backup failure and affect database monitoring. You need to keep the number of tables in a single instance below 1 million.
Large transactions caused by non-primary key tables
Cause
If there is no primary key table in the instance and the binlog is in row format, when a large amount of data is updated or deleted through a SQL statsment, playback on the slave machine will cause a large transaction, resulting in the backup thread unable to acquire the lock, resulting in backup failure.
Troubleshooting
1. Check all the non-primary key tables that exist in the instance through SQL statements.
select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,ENGINE,TABLE_ROWS from information_schema.tables where (table_schema,table_name) not in (select table_schema,table_name from information_schema.columns where COLUMN_KEY='PRI') and table_schema not in ('sys','mysql','information_schema','performance_schema');
2. Add a primary key to a table without a primary key.
alter table table_name add primary key(`column_name`);
Was this page helpful?
You can also Contact Sales or Submit a Ticket for help.
Yes
No