MySQL Error

[BradInOKC]

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

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:

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

[onthepike]

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.

[BradInOKC]

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

[onthepike]

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.

[Joachim Müller]

http://forum.coppermine-gallery.net/index.php/topic,55415.msg270616.html#msg270616

[Αndré]

Please open sql/schema.sql, find

Code Select Expand

#
# 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 Expand

#
# 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.

[BradInOKC]

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

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

[Αndré]

@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.

[Nibbler]

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.

[Joachim Müller]

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?

[Αndré]

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.

[BradInOKC]

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?

[Nibbler]

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

[Αndré]

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.

[Αndré]

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?