MySQL 4.1 problem: FULLTEXT index MySQL 4.1 problem: FULLTEXT index
 

News:

cpg1.5.48 Security release - upgrade mandatory!
The Coppermine development team is releasing a security update for Coppermine in order to counter a recently discovered vulnerability. It is important that all users who run version cpg1.5.46 or older update to this latest version as soon as possible.
[more]

Main Menu

MySQL 4.1 problem: FULLTEXT index

Started by renczus, September 28, 2006, 08:01:44 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Print
Go Down Pages1
User actions

renczus

September 28, 2006, 08:01:44 PM Last Edit: October 02, 2006, 10:07:32 PM by Nibbler
QuoteThe following errors were encountered and need to be corrected first:

mySQL Error: Column 'title' cannot be part of FULLTEXT index on query ' CREATE TABLE cpg148_pictures ( pid int(11) NOT NULL auto_increment, aid int(11) NOT NULL default '0', filepath varchar(255) NOT NULL default '', filename varchar(255) NOT NULL default '', filesize int(11) NOT NULL default '0', total_filesize int(11) NOT NULL default '0', pwidth smallint(6) NOT NULL default '0', pheight smallint(6) NOT NULL default '0', hits int(10) NOT NULL default '0', mtime datetime NOT NULL default '0000-00-00 00:00:00' , ctime int(11) NOT NULL default '0', owner_id int(11) NOT NULL default '0', owner_name varchar(40) NOT NULL default '', pic_rating int(11) NOT NULL default '0', votes int(11) NOT NULL default '0', title varchar(255) NOT NULL default '', caption text NOT NULL, keywords varchar(255) NOT NULL default '', approved enum('YES','NO') NOT NULL default 'NO', galleryicon int(11) NOT NULL default '0', user1 varchar(255) NOT NULL default '', user2 varchar(255) NOT NULL default '', user3 varchar(255) NOT NULL default '', user4 varchar(255) NOT NULL default '', url_prefix tinyint(4) NOT NULL default '0', pic_raw_ip tinytext, pic_hdr_ip tinytext, lasthit_ip tinytext, PRIMARY KEY (pid), KEY owner_id (owner_id), KEY pic_hits (hits), KEY pic_rate (pic_rating), KEY aid_approved (aid,approved), KEY pic_aid (aid), position INT(11) NOT NULL default '0', FULLTEXT KEY search (title,caption,keywords,filename) ) TYPE=MyISAM'

what to do?

mysql ver: 4.1.10a
charset: latin-2

both 1.4.8 and 1.4.9 does the same problem

on mysql 4.0.21 (other server) everything's ok.

Nibbler

#1
September 28, 2006, 09:01:37 PM
Modify sql/schema.sql

Code Select
FULLTEXT KEY search (title,caption,keywords,filename)

change to

Code Select
FULLTEXT KEY search (caption,keywords,filename)

Drop the tables and run the install again.

renczus

#2
September 28, 2006, 10:13:38 PM Last Edit: September 28, 2006, 10:24:57 PM by renczus
QuoteThe following errors were encountered and need to be corrected first:

mySQL Error: Column 'caption' cannot be part of FULLTEXT index on query ' CREATE TABLE cpg149_pictures ( pid int(11) NOT NULL auto_increment, aid int(11) NOT NULL default '0', filepath varchar(255) NOT NULL default '', filename varchar(255) NOT NULL default '', filesize int(11) NOT NULL default '0', total_filesize int(11) NOT NULL default '0', pwidth smallint(6) NOT NULL default '0', pheight smallint(6) NOT NULL default '0', hits int(10) NOT NULL default '0', mtime datetime NOT NULL default '0000-00-00 00:00:00' , ctime int(11) NOT NULL default '0', owner_id int(11) NOT NULL default '0', owner_name varchar(40) NOT NULL default '', pic_rating int(11) NOT NULL default '0', votes int(11) NOT NULL default '0', title varchar(255) NOT NULL default '', caption text NOT NULL, keywords varchar(255) NOT NULL default '', approved enum('YES','NO') NOT NULL default 'NO', galleryicon int(11) NOT NULL default '0', user1 varchar(255) NOT NULL default '', user2 varchar(255) NOT NULL default '', user3 varchar(255) NOT NULL default '', user4 varchar(255) NOT NULL default '', url_prefix tinyint(4) NOT NULL default '0', pic_raw_ip tinytext, pic_hdr_ip tinytext, lasthit_ip tinytext, PRIMARY KEY (pid), KEY owner_id (owner_id), KEY pic_hits (hits), KEY pic_rate (pic_rating), KEY aid_approved (aid,approved), KEY pic_aid (aid), position INT(11) NOT NULL default '0', FULLTEXT KEY search (caption,keywords,filename) ) TYPE=MyISAM COMMENT='Used to store data about individual pics''

nice try, but that's not the point (not only that)

Is it necessary to use fulltext index?

Nibbler

#3
September 28, 2006, 10:48:12 PM
Try it without. It may affect performance of the search function.

renczus

#4
September 28, 2006, 10:54:44 PM
when I delete whote line it returns sth like that:

QuotemySQL Error: 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 ') TYPE=MyISAM COMMENT='Used to store data about individual pics' on query ' CREATE TABLE cpg149_pictures ( pid int(11) NOT NULL auto_increment, aid int(11) NOT NULL default '0', filepath varchar(255) NOT NULL default '', filename varchar(255) NOT NULL default '', filesize int(11) NOT NULL default '0', total_filesize int(11) NOT NULL default '0', pwidth smallint(6) NOT NULL default '0', pheight smallint(6) NOT NULL default '0', hits int(10) NOT NULL default '0', mtime datetime NOT NULL default '0000-00-00 00:00:00' , ctime int(11) NOT NULL default '0', owner_id int(11) NOT NULL default '0', owner_name varchar(40) NOT NULL default '', pic_rating int(11) NOT NULL default '0', votes int(11) NOT NULL default '0', title varchar(255) NOT NULL default '', caption text NOT NULL, keywords varchar(255) NOT NULL default '', approved enum('YES','NO') NOT NULL default 'NO', galleryicon int(11) NOT NULL default '0', user1 varchar(255) NOT NULL default '', user2 varchar(255) NOT NULL default '', user3 varchar(255) NOT NULL default '', user4 varchar(255) NOT NULL default '', url_prefix tinyint(4) NOT NULL default '0', pic_raw_ip tinytext, pic_hdr_ip tinytext, lasthit_ip tinytext, PRIMARY KEY (pid), KEY owner_id (owner_id), KEY pic_hits (hits), KEY pic_rate (pic_rating), KEY aid_approved (aid,approved), KEY pic_aid (aid), position INT(11) NOT NULL default '0', ) TYPE=MyISAM COMMENT='Used to store data about individual pics''

and when I try to delete only 'FULLTEXT' there is sth like that:

QuotemySQL Error: BLOB/TEXT column 'caption' used in key specification without a key length on query ' CREATE TABLE cpg149_pictures ( pid int(11) NOT NULL auto_increment, aid int(11) NOT NULL default '0', filepath varchar(255) NOT NULL default '', filename varchar(255) NOT NULL default '', filesize int(11) NOT NULL default '0', total_filesize int(11) NOT NULL default '0', pwidth smallint(6) NOT NULL default '0', pheight smallint(6) NOT NULL default '0', hits int(10) NOT NULL default '0', mtime datetime NOT NULL default '0000-00-00 00:00:00' , ctime int(11) NOT NULL default '0', owner_id int(11) NOT NULL default '0', owner_name varchar(40) NOT NULL default '', pic_rating int(11) NOT NULL default '0', votes int(11) NOT NULL default '0', title varchar(255) NOT NULL default '', caption text NOT NULL, keywords varchar(255) NOT NULL default '', approved enum('YES','NO') NOT NULL default 'NO', galleryicon int(11) NOT NULL default '0', user1 varchar(255) NOT NULL default '', user2 varchar(255) NOT NULL default '', user3 varchar(255) NOT NULL default '', user4 varchar(255) NOT NULL default '', url_prefix tinyint(4) NOT NULL default '0', pic_raw_ip tinytext, pic_hdr_ip tinytext, lasthit_ip tinytext, PRIMARY KEY (pid), KEY owner_id (owner_id), KEY pic_hits (hits), KEY pic_rate (pic_rating), KEY aid_approved (aid,approved), KEY pic_aid (aid), position INT(11) NOT NULL default '0', KEY search (title,caption,keywords,filename) ) TYPE=MyISAM COMMENT='Used to store data about individual pics''

renczus

#5
October 02, 2006, 08:52:57 AM
please DO help me  :-[

Nibbler

#6
October 02, 2006, 09:14:44 PM
Code Select

CREATE TABLE CPG_pictures (
  pid int(11) NOT NULL auto_increment,
  aid int(11) NOT NULL default '0',
  filepath varchar(255) NOT NULL default '',
  filename varchar(255) NOT NULL default '',
  filesize int(11) NOT NULL default '0',
  total_filesize int(11) NOT NULL default '0',
  pwidth smallint(6) NOT NULL default '0',
  pheight smallint(6) NOT NULL default '0',
  hits int(10) NOT NULL default '0',
  mtime datetime NOT NULL default '0000-00-00 00:00:00' ,
  ctime int(11) NOT NULL default '0',
  owner_id int(11) NOT NULL default '0',
  owner_name varchar(40) NOT NULL default '',
  pic_rating int(11) NOT NULL default '0',
  votes int(11) NOT NULL default '0',
  title varchar(255) NOT NULL default '',
  caption text NOT NULL,
  keywords varchar(255) NOT NULL default '',
  approved enum('YES','NO') NOT NULL default 'NO',
  galleryicon int(11) NOT NULL default '0',
  user1 varchar(255) NOT NULL default '',
  user2 varchar(255) NOT NULL default '',
  user3 varchar(255) NOT NULL default '',
  user4 varchar(255) NOT NULL default '',
  url_prefix tinyint(4) NOT NULL default '0',
#  randpos int(11) NOT NULL default '0',
  pic_raw_ip tinytext,
  pic_hdr_ip tinytext,
  lasthit_ip tinytext,
  PRIMARY KEY  (pid),
  KEY owner_id (owner_id),
  KEY pic_hits (hits),
  KEY pic_rate (pic_rating),
  KEY aid_approved (aid,approved),
#  KEY randpos (randpos),
  KEY pic_aid (aid),
  position INT(11) NOT NULL default '0'
) TYPE=MyISAM COMMENT='Used to store data about individual pics';

renczus

#7
October 02, 2006, 09:40:10 PM
many thanks - it works
it should be replaced in next release if it remains full-functioned   :D

As for me - solved
Print
Go Up Pages1
User actions