Geeklog Forums

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

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

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

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.

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!

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?

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...

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

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?

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.

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;

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??

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

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)

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

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.]

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.

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.

Has anyone discovered a fix for this?

Well, apparently you have 2 options:

1. Install the MySQL binary from mysql.com
   
2. Yell at Apple to fix it
   

bye, Dirk