MySQL 3.x Support

Sorry, I've been a little busy with work lately. I will try to put this a little higher in the acidfree queue.

I too am trying to use the wonderful Acidfree with Drupal 4.7 beta 4, on a mysql 3.x system which unfortunately won't be upgraded to something more recent! So much of it already works, I can enable, create album, import (and mass) images, view my albums, but I get an error when trying to view an individual pic within an album.

user warning: You have an error in your SQL syntax near '(5555555555 + acidfree.weight, CHAR), 10, '0'), LPAD(CONVERT(9999999999 - acidfr' at line 1 query: SELECT COUNT(nid) FROM node n INNER JOIN acidfree ON acidfree.aid = n.nid INNER JOIN acidfree_hierarchy ON child = aid WHERE class <> 'album' AND parent=57 AND CONCAT(LPAD(CONVERT(5555555555 + acidfree.weight, CHAR), 10, '0'), LPAD(CONVERT(9999999999 - acidfree.aid, CHAR), 10, '0')) < '55555555559999999922' in database.mysql.inc on line 124.

I'm no expert but I believe this is because mySQL 3 doesn't have the CONVERT function like that (or the CHAR datatype?).

It's used within function _acidfree_filter_clause in acidfree.module. As I'm not so clever, even with the comments I can't quite envisage what that function is supposed to do, but for anyone a bit brighter, maybe there could be a (slightly-inefficient??) workaround for people stuck with mysql 3 devised? I really don't know mySQL well enough to say!

Hi again,

Actually regarding my above post, I found that replacing all instances of CONVERT with SUBSTRING means that the basic functionality _appears_ to work in MySQL 3.x
e.g.

change
$filter[] = "LPAD(CONVERT(5555555555 + {$acidfree_table}.weight, CHAR), 10, '0')";
to
$filter[] = "LPAD(SUBSTRING(5555555555 + {$acidfree_table}.weight, 1), 10, '0')";

My reasoning was that CONVERT changed the variable to a CHAR, so applying a string function like SUBSTRING to it also might...?

You can now look at pics without error, and weighting still works, as does adding/deleting pics.

However as I didn't really understand the purpose of the function and I am a mysql NOVICE I am far from convinced I haven't broken plenty else!!

Comments appreciated :-)

shouchen, that is the problem. The workaround I had in mind was a query that loaded the nodes and then sorted them using php.

Patches are accepted. I really don't have a lot of time to work on this right now (nor do I have a mysql 3.x server handy) so if someone else wants to go get this one, that would be great. I have an old email from Robb Canfield (who wrote the fancy CONVERT query) that contains an old version of acidfree. From that, I stripped out the following function:

<?php
function _acidfree_get_offset_in_parent(&$parent, &$node) {
    $query = "SELECT nid FROM {node} n ".
        "INNER JOIN {acidfree} ON {acidfree}.aid = n.nid ".
        "INNER JOIN {acidfree_hierarchy} ON child = aid ".
        "WHERE class <> 'album' AND parent=%d ".
         _acidfree_content_sort_order();

    $query = db_rewrite_sql($query);
    $res = db_query($query, $parent->nid);
    if (db_num_rows($res) == 0)
        return 0;

    // A loop is pretty much the only way to reliable locate the node in a set if the sort order is anything
    // other than a single unique column for mysql 3.x. This should be pretty quick for but 100's of rows.
    $count = 0;
    while ($row = db_fetch_object($res)) {
      if ($row->nid == $node->nid) {
        break;
      }
      $count++;
    }

    return $count;
}
?>

Try replacing this same function in acidfree.module with this version and see what it does. Check to make sure the pictures are sorted in the right order (with different weights, etc) and make sure that when you click the next and previous links, it goes to the right spot. And make sure that when you click on a thumbnail (especially from the random or recent blocks) it has the pager in the right spot.

Hi,

Thanks all, I just tried replacing the function as vhmauery said, from a fresh CVS copy. If you just change the reference in it from

_acidfree_content_sort_order();

in it

to

_acidfree_content_sort_clause();

then as far as I can tell everything works great!

I don't really use the extra blocks but after a quick check everything looked ok in the random, recent and favourites. And it works quick enough on a shared server but admittedly my gallery is very small at present.

And I would trust that patch far over my vague attempt :-)

Cheers,

Adam

It's dot work for me. I get this error (its diffrent 'on lines' e every time). I run MySQL 3.23.56

Fatal error: Maximum execution time of 30 seconds exceeded in /export/vol1/unix/p/perra/public_html/arlov/includes/common.inc on line 381

For all of you still using mysql 3.X, try the latest version of Acidfree 4.7. I just made some changes to make it compatible with postgresql and I think the changes may have fixed it for mysql 3.x as well. Take a shot and see. Plus it is always good to be running the latest stuff anyway, right?

Never mind if it doesn't run with mysql 3:

http://www.mysql.com/company/legal/mysql_lifecycle_policy.pdf

I agree that it doesn't matter for mysql 3.x. The changes I made were for postgres. But the CONVERT function was removed from the query, which I think was what was incompatible with mysql 3.x. And Drupal 4.7 still supports mysql 3.x (even though later versions will not), so it is nice for those users, and there are some, that use mysql 3.x. However, I have no way of testing it and I am not going out of my way to anyway. I figured I could dig this up and let people know that they can test out the latest stuff though.