Question 20872 in Mysql Question posted in
The official MySQL documentation can be found here.

MySql.EntityFrameworkCore generating invalid SQL syntax on SaveChanges

I am trying to use .NET 7 and EntityFramework with MySQL.

I am using MySql.EntityFrameworkCore v7.0.0 installed via NUGET.

I am able to scaffold my context and entities ("reverse engineer / db first"). Also I can successfully use the generated DbContext and query the entities.

When I attempt to do an update to an entity I get a MySqlException on calling SaveChanges().

MySqlException: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near ‘RETURNING row_updated_time;

I did some Google searches and I can see that the keyword RETURNING is not valid syntax in MySql (but it is valid syntax in Oracle and PostGres — this is interesting, and odd).

The complete SQL that is generated for this UPDATE (amounting to two SQL statements in one call):

UPDATE `state_agency` SET `abbreviation` = @p0
WHERE `code` = @p1
RETURNING `row_updated_time`;
SELECT `row_updated_time`
FROM `state_agency`
WHERE ROW_COUNT() = 1 AND `code` = @p1;

Is this problem familiar to anyone?

Can you suggest a possible reason why I am seeing Oracle/Postgres syntax get generated by the MySql provider?

My gut tells me that there is some sort of configuration problem and the Oracle syntax is coming out of a different provider, rather than the MySql provider.

Tags:

2

Answers


  1. 0

    I just ran into this exact problem and after some additional research found out the keyword RETURNING is valid mySQL syntax added in version 8.0.21

    https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-R

    This got me headed down a versioning mismatch direction. Eventually I landed here:

    https://www.nuget.org/packages/Pomelo.EntityFrameworkCore.MySql

    I figured out in my case the server’s version of mySQL was 5.7.29 and I had to specify that.

    var connectionString = "server=localhost;user=root;password=1234;database=ef";
var serverVersion = new MySqlServerVersion(new Version(5, 7, 29));

// Replace 'YourDbContext' with the name of your own DbContext derived class.
services.AddDbContext<YourDbContext>(
        dbContextOptions => dbContextOptions
            .UseMySql(connectionString, serverVersion)                
    );
    Login or Signup to reply.
  2. 0

    I have hit this same issue. We are upgrading a project from .Net 3.1 to .Net 7 and upgrading all of the packages along with it, and after the upgrade we started hitting this issue. We are using Oracle’s MySql.EntityFrameworkCore 7.0 as our provider.

    From my testing, the issue is specifically tied to updating entities that have "update time" fields. I was able to get the project to run and to create and update database entries by removing the ".ValueGeneratedOnAddOrUpdate()" property from all of the update time fields in my context. The other fields come in fine, but unsurprisingly the update time fields stay static.

    This is acceptable to us as a temporary workaround and we’re still investigating a better fix. Unfortunately we are unable to switch to Pomelo.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search