PDA

View Full Version : New Version of CK is much more Picky about int files:



Michael Mullan
7-Jan-2017, 07:38 AM
This has been running for years on earlier versions. But the 19.0 runtime gives


C:\DataFlex\19.0\Fionadh\Programs\Fionaidh.exeCan' t open table.
Invalid intermed file index desc.
Index number: 3, Index name: color003.
Client index definition not complete OR index not defined on server.
Intermediate file = color.INT, Keyword = , Value =


Error: 75
Error Source = MSSQLDRV.Open


MSG_FIXCOLORTABLE (17894) - odbSetup (546) - at address 165872
MSG_DBCHECK (17829) - odbSetup (546) - at address 161958
[start] - at address 167881




Added the missing index on the server and now all is happy.

Stephen W. Meeley
7-Jan-2017, 08:44 AM
I wonder if you would do a refresh it would have seen the index didn't exist and just set it as a client-only index? You may want to try that just to see. Can you send me the .int file so I can see what it thought the offending index was supposed to be?

Michael Mullan
7-Jan-2017, 09:02 AM
Sorry,

First thing I did was select COLOR in the table Explorer, and hit the refresh button on the toolbar. - no change.

SQL used to fix the error in SSMS.



USE [FurSystem]
GO


/****** Object: Index [color002] Script Date: 1/7/2017 7:25:16 AM ******/
CREATE UNIQUE NONCLUSTERED INDEX [color003] ON [dbo].[color]
(
[Code3] ASC,
[color] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO




Int File: (did not change after I fixed the db on the server)



DRIVER_NAME MSSQLDRV
SERVER_NAME DFCONNID=FIONAIDH
DATABASE_NAME color
SCHEMA_NAME dbo


RECNUM_TABLE YES
PRIMARY_INDEX 0
GENERATE_RECORD_ID_METHOD IDENTITY_COLUMN
TABLE_CHARACTER_FORMAT ANSI
USE_DUMMY_ZERO_DATE YES


FIELD_NUMBER 1
FIELD_LENGTH 8
FIELD_INDEX 1


FIELD_NUMBER 2
FIELD_INDEX 2


FIELD_NUMBER 3
FIELD_INDEX 3


INDEX_NUMBER 0
INDEX_NAME color000


INDEX_NUMBER 1
INDEX_NAME color001


INDEX_NUMBER 2
INDEX_NAME color002


INDEX_NUMBER 3
INDEX_NAME color003

In truth, I don't thing there is anything else the studio, or the CK could have done. The index should have been there, but was not. There was not enough information anywhere except in my head to deduce what the index should have looked like.

The only change I'd like to see on this is to actually make a "handled" error out of this, with a more friendly "UserError" rather than just ERROR.

ERROR Missing Index. Index "color003" for table "COLOR" is not defined in database "FurSystem", on server "BigBlue\Fur". Please call tech support.

Stephen W. Meeley
7-Jan-2017, 09:31 AM
Michael,

I can see the cause but I'm wondering if a refresh should have simply deleted that reference in the .int file - a fresh connect would not have created an .int entry for it so why would it survive a refresh?

The way things work is that server indexes don't need anything in the .int file other than the index name. Client only indexes can also have names, but must have the segments defined at well. Sever only indexes are not defined in the .int at all.



// This is a server index (exists at the server, defined in the .int file)
INDEX_NUMBER 1
INDEX_NAME PK_Track

// This is a client only index (does not exist at the server, defined in the .int file)
INDEX_NUMBER 2
INDEX_NAME IFK_TrackAlbumId_C
INDEX_NUMBER_SEGMENTS 2
INDEX_SEGMENT_FIELD 3
INDEX_SEGMENT_FIELD 1


Prior to 19.0, a refresh would have just "thrown away" any existing .int file and created a new one only based on the server definition of the table. Now there is logic to attempt to preserve existing .int information and marry it with what the sever has for it's structure. In this case, I think the fact that color003 did not exist at all at the server, combined with the lack of segment information in the .int file should have resulted in that index being removed.

I'm also wondering if just hitting the refresh button actually did the full process (like Refresh in the connect wizard does) - it's supposed to but I'll check this. Now that we know the trigger (thank you) we can focus on providing the best outcome.

We knew that refining this refresh process would be an iterative one...

Michael Mullan
7-Jan-2017, 09:47 AM
Well the Index3 is referenced by Field3, so that may have something to do with it. (I can't delete that index, because it's defined as the main index for column 3.)

This is a better case than actually deleting it, because the application code may well depend on the definition for the main index.

To be absolutely clear here, I'm all in favor of the runtime throwing a fit at this particular circumstance, because somebody messed with my database and removed an otherwise required index. Silently deleting it and slinking off into the night would not be on my list of acceptable outcomes.

Stephen W. Meeley
7-Jan-2017, 10:02 AM
Yup - seems like the refresh in the table editor is not as healing as the refresh in the connection wizard. Here's what I did...



Opened Chinook workspace and made sure all was well
Opened SSMS and added a new index to the Track table (Test_Index_Michael - column composer, non-clustered)
Went back to the Studio and opened Track in the table editor - I could see the new server-only index and then changed it to server so that it would be expressed in the .int file (by number and name only, no segments) and saved - .int file properly updated
Closed the Track table in the Studio
Went back to SSMS and deleted Michael (I have to admit that felt strangely empowering)
Went back to Studio and tried to open Track and got the error that it could not be opened
Did a Refresh Table from Table Explorer (tried both the button and the top and the context menu) and no change, still could not open Track
Ran the Connection Wizard and selected the Refresh option (only selected the track table)
Et voila! The table could now be opened and the table definition was correct!


So, a few things to be gained from this. We can create more instances where the table defined at the server does not match what the .int is saying about what should be at the server (as opposed to client only information) and also see if the refresh at the table explorer can do the full job (or explain why not).

Thanks!

Stephen W. Meeley
7-Jan-2017, 10:05 AM
Michael,

I wouldn't expect the system to do anything at runtime - just what the Studio should do for a refresh (and be consistent about what a refresh does - it's currently different between the Table Explorer and the Connection Wizard).

Michael Mullan
7-Jan-2017, 11:40 AM
The error I originally reported was a runtime error in the application. The studio happily compiled and started the app, because the table wasn't open in the studio.

The request about the error message was about the runtime, in case this happened in production, where the It Guy killed an index, or we forgot/failed to apply the update code to create the index.

MM.