delete deactivated stories and media for 1.10.2 sql?

Hi there-

I tried using the code below to delete deactivated stories
and media  
from my 1.10.2 database-
substituting:  active = FASLE   for: active = '0'

http://wiki.b
ricolage.cc/bin/view/Bric/ 
UsefulSQL#Delete_deactivated_stories_and_m

Delete deactivated stories and media (1.8.5)

DELETE FROM member WHERE id IN (
     SELECT sm.member__id
     FROM   story_member sm, story s
     WHERE  s.id = sm.object_id
            AND s.active = '0'
);

DELETE FROM story WHERE active = '0';

DELETE FROM member WHERE id IN (
     SELECT mm.member__id
     FROM   media_member mm, media m
     WHERE  m.id = mm.object_id
            AND m.active = '0'
);

DELETE FROM media WHERE active = '0';

-------------------

It seemed to work- and my stories are all there, and the
media images  
are all still there- however when I try to publish old
stories I now  
get the following errors -

Can't call method "get_key_name" on an
undefined value at /home/ 
tyeefish/bricolage/lib/Bric/Biz/Asset/Business.pm line 1279,
<GEN527>  
line 512. Bric::Biz::Asset::Business::get_element_key_name 
('Bric::Biz::Asset::Business::Media::Image=HASH(0xa90c6b4)
') called  
at
/home/tyeefish/bricolage/data/burn/comp/oc_1032/util/thumbna
il.mc  
line 147 ...

ect....


  which seem to point to image problems. However if I
unrelated the  
related media and save and try to publish I still get the
same errors.

If I make a new story with new media it publishes. Older
images  
publish fine.
So I think I screwed something up in the database with my
query above  
(and I'll have to restore the old database). If so I wonder
what my  
correct code should be.


I looked up

bricolage/lib/Bric/Biz/Asset/Business.pm line 1279 here it
is:


sub get_element_key_name {
     my $self = shift;
     $self->get_element->get_key_name;
}


If anyone has any insight I'd be grateful-

thank you
Dawn

I imagine the deactivated media haven't been disassociated
from
the old stories. But then I don't know why it wouldn't've
worked
when you unrelated those media.
Some workarounds that come to mind:
- don't republish stories that use deleted media
- reactivate all deactivated media that are associated with
active stories
   before deleting deactivated media as you did below (good
luck figuring
   out the SQL for that)

(/me adds a disclaimer to the wiki page)

On Tue, 19 Sep 2006, Dawn Buie wrote:
> Hi there-
>
> I tried using the code below to delete deactivated
stories and media from my 
> 1.10.2 database-
> substituting:  active = FASLE   for: active = '0'
>
> http://wiki.bricolage.cc/bin/vi
ew/Bric/UsefulSQL#Delete_deactivated_stories_and_m
>
> Delete deactivated stories and media (1.8.5)
>
> DELETE FROM member WHERE id IN (
>   SELECT sm.member__id
>   FROM   story_member sm, story s
>   WHERE  s.id = sm.object_id
>          AND s.active = '0'
> );
>
> DELETE FROM story WHERE active = '0';
>
> DELETE FROM member WHERE id IN (
>   SELECT mm.member__id
>   FROM   media_member mm, media m
>   WHERE  m.id = mm.object_id
>          AND m.active = '0'
> );
>
> DELETE FROM media WHERE active = '0';
>
> -------------------
>
> It seemed to work- and my stories are all there, and
the media images are all 
> still there- however when I try to publish old stories
I now get the 
> following errors -
>
> Can't call method "get_key_name" on an
undefined value at 
> /home/tyeefish/bricolage/lib/Bric/Biz/Asset/Business.pm
line 1279, <GEN527> 
> line 512. 
>
Bric::Biz::Asset::Business::get_element_key_name('Bric::Biz
::Asset::Business::Media::Image=HASH(0xa90c6b4)') 
> called at
/home/tyeefish/bricolage/data/burn/comp/oc_1032/util/thumbna
il.mc 
> line 147 ...
>
> ect....
>
>
> which seem to point to image problems. However if I
unrelated the related 
> media and save and try to publish I still get the same
errors.
>
> If I make a new story with new media it publishes.
Older images publish fine.
> So I think I screwed something up in the database with
my query above (and 
> I'll have to restore the old database). If so I wonder
what my correct code 
> should be.
>
>
> I looked up
>
> bricolage/lib/Bric/Biz/Asset/Business.pm line 1279 here
it is:
>
>
> sub get_element_key_name {
>   my $self = shift;
>   $self->get_element->get_key_name;
> }
>
>
> If anyone has any insight I'd be grateful-
>
> thank you
> Dawn
>
>

We had a component disappear yesterday.  I had recently
republished a
component similar to it, but hadn't touched this one in
particular.  The
page that it was on wouldn't display as a result.  At first
I thought that
it could have been a question of our CDN not re-caching the
component, but
the problem existed on our origin server as well.

Has anyone else experienced components randomly unpublishing
themselves?  It
didn't have an expiration date attached to it.  The raw
error is below:


could not find component for path
'/cantonese/ui/ssi/listen_and_subscribe_links.mas'


Trace begun at
/usr/lib/perl5/site_perl/5.8.5/HTML/Mason/Request.pm line
967
HTML::Mason::Request::comp('HTML::Mason::Request::ApacheHan
dler=HASH(0xac565
44)', '/cantonese/ui/ssi/listen_and_subscribe_links.mas')
called at
/var/bric/comp/cantonese/index.html line 236
HTML::Mason::Commands::__ANON__ at
/usr/lib/perl5/site_perl/5.8.5/HTML/Mason/Component.pm line
133
HTML::Mason::Component::run('HTML::Mason::Component::FileBa
sed=HASH(0xac5690
c)') called at
/usr/lib/perl5/site_perl/5.8.5/HTML/Mason/Request.pm line
1045
eval {...} at
/usr/lib/perl5/site_perl/5.8.5/HTML/Mason/Request.pm line
1039
HTML::Mason::Request::comp('HTML::Mason::Request::ApacheHan
dler=HASH(0xac565
44)',
'HTML::Mason::Component::FileBased=HASH(0xac5690c)')
called at
/usr/lib/perl5/site_perl/5.8.5/HTML/Mason/Request.pm line
735
HTML::Mason::Request::call_next('HTML::Mason::Request::Apac
heHandler=HASH(0x
ac56544)') called at /var/bric/comp/cantonese/autohandler
line 1
HTML::Mason::Commands::__ANON__ at
/usr/lib/perl5/site_perl/5.8.5/HTML/Mason/Component.pm line
133
HTML::Mason::Component::run('HTML::Mason::Component::FileBa
sed=HASH(0xacb4a1
c)') called at
/usr/lib/perl5/site_perl/5.8.5/HTML/Mason/Request.pm line
1040
eval {...} at
/usr/lib/perl5/site_perl/5.8.5/HTML/Mason/Request.pm line
1039
HTML::Mason::Request::comp('HTML::Mason::Request::ApacheHan
dler=HASH(0xac565
44)', 'HASH(0xacaf4c0)',
'HTML::Mason::Component::FileBased=HASH(0xacb4a1c)')
called at
/usr/lib/perl5/site_perl/5.8.5/HTML/Mason/Request.pm line
336
eval {...} at
/usr/lib/perl5/site_perl/5.8.5/HTML/Mason/Request.pm line
336
eval {...} at
/usr/lib/perl5/site_perl/5.8.5/HTML/Mason/Request.pm line
296
HTML::Mason::Request::exec('HTML::Mason::Request::ApacheHan
dler=HASH(0xac565
44)') called at
/usr/lib/perl5/site_perl/5.8.5/HTML/Mason/ApacheHandler.pm
line 134
eval {...} at
/usr/lib/perl5/site_perl/5.8.5/HTML/Mason/ApacheHandler.pm
line 134
HTML::Mason::Request::ApacheHandler::exec('HTML::Mason::Req
uest::ApacheHandl
er=HASH(0xac56544)') called at
/usr/lib/perl5/site_perl/5.8.5/HTML/Mason/ApacheHandler.pm
line 787
HTML::Mason::ApacheHandler::handle_request('HTML::Mason::Ap
acheHandler=HASH(
0xabbc460)', 'Apache=SCALAR(0xac1a180)') called at (eval
120) line 5
HTML::Mason::ApacheHandler::handler('HTML::Mason::ApacheHan
dler',
'Apache=SCALAR(0xac1a180)') called at /dev/null line 0
eval {...} at /dev/null line 0