Problem with changing primary key length

[Fredrik]

I've run into a bit of a problem. In MySQL there seems to be a limit for how large varchar primary keys can be before they are converted to text. 255 seems fine but 256 throws a: 'Specified key was too long; max key length is 767 bytes'

If I try to reduce the key length by one it works good in new setups but when applying to a previous database it results in the following message:
'The migration of a primary key to a different type is not supported. PrimaryKey column [KeyName] of table [DataStore] is modeled as nvarchar(255). Database has varchar(256)'

I thought of adding another field, int or Guid to be the identity key in order to open up "KeyName" for change, which works but not for databases that already have data in the table since new keys are not created (even with autoinc).

Is there any way I can solve this to make the system to work with MySQL and in the same time handle all the sites already running the system?

The original defintion:

MapType(x => new { }).WithConcurencyControl(OptimisticConcurrencyControlStrategy.Changed).ToTable("DataStore");
HasProperty(x => x.KeyName).IsIdentity().ToColumn("KeyName").IsUnicode().IsNotNullable().WithVariableLength(256);

[Viktor Zhivkov]

Hi Fredrik,

Telerik Data Access does not support migration of primary keys by design and we believe this is the safest choice that we could make.
If you have to perform the change you can apply manually the change either through some graphical editor of the data table or via SQL script.

If you use SQL script the best way may be to:

1. backup your database so you can restart in case something goes wrong 
2. create a new table with temporary name, same column structure and new primary key column
3. then copy data from original table to the new one
4. verify that the migration is correct and you have not lost data especially in the primary key(s)
5. drop the original table, but remember the original table name
6. rename the new table to have the original name
7. run your application tests and/or validate manually that the migration was successful

Regards,
Viktor Zhivkov
Telerik