Kunena 6.2.5 & module Kunena Latest 6.0.7 released

The Kunena team has announce the arrival of Kunena 6.2.5 [K 6.2.5] which is now available for download as a native Joomla extension for J! 4.3.x/4.4.x/5.0.x. This version addresses most of the issues that were discovered in K 6.1 / K 6.2 and issues discovered during the last development stages of K 6.2

Question Huge database - Spam staying after deleted?

More
13 years 7 months ago #1 by rocketman007
I recently upgraded one of my forums to Kunena 1.5x, and realized the database was nearly 400 MB. Currently jos_fb_messages_text is taking up almost 300MB, and I have only about 2500 real posts. I noticed my stats say I have over 50,000; which I assume is from spam posters.

My question is, do posts/threads that get deleted actually get removed from the database? Or are they just disabled from being viewed? Also, do avatars, images, and attachments get stored in the database? What else would cause mine to become so large with less than 3,000 posts?

Please Log in or Create an account to join the conversation.

More
13 years 7 months ago #2 by sozzled
K 1.5 - deleted message records are removed from the database

K 1.6 - deleted messages are hidden from view; requires second step to permanently remove the records from the database.

Avatar images are files. Attachments are files. These files are not stored in the database; they are stored in the site file directory.

When you say that you have "stats" that report 50,000+ messages, how did you obtain that information? When you say that you have only about 2,500 real posts, where did you obtain that information? When we know the answers to these questions, we may be able to reconcile the different counts and what might be the cause.
The following user(s) said Thank You: rocketman007

Please Log in or Create an account to join the conversation.

More
13 years 7 months ago - 13 years 7 months ago #3 by rocketman007
I was previously using K 1.0, is that why the messages are still in the DB?

As for the stats, the number of posts is shown and the bottom; while adding up posts while viewing the stats of individual forums from the forum homepage shows are 2,500. I realize this might not be the most accurate method for obtaining a post count, however it appears there is a 20 hidden posts/per 1 shown posts. We have deleted so much spam that I am lead to think that this is the cause.

Thanks for your help thus far, and I can PM you the site address if that would help.
Last edit: 13 years 7 months ago by rocketman007.

Please Log in or Create an account to join the conversation.

More
13 years 7 months ago #4 by sozzled

rocketman007 wrote: I was previously using K 1.0, is that why the messages are still in the DB?

This is an unlikely cause. K 1.5 uses the same database structure as K 1.0.

What are you using to calculate the space used by the database?

Either you have 2,500 records or you have 50,0000+ records and I would like to know what we are discussing. If Kunena gives two different reports, can you please show me where: maybe a screen capture with the relevant areas highlighted?

Databases grow. When you delete records from databases, you don't normally get any release of disk space. To reclaim this "lost" disk space, you need to compact the database. Most database management software (like phpMyAdmin, for example) have features for compacting the database.
The following user(s) said Thank You: rocketman007

Please Log in or Create an account to join the conversation.

More
13 years 7 months ago #5 by rocketman007
Space is being calculated by phpmyadmin, which reports the referenced table is over 300MB. It you goto the site (PM sent), you will see there is 50,000 posts (This is reported at the bottom by Kunena). If you actually look at the numbers reported by the subforums, you will see it is significantly less.

When I browse the database table, there are over 50,000 records shown. This makes be think that even after deletion, the records are staying in the database.

Please Log in or Create an account to join the conversation.

More
13 years 7 months ago #6 by rocketman007
Phpmyadmin screenshot added.

Attachments:

Please Log in or Create an account to join the conversation.

More
13 years 7 months ago - 13 years 7 months ago #7 by sozzled
For comparison purposes, I've included a screenshot of a K 1.5 installation that you might be interested in seeing:


You might find this article helpful: dev.mysql.com/doc/refman/5.1/en/optimize-table.html

I looked at your site. The pertinent statistic is circled below:


I don't know why three-and-a-half thousand messages translates into 60,000 records. It might be useful for you to look into the database to see how those records relate to your forum.

Maybe someone else has some ideas?
Attachments:
Last edit: 13 years 7 months ago by sozzled.

Please Log in or Create an account to join the conversation.

More
13 years 7 months ago #8 by Matias
I have an idea.. Or two:

First you need to check that jos_messages and jos_messages_text have primary keys. If not, it may be the cause of this issue.

There may also be issue with table integrity: mesages outside categories or thread, missing data etc..

I have a few queries I want you to run. Please check your private messages, but answer into here.

Please Log in or Create an account to join the conversation.

More
13 years 7 months ago #9 by rocketman007
After some further research, it appears there was a hidden category that contained almost 55k spam topics. This is not my site, so I was unaware of this at first. When trying to remove the category, a php out of memory error triggered; however the subforum was still removed. The posts however are still in the DB. When deleting a subforum, do the posts normally stay in the DB? Is there a way to manually remove them later?

Please Log in or Create an account to join the conversation.

More
13 years 7 months ago #10 by Matias
You can delete the messages by hand, yes. But that's very dangerous action if you don't know what you're doing (potentially deleting all the messages by accident).

Idea is to check what was the catid of those messages and then delete WHERE catid=X for both jos_fb_messages and jos_fb_messages_text. I will not provide actual SQL in the public and you should REALLY take backup from the tables before doing this.

Please Log in or Create an account to join the conversation.

Time to create page: 0.385 seconds