remove articles directly from database Topic is solved

[Thomsterdam]

Hi there,

This question is only for experts who know a lot about the relationship between Joomla and its database!

We run an intranet website with a lot of articles: 5000-6000. Many of these are news articles that were interesting when they were first published (some as early as 2011), but since have lost their value completely.

We've decided that we want to clean up the news articles and get rid of the old news. So I started deleting old articles in the back end of Joomla on our test server: I used the Search Tools in the Articles Manager to select the ‘News’ category and went to the oldest articles, selected 20 of them (the default number of the view) and deleted them.

To my shock, it took up to 2 or 2.5 minutes to delete 20 articles. This may be caused by the server configuration/speed, the huge database that we have or the vast number of articles or whatever else... It is what it is: up to 2 or 2.5 minutes. You can imagine this is not an attractive option if you want to delete a few thousand articles.

So I needed another method. I went to the database, selected the #__content table an sorted the fields by catid and id. Then I selected a number of rows and deleted them. It all went smoothly and the articles were gone within seconds.

My question is about this direct deletion from the database: I know that you cannot do this with e.g. component tables in Joomla: you cannot delete them directly, as they are also listed in the #__assets table. So deleting the component tables from the database will cause errors in Joomla.


How does that work with deleting articles in this way? Can I go ahead and delete the articles directly from the database as described above? or am I going to wreck the system in the long run? I can imagine that there may be issues with tags. We use CW Joomla's "MCats" (multiple categories for articles). Does that component get distorted? Any other cross references that may be influenced?
You can imagine this method is very appealing, as my website can be cleaned up quickly.

Like I said: I need an answer from an expert, someone who can answer this with authority based on his/her knowledge of the system. Anyone in here who is this knowledgeable?

Thanx,


Thom

[pe7er]

If deleting articles in the Joomla back-end takes a lot of time, then maybe you have some plugins performing some tasks in the background. And are you deleting them (putting them to trash so they are still there but with state -2) or permanently removing them (empty trash) ?
Deleting (permanently removing) the articles directly in phpMyAdmin is always faster then via Joomla.

What you could do (after making sure that you have a good backup) is
1. do a select to select all IDs of articles that you want to remove.
2. use those article IDs to remove the fields from the #__content table (with something like DELETE FROM tablename WHERE IN (all the ids, comma separated).
3. and other tables like the ones for the Tags
4. and the Fields (if you are using that).
5. also the #__ucm_history table if you are using Joomla's versioning system.
6. If you are using Smart Search then you'll probably want to re-index the site after you've deleted the articles.

Removing the articles from the #__assets table will be more difficult. Or with an extension like PWT ACL Manager (paid extension) you might be able to fix any issues with the assets that have no linked article anymore.

I do not know the non-core extension CW Joomla's "MCats" so I do not know what effect the deleting of article will have on that extension.

Furthermore I do not know your site so I don't know if there are any non-core extension dependencies with the article table.

[Thomsterdam]

Hi there,
I am administering a large intranet-site. We have decided to remove a lot (about 2500) of old articles. We have put them in the trash, but now they have to be removed from the database. I've timed this: when I put the view of the Trash to 100 articles in the back end and I delete them all, it takes 3 to 4 minutes to remove them.
What I want to know: is there a way I can do this directly in the database? I am using HeidiSQL and one option is to select the articles with the state -2 (Trash) and delete them directly from the database. Is this a safe way to go about it, or will I be corrupting Joomla or the database?

Thanx,

Thom

[gws]

Make a backup with akeeba first,then try it.If it works fine if not restore from backup and rethink. I don't know Heidisql so cant say for sure.

[AMurray]

I think it would be actually safer to put up with the slowness, and just let Joomla do its job. I'm not sure deleting them from the DB directly would be any faster. I'd be worried if you'd said 3-4 *hours* to complete deleting those trashed articles.

However, there should be a table in the db called 'articles trash' (or search for those types of keywords to find the relevant table) and purge the table to delete the records.

Ironically you should probably do a site backup in case things go sideways.

"HeidiSQL" - a cousin of "MariaDB" ?

[sozzled]

You can use SQL to delete "trashed articles" from the database with the SQL statement

Code: Select all

DELETE FROM xxxxx_content WHERE STATE = 1 

where xxxxx is the database table prefix. Should take less than 1 second to run.

[toivo]

If you delete articles directly from the content table, rather than using the Search Tools to view Thrashed articles and then the function 'Empty Trash', which maintains the integrity of the Joomla database automatically, you should clean the assets table. The asset row has the id of the article appended to the name column: com_content.article.123.

If the articles have tags, check also the table contentitem_tag_map.

As recommended by earlier posters, run a full backup before running any SQL statements or scripts.

In all likelihood, you need to deal with more than one table. To simplify the process, leave the content table as the last table to be cleaned, or, alternatively, create first an auxiliary table and run a SQL query that adds the article id of each item to be deleted into the auxiliary table. This table can then be used as a reference to clean up the other table(s).

If the website uses Smart Search, go to Components - Smart Search and run the functions Clear Index and Index.

[Thomsterdam]

Hey Murray,

1. HeidiSQL is a 'cousin' of PHPmyAdmin. The database we are using is a regular MySQLi database.

2. I can't find a table called 'articles trash' or anything alike. From what I can see in the database, every article can be found at least in the table #__content and it has a state. If the state is 1 the article is published. If the state is 0 it has been unpublished. Is the state -2, then it has been trashed.

The table I have my questions about is the assets table. There I see ID's, Parent ID's, names, titles, etc. I want to know what is going to happen in this table when I delete the articles from the #__content table. Is it going to be screwed up? Is it something that PWT ACL manager can solve?

Anyone who is a database specialist?

Thanx,


Thom

[Thomsterdam]

Hi toivo,
Do I understand correctly I can just go ahead and delete all content from the #__assets table? That seems a bit drastic. Is it safe? Or could I use PWT ACL Manager to correct the lft and rgt entries in the table?

Thanx,


Thom

[toivo]

The table I have my questions about is the assets table. There I see ID's, Parent ID's, names, titles, etc. I want to know what is going to happen in this table when I delete the articles from the #__content table. Is it going to be screwed up?

Yes - most likely.

Is it something that PWT ACL manager can solve?

No.

Anyone who is a database specialist?

Joomla support specialists usually have the skills in building and testing SQL scripts, at least simple joins and insert queries. Just yell out if you need specific information! Tip: the MySQL reference manual is a good source of information.

[toivo]

Do I understand correctly I can just go ahead and delete all content from the #__assets table?

No and no.

Or could I use PWT ACL Manager to correct the lft and rgt entries in the table?

Sorry, I do not know that paid extension.

[toivo]

Is it something that PWT ACL manager can solve?

No.

Correction: Possibly.

[sozzled]

Sorry, my earlier post contained an error. You can use SQL to delete "trashed articles" from the database with the SQL statement

Code: Select all

DELETE FROM xxxxx_content WHERE STATE = -2 

where xxxxx is the database table prefix.

The _assets table has nothing to do with J! articles.

[toivo]

@Thomsterdam, almost a year ago you asked exactly the same question, "remove articles directly from database". Have you tested the recommended process about deleted articles and the other tables, like assets and ucm_history, from the advice you received?

[sozzled]

Further apologies: what @pe7er wrote last year is good general advice.

Yes, it's possible to delete records from the _content table (which is where J! articles are stored) but there may be references to those articles in other tables, e.g. _assets, _ucm_history and _fields. In theory, you should be able to remove records from the _content table without "corrupting" your website but, as others have observed, you do this at your own risk.

I'm inclined to agree with @pe7er and @AMurray and allow the built-in "empty trash" facilities of J! do their work even if they take an hour or so to run.

[brian]

Do I understand correctly I can just go ahead and delete all content from the #__assets table? That seems a bit drastic. Is it safe? Or could I use PWT ACL Manager to correct the lft and rgt entries in the table?

>

Or you could rebuild a clean assets table - there are command line scripts that can do that for you

[Thomsterdam]

toivo: you are absolutely right: I had forgotten about the question last year. What Pe7er wrote, sounded too daunting, so in the end I removed the 2500 articles in Joomla and let Joomla take care of the more complicated stuff under the bonnet:
I selected 50 articles each time and deleted them. It took a long time, as you can imagine and I would get a time out message with a blank screen from time to time. Fortunately I had two computers at my disposal, so I was able to work on one, while the deletion process was taking place on the other one.

All in all: thanks everyone for giving your advice.

Cheers,


Thom