Jump to content
Zeka

Syntax error or access violation: 1064 in selector with OR-groups

Recommended Posts

Hi.

I'm getting this error 

SQLSTATE[42000]: Syntax error or access violation: 1064 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 '.id 
		FROM `pages` 
		JOIN field_banner_categories AS field_banner_categories O' at line 7

with this selector

$this->wire('pages')->find("banner_position=sidebar, (banner_categories|banner_sections=$page), (banner_custom_pages=$page)");

banner_position - Select Options field

banner_categories, banner_sections, banner_custom_pages - Page reference fields. 

This selector produces such SQL queries:

banner_categories|banner_sections=1057
----------------------------
SELECT SQL_NO_CACHE pages.id 
FROM `pages` 
LEFT JOIN field_banner_categories AS field_banner_categories ON field_banner_categories.pages_id=pages.id AND ((((field_banner_categories.data='1057') ) )) 
LEFT JOIN field_banner_sections AS field_banner_sections ON field_banner_sections.pages_id=pages.id AND ((((field_banner_sections.data='1057') ) )) 
WHERE ((((field_banner_categories.data='1057') ) ) 
OR (((field_banner_sections.data='1057') ) ) )

banner_custom_pages=1057
----------------------------
SELECT SQL_NO_CACHE pages.id 
FROM `pages` 
JOIN field_banner_custom_pages AS field_banner_custom_pages ON field_banner_custom_pages.pages_id=pages.id AND ((((field_banner_custom_pages.data='1057') ) ))

banner_position=sidebar, =(banner_categories|banner_sections=1057), =(banner_custom_pages=1057), status<1024
----------------------------
SELECT SQL_NO_CACHE pages.id,pages.parent_id,pages.templates_id 
FROM `pages` 
JOIN field_banner_position AS field_banner_position ON field_banner_position.pages_id=pages.id AND (((field_banner_position.data='1' ) )) 
WHERE (pages.status<1024) 
AND (
pages.id IN (
SELECT SQL_NO_CACHE pages.id 
FROM `pages` 
LEFT JOIN field_banner_categories AS field_banner_categories ON field_banner_categories.pages_id=pages.id AND ((((field_banner_categories.data='1057') ) )) 
LEFT JOIN field_banner_sections AS field_banner_sections ON field_banner_sections.pages_id=pages.id AND ((((field_banner_sections.data='1057') ) )) 
WHERE ((((field_banner_categories.data='1057') ) ) 
OR (((field_banner_sections.data='1057') ) ) ) 
) 
OR pages.id IN (
SELECT SQL_NO_CACHE pages.id 
FROM `pages` 
JOIN field_banner_custom_pages AS field_banner_custom_pages ON field_banner_custom_pages.pages_id=pages.id AND ((((field_banner_custom_pages.data='1057') ) )) 
)
) 
GROUP BY pages.id

I thought that it can be relative to "OR" selector

banner_categories|banner_sections=$page

So I tried 

$this->wire('pages')->find("banner_position=sidebar, (banner_sections=$page), (banner_custom_pages=$page)");

It also produces the same error. 

Any thought? 

Thanks. 

Share this post


Link to post
Share on other sites

Hi Zeka,

No mystery here, you get an error because you have SQL_NO_CACHE in more than one statement. It's also not permitted in sub-queries.

 

What is your MySQL server version ?

  • Like 1

Share this post


Link to post
Share on other sites

@flydev Thank. Not a guru in SQL. 

MySQL 5.7.16 on a remote server. 

On the local server, I have 5.7.19 and I don't remember that I have such error.

Is this error server specific? 

Share this post


Link to post
Share on other sites
3 hours ago, Zeka said:

Is this error server specific? 

Yes.

 

3 hours ago, Zeka said:

I don't remember that I have such error.

can depend on the server config.

  • Like 2

Share this post


Link to post
Share on other sites

@flydev Thank a lot. You brought me to mind that I have been testing the performance of some queries and declared in the config.

$config->dbCache = false;

🤦‍♂️

  • Like 2

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...