I get MySqlException using mysql as backend db

[hamdiRuwiss]

I created a query using orchard core in one controller :

      var query = _session.Query<ContentItem, TextFieldIndex>();
        query = query.With<TextFieldIndex>(x => x.ContentType == contentType);
        query = query.With<TextFieldIndex>(x => x.Published);
        query= query.With<TextFieldIndex>(x=>x.Text=="Sale");
        var maxPagedCount = siteSettings.MaxPagedCount;
        if (maxPagedCount > 0 && pager.PageSize > maxPagedCount)
            pager.PageSize = maxPagedCount;
        var routeData = new RouteData();
        var pagerShape = (await New.Pager(pager)).TotalItemCount(maxPagedCount > 0 ? maxPagedCount : await 
         query.CountAsync()).RouteData(routeData);
        var pageOfContentItems = await query.Skip(pager.GetStartIndex()).Take(pager.PageSize).ListAsync();

I get this exception : MySqlException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'LIMIT 10 OFFSET 0' at line 1

I use mysql as backend thank you.

[hishamco]

@hamdiRuwiss which version of MySQL you are working on, so I may try this?

[hamdiRuwiss]

Server version: 10.3.15-MariaDB OC : 1.0.0-rc2-13450

[hamdiRuwiss]

[hishamco]

Unfortunately I don't have MariaDB installed, I have a MySQL 5.7.14, may be the version is matter, one more thing you can do, can you write a unit test against your DB to make it failed. I'm not sure if it's a bug or a limited feature in some versions

[hamdiRuwiss]

The same query works when using SQLLite

[hishamco]

It's database provider matter, that's why I told you to try to write a unit test to ensure if there's a bug in YesSQL or not

[sebastienros]

@hamdiRuwiss there are two ways you could help us here. Simplify your code to the bare minimum to reproduce this issue, so we could translate it in a unit test in yessq, or intercept the SQL that is executed so we can see what went wrong.

[sebastienros]

I don't see any OrderBy statement in your code, that might be the issue. Not sure MySQL support pages queries without an order by statement.

[hishamco]

I don't see any OrderBy statement in your code, that might be the issue. Not sure MySQL support pages queries without an order by statement.

Seb, I can verify if this is supported

[sebastienros]

Unrelated, but you might want to add a predicate on ContentField too on top of Text=, it might make the query faster, and also more accurate in case you have another field with the same value.

[hamdiRuwiss]

Is there a way to view or log sql statements generated by YesSql

[hishamco]

Is there a way to view or log sql statements generated by YesSql

Ya, there's a logger in YesSQL AFAIK

[hamdiRuwiss]

Please can you point me where to set it up or where to find docs on this

[hishamco]

It's a part of IConfiguration

[sebastienros]

Created a unit test that should match your query, and it worked on all databases we test. Including mysql. https://github.com/sebastienros/yessql/pull/249/files#diff-50c8dfc88150c980068843b43d83c701R2137-R2142

[hamdiRuwiss]

Sorry my bad , there is two call to query I didn't notice again i am sorry var pageOfContentItems = await query.Skip(pager.GetStartIndex()).Take(pager.PageSize).ListAsync(); IEnumerable model = await query.ListAsync(); trying either will work with no exceptions

[hamdiRuwiss]

It worked with SQLLite that is why i didn't noticed when i switched to Mysql

[hishamco]

So, is this mean there's no issue at all?!!

[hamdiRuwiss]

Yep , thank you for all the help

[hishamco]

So, this need to be closed ;)