MySQL Bugs: #72424: EF Migrations error Subquery returns more than 1 row

Bug #72424	EF Migrations error Subquery returns more than 1 row
Submitted:	22 Apr 2014 19:33	Modified:	7 May 2020 15:16
Reporter:	Gabriela Martinez Sanchez	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.8.3, 6.9.8	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
Using EF 6.1 and MySql.Data 6.8.3 when trying to update the rename column migration resulted in a query error: Subquery returns more than 1 row.

How to repeat:
Trying to rename a column. From a migration file:

   public override void Up()
        {
            RenameColumn("dbo.Trades", "UnrealizedResulResultPctLong", "UnrealizedResultPctLong");
        }

This is the generated query:

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 = 'Trades' and column_name = 'UnrealizedResulResultPctLong');
set @sqlstmt := (select concat('alter table `Trades` change `UnrealizedResulResultPctLong` `UnrealizedResultPctLong` ' , @columnType));
prepare stmt from @sqlstmt;
execute stmt;
deallocate prepare stmt;

The query fails with the error "Subquery returns more than 1 row". This is happening because I have another database with an identical table. So the first query returns two rows. Adding another "where" for the table_schema column should do the trick.

Hi all,

I am struggeling across a similar problem with the RenameColumnOperation in the MySQL Connector/NET.

In my case the first query succeeds with a valid result. But the alter statement does not seem to know the database, it should be executed on. 

Maybe defining the database the anonymous block should be executed on would help in both cases.

I have the same issue, the problem is the first query, it tries to find the datatype of the column:

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 = 'Cat' and column_name = 'Title');

I have 5 databases with the table name Cat and the column name Title, so the query fails with error "Subquery returns more than 1 row".

Please add "and table_schema='MyDb' to the query to solve it.

Hello  Gabriela,

Thank you for the bug report.
Verified this behavior on Visual Studio 2013 (C#.Net) and Connector/NET 6.9.8 version.

Thanks,
Chiranjeevi.

Connector 8.0.11, issue still present.

Is this going to be fixed? I have multiple copies of the same database for development and testing... This is an long lasting issue. Can you please make migration SQL database/schema specific!

Any chance of this being fixed?

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.21 release, and here's the proposed changelog entry from the documentation team:

The absence of a target schema in the generated WHERE statement produced
during an Entity Framework migration caused an error when the identical
table was present in multiple databases. This fix adds the table_schema
column to the generated SQL query.

Thank you for the bug report.