5
You do not say what version of MySQL you are using. I am using version 6.0 and have had a similar problem. My knowledge of PHP is nil and as for SQL it is minimal but I have been soldiering away on my own to get this sorted.
If you look at the file "kernel\configitem"
Line 293 looks something like
$sql = 'SELECT * FROM '.$this->db->prefix('config');
.
and
.
Line 296 something like this
$sql .= ' ORDER BY conf_order ASC';
This sectio of code generates the query "SELECT * FROM xoops_config WHERE (conf_modid = '0' AND conf_catid = '1') ORDER BY conf_order ASC". If you run this query directly under the MySQL command line client then 0 records are returned. Take away the ORDER BY clause and it works but without the sort. The answer is use command line client to alter one of the indexes on the config table:
There are 2 lines you need to type in:
1) ALTER TABLE <prefix>_config DROP INDEX conf_mod_cat_id;
2) ALTER TABLE <prefix>_config ADD INDEX conf_mod_cat_id (conf_modid,conf_catid, conf_order);
Adding the extra field to this table should sort the issue. There is one other query you will need to alter and you will need to add a key to the relevant table.
In the file class\xoopsblock.php
Around line 437:
$sql = 'SELECT b.* FROM '.$db->prefix('newblocks').' b, '.$db->prefix('block_module_link').' m WHERE m.block_id=b.bid';
Change to:
$sql = 'SELECT b.* FROM '.$db->prefix('newblocks').' AS b USE INDEX (weight_bid), '.$db->prefix('block_module_link').' AS m WHERE m.block_id=b.bid';
The red bits are what has to be added.
Use the MySQLcommand line client to add an index on the newblocks table with:
ALTER TABLE <prefix>_newblocks ADD INDEX weight_bid (weight, bid);
The other queries you show have not given me any issues yet but that may be because I have corrected the original issues with these two solutions.
