search

openhatch / oh-mainline

The code that runs openhatch.org
http://openhatch.org
GNU Affero General Public License v3.0
241 stars 310 forks source link

It would be nice if the migrations ran on MySQL 5.5 #862

Closed imported-from-roundup closed 10 years ago

imported-from-roundup commented 10 years ago

Comment by paulproteus:

Mkman on IRC reports that, with MySQL 5.5, the migrations fail with a duplicate column name error.

The next step is to install MySQL 5.5 somewhere and see if the migrations work. If so, then we're confused. If they fail, then we can debug the problem and commit a fix.

Comment by paulproteus:

I'm taking a look at this inside a virtual machine to see if I can reproduce it.

Getting MySQL running on a Debian virtual machine has been slow going, but I hope to be able to at least say "yes"/"no" that I can reproduce this within a day.

Comment by paulproteus:

The good news is that I can reproduce this problem. Within a Debian VM that runs MySQL 5.5:

Error in migration: search:0034_answer_points_to_project_and_so_does_question Traceback (most recent call last): File "./bin/mysite", line 87, in <module> djangorecipe.manage.main('mysite.settings') File "/vagrant/eggs/djangorecipe-0.23.1-py2.6.egg/djangorecipe/manage.py", line 16, in main management.execute_manager(mod) File "/vagrant/parts/mysite/django/core/management/init.py", line 438, in execute_manager utility.execute() File "/vagrant/parts/mysite/django/core/management/init.py", line 379, in execute self.fetch_command(subcommand).run_from_argv(self.argv) File "/vagrant/parts/mysite/django/core/management/base.py", line 191, in run_from_argv self.execute(_args, _options.dict) File "/vagrant/parts/mysite/django/core/management/base.py", line 220, in execute output = self.handle(_args, _options) File "/vagrant/eggs/South-0.7.3.1-py2.6.egg/south/management/commands/migrate.py", line 107, in handle ignore_ghosts = ignore_ghosts, File "/vagrant/eggs/South-0.7.3.1-py2.6.egg/south/migration/init.py", line 191, in migrate_app success = migrator.migrate_many(target, workplan, database) File "/vagrant/eggs/South-0.7.3.1-py2.6.egg/south/migration/migrators.py", line 222, in migrate_many result = migrator.class.migrate_many(migrator, target, migrations, database) File "/vagrant/eggs/South-0.7.3.1-py2.6.egg/south/migration/migrators.py", line 293, in migrate_many result = self.migrate(migration, database) File "/vagrant/eggs/South-0.7.3.1-py2.6.egg/south/migration/migrators.py", line 126, in migrate result = self.run(migration) File "/vagrant/eggs/South-0.7.3.1-py2.6.egg/south/migration/migrators.py", line 100, in run return self.run_migration(migration) File "/vagrant/eggs/South-0.7.3.1-py2.6.egg/south/migration/migrators.py", line 81, in run_migration migration_function() File "/vagrant/eggs/South-0.7.3.1-py2.6.egg/south/migration/migrators.py", line 57, in <lambda> return (lambda: direction(orm)) File "/vagrant/mysite/search/migrations/0034_answer_points_to_project_and_so_does_question.py", line 30, in forwards db.delete_column('search_projectinvolvementquestion', 'project_id') File "/vagrant/eggs/South-0.7.3.1-py2.6.egg/south/db/generic.py", line 36, in _column_rm return func(self, table, column, _args, _opts) File "/vagrant/eggs/South-0.7.3.1-py2.6.egg/south/db/mysql.py", line 133, in delete_column super(DatabaseOperations, self).delete_column(table_name, name) File "/vagrant/eggs/South-0.7.3.1-py2.6.egg/south/db/generic.py", line 36, in _column_rm return func(self, table, column, _args, _opts) File "/vagrant/eggs/South-0.7.3.1-py2.6.egg/south/db/generic.py", line 779, in delete_column self.execute(self.delete_column_string % params, []) File "/vagrant/eggs/South-0.7.3.1-py2.6.egg/south/db/generic.py", line 214, in execute cursor.execute(sql, params) File "/vagrant/parts/mysite/django/db/backends/util.py", line 15, in execute return self.cursor.execute(sql, params) File "/vagrant/parts/mysite/django/db/backends/mysql/base.py", line 86, in execute return self.cursor.execute(query, args) File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 166, in execute self.errorhandler(self, exc, value) File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 35, in defaulterrorhandler raise errorclass, errorvalue _mysql_exceptions.OperationalError: (1025, "Error on rename of './oh_milestone_a/#sql-4d98_2c' to './oh_milestone_a/search_projectinvolvementquestion' (errno: 150)")

So hopefully that means I can fix it. Stay tuned for that.

Comment by paulproteus:

LATEST FOREIGN KEY ERROR indicates:

110826 5:24:53 Error in foreign key constraint of table oh_milestone_a/search_projectinvolvementquestion: there is no index in the table which would contain the columns as the first columns, or the data types in the table do not match the ones in the referenced table or one of the ON ... SET NULL columns is declared NOT NULL. Constraint: , CONSTRAINT "project_id_refs_id_30837ad" FOREIGN KEY ("project_id") REFERENCES "search_project" ("id") InnoDB: Renaming table oh_milestone_a.<result 2 when explaining filename '#sql-4d98_2c'> to oh_milestone_a.search_projectinvolvementquestion failed!

Comment by paulproteus:

I'm not going to work on this for a while (more than 10 days), and so I wanted to leave the bug in good shape in case anyone else wants to come by and take a look at it.

The core of the problem is that MySQL gives the "errno 150" and "Renaming table... failed" messages when there is a foreign key constraint that is not being respected.

The two things that we do that trigger this problem are:

  • Add foreign keys connections between models
  • Drop foreign key connections between models

The good news it's easy to fix the "Add foreign keys" cases. The migrations that we've created in the past used version 1 of the South API. If we call the (basically) same method in version 2 of the South API, we can fix the issue. Here's a sample delta that fixes one such problem:

+from south.v2 import SchemaMigration

-class Migration: +class Migration(SchemaMigration):

 def forwards(self, orm):

     # Adding field 'Answer.project'
  • db.add_column('search_answer', 'project', orm['search.answer:project'])
  • db.add_column('search_answer', 'project', self.gf('django.db.models.fields.related.ForeignKey')(default=None, to=orm['search.Project']), keep_default=False)

To fix all those, we should probably look for references to add_column through the migrations, and make sure they are using the new South API.

As for the second category of problem, we need to remove the foreign key constraint from the tables that are affected. I don't currently know a way to ask South to do that, but it is likely that such a way exists.

I'm attaching a format-patch file that contains my (extremely messy) attempts to fix this.

Comment by paulproteus:

This format-patch file contains some commits I referred-to in msg2384 .

Comment by paulproteus:

This probably already works, but needs testing.

Comment by paulproteus:

This is no longer very important because dev setups use sqlite now.

File at http://roundup-archive.openhatch.org/bugs/file388/original by paulproteus

Status: testing Nosy List: paulproteus Priority: wish Imported from roundup ID: 547 (view archived page) Last modified: 2011-11-14.02:11:55

ehashman commented 10 years ago

This appears to be closed already. May be of note for #1204.