ASA10 upgrade to ASA12 with max_inline !=NULL in sys.systabcol: reducing size of char fields ERROR

[Former Member]

When in ASA10 database a char column is changed (with alter table), then the field max_inline in table sys.systabcol will be filled. When upgrading this database to ASA12 (12.0.1.3389 or 12.0.1.3519) the size of the char field cannot be reduced anymore: this gives the error Illegal column definition.

Reproduction:

/*Create a database in ASA10 and create a table:*/
create table tSample(
tField char(100)
);
/*Fill the field with some data*/
insert into tSample(tField) values('A');

/*alter size of the field in ASA10, resulting in sys.systabcol.max_inline=75*/
alter table tSample
alter tField char(75);

Now convert to ASA12!!!

/*After conversion trying to reduce the field size will fail:*/
alter table tSample
alter tField char(70);

Secondary problem:

When the size of the field was NOT cahnged in ASA10, the field sys.systabcol.max_inline is still empty on conversion. After the conversion, it is only one time possible to change the field size. After that sys.systabcol.max_inline is filled and any consecutive reduction of the char field size results in the same error Illegal column definition.

Please advice, since we are on the verge of bringing out our new software, only waiting to resolve this problem.

[MCMartin]

I have tried to reproduce your secondary problem in 12.0.1.3519, without the same effect, for me its possible to reduce the char size after max_inline has been populated. Therefore I think, that your observation might be a non related coincidence.

[Former Member]

Martin, you tried this with char fields (including data in them) in an ASA10 database and then upgrade the database to ASA12? (via dbupgrad.exe) Then in the ASA12 try to reduce size of those fields at least 2 times - the second time the error is given.

[VolkerBarth]

Have you tried to do a full reload (aka dbunload) instead of just upgrading the database from v10 to v12?

For me, "ugrading to a newer version" usually means to reload the database - in order to make use of newer features only available with a changed physical storage... dbupgrad primarily updates the system catalog, AFAIK.

[Former Member]

Sorry, but upgrading to me means using the dbupgrad.exe tool We give our customers a tool which interacts with DbLib12.dll, which essentially does what dbupgrad.exe does and some more.

what you do is create a new database with structure and data from an old one, which can take some time (a whole day is no exception!). That is no update (with dbupgrad.exe which only takes a few minutes) in my understanding.

[VolkerBarth]

I certainly agree that you are using the term "upgrade" in its original meaning. However, it's just as common (at least in this forum, I guess) to refer to reloading as upgrading, too. Even the whole doc book describing the new features is called "Changes and Upgrading", and as stated only a full reload makes all these new features available.

FWIW: The following FAQ discusses some pros of a full reload - the increased time (and diskspace) needed is a disadvantage, apparently.

---

Besides that, I just tried to point out that the problem may not appear with a reloaded database, and that this might be a workaround...

[MCMartin]

No I just tried the 2nd part, I used a DB which we have where the column haven't had already a value in max_inline and added some chars, checked that max_inline was populated and changed it back. The column contained data.

[Former Member]

Yes, I posted it there but response was very low. John can be right, but it is not a solution just to point out there is a bug report. Our customers want to update to their new version and we cannot guarantee they can. And we are talking about thousands of customers!

The solution to use 'inline use default' does not work on systems which already have an inline_max value different to the size of the field.

Also, we have a deadline and changing our current software will imply new testing which results in the software update to be too late.

I posted a reply on the public newsgroup too, but as we found this major problem so near to the deadline, we are in a hurry to get this one solved. So, trying to find the solution on different sites (with different people) is needed in this case, sorry.

[reimer_pods]

Although this forum provides a lot of helpfull hints and solutions, not all problems can be solved in a few hours. Even a know bug in SQL Anywhere will take some time until it's fixed, has passed tests and finally makes it into the next EBF.
If we had a problem with an urgency like yours we'd surely contact Sybase directly and open a support case.

[Former Member]

Reimer, done that - Sybase will try to reproduce. I will post the result here.

We know it cannot be solved in a few hours, but a solution or at least a way to make the upgrade work is required.

What we are trying is before the upgrade first checking that the fields with larger inline_max than the width of the fields are changed: Altering the size to inline_max then change back to the original size all with the option 'inline use default'.

It seems to work then, but the problem of ASA10 fields resizing in ASA12 remains. This will be our second concern.

[johnsmirnios]

BTW, posting that there is already a bug report was supposed to be reassuring that it is a known problem and already in the queue. You should call tech support to express your needs for the fix. As I have recently posted in the newsgroup, the problem as posted here is not reproducible and tech support will likely ask for a reproducible case.

I did also post the following known & reproducible problem to show that the 'use default' workaround I suggested does work in that situation. I have included it here too. The following does not work:

create table test ( elbver varchar (40) not null inline 40 prefix 8);

alter table test alter elbver varchar(4);

The following does work:

create table test ( elbver varchar (40) not null inline 40 prefix 8);

alter table test alter elbver varchar(40) not null inline use default prefix use default;

alter table test alter elbver varchar(4);

[johnsmirnios]

When I tried the repro from your post on the newsgroup, I reloaded to v12 prior to the first alter (as per your instructions there) and could not repro the issue. When I did the reload to v12 after the first alter as described in this forum, I repro'd the issue and I can confirm that my suggested workaround works. Just execute the following two statements to get the alter to work:

alter table tSamplealter tField char( 75 ) inline use default prefix use default;

alter table tSample alter tField char( 70 );

In particular, notice that the first statement does not attempt to change the column width.

This simple workaround should allow you to script your upgrades without waiting for an EBF.

[Former Member]

John, we did this and it does work for the conversion. However, it still blocks reducing field size in ASA12.

Since the conversion works, this problem is not as severe as before and we can await the EBF or, in special cases, make a new ASA12 field with all data from the converted field.

[VolkerBarth]

What exactly does "it still blocks..." mean?

Do these columns contain data that is longer than the new specified column size? - Cf. the string_rtruncation option.

[Former Member]

'it still blocks' means that it is not possible to reduce the size of the field, giving the error Illegal column definition.

On testing i tried a field with char(50) to reduce to char(40). It held one record with '123' only. So, data conversion is not needed.

The case with Sybase is reproduced and is forwarded to the technicians. We will see what the result will be.

[Former Member]

We are getting this error in SQL Anywhere 16. Is there any bug reported to Sybase? or is this issue fixed?

[VolkerBarth]

I don't know for sure but an according fix might have been made in v12.0.1.3575 - cf. this CR 696638 note:

Using an ALTER TABLE statement to change the length of a string column (char, [long] varchar, [long] binary) that used default INLINE/PREFIX values, would also have explicitly set the INLINE/PREFIX values for that column if there was data in the table. This has been fixed.

[Former Member]

Arco had posted the identical question in the NNTP forum sybase.public.sqlanywhere.general, where John Smirnios had indicated that this is a known issue and will be fixed in a forthcoming EBF.

---

Edit: To cite John's response here:

Use two alters: first 'inline use default' then change the datatype. There is already a bug report in the system to prevent the max_inline value from being set to non-null unnecessarily.

-john.

[t1950]

i've upgraded many db's from ASA 8 to 11 and several to ASA 12.
i would suggest using Sybase Central and unload the old db (schema and data)
do not load into a new db.
create a version 12 db.
open iSQL and open reload.sql.
you may get errors because of system table changes (table not found)
i've ignored the errors and the db works as it does in ASA 8.
i've unloaded and reloaded a 3 gig db in less then an hour.

[Former Member]

It is impossible to use Sybase Central, because the upgrade must be done with 1000s of customers, done by themselves.

I replied on another forum (currently the wrong wone, because all discussions must be held here...):

With the convert to ASA12 I mean: DbUnload -ar -y -u -dc -er -c "uid=..."

I hope you didn't forget to enter some data in the field in ASA10?

Also, when the size was not changed in ASA10, it should be changed two time in ASA12 to reproduce the error (first time the inline_max was still NULL)

I tried this with a complete new database created in ASA10 with dbInit, created table wich char field, populated the table and changed field size. After DbUnload with ASA12 on resizing teh field I always get the error. So it is strange you do not get this error.

[VolkerBarth]

Just to be sure: Are you saying the workaround as stated by John (cf. his comment on your question) does not work in your case?

If it does work (as John expects it), I would be content with such a workaround for the moment - if you have to apply update scripts to thousands of customers, it shouldn't matter whether there are two statements needed instead of one in those scripts... It's a workaround, apparently, and it should be fixed, but that shouldn't prevent you from going on.

Of course, if if does not work in your case, then please disregard my post.

[Former Member]

Volker, we have applied those two lines (for each field which has differences in size / inline_max) so the conversions should work, making it less severe.