The repair table command in MySQL is useful to repair database indicies when they become corrupt. A prime example of when an index can become corrupt is when the power is shut off unexpectedly to a server. If the power is shut off after a row has been inserted into the table, but before its index has been updated, the table will become corrupt and unusable.
Unfortunately, there isn’t a “repair all tables” option that I know of. Instead, you can use this simple PHP script to do the trick for you. It simply calls “show databases” to loop through a list of your databases, and then “show tables” so that it knows which tables exist for the repair command. Depending on how many databases you have and the size of their tables, this can take several minutes to run.
<?
### Enter your username and password into the connection string: ###
$dbLink = mysql_connect("localhost", "username", "password") or die("Unable to connect to the database.");
$sql = "show databases";
$query = mysql_query($sql) or die("error fetching database names");
while ($rs=mysql_fetch_array($query)) {
//echo "Database: " . $rs['Database'] . "\n";
mysql_select_db($rs['Database'],$dbLink) or die("Unable to select database: " . $rs['Database']);
$sql = "show tables";
$query2 = mysql_query($sql) or die("error fetching table names");
while ($rs2=mysql_fetch_array($query2)) {
$key = "Tables_in_" . $rs['Database'];
//echo "Table: " . $rs2[$key] . "\n";
$sql = "repair table " . $rs2[$key];
$query3 = mysql_query($sql) or die("Error repairing a table - $sql");
$rs3 = mysql_fetch_array($query3);
echo $rs3['Table'] . " | " . $rs3['Op'] . " | " . $rs3['Msg_type'] . " | " . $rs3['Msg_text'] . "\n";
}
}
echo "Finished!\n";
?>
Invision Powerboard 1.2 and 1.3 are still used by many people because they’re free, where the newest versions are not. However, the coding for them isn’t 100% compatible with the latest versions of PHP and MySQL. Many web hosting companies are moving to PHP5 and MySQL5, and this will break the default installation of IPB. Here is a collection of the changes you’ll need to make to your files so that IPB 1.2 or 1.3 will work with PHP5 & MySQL 4.1 or greater.
If your forum is experiencing any of these issues, the rest of this article may fix your problem. Make sure you back up your files and database before making any changes, just in case.
Symptoms:
Blank ‘Profile’ page
Blank ‘My Controls’ page
MySQL errors when reporting post
MySQL errors on ‘The Moderating Team’ page
Files affected:
sources/misc/contact_member.php
sources/Profile.php
sources/Usercp.php
sources/misc/stats.php
Step 1:
Open sources/Usercp.php
Find:
var $parser;
Change To:
//var $parser;
Save and upload sources/Usercp.php
Step 2:
Open sources/Profile.php
Find:
var $parser;
Change To:
//var $parser;
Save and upload sources/Profile.php
Step 3:
Open sources/misc/contact_member.php
Find:
var $email = “”;
var $forum = “”;
var $email = “”;
Change To:
//var $email = “”;
var $forum = “”;
var $email = “”;
Find:
$DB->query(”SELECT m.name, m.email, mod.member_id FROM gallerymoderators mod, gallerymembers m WHERE mod.forum_id=’$fid’ and mod.member_id=m.id”);
Change To:
$DB->query(”SELECT m.name, m.email, moder.member_id FROM gallerymoderators moder, gallerymembers m WHERE moder.forum_id=’$fid’ and moder.member_id=m.id”);
Save and upload sources/misc/contact_member.php
Step 4:
Open sources/misc/stats.php
Find:
//——————————————–
// Do we have any moderators? NORMAL MODS 1st
//——————————————–
$DB->query(”SELECT m2.id, m2.name, m2.email, m2.hide_email, m2.location, m2.aim_name, m2.icq_number,
f.id as forum_id, f.read_perms, f.name as forum_name, c.state
FROM gallerymoderators mod
LEFT JOIN galleryforums f ON(f.id=mod.forum_id)
LEFT JOIN gallerycategories c ON(c.id=f.category AND c.state != 0)
LEFT JOIN gallerymembers m2 ON (mod.member_id=m2.id)
“);
Change To:
//——————————————–
// Do we have any moderators? NORMAL MODS 1st
//——————————————–
$DB->query(”SELECT m2.id, m2.name, m2.email, m2.hide_email, m2.location, m2.aim_name, m2.icq_number,
f.id as forum_id, f.read_perms, f.name as forum_name, c.state
FROM gallerymoderators moder
LEFT JOIN galleryforums f ON(f.id=moder.forum_id)
LEFT JOIN gallerycategories c ON(c.id=f.category AND c.state != 0)
LEFT JOIN gallerymembers m2 ON (moder.member_id=m2.id)
“);
Find: (this edit isn’t in 1.2, so you should be able to skip this step if that’s your version)
//——————————————–
// Do we have any moderators? GROUP MODS 1st
//——————————————–
$DB->query(”SELECT m.id, m.name, m.email, m.hide_email, m.location, m.aim_name, m.icq_number,
f.id as forum_id, f.read_perms, f.name as forum_name, c.state
FROM gallerymoderators mod
LEFT JOIN galleryforums f ON(f.id=mod.forum_id)
LEFT JOIN gallerycategories c ON(c.id=f.category AND c.state != 0)
LEFT JOIN gallerymembers m ON ((mod.is_group=1 and mod.group_id=m.mgroup))
“);
Change To:
//——————————————–
// Do we have any moderators? GROUP MODS 1st
//——————————————–
$DB->query(”SELECT m.id, m.name, m.email, m.hide_email, m.location, m.aim_name, m.icq_number,
f.id as forum_id, f.read_perms, f.name as forum_name, c.state
FROM gallerymoderators moder
LEFT JOIN galleryforums f ON(f.id=moder.forum_id)
LEFT JOIN gallerycategories c ON(c.id=f.category AND c.state != 0)
LEFT JOIN gallerymembers m ON ((moder.is_group=1 and moder.group_id=m.mgroup))
“);
Save and upload sources/misc/stats.php
Step 5:
Add the following code to the top of index.php and admin.php:
//– mod_php5 begin
$HTTP_SERVER_VARS = isset($_SERVER)?$_SERVER:array();
$HTTP_GET_VARS = isset($_GET)?$_GET:array();
$HTTP_POST_VARS = isset($_POST)?$_POST:array();
$HTTP_POST_FILES = isset($_FILES)?$_FILES:array();
$HTTP_COOKIE_VARS = isset($_COOKIE)?$_COOKIE:array();
$HTTP_ENV_VARS = isset($_ENV)?$_ENV:array();
$HTTP_SESSION_VARS = isset($_SESSION)?$_SESSION:array();
//– mod_php5 end
———
Portions of this article was paraphrased from a post by FuSoYa at http://forums.invisionize.com/index.php?showtopic=73882
This is more of a set of modifications rather than an actual module, but the end result is the same.
First thing you’ll need to do is backup your existing site and database. I’d recommend using a test install of Xcart so that nothing in production gets messed up. With that in mind, the first step is to alter the pricing table by using the following alter table command. If you have a different version of MySQL, you might need to tweak this a bit.
ALTER TABLE `xcart_pricing` ADD COLUMN `numpymnts` INTEGER UNSIGNED NOT NULL DEFAULT 1,
ADD COLUMN `paymentprice` DECIMAL(12,2) NOT NULL DEFAULT 0 AFTER `numpymnts`,
ADD COLUMN `sourceproductcode` VARCHAR(255) DEFAULT 0 AFTER `paymentprice`,
ADD COLUMN `sortby` INTEGER UNSIGNED NOT NULL DEFAULT 0 AFTER `sourceproductcode`,
ADD COLUMN `mastervariant` SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0 AFTER `sortby`,
ADD COLUMN `price_override` SMALLINT(5) UNSIGNED NOT NULL DEFAULT 0 AFTER `mastervariant`,
ADD INDEX `numpymnts`(`numpymnts`),
ADD INDEX `paymentprice`(`paymentprice`),
ADD INDEX `sortby`(`sortby`),
ADD INDEX `sourceproductcode`(`sourceproductcode`),
ADD INDEX `mastervariant`(`mastervariant`),
ADD INDEX `price_override`(`price_override`)
, ENGINE = MyISAM;
The new columns are as follows:
numpyments = number of payments
paymentprice = price the export expects to see (might be one payment or the total)
sourceproductcode = product code (sku) that export expects to see
sortby = the order in which the variants show up on the admin page
mastervariant = overrides the ’starting at’ price
price_override = use this table for the price instead of the orders table for reporting
Next, you’ll need to modify some of the admin template files, such as:
Modify Files:
/skin1/modules/Product_Options/product_variants.tpl - add fields to the variants html table - don’t add the price_override column.
/modules/Product_Options/func.php - edit func_get_product_variants and add in the columns that were added to the product_variants.tpl file
When you submit the variants, the form goes to:
product_modify.php
section=variants
mode = product_variants_modify
productid = [variable]
submode = “data”
geid = “”
Once sumbitted, the code is run in this ELSEIF block in modules/Product_Options/product_variants.php:
} elseif ($mode == ‘product_variants_modify’ && $vs && $submode != ‘prices’) {
In here, you need to edit the $query_price_data array to include the additional fields; for example:
## BB Edit - added additional fields
$query_price_data = array(
"price" => $v['price'],
"variantid" => $k,
"productid" => $productid,
"quantity" => 1,
"membershipid" => 0,
"numpymnts" => $v['numpymnts'],
"paymentprice" => $v['paymentprice'],
"sortby" => $v['sortby'],
"sourceproductcode" => $v['sourceproductcode'],
"mastervariant" => $v['mastervariant']
);
Next, you need to edit the modify options page, otherwise, if you change options, it’ll remove your new field data.
When submitting this form, it passes these variables to product_modify.php:
section=options
mode=product_options_add
classid= your classid
productid = your productid
geid=”"
If you change add[is_modifier] (price modifier = ‘y’, else it’s blank), it rebuilds the variants by calling func_rebuild_variants in modules/Product_Options/func.php
//Rebuild variants: . . . . product_modify.php?productid=18§ion=options&classid=71
In func_rebuild_variants, change the $_product[’prices’] $data variable to:
$data = array(
"productid" => $productid,
"quantity" => $p['quantity'],
"membershipid" => $p['membershipid'],
"variantid" => $variantid,
"price" => $p['price'],
"numpymnts" => $p['numpymnts'],
"paymentprice" => $p['paymentprice'],
"sortby" => $p['sortby'],
"sourceproductcode" => $p['sourceproductcode'],
"mastervariant" => $p['mastervariant']
);
This will keep your data from being lost in the new columns. And that’s it, as far as back end functionality for the multi pay option. Depending on how your templates are set up, you’ll probably want to use the new columns to force the “mastervariant” column to show up in the “starting at” price. Do this by ordering that query by mastervariant in descending order. The rest of the columns really come in handy when you need to see the full price such as in reports, modifying omniture code, and to produce export files where the fulfillment company wants to see the full amount instead of the per payment price.
