MS SQL Server for Store
Locked
 
|
I'm having problems using MS SQL Server 2000 as a backend for
Store. I am replicating all of the code from a postgres database into a new SQL
Server database. After awhile, the attempt to replicate the Base VisualWorks
bundle fails with:
ERROR: Message not understood:
#searchFrom:
In digging through the stack, the pertinent ODBCError seems to
be:
-self: an ODBCError
dbmsErrorCode: 2627 dbmsErrorString: '[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint ''PK__TW_Methods__0425A276''. Cannot insert duplicate key in object ''TW_Methods''.' osErrorCode: nil osErrorString: nil sqlState: '23000' A second ODBCError object ferreted out of the SQLSession's
results mechanism is:
-self: an ODBCError
dbmsErrorCode: 3621 dbmsErrorString: '[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.' osErrorCode: nil osErrorString: nil sqlState: '01000' Don't know what #searchFrom: has to do with duplicate keys and
I sure don't know how to proceed. Is my SQL db trash now? I've installed and
reinstalled SQL Server a couple of times. I won't try to say I know what I'm
doing, because I don't. My knowledge of db administration is smaller
than Don Imus' remaining career.
Charles Adams
Adventa Control Technologies, Inc. |
|
|
searchFrom: is to do with exceptions - it appears that the
exception handling is failing at some point in handling these errors,
possibly trying to resume without success.
A full stack would be useful, as would information on exactly which versions, and which replicator you're using. I think it's unlikely your database is trash. Databases are good at that kind of thing, and it will just have failed to commit the transaction with the duplicate key. But it might be trash if the duplicate key is because there was fundamental corruption in the database before that. But without knowing more about what's going on, that's very hard to say. At 07:04 PM 4/11/2007, Charles Adams wrote: I'm having problems using MS SQL Server 2000 as a backend for Store. I am replicating all of the code from a postgres database into a new SQL Server database. After awhile, the attempt to replicate the Base VisualWorks bundle fails with: --
Alan Knight [|], Cincom Smalltalk Development
"The Static Typing Philosophy: Make it fast. Make it right.
Make it run." - Niall Ross
|
|
|
I am using VW 7.4. The replicator is
Refactory.StoreReplication.ReplicatorUI. I've attached the stack, but the
detailed section doesn't seem to go deep enough. I tried 'more stack' but
got the same results. The stack itself goes back to the beginning of the
process, but the details stop just short of the actual error.
One thing I find curious is that I created a database
(StoreTest) which it seems to ignore.
Store.DbRegistry>>installDatabaseTables created two new databases:
NEWBERN1 and NEWBERN2. It did not install any tables in StoreTest.
After installing the database tables, I replicated the
Advanced Tools bundle. It went in just fine. So, then I replicated Base
VisualWorks. It replicated for ten minutes or so before hitting this error. The
database files are quite large now; 30+ meg, so it was obviously pumping a
lot of code in.
The doc, SourceCodeMgmtGuide.pdf, says to ensure that it is
installed case sensitive. I had no way of determining that with the current
instance, so I reinstalled SQL Server. I could not find any reference to this
switch. I followed the instructions exactly, but I did not install the user
management tables.
|
sqlerrorstack.txt (29K) |
|
In reply to this post by Charles Adams
Charles,
The primary key of TW_Methods is an int, so this maybe isn't a case sensitivity issue. Still, it's good to get case sensitivity turned on: otherwise you'll get odd versioning problems if you have two methods or versions of methods that differ only in case (happened to us once or twice when we corrected the case of part of a selector).
Our SQLServer was installed with Finnish case-insensitive collation, Finnish_Swedish_CI_AS, and because of the other apps running on it we didn't want to change that – let alone reinstall. Although I'm no DBA, I gritted my teeth and used Enterprise Manager to open the NEWBERN2 database and its tables, selected the TW_Method table and chose Design Table. From there I selected the name column and changed its collation to be case-sensitive, Finnish_Swedish_CS_AS. I did the same for TW_ClassRecord, but left the others unchanged. I don't know for sure that this helped, but it made me feel better :-).
Steve
-----Original Message-----
I am using VW 7.4. The replicator is Refactory.StoreReplication.ReplicatorUI. I've attached the stack, but the detailed section doesn't seem to go deep enough. I tried 'more stack' but got the same results. The stack itself goes back to the beginning of the process, but the details stop just short of the actual error.
One thing I find curious is that I created a database (StoreTest) which it seems to ignore. Store.DbRegistry>>installDatabaseTables created two new databases: NEWBERN1 and NEWBERN2. It did not install any tables in StoreTest.
After installing the database tables, I replicated the Advanced Tools bundle. It went in just fine. So, then I replicated Base VisualWorks. It replicated for ten minutes or so before hitting this error. The database files are quite large now; 30+ meg, so it was obviously pumping a lot of code in.
The doc, SourceCodeMgmtGuide.pdf, says to ensure that it is installed case sensitive. I had no way of determining that with the current instance, so I reinstalled SQL Server. I could not find any reference to this switch. I followed the instructions exactly, but I did not install the user management tables.
|
|
|
In reply to this post by Charles Adams
Indeed, that stack stops just short of being useful in
telling us what's actually going on with the rows being written. I'm
suspicious the reason it's cutting off could be because of an error in
printing out the actual exception.
It's possible that case sensitivity would be an issue. You might also try using the replicator in StoreForGlorpVWUI rather than the refactory one. At 08:27 PM 4/11/2007, Charles Adams wrote: I am using VW 7.4. The replicator is Refactory.StoreReplication.ReplicatorUI. I've attached the stack, but the detailed section doesn't seem to go deep enough. I tried 'more stack' but got the same results. The stack itself goes back to the beginning of the process, but the details stop just short of the actual error. --
Alan Knight [|], Cincom Smalltalk Development
"The Static Typing Philosophy: Make it fast. Make it right.
Make it run." - Niall Ross
|
|
|
It occurred to me this morning how to get more stack detail.
Let me try again. Fortunately, I can reproduce this error at will.
Getting the database to "collate" case sensitive is not easy.
Steven Kelly explained earlier how to do it for a single table, but I will
reinstall SQL Server (again) to try to change the system default.
SQL_Latin1_General_CP1_CI_AS
is case insensitive
SQL_Latin1_General_CP1_CS_AS is case sensitive
|
|
|
no no no no no. Don't reinstall.
Check the vwnc archives, there's a code fix that changes the creates in the Store code to do this correctly just for Store's databases.
It's a few years back, but no one seems to change the code in question, so it should go in rather easily.
On 4/12/07, Charles Adams <[hidden email]> wrote:
-- Troy http://www.cincomsmalltalk.com/userblogs/troy/blogView |
|
|
There's the fix, Jeorg Beekman found it first. Anytime I've had to use sql server for store, I end up pulling this out and applying it. I've requested that it be ared and added several times, but I don't know the status of any AR related to it.
On 4/12/07, Troy Brumley <[hidden email]> wrote:
-- Troy http://www.cincomsmalltalk.com/userblogs/troy/blogView |
|
|
In reply to this post by Troy Brumley
Too late :) But that's ok. I'm testing feasibility of SQL
Server as Store backend. We have to move away from postgres due to a recent
crash (database would no longer backup.) I need to get an install procedure down
and I think I've got that now. The advanced options during install allow you to
change the default collation scheme.
Since this is an SQL Server install option, a patch
to the interface code might not make sense.
|
|
|
Charles,
On 12/04/07, Charles Adams <[hidden email]> wrote: > .... We have to move away from postgres due to a recent crash (database > would no longer backup.) Ouch - sounds bad. I use PostgreSQL extensively, and the public Store uses it too, so if there are any lessons to be learned from the problems you had it would be good to learn them. So, if I may ask, what bit you? Many thanks, Bruce -- Make the most of your skills - with OpenSkills http://www.openskills.org/ |
|
|
In reply to this post by Charles Adams
Charles, here's the problem ... most MS tools and users expect the default to be case insensitive, so by changing the default for a whole server, you mess with the minds and software of others. If this is a dedicated "store only" server, that probably isn't a problem for you, but it's still wrong to have to change a whole server when a simple software tweak to configuration data for one particular database is less invasive.
Good luck,
Troy.
On 4/12/07, Charles Adams <[hidden email]> wrote:
-- Troy http://www.cincomsmalltalk.com/userblogs/troy/blogView |
|
|
Troy,
I understand and yes it is a dedicated Store server...for now.
But you know that might not always be the case.
I'll go look at the link you sent; I think you are right: this
tweak should get into the build.
|
|
|
In reply to this post by Bruce Badger
Bruce,
Yeah, I thought it might interest someone. There is an open
case, 394835, with Cincom support but so far have not heard anything. I am
running PostgreSQL 8.1 under SunOS 5.9 connecting from images on Windows XP
SP2.
While trying to load some code, I received:
ERROR: could not open relation 1663/2365174/2365282: No
such file or directory As I recall, this happened on specific package versions and
not everything. Then I tried to make a backup. Here's
the end of the log:
...
pg_dump: ERROR: invalid page header in block 5923 of
relation "tw_blob"
pg_dump: SQL command to dump the contents of table "tw_blob" failed: PQendcopy() failed. pg_dump: Error message from server: ERROR: invalid page header in block 5923 of relation "tw_blob" pg_dump: The command was: COPY public.tw_blob (primarykey, blobtype, blobdata) TO stdout; pg_dump: *** aborted because of error The error was reported to Cincom the middle of last month.
Regardless of the outcome, my faith in postgres was reduced to the point that I
could no longer recommend to my customer continued use. My customer has
site licenses for SQL Server, so we decided to investigate
migrating.
----- Original Message -----
From: "Bruce Badger" <[hidden email]>
To: "Charles Adams" <[hidden email]>
Cc: <[hidden email]>
Sent: Thursday, April 12, 2007 12:26 PM
Subject: Re: MS SQL Server for Store > > On 12/04/07, Charles Adams <[hidden email]> wrote: >> .... We have to move away from postgres due to a recent crash (database >> would no longer backup.) > > Ouch - sounds bad. I use PostgreSQL extensively, and the public Store > uses it too, so if there are any lessons to be learned from the > problems you had it would be good to learn them. > > So, if I may ask, what bit you? > > Many thanks, > Bruce > -- > Make the most of your skills - with OpenSkills > http://www.openskills.org/ |
|
|
Charles,
On 12/04/07, Charles Adams <[hidden email]> wrote: > Yeah, I thought it might interest someone. There is an open case, 394835, > with Cincom support ... > ... > pg_dump: ERROR: invalid page header in block 5923 I don't think Cincom could help you with this one. If you do a Google search on "ERROR: invalid page header in block" you'll see quite a few threads. They all seem to conclude the same thing which is a physical corruption in the database files with the finger being pointed at a hardware or kernel problem(!). There are some actions that can be taken to get the database to work again, but the data in the damaged page(s) is most likely lost. With a backup and a journal the data can be recovered but without those you'd be stuck :-( Sorry to hear you hit this. I hope you find smoother sailing with your new DB. All the best, Bruce -- Make the most of your skills - with OpenSkills http://www.openskills.org/ |
|
|
Bruce,
Thanks for the info. As I said this error has soured my
customer on postgres so that now I am trying to use SQL Server. We continue to
use postgres at Adventa and I don't know if this incident will affect that
decision. Postgres does come at the magic price of $0, after
all.
Fortunately, VW provides parcels to interface with various
database engines and replication between different engines is seamless. This is,
of course, a key feature and one I now appreciate very much.
Thanks,
Charles Adams
----- Original Message -----
From: "Bruce Badger" <[hidden email]>
To: "Charles Adams" <[hidden email]>
Cc: <[hidden email]>
Sent: Thursday, April 12, 2007 4:07 PM
Subject: Re: MS SQL Server for Store > > On 12/04/07, Charles Adams <[hidden email]> wrote: > >> Yeah, I thought it might interest someone. There is an open case, 394835, >> with Cincom support ... >> ... >> pg_dump: ERROR: invalid page header in block 5923 > > I don't think Cincom could help you with this one. If you do a Google > search on "ERROR: invalid page header in block" you'll see quite a > few threads. They all seem to conclude the same thing which is a > physical corruption in the database files with the finger being > pointed at a hardware or kernel problem(!). > > There are some actions that can be taken to get the database to work > again, but the data in the damaged page(s) is most likely lost. With > a backup and a journal the data can be recovered but without those > you'd be stuck :-( > > Sorry to hear you hit this. I hope you find smoother sailing with your new DB. > > All the best, > Bruce > -- > Make the most of your skills - with OpenSkills > http://www.openskills.org/ |
|
|
In reply to this post by Charles Adams
Thanks Troy!
In SQLServer 7.0 (what we had when we started using Store), the collation was global for the whole SQLServer: all databases, tables and columns had the one setting. In SQLServer 2000, the collation can be set at the server, database and column levels. However, changing the collation on an existing database doesn't change existing columns – hence my manual fix to the columns.
The VW code fix you linked to is thus great, and should be included in the base, but for existing Store dbs we need something else. One promising candidate seems to be: http://www.codeproject.com/vb/net/ChangeCollation.asp The "demo project" there is the actual compiled tool, which can be run from any Store client PC (providing you have sufficient rights or know the SQLServer admin password). It alters the database collation and updates all columns to use it, so they do not need to be changed manually. You can look at the script it generates before deciding whether to executes it. At least to my eyes (IANADBA :->) it looks like it's doing the right kinds of things. I've not tried it yet (my manual fixes are enough for me), and of course if anyone does you MUST backup your database first.
It also looks like we should maybe avoid SQL server's own collations, e.g. SQL_Latin1_General_CP1_CI_AS, and use Windows', e.g. Latin1_General_CI_AS:
HTH, Steve
-----Original Message-----
There's the fix, Jeorg Beekman found it first. Anytime I've had
to use sql server for store, I end up pulling this out and applying it.
I've requested that it be ared and added several times, but I don't know the
status of any AR related to it. On 4/12/07, Troy Brumley <[hidden email]> wrote: no no no no no. Don't reinstall.
Check the vwnc archives, there's a code fix that changes the creates in the Store code to do this correctly just for Store's databases.
It's a few years back, but no one seems to change the code in question,
so it should go in rather easily. On 4/12/07, Charles Adams <[hidden email]> wrote: It occurred to me this morning how to get more stack detail. Let me try again. Fortunately, I can reproduce this error at will.
Getting the database to "collate" case sensitive is not easy. Steven Kelly explained earlier how to do it for a single table, but I will reinstall SQL Server (again) to try to change the system default.
SQL_Latin1_General_CP1_CI_AS is case insensitive SQL_Latin1_General_CP1_CS_AS is case sensitive
--
|
|
|
In reply to this post by Charles Adams
Alan,
Just to close out this thread, I'll report my current
status.
I am now able to pump the entire contents of my postgres Store
database into my SQL Server Store database -- without error. Perhaps it was the
case sensitive/insensitive issue after all. At any rate, I'm back in
business.
Thanks for your help,
Charlie
|
|
|
Thanks. That'll be very helpful.
At 07:32 PM 4/13/2007, Charles Adams wrote: Alan, --
Alan Knight [|], Cincom Smalltalk Development
"The Static Typing Philosophy: Make it fast. Make it right.
Make it run." - Niall Ross
|
«
Return to VisualWorks
|
1 view|%1 views
| Free forum by Nabble | Edit this page |