Database Issues

Fix Common Database Problems and Performance Issues

Database Connection Problems

Step 1: Check Database Credentials

`
SLOS → Settings → Database → Connection Test
`

Connection Test Results:
`json
{
“connection_status”: “failed”,
“errormessage”: “Access denied for user ‘wpuser’@’localhost'”,
“error_code”: 1045,
“suggestions”: [
“Verify database username and password”,
“Check user permissions on database”,
“Ensure database server is running”,
“Check firewall settings”
]
}
`

Step 2: Verify Database Configuration

`
SLOS → Settings → Database → Configuration
`

Database Settings:
`php
// wp-config.php database settings
define(‘DBNAME’, ‘yourdatabase_name’);
define(‘DBUSER’, ‘yourdatabase_user’);
define(‘DBPASSWORD’, ‘yourdatabase_password’);
define(‘DB_HOST’, ‘localhost’);
define(‘DB_CHARSET’, ‘utf8mb4’);
define(‘DB_COLLATE’, ”);
`

Step 3: Test Database Permissions

`sql
— Test database permissions
SHOW GRANTS FOR ‘wp_user’@’localhost’;

— Required permissions for SLOS
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, INDEX
ON yourdatabasename.* TO ‘wp_user’@’localhost’;
`

Table Creation and Structure Issues

Step 1: Check Table Existence

`
SLOS → Settings → Database → Table Status
`

Required SLOS Tables:
`sql
— Check if tables exist
SHOW TABLES LIKE ‘wpslos%’;

— Expected tables:
— wpslosconsent_log
— wpslosdsr_requests
— wpsloscookiescanresults
— wpslosaudit_log
— wpslossettings
`

Step 2: Repair Corrupted Tables

`
SLOS → Settings → Database → Repair Tables
`

Table Repair Process:
`sql
— Repair corrupted tables
REPAIR TABLE wpslosconsent_log;
REPAIR TABLE wpslosdsr_requests;
REPAIR TABLE wpsloscookiescanresults;

— Check table structure
DESCRIBE wpslosconsent_log;
DESCRIBE wpslosdsr_requests;
`

Step 3: Recreate Missing Tables

`
SLOS → Settings → Database → Recreate Tables
`

Table Recreation:

1. Backs up existing data
2. Drops corrupted tables
3. Recreates tables with proper structure
4. Restores data from backup
5. Validates data integrity

Data Corruption Issues


Step 1: Identify Corrupted Records


`
SLOS → Settings → Database → Data Integrity Check
`

Integrity Check Results:
`json
{
“corrupted_records”: [
{
“table”: “wpslosconsent_log”,
“record_id”: 12345,
“issue”: “invalidjsoninconsentcategories”,
“fix_available”: true
},
{
“table”: “wpslosdsr_requests”,
“record_id”: 67890,
“issue”: “missingrequiredfields”,
“fix_available”: true
}
],
“total_corrupted”: 2,
“repair_recommended”: true
}
`

Step 2: Repair Corrupted Data


`
SLOS → Settings → Database → Repair Data
`

Data Repair Options:
`json
{
“repairinvalidjson”: true,
“fixmissingfields”: true,
“removeorphanedrecords”: true,
“validateforeignkeys”: true,
“createbackupbefore_repair”: true
}
`

Step 3: Data Validation


`sql
— Validate consent log data
SELECT
id,
user_id,
consent_timestamp,
JSONVALID(consentcategories) as valid_json,
CASE
WHEN consenttimestamp > NOW() THEN ‘futuredate’
WHEN consenttimestamp < '2020-01-01' THEN 'tooold’
ELSE ‘valid’
END as date_check
FROM wpslosconsent_log
WHERE JSONVALID(consentcategories) = 0
OR consent_timestamp > NOW()
OR consent_timestamp < '2020-01-01'; `

Performance Optimization


Step 1: Database Index Check


`
SLOS → Settings → Database → Index Optimization
`

Missing Indexes:
`sql
— Add performance indexes
CREATE INDEX idxconsentusertimestamp ON wpslosconsentlog (userid, consenttimestamp);
CREATE INDEX idxconsentcategories ON wpslosconsentlog (consentcategories(255));
CREATE INDEX idxdsrstatustimestamp ON wpslosdsrrequests (status, created_timestamp);
CREATE INDEX idxcookiedomain ON wpsloscookiescanresults (domain, scan_date);
`

Step 2: Query Optimization


`
SLOS → Settings → Database → Query Analysis
`

Slow Query Identification:
`sql
— Find slow queries
SELECT
sql_text,
exec_count,
avgtimerwait/1000000000 as avgtimesec,
maxtimerwait/1000000000 as maxtimesec
FROM performanceschema.eventsstatementssummaryby_digest
WHERE sqltext LIKE ‘%slos%’
ORDER BY avgtimerwait DESC
LIMIT 10;
`

Step 3: Table Optimization


`sql
— Optimize tables for better performance
OPTIMIZE TABLE wpslosconsent_log;
OPTIMIZE TABLE wpslosdsr_requests;
OPTIMIZE TABLE wpsloscookiescanresults;

— Analyze table statistics
ANALYZE TABLE wpslosconsent_log;
ANALYZE TABLE wpslosdsr_requests;
`

Storage and Disk Space Issues


Step 1: Check Database Size


`
SLOS → Settings → Database → Storage Analysis
`

Database Size Report:
`sql
— Check database and table sizes
SELECT
table_schema,
table_name,
ROUND((datalength + indexlength) / 1024 / 1024, 2) as size_mb,
table_rows
FROM information_schema.tables
WHERE tableschema = ‘yourdatabase_name’
AND tablename LIKE ‘wpslos_%’
ORDER BY size_mb DESC;
`

Step 2: Data Archiving


`
SLOS → Settings → Database → Data Archiving
`

Archiving Configuration:
`json
{
“archiveolderthan_days”: 365,
“archive_tables”: [
“wpslosconsent_log”,
“wpslosaudit_log”,
“wpsloscookiescanresults”
],
“compression_enabled”: true,
“archive_location”: “/wp-content/uploads/slos-archives/”,
“autocleanuparchives”: true
}
`

Step 3: Log Rotation


`sql
— Set up automatic log rotation
SET GLOBAL log_bin = ‘OFF’;
SET GLOBAL general_log = ‘OFF’;
SET GLOBAL slowquerylog = ‘OFF’;

— Configure MySQL log rotation
[mysqld]
log-bin = /var/log/mysql/mysql-bin
expirelogsdays = 7
maxbinlogsize = 100M
`

Backup and Recovery


Step 1: Create Database Backup


`
SLOS → Settings → Database → Backup → Create Backup
`

Backup Options:
`json
{
“backup_type”: “full”,
“include_data”: true,
“include_structure”: true,
“compression”: “gzip”,
“encryption”: true,
“destination”: “local”,
“retention_copies”: 10
}
`

Step 2: Test Backup Restoration


`
SLOS → Settings → Database → Backup → Test Restore
`

Restore Test Process:

6. Creates temporary database
7. Restores backup data
8. Validates table structure
9. Checks data integrity
10. Compares record counts
11. Cleans up test environment

Step 3: Automated Backup Schedule


`json
{
“backup_schedule”: {
“frequency”: “daily”,
“time”: “02:00”,
“type”: “incremental”,
“retention_days”: 30,
“remote_backup”: {
“enabled”: true,
“provider”: “aws_s3”,
“bucket”: “slos-database-backups”,
“region”: “us-east-1”
}
}
}
`

Migration and Upgrade Issues


Step 1: Pre-Upgrade Backup


`
SLOS → Settings → Database → Migration → Pre-Upgrade Backup
`

Migration Preparation:

12. Full database backup
13. Schema documentation
14. Data validation
15. Rollback plan creation
16. Test environment setup

Step 2: Schema Migration


`
SLOS → Settings → Database → Migration → Run Migration
`

Migration Steps:
`sql
— Add new columns safely
ALTER TABLE wpslosconsent_log
ADD COLUMN IF NOT EXISTS geo_location VARCHAR(10) DEFAULT NULL,
ADD COLUMN IF NOT EXISTS consent_version VARCHAR(10) DEFAULT ‘1.0’;

— Update existing records
UPDATE wpslosconsent_log
SET consent_version = ‘2.0’
WHERE consent_version IS NULL;

— Create new indexes
CREATE INDEX IF NOT EXISTS idxconsentgeo ON wpslosconsentlog (geolocation);
`

Step 3: Data Migration


`php
// Migrate data between versions
function migrateconsentdata() {
global $wpdb;

// Migrate old format to new format
$oldrecords = $wpdb->getresults(“
SELECT * FROM wpslosconsent_log
WHERE consent_categories LIKE ‘a:%’
“);

foreach ($old_records as $record) {
$oldcategories = unserialize($record->consentcategories);
$newcategories = jsonencode($old_categories);

$wpdb->update(
‘wpslosconsent_log’,
[‘consentcategories’ => $newcategories],
[‘id’ => $record->id]
);
}
}
`

Monitoring and Alerts


Step 1: Set Up Database Monitoring


`
SLOS → Settings → Database → Monitoring → Configure
`

Monitoring Metrics:
`json
{
“connectionpoolusage”: {
“alert_threshold”: 80,
“alert_frequency”: “immediate”
},
“slowquerycount”: {
“alert_threshold”: 10,
“alert_frequency”: “hourly”
},
“diskspaceusage”: {
“alert_threshold”: 85,
“alert_frequency”: “daily”
},
“tablelockwaits”: {
“alert_threshold”: 5,
“alert_frequency”: “immediate”
}
}
`

Step 2: Performance Dashboard


`
SLOS → Dashboard → Database → Performance Monitor
`

Dashboard Widgets:

17. Query response times
18. Connection pool status
19. Table sizes and growth
20. Index usage statistics
21. Backup status
22. Error rates

Troubleshooting Tools


Step 1: Database Diagnostic Tools


`
SLOS → Tools → Database → Diagnostics
`

Diagnostic Reports:
`json
{
“connection_test”: {
“status”: “passed”,
“responsetimems”: 45
},
“table_integrity”: {
“status”: “passed”,
“checked_tables”: 15,
“corrupted_tables”: 0
},
“index_analysis”: {
“status”: “warning”,
“missing_indexes”: 3,
“unused_indexes”: 1
},
“performance_metrics”: {
“avgquerytime_ms”: 120,
“slowqueriescount”: 5,
“cachehitratio”: 0.85
}
}
`

Step 2: Query Profiler


`
SLOS → Tools → Database → Query Profiler
`

Profiling Results:
`sql
— Profile slow queries
SET profiling = 1;
— Run your query
SHOW PROFILES;
SHOW PROFILE FOR QUERY 1;
SET profiling = 0;
`

Emergency Recovery


Step 1: Emergency Database Repair


`
SLOS → Emergency → Database → Force Repair
`

Emergency Repair Options:

23. Force table repair (may lose data)
24. Rebuild from last good backup
25. Reset to default state
26. Contact support for recovery

Step 2: Data Recovery Service


`
SLOS → Emergency → Database → Recovery Service
`

Recovery Options:

27. Professional data recovery
28. Backup restoration service
29. Emergency support hotline
30. On-site recovery assistance

Prevention Best Practices


Regular Maintenance


31. Weekly integrity checks
32. Monthly optimization runs
33. Quarterly backup testing
34. Annual performance audits

Monitoring Setup


35. Real-time alerting
36. Performance dashboards
37. Automated reporting
38. Trend analysis

Security Measures


39. Regular security updates
40. Access control auditing
41. Encryption at rest
42. Backup encryption

Support Resources


Documentation


43. Database Configuration
44. Performance Optimization
45. Backup Procedures

Help


46. Database troubleshooting FAQ
47. MySQL optimization guide
48. Emergency recovery procedures
49. Technical support contact