Hi, For anyone else who comes across this thread in future, I would like to report that I was able resolve this issue and proceed. All the details and observation are documented in the following issue and a resulting PR: https://github.com/openSUSE/open-build-service/issues/2574 https://github.com/openSUSE/open-build-service/pull/5337 To summarize, in case of a database that has been upgraded for a few OBS releases, the "name" column of "projects" table will not have the correct character set or collation options set. In 2.7.x, one of the database migrations sets these options only for "title" and "description" columns: https://github.com/openSUSE/open-build-service/blob/2.7/src/api/db/migrate/2... I found this by deploying a fresh 2.9 appliance and comparing both structure.sql and schema.rb files with my production database. Once I set the character set and collation options, migration continued. Regards, Srinidhi.
On Sunday, 08 July, 2018 at 11:26 AM, Srinidhi B <Srinidhi.BS@microfocus.com> wrote: Hello List,
I'm upgrading from OBS 2.7.1 to 2.9.3 and have followed steps mentioned in README.UPDATERS file. Database migration is failing when running the following migration:
== Schema ===================================================================== == 20170103132257 ChangeProjectPackageNameToString: migrating ================= -- execute("UPDATE projects SET name = SUBSTR(name, 1, 200)") -> 0.0073s -- execute("UPDATE projects SET name = \"\" WHERE name is null") -> 0.0002s -- execute("UPDATE packages SET name = SUBSTR(name, 1, 200)") -> 0.0351s -- execute("UPDATE packages SET name = \"\" WHERE name is null") -> 0.0239s -- change_column(:projects, :name, :string, {:limit=>200, :null=>false}) rails aborted! StandardError: An error has occurred, all later migrations canceled:
Mysql2::Error: Cannot delete rows from table which is parent in a foreign key constraint 'attribs_ibfk_3' of table 'attribs': ALTER TABLE `projects` CHANGE `name` `name` varchar(200) NOT NULL /srv/www/obs/api/db/migrate/20170103132257_change_project_package_name_to_st ring.rb:7:in `change' bin/rails:4:in `require' bin/rails:4:in `<main>'
Caused by: ActiveRecord::StatementInvalid: Mysql2::Error: Cannot delete rows from table which is parent in a foreign key constraint 'attribs_ibfk_3' of table 'attribs': ALTER TABLE `projects` CHANGE `name` `name` varchar(200) NOT NULL /srv/www/obs/api/db/migrate/20170103132257_change_project_package_name_to_st ring.rb:7:in `change' bin/rails:4:in `require' bin/rails:4:in `<main>'
Caused by: Mysql2::Error: Cannot delete rows from table which is parent in a foreign key constraint 'attribs_ibfk_3' of table 'attribs' /srv/www/obs/api/db/migrate/20170103132257_change_project_package_name_to_st ring.rb:7:in `change' bin/rails:4:in `require' bin/rails:4:in `<main>' Tasks: TOP => db:migrate:with_data (See full trace by running task with --trace)
Full output of rake db:migrate is available at the end of the email. Do I need to check or change something in the database for this migration to succeed?
Regards, Srinidhi.
# RAILS_ENV="production" rails.ruby2.5 db:migrate:with_data == Schema ===================================================================== == 20160610105300 SimplifyUserStates: migrating =============================== -- execute("ALTER TABLE users add column new_state enum('unconfirmed', 'confirmed', 'locked', 'deleted') DEFAULT 'unconfirmed'") -> 0.0452s -- execute("UPDATE users SET new_state = 'confirmed' where state = 2;") -> 0.0034s -- execute("UPDATE users SET new_state = 'locked' where state = 3;") -> 0.0012s -- execute("UPDATE users SET new_state = 'deleted' where state = 4;") -> 0.0006s -- execute("UPDATE users SET new_state = 'confirmed' where state = 6;") -> 0.0006s -- remove_column(:users, :state) -> 0.0378s -- rename_column(:users, :new_state, :state) -> 0.0063s == 20160610105300 SimplifyUserStates: migrated (0.0957s) ======================
== Schema ===================================================================== == 20160808135426 PlannedReleaseAttribute: migrating ========================== == 20160808135426 PlannedReleaseAttribute: migrated (0.1313s) =================
== Schema ===================================================================== == 20160819105300 Subaccount: migrating ======================================= -- execute("alter table users modify column `state` enum('unconfirmed','confirmed','locked','deleted','subaccount') DEFAULT 'unconfirmed';") -> 0.0008s -- add_column(:users, :owner_id, :integer) -> 0.0663s == 20160819105300 Subaccount: migrated (0.0672s) ==============================
== Schema ===================================================================== == 20160824132643 FixBsRequestCounter: migrating ============================== -- change_column_default(:bs_request_counter, :counter, 1) -> 0.0021s == 20160824132643 FixBsRequestCounter: migrated (0.0496s) =====================
== Schema ===================================================================== == 20161019231042 AddApiUrlToConfiguration: migrating ========================= -- add_column(:configurations, :api_url, :string) -> 0.0185s == 20161019231042 AddApiUrlToConfiguration: migrated (0.0186s) ================
== Schema ===================================================================== == 20161025231042 AddReleasename: migrating =================================== -- add_column(:packages, :releasename, :string) -> 0.5109s == 20161025231042 AddReleasename: migrated (0.5110s) ==========================
== Schema ===================================================================== == 20161115094035 AddCommentableToComments: migrating ========================= -- add_reference(:comments, :commentable, {:type=>:integer, :polymorphic=>true, :index=>true}) -> 0.1489s == 20161115094035 AddCommentableToComments: migrated (0.1490s) ================
== Schema ===================================================================== == 20161117135426 ExtendXamarinMatching: migrating ============================ == 20161117135426 ExtendXamarinMatching: migrated (0.0593s) ===================
== Schema ===================================================================== == 20161121162349 AddLinkOutsideToProjects: migrating ========================= -- add_column(:projects, :url, :string, {:null=>true}) -> 0.0878s == 20161121162349 AddLinkOutsideToProjects: migrated (0.0879s) ================
== Schema ===================================================================== == 20161122121211 MoveCommentsData: migrating ================================= == 20161122121211 MoveCommentsData: migrated (0.5931s) ========================
== Schema ===================================================================== == 20161122123708 RemoveOldRelationsFromComments: migrating =================== -- remove_reference(:comments, :project, {:index=>true, :foreign_key=>true}) -> 0.0542s -- remove_reference(:comments, :package, {:index=>true, :foreign_key=>true}) -> 0.0444s -- remove_reference(:comments, :bs_request, {:index=>true}) -> 0.0330s -- remove_column(:comments, :type, :string) -> 0.0359s == 20161122123708 RemoveOldRelationsFromComments: migrated (0.1681s) ==========
== Schema ===================================================================== == 20161123124803 ImageTemplatesAttribute: migrating ========================== == 20161123124803 ImageTemplatesAttribute: migrated (0.0123s) =================
== Schema ===================================================================== == 20161124133941 DatetimeNotZeroDefaultNull: migrating ======================= -- execute("alter table packages modify created_at datetime default NULL;") -> 0.0008s -- execute("alter table packages modify updated_at datetime default NULL;") -> 0.0007s -- execute("alter table projects modify created_at datetime default NULL;") -> 0.0007s -- execute("alter table projects modify updated_at datetime default NULL;") -> 0.0012s == 20161124133941 DatetimeNotZeroDefaultNull: migrated (0.0035s) ==============
== Schema ===================================================================== == 20161124135426 DropObsoleteTable: migrating ================================ -- drop_table(:bs_request_histories) == 20161124135426 DropObsoleteTable: migrated (0.0006s) =======================
== Schema ===================================================================== == 20161128115942 AddWhenAttributeToBsRequest: migrating ====================== -- add_column(:bs_requests, :updated_when, :datetime) -> 0.5583s == 20161128115942 AddWhenAttributeToBsRequest: migrated (1.0068s) =============
== Schema ===================================================================== == 20170103132257 ChangeProjectPackageNameToString: migrating ================= -- execute("UPDATE projects SET name = SUBSTR(name, 1, 200)") -> 0.0073s -- execute("UPDATE projects SET name = \"\" WHERE name is null") -> 0.0002s -- execute("UPDATE packages SET name = SUBSTR(name, 1, 200)") -> 0.0351s -- execute("UPDATE packages SET name = \"\" WHERE name is null") -> 0.0239s -- change_column(:projects, :name, :string, {:limit=>200, :null=>false}) rails aborted! StandardError: An error has occurred, all later migrations canceled:
Mysql2::Error: Cannot delete rows from table which is parent in a foreign key constraint 'attribs_ibfk_3' of table 'attribs': ALTER TABLE `projects` CHANGE `name` `name` varchar(200) NOT NULL /srv/www/obs/api/db/migrate/20170103132257_change_project_package_name_to_st ring.rb:7:in `change' bin/rails:4:in `require' bin/rails:4:in `<main>'
Caused by: ActiveRecord::StatementInvalid: Mysql2::Error: Cannot delete rows from table which is parent in a foreign key constraint 'attribs_ibfk_3' of table 'attribs': ALTER TABLE `projects` CHANGE `name` `name` varchar(200) NOT NULL /srv/www/obs/api/db/migrate/20170103132257_change_project_package_name_to_st ring.rb:7:in `change' bin/rails:4:in `require' bin/rails:4:in `<main>'
Caused by: Mysql2::Error: Cannot delete rows from table which is parent in a foreign key constraint 'attribs_ibfk_3' of table 'attribs' /srv/www/obs/api/db/migrate/20170103132257_change_project_package_name_to_st ring.rb:7:in `change' bin/rails:4:in `require' bin/rails:4:in `<main>' Tasks: TOP => db:migrate:with_data (See full trace by running task with --trace) -- To unsubscribe, e-mail: opensuse-buildservice+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-buildservice+owner@opensuse.org
-- To unsubscribe, e-mail: opensuse-buildservice+unsubscribe@opensuse.org To contact the owner, e-mail: opensuse-buildservice+owner@opensuse.org