Completed

426280 MySQL & Kunena: Repair tables having duplicate keys

I have a Joomla site running the Kunena forum component. Kunena's MySQL data tables got corrupted while migrating incorrectly from FireBoard (an older forum component out of which Kunena was developed).

The Kunena forum has been running (without indexes!!) for so long that I don't want to revert to the old Fireboard forum tables and redo the conversion.

I want to backup the Kunena tables,

then uninstall the Kunena component,

then drop the Kunena tables,

then do a fresh install of the Kunena component.

This should give me the correct table structures, indexes, etc.

Then delete all the rows in those new tables (so that the backed up Kunena tables can be imported without the SQL import failing due to duplicate primary keys).

Then import the saved Kunena tables.

Unfortunately, the import fails because the saved tables themselves have duplicate keys that have to be fixed first.

(Also because the saved jos_fb_categories table has a text field called 'image' that is not present in the table structure of the fresh Kunena install. However, this is easily fixed by adding it to the new table. It isn't going to be used by Kunena, but I don't suppose it hurts to leave it there.)

As I said, the import fails because the saved tables themselves have duplicate keys that have to be fixed first. Specifically, at least two of the tables (jos_fb_sessions and jos_fb_users) now have records with duplicate primary keys.

The jos_fb_users table now includes up to three records for each user, with identical primary keys. But each record has different values in the other fields:

One duplicate record has that user's correct value in the [number of] ‘posts' field,

one holds the correct text in the ‘signature' field,

and one record has the correct karma-level and karma-date fields.

(The same fields in the other records hold zero, or empty, or null values.)

For each user with duplicate records, I need to merge the info from three records into one record: For each of the non-matching fields, take the greatest number, or longest text. Stuff those field contents into a single record. So the resulting user record would have the highest karma rating, the most recent karma date, the longest signature, and the greatest number of posts.

Do this for all the duplicate primary keys.

There are also duplicate primary keys in the jos_fb_sessions table. I'm not sure whether these duplicates have different data in their other fields, but if so then a similar merging (rather than just deleting the duplicates) would be needed.

To make matters worse, this is a live site with users joining and posting on the forums every day. So you'd need to develop your queries on a copy of the database, test them on a development copy of the site (which I can provide you with); and then I'd have to shut the live site down (for the shortest possible time) so that you can do it all again for the up-to-date database.

The ideal contractor for this would be familiar not only with MySQL, but would have been involved in the Kunena database structure before.

This is MySQL [url removed, login to view]

and Kunena [url removed, login to view]

There is thread about this problem on the kunena support forum, titled: Post-Migration Issues: need to recalc hit-count and posts-count. This thread includes screenshots of the databases viewed in phpMyAdmin, for your inspection of the problem.

This is a Featured Project, and in addition to fixed price bids, I am willing to consider hourly bids if they include a time estimate and are from highly rated bidders.

Thanks for your attention.

Skills: Anything Goes, Joomla, MySQL, SQL

See more: used structures, thread up, tables in data structure, sql data conversion, signature database, problem structures, new data structures, joomla support price, ideal image, highest rating, different data structures, develop databases, data structure problem, data conversion sql, about data structures in c, about data structures, mysql 5.1, zero day, worse, sql mysql

About the Employer:
( 3 reviews )

Project ID: #2172150

Awarded to:

alexvinnik

Hello, I can fix your database. Regards. Alex

$100 USD in 1 day
(2 Reviews)
0.8