http://www.magentocommerce.com/bug-tracking/ en MagentoCommerce Copyright {gmt_date format="%Y"} {gmt_date format="%Y-%m-%dT%H:%i:%s%Q"} http://www.magentocommerce.com/bug-tracking/issue?issue=5222 Posted: 2009-02-13 01:30:14
Category: Orders
Version: 1.2.1
Priority: high
Status: closed
Reported By: ShopGuy

lib/Zend/Db/Adapter/Pdo/MySql.php:describeTable() is called a LOT in magento. In order to decrease db load, query latency, etc this function should be cached. If 2 second old describe query info cannot be trusted, then neither can 1/10 second describe table information, so caching is appropriate. Here is the new function that caches data appropriately:

public function describeTable($tableName, $schemaName = null)
{
// begin changes
static $cache = array();

// get current connection config
$config = $this->getConfig();

// create database cache if needed
if (array_key_exists($config['dbname'], $cache) === false) {
$cache[$config['dbname']] = array();
}

// IF table cache does not exist then create it
// ELSE return cache value instead of continuing execution
$cacheKey = $tableName . '~~' . (string) $schemaName;
if (array_key_exists($cacheKey, $cache[$config['dbname']]) === false) {
$cache[$config['dbname']][$cacheKey] = array();
} elseif ((time() - $cache[$config['dbname']][$cacheKey]['cache_time']) <= 2) {
return $cache[$config['dbname']][$cacheKey]['value'];
}
// end changes

// @todo use INFORMATION_SCHEMA someday when MySQL's
// implementation has reasonably good performance and
// the version with this improvement is in wide use.

if ($schemaName) {
$sql = 'DESCRIBE ' . $this->quoteIdentifier("$schemaName.$tableName", true);
} else {
$sql = 'DESCRIBE ' . $this->quoteIdentifier($tableName, true);
}
$stmt = $this->query($sql);

// Use FETCH_NUM so we are not dependent on the CASE attribute of the PDO connection
$result = $stmt->fetchAll(Zend_Db::FETCH_NUM);

$field = 0;
$type = 1;
$null = 2;
$key = 3;
$default = 4;
$extra = 5;

$desc = array();
$i = 1;
$p = 1;
foreach ($result as $row) {
list($length, $scale, $precision, $unsigned, $primary, $primaryPosition, $identity)
= array(null, null, null, null, false, null, false);
if (preg_match('/unsigned/', $row[$type])) {
$unsigned = true;
}
if (preg_match('/^((?:var)?char)((d+))/', $row[$type], $matches)) {
$row[$type] = $matches[1];
$length = $matches[2];
} else if (preg_match('/^decimal((d+),(d+))/', $row[$type], $matches)) {
$row[$type] = 'decimal';
$precision = $matches[1];
$scale = $matches[2];
} else if (preg_match('/^((?:big|medium|small|tiny)?int)((d+))/', $row[$type], $matches)) {
$row[$type] = $matches[1];
// The optional argument of a MySQL int type is not precision
// or length; it is only a hint for display width.
}
if (strtoupper($row[$key]) == 'PRI') {
$primary = true;
$primaryPosition = $p;
if ($row[$extra] == 'auto_increment') {
$identity = true;
} else {
$identity = false;
}
++$p;
}
$desc[$this->foldCase($row[$field])] = array(
'SCHEMA_NAME' => null, // @todo
'TABLE_NAME' => $this->foldCase($tableName),
'COLUMN_NAME' => $this->foldCase($row[$field]),
'COLUMN_POSITION' => $i,
'DATA_TYPE' => $row[$type],
'DEFAULT' => $row[$default],
'NULLABLE' => (bool) ($row[$null] == 'YES'),
'LENGTH' => $length,
'SCALE' => $scale,
'PRECISION' => $precision,
'UNSIGNED' => $unsigned,
'PRIMARY' => $primary,
'PRIMARY_POSITION' => $primaryPosition,
'IDENTITY' => $identity
);
++$i;
}

// begin changes
$cache[$config['dbname']][$cacheKey] = array('cache_time' => time(), 'value' => $desc);
// end changes

return $desc;
}

---

]]> #1 / Comment by Magento Team

Hello ShopGuy,

This issue was fixed. Please check the latest Magento release at http://www.magentocommerce.com/download/

Thank you.]]>