Mailinglist Archive: opensuse-buildservice (70 mails)

< Previous Next >
Re: [opensuse-buildservice] Upgrade from 2.7.1 to 2.9.3 fails to migrate database
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/20121112104534_fix_projects_charset.rb

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@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxx
To contact the owner, e-mail: opensuse-buildservice+owner@xxxxxxxxxxxx



--
To unsubscribe, e-mail: opensuse-buildservice+unsubscribe@xxxxxxxxxxxx
To contact the owner, e-mail: opensuse-buildservice+owner@xxxxxxxxxxxx

< Previous Next >
This Thread
References