Text only | Text with Images

coppermine-gallery.com/forum

Support => cpg1.4.x Support => Older/other versions => cpg1.4 install => Topic started by: renczus on September 28, 2006, 08:01:44 PM

Title: MySQL 4.1 problem: FULLTEXT index
Post by: renczus on September 28, 2006, 08:01:44 PM
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.
Title: Re: MySQL 4.1 problem: FULLTEXT index
Post by: Nibbler on 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.
Title: Re: MySQL 4.1 problem: FULLTEXT index
Post by: renczus on September 28, 2006, 10:13:38 PM
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?
Title: Re: MySQL 4.1 problem: FULLTEXT index
Post by: Nibbler on September 28, 2006, 10:48:12 PM
Try it without. It may affect performance of the search function.
Title: Re: MySQL 4.1 problem: FULLTEXT index
Post by: renczus on 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''
Title: Re: MySQL 4.1 problem: FULLTEXT index
Post by: renczus on October 02, 2006, 08:52:57 AM
please DO help me  :-[
Title: Re: MySQL 4.1 problem: FULLTEXT index
Post by: Nibbler on 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';
Title: Re: MySQL 4.1 problem: FULLTEXT index
Post by: renczus on 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
Text only | Text with Images