Welcome to Geeklog Saturday, October 16 2021 @ 11:34 pm EDT

Geeklog Forums

Site is down with sql error

Page navigation


Status: offline

dnowery

Forum User
Newbie
Registered: 19/08/07
Posts: 8
Location:Cincinnati, OH
grumpy
Hello, I have a soccer site built upon Geeklog that is suddenly down following some changes (below). Error Message: An SQL error has occurred. Please see error.log for details. I can't even login... this is all I get. URL of site: http://www.webst.us/geeklog/public_html/ Here are the changes I made: Ran Spamx plugin to remove spam from comments and trackbacks Added Spamx keywords and reran again Deleted users that were obviously spammers Updated the config.php file as follows: - Set trackback_enabled to 'false" - Set pingback_enabled to 'false' - Set ping_enabled to 'false' - Set trackback_code to -1 - Added 3 event types to the event_types array - Set usersubmission to 1 // 1 = new users must be approved Uploaded the new config.php file Edited the site by adding changes to a static page (added a
in html source - minor) Then when I logged out it gave me the sql error message (above). I tried clearing my browser cache and cookies but this did not work. I get the same error with another identical error in the log file. I looked in the log file and found this: Sun 19 Aug 2007 03:56:18 PM EDT - 1064: 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 1. SQL in question: SELECT DISTINCT ft_name FROM gl_access,gl_features WHERE ft_id = acc_ft_id AND acc_grp_id IN () I have seen this error message mentioned in other threads on this forum but I never saw a solution or at least one that I could understand. I'm not sure how to get it up and running again. Your help/advice would be much appreciated. Thanks, Dave, Frustrated Soccer Coach
Dave from Cincy
 Quote

Status: offline

suprsidr

Forum User
Full Member
Registered: 29/12/04
Posts: 555
Location:Champaign, Illinois
As a plugin developer for geeklog, I see these types of messages quite a bit as I push the geeklog envelope.
My best friend in these situations is phpMyAdmin.
This way I have access to my remote database in order to disable the offending plugin.
Try disabling each plugin one-by-one(likely starting with the one you were editing) until you are able to login again.

-s
FlashYourWeb and Your Gallery with the E2 XML Media Player for Gallery2 - http://www.flashyourweb.com
 Quote

dnowery

Anonymous
Hello suprsidr,

I wasn't editing a plugin, at least I didn't think I was. I used some plugins... the spamx which came installed. The editor that I used to edit the static page I suppose was a plugin.

My take on the error message is that my group information somehow got corrupted. I did manage to find the SQL statement causing the problem inside the group.php file.

Your suggestion is to install this utility that allows me to tweak the data inside the MySQL tables, is that correct? I noticed that I would need to have PHP 5 running for this utility to work.

Thanks,
Dave
 Quote

Status: Banned

machinari

Forum User
Full Member
Registered: 22/03/04
Posts: 1512
Do you have a database backup from which you can restore your working state?
If you do, I suggest that you use it and then begin your edits again--slowly.
 Quote

Status: Banned

machinari

Forum User
Full Member
Registered: 22/03/04
Posts: 1512
Quote by: dnowery

...Your suggestion is to install this utility that allows me to tweak the data inside the MySQL tables, is that correct? I noticed that I would need to have PHP 5 running for this utility to work.

Thanks,
Dave

You don't need phpmyadmin. Whatever database utility you used to create your geeklog database will do.
 Quote

Status: offline

suprsidr

Forum User
Full Member
Registered: 29/12/04
Posts: 555
Location:Champaign, Illinois
Your suggestion is to install this utility that allows me to tweak the data inside the MySQL tables

yes that is my suggestion. Plugins are easy to disable that way, and may be the culprit(not familiar with your precise error).

I noticed that I would need to have PHP 5 running for this utility to work.

not true at all. I'm not.and I'm using: phpMyAdmin - 2.10.3

-s


FlashYourWeb and Your Gallery with the E2 XML Media Player for Gallery2 - http://www.flashyourweb.com
 Quote

Status: offline

Laugh

Site Admin
Admin
Registered: 27/09/05
Posts: 1438
Usually when I get an error that should not be happening I will reboot the servers or at least restart the services that geeklog is using (IIS, mysql, etc...).
One of the Geeklog Core Developers.
 Quote

Status: offline

dnowery

Forum User
Newbie
Registered: 19/08/07
Posts: 8
Location:Cincinnati, OH
Hello, Thanks for your replies... Unfortunately, I don't have a database utility to use. Geeklog was installed automatically by the host (Go Daddy). When I go to the host management screen under "Database" it says that I don't even have a SQL database setup. It shows 0 of 25 available databases being used. I configured Geeklog by editing everything via my html/text editor and uploading it via FTP. Thus far, my only interaction with the database is through the GeekLog application. When I look at the config.php file section related to the database it shows an IP address for the db name. I tried entering that IP address into a browser and got a site not found message so I gather this is strictly a database server address. Since group.php is causing the problem... I wonder if I could put in some temporary code to populate that table and then comment it out later. Sorry, I was misinformed on the PHP version required for phpmyadmin... it appeared to be a requirement on their Web site. BTW, I'm sure none of you approve of Go Daddy hosting. In case you are familiar with them I am currently running their hosting configuration 1.0. Any suggestions? Regards, Dave
Dave from Cincy
 Quote

Status: Banned

machinari

Forum User
Full Member
Registered: 22/03/04
Posts: 1512
Quote by: dnowery

... It shows 0 of 25 available databases being used..

If they offer you databases in which to work, they must have a utility of some kind by which to access those databases. Perhaps you should ask godaddy about how the databases are accessed. Once you have access, disable your plugins via the plugins table, most commonly called gl_plugins. Unless your files are corrupt then I wouldn't go editing group.php or any other file (lib-security.php) where that query may be made from.
 Quote

Status: Banned

machinari

Forum User
Full Member
Registered: 22/03/04
Posts: 1512
According to godaddy, "...you can create new databases, delete databases, or make changes to existing databases."
See http://help.godaddy.com/article.php?article_id=33&topic_id=67 for more info
 Quote

Status: offline

dnowery

Forum User
Newbie
Registered: 19/08/07
Posts: 8
Location:Cincinnati, OH
Quote by: machinari

Perhaps you should ask godaddy about how the databases are accessed. Once you have access, disable your plugins via the plugins table, most commonly called gl_plugins. Unless your files are corrupt then I wouldn't go editing group.php or any other file (lib-security.php) where that query may be made from.

I think we are on the same wavelength... I sent in a trouble ticket to godaddy about an hour ago. I'll keep my fingers crossed that they can help me get access to the database. Thanks, Dave
Dave from Cincy
 Quote

Status: offline

dnowery

Forum User
Newbie
Registered: 19/08/07
Posts: 8
Location:Cincinnati, OH
Quote by: machinari

According to godaddy, "...you can create new databases, delete databases, or make changes to existing databases." See http://help.godaddy.com/article.php?article_id=33&topic_id=67 for more info

For some reason I have no database to manage using the interface they mention in this article. Confused I think its because my GeekLog was installed prior to this new interface (host manager). I'll have to wait to see what godaddy support says about it. Thanks, Dave
Dave from Cincy
 Quote

Status: offline

dnowery

Forum User
Newbie
Registered: 19/08/07
Posts: 8
Location:Cincinnati, OH
moody
Hello, Here is the response I got from GoDaddy... Thank you for contacting Online Support. I apologize for any confusion regarding this issue. Unfortunately, you are not able to access the databases associated with GeekLog. However, should you require this access, you would need to uninstall GeekLog from your hosting account and then perform an independent installation of GeekLog from their official site. I sincerely apologize for any inconvenience this may have caused. Please let us know if there is any other way we can assist you. This is pretty disheartening. I have approximately 120 messages and a whole season's worth of photos from last year. I have it all locally but it will be a major pain to upload it back into another database. There is no database backup since I was given no database tools to work with. Am I out of luck? Would this code snippet do the trick if I put it in a script file to be executed one time? insert into gl_group_assignments (ug_main_grp_id, ug_uid) values (2,1); I think this might add the anonymous user to the main group but not certain of that. I found it from another thread where someone was having a similar problem. Any suggestions? :helpme: Thanks, Dave
Dave from Cincy
 Quote

Status: offline

mevans

Forum User
Full Member
Registered: 08/02/04
Posts: 393
Location:Texas
Dave,

Since you don't have any tools available to see what is actually in the DB, here is a quick script you can save as dbDump.php in your web root, run it and it will show what is in both the group_assignment table and the groups table. Once we know this, we can see if the insert you referenced will help or not.

You will need to edit the server, dbname, user and password to match those entries from your Geeklog config.php file:

PHP Formatted Code

<?php

    $hostname = 'localhost';
    $user          = 'dbuserhere';
    $pass         = 'dbpasswdhere';
    $dbname   = 'dbnamehere';

    // connect to the database
    $db = mysql_connect($hostname,$user,$pass) or die('Cannot connect to DB server');
    // Set the database
    @mysql_select_db($dbname) or die('error selecting database');

    $sql = "SELECT * FROM gl_group_assignments";

    $result = @mysql_query($sql,$db);

    $numrows = mysql_numrows($result);

    echo 'gl_group_assignment table';
    echo '<table border="1">';
    echo '<tr><th>ug_main_grp_id</th><th>ug_uid</th><th>ug_grp_id</th></tr>';

    while ( $row = mysql_fetch_array($result, MYSQL_ASSOC ) ) {
        echo '<tr><td>' . $row['ug_main_grp_id'] . '</td>' .
                 '<td>' . $row['ug_uid'] . '</td>' .
                 '<td>' . $row['ug_grp_id'] . '</td>' .
                 '</tr>';
    }
    echo '</table>';

    $sql = "SELECT * FROM gl_groups";

    $result = @mysql_query($sql,$db);

    $numrows = mysql_numrows($result);

    echo 'gl_groups table';
    echo '<table border="1">';
    echo '<tr><th>grp_id</th><th>grp_name</th><th>grp_descr</th><th>grp_gl_core</th></tr>';

    while ( $row = mysql_fetch_array($result, MYSQL_ASSOC ) ) {
        echo '<tr><td>' . $row['grp_id'] . '</td>' .
                 '<td>' . $row['grp_name'] . '</td>' .
                 '<td>' . $row['grp_descr'] . '</td>' .
                 '<td>' . $row['grp_gl_core'] . '</td>' .
                 '</tr>';
    }
    echo '</table>';

?>
 


You'll probably need to save it locally and then FTP to your server. Then run in the browser: http://www.webst.us/geeklog/public_html/dbDump.php

Hopefully this will help a little.

Thanks!
Mark
 Quote

Status: Banned

machinari

Forum User
Full Member
Registered: 22/03/04
Posts: 1512
...and you can use a similar technique to print/download an entire database backup
 Quote

Status: offline

dnowery

Forum User
Newbie
Registered: 19/08/07
Posts: 8
Location:Cincinnati, OH
Hello, Here is what I found: It might be more easily viewed from the Web page (URL same as you suggested) Does anything jump out at you as being the culprit? Thanks very much, Dave gl_group_assignment tableug_main_grp_id ug_uid ug_grp_id 2 1 3 1 4 1 5 1 6 1 7 1 8 1 9 1 10 1 11 1 13 2 12 2 11 2 2 12 2 10 2 9 2 8 2 7 2 6 2 5 2 4 2 3 12 1 9 11 2 11 10 2 9 2 8 2 7 2 6 2 5 2 4 2 3 2 1 2 14 1 15 1 17 1 13 4 15 3 14 3 13 3 12 3 11 3 10 3 9 3 8 3 7 3 5 3 3 3 17 2 17 3 13 5 13 6 13 9 2 4 2 9 2 6 2 5 2 3 2 2 gl_groups tablegrp_id grp_name grp_descr grp_gl_core 1 Root Has full access to the site 1 2 All Users Group that a typical user is added to 1 3 Story Admin Has full access to story features 1 4 Block Admin Has full access to block features 1 5 Links Admin Has full access to links features 0 6 Topic Admin Has full access to topic features 1 7 Event Admin Has full access to event features 1 8 Polls Admin Has full access to polls features 0 9 User Admin Has full access to user features 1 10 Plugin Admin Has full access to plugin features 1 11 Group Admin Is a User Admin with access to groups, too 1 12 Mail Admin Can use Mail Utility 1 13 Logged-in Users All registered members 1 14 Static Page Admin Can administer static pages 0 15 spamx Admin Users in this group can administer the Spam-X plugin 0 16 Remote Users Users in this group can have authenticated against a remote server. 1 17 mediagallery Admin Users in this group can administer the mediagallery plugin 0
Dave from Cincy
 Quote

Status: offline

mevans

Forum User
Full Member
Registered: 08/02/04
Posts: 393
Location:Texas
Dave,

The output tells me that you are indeed missing the anonymous user group assignment. Cut / Paste this script to you site (call it fixdb.php), give it a run and see if it helps:

PHP Formatted Code

<?php

    $hostname = 'localhost';
    $user          = 'dbuserhere';
    $pass         = 'dbpasswdhere';
    $dbname   = 'dbnamehere';

    // connect to the database
    $db = mysql_connect($hostname,$user,$pass) or die('Cannot connect to DB server');
    // Set the database
    @mysql_select_db($dbname) or die('error selecting database');

    $sql = "insert into gl_group_assignments (ug_main_grp_id, ug_uid) values (2,1);"
    $result = @mysql_query($sql,$db);

    echo 'Group assignment database updated - try site again...';
?>
 


Give this a shot and hopefully that will get you going again.

Thanks!
Mark
 Quote

Status: offline

dnowery

Forum User
Newbie
Registered: 19/08/07
Posts: 8
Location:Cincinnati, OH
happy
Hello, That did the trick... I'm up and running again!!! mevans... your code saved the day. I'm totally relieved to have the site back online! Big Grin Best regards, Dave ps. For those who want to use that last code snippet be sure to move the semicolon outside the double quotes on line 14 ($sql = ...)
Dave from Cincy
 Quote

Status: offline

mevans

Forum User
Full Member
Registered: 08/02/04
Posts: 393
Location:Texas
rockin
I'm glad you are up and running again! Also, thanks for fixing my fix Smile Hopefully this will be useful to anyone else running into this type of problem.

Thanks!
Mark
 Quote

Status: offline

jmucchiello

Forum User
Full Member
Registered: 29/08/05
Posts: 985
Quote by: dnowery

Am I out of luck?

Thanks to Mark, not this time. But you should heed GoDaddy's (and our) advice and not use their automatic install for anything important. Do your own install of Geeklog with a database you create and can access properly. GoDaddy's autoinstaller for Geeklog has lots of problems this being one of them: no external database access.
 Quote

Page navigation

All times are EDT. The time is now 11:34 pm.

  • Normal Topic
  • Sticky Topic
  • Locked Topic
  • New Post
  • Sticky Topic W/ New Post
  • Locked Topic W/ New Post
  •  View Anonymous Posts
  •  Able to post
  •  Filtered HTML Allowed
  •  Censored Content