| Author |
 Topic  |
|
|
trevally
Starting Member
46 Posts |
 Posted - 23 September 2006 : 06:42:12
|
Thanks to the wonderful advice here, I have successfully migrated my forum to MySQL and it has been running smoothly for several months. Another thing crept up when I archived, I faced the following duplicate entry error:
Administrative Forum Archive Functions
Archive all topics:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
Duplicate entry '237846' for key 1
/forum/admin_forums.asp, line 489
When I searched through the support forum, I found another member whose advice is to look for an empty field and delete it. That's gonna be a tedious task for me.
Is this an index issue?
Thanks,
Vincent |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 23 September 2006 : 07:20:27
|
| It may not be an index thing, you may simply have a duplicate entry from a previous failed archiving attempt. You can try and find entries in FORUM_A_TOPICS that also exist in FORUM_TOPICS or in FORUM_REPLY and FORUM_A_REPLY and delete the duplicate ones in the _A_ tables. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
 |
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 23 September 2006 : 07:38:08
|
If you need help writing the query then please ask as I'm sure we can help  |
|
|
|
trevally
Starting Member
46 Posts |
Posted - 23 September 2006 : 08:51:43
|
Can I ask you guys something:
Is "A" in FORUM_A_TOPICS means Archive?
Geez, it's gonna be an red eye exercise to compare thousands of topics. Is there any SQL command which is available to automate such process?
Tiger beer on me when you guys are in Malaysia the next time. Promise!
Regards,
Vincent |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 23 September 2006 : 09:11:50
|
yes to both questions.
you need to basically write a query that says something like this (not sure if it will work in MySQL)
SELECT * FROM FORUM_A_TOPICS FA WHERE FA.TOPIC_ID IN (SELECT TOPIC_ID FROM FORUM_TOPICS)
that should list all the topics that exist in both tables |
|
|
|
trevally
Starting Member
46 Posts |
Posted - 23 September 2006 : 09:27:10
|
HuwR,
I'll try now...
Vincent |
|
|
|
trevally
Starting Member
46 Posts |
Posted - 23 September 2006 : 09:39:18
|
I got this result:
MySQL returned an empty result set (i.e. zero rows). (Query took 0.0059 sec)
SQL query: SELECT *
FROM FORUM_A_TOPICS FA
WHERE FA.TOPIC_ID
IN (
SELECT TOPIC_ID
FROM FORUM_TOPICS
)
LIMIT 0 , 30
Am I doing this correctly?
Regards,
Vincent |
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 23 September 2006 : 15:30:47
|
it could be a duplicate in the replies tables, so try this
SELECT * FROM FORUM_A_REPLY FR WHERE FR.REPLY_ID IN (SELECT REPLY_ID FROM FORUM_REPLY)
|
|
|
|
trevally
Starting Member
46 Posts |
Posted - 24 September 2006 : 10:18:22
|
Yes! I got some results! 8,230 in total. Are these the records I should delete? Are these from the archive table?
Hope the screen capture below is what the result should be from the SQL query string.
|
|
|
|
HuwR
Forum Admin
United Kingdom
20595 Posts |
Posted - 24 September 2006 : 14:15:41
|
Ok, now we are getting somewhere. these are replies in the archived table which also appear in your non-archived tables, hence the reason you are now getting the duplicate errors, yes you should either remove them or remove the ones in the non-archived table, however if you are unsure or a bit wary, may I suggest doing a backup dump of your database before you proceed any further.
|
|
|
|
trevally
Starting Member
46 Posts |
Posted - 06 October 2006 : 20:52:39
|
HuwR,
I am gonna take a raincheck on this but if I really proceed with this, that means I should:
1. Stop the board into Maintenance Mode
2. Enter my MySQL via PHPmyAdmin
3. Do an export of FORUM_REPLY for backup
4. Then run the "SELECT * FROM FORUM_A_REPLY FR WHERE FR.REPLY_ID IN (SELECT REPLY_ID FROM FORUM_REPLY" script
5. Let the result be displayed as above
6. Delete all the shown results
7. Reactivate back board
Am I correct for the steps?
Vincent
|
|
|
|
AnonJr
Moderator
United States
5768 Posts |
Posted - 06 October 2006 : 21:23:58
|
I think you forgot the "Make backup first and foremost just in case" at the very beginning.  |
|
|
| |
Topic |
|
Topic Locked
