I have discovered a bug in the MySqlMigrationSqlGenerator class with RenameColumnOperation objects. Initially this bug was reported as #71102 on december 6 2013.
http://bugs.mysql.com/bug.php?id=71102. The bug was closed on december 30 2013 and marked as fixed but after checking the sources of the .NET connector I concluded that the bug has not been fixed.
The problem I have is that it is not possible to rename an existing column due to broken SQL being produced by the MySqlMigrationSqlGenerator class. I applied the suggested fix by the bug reporter but this still produces the following errors when performing an update-database:
Parameter '@sqlstmt' must be defined.
It appears that when performing database updates using user-variables from the NuGet console doesn't work. I need this functionality so I am set on fixing the bug and submitting a patch to be included in a next release. I re-wrote the solution as a stored procedure but it appears that no matter what I try, user variables do not work. I came up with the following:
begin
declare columnType varchar(512);
set columnType = (select case lower(IS_NULLABLE) when 'no' then CONCAT(`COLUMN_TYPE`, ' ' , 'not null ') when 'yes' then `COLUMN_TYPE` end from information_schema.columns where `TABLE_NAME` = 'Agency' and `COLUMN_NAME` = 'Descript' );
set @query := concat("alter table `Agency` change `Descript` `Description` ", columnType);
select @query;
prepare stmt from @query;
execute stmt;
deallocate prepare stmt;
end
However, whenever the select @query is executed the result is ALWAYS null and when calling/running this stored procedure from within HeidiSQL or the NuGet Package Manager console I always get the same result: @query is null.
Could anyone shed some light on my case? This scenario is a serious problem as the work around solution involves direct SQL during the migration but that doesn't work on MS SQL server as the statements are different.