Need help to remove sakai site through sql query

[Danish Khan]

HI,

We are using sakai 2.8.1 last 5 years and now our database size is around 300GB which is more difficult to take backup we are storing our all files into database which is the reason to increase size day by day. We tried to remove worksite through sakai front end but the database size is remain same. we need your help how to manage this. 

please respond this is urgent.

Thanks & Regards,

Danish

[Sam Ottenhoff]

Look into conver...@org.sakaiproject.content.api.ContentHostingService and https://jira.sakaiproject.org/browse/SAK-18389

--
You received this message because you are subscribed to the Google Groups "Sakai Production" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sakai-production+unsubscribe@apereo.org.
To post to this group, send email to sakai-pr...@apereo.org.
Visit this group at https://groups.google.com/a/apereo.org/group/sakai-production/.

[Danish Khan]

Dear SAKAI community,

Please share query of saki worksite removing. if any body have please share with me.

On Tue, Mar 13, 2018 at 8:01 PM, Danish Khan <amdk...@gmail.com> wrote:

Dear Mr. Qinsheng,

THanks for quick response basically we deleted worksites from the front end but we want to remove sakai sites through hard delete via sql query. Do you have work site sql query for removing worksites in sakai.

If you have please share i will be thankful.

Thanks & Regards,

Danish

On Tue, Mar 13, 2018 at 7:57 PM, Qinsheng Zhu <qs...@umich.edu> wrote:

Danish,

I think your remove uses DELETE.  That won't reduce the datafile's size.  If you know which tables the deletes are done, run the following SQLs and reduce the sizes the tables take first:

alter table table_name enable row movement;

alter table table_name shrink space;

alter table table_name disable row movement;

That will reset the high water mark of the tables and reduce the sizes of the tables take.  After that, you should be able to resize the datafile or shrink the sizes of the tablespaces.

You can rebuild the indexes online and make sure no performance issues.

Hope that helps.

Drew

On Tue, Mar 13, 2018 at 10:42 AM, Danish Khan <amdk...@gmail.com> wrote:

--

[Steve Swinsburg]

The size isn't the site, it's all the content. As Sam mentioned, use the setting to convert the content out to the filesystem.

You received this message because you are subscribed to the Google Groups "Sakai Development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sakai-dev+unsubscribe@apereo.org.
To post to this group, send email to saka...@apereo.org.
Visit this group at https://groups.google.com/a/apereo.org/group/sakai-dev/.

[amdk2011]

thanks for your reply. yes Mr. sam gives that solution but we dont want to keep files in file system and we also dont want to keep sites into database we totally want to remove old sites plz share sql query if you have.

thanks again

Sent from my Samsung Galaxy smartphone.

[Steve Swinsburg]

If you upgrade there is the ability to hard delete a site which will remove content ( not all but the big stuff).

--

To unsubscribe from this group and stop receiving emails from it, send an email to sakai-producti...@apereo.org.

To post to this group, send email to sakai-pr...@apereo.org.
Visit this group at https://groups.google.com/a/apereo.org/group/sakai-production/.

--
You received this message because you are subscribed to the Google Groups "Sakai Development" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sakai-dev+...@apereo.org.

[Danish Khan]

Dear Sakai Team,

We are using sakai with all uploaded files stored in database system. We upgrade sakai to 11.4 and there is an option hard delete but that hard delete is not removing storing rows from "content_resource_body_binary" table. Please suggest what the solution to remove files from database system. We have 326849 rows in our  "content_resource_body_binary" and that size is approximate 300GB. 

Please help in this area.

Thanks & Regards,

Danish

--

To unsubscribe from this group and stop receiving emails from it, send an email to sakai-production+unsubscribe@apereo.org.

To post to this group, send email to sakai-pr...@apereo.org.
Visit this group at https://groups.google.com/a/apereo.org/group/sakai-production/.

--
You received this message because you are subscribed to the Google Groups "Sakai Development" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sakai-dev+unsubscribe@apereo.org.

[Danish Khan]

Dear Team,

You are making releases of sakai. This is proud of us you are improving sakai but how can we delete the "content_resource_body_binary"  table data. We used sakai 11.4 and try to delete hard delete but its not deleted data from " content_resource_body_binary" table data.

Please any one can help us on this issue.

Thanks & Regards,

Danish

[Miguel Pellicer]

Hi Danish,

You can delete the rows which are not present in content_resource table:

select * from content_resource_body_binary crb where not exists (select * from content_resource cr where crb.resource_id=cr.resource_id);

After checking everything and ensuring you will not lose anything, proceed with the delete of the rows.

I suggest moving the files to the disk, using the "conver...@org.sakaiproject.content.api.ContentHostingService=true" property, then delete all the contents of the "content_resource_body_binary" table. This performs much better.

More info here https://jira.sakaiproject.org/browse/SAK-18389

Regards

To unsubscribe from this group and stop receiving emails from it, send an email to sakai-dev+...@apereo.org.

To post to this group, send email to saka...@apereo.org.
Visit this group at https://groups.google.com/a/apereo.org/group/sakai-dev/.

-- 
-----------------------------------------------
Miguel Pellicer
CTO at EDF

Website: https://www.edf.global
LinkedIn: https://www.linkedin.com/in/mpellicer-edf
Office Phone: +34 - 96 381 35 75
Requesting a meeting: https://calendly.com/mpellicer
-----------------------------------------------

[Danish Khan]

Dear Mr. Miguel,

I really appreciated your response. We tried its remove data but it removes only "/group/006ad029-dc69-4913-964f-2f8419eaa9b9/3rd June plan.pptx" but its not deleting "/attachment/0065c2e3-2246-4520-a268-1a0dc0abc46b/Announcements/a175242b-8db3-4b94-be5f-d053d50feb40/QUIZ-2 on LMS.xlsx" 

I hope you understand this.

Please help how can we remove other content also from " content_resource_body_binary ".

Thanks & Regards,

Danish

On Fri, May 4, 2018 at 12:29 PM, Miguel Pellicer <mpel...@entornosdeformacion.com> wrote:

Hi Danish,

You can delete the rows which are not present in content_resource table:

select * from content_resource_body_binary crb where not exists (select * from content_resource cr where crb.resource_id=cr.resource_id);

After checking everything and ensuring you will not lose anything, proceed with the delete of the rows.

I suggest moving the files to the disk, using the "convertToFile@org.sakaiproject.content.api.ContentHostingService=true" property, then delete all the contents of the "content_resource_body_binary" table. This performs much better.

-- 
-----------------------------------------------
Miguel Pellicer
CTO at EDF

Website: https://www.edf.global

[Danish Khan]

Dear All,

Just to inform you after the delete all course site via hard delete, still rows in content resource table. We analyzed "attachment" of assignment is there. Ideally after deleting complete course site it should be deleted with that.

Please reply We have stuck on this. Kindly help.

Thanks & Regards,

Danish

On Wed, May 9, 2018 at 3:10 PM, Danish Khan <amdk...@gmail.com> wrote:

Dear Mr. Miguel,

I really appreciated your response. We tried its remove data but it removes only "/group/006ad029-dc69-4913-964f-2f8419eaa9b9/3rd June plan.pptx" but its not deleting "/attachment/0065c2e3-2246-4520-a268-1a0dc0abc46b/Announcements/a175242b-8db3-4b94-be5f-d053d50feb40/QUIZ-2 on LMS.xlsx" 

I hope you understand this.

Please help how can we remove other content also from " content_resource_body_binary ".

Thanks & Regards,

Danish

On Fri, May 4, 2018 at 12:29 PM, Miguel Pellicer <mpellicer@entornosdeformacion.com> wrote:

Hi Danish,

You can delete the rows which are not present in content_resource table:

select * from content_resource_body_binary crb where not exists (select * from content_resource cr where crb.resource_id=cr.resource_id);

After checking everything and ensuring you will not lose anything, proceed with the delete of the rows.

I suggest moving the files to the disk, using the "conver...@org.sakaiproject.content.api.ContentHostingService=true" property, then delete all the contents of the "content_resource_body_binary" table. This performs much better.

[Danish Khan]

Dear Sir,

I run this query (

select * from content_resource_body_binary crb where not exists (select * from content_resource cr where crb.resource_id=cr.resource_id);

& its deleted rows but its not deleting "assignment" related rows.  Please see screenshot i attached it. I have deleted all course site via hard delete & run above query but it still shows attachment rows.

I know we can delete all rows via doing truncate table but i don't want to do truncate or delete on that tables because i want to keep one semester worksite. I hope you understand & give us solution.

I request to Mr. Miguel & whole sakai development community Please help me.

Thanks & Regards,

Danish

[Miguel Pellicer]

Hey Danish, you asked a question about delete rows from the "content_resource_body_binary" to free up some GB of space, I provided you examples and the correct documentation.

I think you're mixing stuff right now, because now you're talking about remove rows from "content_resource". Maybe you could identify the files from a particular site in the "content_resource" using a query like this:

SELECT * FROM sakaimaster.content_resource where in_collection like '%SITE_ID%';

Deleting the content is dangerous and is up to you, these files are referenced in other Sakai tables like assignment's attachments, be careful if you don't want to have a bad day :S

Regards

[Danish Khan]

Dear Sir,

I am very thankful you are supporting & reply us. Basically i followed your instruction & i deleted as per you given query but the data is still there after deleting the hard delete all data should remove. My simple question is.

If i have removed all the work sites in the system via front end (hard delete) It should delete all the content & other things like course site assignment, announcement & other site related file/rows from the DB but right now sakai front end is not doing this. its only deleted rows which starts from the " "/group/". its not deleting which starts "/attachment/"

You are saying deleting the content is dangerous if i have deleted all the complete course site so why it should danger.

Thanks & Regards,

Danish

[Miguel Pellicer]

Yeah, I trust you, the hard-delete operation may not clean the entire contents of the site, the contributors of the feature may clarify more what removes and what not.

https://jira.sakaiproject.org/browse/SAK-26217

The Jira says "In the future, this feature may be extended to also purge database content from tools." so probably the contents of the site in the several tool tables should be purged separately, same with attachments in the content_resource tables.

Regards

[Danish Khan]

Dear Mr. Miguel,

Thanks for reply, After applying this patch can i able to purge all or not  https://jira.sakaiproject.org/browse/SAK-26217. If not so I request to sakai community should worked seriously on this issue. Please let me know other ways how can we remove that should we remove directly via site_id. Please suggest.

Thanks & Regards,

Danish

[Miguel Pellicer]

Just to be clear, I didn't say you need to apply SAK-26217, it's just the reference of the hard-delete feature.

If you plan to work on improving the hard-delete feature, you're really welcome and the community would appreciate a lot your efforts, let me know if I can help.

Regards