6-0-15 to 6.2.7 upgrade - COAPI changes

[Might Aswell]

Hi all.. just a quick ? for anyone that might know... just upgraded from 6-0-15 to 6.2.7 and see a lot of schema changes.. I notice objectid is to be dropped in many tables.. should I be worried about this.. seems like I use objectID in a lot of places...

[Chris Kent]

Check the message that you see, if I remember correctly it was dropping indexes and not dropping columns for objectID.

Chris.

[Might Aswell]

Hi Chris...

Here's an example of where I am hesitating... I guess I am not sure what its doing here.. certainly its not going to drop the objectid column from this table...???

refCategories

 Ignore All  Deploy All Defaults

   typename

 Ignore  Drop  Rename   -- select --  typename 

   objectid

 Ignore  Drop

   primary

 Ignore  Deploy

[Blair McKenzie]

That is very strange. It shouldn't be recommending that. I don't know why it would have such an inaccurate idea of what the table should look like, maybe the component it's building the schema (packages/schema/refCategories.cfc) from is corrupted, or there's a problem with your application scope.

Blair

--
You received this message cos you are subscribed to "farcry-dev" Google group.
To post, email: farcr...@googlegroups.com
To unsubscribe, email: farcry-dev+...@googlegroups.com
For more options: http://groups.google.com/group/farcry-dev
--------------------------------
Follow us on Twitter: http://twitter.com/farcry
---
You received this message because you are subscribed to the Google Groups "farcry-dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to farcry-dev+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[AJ Mercer]

Maybe try reloading coapi

Or update app

Or restart CFML engine

On Thursday, 21 March 2013, Blair McKenzie wrote:

That is very strange. It shouldn't be recommending that. I don't know why it would have such an inaccurate idea of what the table should look like, maybe the component it's building the schema (packages/schema/refCategories.cfc) from is corrupted, or there's a problem with your application scope.

Blair

On Thu, Mar 21, 2013 at 4:12 AM, Might Aswell <chris...@gmail.com> wrote:

Hi Chris...

Here's an example of where I am hesitating... I guess I am not sure what its doing here.. certainly its not going to drop the objectid column from this table...???

refCategories

 Ignore All  Deploy All Defaults

   typename

 Ignore  Drop  Rename   -- select --  typename 

   objectid

 Ignore  Drop

   primary

 Ignore  Deploy

On Wednesday, March 20, 2013 9:57:28 AM UTC-7, Chris Kent wrote:

--

AJ Mercer
<webonix:net strength="Industrial" /> | <webonix:org community="Open" />
http://twitter.com/webonix

Railo Community Manager

[Might Aswell]

I restarted CF the ref cats problem is still there... here's another example..

Conflicts

nested_tree_objects

 Ignore All  Deploy All Defaults

   nleft

 Ignore  Repair

   nright

 Ignore  Repair

   typename

 Ignore  Repair

   objectname

 Ignore  Repair

   nlevel

 Ignore  Repair

   objectid

 Ignore  Drop

   parentid_index

 Ignore  Deploy

   IDX_NTO

 Ignore  Deploy

[Might Aswell]

dmhtml also...

dmHTML

 Ignore All  Deploy All Defaults

   Title

 Ignore  Repair

   catHTML

 Ignore  Repair

   metaKeywords

 Ignore  Repair

   label

 Ignore  Repair

   objectID

 Ignore  Drop

   PRIMARY

 Ignore  Deploy

On Wednesday, March 20, 2013 9:50:51 AM UTC-7, Might Aswell wrote:

[AJ Mercer]

what database server / version are you using?

--

You received this message cos you are subscribed to "farcry-dev" Google group.
To post, email: farcr...@googlegroups.com
To unsubscribe, email: farcry-dev+...@googlegroups.com
For more options: http://groups.google.com/group/farcry-dev
--------------------------------
Follow us on Twitter: http://twitter.com/farcry
---
You received this message because you are subscribed to the Google Groups "farcry-dev" group.
To unsubscribe from this group and stop receiving emails from it, send an email to farcry-dev+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Might Aswell]

Attached dumps

On Wednesday, March 20, 2013 10:53:13 PM UTC-7, Jason Barnes wrote:

Not sure if my previous post worked but cfdump the following two scopes for debugging and attach as txt files to this thread:

application.fc.lib.db.tablemetadata

application.stCoapi["dmHTML"]

[Might Aswell]

Any thoughts? 

On Wednesday, March 20, 2013 10:53:13 PM UTC-7, Jason Barnes wrote:

Not sure if my previous post worked but cfdump the following two scopes for debugging and attach as txt files to this thread:

application.fc.lib.db.tablemetadata

application.stCoapi["dmHTML"]

On Thursday, March 21, 2013 3:50:51 AM UTC+11, Might Aswell wrote:

[Blair McKenzie]

Jason gave me poke to take a look at the dumps. The second dump you sent (tablemetadata) is the struct that FarCry compares with the DB as it is. You've mentioned refCategories, nested_tree_objects, and dmHTML - all three have an "objectid" property in their array of fields, and FarCry should only suggest dropping a field if it ISN'T in that struct and IS in the database. I think we can firmly rule out memory corruption / ColdFusion weirdness.

What DB are you using? Is it possible you have case-sensitivity enabled? - that has caused problems for us in past.

Blair

--

[Might Aswell]

I'm using MSSQL2K5  I dont believe any case sensitive settings have been set..

[Jason Barnes]

Can you paste the db collation 

You received this message because you are subscribed to a topic in the Google Groups "farcry-dev" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/farcry-dev/fAT9Z3jy-LE/unsubscribe?hl=en-GB.
To unsubscribe from this group and all of its topics, send an email to farcry-dev+...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--
Jason Barnes | CTO | Daemon | p. 02 8999 8885 | http://www.daemon.com.au

[Geoff Bowers]

Chris,

This isn't something we can replicate internally so I'm assuming its
an environment issue.

Can we get the following:
- operating system/webserver/CF version
- database gateway in use (ie. what is application.dbtype)

Hopefully we can get a bit further with that info ;)

GB

[Might Aswell]

Hi Geoff,

I am running on Windows Server 2003, IIS6, CF8 and MSSQL2005.

application.dbtype is set to mssql

Plans are in the works to begin migrating to Win2008/IIS7x/CF10/MSSQL2008

But I am hoping to get everything 'working' on the current dev/prod platforms first.

[Might Aswell]

MSSQL 2005 -   Collation: SQL_Latin1_General_CP1_CI_AS

[Blair McKenzie]

Can you try "mssql2005" as the dbtype? I don't remember what the differences where that required a separate db gateway, but this issue may be related.

Blair

[Might Aswell]

<cfcomponent displayname="Category References" hint="Category-Object associations" extends="schema" output="false">

<cfproperty name="objectid" type="uuid" dbNullable="false" dbPrimaryKey="true" />

<cfproperty name="categoryid" type="uuid" dbNullable="false" dbPrimaryKey="true" />

</cfcomponent>

looking at refCategories.cfc... Is is legit to have 2 primary keys in a table?

[Jason Barnes]

It's one primary key made up of two properties which is valid.

Sent from my iPhone

[Might Aswell]

Thanks Jason,

I seem to be having issues cleaning up COAPI after this upgrade, particilarly on refcats and refobjects as I have duplicate values

for example, trying to deploy changes for refcats..

[Macromedia][SQLServer JDBC Driver][SQLServer]The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.refCategories' and the index name 'PK__refCategories__59C61FAD'. The duplicate key value is (1A31242A-4636-11DE-BF9A005056B02320, FDAAFF10-C25E-11DE-A383005056B02320).

refobjects

[Macromedia][SQLServer JDBC Driver][SQLServer]The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.refObjects' and the index name 'PK__refObjects__5BAE681F'. The duplicate key value is (05BC5C9F-5056-B03B-BAE2CF5A04773B7F).

then for nestedtreeobjects... nleft and right wont repair...

Failed to repair 'nested_tree_objects.nleft' column ---- the object 'CK__nested_tree_obje__67FE6514' is dependent on column 'nleft'. ---- ALTER TABLE ALTER COLUMN nleft failed because one or more objects access this column.

[AJ Mercer]

In farcry webtop admin section,

There is an option to fix refOjects.

May want to back up DB first 

--

[Jason Barnes]

I believe this means in the tables in question you have duplicate values so the PK can't be created. You would need to dedupe the tables in question to be able to then apply the indexing. To do that you'd run the following queries:

Please take a backup first and also change the delete from to select * from first to confirm what will be deleted. Use at own risk, disclaimer etc :)

delete from a

from

(select objectid, categoryid

       ,ROW_NUMBER() over (partition by objectid, categoryid

                          

                           order by objectid, categoryid

                          ) RowNumber 

from refcategories) a

where a.RowNumber > 1

delete from a

from

(select objectid, typename

       ,ROW_NUMBER() over (partition by objectid, typename

                          

                           order by objectid, typename

                          ) RowNumber 

from refobjects) a

where a.RowNumber > 1

[Jason Barnes]

I may be wrong but I believe fix refobjects merely cleans up orphaned objects and not dedupe?

You received this message because you are subscribed to a topic in the Google Groups "farcry-dev" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/farcry-dev/fAT9Z3jy-LE/unsubscribe?hl=en-GB.
To unsubscribe from this group and all of its topics, send an email to farcry-dev+...@googlegroups.com.

For more options, visit https://groups.google.com/groups/opt_out.
 
 

--

[Geoff Bowers]

Hmm.. This is unfortunate. Previously indexation, referential integrity etc was not enforced by the framework -- you needed to optimize the database as part of a FarCry install. That combined with the earlier ORM model not having transactions, leads to the possibility of duplicates in tables that should not have duplicates.

6.1 replaced the database gateways; these are much more robust and enforce indices and so on.

You will need to deduce these tables manually or you will run into trouble.

GB