डेटाबेस डिबगिंग

डेटाबेस डिबगिंग तकनीक

XOOPS अनुप्रयोगों में SQL क्वेरी और डेटाबेस समस्याओं को डीबग करने के तरीके और उपकरण।

डायग्नोस्टिक फ़्लोचार्ट

flowchart TD
    A[Database Problem] --> B{Query Runs?}
    B -->|No| C[Check Syntax]
    B -->|Yes| D{Correct Results?}

    C --> C1[Run in MySQL CLI]
    C1 --> C2[Check EXPLAIN plan]
    C2 --> C3[Fix SQL]

    D -->|No| E[Check Logic]
    D -->|Yes| F{Performance OK?}

    E --> E1[Log result set]
    E1 --> E2[Verify data]
    E2 --> E3[Fix query]

    F -->|Slow| G[Optimize]
    F -->|Fast| H[Debugging Complete]

    G --> G1[Add indexes]
    G1 --> G2[Refactor query]
    G2 --> G3[Retest]

क्वेरी लॉगिंग सक्षम करें

विधि 1: XOOPS डिबग मोड

<?php
// In mainfile.php
define('XOOPS_DEBUG_LEVEL', 2);

// Now all queries appear in xoops_log table
// Or in files: xoops_data/logs/
?>

परिणाम जांचें:

# View logs
tail -100 xoops_data/logs/*.log

# Or query database
SELECT * FROM xoops_log ORDER BY created DESC LIMIT 20;

विधि 2: MySQL धीमी क्वेरी लॉग

/etc/mysql/my.cnf में सक्षम करें:

[mysqld]
# Enable slow query logging
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1          # Log queries > 1 second
log_queries_not_using_indexes = 1

पुनः प्रारंभ करें MySQL:

sudo systemctl restart mysql
# or
sudo systemctl restart mariadb

लॉग देखें:

tail -100 /var/log/mysql/slow.log

# Or analyze with mysqldumpslow
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

विधि 3: सामान्य क्वेरी लॉग

सभी प्रश्नों के लिए सक्षम करें (सावधान: बड़ी लॉग फ़ाइलें):

-- Enable
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'FILE';
SET GLOBAL general_log_file = '/var/log/mysql/general.log';

-- Disable
SET GLOBAL general_log = 'OFF';

-- View
SHOW VARIABLES LIKE 'general_log%';

कोड में SQL डीबग करें

लॉग क्वेरी निष्पादन

<?php
require_once 'mainfile.php';

$ray = ray();  // If using Ray debugger

// Execute query
$query = "SELECT u.uid, u.uname, COUNT(a.id) as total_articles
          FROM xoops_users u
          LEFT JOIN xoops_articles a ON u.uid = a.author_id
          GROUP BY u.uid
          ORDER BY total_articles DESC";

$ray->label('Query')->info($query);

$result = $GLOBALS['xoopsDB']->query($query);

if (!$result) {
    $ray->error("SQL Error: " . $GLOBALS['xoopsDB']->error);
    exit;
}

// Log results
$data = [];
while ($row = $result->fetch_assoc()) {
    $data[] = $row;
}

$ray->label('Results')->dump($data);
$ray->info("Found " . count($data) . " rows");
?>

क्वेरी प्रदर्शन को मापें

<?php
$db = $GLOBALS['xoopsDB'];
$ray = ray();

// Measure execution time
$start = microtime(true);

$query = "SELECT * FROM xoops_articles LIMIT 1000";
$result = $db->query($query);

$exec_time = (microtime(true) - $start) * 1000;  // milliseconds

$ray->info("Query executed in: {$exec_time}ms");

// Log slow queries
if ($exec_time > 100) {  // Alert if > 100ms
    $ray->warning("Slow query detected: {$exec_time}ms");
    $ray->info($query);
}
?>

क्वेरी परिणाम सत्यापित करें

<?php
$db = $GLOBALS['xoopsDB'];
$ray = ray();

$query = "SELECT * FROM xoops_articles WHERE author_id = 5";
$result = $db->query($query);

// Check if query succeeded
if (!$result) {
    $ray->error("Query failed: " . $db->error);
    exit;
}

// Get row count
$count = $result->num_rows;
$ray->info("Query returned: $count rows");

// Fetch results
$articles = [];
while ($row = $result->fetch_assoc()) {
    $articles[] = $row;
}

// Verify data
if (empty($articles)) {
    $ray->warning("No articles found for author 5");
} else {
    $ray->success("Found " . count($articles) . " articles");
    $ray->dump($articles);
}
?>

क्वेरी प्रदर्शन का विश्लेषण करें

EXPLAIN कमांड

क्वेरी निष्पादन का विश्लेषण करने के लिए EXPLAIN का उपयोग करें:

-- Analyze a query
EXPLAIN SELECT * FROM xoops_articles WHERE author_id = 5;

-- With extended information
EXPLAIN EXTENDED SELECT * FROM xoops_articles WHERE author_id = 5;

-- JSON format (shows more details)
EXPLAIN FORMAT=JSON SELECT * FROM xoops_articles WHERE author_id = 5\G

जांचने के लिए मुख्य क्षेत्र:

type: ALL           (bad) - Full table scan
      INDEX         (ok) - Index scan
      ref/const     (good) - Direct index lookup
      range         (ok) - Range scan using index

possible_keys:      Indexes available
key:                Index actually used
key_len:            Length of index used
rows:               Estimated rows examined
Extra:              Additional info (Using where, Using index, etc.)

उदाहरण विश्लेषण

-- Slow query without index
EXPLAIN SELECT * FROM xoops_articles WHERE author_id = 5;

+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | articles | ALL  | NULL          | NULL | NULL    | 1000 | Using where |
+----+-------------+----------+------+---------------+------+-------+------+-------+-------------+
                                      ↑
                          No index available!

-- After adding index
ALTER TABLE xoops_articles ADD INDEX (author_id);

EXPLAIN SELECT * FROM xoops_articles WHERE author_id = 5;

+----+-------------+----------+------+---------------+-----------+---------+-------+------+
| id | select_type | table    | type | possible_keys | key       | key_len | rows  | Extra|
+----+-------------+----------+------+---------------+-----------+---------+-------+------+
|  1 | SIMPLE      | articles | ref  | author_id     | author_id | 4       | 10    |
+----+-------------+----------+------+---------------+-----------+---------+-------+------+
                                                              ↑
                                      Using index - much faster!

सामान्य SQL मुद्दे

1. एन+1 क्वेरी समस्या

समस्या:

<?php
// WRONG: Multiple queries in loop
$authors = $db->query("SELECT uid FROM xoops_users LIMIT 100");
while ($author = $authors->fetch_assoc()) {
    // This executes 100 times!
    $articles = $db->query(
        "SELECT COUNT(*) FROM xoops_articles WHERE author_id = " . $author['uid']
    );
    echo $articles->fetch_row()[0];
}
?>

समाधान: JOIN का उपयोग करें

<?php
// CORRECT: One query
$result = $db->query("
    SELECT u.uid, u.uname, COUNT(a.id) as total
    FROM xoops_users u
    LEFT JOIN xoops_articles a ON u.uid = a.author_id
    GROUP BY u.uid
    LIMIT 100
");

while ($row = $result->fetch_assoc()) {
    echo $row['total'];
}
?>

2. गुम अनुक्रमणिकाएँ

पहचानें:

-- Find queries that scan all rows
SELECT * FROM xoops_log
WHERE info LIKE '%type: ALL%'
ORDER BY created DESC;

सूचकांक जोड़ें:

-- Single column index
ALTER TABLE xoops_articles ADD INDEX (author_id);
ALTER TABLE xoops_articles ADD INDEX (created);

-- Composite index
ALTER TABLE xoops_articles ADD INDEX (author_id, created);

-- Unique index
ALTER TABLE xoops_articles ADD UNIQUE INDEX (slug);

3. अकुशल WHERE स्थितियाँ