Hi,
I hope that I'm not wasting anybodies time, but I'm getting an error that I can't resolve. I'm in the process of developing a site at the moment that will need a gallery and Acidfree sounds perfect for the job. So I decided to test it on a bare bones fresh drupal install before intergrating it into our main development.

I've followed the install instructions for Acidfree to the letter, first of all installing the filemanager.module and loading the necessary .mysql file. Then setting up of a public and private file areas.

Then untaring the acidfree module into the modules folder and running the mysql script. I went to the acidfree/test page and got the all clear there. Then in the create content menu 'Acidfree media' is available. But when I go to create an album or any other acidfree content I get the following error...? The error is printed in two lines across the top of the page, I have formatted it to make it easier to read. The same error is presented in the logs.

user error: You have an error in your SQL syntax near 
'ON n.nid = acidfree.aid 
JOIN acidfree_hierarchy ON acidfree_hierarchy.child = ac' at line 1

query: 
SELECT n.nid, child FROM node n 
JOIN acidfree ON n.nid = acidfree.aid 
JOIN acidfree_hierarchy ON acidfree_hierarchy.child = acidfree.aid 
WHERE parent = 1 AND class='album' 
ORDER BY weight ASC, aid DESC 
in /home/sam/webs/drupalacidtest/includes/database.mysql.inc on line 66.

I haven't seen this particular bug posted on the forums, looking forward to using Acidfree so I hope that someone can help.

Sam

Comments

vhmauery’s picture

vhmauery commented

Are you possibly using an old version of mysql? I think the oldest mysql version this has been tested on is 4.02. Can you try the sql manually using the mysql client? If you do this, try putting an AS in where it says node n so your query looks like this:

SELECT n.nid, child FROM node AS n
JOIN acidfree ON n.nid = acidfree.aid
JOIN acidfree_hierarchy ON acidfree_hierarchy.child = acidfree.aid
WHERE parent = 1 AND class='album'
ORDER BY weight ASC, aid DESC
shouchen’s picture

shouchen commented

I just tried this on mysql-3.23.58 (Fedora Core 3)

Both versions result in error:

mysql> SELECT n.nid, child FROM node n JOIN acidfree ON n.nid = acidfree.aid JOIN acidfree_hierarchy ON acidfree_hierarchy.child = acidfree.aid WHERE parent = 1 AND class='album' ORDER BY weight ASC, aid DESC;
ERROR 1064: You have an error in your SQL syntax near 'ON n.nid = acidfree.aid JOIN acidfree_hierarchy ON acidfree_hierarchy.child = ac' at line 1

mysql> SELECT n.nid, child FROM node AS n JOIN acidfree ON n.nid = acidfree.aid JOIN acidfree_hierarchy ON acidfree_hierarchy.child = acidfree.aid WHERE parent = 1 AND class='album' ORDER BY weight ASC, aid DESC;
ERROR 1064: You have an error in your SQL syntax near 'ON n.nid = acidfree.aid JOIN acidfree_hierarchy ON acidfree_hierarchy.child = ac' at line 1
vhmauery’s picture

vhmauery commented

It looks like this is a problem with sql versions. It works fine with newer versions of mysql. I do not know sql or the difference between mysql versions well enough to know what is the problem. I could use some help with this.

NaX’s picture

NaX commented

Not exactly a whiz at mysql ether but maybe it is because you have 2 joins following each other.
Try this.

SELECT n.nid, child FROM node AS n
JOIN acidfree ON n.nid = acidfree.aid left
JOIN acidfree_hierarchy ON acidfree_hierarchy.child = acidfree.aid
WHERE parent = 1 AND class='album'
ORDER BY weight ASC, aid DESC
shouchen’s picture

shouchen commented

Putting "LEFT" before both JOINs worked for me:

mysql> SELECT n.nid,child FROM node AS n JOIN acidfree ON n.nid = acidfree.aid LEFT JOIN acidfree_hierarchy ON acidfree_hierarchy.child = acidfree.aid WHERE parent = 1 AND class='album' ORDER BY weight ASC, aid DESC;
ERROR 1064: You have an error in your SQL syntax near 'ON n.nid = acidfree.aid LEFT JOIN acidfree_hierarchy ON acidfree_hierarchy.child' at line 1

mysql> SELECT n.nid,child FROM node AS n LEFT JOIN acidfree ON n.nid = acidfree.aid LEFT JOIN acidfree_hierarchy ON acidfree_hierarchy.child = acidfree.aid WHERE parent = 1 AND class='album' ORDER BY weight ASC, aid DESC;
Empty set (0.00 sec)

I'm not a SQL expert either, so I don't know if this will cause the wrong results to be returned. In my case, "Empty set" is an OK result. (I have empty acidfree-related tables.)

There are other queries in acidfree.module that would probably benefit from the same change, syntax-wise anyway.

-Steve

vhmauery’s picture

vhmauery commented
Status: Active » Fixed

Thanks NaX. I think that is the problem (though we want to use INNER joins rather than LEFT). I have committed the changes to CVS 4.6 branch and they should be repackaged in about 8 hours or so.

vhmauery’s picture

vhmauery commented
Status: Fixed » Closed (fixed)

The fix is available, so I am closing this bug.