Subversion Repositories SmartDukaan

-- ============================================================

-- Solr Update Failures Table

-- Purpose: Track failed Solr updates for visibility and retry

-- Created: 2026-01-14

-- ============================================================

-- Create table for tracking Solr update failures

CREATE TABLE IF NOT EXISTS solr_update_failures (

    id INT AUTO_INCREMENT PRIMARY KEY,

    catalog_id INT NOT NULL COMMENT 'Catalog ID that failed to update in Solr',

    item_id INT COMMENT 'Item ID (optional, for reference)',

    change_type VARCHAR(50) COMMENT 'Type of change: PRICE_UPDATED, STATUS_CHANGED, CREATED, DELETED',

    error_message TEXT COMMENT 'Error message from failed Solr update',

    retry_count INT DEFAULT 0 COMMENT 'Number of retry attempts',

    status VARCHAR(20) DEFAULT 'PENDING' COMMENT 'PENDING, RETRYING, FAILED, RESOLVED',

    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 'When the failure occurred',

    last_retry_at DATETIME COMMENT 'Last retry attempt timestamp',

    INDEX idx_status (status),

    INDEX idx_catalog_id (catalog_id),

    INDEX idx_created_at (created_at)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Tracks failed Solr update attempts for monitoring and retry';

-- ============================================================

-- Sample Queries for Dashboard

-- ============================================================

-- Get count of pending failures

-- SELECT COUNT(*) as pending_count FROM solr_update_failures WHERE status = 'PENDING';

-- Get all pending failures

-- SELECT * FROM solr_update_failures WHERE status = 'PENDING' ORDER BY created_at ASC;

-- Get failures that exceeded max retries (need manual intervention)

-- SELECT * FROM solr_update_failures WHERE status = 'FAILED' ORDER BY created_at DESC;

-- Clean up old resolved entries (optional maintenance)

-- DELETE FROM solr_update_failures WHERE status = 'RESOLVED' AND created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);