Domain Access Upgrade (> 7.x-3.7) causes PDOException: SQLSTATE[42000] for User 1 (i18n_select)

Can you please copy the entire, processed query here using Devel module. This is not readable.

It also does not happen for me (nor for most users). So steps to replicate and information about your MySQL version are required.

to I have the same problem since the update version.

Complete information below

PDOException: 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 'EXISTS (SELECT na.nid AS nid FROM node_access na WHERE (( (na.gid = '0')' at line 2: SELECT COUNT(*) AS expression FROM {comment} c INNER JOIN {node} node ON c.nid = node.nid WHERE (c.nid = :db_condition_placeholder_0) AND (c. EXISTS (SELECT na.nid AS nid FROM {node_access} na WHERE (( (na.gid = :db_condition_placeholder_1) AND (na.realm = :db_condition_placeholder_2) )OR( (na.gid = :db_condition_placeholder_3) AND (na.realm = :db_condition_placeholder_4) )OR( (na.gid = :db_condition_placeholder_5) AND (na.realm = :db_condition_placeholder_6) )OR( (na.gid = :db_condition_placeholder_7) AND (na.realm = :db_condition_placeholder_8) ))AND (na.grant_view >= :db_condition_placeholder_9) AND (c.nid = na.nid) )) AND (node.language IN (:db_condition_placeholder_10, :db_condition_placeholder_11)) ; Array ( [:db_condition_placeholder_0] => 6990 [:db_condition_placeholder_1] => 0 [:db_condition_placeholder_2] => all [:db_condition_placeholder_3] => 0 [:db_condition_placeholder_4] => domain_site [:db_condition_placeholder_5] => 1 [:db_condition_placeholder_6] => domain_id [:db_condition_placeholder_7] => 1 [:db_condition_placeholder_8] => domain_unpublished [:db_condition_placeholder_9] => 1 [:db_condition_placeholder_10] => es [:db_condition_placeholder_11] => und ) en PagerDefault->execute() (línea 74 de /pager.inc).

Mysql 5.0.45
Php 5.0.45
Server version: Apache/2.2.9 (Unix)
Server built: Jul 14 2008 15:29:50

Same thing is happening to me.
I suppose it's related to internationalization.

In my logs I get
Menu and taxonomy name mismatch: Proyectos abiertos != open projects
("Proyectos abiertos" is the spanish translation of open projects")

I will try to fix that problem first and see if the error goes away.

fixed by rebuilding the taxonomy menu. The error stays.

And I assume that when you turn off the "Restrict node views for administrators " setting, this goes away?

I cannot replicate the error.

I need a list of steps to reproduce, including the modules being used for translation.

And where this query appears. This looks like a {comment} table query, which might explain the failure.

And PLEASE replace the db_placeholders when reporting a query.

I encountered the same problem, with a new install of domain access module (not an upgrade from 7.x-3.7).

At first everything worked fine but after installing http://drupal.org/project/support (which needs comment module) it was broken. The problem arises when I try to view a node with comments enabled.

Error
PDOException: 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 'EXISTS (SELECT na.nid AS nid FROM node_access na WHERE (( (na.gid = '0') AND (' at line 3: SELECT COUNT(*) AS expression FROM {comment} c INNER JOIN {node} node ON c.nid = node.nid LEFT OUTER JOIN {support_ticket} st ON st.nid = node.nid WHERE (c.nid = :db_condition_placeholder_0) AND (c. EXISTS (SELECT na.nid AS nid FROM {node_access} na WHERE (( (na.gid = :db_condition_placeholder_1) AND (na.realm = :db_condition_placeholder_2) )OR( (na.gid = :db_condition_placeholder_3) AND (na.realm = :db_condition_placeholder_4) )OR( (na.gid = :db_condition_placeholder_5) AND (na.realm = :db_condition_placeholder_6) )OR( (na.gid = :db_condition_placeholder_7) AND (na.realm = :db_condition_placeholder_8) ))AND (na.grant_view >= :db_condition_placeholder_9) AND (c.nid = na.nid) )) AND (node.language IN (:db_condition_placeholder_10, :db_condition_placeholder_11)) AND( (st.client IN (:db_condition_placeholder_12, :db_condition_placeholder_13)) OR (st.client IS NULL ) ); Array ( [:db_condition_placeholder_0] => 34 [:db_condition_placeholder_1] => 0 [:db_condition_placeholder_2] => all [:db_condition_placeholder_3] => 0 [:db_condition_placeholder_4] => domain_site [:db_condition_placeholder_5] => 1 [:db_condition_placeholder_6] => domain_id [:db_condition_placeholder_7] => 1 [:db_condition_placeholder_8] => domain_unpublished [:db_condition_placeholder_9] => 1 [:db_condition_placeholder_10] => en [:db_condition_placeholder_11] => und [:db_condition_placeholder_12] => 1 [:db_condition_placeholder_13] => 2 ) in PagerDefault->execute() (line 74 of /Applications/MAMP/htdocs/atsgroepdemo/www/includes/pager.inc).

Used modules: see attachment

I still can't replicate the error. I enabled Support, created a language, create some nodes (some Tickets and some Articles) with content.

No issues.

More information on this issue:

The problem only occurs when logged in with user 1 and when the core 'Domain Access' module is enabled.

Does it only occur when the Domain Access setting for "Restrict node views for administrators" is enabled?

If so the simple fix is the disable that until we pinpoint the issue, which would be in domain_alter_node_query(). But that function is really just a clone of a core function, so the error is curious.

Notice that in the original query, we have these lines throwing the actual error:

AND (c. EXISTS (SELECT na.n

Which means that no column is being added to the query. If we knew which base query this was, it would make things much easier.

It's like you said agentrickard: it only occurs when you've set the Domain Access setting to "Restrict node views for administrators".

Right, so once we know what code is generating the query, we can test a solution.

I'm still inclined to think that this is a bug in another module. The code we are executing is a direct clone of _node_query_node_access_alter(). The only part that changes is that we have removed the logic that skips the query alter if the user can bypass node access.

If you can debug what is happening, take a look inside domain_alter_node_query(). A debug_backtrace() on that function would tell us where the query is coming from.

There is, of course, a workaround in that you can disable this feature, which (ironically) is designed to help administrators debug their sites.

I noticed the error also occurs when I try to view a term page.

Both acktraces attached.

Hi agentrickard

I have the same problem. I'm still debugging but one this is sure for me:

I have a node with enabled comments. The error happens here. On a node without comments it does not happen. And of course, I have panels and ctools enabled and the comment form is on the panel for this node.

Maybe this helps reproducing it. If I disable the list of comments on the panel, the page works without a problem.

It seems, that it has to do with a pager:
"SELECT c.cid AS cid, SUBSTRING(c.thread, 1, (LENGTH(c.thread) - 1)) AS torder FROM {comment} c WHERE (c.nid = :db_condition_placeholder_0) ORDER BY torder ASC::node();"

What really scares me is the fact, that the same code works on my live server. The only difference is the php version:

Live Server: PHP Version 5.2.10
Local: PHP Version 5.3.9

With what version did you test agentrickard?

I still cannot reproduce. Here's the comment query I get when viewing the node page:

SELECT COUNT(*) AS expression FROM comment c LEFT OUTER JOIN support_ticket st ON st.nid = c.nid WHERE (c.nid = '51') AND ( EXISTS (SELECT na.nid AS nid FROM node_access na WHERE (( (na.gid = '0') AND (na.realm = 'all') )OR( (na.gid = '0') AND (na.realm = 'domain_site') )OR( (na.gid = '1') AND (na.realm = 'domain_id') )OR( (na.gid = '1') AND (na.realm = 'domain_unpublished') ))AND (na.grant_view >= '1') AND (c.nid = na.nid) )) AND( (st.client IN ('1')) OR (st.client IS NULL ) )

The part that I don't seem to be able to reproduce is this section:

LEFT OUTER JOIN {support_ticket} st ON st.nid = node.nid WHERE (c.nid = :db_condition_placeholder_0) AND (c. EXISTS 

We need to figure out why that c. is being added to the EXISTS clause. That's the error. I wonder if Panels has anything to do with it? I'm not testing with Panels.

What is the SQL error you get on the taxonomy term page?

FYI. My MAMP has PHP 5.2.17 and 5.4.4 and they both don't work.

This is the executed query on the taxonomy term page.

SELECT t.nid AS nid, t.tid AS tid, t.sticky AS sticky, t.created AS created FROM {taxonomy_index} t WHERE (tid = :db_condition_placeholder_0) AND ( EXISTS (SELECT na.nid AS nid FROM {node_access} na WHERE (( (na.gid = :db_condition_placeholder_1) AND (na.realm = :db_condition_placeholder_2) )OR( (na.gid = :db_condition_placeholder_3) AND (na.realm = :db_condition_placeholder_4) )OR( (na.gid = :db_condition_placeholder_5) AND (na.realm = :db_condition_placeholder_6) )OR( (na.gid = :db_condition_placeholder_7) AND (na.realm = :db_condition_placeholder_8) ))AND (na.grant_view >= :db_condition_placeholder_9) AND (t.nid = na.nid) )) ORDER BY t.sticky DESC, t.created DESCSELECT COUNT(t.nid) AS expression FROM {taxonomy_index} t WHERE (tid = :db_condition_placeholder_0) AND ( EXISTS (SELECT na.nid AS nid FROM {node_access} na WHERE (( (na.gid = :db_condition_placeholder_1) AND (na.realm = :db_condition_placeholder_2) )OR( (na.gid = :db_condition_placeholder_3) AND (na.realm = :db_condition_placeholder_4) )OR( (na.gid = :db_condition_placeholder_5) AND (na.realm = :db_condition_placeholder_6) )OR( (na.gid = :db_condition_placeholder_7) AND (na.realm = :db_condition_placeholder_8) ))AND (na.grant_view >= :db_condition_placeholder_9) AND (t.nid = na.nid) ))

Does that actually execute? Or does it error out?

As reported, this part of the query is simply malformed:

ORDER BY t.sticky DESC, t.created DESCSELECT COUNT(t.nid) AS expression FROM {taxonomy_index} t WHERE (tid = :db_condition_placeholder_0) AND ( EXISTS (SELECT na

What version of Drupal core are you using?

My version of the same taxonomy query:

SELECT t.nid AS nid, t.tid AS tid, t.sticky AS sticky, t.created AS created FROM taxonomy_index t LEFT OUTER JOIN support_ticket st ON st.nid = t.nid WHERE (tid = '1') AND ( EXISTS (SELECT na.nid AS nid FROM node_access na WHERE (( (na.gid = '0') AND (na.realm = 'all') )OR( (na.gid = '0') AND (na.realm = 'domain_site') )OR( (na.gid = '1') AND (na.realm = 'domain_id') )OR( (na.gid = '1') AND (na.realm = 'domain_unpublished') ))AND (na.grant_view >= '1') AND (t.nid = na.nid) )) AND( (st.client IN ('1')) OR (st.client IS NULL ) ) ORDER BY t.sticky DESC, t.created DESC LIMIT 10 OFFSET 0

It doesn't execute.

PDOException: 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 'EXISTS (SELECT na.nid AS
...
in PagerDefault->execute() (regel 74 van /Applications/MAMP/htdocs/atsgroep/www/includes/pager.inc).

I'm using Drupal core version 7.19.

Hi agentrickard

I think it has to do with ctools ot with panels. You should be able to reproduce it this way:

1. Install ctools and panels and activate it
2. activate comments on story node type
3. Create a panel site for node view and override the story node type
4. Add the full node with all addons to the panel
5. Add a comment form to the panel
6. Add a comment flat list to the panel

Then you will be able to see the error as user 1.

If that's the case, I'm very tempted to re-file this as a Panels bug, but I'd like to reproduce it first.

@ayalon

Step 3 in your instructions doesn't make any sense to me. Please export your panel.

Here's the panel layout I created, overriding the default "article" content type. No errors. I still cannot replicate this issue.

Does it occur for anon users or other non-admin users? If so, this is a Panels bug.

Also had this issue and retrograding to 7.x-3.7 solved the issue for me

@Anticosti

Please provide more information. That is not actionable. I have not been able to replicate this bug report.

@ agentrickard

Here's the complete (exhaustive) list of my activated modules:

drupal 7.21

Modules

1. Administration menu 7.x-3.0-rc4
2. Advanced Forum 7.x-2.0
3. Better Formats 7.x-1.0-beta1
4. Block Class 7.x-1.2
5. Calendar 7.x-3.4
6. CAPTCHA 7.x-1.0-beta2
7. CCK Blocks 7.x-1.1
8. Collapsiblock 7.x-1.0
9. Colorbox 7.x-1.4
10. Comment Notify 7.x-1.1
11. Context 7.x-3.0-beta6
12. Chaos tools 7.x-1.2
13. Custom Search 7.x-1.11
14. Date 7.x-2.6
15. Dynamic display block 7.x-1.0
16. Delta API 7.x-3.0-beta11
17. Domain Access 7.x-3.7
18. Domain Context 7.x-1.0-alpha1
19. Domain Blocks 7.x-3.0-alpha1
20. Domain Taxonomy 7.x-3.x-dev
21. Drupad 7.x-1.13
22. DrupalGap 7.x-1.2+8-dev
23. Echo 7.x-1.9
24. ed_classified 7.x-3.0+3-dev
25. Emogrifier 7.x-1.18
26. Enabled modules 7.x-1.0-beta3
27. Entity API 7.x-1.0
28. Entity Reference 7.x-1.0
29. Entity reference prepopulate 7.x-1.2
30. Entity Translation 7.x-1.0-beta2
31. Features 7.x-1.0
32. features_vivacity 7.x-1.1
33. Feedback 7.x-2.x-dev
34. Fieldgroup 7.x-1.1
35. File (Field) Paths 7.x-1.0-beta3+4-dev
36. Filters Image Effect (filtersie) 7.x-1.0
37. Flag 7.x-2.0
38. @font-your-face 7.x-2.6
39. front 7.x-2.1
40. GMap 7.x-1.0-beta1
41. HTML Mail 7.x-2.65
42. Internationalization 7.x-1.8
43. Views translation 7.x-3.x-dev
44. Icon Fonts 7.x-1.0-alpha1+1-dev
45. Image resize filter 7.x-1.13
46. jCarousel 7.x-2.6
47. Localization client 7.x-1.1
48. Localization update 7.x-1.0-beta3
49. Language Switcher Dropdown 7.x-1.5
50. Legal 7.x-1.2+4-dev
51. Libraries 7.x-2.0
52. Link Badges 7.x-1.1
53. LM PayPal 7.x-1.x-dev
54. Location 7.x-3.0-alpha1
55. LoginToboggan 7.x-1.3
56. Mail System 7.x-2.34
57. Mail Editor 7.x-1.0
58. Make Meeting 7.x-2.0-rc3
59. Media 7.x-1.2
60. Menu Icons 7.x-3.0-beta3
61. Message 7.x-1.7
62. Message notify 7.x-2.3
63. Messaging 7.x-1.0-alpha2+2-dev
64. Module filter 7.x-1.7
65. MultiBlock 7.x-1.1
66. Nice Menus 7.x-2.1+3-dev
67. Node Reference URL Widget 7.x-1.12
68. node_clone 7.x-1.0-rc1+1-dev
69. Notifications 7.x-1.0-alpha2+2-dev
70. Organic groups 7.x-2.0-rc2
71. Panels 7.x-3.3
72. Pathauto 7.x-1.2
73. Pathologic 7.x-2.10
74. Private messages 7.x-1.3
75. Progress 7.x-1.4
76. Quicktabs 7.x-3.4
77. Read More Control 7.x-1.0
78. Real name 7.x-1.0
79. References 7.x-2.0
80. Responsive images and styles 7.x-1.3
81. Rules 7.x-2.2
82. Services 7.x-3.3
83. Signup 7.x-1.x-dev
84. SimpleAds 7.x-1.8
85. Simplenews 7.x-1.0
86. simplenews_content_selection 7.x-1.0-alpha5
87. SmartCrop 7.x-1.0-beta2
88. String Overrides 7.x-1.8
89. Superfish 7.x-1.9-beta5
90. Tab Tamer 7.x-1.1+1-dev
91. Taxonomy Manager 7.x-1.0-rc1
92. Translation Management Core 7.x-1.0-alpha2
93. Token 7.x-1.4
94. Translation helpers 7.x-1.0
95. Translation table 7.x-1.0-beta1
96. Transliteration 7.x-3.1
97. Username available AJAX check 7.x-1.x-dev
98. Variable 7.x-2.2
99. Views 7.x-3.5
100. Views Bulk Operations 7.x-3.1
101. views_datasource 7.x-1.x-dev
102. Views Grouping Row Limit 7.x-1.x-dev
103. Views Slideshow 7.x-3.0
104. Views System 7.x-3.2
105. Workbench 7.x-1.2
106. Workbench Access 7.x-1.2
107. Workbench Files 7.x-1.0
108. Workbench Media 7.x-2.1
109. Wysiwyg 7.x-2.2
110. Wysiwyg Template Plugin 7.x-2.9

Sorry for this long list ;(

Kind Regards,

That doesn't help either.

See https://drupal.org/node/73179

I need repeatable steps to reproduce the bug.

Server
Perl
Sendmail /usr/sbin/sendmail
PHP version 5.3.21
Path to PHP /usr/bin/php
Configure Command './configure' '--disable-fileinfo' '--enable-bcmath' '--enable-calendar' '--enable-exif' '--enable-ftp' '--enable-gd-native-ttf' '--enable-libxml' '--enable-magic-quotes' '--enable-mbstring' '--enable-pdo=shared' '--enable-soap' '--enable-sockets' '--enable-wddx' '--enable-zip' '--prefix=/usr' '--with-bz2' '--with-curl=/opt/curlssl/' '--with-freetype-dir=/usr' '--with-gd' '--with-gettext' '--with-gmp' '--with-imap=/opt/php_with_imap_client/' '--with-imap-ssl=/usr' '--with-jpeg-dir=/usr' '--with-kerberos' '--with-libdir=lib64' '--with-libexpat-dir=/usr' '--with-libxml-dir=/opt/xml2' '--with-libxml-dir=/opt/xml2/' '--with-mcrypt=/opt/libmcrypt/' '--with-mm=/opt/mm/' '--with-mysql=/usr' '--with-mysql-sock=/var/lib/mysql/mysql.sock' '--with-mysqli=/usr/bin/mysql_config' '--with-openssl=/usr' '--with-openssl-dir=/usr' '--with-pcre-regex=/opt/pcre' '--with-pdo-mysql=shared' '--with-pdo-sqlite=shared' '--with-pic' '--with-png-dir=/usr' '--with-pspell' '--with-sqlite=shared' '--with-tidy=/opt/tidy/' '--with-xmlrpc' '--with-xpm-dir=/usr' '--with-xsl=/opt/xslt/' '--with-zlib' '--with-zlib-dir=/usr'
Server API CGI/FastCGI
Ruby 1.8.7
Rails 3.2.11
Python 2.4.3
Python 2.6.1
Python 3.0.1
MySQL 5.1

Repeatable: Always
Steps to repeat:
1. Upgrade to 7.x-3.8 from 7.x-3.7
2. Visit Front page in site
3. Click on an Article's photography (linked to the complete content)

Expected Results:
User suppose to see the complete article content page

Actual Results:
Blank Page returns this error:

PDOException : 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 'EXISTS (SELECT na.nid AS nid FROM node_access na WHERE (( (na.gid = '0') AND (' at line 2: SELECT COUNT(*) AS expression FROM {comment} c INNER JOIN {node} node ON c.nid = node.nid WHERE (c.nid = :db_condition_placeholder_0) AND (c. EXISTS (SELECT na.nid AS nid FROM {node_access} na WHERE (( (na.gid = :db_condition_placeholder_1) AND (na.realm = :db_condition_placeholder_2) )OR( (na.gid = :db_condition_placeholder_3) AND (na.realm = :db_condition_placeholder_4) )OR( (na.gid = :db_condition_placeholder_5) AND (na.realm = :db_condition_placeholder_6) )OR( (na.gid = :db_condition_placeholder_7) AND (na.realm = :db_condition_placeholder_8) )OR( (na.gid = :db_condition_placeholder_9) AND (na.realm = :db_condition_placeholder_10) ))AND (na.grant_view >= :db_condition_placeholder_11) AND (c.nid = na.nid) )) AND (node.language IN (:db_condition_placeholder_12, :db_condition_placeholder_13)) ; Array ( [:db_condition_placeholder_0] => 88 [:db_condition_placeholder_1] => 0 [:db_condition_placeholder_2] => all [:db_condition_placeholder_3] => 0 [:db_condition_placeholder_4] => domain_site [:db_condition_placeholder_5] => 1 [:db_condition_placeholder_6] => domain_id [:db_condition_placeholder_7] => 1 [:db_condition_placeholder_8] => domain_unpublished [:db_condition_placeholder_9] => 2 [:db_condition_placeholder_10] => og_access:node [:db_condition_placeholder_11] => 1 [:db_condition_placeholder_12] => fr [:db_condition_placeholder_13] => und ) dans PagerDefault->execute() (ligne 74 dans /home/user/public_html/sitedev/includes/pager.inc).

Kind Regards,

Thanks. That's in line with the other reports, but I can't make my demo site throw that error. The problem is here:

AND (c. EXISTS (SELECT na.nid AS

There is something going on in each instance where the comment id is not being passed properly to the query alter hook.

Is that Article page a Panel? If so, can you export the Panel configuration and attach it here?

In any event, if you can stick a debug_backtrace inside domain_alter_node_query(), we still have to figure out where this query is being generated.

The current solution, btw, is not to downgrade, but to disable the "Restrict content views for administrators" setting of DA.

Interesting. The core version of that query has no JOIN.

SELECT COUNT(*) AS expression FROM comment c WHERE (c.nid = :db_condition_placeholder_0) AND ( EXISTS (SELECT na.nid AS nid FROM node_access na WHERE (( (na.gid = :db_condition_placeholder_1) AND (na.realm = :db_condition_placeholder_2) )OR( (na.gid = :db_condition_placeholder_3) AND (na.realm = :db_condition_placeholder_4) )OR( (na.gid = :db_condition_placeholder_5) AND (na.realm = :db_condition_placeholder_6) )OR( (na.gid = :db_condition_placeholder_7) AND (na.realm = :db_condition_placeholder_8) ))AND (na.grant_view >= :db_condition_placeholder_9) AND (c.nid = na.nid) )) 

Is this a Comment list View inside a Panel?

@ agentrickard
I followed your advise:

1. I upgraded to latest 7.x-3.9
2. I disabled the "Restrict content views for administrators" setting of DA

Result:
No more errors

Answer to your question:
I am not using panels. Even if the module is activated. Not there yet ;-)
Edit: This is just a straight out of the box front page articles' listing with summaries and pictures

Wish I could be of any help so...
To help you: How could I stick a "debug_backtrace inside domain_alter_node_query()" ?
Not familiar with this ;(

Kind Regards,

@ agentrickard
Followup:

1. I just reactivated the "Restrict content views for administrators" setting of DA

Result:
Still... No errors :) :) :)

Ok, that is interesting, and suggests something else may be awry. Have you rebuilt access permissions recently?

@ agentrickard > No, I didn't rebuilt access permissions recently.
BTW: I guess you devs sometime appreciate a little tap on the shoulder...
I love your module. Great & Awesome Job! Could not go without it anymore :)

Cheers,

@ agentrickard
Followup:

De/Reactivated the "Restrict content views for administrators" setting of DA

Just noticed that this issue only happens for me, when I am logged in as administrator.

Hope this may help ;-)

That behavior is consistent for everyone in the thread. However, I can't reproduce it to test it.

What version of Drupal core are you running? I'm testing against 7.21.

Running 7.21
I need to install Forum Access, so, I'll do a test activating it with it's depencies:
ACL and Chain Menu Access API

To follow...

Update: No change

Could it be related to this Views issue: http://drupal.org/node/1055616
(Caches are missed, due to changing :db_condition_placeholder)

I'll do some tests

That Views issue is unrelated.

Seems to be related to this private messaging issue:
http://drupal.org/node/1911132

Cheers,

No, that is a different error as well.

Please concentrate on telling us how to reproduce the error.

It occurs to me that the current stable is 7.x.-3.9, so perhaps that is why I can't replicate this error.

Please, everyone, check your versions.

I'm still investigating...
Restarted with a fesh install using version 7.x-3.9
All was going fine untill i installed a bunch of internationalization / strings / translation modules
Then... Here comes back the issue when I click on forum subject
I will revert and install one by one these last modules and see what I can come up with... ?

Cheers,

Title change.

Today I tried again updating the Domain Access Module.

I have hunted down the error, but I don't what is the reason.

I have a node page panel with a comment list on it.The node has comments enabled.

In the ctools/plugins/content_types/node_context/node_comments.inc

is a function called function ctools_comment_render($node, $conf) {}

There the comments a rendered with this function call:
$cids = comment_get_thread($node, $mode, $comments_per_page);

I went to the core function:

This SQL Statement produces the error:

  $query = db_select('comment', 'c')->extend('PagerDefault');
  $query->addField('c', 'cid');
  $query
    ->condition('c.nid', $node->nid)
    ->addTag('node_access')
    ->addTag('comment_filter')
    ->addMetaData('node', $node)
    ->limit($comments_per_page);
$cids = $query->execute()->fetchCol();

I tried a lot of modification. Only commenting out / disabling node access solves the SQL error.

Maybe agentrickard has an idea, because the query is now identified.

Let's try to replicate with that query, then.

I can't replicate that. I suspect this is a Panels issue.

I turned on the rewrite for admins feature and added this function to a test module.

function test_init() {
  $node = menu_get_object();
  $query = db_select('comment', 'c')->extend('PagerDefault');
  $query->addField('c', 'cid');
  $query
    ->condition('c.nid', $node->nid)
    ->addTag('node_access')
    ->addTag('comment_filter')
    ->addMetaData('node', $node)
    ->limit($comments_per_page);
  $cids = $query->execute();
}

No errors in the query, and the $base_table was found correctly.

Please report a new (cleaned up) issue in the Panels queue with steps to replicate.

Hi agentrickard

Thanks for your reply. Sorry for asking you again. I think, there must be an other module that conflicts with the node rewriting. But as far as I know, panels does no SQl Rewriting.

If I comment out this line:
->addTag('node_access')
the query works.

How can I find out, which module reacts on this code line. I really want to narrow down this error but I don't have enough knowledge about node access to really find the underlying problem.

My suspection is, that another module (besides from domain_access) modifies the query and leads to this SQL error. But how can I identify this module?

I too am seeing the PDOException on all pages apart from the front page when I am signed in as user 1, but not for anonymous users.

Toggling "Enforce rules on administrators" to "Do not enforce" fixes the PDOException, although this isn't a solution as I can then see content for all domains.

The problem looks like is has something to do with the i18n module.

These combinations of upgrades do not cause the PDOException:
- i18n <= 7.x-1.8 & Domain Access <= 7.x-3.7
- i18n <= 7.x-1.4 & Domain Access <= 7.x-3.10

As soon as I upgrade i18n to 7.x-1.5 or above, upgrading Domain Access to 7.x-3.8 or above would cause the PDOException problem to return.

I should also note that just upgrading either the i18n module to 7.x-1.8 or Domain Access to 7.x-3.10 works without a problem, so it looks like a conflict.

This is such an edge case and only really applies to user 1, so I'm not inclined to change anything. Any idea what i18n is actually doing?

Tadaaaaa!

I think I search for more than 3 days in total for reason of this bug.

Finally I found the problem. The problem occurs, if you have installed domain_access and i18n_select module.

If a query is tagged and altered by both modules

$query->addTag('node_access');
$query->addTag('i18n_select');

the i18n_select module alters the query wrong resulting in wrong sql.

Attached you find a patch that checks if the field_name is empty and does not alter the subquery of Domain Access. The patch works against i18n 1.9. Maybe someone can confirm that this fixes the problem.

Great work ayalon, using your patch seems to fix the problem for me, thanks!

Awesome, ayalon :)
Works for me too.
You saved me a whole bunch of headaches.
Great job !!! Cool ;-)

Kind Regards,

Is this not an i18n bug?

Made an issue on i18n_select: https://drupal.org/node/2066563
Greetings, Martijn

I still don't see how this is a bug in i18n and just skipping queries from rewriting for no known reason doesn't actually fix anything.

Anyway, moving to i18n queue for assessment -and to let the tests run-.

But before committing this, really, I'd need at least a test to make sure i18n_select keeps working as expected (with or without domain) because otherwise we'd be introducing a bug here (Wrong comment count on some pages).

I agree. Jose, if you find a good solution, let me know.

All we're doing in this case is applying node_access filters to user 1 and users with "bypass node access" permissions. The code is copied straight from core.

The patch works now for weeks on different multi domain + multi language sites.

PLease review.

same to me
when i update i18n module from 7.x-1.10 to 7.x-1.11

PDOException: 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 'EXISTS (SELECT na.nid AS nid FROM node_access na WHERE (( (na.gid = '0') AND (' at line 2: SELECT COUNT(*) AS expression FROM {comment} c INNER JOIN {node} node ON c.nid = node.nid WHERE (c.nid = :db_condition_placeholder_0) AND (c. EXISTS (SELECT na.nid AS nid FROM {node_access} na WHERE (( (na.gid = :db_condition_placeholder_1) AND (na.realm = :db_condition_placeholder_2) )OR( (na.gid = :db_condition_placeholder_3) AND (na.realm = :db_condition_placeholder_4) )OR( (na.gid = :db_condition_placeholder_5) AND (na.realm = :db_condition_placeholder_6) )OR( (na.gid = :db_condition_placeholder_7) AND (na.realm = :db_condition_placeholder_8) )OR( (na.gid = :db_condition_placeholder_9) AND (na.realm = :db_condition_placeholder_10) )OR( (na.gid = :db_condition_placeholder_11) AND (na.realm = :db_condition_placeholder_12) )OR( (na.gid = :db_condition_placeholder_13) AND (na.realm = :db_condition_placeholder_14) )OR( (na.gid = :db_condition_placeholder_15) AND (na.realm = :db_condition_placeholder_16) )OR( (na.gid = :db_condition_placeholder_17) AND (na.realm = :db_condition_placeholder_18) )OR( (na.gid = :db_condition_placeholder_19) AND (na.realm = :db_condition_placeholder_20) )OR( (na.gid = :db_condition_placeholder_21) AND (na.realm = :db_condition_placeholder_22) )OR( (na.gid = :db_condition_placeholder_23) AND (na.realm = :db_condition_placeholder_24) )OR( (na.gid = :db_condition_placeholder_25) AND (na.realm = :db_condition_placeholder_26) )OR( (na.gid = :db_condition_placeholder_27) AND (na.realm = :db_condition_placeholder_28) )OR( (na.gid = :db_condition_placeholder_29) AND (na.realm = :db_condition_placeholder_30) )OR( (na.gid = :db_condition_placeholder_31) AND (na.realm = :db_condition_placeholder_32) )OR( (na.gid = :db_condition_placeholder_33) AND (na.realm = :db_condition_placeholder_34) )OR( (na.gid = :db_condition_placeholder_35) AND (na.realm = :db_condition_placeholder_36) )OR( (na.gid = :db_condition_placeholder_37) AND (na.realm = :db_condition_placeholder_38) ))AND (na.grant_view >= :db_condition_placeholder_39) AND (c.nid = na.nid) )) AND (node.language IN (:db_condition_placeholder_40, :db_condition_placeholder_41)) ; Array ( [:db_condition_placeholder_0] => 1796 [:db_condition_placeholder_1] => 0 [:db_condition_placeholder_2] => all [:db_condition_placeholder_3] => 0 [:db_condition_placeholder_4] => domain_site [:db_condition_placeholder_5] => 7 [:db_condition_placeholder_6] => domain_id [:db_condition_placeholder_7] => 516 [:db_condition_placeholder_8] => og_access:node [:db_condition_placeholder_9] => 571 [:db_condition_placeholder_10] => og_access:node [:db_condition_placeholder_11] => 572 [:db_condition_placeholder_12] => og_access:node [:db_condition_placeholder_13] => 526 [:db_condition_placeholder_14] => og_access:node [:db_condition_placeholder_15] => 518 [:db_condition_placeholder_16] => og_access:node [:db_condition_placeholder_17] => 597 [:db_condition_placeholder_18] => og_access:node [:db_condition_placeholder_19] => 602 [:db_condition_placeholder_20] => og_access:node [:db_condition_placeholder_21] => 630 [:db_condition_placeholder_22] => og_access:node [:db_condition_placeholder_23] => 1800 [:db_condition_placeholder_24] => og_access:node [:db_condition_placeholder_25] => 1801 [:db_condition_placeholder_26] => og_access:node [:db_condition_placeholder_27] => 1802 [:db_condition_placeholder_28] => og_access:node [:db_condition_placeholder_29] => 1803 [:db_condition_placeholder_30] => og_access:node [:db_condition_placeholder_31] => 1804 [:db_condition_placeholder_32] => og_access:node [:db_condition_placeholder_33] => 1805 [:db_condition_placeholder_34] => og_access:node [:db_condition_placeholder_35] => 1806 [:db_condition_placeholder_36] => og_access:node [:db_condition_placeholder_37] => 1807 [:db_condition_placeholder_38] => og_access:node [:db_condition_placeholder_39] => 1 [:db_condition_placeholder_40] => en [:db_condition_placeholder_41] => und ) in PagerDefault->execute() (line 74 of /var/www/d7/college/includes/pager.inc).

@A.Eid,
There is no change in that version for i18n_select, so the issue should be something else.

And btw, your issue seems to be with OG, so definitely not this one.

For the rest of the issue:
I still haven't seen any explanation about why this is a i18n issue, nor the patch passes tests so closing this one, unless someone can provide that. See (and address questions) comment #58 if anyone wants to see this moving.

OK, I'll try to explain why it is a i18n_select issue. In function i18n_select_check_conditions "Check all query conditions have a table alias" is performed, so if a field in condition has no table alias - it is added. But conditions such as EXISTS have no field set and a condition of form

array('field' => '', 'value' => SelectQuery, 'operator' => 'EXISTS')
is transformed to
array('field' => 'c.', 'value' => SelectQuery, 'operator' => 'EXISTS')

and the table alias before EXISTS is the cause of our error

EXISTS condition is added in _node_query_node_access_alter

The patch fixed this issue for me and I think it failed to pass the tests because it was generated from drupal's root directory

The patch fixed the issue for me also.

I've made small changes to patch from #53, because it made i18n_select_check_conditions function to return FALSE, which will affect nodes and taxonomy terms selects. If that function will return FALSE selects will not take into account the current language.

53: comments-fix-domain-access-i18_select_compatibility.patch queued for re-testing.

I humbled over this issue and have tried to reproduce it and I can confirm the conditions and circumstances from comment #53 under which this error occurs. It breaks the side and the whole main content region and everything below is not rendered no more. The html header and the branding where still there. I also wanted to avoid that more than one error is causing this issue in combination and turned off modules and custom themes step by step. Finally running bartik under D7.28 with latest dev of i18n and domain. Error still there. Info: The test site here has no comments. Only some standard article nodes set up in 2 languages for 2 domains, each domain with the 2 enabled language translated nodes running it with the language settings path -> domain1.com/en/node -> domain2.com/de/node. The patch "works" but I'm not deep enough in the code to say this patch is finally tested, since I don't know of possible side effects ... requeued for testing.

And finally testing #66 patch modification here on test stage results in

git apply -v comments-fix-domain-access-i18_select_compatibility2.patch
Checking patch i18n_select/i18n_select.module...
Applied patch i18n_select/i18n_select.module cleanly.

without any trouble.

I'll queue dscutaru's modified patch for testing. Therefore in need to re-upload it and set the issue to "needs review".

PASSED: [[SimpleTest]]: [MySQL] 2,223 pass(es).

perfect. RTBC. patch works here. regarding SQL errors disappear. credits go to patches from ayalon and follow up from dscutaru.

This syntax is redundant:

isset($condition['field']) && !empty($condition['field'])

You can just use:

!empty($condition['field'])

Because !empty returns FALSE if the value is not set.

true,

new patch ready for testing

Ok, this finally makes sense, patch looks good... committed.

Thanks everybody.

Note: Updated title (and commit message) to provide some context when in i18n project.

thanks to @ayalon (#53) for researching 3 days and to point us to the right direction ....

You folks are great! I just installed i18n today and had the same PDO exception when i18n_select module was enabled. (I have Domain Access already installed). The one line change to i18n_select.module per the patch fixes the problem. Thanks!