0009637: Does not work with MySQL 6 - MantisBT

ID	Project	Category	View Status	Date Submitted	Last Update
0009637	mantisbt	db mysql	public	2008-09-16 19:21	2009-01-15 11:26

Reporter	samj	Assigned To	thraxisp
Priority	normal	Severity	major	Reproducibility	always
Status	closed	Resolution	fixed
Platform	Unix	OS	OS X	OS Version	10.5
Product Version	1.1.2
Fixed in Version	1.2.0a3

Summary	0009637: Does not work with MySQL 6
Description	It does not appear to be possible to use Mantis with MySQL 6 on OS X (and likely on other platforms too).
Steps To Reproduce	sudo cp /usr/local/mysql/support-files/my-small.cnf /etc/my.cnf mkdir /var/mysql chown _mysql /var/mysql s\|/tmp/mysql.sock\|/var/mysql/mysql.sock\|g start installation process observe error below at Schema CreateTableSQL ( mantis_config_table )
Additional Information	BAD CREATE TABLE mantis_config_table ( config_id VARCHAR(64) NOT NULL, project_id INTEGER NOT NULL DEFAULT 0, user_id INTEGER NOT NULL DEFAULT 0, access_reqd INTEGER DEFAULT 0, type INTEGER DEFAULT 90, value LONGTEXT NOT NULL, PRIMARY KEY (config_id, project_id, user_id) )TYPE=MyISAM 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' at line 9
Tags	No tags attached.
Attached Files	mantis-mysql6.diff (14,489 bytes) diff -urN mantis-1.1.2/admin/schema.php mantis/admin/schema.php --- mantis-1.1.2/admin/schema.php 2007-10-14 00:36:41.000000000 +0200 +++ mantis/admin/schema.php 2008-09-17 01:28:58.000000000 +0200 @@ -37,7 +37,7 @@ access_reqd I DEFAULT '0', type I DEFAULT '90', value XL NOTNULL", -Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateIndexSQL',Array('idx_config',config_get('mantis_config_table'),'config_id')); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_bug_file_table')," id I UNSIGNED NOTNULL PRIMARY AUTOINCREMENT, @@ -51,7 +51,7 @@ file_type C(250) NOTNULL DEFAULT \" '' \", date_added T NOTNULL DEFAULT '1970-01-01 00:00:01', content B NOTNULL - ",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); + ",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateIndexSQL',Array('idx_bug_file_bug_id',config_get('mantis_bug_file_table'),'bug_id')); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_bug_history_table')," id I UNSIGNED NOTNULL PRIMARY AUTOINCREMENT, @@ -62,19 +62,19 @@ old_value C(128) NOTNULL DEFAULT \" '' \", new_value C(128) NOTNULL DEFAULT \" '' \", type I2 NOTNULL DEFAULT '0' - ",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); + ",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateIndexSQL',Array('idx_bug_history_bug_id',config_get('mantis_bug_history_table'),'bug_id')); $upgrade[] = Array('CreateIndexSQL',Array('idx_history_user_id',config_get('mantis_bug_history_table'),'user_id')); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_bug_monitor_table')," user_id I UNSIGNED NOTNULL PRIMARY DEFAULT '0', bug_id I UNSIGNED NOTNULL PRIMARY DEFAULT '0' -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_bug_relationship_table')," id I UNSIGNED NOTNULL AUTOINCREMENT PRIMARY, source_bug_id I UNSIGNED NOTNULL DEFAULT '0', destination_bug_id I UNSIGNED NOTNULL DEFAULT '0', relationship_type I2 NOTNULL DEFAULT '0' -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateIndexSQL',Array('idx_relationship_source',config_get('mantis_bug_relationship_table'),'source_bug_id')); $upgrade[] = Array('CreateIndexSQL',Array('idx_relationship_destination',config_get('mantis_bug_relationship_table'),'destination_bug_id')); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_bug_table')," @@ -105,7 +105,7 @@ summary C(128) NOTNULL DEFAULT \" '' \", sponsorship_total I NOTNULL DEFAULT '0', sticky L NOTNULL DEFAULT '0' -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateIndexSQL',Array('idx_bug_sponsorship_total',config_get('mantis_bug_table'),'sponsorship_total')); $upgrade[] = Array('CreateIndexSQL',Array('idx_bug_fixed_in_version',config_get('mantis_bug_table'),'fixed_in_version')); $upgrade[] = Array('CreateIndexSQL',Array('idx_bug_status',config_get('mantis_bug_table'),'status')); @@ -115,7 +115,7 @@ description XL NOTNULL, steps_to_reproduce XL NOTNULL, additional_information XL NOTNULL -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_bugnote_table')," id I UNSIGNED PRIMARY NOTNULL AUTOINCREMENT, bug_id I UNSIGNED NOTNULL DEFAULT '0', @@ -126,23 +126,23 @@ last_modified T NOTNULL DEFAULT '1970-01-01 00:00:01', note_type I DEFAULT '0', note_attr C(250) DEFAULT \" '' \" -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateIndexSQL',Array('idx_bug',config_get('mantis_bugnote_table'),'bug_id')); $upgrade[] = Array('CreateIndexSQL',Array('idx_last_mod',config_get('mantis_bugnote_table'),'last_modified')); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_bugnote_text_table')," id I UNSIGNED NOTNULL PRIMARY AUTOINCREMENT, note XL NOTNULL -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_custom_field_project_table')," field_id I NOTNULL PRIMARY DEFAULT '0', project_id I UNSIGNED PRIMARY NOTNULL DEFAULT '0', sequence I2 NOTNULL DEFAULT '0' -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_custom_field_string_table')," field_id I NOTNULL PRIMARY DEFAULT '0', bug_id I NOTNULL PRIMARY DEFAULT '0', value C(255) NOTNULL DEFAULT \" '' \" -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateIndexSQL',Array('idx_custom_field_bug',config_get('mantis_custom_field_string_table'),'bug_id')); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_custom_field_table')," id I NOTNULL PRIMARY AUTOINCREMENT, @@ -164,7 +164,7 @@ display_resolved L NOTNULL DEFAULT '0', display_closed L NOTNULL DEFAULT '0', require_closed L NOTNULL DEFAULT '0' -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateIndexSQL',Array('idx_custom_field_name',config_get('mantis_custom_field_table'),'name')); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_filters_table')," id I UNSIGNED NOTNULL PRIMARY AUTOINCREMENT, @@ -173,7 +173,7 @@ is_public L DEFAULT NULL, name C(64) NOTNULL DEFAULT \" '' \", filter_string XL NOTNULL -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_news_table')," id I UNSIGNED PRIMARY NOTNULL AUTOINCREMENT, project_id I UNSIGNED NOTNULL DEFAULT '0', @@ -184,12 +184,12 @@ announcement L NOTNULL DEFAULT '0', headline C(64) NOTNULL DEFAULT \" '' \", body XL NOTNULL -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_project_category_table')," project_id I UNSIGNED NOTNULL PRIMARY DEFAULT '0', category C(64) NOTNULL PRIMARY DEFAULT \" '' \", user_id I UNSIGNED NOTNULL DEFAULT '0' -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_project_file_table')," id I UNSIGNED NOTNULL PRIMARY AUTOINCREMENT, project_id I UNSIGNED NOTNULL DEFAULT '0', @@ -202,11 +202,11 @@ file_type C(250) NOTNULL DEFAULT \" '' \", date_added T NOTNULL DEFAULT '1970-01-01 00:00:01', content B NOTNULL -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_project_hierarchy_table')," child_id I UNSIGNED NOTNULL, parent_id I UNSIGNED NOTNULL", -Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_project_table')," id I UNSIGNED PRIMARY NOTNULL AUTOINCREMENT, name C(128) NOTNULL DEFAULT \" '' \", @@ -216,7 +216,7 @@ access_min I2 NOTNULL DEFAULT '10', file_path C(250) NOTNULL DEFAULT \" '' \", description XL NOTNULL -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateIndexSQL',Array('idx_project_id',config_get('mantis_project_table'),'id')); $upgrade[] = Array('CreateIndexSQL',Array('idx_project_name',config_get('mantis_project_table'),'name',Array('UNIQUE'))); $upgrade[] = Array('CreateIndexSQL',Array('idx_project_view',config_get('mantis_project_table'),'view_state')); @@ -224,7 +224,7 @@ project_id I UNSIGNED PRIMARY NOTNULL DEFAULT '0', user_id I UNSIGNED PRIMARY NOTNULL DEFAULT '0', access_level I2 NOTNULL DEFAULT '10' -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array( 'CreateIndexSQL',Array('idx_project_user',config_get('mantis_project_user_list_table'),'user_id')); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_project_version_table')," id I NOTNULL PRIMARY AUTOINCREMENT, @@ -233,7 +233,7 @@ date_order T NOTNULL DEFAULT '1970-01-01 00:00:01', description XL NOTNULL, released L NOTNULL DEFAULT '1' -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateIndexSQL',Array('idx_project_version',config_get('mantis_project_version_table'),'project_id,version',Array('UNIQUE'))); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_sponsorship_table')," id I NOTNULL PRIMARY AUTOINCREMENT, @@ -245,7 +245,7 @@ paid L NOTNULL DEFAULT '0', date_submitted T NOTNULL DEFAULT '1970-01-01 00:00:01', last_updated T NOTNULL DEFAULT '1970-01-01 00:00:01' -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateIndexSQL',Array('idx_sponsorship_bug_id',config_get('mantis_sponsorship_table'),'bug_id')); $upgrade[] = Array('CreateIndexSQL',Array('idx_sponsorship_user_id',config_get('mantis_sponsorship_table'),'user_id')); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_tokens_table')," @@ -255,7 +255,7 @@ timestamp T NOTNULL, expiry T, value XL NOTNULL", -Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_user_pref_table')," id I UNSIGNED NOTNULL PRIMARY AUTOINCREMENT, user_id I UNSIGNED NOTNULL DEFAULT '0', @@ -288,11 +288,11 @@ email_on_new_min_severity I2 NOTNULL DEFAULT '10', email_bugnote_limit I2 NOTNULL DEFAULT '0', language C(32) NOTNULL DEFAULT 'english' -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_user_print_pref_table')," user_id I UNSIGNED NOTNULL PRIMARY DEFAULT '0', print_pref C(27) NOTNULL DEFAULT \" '' \" -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_user_profile_table')," id I UNSIGNED NOTNULL PRIMARY AUTOINCREMENT, user_id I UNSIGNED NOTNULL DEFAULT '0', @@ -300,7 +300,7 @@ os C(32) NOTNULL DEFAULT \" '' \", os_build C(32) NOTNULL DEFAULT \" '' \", description XL NOTNULL -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateTableSQL',Array(config_get('mantis_user_table')," id I UNSIGNED NOTNULL PRIMARY AUTOINCREMENT, username C(32) NOTNULL DEFAULT \" '' \", @@ -316,7 +316,7 @@ lost_password_request_count I2 NOTNULL DEFAULT '0', failed_login_count I2 NOTNULL DEFAULT '0', cookie_string C(64) NOTNULL DEFAULT \" '' \" -",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); +",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateIndexSQL',Array('idx_user_cookie_string',config_get('mantis_user_table'),'cookie_string',Array('UNIQUE'))); $upgrade[] = Array('CreateIndexSQL',Array('idx_user_username',config_get('mantis_user_table'),'username',Array('UNIQUE'))); $upgrade[] = Array('CreateIndexSQL',Array('idx_enable',config_get('mantis_user_table'),'enabled')); @@ -335,7 +335,7 @@ submitted T NOTNULL DEFAULT '1970-01-01 00:00:01', metadata XL NOTNULL, body XL NOTNULL - ",Array('mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS'))); + ",Array('mysql' => '', 'pgsql' => 'WITHOUT OIDS'))); $upgrade[] = Array('CreateIndexSQL',Array('idx_email_id',config_get('mantis_email_table'),'email_id')); $upgrade[] = Array('AddColumnSQL',Array(config_get('mantis_bug_table'), "target_version C(64) NOTNULL DEFAULT \" '' \"")); $upgrade[] = Array('AddColumnSQL',Array(config_get('mantis_bugnote_table'), "time_tracking I UNSIGNED NOTNULL DEFAULT \" 0 \"")); @@ -352,13 +352,13 @@ description XL NOTNULL, date_created T NOTNULL DEFAULT '1970-01-01 00:00:01', date_updated T NOTNULL DEFAULT '1970-01-01 00:00:01' - ", Array( 'mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS' ) ) ); + ", Array( 'mysql' => '', 'pgsql' => 'WITHOUT OIDS' ) ) ); $upgrade[] = Array('CreateTableSQL', Array( config_get( 'mantis_bug_tag_table' ), " bug_id I UNSIGNED NOTNULL PRIMARY DEFAULT '0', tag_id I UNSIGNED NOTNULL PRIMARY DEFAULT '0', user_id I UNSIGNED NOTNULL DEFAULT '0', date_attached T NOTNULL DEFAULT '1970-01-01 00:00:01' - ", Array( 'mysql' => 'TYPE=MyISAM', 'pgsql' => 'WITHOUT OIDS' ) ) ); + ", Array( 'mysql' => '', 'pgsql' => 'WITHOUT OIDS' ) ) ); $upgrade[] = Array('CreateIndexSQL', Array( 'idx_typeowner', config_get( 'mantis_tokens_table' ), 'type, owner' ) ); ?> diff -urN mantis-1.1.2/admin/upgrades/0_18_inc.php mantis/admin/upgrades/0_18_inc.php --- mantis-1.1.2/admin/upgrades/0_18_inc.php 2007-10-14 00:36:41.000000000 +0200 +++ mantis/admin/upgrades/0_18_inc.php 2008-09-17 01:29:23.000000000 +0200 @@ -188,7 +188,7 @@ PRIMARY KEY (id), KEY bug_id (bug_id), KEY user_id (user_id) - ) TYPE=MyISAM COMMENT='A table for sponsorships' AUTO_INCREMENT=1" ); + ) COMMENT='A table for sponsorships' AUTO_INCREMENT=1" ); $upgrades[] = new SQLUpgrade( 'sponsorship-2', mantis-mysql6.diff (14,489 bytes)

samj 2008-09-16 19:24 reporter ~0019402	Hrm... so the reason I was installing was to test the sponsorship functionality - didn't really work as I'd expected and appears to be irreversible. Never mind. Hope the feedback helps - seems the Type=MyISAM is not understood... maybe syntax has changed or I just don't have it compiled in.

samj 2008-09-16 19:38 reporter ~0019403	Seems it's just a case of removing the TYPE=MyISAM from two files - refer to diff.

samj 2008-09-16 20:14 reporter ~0019404	Still doesn't work with 1.2.0a2/trunk either.

thraxisp 2008-09-17 20:19 reporter ~0019408 Last edited: 2008-09-17 20:24	It should be "ENGINE=MyISAM". Let me know it this works. (TYPE was depricated in MySQL 5.1, ENGINE works in 4.1 and above).

FrederickChapleau 2008-12-06 16:52 reporter ~0020271	A search & replace for TYPE=MyISAM with ENGINE=MyISAM in the schema.php file of the admin folder worked. Thanks.

grangeway 2008-12-06 18:05 reporter ~0020276	Fixed in git trunk