checksetup.pl: Unknown column 'profiles.nickname'
Categories
(Bugzilla :: Installation & Upgrading, task)
Tracking
()
People
(Reporter: Martijn.Ras, Unassigned)
References
(Depends on 1 open bug)
Details
(Whiteboard: [blocker will fix])
When running checksetup.pl in an attempt to upgrade from bugzilla-5.1.2 to bugzilla-5.9.1 I get the following failure:
...
Adding new column 'owner_user_id' to the 'groups' table...
DBD::mysql::db selectrow_hashref failed: Unknown column 'profiles.nickname' in 'SELECT' [for Statement "SELECT profiles.userid,profiles.login_name,profiles.realname,profiles.mybugslink AS showmybugslink,profiles.disabledtext,profiles.disable_mail,profiles.extern_id,profiles.is_enabled,DATE_FORMAT(last_seen_date, '%Y-%m-%d') AS last_seen_date,profiles.password_change_required,profiles.password_change_reason,profiles.mfa,profiles.mfa_required_date,profiles.nickname,profiles.bounce_count,first_patch_reviewed_id,review_request_count,feedback_request_count,needinfo_request_count,comment_count,creation_ts,first_patch_bug_id,last_activity_ts,last_statistics_ts FROM profiles WHERE login_name = ?"] at /opt/bugzilla/bugzilla-5.9.1/Bugzilla/DB.pm line 71.
|
||
Comment 1•6 months ago
|
||
This should be fixed when bug 1963773 lands.
|
|
||
Comment 2•6 months ago
•
|
||
(In reply to Dave Miller [:justdave] from comment #1)
This should be fixed when bug 1963773 lands.
Actually, if you're testing it, it would be great if you could try out the pull request on that bug and see how well it works for you.
|
Reporter | |
Comment 3•6 months ago
|
||
After adding this column to the table:
ALTER TABLE profiles ADD COLUMN nickname varchar(255) NOT NULL DEFAULT '' AFTER realname;
I got a similar error on 'bounce_count', which I also added:
ALTER TABLE profiles ADD COLUMN bounce_count int(1) NOT NULL DEAFULT 0;
Then i ran into the error:
Can't call method "is_valid" on an undefined value at /opt/bugzilla/bugzilla-5.9.1/Bugzilla/Config/Common.pm line 82.
which is fixed in this PR as follows:
https://github.com/bugzilla/harmony/pull/147/files#diff-774fa4e201c29120a16b714b2f4a989fa7e6d11869b95eeb11b03539c9550b04
Appearantly the column is used in the select statement before it is being created by this statement:
....
Adding new column 'nickname' to the 'profiles' table...
DBD::mysql::db do failed: Duplicate column name 'nickname' [for Statement "ALTER TABLE profiles ADD COLUMN nickname varchar(255) DEFAULT '' NOT NULL"] at /opt/bugzilla/bugzilla-5.9.1/Bugzilla/DB.pm line 71.
I'll try out to the pull request tomorrow.
|
|
Reporter | |
Comment 4•6 months ago
•
|
||
To work around the above failure on the column nickname, lines 774 and 775 in Bugzilla/Install/DB.pm can be commented out:
#$dbh->bz_add_column('profiles', 'nickname',
# {TYPE => 'varchar(255)', NOTNULL => 1, DEFAULT => "''"});
To work around the above failure on bounce_count, line 808 in Bugzilla/Install/DB.pm can be commented out:
#$dbh->bz_add_column('profiles', 'bounce_count', {TYPE => 'INT1', NOTNULL => 1, DEFAULT => 0});
|
|
Reporter | |
Comment 5•6 months ago
|
||
Ran into a bit of trouble with attach_id, commented out more lines in Bugzilla/Install/DB.pm:
263 #$dbh->bz_add_column("bugs_activity", "attach_id", {TYPE => 'INT5'});
...
810 # Bug 1588221 - dkl@mozilla.com
811 #$dbh->bz_alter_column('bugs_activity', 'attach_id', {TYPE => 'INT5'});
812 #$dbh->bz_alter_column('attachments', 'attach_id',
813 # {TYPE => 'BIGSERIAL', NOTNULL => 1, PRIMARYKEY => 1});
814 #$dbh->bz_alter_column('attach_data', 'id',
815 # {TYPE => 'INT5', NOTNULL => 1, PRIMARYKEY => 1});
816 #$dbh->bz_alter_column('flags', 'attach_id', {TYPE => 'INT5'});
Updated the DB by hand:
ALTER TABLE attach_data DROP CONSTRAINT fk_attach_data_id_attachments_attach_id;
ALTER TABLE attachments DROP CONSTRAINT fk_attachments_bug_id_bugs_bug_id;
ALTER TABLE bugs_activity DROP CONSTRAINT fk_bugs_activity_attach_id_attachments_attach_id;
ALTER TABLE flags DROP CONSTRAINT fk_flags_attach_id_attachments_attach_id;
ALTER TABLE attach_data CHANGE COLUMN attach_id attach_id bigint(20);
ALTER TABLE attachments CHANGE COLUMN attach_id attach_id bigint(20);
ALTER TABLE bugs_activity CHANGE COLUMN attach_id attach_id bigint(20);
ALTER TABLE flags CHANGE COLUMN attach_id attach_id bigint(20);
ALTER TABLE attach_data ADD CONSTRAINT fk_attach_data_id_attachments_attach_id FOREIGN KEY (id) REFERENCES attachments (attach_id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE attachments ADD CONSTRAINT fk_attachments_bug_id_bugs_bug_id FOREIGN KEY (bug_id) REFERENCES bugs (bug_id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE bugs_activity ADD CONSTRAINT fk_bugs_activity_attach_id_attachments_attach_id FOREIGN KEY (attach_id) REFERENCES attachments (attach_id) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE flags ADD CONSTRAINT fk_flags_attach_id_attachments_attach_id FOREIGN KEY (attach_id) REFERENCES attachments (attach_id) ON DELETE CASCADE ON UPDATE CASCADE;
|
|
Reporter | |
Comment 6•6 months ago
|
||
Ran into upgrade issues around profiles_emails, fixed them by adding the following lines in Bugzilla/Install/DB.pm:
820 # Bug 1963773 - topunixguy@gmail.com
821 _copy_valid_emails_to_profiles_emails();
...
4399 sub _copy_valid_emails_to_profiles_emails {
4400 my $dbh = Bugzilla->dbh;
4401
4402 my ($total) = $dbh->selectrow_array("SELECT COUNT(*) FROM profiles");
4403 unless ($total) {
4404 print "Skipping profiles_emails population: no profiles to process.\n";
4405 return;
4406 }
4407
4408 # Check if 'email' column exists in 'profiles'
4409 my $columns = $dbh->selectcol_arrayref(
4410 "SHOW COLUMNS FROM profiles LIKE 'email'"
4411 );
4412 my $has_email_column = scalar(@$columns) > 0;
4413
4414 # Build SELECT statement dynamically
4415 my $select_sql = 'SELECT userid, login_name';
4416 $select_sql .= ', email' if $has_email_column;
4417 $select_sql .= ' FROM profiles';
4418
4419 my $select_sth = $dbh->prepare($select_sql);
4420 my $check_sth = $dbh->prepare('SELECT 1 FROM profiles_emails WHERE user_id = ?');
4421 my $insert_sth = $dbh->prepare('
4422 INSERT INTO profiles_emails (user_id, email, is_primary_email, display_order)
4423 VALUES (?, ?, 1, 1)
4424 ');
4425
4426 $select_sth->execute();
4427
4428 while (my $row = $select_sth->fetchrow_hashref) {
4429 my $user_id = $row->{userid};
4430
4431 # Skip if the user already has an entry
4432 $check_sth->execute($user_id);
4433 next if $check_sth->fetchrow_array;
4434
4435 my $email = $has_email_column ? $row->{email} : undef;
4436 my $login = $row->{login_name};
4437
4438 my $valid_email;
4439 if (defined $email && $email ne '' && validate_email_syntax($email)) {
4440 $valid_email = $email;
4441 } elsif (defined $login && $login ne '' && validate_email_syntax($login)) {
4442 $valid_email = $login;
4443 }
4444
4445 next unless defined $valid_email;
4446
4447 eval {
4448 $insert_sth->execute($user_id, $valid_email);
4449 };
4450 warn "Failed to insert email for user $user_id: $@" if $@;
4451 }
4452
4453 $select_sth->finish;
4454 $check_sth->finish;
4455 $insert_sth->finish;
4456 }
|
|
Reporter | |
Comment 7•6 months ago
|
||
Now running into this failure around bugmail_filters:
Adding foreign key: bugmail_filters.user_id -> profiles.userid...
DBD::mysql::db do failed: Can't create table bugs.bugmail_filters (errno: 150 "Foreign key constraint is incorrectly formed") [for Statement "ALTER TABLE bugmail_filters ADD
CONSTRAINT fk_bugmail_filters_component_id_components_id FOREIGN KEY (component_id)
REFERENCES components(id)
ON UPDATE CASCADE ON DELETE CASCADE, ADD
CONSTRAINT fk_bugmail_filters_changer_id_profiles_userid FOREIGN KEY (changer_id)
REFERENCES profiles(userid)
ON UPDATE CASCADE ON DELETE CASCADE, ADD
CONSTRAINT fk_bugmail_filters_user_id_profiles_userid FOREIGN KEY (user_id)
REFERENCES profiles(userid)
ON UPDATE CASCADE ON DELETE CASCADE, ADD
CONSTRAINT fk_bugmail_filters_product_id_products_id FOREIGN KEY (product_id)
REFERENCES products(id)
ON UPDATE CASCADE ON DELETE CASCADE"] at /opt/bugzilla/harmony-main-TESTING/Bugzilla/DB.pm line 71.
Will continue later this week.
|
|
||
Comment 8•6 months ago
|
||
Just to be clear, these issues are happening after applying the topunix pull request?
|
|
Reporter | |
Comment 9•6 months ago
|
||
Those were on the original harmony-main codebase ...
I can confirm checksetup.pl from the top unix codebase works, Buzilla doesn't start because of an internal server error.
Here's the error_log:
[Wed Nov 19 11:23:14.654632 2025] [ssl:error] [pid 119515:tid 119515] AH02604: Unable to configure certificate 192.168.10.103:443:0 for stapling
[Wed Nov 19 11:23:27.172738 2025] [cgid:error] [pid 119527:tid 119594] [remote 31.187.132.239:50722] AH01215: stderr from /opt/bugzilla/bugzilla/index.cgi: Possible precedence problem between ! and string eq at Bugzilla/Bug.pm line 620.
[Wed Nov 19 11:23:27.301232 2025] [cgid:error] [pid 119527:tid 119594] [remote 31.187.132.239:50722] AH01215: stderr from /opt/bugzilla/bugzilla/index.cgi: Global symbol "$C" requires explicit package name (did you forget to declare "my $C"?) at /opt/bugzilla/bugzilla/index.cgi line 71.
[Wed Nov 19 11:23:27.301479 2025] [cgid:error] [pid 119527:tid 119594] [remote 31.187.132.239:50722] AH01215: stderr from /opt/bugzilla/bugzilla/index.cgi: Execution of /opt/bugzilla/bugzilla/index.cgi aborted due to compilation errors.
[Wed Nov 19 11:23:28.195174 2025] [cgid:error] [pid 119527:tid 119594] [remote 31.187.132.239:50722] End of script output before headers: index.cgi
[Wed Nov 19 11:26:57.309734 2025] [cgid:error] [pid 119524:tid 119599] [remote 31.187.132.239:51492] AH01215: stderr from /opt/bugzilla/bugzilla/index.cgi: [Wed Nov 19 11:26:57 2025] index.cgi: Possible precedence problem between ! and pattern match (m//) at Bugzilla/Template/Plugin/Hook.pm line 36.
Description
•