Advanced search  

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]

Pages: [1]   Go Down

Author Topic: MySQL Error  (Read 10823 times)

0 Members and 1 Guest are viewing this topic.

BradInOKC

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 38
MySQL Error
« on: April 22, 2010, 09:46:58 pm »

After selecting the MySQL database for Coppermine to install to, I get the following error during installation:

Quote
MySQL error: You have an error in your SQL syntax near 'ENGINE=MyISAM COMMENT='Holds the categories where groups can create albums'' at line 11 on query ' CREATE TABLE IF NOT EXISTS `cpg15x_categorymap` ( cid int(11) NOT NULL, group_id int(11) NOT NULL, PRIMARY KEY (cid,group_id) ) ENGINE=MyISAM COMMENT='Holds the categories where groups can create albums''


I have tried deleting the database and installing again, and I've tried using a different database named something else...  I'm not really sure what to do from here? 

This is the point at which the error occurs:

Quote
Trying to insert data in the database.

 

Creating table 'cpg15x_albums'    Status:... OK
Creating table 'cpg15x_banned'    Status:... OK
Creating table 'cpg15x_bridge'    Status:... OK
Creating table 'cpg15x_categories'    Status:... OK
Creating table 'cpg15x_categorymap'    Status:... Not OK 


I'd sure appreciate any help you could offer...  I am trying to setup a photo gallery for my boss, and am absolutely clueless on how to fix this particular type of error.  Thanks for helping.

Brad
Logged

onthepike

  • Guest
Re: MySQL Error
« Reply #1 on: April 22, 2010, 10:51:53 pm »

If the installer is having issues creating one table, I'd eliminate the obvious and try another install after first downloading a fresh copy of CPG. It's possible that one or more files are corrupt in your current copy (syntax error). You could also try and manually create and populate the table, but I'd first try a fresh copy of CPG and go from there.
Logged

BradInOKC

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 38
Re: MySQL Error
« Reply #2 on: April 22, 2010, 10:54:38 pm »

OK, I'll re-download tomorrow morning and try to do it completely again from scratch.  Hope that works.  Please feel free to share any other ideas in the meantime.  Thanks for responding.

Brad
Logged

onthepike

  • Guest
Re: MySQL Error
« Reply #3 on: April 22, 2010, 11:04:06 pm »

I don't know what else to suggest, other than verifying the version of MySQL you have running there. I'm sure someone else will come along with a better approach, however.
Logged

Joachim Müller

  • Dev Team member
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 47844
  • aka "GauGau"
    • gaugau.de
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: MySQL Error
« Reply #5 on: April 23, 2010, 07:47:53 am »

Please open sql/schema.sql, find
Code: [Select]
#
# Table structure for table CPG_categorymap
#
CREATE TABLE IF NOT EXISTS `CPG_categorymap` (
  cid int(11) NOT NULL,
  group_id int(11) NOT NULL,
  PRIMARY KEY  (cid,group_id)
) ENGINE=MyISAM COMMENT='Holds the categories where groups can create albums';
# --------------------------------------------------------
and replace with
Code: [Select]
#
# Table structure for table CPG_categorymap
#
CREATE TABLE IF NOT EXISTS `CPG_categorymap` (
  cid int(11) NOT NULL,
  group_id int(11) NOT NULL,
  PRIMARY KEY  (cid,group_id)
) TYPE=MyISAM COMMENT='Holds the categories where groups can create albums';
# --------------------------------------------------------
and check if the installer still stops while creating that table.
Logged

BradInOKC

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 38
Re: MySQL Error
« Reply #6 on: April 23, 2010, 04:28:15 pm »

Thank you!!!  I had to edit another string in the same way at

Quote
SQL syntax near 'ENGINE=MyISAM COMMENT='Stores EXIF data from individual pics''


I changed "ENGINE" to "TYPE" in that location too, and it installed all the way.  Thank you very very much!

Brad
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: MySQL Error
« Reply #7 on: April 23, 2010, 05:00:05 pm »

@devs: should we change all queries to use TYPE instead of ENGINE. I didn't find any useful information about the difference and MySQL versions.

@BradInOKC: please help us to improve Coppermine and post your MySQL version. Thank you.
Logged

Nibbler

  • Guest
Re: MySQL Error
« Reply #8 on: April 23, 2010, 05:29:17 pm »

The older TYPE option was synonymous with ENGINE. TYPE  has been deprecated since MySQL 4.0 but is still supported for backward compatibility in MySQL 5.1 (excepting MySQL 5.1.7). Since MySQL 5.1.8, it produces a warning. It is removed in MySQL 5.5. You should not use TYPE in any new applications, and you should immediately begin conversion of existing applications to use ENGINE  instead. (See Section C.1.45, “Changes in MySQL 5.1.8 (Not released)”.)

(http://dev.mysql.com/doc/refman/5.1/en/create-table.html)

However

The ENGINE and TYPE  options specify the storage engine for the table. ENGINE was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1). It is the preferred option name as of those versions, and TYPE has become deprecated. TYPE is supported throughout the 4.x series, but likely will be removed in the future.

(http://dev.mysql.com/doc/refman/4.1/en/create-table.html)

So, we should switch from TYPE to ENGINE but we also need to bump the minimum MySQL version requirement.
Logged

Joachim Müller

  • Dev Team member
  • Coppermine addict
  • ****
  • Offline Offline
  • Gender: Male
  • Posts: 47844
  • aka "GauGau"
    • gaugau.de
Re: MySQL Error
« Reply #9 on: April 23, 2010, 07:14:21 pm »

Fine by me to have the minimum requirements for cpg1.5.x raised a bit as far as the mysql database is concerned.

mySQL 4.1.2 would then be the minimum if I understand this right?
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: MySQL Error
« Reply #10 on: April 23, 2010, 08:02:32 pm »

Do we have some other advantages we can use of? I don't know how many people run MySQL < 4.1.2 and will have troubles installing cpg1.5.x. Maybe we use TYPE until MySQL 5.1.8 (or maybe even MySQL 5.5) is released and then switch to ENGINE in the next release of Coppermine for compatibility reasons? Not sure about the release cycle of MySQL and the life time of cpg1.5.x.
Logged

BradInOKC

  • Coppermine novice
  • *
  • Offline Offline
  • Posts: 38
Re: MySQL Error
« Reply #11 on: April 23, 2010, 08:44:06 pm »

I had a hard time finding the version, but I think it is MySQL 3.23.58 ...  I'm not 100% sure though.  If so, I guess that means our web host is using a pretty old version of MySQL...I wonder why they haven't upgraded?
Logged

Nibbler

  • Guest
Re: MySQL Error
« Reply #12 on: April 23, 2010, 08:50:09 pm »

I think the safest approach would be to remove both TYPE and ENGINE and accept the default engine type which is MyISAM anyway.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: MySQL Error
« Reply #13 on: April 23, 2010, 09:27:20 pm »

I think the safest approach would be to remove both TYPE and ENGINE and accept the default engine type which is MyISAM anyway.
Fine by me.
Logged

Αndré

  • Administrator
  • Coppermine addict
  • *****
  • Country: de
  • Offline Offline
  • Gender: Male
  • Posts: 15764
Re: MySQL Error
« Reply #14 on: April 23, 2010, 09:35:18 pm »

The German Wikipedia says that the standard storage engine is MyISAM for Linux and InnoDB for Windows. But afaik all MyISAM queries can be used with InnoDB, am I right?
Logged
Pages: [1]   Go Up
 

Page created in 0.021 seconds with 21 queries.