I got an Assertion failed: 201200 "Did not expect to continue row segment" on a production database. I can't find any information about it. Can you help me? Now my db is ok, and I'm running a validate on it. I'm running SA 11.0.1 2808 on windows 2003 R2 x64. |
Depending on your situation, you may want to consider turning on checksums in order to flag disk page corruption more quickly, since the server will assert as soon as it reads a page from disk where the page checksum doesn't match the page contents. It is impossible to know what the initial cause of the problem is. I can tell you that we frequently see corrupt databases from customers where the databases are kept on commodity hardware. The first thing I would do is not only validate your current database copy, but in additional shutdown the server and copy fhe database files to another location and attempt an unload to verify that you can recover all your data. Mr Glenn, I did unload of struture and data with success and after that full valitate with no errors. Can I trust this database or I need to create a new one? It's about 150gb...
(21 Jun '12, 08:45)
Zote
Replies hidden
If VALIDATE ran successfully then the database is very likely just fine. I would consider using checksums to try to catch a media failure as quickly as possible so that, hopefully, any corruption that may occur will affect as few pages as possible. I am aware of an outstanding issue that can raise assertion 201200 - the issue pertains (only) to RECURSIVE UNION queries where during the recursion strings are continuously concatenated, eventually exceeding the query's memory quota. If your application issues RECURSIVE UNION queries then this might be the cause of the problem, but otherwise I would assume the cause is transient disk or memory failure.
(21 Jun '12, 09:19)
Glenn Paulley
Should I activate checksum using alter database or dbeng switch ?
(21 Jun '12, 15:32)
Zote
2
I would use the ALTER DATABASE statement. Make sure you have a backup of your files first, and that you can re-create a database from that backup, before you proceed any further.
(21 Jun '12, 15:47)
Glenn Paulley
I got this error again! Same database, but now running on SA12.0.1.3750 under Windows 2008. This night I'll do copy and validation.
(10 Dec '12, 07:22)
Zote
|
Sometimes RAM errors cause symptoms that look like disk errors. Remember, EVERYTHING goes through RAM... SQL Anywhere doesn't actually see data on the disk, it only sees data after it's been copied to RAM by the O/S, BIOS, device drivers and disk adapters. RAM errors are particularly evil since continued use of bad RAM can CAUSE true data errors, and can in some cases cause complete destruction of data even though the physical disk drive is flawless. If you suspect a RAM error, stop using the database immediately and run a full diagnosis of the RAM. With commodity hardware, it's often easier/cheaper just to swap out the RAM, or swap computers. Hi Breck, our server uses ECC, is that possible to get memory errors without ECC detect it? Anyway, at this time I can't run diagnosis on RAM, since it's 16gb and I need to shutdown my server to do it, and I have several db that I can't stop. Any tips?
(26 Jun '12, 10:21)
Zote
Replies hidden
Most ECC memory will detect 1 and 2 bit errors and correct 1 bit errors. Multi bit errors may occur undetected, als long as the redundant bits stay the same. But practically speaking the probabilty for those errors should be rather low.
(26 Jun '12, 11:22)
Reimer Pods
FWIW, the following FAQ deals with RAM checks, too - though they surely will require a server shutdown...
(26 Jun '12, 15:58)
Volker Barth
|
We are able to reproduce an issue that results in the assertion error 201200 "Did not expect to continue row segment". Its reference number is QTS 674549. In our case, that recursive clause of a recursive select statement first cause the non-fatal asssertion error 111706 or 111707 and then the fatal assertion error 201200 when trying to write a subsequent row segment. For example: The below recursive select does a string concatenation z||c and exceeds the describe data type "char(10)" so it should return a SQL error but not an assertion error. create table T1 ( a int, b int, c char(10) ) with recursive V0 ( x, y, z ) as ( select a, b, c from T1 where a = 1 and b = 2 union all select a, b, z||c f from T1,V0 where T1.a = V0.y ) select * from V0 You may check for recursive selects and/or assertion errors 111706 or 111707. To work around the above issue the corresponding result set column in the first subselect needs to be casted to a larger type, e.g.: with recursive V0 ( x, y, z ) as ( select a, b, cast(c as varchar(32767) from T1 where a = 1 and b = 2 union all select a, b, z||c f from T1,V0 where T1.a = V0.y ) select * from V0 We are currently looking for a proper fix for this issue. I'm not getting any assertion failed before 201200. Here is my output log: I. 06/29 08:54:46. Now accepting requests I. 06/29 09:00:03. Performance warning: No unique index or primary key for table "tbIteAr" in database "credinet9" I. 06/29 09:02:47. Starting checkpoint of "credinet9" (Credinet.db) at Fri Jun 29 2012 09:02 I. 06/29 09:02:47. Finished checkpoint of "credinet9" (Credinet.db) at Fri Jun 29 2012 09:02 I. 06/29 09:02:48. Starting checkpoint of "credinet9" (Credinet.db) at Fri Jun 29 2012 09:02 I. 06/29 09:02:48. Finished checkpoint of "credinet9" (Credinet.db) at Fri Jun 29 2012 09:02 I. 06/29 09:06:22. Cache size adjusted to 9944816K I. 06/29 09:07:22. Cache size adjusted to 11902584K I. 06/29 09:08:22. Cache size adjusted to 12390992K I. 06/29 09:09:22. Cache size adjusted to 12512832K I. 06/29 09:15:06. Cache size adjusted to 12543224K I. 06/29 09:16:06. Cache size adjusted to 12550808K I. 06/29 09:17:50. Starting checkpoint of "credinet9" (Credinet.db) at Fri Jun 29 2012 09:17 I. 06/29 09:18:01. Finished checkpoint of "credinet9" (Credinet.db) at Fri Jun 29 2012 09:18 I. 06/29 09:18:07. Cache size adjusted to 12552704K E. 06/29 09:18:13. *** ERROR *** Assertion failed: 201200[credinet9] (11.0.1.2808) Did not expect to continue row segment I. 06/29 09:18:13. Database server shutdown requested via server console Today I get it 3 times (until now). This error is driving me crazy!
(29 Jun '12, 08:25)
Zote
|
The assertion means that a database page was corrupted. The cause can be due to a software fault, or a transient or permanent corruption of the media. Do you have checksums enabled for your database? Is there anything in the Windows event log that would pertain to the failure? How do I check if checksums is enabled? I'm looking windows event log.
(20 Jun '12, 16:43)
Zote
Replies hidden
Glenn, nothing on event log. Just 2 assertion failed.
(20 Jun '12, 16:44)
Zote
SELECT DB_PROPERTY ( 'Checksum' );
(20 Jun '12, 16:47)
Glenn Paulley
It's Off...
(20 Jun '12, 16:55)
Zote
|
Turn on request level debugging (server options -zr sql -zo requestlog.out) and look for the last requests. This should point you to the causing sql statement. I did it and I'm looking file right now.
(29 Jun '12, 09:11)
Zote
The last sql just before the assertion failed was what caused the error may have been some before it?
(29 Jun '12, 09:15)
Zote
One of the last uncompleted requests. Requests start with ,<, and end with ,>, in the request log. If you cannot find a request you likely need to run with -zrall to also print all fetch requests.
(29 Jun '12, 09:22)
Hartmut Branz
Replies hidden
I'm runnig -zr all...
(29 Jun '12, 09:27)
Zote
1
I was able to reproduce the problem! It occurs when I update a column with accents. Very probably have some error in my application, but this should not cause the shutdown of the database. This application is PHP and does this by calling a webservice update the database.
(29 Jun '12, 10:28)
Zote
|
Got same error on recursive select. Looks like BUG in SA. 12.0.1.3769
(19 Oct '12, 02:14)
Andrei
Replies hidden
In case your situation fits Hartmut's explanation, does his workaround resolves the problem? FWIW, the according fix for this CR is documented to be in build 12.0.1.3788, so not yet contained in your version (and not yet available on Windows/Linux, AFAIK).
(19 Oct '12, 03:26)
Volker Barth
I use long nvarchar (not a varchar or nvarchar). So it doesn't look as my case. I used recursive select in a View ( automaticaly generated column already was varchar(32767) ). What I did to workaround? 1) rebuild DB (dbunload -c "{skiped}" -ar ) 2) tried to use my View, but got same assertion 3) so I had to rewrite my view to do not use recursive select
(20 Oct '12, 16:03)
Andrei
Replies hidden
1
If you have a recursive query with your problem as a reproducible, feel free to show it here...
(22 Oct '12, 03:21)
Volker Barth
|