remove articles directly from database Topic is solved

Need help with the Administration of your Joomla! 3.x site? This is the spot for you.

Moderator: General Support Moderators

Forum rules
Forum Rules
Absolute Beginner's Guide to Joomla! <-- please read before posting, this means YOU.
Forum Post Assistant - If you are serious about wanting help, you will use this tool to help you post.
Windows Defender SmartScreen Issues <-- please read this if using Windows 10.
Post Reply
Thomsterdam
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 124
Joined: Mon Dec 12, 2011 5:55 pm

remove articles directly from database

Post by Thomsterdam » Thu Jul 30, 2020 1:31 pm

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
Give a man a fish and you feed him for a day;
teach a man to fish and you feed him for a lifetime.

User avatar
pe7er
Joomla! Master
Joomla! Master
Posts: 23172
Joined: Thu Aug 18, 2005 8:55 pm
Location: Nijmegen, Netherlands
Contact:

Re: remove articles directly from database

Post by pe7er » Thu Jul 30, 2020 8:25 pm

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.
Kind Regards,
Peter Martin, Global Moderator
https://db8.nl - Joomla specialist, Nijmegen, Nederland
The best website: https://the-best-website.com

Thomsterdam
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 124
Joined: Mon Dec 12, 2011 5:55 pm

remove articles directly from database

Post by Thomsterdam » Mon Jun 14, 2021 9:17 am

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
Last edited by toivo on Mon Jun 14, 2021 1:43 pm, edited 1 time in total.
Reason: mod note: merged with the current topic
Give a man a fish and you feed him for a day;
teach a man to fish and you feed him for a lifetime.

gws
Joomla! Virtuoso
Joomla! Virtuoso
Posts: 4316
Joined: Tue Aug 23, 2005 1:56 pm
Location: South coast, UK
Contact:

Re: remove articles directly from database

Post by gws » Mon Jun 14, 2021 9:23 am

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.

User avatar
AMurray
Joomla! Champion
Joomla! Champion
Posts: 6910
Joined: Sat Feb 13, 2010 7:35 am
Location: Australia

Re: remove articles directly from database

Post by AMurray » Mon Jun 14, 2021 9:24 am

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" ? :)
Regards - A Murray

User avatar
sozzled
Joomla! Master
Joomla! Master
Posts: 10418
Joined: Sun Jul 05, 2009 3:30 am
Location: Canberra, Australia

Re: remove articles directly from database

Post by sozzled » Mon Jun 14, 2021 9:52 am

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.
“If you think I’m wrong then say, ‘I think you’re wrong.’ If you say ‘You’re wrong!’, how do you know?” :)
Testing J! 3.10: https://sintranet.enduring.com.au

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 13583
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

Re: remove articles directly from database

Post by toivo » Mon Jun 14, 2021 10:17 am

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.
Toivo Talikka, Global Moderator

Thomsterdam
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 124
Joined: Mon Dec 12, 2011 5:55 pm

Re: remove articles directly from database

Post by Thomsterdam » Mon Jun 14, 2021 10:20 am

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
Give a man a fish and you feed him for a day;
teach a man to fish and you feed him for a lifetime.

Thomsterdam
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 124
Joined: Mon Dec 12, 2011 5:55 pm

Re: remove articles directly from database

Post by Thomsterdam » Mon Jun 14, 2021 10:24 am

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
Give a man a fish and you feed him for a day;
teach a man to fish and you feed him for a lifetime.

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 13583
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

Re: remove articles directly from database

Post by toivo » Mon Jun 14, 2021 10:31 am

Thomsterdam wrote:
Mon Jun 14, 2021 10:20 am
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.

Thomsterdam wrote:
Mon Jun 14, 2021 10:20 am
Is it something that PWT ACL manager can solve?
No.

Thomsterdam wrote:
Mon Jun 14, 2021 10:20 am
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 Talikka, Global Moderator

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 13583
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

Re: remove articles directly from database

Post by toivo » Mon Jun 14, 2021 10:33 am

Thomsterdam wrote:
Mon Jun 14, 2021 10:24 am
Do I understand correctly I can just go ahead and delete all content from the #__assets table?
No and no.

Thomsterdam wrote:
Mon Jun 14, 2021 10:24 am
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 Talikka, Global Moderator

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 13583
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

Re: remove articles directly from database

Post by toivo » Mon Jun 14, 2021 10:59 am

toivo wrote:
Mon Jun 14, 2021 10:31 am
Thomsterdam wrote:
Mon Jun 14, 2021 10:20 am
Is it something that PWT ACL manager can solve?
No.
Correction: Possibly.
Toivo Talikka, Global Moderator

User avatar
sozzled
Joomla! Master
Joomla! Master
Posts: 10418
Joined: Sun Jul 05, 2009 3:30 am
Location: Canberra, Australia

Re: remove articles directly from database

Post by sozzled » Mon Jun 14, 2021 12:51 pm

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.
“If you think I’m wrong then say, ‘I think you’re wrong.’ If you say ‘You’re wrong!’, how do you know?” :)
Testing J! 3.10: https://sintranet.enduring.com.au

User avatar
toivo
Joomla! Master
Joomla! Master
Posts: 13583
Joined: Thu Feb 15, 2007 5:48 am
Location: Sydney, Australia

Re: remove articles directly from database

Post by toivo » Mon Jun 14, 2021 1:37 pm

@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?
Toivo Talikka, Global Moderator

User avatar
sozzled
Joomla! Master
Joomla! Master
Posts: 10418
Joined: Sun Jul 05, 2009 3:30 am
Location: Canberra, Australia

Re: remove articles directly from database

Post by sozzled » Mon Jun 14, 2021 8:09 pm

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. ;)
“If you think I’m wrong then say, ‘I think you’re wrong.’ If you say ‘You’re wrong!’, how do you know?” :)
Testing J! 3.10: https://sintranet.enduring.com.au

User avatar
brian
Joomla! Master
Joomla! Master
Posts: 12108
Joined: Fri Aug 12, 2005 7:19 am
Location: Leeds, UK
Contact:

Re: remove articles directly from database

Post by brian » Mon Jun 14, 2021 10:35 pm

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
"Exploited yesterday... Hacked tomorrow"
Blog http://brian.teeman.net/
Joomla Hidden Secrets http://hiddenjoomlasecrets.com/

Thomsterdam
Joomla! Enthusiast
Joomla! Enthusiast
Posts: 124
Joined: Mon Dec 12, 2011 5:55 pm

Re: remove articles directly from database

Post by Thomsterdam » Sat Jun 19, 2021 12:03 am

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
Give a man a fish and you feed him for a day;
teach a man to fish and you feed him for a lifetime.


Post Reply

Return to “Administration Joomla! 3.x”