110-120-migration.sql

[Charles Baker <ch...@datastream.net>]

This script ran fine until it got to the end where some foreign keys are
being created

alter table pingtarget add constraint pingtarget_websiteid_fk
    foreign key (websiteid) references website(id);

alter table autoping add constraint autoping_websiteid_fk
    foreign key (websiteid) references website(id);

alter table autoping add constraint autoping_pingtargetid_fk
    foreign key (pingtargetid) references pingtarget(id);

alter table pingcategory add constraint pingcategory_autopingid_fk
    foreign key (autopingid) references autoping(id);

alter table pingcategory add constraint pingcategory_categoryid_fk
    foreign key (categoryid) references weblogcategory(id);

I get error 1005 from mysql. Google yielded a tip that an index should
be on the field before trying to create the foreign key. Using the first
alter table statement as an example, would that mean that an index needs
to be created on website(id)?


----
Charles H. Baker
O: 864.422.5349 C: 864.201.8456
charles.baker@datastream.net 
There's nothing wrong with getting a steady paycheck, unless it
interferes with your ability to earn what you're worth. There's the rub.
It usually does. -- T Harv Eker

Re: 110-120-migration.sql

[David M Johnson <Da...@Sun.COM>]

On Jan 27, 2006, at 1:52 PM, Charles Baker wrote:

> This script ran fine until it got to the end where some foreign  
> keys are
> being created
>
> alter table pingtarget add constraint pingtarget_websiteid_fk
>     foreign key (websiteid) references website(id);
>
> alter table autoping add constraint autoping_websiteid_fk
>     foreign key (websiteid) references website(id);
>
> alter table autoping add constraint autoping_pingtargetid_fk
>     foreign key (pingtargetid) references pingtarget(id);
>
> alter table pingcategory add constraint pingcategory_autopingid_fk
>     foreign key (autopingid) references autoping(id);
>
> alter table pingcategory add constraint pingcategory_categoryid_fk
>     foreign key (categoryid) references weblogcategory(id);
>
> I get error 1005 from mysql. Google yielded a tip that an index should
> be on the field before trying to create the foreign key. Using the  
> first
> alter table statement as an example, would that mean that an index  
> needs
> to be created on website(id)?

Hmmm... since website.id is a primary key, it should already have an  
index.

Do the remaining alter table statements complete OK?

- Dave


>
>
> ----
> Charles H. Baker
> O: 864.422.5349 C: 864.201.8456
> charles.baker@datastream.net
> There's nothing wrong with getting a steady paycheck, unless it
> interferes with your ability to earn what you're worth. There's the  
> rub.
> It usually does. -- T Harv Eker
>

RE: 110-120-migration.sql

[John Greenhill <jg...@dayspring-tech.com>]

I put a lot of time in upgrading from 098 to 130 and ran into a number of
issues you're finding. I got the same errors on the scripts, but since it
was just for some constraints I continued the process to see how it would
go.

I found that the scripts alone were not sufficient to jump versions, but
that different roller versions actually had database modifications coded
into the java. Thus, I had to write my own scripts to do what the java in
each version did (things like adding an admin user, dealing with categories
and associations etc).

I never did go back and fix the constraint thing, so I'll be interested in
what you find.

-John

-----Original Message-----
From: Charles Baker [mailto:charles.baker@datastream.net] 
Sent: Friday, January 27, 2006 10:52 AM
To: roller-user@incubator.apache.org
Subject: 110-120-migration.sql

This script ran fine until it got to the end where some foreign keys are
being created

alter table pingtarget add constraint pingtarget_websiteid_fk
    foreign key (websiteid) references website(id);

alter table autoping add constraint autoping_websiteid_fk
    foreign key (websiteid) references website(id);

alter table autoping add constraint autoping_pingtargetid_fk
    foreign key (pingtargetid) references pingtarget(id);

alter table pingcategory add constraint pingcategory_autopingid_fk
    foreign key (autopingid) references autoping(id);

alter table pingcategory add constraint pingcategory_categoryid_fk
    foreign key (categoryid) references weblogcategory(id);

I get error 1005 from mysql. Google yielded a tip that an index should
be on the field before trying to create the foreign key. Using the first
alter table statement as an example, would that mean that an index needs
to be created on website(id)?


----
Charles H. Baker
O: 864.422.5349 C: 864.201.8456
charles.baker@datastream.net 
There's nothing wrong with getting a steady paycheck, unless it
interferes with your ability to earn what you're worth. There's the rub.
It usually does. -- T Harv Eker