[8.0.pre] Database migration fails

Hi,

I'm trying to upgrade from 7.14 to the codebase for 8.0 (1eb3dde4). During the migration task (I'm using mysql) the following error message appears:

bundle exec rake db:migrate RAILS_ENV=production --trace
** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Invoke db:load_config (first_time)
** Execute db:load_config
** Execute db:migrate

== 20150812080800 AddSettingsImportSources: migrating =========================
-- column_exists?(:application_settings, :import_sources)
   -> 0.0076s
-- add_column(:application_settings, :import_sources, :text)
   -> 0.3592s
-- execute("update application_settings set import_sources = '---\n- github\n- bitbucket\n- gitlab\n- gitorious\n- google_code\n- fogbugz\n- git\n'")
   -> 0.0254s
== 20150812080800 AddSettingsImportSources: migrated (0.3948s) ================

== 20150814065925 RemoveOauthTokensFromUsers: migrating =======================
-- remove_column(:users, :github_access_token, :string)
   -> 0.5187s
-- remove_column(:users, :gitlab_access_token, :string)
   -> 0.9718s
-- remove_column(:users, :bitbucket_access_token, :string)
   -> 0.4639s
-- remove_column(:users, :bitbucket_access_token_secret, :string)
   -> 0.4797s
== 20150814065925 RemoveOauthTokensFromUsers: migrated (2.4370s) ==============

== 20150817163600 DeduplicateUserIdentities: migrating ========================
-- execute("DROP TABLE IF EXISTS tt_migration_DeduplicateUserIdentities;")
   -> 0.0013s
-- execute("CREATE TEMPORARY TABLE tt_migration_DeduplicateUserIdentities AS SELECT id,provider,user_id FROM identities;")
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Access denied for user 'git'@'localhost' to database 'gitlabhq_production': CREATE TEMPORARY TABLE tt_migration_DeduplicateUserIdentities AS SELECT id,provider,user_id FROM identities;/home/git/gitlab/vendor/bundle/ruby/2.2.0/gems/activerecord-4.1.12/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:303:in `query'

But access is possible for git on localhost as I didn't change anything in the GitLab database configuration file:

mysql --host=localhost --user=git -p gitlabhq_production

works without any problems!

I can reproduce the error on mysql shell:

MariaDB [gitlabhq_production]> CREATE TEMPORARY TABLE tt_migration_DeduplicateUserIdentities AS SELECT id,provider,user_id FROM identities;
ERROR 1044 (42000): Access denied for user 'git'@'localhost' to database 'gitlabhq_production'

Can anyone reproduce this?

The official mysql installation guide from here grants no permission for creating temporary tables :( So this must be done manually before the migration process I guess?

mentioned in issue #2572 (closed)

The mysql install guide grants create and drop rights. So we could simply create a normal table in the migration and drop it afterwards.

I just looked at the migration. The table gets dropped afterwards already, so we only need to remove the word TEMPORARY from it :)

I totally agree with you @haynes, removing this "temporary" attribute should be fine. It is btw. only used in that migration script:

# grep -in "temporary" db/migrate/*
db/migrate/20150817163600_deduplicate_user_identities.rb:4:    execute 'CREATE TEMPORARY TABLE tt_migration_DeduplicateUserIdentities AS SELECT id,provider,user_id FROM identities;'

I'll create an MR for it when I'm home unless someone beats me to it 😄

👍

Separately, it may be nice to add TEMPORARY TABLES permissions so that we may use them in the future.

@stanhu I don't think that'll work because you won't catch all admins to update the mysql user. If we start using them, you will have many reports about failed migrations.
But it's not really a big issue. We can use normal tables.
The only advantage of Temporary tables in migrations is, that you don't need to drop them later.
So it only saves you 1 line of code, which I think is no problem to have here 😃

@haynes Temporary tables were introduced for a reason. If a failure happens during the migration, you have to be careful to rescue and drop the table. Also, if you reapply the migration, you have to be careful that it doesn't fail if the table already exists.

@stanhu yes. I understand that. I'm just saying that we can easily work around these things. But let's change the docs to include the necessary rights anyway.
Just expect a fair amount of bug reports when we actually start to use them :)

mentioned in merge request !1351 (merged)

Status changed to closed by commit 96790b1a

mentioned in commit 96790b1a

mentioned in merge request !2081 (closed)