Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

Hello experts,

I've got a Ultralite BUG during a database schema upgrade using the ALTER DATABASE SCHEMA FROM FILE command.

If I try to add an index to a column of a existent table that already has an indexed column (by schema migration), the Ultralite database becomes invalid and the following error is shown during the database validation:

Validation failed:
Index validation failed for table Test, index Test with code: 120

Is there any workaround for it? I've used the SqlAnywhere

Following, I put the steps to reproduce this problem (All steps were done using the SybaseCentral):

  • Create a new database (TestDatabase.udb)
    SybaseCentral: ulinit -p "4096" -S "0" --max_hash_size=4 --timestamp_increment=1 -y "TestDatabase.udb"

  • Create a new table:
    Test (id SMALLINT PK, code VARCHAR(50) NOT NULL, value VARCHAR(50))

  • Create a non-unique index to value column

  • Feed the Test table with some data

  • Create a copy of the database to design the second version of schema (TestDatabaseV2.udb)

  • Add the unique constraint for code column of Test table (The Ultralite will add a index to this column automaticaly)

  • Unload the schema for the second schema as a SQL file (TestDatabaseV2.sql)
    SybaseCentral: ulunload -v -y -c "DBN=TestDatabaseV2;CON=N_Sybase Central 1;DBF=TestDatabaseV2.udb" -b 1024 -n -s "TestDatabaseV2.sql"

  • Connect to the first database and execute the schema upgrade using the command below:

  • Check the database, using the "Validate Database..." option of Sybase Central. It will return an index error.
    SybaseCentral: ulvalid -v -c "DBN=TestDatabase;CON=N_Sybase Central 2;DBF=TestDatabase.udb"

The following output is shown:

Checking page (1)
Validating table Test
Validating index primary on table Test
Validating index Test on table Test
Incorrect row count for index Test on table Test
Validating index IX_test on table Test
Validating table sysuldata
Validating index primary on table sysuldata
Validation failed:
Index validation failed for table Test, index Test with code: 120

asked 19 Jan '12, 14:20

Alex's gravatar image

accept rate: 25%

edited 19 Jan '12, 14:26

I assume this is all done on a Windows desktop? What is your deployment platform?

(20 Jan '12, 10:36) Tim McClements
Replies hidden

Yes, but we've tested with the MacOS Desktop as well and the same problem was reproduced. We are targeting the iOS and Android devices (both with the same issue as well)

(21 Jan '12, 13:10) Alex

Thank you for posting this, and for the repro. We will look into it.

At this point I can't think of a workaround other than the obvious :)

permanent link

answered 20 Jan '12, 10:35

Tim%20McClements's gravatar image

Tim McClements
accept rate: 35%

Tim, please, keep in touch if you have any news about this issue. We have a huge product using the Sybase solution and we are almost deciding if we are going to upgrade our client dabatase using either the ALTER DATABASE command or using DLL executions directly in the database. This decision has been a big "headache" to us =)

(21 Jan '12, 13:19) Alex
Replies hidden

Question: Why not use alter-database-schema-from-file?

News: Given what you've provided here, we have reproduced and fixed this bug - thanks. It should be available in build or later.

(26 Jan '12, 16:16) Tim McClements

We were wondering if we could use that feature =)
The hard decision was only about that bug, but as I can see, it isn't going to be a problem anymore =D
Tks so much!

Is there a plan to when the next revision will be released?

(27 Jan '12, 07:16) Alex

Another doubt about why use or not use the ALTER DATABASE command is the renaming of columns and tables.
We are studying implement a mix of the Ultralite command and DDLs to update our database schemas.

Since the RENAME commands are allowed by Ultralite SQL, it would be a really nice feature if this change could be made using the ALTER DATABASE command.

Maybe using a special commented section of the migration script to store a pos-processing batch of SQL commands.
What do you think? =)

(27 Jan '12, 08:44) Alex

No problem!

I don't know exactly when the EBFs will be out, but it should be something like 2 - 6 weeks.

(30 Jan '12, 11:57) Tim McClements

As you say, it would require some special work to add a rename feature to alter-database-from-file. We decided originally not to do it because of the added complication, and we viewed the ability to rename as secondary compared to more substantial and typical schema changes (like adding a column or modifying a column type or adding an index).

If you really need rename, then yes, I would suggest you do that with DDL before or after the alter-database statement (depending on whether the new names are in the .sql file or not).

Alter-database is a powerful statement because it automatically determines the delta required to move from the current schema to the new one. If you need to do renames (and not knowing exactly how your application works) then you may end up having to track the versions and deltas yourself anyway, in which case you'd have the option of using all individual DDL statements if that suited.

(30 Jan '12, 12:14) Tim McClements

Ok. We are going to use the alter-database-schema command and maybe some individual DLL executions whenever we need rename some database object.

Thank you very much.

(30 Jan '12, 12:53) Alex
showing 1 of 7 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 19 Jan '12, 14:20

question was seen: 6,427 times

last updated: 30 Jan '12, 12:53