Welcome to Geeklog, Anonymous Saturday, January 11 2025 @ 10:57 am EST
Geeklog Forums
1.4.0b1 search SQL error
Status: offline
suvi
Forum User
Chatty
Registered: 11/20/05
Posts: 44
Location:Zurich
Good morning,
I tried the search functionality and I get:
An SQL error has occurred. Please see error.log for details.
From the log:
11/26/05 13:27:35 - 1054: Unknown column 'gl_comments.sid' in 'on clause'. SQL in question: SELECT gl_users.username,gl_users.fullname,gl_stories.sid,gl_comments.title,comment,pid,cid,gl_comments.uid,gl_comments.sid AS qid,type as comment_type,UNIX_TIMESTAMP(gl_comments.date) as day,'comment' as type FROM gl_comments,gl_users LEFT JOIN gl_stories ON ((gl_stories.sid = gl_comments.sid) AND (gl_stories.perm_anon >= 2) AND (tid IN ('General','GeekLog'))) WHERE gl_users.uid = gl_comments.uid AND (gl_stories.draft_flag = 0) AND (gl_stories.date <= NOW()) AND (comment LIKE '%zurich%' OR gl_comments.title LIKE '%zurich%') AND ((gl_stories.perm_anon IS NOT NULL)) ORDER BY gl_comments.date DESC LIMIT 0,10
11/26/05 13:34:40 - 1054: Unknown column 'gl_comments.sid' in 'on clause'. SQL in question: SELECT gl_users.username,gl_users.fullname,gl_stories.sid,gl_comments.title,comment,pid,cid,gl_comments.uid,gl_comments.sid AS qid,type as comment_type,UNIX_TIMESTAMP(gl_comments.date) as day,'comment' as type FROM gl_comments,gl_users LEFT JOIN gl_stories ON ((gl_stories.sid = gl_comments.sid)) WHERE gl_users.uid = gl_comments.uid AND (gl_stories.draft_flag = 0) AND (gl_stories.date <= NOW()) AND (comment LIKE '%test%' OR gl_comments.title LIKE '%test%') AND ((gl_stories.owner_id IS NOT NULL AND gl_stories.perm_owner IS NOT NULL) OR (gl_stories.group_id IS NOT NULL AND gl_stories.perm_group IS NOT NULL) OR (gl_stories.perm_members IS NOT NULL)) ORDER BY gl_comments.date DESC LIMIT 0,10
how can this be fixed?
I tried the search functionality and I get:
An SQL error has occurred. Please see error.log for details.
From the log:
Text Formatted Code
11/26/05 13:27:35 - 1054: Unknown column 'gl_comments.sid' in 'on clause'. SQL in question: SELECT gl_users.username,gl_users.fullname,gl_stories.sid,gl_comments.title,comment,pid,cid,gl_comments.uid,gl_comments.sid AS qid,type as comment_type,UNIX_TIMESTAMP(gl_comments.date) as day,'comment' as type FROM gl_comments,gl_users LEFT JOIN gl_stories ON ((gl_stories.sid = gl_comments.sid) AND (gl_stories.perm_anon >= 2) AND (tid IN ('General','GeekLog'))) WHERE gl_users.uid = gl_comments.uid AND (gl_stories.draft_flag = 0) AND (gl_stories.date <= NOW()) AND (comment LIKE '%zurich%' OR gl_comments.title LIKE '%zurich%') AND ((gl_stories.perm_anon IS NOT NULL)) ORDER BY gl_comments.date DESC LIMIT 0,10
11/26/05 13:34:40 - 1054: Unknown column 'gl_comments.sid' in 'on clause'. SQL in question: SELECT gl_users.username,gl_users.fullname,gl_stories.sid,gl_comments.title,comment,pid,cid,gl_comments.uid,gl_comments.sid AS qid,type as comment_type,UNIX_TIMESTAMP(gl_comments.date) as day,'comment' as type FROM gl_comments,gl_users LEFT JOIN gl_stories ON ((gl_stories.sid = gl_comments.sid)) WHERE gl_users.uid = gl_comments.uid AND (gl_stories.draft_flag = 0) AND (gl_stories.date <= NOW()) AND (comment LIKE '%test%' OR gl_comments.title LIKE '%test%') AND ((gl_stories.owner_id IS NOT NULL AND gl_stories.perm_owner IS NOT NULL) OR (gl_stories.group_id IS NOT NULL AND gl_stories.perm_group IS NOT NULL) OR (gl_stories.perm_members IS NOT NULL)) ORDER BY gl_comments.date DESC LIMIT 0,10
how can this be fixed?
16
10
Quote
Status: offline
suvi
Forum User
Chatty
Registered: 11/20/05
Posts: 44
Location:Zurich
I am using MySQL 5.0.15
should it be calles gl_comment.cid ? What is this field for?
this is the faulty table:
--
-- Table structure for table `gl_comments`
--
CREATE TABLE `gl_comments` (
`cid` int(10) unsigned NOT NULL auto_increment,
`type` varchar(30) NOT NULL default 'article',
`sid` varchar(40) NOT NULL default '',
`date` datetime default NULL,
`title` varchar(128) default NULL,
`comment` text,
`score` tinyint(4) NOT NULL default '0',
`reason` tinyint(4) NOT NULL default '0',
`pid` int(10) unsigned NOT NULL default '0',
`lft` mediumint(10) unsigned NOT NULL default '0',
`rht` mediumint(10) unsigned NOT NULL default '0',
`indent` mediumint(10) unsigned NOT NULL default '0',
`uid` mediumint(8) NOT NULL default '1',
`ipaddress` varchar(15) NOT NULL default '',
PRIMARY KEY (`cid`),
KEY `comments_sid` (`sid`),
KEY `comments_uid` (`uid`),
KEY `comments_lft` (`lft`),
KEY `comments_rht` (`rht`),
KEY `comments_date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;
should it be calles gl_comment.cid ? What is this field for?
this is the faulty table:
Text Formatted Code
--
-- Table structure for table `gl_comments`
--
CREATE TABLE `gl_comments` (
`cid` int(10) unsigned NOT NULL auto_increment,
`type` varchar(30) NOT NULL default 'article',
`sid` varchar(40) NOT NULL default '',
`date` datetime default NULL,
`title` varchar(128) default NULL,
`comment` text,
`score` tinyint(4) NOT NULL default '0',
`reason` tinyint(4) NOT NULL default '0',
`pid` int(10) unsigned NOT NULL default '0',
`lft` mediumint(10) unsigned NOT NULL default '0',
`rht` mediumint(10) unsigned NOT NULL default '0',
`indent` mediumint(10) unsigned NOT NULL default '0',
`uid` mediumint(8) NOT NULL default '1',
`ipaddress` varchar(15) NOT NULL default '',
PRIMARY KEY (`cid`),
KEY `comments_sid` (`sid`),
KEY `comments_uid` (`uid`),
KEY `comments_lft` (`lft`),
KEY `comments_rht` (`rht`),
KEY `comments_date` (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=1 ;
9
12
Quote
Status: offline
Dirk
Site Admin
Admin
Registered: 01/12/02
Posts: 13073
Location:Stuttgart, Germany
I don't know yet what the problem is. The requests work just fine on MySQL 4 (e.g. here on geeklog.net), but throw up the same error you get when I run them on a copy of the geeklog.net database on MySQL 5.
bye, Dirk
bye, Dirk
11
9
Quote
Status: offline
suvi
Forum User
Chatty
Registered: 11/20/05
Posts: 44
Location:Zurich
I don't understand the problem, because there is a colum called glcomment.sid.
Here is the workaround which turn searching in comments off. Then search functionality is working for storys and events.
In
system/classes/search.class.php comment line number 1128 as
//$this->comment_results = $this->_searchComments();
howdy
Suvi
Here is the workaround which turn searching in comments off. Then search functionality is working for storys and events.
In
system/classes/search.class.php comment line number 1128 as
Text Formatted Code
//$this->comment_results = $this->_searchComments();
howdy
Suvi
11
11
Quote
Status: offline
suvi
Forum User
Chatty
Registered: 11/20/05
Posts: 44
Location:Zurich
There is also a problem with
http://localhost/geeklog-1.4.0b1/public_html/admin/moderation.php
from the error.log:
<ol>
<li>Change the default password for the Admin account.</li>
<li>Remove the install directory (you won't need it any more).</li>
</ol>','','2005-11-22 09:33:37','1','0','0','html',1,2,6,3,2,2,2)
12/02/05 12:02:50 - 1054: Unknown column 'security' in 'field list'. SQL in question: REPLACE INTO gl_stories (sid,uid,tid,title,introtext,related,date,show_topic_icon,commentcode,trackbackcode,postmode,frontpage,owner_id,group_id,perm_owner,perm_group,perm_members,perm_anon) VALUES (security-reminder,2,'GeekLog','Are you secure?','<p>This is a reminder to secure your site once you have Geeklog up and running. What you should do:</p>
http://localhost/geeklog-1.4.0b1/public_html/admin/moderation.php
from the error.log:
Text Formatted Code
<ol>
<li>Change the default password for the Admin account.</li>
<li>Remove the install directory (you won't need it any more).</li>
</ol>','','2005-11-22 09:33:37','1','0','0','html',1,2,6,3,2,2,2)
12/02/05 12:02:50 - 1054: Unknown column 'security' in 'field list'. SQL in question: REPLACE INTO gl_stories (sid,uid,tid,title,introtext,related,date,show_topic_icon,commentcode,trackbackcode,postmode,frontpage,owner_id,group_id,perm_owner,perm_group,perm_members,perm_anon) VALUES (security-reminder,2,'GeekLog','Are you secure?','<p>This is a reminder to secure your site once you have Geeklog up and running. What you should do:</p>
12
12
Quote
Status: offline
dariball
Forum User
Newbie
Registered: 12/27/05
Posts: 3
Location:Frankfurt - Hanau
hey,
also stumpled over this search issue....
the first problem is because of the new weird mysql 5 implementation concerning column/variable scopes. At some point I guess the dev team decided to drop compatibility to mysql4.
but actually the problem is really easy to solve,
on line 364 in classes/search.class.php is
$sql = " FROM {$_TABLES['comments']},{$_TABLES['users']} ";
just change it to:
$sql = " FROM {$_TABLES['users']},{$_TABLES['comments']} ";
and the search will work again,
the second problem pointed out by suvi seems not reproducible for me, with some additional information I could take a look on it.
greetz
dariball
also stumpled over this search issue....
the first problem is because of the new weird mysql 5 implementation concerning column/variable scopes. At some point I guess the dev team decided to drop compatibility to mysql4.
but actually the problem is really easy to solve,
on line 364 in classes/search.class.php is
Text Formatted Code
$sql = " FROM {$_TABLES['comments']},{$_TABLES['users']} ";
just change it to:
Text Formatted Code
$sql = " FROM {$_TABLES['users']},{$_TABLES['comments']} ";
and the search will work again,
the second problem pointed out by suvi seems not reproducible for me, with some additional information I could take a look on it.
greetz
dariball
14
12
Quote
Status: offline
Dirk
Site Admin
Admin
Registered: 01/12/02
Posts: 13073
Location:Stuttgart, Germany
Quote by dariball: but actually the problem is really easy to solve
Wow, that really works. I've been pulling my hair out over this one. What were the MySQL people thinking?
Thanks a lot!
bye, Dirk
13
12
Quote
Status: offline
dariball
Forum User
Newbie
Registered: 12/27/05
Posts: 3
Location:Frankfurt - Hanau
Quote by Dirk: What were the MySQL people thinking?
Well you'll stumble over this from time to time, if you're changing running sites from MySQL4 to 5.
Another very evil example is:
Text Formatted Code
SELECT * FROM table1 LEFT JOIN table2 ON <something>
LEFT JOIN table3 ON table1.id = table3.id
In MySQL 4 this worked like a charm, but in MySQL 5 it will show up with the same error ( table1.id does not exist ), because table1 isn't inside the scope of the second join. Knowing this, spares a lot of hair^^
greetz
dariball
13
13
Quote
All times are EST. The time is now 10:57 am.
- 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