Pomelo.entityframeworkcore.mysql: Incorrect queries generated by Pomelo after migrating from .net core 2.2 to 3.1
Hello,
I have been using this library with my Asp.net core 2.2 on a linux server in the past and everything was working fine. Since today we decided to migrate from 2.2 to 3.1 and so we upgraded the Entityframework and also the Pomeleo package as well. I have seen the queries that were working earlier now has stopped working and as of my knowledge the queries are not being generated correcrtly.
Linq Query
var query = (
from m in _context.Mail_Accounts
where m.access_type == "Shared" || (m.access_type == "Personal" && m.created_by == ti.UserId)
select new
{
id = m.id,
email = m.email,
unread_count = (from m1 in _context.Mails where m1.is_read == false && m1.incoming == true && m1.mail_account_id == m.id select m1.id).Count(),
folder = (from m2 in _context.Mail_Folders where m2.mail_account_id == m.id && m.is_deleted != true select new { m2.name, m2.id }).FirstOrDefault()
}
).ToList()
Exception:
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 '(
SELECT `m1`.`name`, `m1`.`id`, **1 AS `c`**, `m1`.`mail_account_id`
FROM' at line 6
I don't know why this is happening or we have to fall back to the old version.
hazhayder
All 31 comments
Please share with us the full generated SQL query, the Mail, Mail_Account and Mail_Folder models (classes) and their model definitions (either FluentAPI or data annotations).
The SQL queries are being logged by EF Core (at the information level). See Logging - EF Core and Logging in .NET Core and ASP.NET Core.
lauxjpn
on 14 Apr 2020
Please share with as the full generated SQL query, the
Mail_AccountandMail_Foldermodels (classes) and their model definitions (either FluentAPI or data annotations).The SQL queries are being logged by EF Core (at the information level). See Logging - EF Core and Logging in .NET Core and ASP.NET Core.
```
public class Mail_Account : BaseEntity
{
public string type { get; set; }
public string email { get; set; }
public string password { get; set; }
public string imap { get; set; }
public int? imap_port { get; set; }
public string smtp { get; set; }
public int? smtp_port { get; set; }
public string user_id { get; set; }
public string history_id { get; set; }
public string folder_id { get; set; }
public bool is_shared { get; set; }
public string IMAP_Encryption { get; set; }
public string SMTP_Encryption { get; set; }
public bool has_access { get; set; } = true;
public bool threading { get; set; }
public string access_type { get; set; } = "Shared";
}
public class Mail : BaseEntity
{
public string from { get; set; }
public string from_name { get; set; }
public string to { get; set; }
public string to_name { get; set; }
public int? from_recipient { get; set; }
public int? to_recipient { get; set; }
public string cc { get; set; }
public string snippet { get; set; }
public string subject { get; set; }
public string body { get; set; }
public string thread_id { get; set; }
public DateTime recieved_date { get; set; }
public bool is_read { get; set; }
public string ref_thread_id { get; set; }
public string source { get; set; }
public string source_mail_id { get; set; }
public int mail_account_id { get; set; }
public string label { get; set; }
public int folder_id { get; set; }
public string mime_type { get; set; }
public string headers { get; set; }
public string sent_identifier { get; set; }
public bool incoming { get; set; } = false;
}
public class Mail_Folder : BaseEntity
{
public string name { get; set; }
public string source_id { get; set; }
public string history_id { get; set; }
public DateTime? last_sync { get; set; }
public int mail_account_id { get; set; }
}
public abstract class BaseEntity
{
public int id { get; set; }
public int tenant_id { get; set; }
public bool is_deleted { get; set; }
public string created_by { get; set; }
public DateTime created_date { get; set; }
[JsonIgnore]
public string modified_by { get; set; }
public DateTime modified_date { get; set; } = DateTime.UtcNow;
}
**SQL Generated Query:**
SELECT m0.id, m0.email, (
SELECT COUNT(*)
FROM Mails AS m
WHERE ((m.tenant_id = 1) AND NOT (m.is_deleted)) AND (((m.is_read = FALSE) AND (m.incoming = TRUE)) AND (m.mail_account_id = m0.id))), t.name, t.id, t.c
FROM Mail_Accounts AS m0
LEFT JOIN LATERAL (
SELECT m1.name, m1.id, 1 AS c, m1.mail_account_id
FROM Mail_Folders AS m1
WHERE (((m1.tenant_id = 1) AND NOT (m1.is_deleted)) AND (m0.is_deleted <> TRUE)) AND (m0.id = m1.mail_account_id)
LIMIT 1
) AS t ON TRUE
WHERE ((m0.tenant_id = 1) AND NOT (m0.is_deleted)) AND ((m0.access_type = 'Shared') OR ((m0.access_type = 'Personal') AND ((m0.created_by = "27882142-a200-4e00-837b-6bb0fb6d8cb5") OR (m0.created_by IS NULL AND "27882142-a200-4e00-837b-6bb0fb6d8cb5" IS NULL))))
```
Executing the above query in MySQL is working pretty fine.
hazhayder
on 15 Apr 2020
Please share with as the full generated SQL query, the
Mail_AccountandMail_Foldermodels (classes) and their model definitions (either FluentAPI or data annotations).The SQL queries are being logged by EF Core (at the information level). See Logging - EF Core and Logging in .NET Core and ASP.NET Core.
This is another example where the query generated is not either working in MySQL.
SELECT `t`.`id`, `t`.`salutation`, `t`.`first_name`, `t`.`last_name`, `t`.`work_email`, `t`.`work_phone`, `t`.`work_email_type`, `t`.`work_phone_type`, `l`.`id`, `t1`.`id`, `t1`.`id0`, `t1`.`name`, `t1`.`value`, `t1`.`type`, `t1`.`options`
FROM `Lead_Contacts` AS `l`
INNER JOIN (
SELECT `c`.`id`, `c`.`company_id`, `c`.`created_by`, `c`.`created_date`, `c`.`first_name`, `c`.`is_company_primary`, `c`.`is_deleted`, `c`.`last_name`, `c`.`modified_by`, `c`.`modified_date`, `c`.`resp_user`, `c`.`salutation`, `c`.`tenant_id`, `c`.`work_email`, `c`.`work_email_type`, `c`.`work_phone`, `c`.`work_phone_type`
FROM `Contacts` AS `c`
WHERE (`c`.`tenant_id` = 1) AND NOT (`c`.`is_deleted`)
) AS `t` ON `l`.`contact_id` = `t`.`id`
LEFT JOIN LATERAL (
SELECT `t0`.`id`, `c0`.`id` AS `id0`, `c0`.`name`, `t0`.`value`, `c0`.`type`, `c0`.`options`
FROM `Contact_Fields` AS `c0`
LEFT JOIN (
SELECT `c1`.`id`, `c1`.`contact_field_id`, `c1`.`contact_id`, `c1`.`created_by`, `c1`.`created_date`, `c1`.`is_deleted`, `c1`.`modified_by`, `c1`.`modified_date`, `c1`.`tenant_id`, `c1`.`value`
FROM `Contact_Field_Values` AS `c1`
WHERE (`c1`.`tenant_id` = 1) AND NOT (`c1`.`is_deleted`)
) AS `t0` ON (`c0`.`id` = `t0`.`contact_field_id`) AND (`t`.`id` = `t0`.`contact_id`)
WHERE (`c0`.`tenant_id` = 1) AND NOT (`c0`.`is_deleted`)
) AS `t1` ON TRUE
WHERE ((`l`.`tenant_id` = 1) AND NOT (`l`.`is_deleted`)) AND (`l`.`lead_id` = 6)
ORDER BY `l`.`id`, `t`.`id`, `t1`.`id0`
Classes
public class Leads_Contact : BaseEntity
{
public int lead_id { get; set; }
public int contact_id { get; set; }
public string type { get; set; }
}
public class Contact : BaseEntity
{
public string salutation { get; set; }
public string first_name { get; set; }
public string last_name { get; set; }
public string resp_user { get; set; }
public string work_phone { get; set; }
public string work_email { get; set; }
public int? company_id { get; set; }
//Represents whether this contact is company's primary contact or not
public int? is_company_primary { get; set; }
public string work_phone_type { get; set; } = "Work Phone";
public string work_email_type { get; set; } = "Work Email";
}
public class Contact_Field : BaseEntity
{
public int sort_id { get; set; }
public string name { get; set; }
public string type { get; set; }
public string options { get; set; }
}
public class Contact_Field_Value : BaseEntity
{
public int contact_id { get; set; }
public int contact_field_id { get; set; }
public string value { get; set; }
}
Executing the above query in MySQL is working pretty fine.
That should not be possible. Either the query has a syntax error, or it hasn't.
Also, you exception message contains the following part:
**1 AS `c`**
Pomelo should not insert * characters into the SQL, and I am pretty sure it doesn't, because this is definitely a syntax error. Are you altering the generated SQL in any way?
The asterisks don't appear in the generated SQL. Did you get the SQL query from the ASP.NET logging, or in a different way?
This is another example where the query generated is not either working in MySQL.
Please post the LINQ query (C#) that is used to generate this second SQL query.
lauxjpn
on 15 Apr 2020
I have two environments setup one with .net core 2.2 and one with .net core 3.1 and the respective packages are also latest and stable on .net core 3.1 environment.
In .net core 2.2 these queries are working exactly fine with the same MySQL instance.
var contacts = (from lc in _context.Lead_Contacts
where lc.lead_id == leadId
join c in _context.Contacts on lc.contact_id equals c.id
select new Lead_Contacts_DTO
{
id = c.id,
salutation = c.salutation,
first_name = c.first_name,
last_name = c.last_name,
work_email = c.work_email,
work_phone = c.work_phone,
work_email_type = c.work_email_type,
work_phone_type = c.work_phone_type,
unlink = false,
Fields = (from cf in _context.Contact_Fields
join cfv in _context.Contact_Field_Values on new { id = cf.id, contactId = c.id } equals new { id = cfv.contact_field_id, contactId = cfv.contact_id }
into cfv_v
from cf1 in cfv_v.DefaultIfEmpty()
select new Lead_Contact_Field_Value
{
Contact_Field_Id = cf1.id,
Field_Id = cf.id,
Field_Name = cf.name,
Field_Value = cf1.value,
Field_Type = cf.type,
Field_Options = cf.options,
Field_Value_Arr = new List<string>()
}).ToList()
}).ToList();
The following statement is only supported on MySQL 8+:
LEFT JOIN LATERAL (
You didn't use our issue template. What is the exact MySQL/MariaDB version you are using?
lauxjpn
on 15 Apr 2020
The following statement is only supported on MySQL 8+:
LEFT JOIN LATERAL (You didn't use our issue template. What is the exact MySQL/MariaDB version you are using?
[root@francisschool ~]# mysql --version
mysql Ver 8.0.18 for Linux on x86_64 (MySQL Community Server - GPL)
hazhayder
on 15 Apr 2020
Executing the above query in MySQL is working pretty fine.
That should not be possible. Either the query has a syntax error, or it hasn't.
Also, you exception message contains the following part:**1 AS `c`**Pomelo should not insert
*characters into the SQL, and I am pretty sure it doesn't, because this _is_ definitely a syntax error. Are you altering the generated SQL in any way?The asterisks don't appear in the generated SQL. Did you get the SQL query from the ASP.NET logging, or in a different way?
This is another example where the query generated is not either working in MySQL.
Please post the LINQ query (C#) that is used to generate this second SQL query.
Sorry, that created a misconception basically I tried to highlight it. There are no asteric(*) in the exception.
hazhayder
on 15 Apr 2020
Did you get the [generated] SQL query from the ASP.NET logging, or in a different way?
Please try to provide us with the remaining requested information, so we can get to the bottom of this.
Executing the above query in MySQL is working pretty fine.
That should not be possible. Either the query has a syntax error, or it hasn't.
Do you execute the queries on a MySQL production server only, or do you use a development server as well?
If so, what is the version of the development server in comparison to the production server and did you generate and run the query on your development or production environment?
The same question then also applies to manually executing the first query, which you said worked. Did you manually execute the working query on your development MySQL server or on the production MySQL server?
lauxjpn
on 15 Apr 2020
Did you get the [generated] SQL query from the ASP.NET logging, or in a different way?
Please try to provide us with the remaining requested information, so we can get to the bottom of this.
Executing the above query in MySQL is working pretty fine.
That should not be possible. Either the query has a syntax error, or it hasn't.
Do you execute the queries on a MySQL production server only, or do you use a development server as well?
If so, what is the version of the development server in comparison to the production server and did you generate and run the query on your development or production environment?
The same question then also applies to manually executing the first query, which you said worked. Did you manually execute the working query on your development MySQL server or on the production MySQL server?
We use the same MySQL instance on both the Development and Production environment. So either the EF Core is executing the query or we doing that manually it is executed on Production Server only which has MySQL 8.0 installed.
hazhayder
on 15 Apr 2020
The generated SQL looks perfectly fine for me, but only on MySQL 8+. That's why I suspected, that you are not running the queries on MySQL 8. It needs to be 8.0.14-mysql or higher, to be precise, because LATERAL is only supported then. But this is the case for your environment, so this cannot be the issue here then.
So if you are running the queries on 8.0.14-mysql or higher, and running the first query from Pomelo does not work, while running the exact same query manually on the same server does work, then this would be very odd (to not say impossible).
What are the connection settings (connection string without host, username and password) you are using from your ASP.NET app?
Did you get the [generated] SQL query from the ASP.NET logging, or in a different way?
We really need an answer to this question to absolutely make sure, that the query you manually ran and the query that your app runs are definitely the same.
If for example you did not get the generated query from the ASP.NET logging mechanism, but from the MySQL query log, then they could be different.
lauxjpn
on 15 Apr 2020
The generated SQL looks perfectly fine for me, but only on MySQL 8+. That's why I suspected, that you are not running the queries on MySQL 8. It needs to be
8.0.14-mysqlor higher, to be precise, becauseLATERALis only supported then. But this is the case for your environment, so this cannot be the issue here then.So if you _are_ running the queries on
8.0.14-mysqlor higher, and running the first query from Pomelo does _not_ work, while running the _exact same_ query manually on the same server _does_ work, then this would be very odd (to not say impossible).What are the connection settings (connection string without host, username and password) you are using from your ASP.NET app?
Did you get the [generated] SQL query from the ASP.NET logging, or in a different way?
We really need an answer to this question to absolutely make sure, that the query you manually ran and the query that your app runs are definitely the same.
If for example you did not get the generated query from the ASP.NET logging mechanism, but from the MySQL query log, then they could be different.
Let me create a short video for you explaining everything.
hazhayder
on 15 Apr 2020
hazhayder
on 15 Apr 2020
https://drive.google.com/file/d/1QZY86mQmPYn24HPmFy8S5t5Ak8v9IqDU/view
Let me know when you watch it so I can delete this after.
hazhayder
on 15 Apr 2020
This is the connection string
$"Server={host}; Port=3306;" +
$"database={databaseName};" +
$"uid={databaseUser};" +
$"pwd={databasePass};" +
$"pooling=true;" +
$"charset=utf8mb4;";
Let me know when you watch it so I can delete this after.
Thanks, I have seen it. You can delete the video.
Based on the code you provided, I created the following console program, that runs without issues on my MySQL 8.0.18 instance:
```c#
using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using Pomelo.EntityFrameworkCore.MySql.Extensions;
namespace IssueConsoleTemplate
{
public class Mail_Account : BaseEntity
{
public string type { get; set; }
public string email { get; set; }
public string password { get; set; }
public string imap { get; set; }
public int? imap_port { get; set; }
public string smtp { get; set; }
public int? smtp_port { get; set; }
public string user_id { get; set; }
public string history_id { get; set; }
public string folder_id { get; set; }
public bool is_shared { get; set; }
public string IMAP_Encryption { get; set; }
public string SMTP_Encryption { get; set; }
public bool has_access { get; set; } = true;
public bool threading { get; set; }
public string access_type { get; set; } = "Shared";
}
public class Mail : BaseEntity
{
public string from { get; set; }
public string from_name { get; set; }
public string to { get; set; }
public string to_name { get; set; }
public int? from_recipient { get; set; }
public int? to_recipient { get; set; }
public string cc { get; set; }
public string snippet { get; set; }
public string subject { get; set; }
public string body { get; set; }
public string thread_id { get; set; }
public DateTime recieved_date { get; set; }
public bool is_read { get; set; }
public string ref_thread_id { get; set; }
public string source { get; set; }
public string source_mail_id { get; set; }
public int mail_account_id { get; set; }
public string label { get; set; }
public int folder_id { get; set; }
public string mime_type { get; set; }
public string headers { get; set; }
public string sent_identifier { get; set; }
public bool incoming { get; set; } = false;
}
public class Mail_Folder : BaseEntity
{
public string name { get; set; }
public string source_id { get; set; }
public string history_id { get; set; }
public DateTime? last_sync { get; set; }
public int mail_account_id { get; set; }
}
public abstract class BaseEntity
{
public int id { get; set; }
public int tenant_id { get; set; }
public bool is_deleted { get; set; }
public string created_by { get; set; }
public DateTime created_date { get; set; }
public string modified_by { get; set; }
public DateTime modified_date { get; set; } = DateTime.UtcNow;
}
public class Context : DbContext
{
public DbSet<Mail> Mails { get; set; }
public DbSet<Mail_Account> Mail_Accounts { get; set; }
public DbSet<Mail_Folder> Mail_Folders { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder
.UseMySql(
"server=127.0.0.1;port=3306;user=root;password=;database=Issue1067",
b => b.ServerVersion("8.0.18-mysql"))
.UseLoggerFactory(
LoggerFactory.Create(
b => b
.AddConsole()
.AddFilter(level => level >= LogLevel.Information)))
.EnableSensitiveDataLogging()
.EnableDetailedErrors();
}
}
internal class Program
{
private static void Main()
{
using (var context = new Context())
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
var tiUserId = Guid.NewGuid().ToString();
var query = (
from m in context.Mail_Accounts
where m.access_type == "Shared" || (m.access_type == "Personal" && m.created_by == tiUserId)
select new
{
id = m.id,
email = m.email,
unread_count = (from m1 in context.Mails where m1.is_read == false && m1.incoming == true && m1.mail_account_id == m.id select m1.id).Count(),
folder = (from m2 in context.Mail_Folders where m2.mail_account_id == m.id && m.is_deleted != true select new {m2.name, m2.id}).FirstOrDefault()
}
).ToList();
}
}
}
}
It runs the following queries:
```sql
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (5ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE DATABASE `Issue1067`;
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (34ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE `Mail_Accounts` (
`id` int NOT NULL AUTO_INCREMENT,
`tenant_id` int NOT NULL,
`is_deleted` tinyint(1) NOT NULL,
`created_by` longtext CHARACTER SET utf8mb4 NULL,
`created_date` datetime(6) NOT NULL,
`modified_by` longtext CHARACTER SET utf8mb4 NULL,
`modified_date` datetime(6) NOT NULL,
`type` longtext CHARACTER SET utf8mb4 NULL,
`email` longtext CHARACTER SET utf8mb4 NULL,
`password` longtext CHARACTER SET utf8mb4 NULL,
`imap` longtext CHARACTER SET utf8mb4 NULL,
`imap_port` int NULL,
`smtp` longtext CHARACTER SET utf8mb4 NULL,
`smtp_port` int NULL,
`user_id` longtext CHARACTER SET utf8mb4 NULL,
`history_id` longtext CHARACTER SET utf8mb4 NULL,
`folder_id` longtext CHARACTER SET utf8mb4 NULL,
`is_shared` tinyint(1) NOT NULL,
`IMAP_Encryption` longtext CHARACTER SET utf8mb4 NULL,
`SMTP_Encryption` longtext CHARACTER SET utf8mb4 NULL,
`has_access` tinyint(1) NOT NULL,
`threading` tinyint(1) NOT NULL,
`access_type` longtext CHARACTER SET utf8mb4 NULL,
CONSTRAINT `PK_Mail_Accounts` PRIMARY KEY (`id`)
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (25ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE `Mail_Folders` (
`id` int NOT NULL AUTO_INCREMENT,
`tenant_id` int NOT NULL,
`is_deleted` tinyint(1) NOT NULL,
`created_by` longtext CHARACTER SET utf8mb4 NULL,
`created_date` datetime(6) NOT NULL,
`modified_by` longtext CHARACTER SET utf8mb4 NULL,
`modified_date` datetime(6) NOT NULL,
`name` longtext CHARACTER SET utf8mb4 NULL,
`source_id` longtext CHARACTER SET utf8mb4 NULL,
`history_id` longtext CHARACTER SET utf8mb4 NULL,
`last_sync` datetime(6) NULL,
`mail_account_id` int NOT NULL,
CONSTRAINT `PK_Mail_Folders` PRIMARY KEY (`id`)
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (35ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE `Mails` (
`id` int NOT NULL AUTO_INCREMENT,
`tenant_id` int NOT NULL,
`is_deleted` tinyint(1) NOT NULL,
`created_by` longtext CHARACTER SET utf8mb4 NULL,
`created_date` datetime(6) NOT NULL,
`modified_by` longtext CHARACTER SET utf8mb4 NULL,
`modified_date` datetime(6) NOT NULL,
`from` longtext CHARACTER SET utf8mb4 NULL,
`from_name` longtext CHARACTER SET utf8mb4 NULL,
`to` longtext CHARACTER SET utf8mb4 NULL,
`to_name` longtext CHARACTER SET utf8mb4 NULL,
`from_recipient` int NULL,
`to_recipient` int NULL,
`cc` longtext CHARACTER SET utf8mb4 NULL,
`snippet` longtext CHARACTER SET utf8mb4 NULL,
`subject` longtext CHARACTER SET utf8mb4 NULL,
`body` longtext CHARACTER SET utf8mb4 NULL,
`thread_id` longtext CHARACTER SET utf8mb4 NULL,
`recieved_date` datetime(6) NOT NULL,
`is_read` tinyint(1) NOT NULL,
`ref_thread_id` longtext CHARACTER SET utf8mb4 NULL,
`source` longtext CHARACTER SET utf8mb4 NULL,
`source_mail_id` longtext CHARACTER SET utf8mb4 NULL,
`mail_account_id` int NOT NULL,
`label` longtext CHARACTER SET utf8mb4 NULL,
`folder_id` int NOT NULL,
`mime_type` longtext CHARACTER SET utf8mb4 NULL,
`headers` longtext CHARACTER SET utf8mb4 NULL,
`sent_identifier` longtext CHARACTER SET utf8mb4 NULL,
`incoming` tinyint(1) NOT NULL,
CONSTRAINT `PK_Mails` PRIMARY KEY (`id`)
);
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (17ms) [Parameters=[@__tiUserId_0='2dcfcaa0-008e-4428-8997-bf82bd9c89aa' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT `m0`.`id`, `m0`.`email`, (
SELECT COUNT(*)
FROM `Mails` AS `m`
WHERE ((`m`.`is_read` = FALSE) AND (`m`.`incoming` = TRUE)) AND (`m`.`mail_account_id` = `m0`.`id`)), `t`.`name`, `t`.`id`, `t`.`c`
FROM `Mail_Accounts` AS `m0`
LEFT JOIN LATERAL (
SELECT `m1`.`name`, `m1`.`id`, 1 AS `c`, `m1`.`mail_account_id`
FROM `Mail_Folders` AS `m1`
WHERE (`m0`.`is_deleted` <> TRUE) AND (`m0`.`id` = `m1`.`mail_account_id`)
LIMIT 1
) AS `t` ON TRUE
WHERE (`m0`.`access_type` = 'Shared') OR ((`m0`.`access_type` = 'Personal') AND (`m0`.`created_by` = @__tiUserId_0))
As you can see, the last generated query differs to some degree from the one you posted.
What are the exact package versions of Pomelo.EntityFramework.MySql and Microsoft.EntityFrameworkCore you are using?
Also, if you are using NLog, you can set the log level to Info and should get all executed queries output to the log. This will be better than using ToSql(), because the actual query contains a parameter, and therefore differs from the one generated by ToSql().
lauxjpn
on 15 Apr 2020
Let me know when you watch it so I can delete this after.
Thanks, I have seen it. You can delete the video.
Based on the code you provided, I created the following console program, that runs without issues on my MySQL 8.0.18 instance:
Code removedIt runs the following queries:
SQL removedAs you can see, the last generated query differs to some degree from the one you posted.
What are the exact package versions of
Pomelo.EntityFrameworkCore.MySqlandMicrosoft.EntityFrameworkCoreyou are using?Also, if you are using NLog, you can set the log level to
Infoand should get all executed queries output to the log. This will be better than usingToSql(), because the actual query contains a parameter, and therefore differs from the one generated byToSql().
We are using EFCore 3.1.3.
hazhayder
on 15 Apr 2020
What are the exact package versions of Pomelo.EntityFrameworkCore.MySql [...] you are using?
The SQL output of my previous example code used EF Core 3.1.3 and Pomelo 3.1.1.
We will also really need the query as logged by EF Core (on your case through NLog). The ToSql() method is not sufficient.
lauxjpn
on 15 Apr 2020
Let me paste the pieces of code into a separate repo then we can give it a try. What do you think?
hazhayder
on 15 Apr 2020
We are also using v3.1.1 of Pomelo.EntityFrameworkCore.MySql
hazhayder
on 15 Apr 2020
Its not a bad idea for you to run the code on your end.
But please first post the original query of your project as logged by EF Core (in your case through NLog). The ToSql() method is not sufficient.
This will be necessary, because your original generated query contains e.g. (`m`.`tenant_id` = 1), while your LINQ code does not check tenant_id at all (and definitely not against a constant 1).
(Because my example code uses the same package versions as you are, and the same LINQ query as you are, both projects need to generate the same SQL query. Since they don't, either I copied your code in a wrong way, or the information you provided is incomplete (or incorrect).)
(Because you did not post any model definition for you model classes, I am assuming there is none.)
lauxjpn
on 15 Apr 2020
This _will_ be necessary, because your original generated query contains e.g.
(`m`.`tenant_id` = 1), while your LINQ code does not checktenant_idat all (and definitely not against a constant1).
Yeah, probably using a global query filter in which case they should also share their DbContext for completeness.
Also agree that they should show logged SQL from output window and also Show Parameter Values in Logging as well.
They should also be aware of the many Breaking changes included in EF Core 3.0 if they haven't already looked at those.
The error appears to show LEFT JOIN LATERAL as the issue, so they should double check server is a MySQL 8.0.18 instance.
mguinness
on 15 Apr 2020
Yeah, probably using a global query filter in which case they should also share their
DbContextfor completeness.
Good point!
They should also be aware of the many Breaking changes included in EF Core 3.0 if they haven't already looked at those.
Yes, being aware about the fact, that the query pipeline was fundamentally rewritten between EF Core 2.2.6 and 3.0.0 is good. But since the query does work generally, as I have proven with the example code above, this is not the issue here.
The error appears to show
LEFT JOIN LATERALas the issue, so they should double check server is a MySQL 8.0.18 instance.
That was my first though as well. But he is very sure, that he is running the query against a MySQL 8.0.18 server.
@hazhayder Here is what you should do next:
- To make us shut up about the MySQL version you are using, add the following lines to your code base on your production server app just before the failing LINQ query and then run the app. It needs to run in your production environment (as part of the real app that is failing).
It will log your MySQL version number that is actually being used in your log. The result should be prove enough for us, that your MySQL version is high enough.
The_loggerfield in the following code contains a constructor injectedILogger<MainController>instance:
```c#
using (var command = _context.Database.GetDbConnection().CreateCommand())
{
_context.Database.OpenConnection();
command.CommandText = "select @@version";
var version = (string)command.ExecuteScalar();
_logger.LogInformation($"Actual MySQL Version: {version}");
}
```
If the MySQL version is 8.0.14+, then post the SQL that got logged by EF Core for your failing LINQ query. As @mguinness suggested, you want to enable parameter logging as well. My example code does this too.
Post the code you use to create your
DbContext, meaning theAddDbContext()call as well as theDbContextconstructors and theOnConfiguring()method. This will address @mguinness point, that global query filters might be at work here.
lauxjpn
on 16 Apr 2020
I will be sharing the information required today had a busy schedule.
hazhayder
on 17 Apr 2020
Here is the project in which i am facing the issue.
To simulate the error please follow these instructionsEndpoint => http://localhost:59604/api/account/FailQuery Token => Bearer EwCAA8l6BAAUO9chh8cJscQLmU+LSWpbnr0vmwwAATcap+i5UsCZkwz/cu75Q9NT2ixummh7Y1n7c+wcRL5D+2Dle2NE2GjFOPny+VNxMTr39dsk2UcgdFvDkM71f8SeUzGp8w5x29kYYmaW4QaQCl9mTEprtuww7hgeSFmAJBeEKA4EaT+Nkrst668mQv+wdeFOR6mPN9f1UOvlWNUH/RpLDKhPfB0xl8Y6qS8ZoxwJBDrbs+UbT02gPFJ7Q1uS4ET+Y5aID1ObWorrGO+ny31p9Mx5xQqpQS50QgUdShSgR+wH5k8lIMf8ekb3OSpi1Jbz82Oj3Lu//JHxZNE5OXtBx+IwNy+EfBjMngMixe7QOqHxyTEv02YwJKDD6BwDZgAACLkamlHOm7KlUAK1ZAycsklZT7z3d5mV7AtHLvenUeZnOfWRYdqkuHzvxIbgzR47JXMlcNlw05bbzfIrrxCKuR4KskKDfrYXPYvcY5eWkU2kAExRB8ImxKFaIuxHPCwCx/CK8qT4k9vAFUOAuUG/4adCB5n8Z1J2SW37MK1N+hUFJGzubGEfR/+cjkVJDdZu7B4alj0t44RPG9Bp4t8z7gMKV9t1E5O03672rSBa1qUA3osaPEW3GMzt1GWbSCs81mO7LaaHBj3vFoCkRS4nDTq4r6k1a0a7jF/O3qsVpaK3vFudD+izncvKo44vXvNXl3Jvt+Z6yFHg4u+nLmPSDDuwYHi+6vYU9VIPLJxwGRepkMsorj06LdP4EDHb1MKhbW2ETpUqOpUfUFbuKl4Qmc5RQ6JpUVklaXCMAWmmq+o+gAS6Y83cTH0SDItDks/wVfP/qY0w9pelPFJ5PS3+qELr7ooBF9Gmz63YrDwPebDLr2dR9Mjdhj0p7vSKUCP/0kerN/EiMDDwG0pXpbk75h7Ajuq/xbRmi0l4ahxYpMygTepoTOSJ0q74HpZNPVv3S5bv/NE/G9v676YyI9IIXBSGxXmQl3GWCpsDikEdH9EfGEimiReQSM87PLkclTg4t9JwVtvtVo+yzNB1xkwZLMaUerUCnBwgvBssjfGUSz6vhe1QBMfYbXOFfEN0H5jVtLBCWhfN/RBpOD+G860RTYY4YwCTdlcV+JmHj/MEITPfA7VcBGMv78knZrXDupWPB0XWXXStYQZBzMivn5u3kL03wjTF/fFwm0T+iQI=The database file is attached in this zip.
@hazhayder Unfortunately it seems, that you left sensitive information hard coded in your .\Entities\ApplicationDbContext.cs file!
If this was not intentional (and I believe it wasn't), and the file does contain your production database server credentials, you need to take immediate action to change the database credentials, because your zip file has been publicly available for over 3 days now. (I deleted your original post anyway, but that does not do much.)
After that, make sure, that the same password has not been used anywhere else, and change those credentials as well, if it has.
The next step would be to make sure, that nobody compromised the server or database server in any way. If you can't ensure that, you will need to setup a new server.
In case your database contains personal (or sensitive) information, you need to check, that those did not got accessed. It is likely that the database contains personal information, because the DB.sql file of the zip file you provided, contains columns like first_name and last_name etc.
In case personal information got accessed by anybody unauthorized, then, depending on your privacy policy or the country of the people who's information was accessed (e.g. whether they are EU citizens), you might need to report this incident to the appropriate data protection authorities, or contact the affected people.
(I would have contacted you via email first, but you did not provide a public email address on your profile.)
lauxjpn
on 30 Apr 2020
In retrospect, we should handle cases like this differently in the future:
- Delete the original post.
- Contact the GutHub support to remove the attachment.
2.a If the user did not provide an email address, let the GitHub support team inform the user about the issue and that he can contact us via an email address we provide to the support team.
2.b If the user did provide an email address, contact him directly via email. - Handle all further communication over a non-public channel, until mitigation steps have been taken.
lauxjpn
on 1 May 2020
@hazhayder I can confirm, that the MySQL version seems to be in order here, because I can reproduce this issue on my end with MySQL 8.0.18.
To make context use the ILoggerFactory instance, you need to modify your ApplicationDbContext constructor, to include a DbContextOptions<ApplicationDbContext> parameters:
```c#
public ApplicationDbContext(
DbContextOptions
ClaimsPrincipal c,
ILoggerFactory factory)
: base(options)
{
// ...
}
Then, add some additional options, to get more detailed logging output:
```c#
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseMySql(
GetConnectionString(),
mySqlOptionsAction => mySqlOptionsAction
.ServerVersion(
new Version(8, 0, 18),
Pomelo.EntityFrameworkCore.MySql.Infrastructure.ServerType.MySql))
.EnableDetailedErrors()
.EnableSensitiveDataLogging();
}
With these settings, the following SQL is being logged:
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (226ms) [Parameters=[@__ef_filter___tenant_id_1='1', @__ef_filter___tenant_id_3='1', @__ef_filter___tenant_id_2='1', @__ef_filter___tenant_id_0='1'], CommandType='Text', CommandTimeout='30']
SELECT `t`.`id`, `t`.`salutation`, `t`.`first_name`, `t`.`last_name`, `t`.`work_email`, `t`.`work_phone`, `t`.`work_email_type`, `t`.`work_phone_type`, `l`.`id`, `t1`.`id`, `t1`.`id0`, `t1`.`name`, `t1`.`value`, `t1`.`type`, `t1`.`options`
FROM `Lead_Contacts` AS `l`
INNER JOIN (
SELECT `c`.`id`, `c`.`company_id`, `c`.`created_by`, `c`.`created_date`, `c`.`first_name`, `c`.`is_company_primary`, `c`.`is_deleted`, `c`.`last_name`, `c`.`modified_by`, `c`.`modified_date`, `c`.`resp_user`, `c`.`salutation`, `c`.`tenant_id`, `c`.`work_email`, `c`.`work_email_type`, `c`.`work_phone`, `c`.`work_phone_type`
FROM `Contacts` AS `c`
WHERE (`c`.`tenant_id` = @__ef_filter___tenant_id_1) AND NOT (`c`.`is_deleted`)
) AS `t` ON `l`.`contact_id` = `t`.`id`
LEFT JOIN LATERAL (
SELECT `t0`.`id`, `c0`.`id` AS `id0`, `c0`.`name`, `t0`.`value`, `c0`.`type`, `c0`.`options`
FROM `Contact_Fields` AS `c0`
LEFT JOIN (
SELECT `c1`.`id`, `c1`.`contact_field_id`, `c1`.`contact_id`, `c1`.`created_by`, `c1`.`created_date`, `c1`.`is_deleted`, `c1`.`modified_by`, `c1`.`modified_date`, `c1`.`tenant_id`, `c1`.`value`
FROM `Contact_Field_Values` AS `c1`
WHERE (`c1`.`tenant_id` = @__ef_filter___tenant_id_3) AND NOT (`c1`.`is_deleted`)
) AS `t0` ON (`c0`.`id` = `t0`.`contact_field_id`) AND (`t`.`id` = `t0`.`contact_id`)
WHERE (`c0`.`tenant_id` = @__ef_filter___tenant_id_2) AND NOT (`c0`.`is_deleted`)
) AS `t1` ON TRUE
WHERE ((`l`.`tenant_id` = @__ef_filter___tenant_id_0) AND NOT (`l`.`is_deleted`)) AND (`l`.`lead_id` = 1)
ORDER BY `l`.`id`, `t`.`id`, `t1`.`id0`
@mguinness You were right, there are global query filters at work here:
```c#
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
foreach (var type in GetEntityTypes())
{
var method = SetGlobalQueryMethod.MakeGenericMethod(type);
method.Invoke(this, new object[] {modelBuilder});
}
this.RegisterSP(modelBuilder);
base.OnModelCreating(modelBuilder);
}
static readonly MethodInfo SetGlobalQueryMethod = typeof(ApplicationDbContext).GetMethods(BindingFlags.Public | BindingFlags.Instance)
.Single(t => t.IsGenericMethod && t.Name == "SetGlobalQuery");
public void SetGlobalQuery
where T : BaseEntity
{
builder.Entity
.HasKey(e => e.id);
builder.Entity<T>()
.HasQueryFilter(e => e.tenant_id == _tenant_id && !e.is_deleted);
}
This is also evident by the `@__ef_filter___tenant_id_1` parameters shown in the logged query.
The query fails with the following exception:
MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 't.id' in 'on clause'
---> MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 't.id' in 'on clause'
at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projectsmysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 49
With the query filter removed, the query is simpler, but still fails:
```sql
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (238ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT `c`.`id`, `c`.`salutation`, `c`.`first_name`, `c`.`last_name`, `c`.`work_email`, `c`.`work_phone`, `c`.`work_email_type`, `c`.`work_phone_type`, `l`.`id`, `t`.`id`, `t`.`id0`, `t`.`name`, `t`.`value`, `t`.`type`, `t`.`options`
FROM `Lead_Contacts` AS `l`
INNER JOIN `Contacts` AS `c` ON `l`.`contact_id` = `c`.`id`
LEFT JOIN LATERAL (
SELECT `c1`.`id`, `c0`.`id` AS `id0`, `c0`.`name`, `c1`.`value`, `c0`.`type`, `c0`.`options`
FROM `Contact_Fields` AS `c0`
LEFT JOIN `Contact_Field_Values` AS `c1` ON (`c0`.`id` = `c1`.`contact_field_id`) AND (`c`.`id` = `c1`.`contact_id`)
) AS `t` ON TRUE
WHERE `l`.`lead_id` = 1
ORDER BY `l`.`id`, `c`.`id`, `t`.`id0`
The exception is basically the same:
MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'c.id' in 'on clause'
---> MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'c.id' in 'on clause'
at MySqlConnector.Core.ResultSet.ReadResultSetHeaderAsync(IOBehavior ioBehavior) in C:\projects\mysqlconnector\src\MySqlConnector\Core\ResultSet.cs:line 49
Looks like MySQL is unable to access the `c`.`id` field, though I expect it should be able to, due to the use of LATERAL.
I reported this issue a while ago on the MySQL Bugtracker as Outer reference in join condition isn't allowed. Looks like this has been fixed for MySql 8.0.20.
This did not really work in Pomelo 2.2 either. EF Core 2.2 just gave up to translate the query, because it was to complicated and instead switch to client evaluation mode, meaning it just queried all the rows from the database (using multiple simple SELECT statements) and then executed the actual query in memory using LINQ. So you got a correct result, but a very inefficient one. You can verify this easily by logging the generated queries for EF Core 2.2.
I did check the original query in MySQL 8.0.20 and can confirm that it runs correct there.
We could implement a workaround for MySQL versions 8.0.14 to 8.0.19, where the ON clause is always true, and the actual condition is moved to the WHERE clause. But I am not convinced, that we need to implement this workaround in Pomelo, since anybody affected could just fix this issue by updating MySQL:
SELECT `c`.`id`, `c`.`salutation`, `c`.`first_name`, `c`.`last_name`, `c`.`work_email`, `c`.`work_phone`, `c`.`work_email_type`, `c`.`work_phone_type`, `l`.`id`, `t`.`id`, `t`.`id0`, `t`.`name`, `t`.`value`, `t`.`type`, `t`.`options`
FROM `Lead_Contacts` AS `l`
INNER JOIN `Contacts` AS `c` ON `l`.`contact_id` = `c`.`id`
LEFT JOIN LATERAL (
SELECT `c1`.`id`, `c0`.`id` AS `id0`, `c0`.`name`, `c1`.`value`, `c0`.`type`, `c0`.`options`
FROM `Contact_Fields` AS `c0`
LEFT JOIN `Contact_Field_Values` AS `c1` ON TRUE
WHERE `c1`.`id` IS NULL OR (`c0`.`id` = `c1`.`contact_field_id`) AND (`c`.`id` = `c1`.`contact_id`)
) AS `t` ON TRUE
WHERE `l`.`lead_id` = 1
ORDER BY `l`.`id`, `c`.`id`, `t`.`id0`
If you cannot update to MySQL 8.0.20, you can change your query as follows, by manually applying the workaround I showed above:
```c#
var contacts = (from lc in _context.Lead_Contacts
join c in _context.Contacts on lc.contact_id equals c.id
where lc.lead_id == 1
select new Lead_Contacts_DTO
{
id = c.id,
salutation = c.salutation,
first_name = c.first_name,
last_name = c.last_name,
work_email = c.work_email,
work_phone = c.work_phone,
work_email_type = c.work_email_type,
work_phone_type = c.work_phone_type,
unlink = false,
Fields = (from cf in _context.Contact_Fields
join cfv in _context.Contact_Field_Values on 1 equals 1 // <-- changed
into cfv_v
from cf1 in cfv_v.DefaultIfEmpty()
where cf1 == null || // <-- added
cf.id == cf1.contact_field_id && c.id == cf1.contact_id // <-- added
select new Lead_Contact_Field_Value
{
Contact_Field_Id = cf1.id,
Field_Id = cf.id,
Field_Name = cf.name,
Field_Value = cf1.value,
Field_Type = cf.type,
Field_Options = cf.options,
Field_Value_Arr = new List
}).ToList()
}).ToList();
It generates the following SQL, that should run on MySQL `8.0.18`:
```sql
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
Executed DbCommand (24ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT `c`.`id`, `c`.`salutation`, `c`.`first_name`, `c`.`last_name`, `c`.`work_email`, `c`.`work_phone`, `c`.`work_email_type`, `c`.`work_phone_type`, `l`.`id`, `t`.`id`, `t`.`id0`, `t`.`name`, `t`.`value`, `t`.`type`, `t`.`options`
FROM `Lead_Contacts` AS `l`
INNER JOIN `Contacts` AS `c` ON `l`.`contact_id` = `c`.`id`
LEFT JOIN LATERAL (
SELECT `c1`.`id`, `c0`.`id` AS `id0`, `c0`.`name`, `c1`.`value`, `c0`.`type`, `c0`.`options`
FROM `Contact_Fields` AS `c0`
LEFT JOIN `Contact_Field_Values` AS `c1` ON 1 = 1
WHERE `c1`.`id` IS NULL OR ((`c0`.`id` = `c1`.`contact_field_id`) AND (`c`.`id` = `c1`.`contact_id`))
) AS `t` ON TRUE
WHERE `l`.`lead_id` = 1
ORDER BY `l`.`id`, `c`.`id`, `t`.`id0`
In retrospect, we should handle cases like this differently in the future:
- Delete the original post.
- Contact the GutHub support to remove the attachment.
2.a If the user did not provide an email address, let the GitHub support team inform the user about the issue and that he can contact us via an email address we provide to the support team.
2.b If the user did provide an email address, contact him directly via email.- Handle all further communication over a non-public channel, until mitigation steps have been taken.
Hello,
Thanks you very much for considering the consequences of this. But the credential that are there are just testing environment of our server and is with dummy data. Once the issue is resolved we can delete the attachment by contacting GitHub.
hazhayder
on 1 May 2020
We could implement a workaround for MySQL versions
8.0.14to8.0.19
Would it be possible to instead just log a warning?
mguinness
on 1 May 2020
Would it be possible to instead just log a warning?
@mguinness Yes, this would be possible as well.
But the credential that are there are just testing environment of our server and is with dummy data.
Glad to hear! You should change those test credentials anyhow and make sure, that the server did not get compromised.
Once the issue is resolved [...]
Can you confirm, that the issue is resolved now (either by upgrading to MySQL 8.0.20, or by manually applying the described query workaround)?
lauxjpn
on 1 May 2020
@hazhayder Since we haven't heard back from you, we will assume that the issue has been resolved for you. Feel free to provide us with updated information, if this is not the case.
lauxjpn
on 16 Aug 2020
Related issues
lauxjpn
路
3Comments
matthewjcooper
路
4Comments
neistow
路
4Comments
SharmaHarsh7
路
4Comments
hainguyenthanh
路
3Comments