View Issue Details

WikiJump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0008704mantisbtupgradepublic2008-01-03 21:542008-07-31 17:40
Reporterdjcarr Assigned Togiallu  
PrioritynormalSeveritymajorReproducibilityhave not tried
Status closedResolutionno change required 
PlatformWindowsOSXPOS VersionSP2
Product Version1.1.0 
Summary0008704: 1.0.1 to 1.1.0 : default value errors
Description

Apache 2.2.6, PHP 5.2.5, MySQL 5.0.18

Installed 1.1.0 Final to a new directory, pointed it at a mysqldump'd 1.0.1 db, and went to /admin to upgrade. All lights green during upgrade, and also during check.php after.

Login and View Issues works. But Issue View and Report Issue among other actions report 'Field 'xxx' doesn't have a default value for the query' errors (see Additional Info).

Additional Information

Issue View fails when displaying history:

Database query failed. Error received from database was #1364: Field 'id' doesn't have a default value for the query: INSERT INTO mantis_tokens_table
( type, value, timestamp, expiry, owner )
VALUES ( '3', '12924', '2008-01-04 00:51:26', '2008-01-05 00:51:26', '18' )

Report Issue also fails:

Database query failed. Error received from database was #1364: Field 'id' doesn't have a default value for the query: INSERT INTO mantis_bug_text_table
( description, steps_to_reproduce, additional_information )
VALUES
( 'asdfasdf', '',
'asdf' )

Checking the mysqldump for the 1.0.1 db:

CREATE TABLE mantis_tokens_table (
id int(11) NOT NULL,
owner int(11) NOT NULL default '0',
type int(11) NOT NULL default '0',
timestamp datetime NOT NULL default '0000-00-00 00:00:00',
expiry datetime NOT NULL default '0000-00-00 00:00:00',
value text NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE mantis_bug_text_table (
id int(7) unsigned NOT NULL,
description text NOT NULL,
steps_to_reproduce text NOT NULL,
additional_information text NOT NULL,
PRIMARY KEY (id)
);

And finally checking the 1.1.0 tables, post-upgrade:

CREATE TABLE testmantis.mantis_bug_text_table (
id int(7) unsigned NOT NULL,
description text NOT NULL,
steps_to_reproduce text NOT NULL,
additional_information text NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

REATE TABLE testmantis.mantis_tokens_table (
id int(11) NOT NULL,
owner int(11) NOT NULL default '0',
type int(11) NOT NULL default '0',
timestamp datetime NOT NULL default '0000-00-00 00:00:00',
expiry datetime NOT NULL default '0000-00-00 00:00:00',
value text NOT NULL,
PRIMARY KEY (id),
KEY idx_typeowner (type,owner)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

It appears that Mantis is expecting a default value on these tables, but their fields haven't been specified to use defaults, either in 1.0.1 or 1.1.0.

Tagsverify
Attached Files
mantis_upgrade_sql.txt (19,662 bytes)    
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;

ALTER TABLE mantis_config_table ADD  INDEX idx_config  (config_id);

CREATE TABLE mantis_bug_file_table (
id                       INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
bug_id                   INTEGER UNSIGNED NOT NULL DEFAULT 0,
title                    VARCHAR(250) NOT NULL DEFAULT '',
description              VARCHAR(250) NOT NULL DEFAULT '',
diskfile                 VARCHAR(250) NOT NULL DEFAULT '',
filename                 VARCHAR(250) NOT NULL DEFAULT '',
folder                   VARCHAR(250) NOT NULL DEFAULT '',
filesize                 INTEGER NOT NULL DEFAULT 0,
file_type                VARCHAR(250) NOT NULL DEFAULT '',
date_added               DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
content                  LONGBLOB,
                 PRIMARY KEY (id)
)TYPE=MyISAM;

ALTER TABLE mantis_bug_file_table ADD  INDEX idx_bug_file_bug_id  (bug_id);

CREATE TABLE mantis_bug_history_table (
id                       INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
user_id                  INTEGER UNSIGNED NOT NULL DEFAULT 0,
bug_id                   INTEGER UNSIGNED NOT NULL DEFAULT 0,
date_modified            DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
field_name               VARCHAR(32) NOT NULL DEFAULT '',
old_value                VARCHAR(128) NOT NULL DEFAULT '',
new_value                VARCHAR(128) NOT NULL DEFAULT '',
type                     SMALLINT NOT NULL DEFAULT 0,
                 PRIMARY KEY (id)
)TYPE=MyISAM;

ALTER TABLE mantis_bug_history_table ADD  INDEX idx_bug_history_bug_id  (bug_id);

ALTER TABLE mantis_bug_history_table ADD  INDEX idx_history_user_id  (user_id);

CREATE TABLE mantis_bug_monitor_table (
user_id                  INTEGER UNSIGNED NOT NULL DEFAULT 0,
bug_id                   INTEGER UNSIGNED NOT NULL DEFAULT 0,
                 PRIMARY KEY (user_id, bug_id)
)TYPE=MyISAM;

CREATE TABLE mantis_bug_relationship_table (
id                       INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
source_bug_id            INTEGER UNSIGNED NOT NULL DEFAULT 0,
destination_bug_id       INTEGER UNSIGNED NOT NULL DEFAULT 0,
relationship_type        SMALLINT NOT NULL DEFAULT 0,
                 PRIMARY KEY (id)
)TYPE=MyISAM;

ALTER TABLE mantis_bug_relationship_table ADD  INDEX idx_relationship_source  (source_bug_id);

ALTER TABLE mantis_bug_relationship_table ADD  INDEX idx_relationship_destination  (destination_bug_id);

CREATE TABLE mantis_bug_table (
id                       INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
project_id               INTEGER UNSIGNED NOT NULL DEFAULT 0,
reporter_id              INTEGER UNSIGNED NOT NULL DEFAULT 0,
handler_id               INTEGER UNSIGNED NOT NULL DEFAULT 0,
duplicate_id             INTEGER UNSIGNED NOT NULL DEFAULT 0,
priority                 SMALLINT NOT NULL DEFAULT 30,
severity                 SMALLINT NOT NULL DEFAULT 50,
reproducibility          SMALLINT NOT NULL DEFAULT 10,
status                   SMALLINT NOT NULL DEFAULT 10,
resolution               SMALLINT NOT NULL DEFAULT 10,
projection               SMALLINT NOT NULL DEFAULT 10,
category                 VARCHAR(64) NOT NULL DEFAULT '',
date_submitted           DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
last_updated             DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
eta                      SMALLINT NOT NULL DEFAULT 10,
bug_text_id              INTEGER UNSIGNED NOT NULL DEFAULT 0,
os                       VARCHAR(32) NOT NULL DEFAULT '',
os_build                 VARCHAR(32) NOT NULL DEFAULT '',
platform                 VARCHAR(32) NOT NULL DEFAULT '',
version                  VARCHAR(64) NOT NULL DEFAULT '',
fixed_in_version         VARCHAR(64) NOT NULL DEFAULT '',
build                    VARCHAR(32) NOT NULL DEFAULT '',
profile_id               INTEGER UNSIGNED NOT NULL DEFAULT 0,
view_state               SMALLINT NOT NULL DEFAULT 10,
summary                  VARCHAR(128) NOT NULL DEFAULT '',
sponsorship_total        INTEGER NOT NULL DEFAULT 0,
sticky                   TINYINT NOT NULL DEFAULT 0,
                 PRIMARY KEY (id)
)TYPE=MyISAM;

ALTER TABLE mantis_bug_table ADD  INDEX idx_bug_sponsorship_total  (sponsorship_total);

ALTER TABLE mantis_bug_table ADD  INDEX idx_bug_fixed_in_version  (fixed_in_version);

ALTER TABLE mantis_bug_table ADD  INDEX idx_bug_status  (status);

ALTER TABLE mantis_bug_table ADD  INDEX idx_project  (project_id);

CREATE TABLE mantis_bug_text_table (
id                       INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
description              LONGTEXT NOT NULL,
steps_to_reproduce       LONGTEXT NOT NULL,
additional_information   LONGTEXT NOT NULL,
                 PRIMARY KEY (id)
)TYPE=MyISAM;

CREATE TABLE mantis_bugnote_table (
id                       INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
bug_id                   INTEGER UNSIGNED NOT NULL DEFAULT 0,
reporter_id              INTEGER UNSIGNED NOT NULL DEFAULT 0,
bugnote_text_id          INTEGER UNSIGNED NOT NULL DEFAULT 0,
view_state               SMALLINT NOT NULL DEFAULT 10,
date_submitted           DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
last_modified            DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
note_type                INTEGER DEFAULT 0,
note_attr                VARCHAR(250) DEFAULT '',
                 PRIMARY KEY (id)
)TYPE=MyISAM;

ALTER TABLE mantis_bugnote_table ADD  INDEX idx_bug  (bug_id);

ALTER TABLE mantis_bugnote_table ADD  INDEX idx_last_mod  (last_modified);

CREATE TABLE mantis_bugnote_text_table (
id                       INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
note                     LONGTEXT NOT NULL,
                 PRIMARY KEY (id)
)TYPE=MyISAM;

CREATE TABLE mantis_custom_field_project_table (
field_id                 INTEGER NOT NULL DEFAULT 0,
project_id               INTEGER UNSIGNED NOT NULL DEFAULT 0,
sequence                 SMALLINT NOT NULL DEFAULT 0,
                 PRIMARY KEY (field_id, project_id)
)TYPE=MyISAM;

CREATE TABLE mantis_custom_field_string_table (
field_id                 INTEGER NOT NULL DEFAULT 0,
bug_id                   INTEGER NOT NULL DEFAULT 0,
value                    VARCHAR(255) NOT NULL DEFAULT '',
                 PRIMARY KEY (field_id, bug_id)
)TYPE=MyISAM;

ALTER TABLE mantis_custom_field_string_table ADD  INDEX idx_custom_field_bug  (bug_id);

CREATE TABLE mantis_custom_field_table (
id                       INTEGER NOT NULL AUTO_INCREMENT,
name                     VARCHAR(64) NOT NULL DEFAULT '',
type                     SMALLINT NOT NULL DEFAULT 0,
possible_values          VARCHAR(255) NOT NULL DEFAULT '',
default_value            VARCHAR(255) NOT NULL DEFAULT '',
valid_regexp             VARCHAR(255) NOT NULL DEFAULT '',
access_level_r           SMALLINT NOT NULL DEFAULT 0,
access_level_rw          SMALLINT NOT NULL DEFAULT 0,
length_min               INTEGER NOT NULL DEFAULT 0,
length_max               INTEGER NOT NULL DEFAULT 0,
advanced                 TINYINT NOT NULL DEFAULT 0,
require_report           TINYINT NOT NULL DEFAULT 0,
require_update           TINYINT NOT NULL DEFAULT 0,
display_report           TINYINT NOT NULL DEFAULT 1,
display_update           TINYINT NOT NULL DEFAULT 1,
require_resolved         TINYINT NOT NULL DEFAULT 0,
display_resolved         TINYINT NOT NULL DEFAULT 0,
display_closed           TINYINT NOT NULL DEFAULT 0,
require_closed           TINYINT NOT NULL DEFAULT 0,
                 PRIMARY KEY (id)
)TYPE=MyISAM;

ALTER TABLE mantis_custom_field_table ADD  INDEX idx_custom_field_name  (name);

CREATE TABLE mantis_filters_table (
id                       INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
user_id                  INTEGER NOT NULL DEFAULT 0,
project_id               INTEGER NOT NULL DEFAULT 0,
is_public                TINYINT DEFAULT NULL,
name                     VARCHAR(64) NOT NULL DEFAULT '',
filter_string            LONGTEXT NOT NULL,
                 PRIMARY KEY (id)
)TYPE=MyISAM;

CREATE TABLE mantis_news_table (
id                       INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
project_id               INTEGER UNSIGNED NOT NULL DEFAULT 0,
poster_id                INTEGER UNSIGNED NOT NULL DEFAULT 0,
date_posted              DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
last_modified            DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
view_state               SMALLINT NOT NULL DEFAULT 10,
announcement             TINYINT NOT NULL DEFAULT 0,
headline                 VARCHAR(64) NOT NULL DEFAULT '',
body                     LONGTEXT NOT NULL,
                 PRIMARY KEY (id)
)TYPE=MyISAM;

CREATE TABLE mantis_project_category_table (
project_id               INTEGER UNSIGNED NOT NULL DEFAULT 0,
category                 VARCHAR(64) NOT NULL DEFAULT '',
user_id                  INTEGER UNSIGNED NOT NULL DEFAULT 0,
                 PRIMARY KEY (project_id, category)
)TYPE=MyISAM;

CREATE TABLE mantis_project_file_table (
id                       INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
project_id               INTEGER UNSIGNED NOT NULL DEFAULT 0,
title                    VARCHAR(250) NOT NULL DEFAULT '',
description              VARCHAR(250) NOT NULL DEFAULT '',
diskfile                 VARCHAR(250) NOT NULL DEFAULT '',
filename                 VARCHAR(250) NOT NULL DEFAULT '',
folder                   VARCHAR(250) NOT NULL DEFAULT '',
filesize                 INTEGER NOT NULL DEFAULT 0,
file_type                VARCHAR(250) NOT NULL DEFAULT '',
date_added               DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
content                  LONGBLOB,
                 PRIMARY KEY (id)
)TYPE=MyISAM;

CREATE TABLE mantis_project_hierarchy_table (
child_id                 INTEGER UNSIGNED NOT NULL,
parent_id                INTEGER UNSIGNED NOT NULL
)TYPE=MyISAM;

CREATE TABLE mantis_project_table (
id                       INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
name                     VARCHAR(128) NOT NULL DEFAULT '',
status                   SMALLINT NOT NULL DEFAULT 10,
enabled                  TINYINT NOT NULL DEFAULT 1,
view_state               SMALLINT NOT NULL DEFAULT 10,
access_min               SMALLINT NOT NULL DEFAULT 10,
file_path                VARCHAR(250) NOT NULL DEFAULT '',
description              LONGTEXT NOT NULL,
                 PRIMARY KEY (id)
)TYPE=MyISAM;

ALTER TABLE mantis_project_table ADD  INDEX idx_project_id  (id);

ALTER TABLE mantis_project_table ADD  UNIQUE INDEX idx_project_name  (name);

ALTER TABLE mantis_project_table ADD  INDEX idx_project_view  (view_state);

CREATE TABLE mantis_project_user_list_table (
project_id               INTEGER UNSIGNED NOT NULL DEFAULT 0,
user_id                  INTEGER UNSIGNED NOT NULL DEFAULT 0,
access_level             SMALLINT NOT NULL DEFAULT 10,
                 PRIMARY KEY (project_id, user_id)
)TYPE=MyISAM;

ALTER TABLE mantis_project_user_list_table ADD  INDEX idx_project_user  (user_id);

CREATE TABLE mantis_project_version_table (
id                       INTEGER NOT NULL AUTO_INCREMENT,
project_id               INTEGER UNSIGNED NOT NULL DEFAULT 0,
version                  VARCHAR(64) NOT NULL DEFAULT '',
date_order               DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
description              LONGTEXT NOT NULL,
released                 TINYINT NOT NULL DEFAULT 1,
                 PRIMARY KEY (id)
)TYPE=MyISAM;

ALTER TABLE mantis_project_version_table ADD  UNIQUE INDEX idx_project_version  (project_id, version);

CREATE TABLE mantis_sponsorship_table (
id                       INTEGER NOT NULL AUTO_INCREMENT,
bug_id                   INTEGER NOT NULL DEFAULT 0,
user_id                  INTEGER NOT NULL DEFAULT 0,
amount                   INTEGER NOT NULL DEFAULT 0,
logo                     VARCHAR(128) NOT NULL DEFAULT '',
url                      VARCHAR(128) NOT NULL DEFAULT '',
paid                     TINYINT NOT NULL DEFAULT 0,
date_submitted           DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
last_updated             DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
                 PRIMARY KEY (id)
)TYPE=MyISAM;

ALTER TABLE mantis_sponsorship_table ADD  INDEX idx_sponsorship_bug_id  (bug_id);

ALTER TABLE mantis_sponsorship_table ADD  INDEX idx_sponsorship_user_id  (user_id);

CREATE TABLE mantis_tokens_table (
id                       INTEGER NOT NULL AUTO_INCREMENT,
owner                    INTEGER NOT NULL,
type                     INTEGER NOT NULL,
timestamp                DATETIME NOT NULL,
expiry                   DATETIME,
value                    LONGTEXT NOT NULL,
                 PRIMARY KEY (id)
)TYPE=MyISAM;

CREATE TABLE mantis_user_pref_table (
id                       INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
user_id                  INTEGER UNSIGNED NOT NULL DEFAULT 0,
project_id               INTEGER UNSIGNED NOT NULL DEFAULT 0,
default_profile          INTEGER UNSIGNED NOT NULL DEFAULT 0,
default_project          INTEGER UNSIGNED NOT NULL DEFAULT 0,
advanced_report          TINYINT NOT NULL DEFAULT 0,
advanced_view            TINYINT NOT NULL DEFAULT 0,
advanced_update          TINYINT NOT NULL DEFAULT 0,
refresh_delay            INTEGER NOT NULL DEFAULT 0,
redirect_delay           TINYINT NOT NULL DEFAULT 0,
bugnote_order            VARCHAR(4) NOT NULL DEFAULT 'ASC',
email_on_new             TINYINT NOT NULL DEFAULT 0,
email_on_assigned        TINYINT NOT NULL DEFAULT 0,
email_on_feedback        TINYINT NOT NULL DEFAULT 0,
email_on_resolved        TINYINT NOT NULL DEFAULT 0,
email_on_closed          TINYINT NOT NULL DEFAULT 0,
email_on_reopened        TINYINT NOT NULL DEFAULT 0,
email_on_bugnote         TINYINT NOT NULL DEFAULT 0,
email_on_status          TINYINT NOT NULL DEFAULT 0,
email_on_priority        TINYINT NOT NULL DEFAULT 0,
email_on_priority_min_severity SMALLINT NOT NULL DEFAULT 10,
email_on_status_min_severity SMALLINT NOT NULL DEFAULT 10,
email_on_bugnote_min_severity SMALLINT NOT NULL DEFAULT 10,
email_on_reopened_min_severity SMALLINT NOT NULL DEFAULT 10,
email_on_closed_min_severity SMALLINT NOT NULL DEFAULT 10,
email_on_resolved_min_severity SMALLINT NOT NULL DEFAULT 10,
email_on_feedback_min_severity SMALLINT NOT NULL DEFAULT 10,
email_on_assigned_min_severity SMALLINT NOT NULL DEFAULT 10,
email_on_new_min_severity SMALLINT NOT NULL DEFAULT 10,
email_bugnote_limit      SMALLINT NOT NULL DEFAULT 0,
language                 VARCHAR(32) NOT NULL DEFAULT 'english',
                 PRIMARY KEY (id)
)TYPE=MyISAM;

CREATE TABLE mantis_user_print_pref_table (
user_id                  INTEGER UNSIGNED NOT NULL DEFAULT 0,
print_pref               VARCHAR(27) NOT NULL DEFAULT '',
                 PRIMARY KEY (user_id)
)TYPE=MyISAM;

CREATE TABLE mantis_user_profile_table (
id                       INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
user_id                  INTEGER UNSIGNED NOT NULL DEFAULT 0,
platform                 VARCHAR(32) NOT NULL DEFAULT '',
os                       VARCHAR(32) NOT NULL DEFAULT '',
os_build                 VARCHAR(32) NOT NULL DEFAULT '',
description              LONGTEXT NOT NULL,
                 PRIMARY KEY (id)
)TYPE=MyISAM;

CREATE TABLE mantis_user_table (
id                       INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
username                 VARCHAR(32) NOT NULL DEFAULT '',
realname                 VARCHAR(64) NOT NULL DEFAULT '',
email                    VARCHAR(64) NOT NULL DEFAULT '',
password                 VARCHAR(32) NOT NULL DEFAULT '',
date_created             DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
last_visit               DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
enabled                  TINYINT NOT NULL DEFAULT 1,
protected                TINYINT NOT NULL DEFAULT 0,
access_level             SMALLINT NOT NULL DEFAULT 10,
login_count              INTEGER NOT NULL DEFAULT 0,
lost_password_request_count SMALLINT NOT NULL DEFAULT 0,
failed_login_count       SMALLINT NOT NULL DEFAULT 0,
cookie_string            VARCHAR(64) NOT NULL DEFAULT '',
                 PRIMARY KEY (id)
)TYPE=MyISAM;

ALTER TABLE mantis_user_table ADD  UNIQUE INDEX idx_user_cookie_string  (cookie_string);

ALTER TABLE mantis_user_table ADD  UNIQUE INDEX idx_user_username  (username);

ALTER TABLE mantis_user_table ADD  INDEX idx_enable  (enabled);

ALTER TABLE mantis_user_table ADD  INDEX idx_access  (access_level);

INSERT INTO mantis_user_table(username, realname, email, password, date_created, last_visit, enabled, protected, access_level, login_count, lost_password_request_count, failed_login_count, cookie_string) VALUES 
        ('administrator', '', 'root@localhost', '63a9f0ea7bb98050796b649e85481845', '2008-01-04 03:10:19', '2008-01-04 03:10:19', 1, 0, 90, 3, 0, 0, '39ad3441317f253fc572a57278c9545d35a83de23e14f898a8d6ac95bbe3b86d');

ALTER TABLE mantis_bug_history_table MODIFY COLUMN old_value VARCHAR(255) NOT NULL;

ALTER TABLE mantis_bug_history_table MODIFY COLUMN new_value VARCHAR(255) NOT NULL;

CREATE TABLE mantis_email_table (
email_id                 INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
email                    VARCHAR(64) NOT NULL DEFAULT '',
subject                  VARCHAR(250) NOT NULL DEFAULT '',
submitted                DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
metadata                 LONGTEXT NOT NULL,
body                     LONGTEXT NOT NULL,
                 PRIMARY KEY (email_id)
)TYPE=MyISAM;

ALTER TABLE mantis_email_table ADD  INDEX idx_email_id  (email_id);

ALTER TABLE mantis_bug_table ADD target_version VARCHAR(64) NOT NULL DEFAULT '';

ALTER TABLE mantis_bugnote_table ADD time_tracking INTEGER UNSIGNED NOT NULL DEFAULT 0;

ALTER TABLE mantis_bug_file_table ADD  INDEX idx_diskfile  (diskfile);

ALTER TABLE mantis_user_print_pref_table MODIFY COLUMN print_pref VARCHAR(64) NOT NULL;

ALTER TABLE mantis_bug_history_table MODIFY COLUMN field_name VARCHAR(64) NOT NULL;

CREATE TABLE mantis_tag_table (
id                       INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
user_id                  INTEGER UNSIGNED NOT NULL DEFAULT 0,
name                     VARCHAR(100) NOT NULL DEFAULT '',
description              LONGTEXT NOT NULL,
date_created             DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
date_updated             DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
                 PRIMARY KEY (id, name)
)TYPE=MyISAM;

CREATE TABLE mantis_bug_tag_table (
bug_id                   INTEGER UNSIGNED NOT NULL DEFAULT 0,
tag_id                   INTEGER UNSIGNED NOT NULL DEFAULT 0,
user_id                  INTEGER UNSIGNED NOT NULL DEFAULT 0,
date_attached            DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
                 PRIMARY KEY (bug_id, tag_id)
)TYPE=MyISAM;

ALTER TABLE mantis_tokens_table ADD  INDEX idx_typeowner  (type, owner);

INSERT INTO mantis_config_table ( value, type, access_reqd, config_id, project_id, user_id ) VALUES ('63', 1, 90, 'database_version', 20, 0 );
mantis_upgrade_sql.txt (19,662 bytes)   

Activities

djcarr

djcarr

2008-01-03 23:42

reporter   ~0016543

The mysqldump command used was:

mysqldump --host=xxx --user=xxx --password=xxx mantisdb --hex-blob --skip-opt --result-file=outputfile.sql
giallu

giallu

2008-01-04 03:14

reporter   ~0016546

Well. the pre and post 1.1 schema looks identical.

Your error "1364: Field 'id' doesn't have a default value" looks suspicious because the id is an auto increment field so it should be added automatically on INSERTs.

All in all, I think it's a problem with your dump/restore (but I can't say where is the problem for sure).

Would you retry the dump omitting "--hex-blob --skip-opt" please? I'm asking this because those are the only options I never used ;)
thraxisp

thraxisp

2008-01-05 20:18

reporter   ~0016557

I retested this on a fresh database. The "--skip-opt' seems to lose the autoincrement status on all of the tables. Without it, the table dump looks good.

Is it possible that the table was created on one machine and restored on another using the dump?
djcarr

djcarr

2008-01-07 01:28

reporter   ~0016567

Hi... I investigated the mysqldump as well and found the same cause. To confirm, the fault is not with Mantis.

After more investigation I've come up with a set of good guidelines for dumping and importing fairly large mantis databases. I'll post them here in case they can help someone else.

  1. In MySQL Server's my.ini set the following. This allows file attachments greater than 1MB to be imported back in.

    [mysqld]
    max_allowed_packet=16M

  2. Dump the file attachments table alone in hex format.

mysqldump --host= --user= --password=*** --hex-blob --skip-extended-insert --result-file=mantisdb_files.sql mantisdb mantis_bug_file_table

Why? Because this one table can get very big and MySQL Server cannot reimport scripts more than about 50MB. So you can then split this table dump into multiple smaller files if needed.

  1. Dump the remainder of the database. These options ensure one line per row, again for ease of reading and splitting the dump if needed.

mysqldump --host= --user= --password=*** --skip-extended-insert --ignore-table=mantisdb.mantis_bug_file_table --result-file=mantisdb.sql mantisdb

  1. Import the files in sequence into the NEW database:

mysql --host= --user= --password=*** newmantisdb < mantisdb_files.sql

mysql --host= --user= --password=*** newmantisdb < mantisdb.sql

  1. Do NOT use the MySQL Query Browser GUI to import - use the command line as above. The Query Browser chokes badly on the many '/ escapings that mysqldump places in the bug text and descriptions.

Anyway hope that helps someone. You may want to close the issue ... thanks!
giallu

giallu

2008-01-07 06:41

reporter   ~0016571

Ok. closing.

I think your procedure could be helpful to other users; it would be nice if you can add it to the wiki, probably in the HOWTO section
thraxisp

thraxisp

2008-01-07 08:16

reporter   ~0016574

I added the notes to the wiki in http://www.mantisbt.org/wiki/doku.php/mantisbt:db_dump_restore