Subject: Sort order isn't by date - panther fix

Posted on: 31/10/03 08:07pm
By: dreness

I recently migrated to a new server, and got GL up and running just fine - everything works just like it did. The only difference I have noticed is that submissions are no longer sorted from newest to oldest. The date stamp in each submission is still correct, but they are not ordered properly. Any ideas? I tried editing a story and then saving without changing anything, that didn't cause a change in the sort order. I also tried modifying the story's post time to one minute later than it was actually posted. This did cause a change in the sort order, but there seems to be no rhyme or reason as to the order it's actually using.

I also changed my user preference from Newest to Oldest and back again; this seemed to have no effect either.

Thanks, GL rocks

-Andre

Re:Sort order isn't by date - panther server users, read this!

Posted on: 01/11/03 03:30am
By: dreness

This problem has been solved by updating the mysql query that the geeklog php was issuing to get the dates for each submission. The version of mysql that ships in Mac OS X Server 10.3 does date queries a bit differently, so geeklog wasn't getting what it expected. The query was re-written:

$sql .= "ORDER BY featured asc,DATE_FORMAT(date,'%m-%d-%y %r') DESC";

from

$sql .= "ORDER BY featured DESC, date DESC";

on line 163 in index.php

Hope this helps others who upgrade to panther serever... I'm guessing everybody with panther will have this problem.

Just for the record:

root# mysql --version
mysql Ver 12.21 Distrib 4.0.14, for apple-darwin7.0 (powerpc)

-Andre

Re:Sort order isn't by date

Posted on: 01/11/03 11:03am
By: Anonymous

My articles after the little update in the index.php in sorting dates are still not fixed all my stories are out of sequence please help cuz I am running out of ideas! I just upgraded my server to 10.3 panther server. thanx

sam

Re:Sort order isn't by date

Posted on: 01/11/03 02:44pm
By: dreness

[QUOTE BY= sam] My articles after the little update in the index.php in sorting dates are still not fixed all my stories are out of sequence please help cuz I am running out of ideas! I just upgraded my server to 10.3 panther server. thanx

sam[/QUOTE]

It turns out that the index.php modification isn't the only one needed. After applying that change, I noticed that stories were in order, but comments to stories are not.

With the help of a good friend who is a professional php ninja, it was fixed in about 10 minutes, but in this case, it involves changes in many files in public_html. I'm not even sure I know how to report back to the developers what was changed... I'm thinking maybe recursively diff'ing the original and modified public_html directories.

In any case, my own expertise in php / mysql is quite limited, so the best I can do is offer up the revised public_html directory with all the changes. I don't even know what files are changed, so it's up to you to merge your existing site with this. Theoretically, since all the actual content is in the db, the only things you'll have to migrate over are any custom images or changes to templates.

To try it out, download the following to the same directory as your geeklog's public_html folder.

http://www.dreness.com/phtml_b2.tar.gz

Rename the existing public_html to public_html.bk and then gunzip / untar this one. It should be in the same place with the same name as your current public_html directory. Also be sure to recursively chown it to the www user: sudo chown -R www /path/to/public_html

Note: I'm about to rebuild my server and move a bunch of websites around. This link will be valid for a while, but if it goes dead and somebody needs it, drop me an email.

Re:Sort order isn't by date - panther fix

Posted on: 03/01/04 12:51pm
By: heidialyssa

You have to manipulate it a little in the index.php -

$sql .= "ORDER BY featured asc, DATE_FORMAT(date,'%y-%m-%d %r') DESC";

Hope this helps!

Sort order of Story list

Posted on: 03/01/04 03:49pm
By: heidialyssa

Although the hack fixed the story order on the front page, the Story list is still wacky. Does anyone know where the story list calls from and what a possible solution would be to put them in date ascending order?

Re:Sort order isn't by date - panther fix

Posted on: 05/01/04 03:43pm
By: das

Actually, this fix doesn't completely work either, because though all my stories from 2003 are sorted correctly, the stories from 2004 go to the end! Hopefully we'll have a real fix for this soon...

Re:Sort order isn't by date - panther fix

Posted on: 05/01/04 04:05pm
By: Dirk

No such problems here, running MySQL 4.0.17 on MacOS X 10.3.2 (consumer version).

In the light of other problems posted here, I wonder if the MySQL that Apple ships has a problem.

Has anyone tried upgrading to MySQL 4.0.17 (available from mysql.com)?

bye, Dirk

Re:Sort order isn't by date - panther fix

Posted on: 05/01/04 05:10pm
By: das

The problem is that upgrading core OS components could (and usually does) break Apple's OS upgrades when they come out...since MySQL is an integrated part of OS X Server, it is indescribably easier to keep it as-is, or it causes a lot of headaches down the road...so OS X Server 10.3.2 users are really stuck with the Apple-supplied MySQL 4.0.16 if they don't want to upset the apple cart.

Is this really MySQL, and not php, causing the issue, though?

(Also, heidialyssa's amended date statement for index.php does indeed fix the story sorting issue. Of course, that doesn't address any of the other date issues, however.)

Is there any way for me to look at something that would let us know how the date is being reported, that might give a hint as to the issue?

Re:Sort order isn't by date - panther fix

Posted on: 05/01/04 05:39pm
By: das

Ok, I tried installing MySQL 4.0.17-standard on Mac OS X Server 10.3.2. I shut down the old MySQL, installed 4.0.17, and moved the entire /var/mysql to the "new" MySQL's data dir (/usr/local/mysql/data), and then started MySQL 4.0.17. All of my geeklog sites had a bunch of errors (strange numbers for forum post counts, no articles displayed on front page, etc) so I simply reverted back to 4.0.16 for now...

And Dirk, that link you posted actually wasn't Apple's MySQL, it was MySQL installed via fink, on 10.2.8.

Also, all of my other MySQL/php based applications that rely on dates work fine.

Re:Sort order isn't by date - panther fix

Posted on: 05/01/04 05:52pm
By: dreness

[QUOTE BY= das]
Is there any way for me to look at something that would let us know how the date is being reported, that might give a hint as to the issue?[/QUOTE]

First of all, I absolutely agree with sticking with the apple-supplied mysql, substandard as it may be.

As far as fixing the problem, the way to do it (I think) is to find the sql queries in the geeklog code and then run them manually inside a mysql shell. Note that I am not a mysql hacker at all, but I have a friend who's into it and when he initially fixed this for me (I have since reinstalled geeklog (do to drunken rm -R'ing) and lost the fixes), that's how he did it... found the queruies, ran them manually, observed the results, and then changed the queries so that they returned the expected results. Part of the problem seems to be that there is more than one query that is date sensitive, so the scope of the necessary changes is not limited to a single file.

An example mysql query (nabbed from the transcript of my friend troubleshooting this problem):

mysql> select title, date, featured from gl_stories order by featured,date_format(date,'%m-%d-%y %r') desc;

Re:Sort order isn't by date - panther fix

Posted on: 06/01/04 03:47pm
By: das

Ok, so now I've gone through and done some more experimenting. I dumped all my geeklog databases from the Apple-supplied MySQL 4.0.16 on Mac OS X Server 10.3.2, and imported them into the mysql.com-supplied MySQL 4.0.17.

The same problem with date sorting remains.

So this isn't an Apple-supplied MySQL issue; it's something else. Dirk, I'd be happy to provide an OS X Server 10.3.2 box to you to test on, if you'd like.

I also have another problem, that only started on Panther Server. In the "What's new" section, under "New comments", I'm getting entries like this:

COMMENTS last 48 hrs
Xserve RAID with ...
[+72057594037927936]

Isn't *anyone* else running geeklog on Panther Server, and encountering these issues??

Re:Sort order isn't by date - panther fix

Posted on: 06/01/04 10:09pm
By: dreness

I'm not seeing the strange "what's new" output for recent comments. Feel free to mess around @ log.dreness.com, nobody but me uses it. Anonymous comments are allowed. (please excuse the message sorting problems

Re:Sort order isn't by date - panther fix

Posted on: 07/01/04 12:24am
By: das

Ok, little bit more info. My "What's new" comment number are all multiplied by 2^56 (72057594037927936). So if there's supposed to be one new comment, it says +72057594037927936. If there are 2 new comments, it says +144115188075855872, which is 2*(2^56).

So what the heck would be causing something like this???

(Of course, I'm still looking for a solution for the date sorting issue, which I have confirmed is NOT related to the Apple-supplied MySQL on OS X Server, since it does exactly the same thing with the MySQL 4.0.17 directly from mysql.com)

Re:Sort order isn't by date - panther fix

Posted on: 07/01/04 12:52am
By: dreness

erm... how the hell do you recognize a power of 2 that high?

Re:Sort order isn't by date - panther fix

Posted on: 07/01/04 01:21am
By: das

Well, I didn't do it in my head. :-)

Seriously though:

I believe that there are serious date/query problems with geeklog running on Mac OS X Server 10.3.x that are NOT resolved by using the mysql.com-supplied MySQL 4.0.17, as suggested by Dirk. These problems really need to be resolved, but unfortunately, I'm not skilled enough with php and/or MySQL to begin to attempt it.

I would, however, offer the geeklog project the use of a Mac OS X Server 10.3.2 system to track down and troubleshoot these issues. I'm sure someone versed with php, MySQL, and geeklog would be able to see what the issue is in very short order, and likely kill many of the issues with one stone. I can be reached at das@doit.wisc.edu.

[Edit: it appears that MySQL 4.0.17 DOES solve these problems...it didn't appear to at first, likely due either to Safari's agressive caching or me screwing something up. See below for more info.]

Re:Sort order isn't by date - panther fix

Posted on: 07/01/04 09:37am
By: dewdoo

Every since I updated to 10.3 Server, I had MYSQL problems with Geeklog. Everything to the Date Entry of my story being out of sequence and the funky number thing in Comments as you mentioned. I loved 10.2 Server but I coulnt get sendmail to work...so when panther came out it fixed my sendmail problem w/postfix, but my stories was all out of order. What I have done is used phpmyadmin and back up my data. Then printed out the the sql.dump. Highlighted all my stories and put them in order. Then I preceded to manually cut and paste every story to Geeklog. It took me a whole entire day to finish....now everything is ok....sorta....

I notice when I add a new story and I look at my list of stories they are out of order. The main page is okay sorting the stories...but it is a hassle on the new story page finging my story. I now put a date on my stories so when I upgrade the nextime I now what order my stories are in. If it sounds confusing...I am sorry...I am typing this out b4 I go to work.

LMK if this works for you.....I am sorry I didn't answer anything earlier....just been busy. I am running G3 B/W 450 320 mb RAM, 10.3.1 MAC OS Server.

Re:Sort order isn't by date - panther fix

Posted on: 07/01/04 02:54pm
By: das

Ok, more info.

I installed Mac OS X Server 10.3.2 clean on a staging machine, just to eliminate any variables. I then installed the mysql.com supplied MySQL 4.0.17. I dumped my geeklog databases on the non-working machine, and imported them all into 4.0.17: the problem is indeed fixed. I then installed 4.0.17 on my production machine and moved the databases back over to my primary machine...it appears all of the sorting problems and weirdness are fixed.

A little more info: apparently others have noticed this as well:

http://bugs.mysql.com/bug.php?id=2158
http://discussions.info.apple.com/WebX?50@95.ccq1ao3urzy.1@.599e3f3f

Note that this is ONLY on Mac OS X Server 10.3.x (Panther Server), with the Apple-supplied MySQL 4.0.1x.

Sort order isn't by date - panther fix

Posted on: 30/01/04 04:07pm
By: heidialyssa

Has anyone discovered a fix for this?

Sort order isn't by date - panther fix

Posted on: 30/01/04 04:15pm
By: Dirk

[QUOTE BY= heidialyssa] Has anyone discovered a fix for this?[/QUOTE]
Well, apparently you have 2 options:
  1. Install the MySQL binary from mysql.com
  2. Yell at Apple to fix it

bye, Dirk

Sort order isn't by date - panther fix

Posted on: 02/02/04 02:02am
By: dreness

[QUOTE BY= Dirk] [QUOTE BY= heidialyssa] Has anyone discovered a fix for this?[/QUOTE]
Well, apparently you have 2 options:
  1. Install the MySQL binary from mysql.com
  2. Yell at Apple to fix it

bye, Dirk[/QUOTE]

I installed the binary from mysql.com, and it does appear to sort messages properly, with one BIG caveat: all stories seemed to have lost their date stamp. In my case, not a big deal since I don't have a lot; I just manually re-dated them all, and now they sort properly.

The basic process for installing the binary went about like this:

1) Download the binary release and unzip / untar it, then move the resulting directory to /usr/local.

2) Follow the installation steps in the INSTALL-BINARY file for creating a symbolic link to /usr/local/mysql from the mysql directory, e.g.
ln -s /usr/local/mysql-standard-4.0.17-apple-darwin6.8-powerpc 
/usr/local/mysql
and also follow the 'chown' steps.

3) Rename /usr/local/mysql/data to data.new or something, then symlink from /var/mysql (which is where the existing databases live) to /usr/local/mysql/data as follows:
ln -s /var/mysql /usr/local/mysql/data


That should be about it. In my case, I had to also revert to a 'stock' geeklog distribution, free of the various hacks I'd attempted to fix this sorting problem, so I just took the opportunity to upgrade to the latest version.

Note that if you're upgrading, you can skip the creation of the mysql user / group, and also skip the install_db (since you'll be using your existing one).

Hope this helps,
-Andre

Sort order isn't by date - panther fix

Posted on: 18/03/04 01:07pm
By: das

This issue is now fixed in the Mac OS X Server 10.3.3 update:

http://docs.info.apple.com/article.html?artnum=107851#sql

Mac OS X Server 10.3.3 includes a new binary of MySQL 4.0.18 that fixes the issue that was present in the included MySQL in Mac OS X Server 10.3, 10.3.1, and 10.3.2.

Sort order isn't by date - panther fix

Posted on: 18/03/04 07:56pm
By: Anonymous

[QUOTE BY= das] This issue is now fixed in the Mac OS X Server 10.3.3 update:

http://docs.info.apple.com/article.html?artnum=107851#sql
[/QUOTE]

Though it's mentioned in the document linked above, since some people never read before installing, I will add this:

** Be sure to dump your mysql db before the update, and then load it after **

Geeklog - Forum
https://www.geeklog.net/forum/viewtopic.php?showtopic=26365