SQL syntax error at user/[UID]/user_galleries when Content Access is enabled

Yes, I'm getting the same error.

Could this be related to the fact that the user profile settings do not appear for each user as reported in the other recent issue?

If it helps, here's what I'm seeing when I view the gallery tab in a user profile:

Could be a non-native speaker here. He means: Why do you reply to others and not to me.

Hello ianraf,

you have to understand that this error is not from Gallery Assist. If you can't belive it please search for this sql statement. This not exists.

my statement is:

SELECT count( * ) as items, n.nid, n.created, n.title
FROM {gallery_assist_item} p
JOIN {node} n ON n.nid = p.ref
WHERE p.uid = $user->uid
GROUP BY p.gref

your exposed here is
SELECT count( * ) as items, DISTINCT(n.nid), n.created, n.title

why you use "DISTINCT"??? If you test my query string directly in your MySQL you get a result if you test the same statement with DISTINCT you get a error also my is good the other is wrong.

Please answer this here and I give you a explanation why my statement is throug Drupal changed.

Regards
Juan Carlos

PS
I try (if I can and have time) to give answers to all the requested questions

That's odd. My code says like yours:

SELECT count( * ) as items, n.nid, n.created, n.title
FROM {gallery_assist_item} p
JOIN {node} n ON n.nid = p.ref
WHERE p.uid = $user->uid
GROUP BY p.gref

But when the query is revealed, it shows "DISTINCT"

Why would that be?

Dan

http://drupal.org/node/324070

the queries violate an undocumented requirement of db_rewrite_sql(). Namely that they shouldn't use 'SELECT *'. SELECT queries (on nodes at least) need to name the fields explicitly because of the way node authorisation works.

?????????? Is this above a clue?

Yes and not royerd,

the select * (select all columns) violated realy the db_rewrite_sql requiriments and I apply this rule. See here, I call the node fields explicitly:

SELECT count( * ) as items, n.nid, n.created, n.title
FROM {gallery_assist_item} p
JOIN {node} n ON n.nid = p.ref
WHERE p.uid = $user->uid
GROUP BY p.gref

My SELECT COUNT( * ) as items is a request to the table gallery_assist_items. I can use SELECT( p.pid ) as items or SELECT ( SELECT count(pid) FROM {table} WHERE ......) as items and it is the same.

The issue is only the rewrite of SQL statements of the sql_query. I find the use of DISTINCT by a request to the node table is unnecesary. Why? Because the nid s are the primary keys of the node table (are uniques). I find the sql_layer have to make a difference between requests to the node table and the node_revisions table, here you have multiples entries from a nid and the primary key is the vid. In this case can/should the SQL statement containing a DISTINCT.

I hoppe you understand what I explain.

Resume:
The sql_layer, produce a SQL error through the sql_db_rewrite() function if a access module is in use.

I think, I can separate the my query in two or three queries (not the right way) and the issue is for the first solved but with a overhead.

The result of my investigation about this is: I found many reports and discutions about this error here but not solutions.

that is the explanation why this error and why gallery assist at this point can't work properly but I can solve this issue at the module level.

I repeat, I hope you understand and if not please tell me it.

Regards
Juan Carlos

Yes, I understand.

Writing queries is over my head. But it seems you have located the problem. Very good.

For some reason the query inserts "distinct". Ugh.

If I could help, I would, but I am not good at queries.

Dan

I changed the name of this issue to avoid repeated reports. I less it open because the issue is currently not fixed and if you want contact me royerd. Use the contact form.

I am getting exactly the same error as shown in the screenshot in reply #3.

Hello roball,

can you tell me or you are using some access module?
I think maybe I have a solution for this issue but only for Gallery Assist.
This issue is older as my module and it is produced from the mysql.api

Thanks in advance
Juan Carlos

Yes Juan - you are right. I am using Content Access 6.x-1.2. After disabling that module, the error at user/[UID]/user_galleries was gone.

Ok I will investigate why this module insert a DISTICNT by a request of the node table. You understand? The nid will bee allways unique also it is unnecessary in this case to rewrite the statement.
I investigate tomorrow.

Thanks

Hello royerd and roball,

can you please test my first atempt to solve the DISTINCT issue???

I thank you in advance
Juan Carlos

Wow - the patch indeed solves the problem!

To apply the patch from within the "gallery_assist" directory, you just have to change the first two lines from

--- gallery_assist/gallery_assist.module	2010-02-03 05:12:12.000000000 +0100
+++ gallery_assist_2/gallery_assist.module	2010-03-01 04:59:53.000000000 +0100

to

--- gallery_assist.module	2010-02-03 05:12:12.000000000 +0100
+++ gallery_assist.module	2010-03-01 04:59:53.000000000 +0100

Nice that in the table there is now also a "Image" column. However, I don't think it makes sense to make this column sortable.

I've been applying the patch manually. I don't know how to use the patch line command.

Can you put it in the download file for me to test?

You are doing great work!

Or, Roball, could you attach that patched file for me?

Dan

Apply the patch as follows:

cd /etc/drupal/all/modules/gallery_assist
wget 'http://drupal.org/files/issues/gallery_assist_module-fix-DISTINCT-error_2.patch'
patch -p0 < gallery_assist_module-fix-DISTINCT-error_2.patch

Thanks roball. I just learned how to patch.

That fixed the error I was getting before.

Thanks Juan for the fix!

Dan

Hello roball,

You have right, it is unnecesary to make this column sortable!
this was the result from copy and paste habit :-)

here the patch with the not sorteable image field.

Thank you Juan - I am now testing your rc3 pre-release from http://drupal.org/node/729178#comment-2662130 which has your above patch incorporated. Images are no more sortable - fine. The only remaining issue on that table for me is which image is displayed for each gallery. It is *not* always the gallerie's first one - but I would expect that.

The latest uploaded image is displayed but you need the first, this give me a idea (funny, dass ich nicht von selbst darauf kam), I will implement a flag pro gallery so people can decide which image is the principal, cover ...
I test it now.

So I am getting this error too, except for some errors written to a log everything seems to work. Is it really necessary to fix this now or can I wait for the next version of gallery assist?

DISTINCT issue - fixed
Image field - sorteable removed
all new changes commited to the dev

Applied this version to my test env. It wiped all my galleries of images. Is this expected behavior? It's no biggie as this is a test environment. You might want to add that info to the release notes.

I also spied with my little eye this log entry:

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 '' at line 5 query: SELECT a.pid, a.nid, a.ref, a.gid, a.gref, a.sid, a.uid, a.fid, a.filename, a.opath, a.ppath, a.tpath, a.copyright, a.weight, f.timestamp, tp.lang, tp.ptitle, tp.palt, tp.pdescription, tp.format, tp.did FROM gallery_assist_item a JOIN gallery_assist_translated tp ON a.pid = tp.pid JOIN files f ON f.fid = a.fid WHERE a.gref=581 AND tp.lang = '' ORDER BY in /Users/blah/Sites/blahblah/sites/default/modules/gallery_assist/gallery_assist.module on line 1397.

I hope that helped.

Hello,
Hello, you mean 1.9-rc2 or the dev version????

thanks

It was the dev version, I thought I had replied to your post about this version.

This patch should *not* be applied to the current dev version (6.x-1.x-dev (2010-Mar-10) or later), since it already includes that fix. The problem reported at #27 does not occur then, at least for me.

Ok, perhaps we are having a language problem... Let me make this perfectly clear. My comment in 27 was: I downloaded the new dev version and copied it into my site's module directory. I did not patch anything... I just installed the newer version. If you consider that a patch then thats fine but I did not run any patch command.

Now this maybe a red herring or a clue to what may be going on. I think there is a problem between two modules that I use in my installation, Gallery Assist and Path_Redirect. In my present production site I generate errors to my log file with infinite looping whilst opening a gallery... Although this generates the end user experience is not impacted they still see the galleries. I know a patch exists for this infinite loop , but I was unsuccessful in getting it to work. I have another comment on that in that thread.

I was hoping if I downloaded and applied this newer version that problem would go away. It was a stab in the dark. During this attempt I noticed the strange occurrence (comment 27) of the galleries not being seen also the UI for adding new pics is missing as well. We have many variables to solve for I don't know if we have enough equations.

The only other thing I do that maybe unusual is I have my production site on a windows machine running XAMPP and my test environment in on a Mac running MAMP.

I hope this helps and I want to thank all the developers of drupal, apache, php and mysql. I am a web neophyte and I was able to create a website with no previous experience. Thanks!

Hello Lars Vandergraaf,

this is the point, why some times I can't help. Read Your report #27 is a report with missing information and in the wrong place.

See #1 from author this thread ianraf
* Apache/2.2.11 (Unix) DAV/2 mod_ssl/2.2.11 OpenSSL/0.9.8l PHP/5.2.9 mod_perl/2.0.4 Perl/v5.10.0
* Versione MySQL client: 5.1.33
* Estensioni PHP: mysql

Your installation works in another system. The last explanation you made was missing etc etc.

The form of cooperation, "Help me understand your problem, so that I can reproduce and resolve it", was not applied.

Now I have understand your issue and I would be pleased if you create a new issue threat for this. So can people that use the same system as you and have the same error as you in the right thread read and write reports.

I thank you in advance
Juan Carlos

Juan Carlos,

So I see what you are saying although I applied your fix on my test environment that isn't xampp but Mamp and it created a side effect problem and even though both my production site(xampp) and test site were having the same symptoms(initial thread subject) before I applied your fix. You think the issue I am having with the new dev on my test environment is a separate problem. Well the only way to find out that is to test it on my production site. No can do.

Perhaps I am really stupid but I applied your patch to a working test environment and it created problems. Why you would want to classify this as another problem sounds like a lot of book keeping to me. If you want to classify it as something else be my guest. It's ok with me.

Thanks for all your hard work. I really do appreciate it.

PS. I can live with this message(back up to #1) being written to a logfile but the cure right now on my test environment is worse. I hope you can understand what I am saying.