not faking 0001_initial migration

[Brian May]

I tend to think something is wrong with my application, but can't think of what.

(kg-manage is another name for manage.py)

(sid-amd64)root@aquitard:/# kg-manage  migrate
Operations to perform:
  Synchronize unmigrated apps: pipeline, djcelery, captcha, django_tables2, ajax_select, jsonfield
  Apply all migrations: karaage, methods, sessions, sites, contenttypes, kgsoftware, kgusage, kgapplications
Synchronizing apps without migrations:
  Creating tables...
  Installing custom SQL...
  Installing indexes...
Running migrations:
  Applying kgusage.0001_initial...Traceback (most recent call last):
  File "/usr/share/python-karaage/bin/kg-manage", line 7, in <module>
    management.execute_from_command_line()
  File "/usr/lib/python2.7/dist-packages/django/core/management/__init__.py", line 385, in execute_from_command_line
    utility.execute()
  File "/usr/lib/python2.7/dist-packages/django/core/management/__init__.py", line 377, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/lib/python2.7/dist-packages/django/core/management/base.py", line 288, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/usr/lib/python2.7/dist-packages/django/core/management/base.py", line 338, in execute
    output = self.handle(*args, **options)
  File "/usr/lib/python2.7/dist-packages/django/core/management/commands/migrate.py", line 160, in handle
    executor.migrate(targets, plan, fake=options.get("fake", False))
  File "/usr/lib/python2.7/dist-packages/django/db/migrations/executor.py", line 63, in migrate
    self.apply_migration(migration, fake=fake)
  File "/usr/lib/python2.7/dist-packages/django/db/migrations/executor.py", line 97, in apply_migration
    migration.apply(project_state, schema_editor)
  File "/usr/lib/python2.7/dist-packages/django/db/migrations/migration.py", line 107, in apply
    operation.database_forwards(self.app_label, schema_editor, project_state, new_state)
  File "/usr/lib/python2.7/dist-packages/django/db/migrations/operations/models.py", line 36, in database_forwards
    schema_editor.create_model(model)
  File "/usr/lib/python2.7/dist-packages/django/db/backends/schema.py", line 270, in create_model
    self.execute(sql, params)
  File "/usr/lib/python2.7/dist-packages/django/db/backends/schema.py", line 98, in execute
    cursor.execute(sql, params)
  File "/usr/lib/python2.7/dist-packages/django/db/backends/utils.py", line 65, in execute
    return self.cursor.execute(sql, params)
  File "/usr/lib/python2.7/dist-packages/mysql/connector/django/base.py", line 133, in execute
    return self._execute_wrapper(self.cursor.execute, query, args)
  File "/usr/lib/python2.7/dist-packages/mysql/connector/django/base.py", line 116, in _execute_wrapper
    utils.ProgrammingError(err.msg), sys.exc_info()[2])
  File "/usr/lib/python2.7/dist-packages/mysql/connector/django/base.py", line 113, in _execute_wrapper
    return method(query, args)
  File "/usr/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 494, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 683, in cmd_query
    statement))
  File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 601, in _handle_result
    raise errors.get_exception(packet)
django.db.utils.ProgrammingError: Table 'cpu_job' already exists

I compared the schema of the cpu_job table on the database (mysql command "SHOW CREATE TABLE cpu_job") with the output from "kg-manage sqlmigrate kgusage 0001", they look identical apart from some differences in ordering.

Other apps did the right thing without any problems.

[Markus Holtermann]

Can you share some code parts, e.g. the cpu.Job model and the output of the SHOW CREATE TABLE cpu_job command, please.

[Brian May]

Yes, sure. Sorry wasn't really sure what information would be useful here; I find sometimes posting too much information can distract from the real cause of the problem.

It occurred to me that maybe CPUJob isn't the problem, maybe Django think's another table in the same models file is somehow wrong, and decides it needs to rerun the entire migration. Wish there was an easy way to find out why the test to fake the migration appears to have failed.

It is very possible that the previous generation of south migrations have introduced minor differences compared with the db models that have gone unnoticed. Don't know an easy way of finding out however. Comparing show create table with manage.py sqlmigrate is a tedious error prone process.

I will continue investigating this possibility however.

From ​https://github.com/Karaage-Cluster/karaage-usage/blob/master/kgusage/models.py:

@python_2_unicode_compatible
class CPUJob(models.Model):
    account = models.ForeignKey(Account, blank=True, null=True)
    username = models.CharField(max_length=50, blank=True, null=True)
    project = models.ForeignKey(Project, null=True, blank=True)
    machine = models.ForeignKey(Machine, blank=True, null=True)
    date = models.DateField(db_index=True, blank=True, null=True)
    queue = models.ForeignKey(Queue, blank=True, null=True)
    cpu_usage = models.BigIntegerField(blank=True, null=True)
    mem = models.BigIntegerField(blank=True, null=True)
    vmem = models.BigIntegerField(blank=True, null=True)
    ctime = models.DateTimeField(blank=True, null=True)
    qtime = models.DateTimeField(blank=True, null=True)
    etime = models.DateTimeField(blank=True, null=True)
    start = models.DateTimeField(blank=True, null=True)
    act_wall_time = models.BigIntegerField(blank=True, null=True)
    est_wall_time = models.BigIntegerField(blank=True, null=True)
    jobid = models.CharField(max_length=50, blank=True, null=True, unique=True)
    cores = models.BigIntegerField(blank=True, null=True)
    list_mem = models.BigIntegerField(blank=True, null=True)
    list_pmem = models.BigIntegerField(blank=True, null=True)
    list_vmem = models.BigIntegerField(blank=True, null=True)
    list_pvmem = models.BigIntegerField(blank=True, null=True)
    exit_status = models.BigIntegerField(blank=True, null=True)
    jobname = models.CharField(max_length=256, blank=True, null=True)
    software = models.ManyToManyField(SoftwareVersion, blank=True, null=True)

    class Meta:
        ordering = ['-date']
        db_table = 'cpu_job'

dbshell:

CREATE TABLE `cpu_job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_id` int(11) DEFAULT NULL,
  `username` varchar(50) DEFAULT NULL,
  `machine_id` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  `queue_id` varchar(50) DEFAULT NULL,
  `cpu_usage` bigint(20),
  `mem` bigint(20),
  `vmem` bigint(20),
  `ctime` datetime DEFAULT NULL,
  `qtime` datetime DEFAULT NULL,
  `etime` datetime DEFAULT NULL,
  `start` datetime DEFAULT NULL,
  `act_wall_time` bigint(20),
  `est_wall_time` bigint(20),
  `jobid` varchar(50) DEFAULT NULL,
  `cores` bigint(20),
  `list_mem` bigint(20),
  `list_pmem` bigint(20),
  `list_vmem` bigint(20),
  `list_pvmem` bigint(20),
  `exit_status` bigint(20),
  `jobname` varchar(256),
  `project_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `jobid` (`jobid`),
  KEY `cpu_job_6340c63c` (`account_id`),
  KEY `cpu_job_dbaea34e` (`machine_id`),
  KEY `cpu_job_c80a9385` (`queue_id`),
  KEY `cpu_job_eeede814` (`date`),
  KEY `cpu_job_f5be4463` (`project_id`),
  CONSTRAINT `machine_id_refs_id_3faed53c` FOREIGN KEY (`machine_id`) REFERENCES `machine` (`id`),
  CONSTRAINT `queue_id_refs_name_597286bc` FOREIGN KEY (`queue_id`) REFERENCES `queue` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

kg-manage sqlmigrate kgusage 0001 (contains everything from the above models file; I have not tried to remove the bits that don't look relevant:

BEGIN;
CREATE TABLE `cpu_job` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `username` varchar(50) NULL, `date` date NULL, `cpu_usage` bigint NULL, `mem` bigint NULL, `vmem` bigint NULL, `ctime` datetime NULL, `qtime` datetime NULL, `etime` datetime NULL, `start` datetime NULL, `act_wall_time` bigint NULL, `est_wall_time` bigint NULL, `jobid` varchar(50) NULL UNIQUE, `cores` bigint NULL, `list_mem` bigint NULL, `list_pmem` bigint NULL, `list_vmem` bigint NULL, `list_pvmem` bigint NULL, `exit_status` bigint NULL, `jobname` varchar(256) NULL, `account_id` integer NULL, `machine_id` integer NULL, `project_id` integer NULL);
CREATE TABLE `cache_institutecache` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `date` date NOT NULL, `start` date NOT NULL, `end` date NOT NULL, `cpu_time` numeric(30, 2) NOT NULL, `no_jobs` integer NOT NULL, `institute_id` integer NOT NULL, `machine_category_id` integer NOT NULL);
CREATE TABLE `cache_machinecache` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `date` date NOT NULL, `start` date NOT NULL, `end` date NOT NULL, `cpu_time` numeric(30, 2) NOT NULL, `no_jobs` integer NOT NULL, `machine_id` integer NOT NULL);
CREATE TABLE `cache_machinecategorycache` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `date` date NOT NULL, `start` date NOT NULL, `end` date NOT NULL, `cpu_time` numeric(30, 2) NOT NULL, `no_jobs` integer NOT NULL, `available_time` numeric(30, 2) NOT NULL, `machine_category_id` integer NOT NULL);
CREATE TABLE `cache_personcache` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `date` date NOT NULL, `start` date NOT NULL, `end` date NOT NULL, `cpu_time` numeric(30, 2) NOT NULL, `no_jobs` integer NOT NULL, `machine_category_id` integer NOT NULL, `person_id` integer NOT NULL, `project_id` integer NOT NULL);
CREATE TABLE `cache_projectcache` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `date` date NOT NULL, `start` date NOT NULL, `end` date NOT NULL, `cpu_time` numeric(30, 2) NOT NULL, `no_jobs` integer NOT NULL, `machine_category_id` integer NOT NULL, `project_id` integer NOT NULL);
CREATE TABLE `queue` (`name` varchar(50) NOT NULL PRIMARY KEY, `description` varchar(200) NULL);
CREATE TABLE `kgusage_usedmodules` (`jobid` varchar(100) NOT NULL PRIMARY KEY, `date_added` date NOT NULL, `modules` longtext NOT NULL);
ALTER TABLE `cache_projectcache` ADD CONSTRAINT cache_projectcache_date_43507e13557983c6_uniq UNIQUE (`date`, `start`, `end`, `project_id`, `machine_category_id`);
ALTER TABLE `cache_personcache` ADD CONSTRAINT cache_personcache_date_28991f5c68ceb1b8_uniq UNIQUE (`date`, `start`, `end`, `person_id`, `project_id`, `machine_category_id`);
ALTER TABLE `cache_machinecategorycache` ADD CONSTRAINT cache_machinecategorycache_date_1dabed278a3e35fe_uniq UNIQUE (`date`, `start`, `end`, `machine_category_id`);
ALTER TABLE `cache_machinecache` ADD CONSTRAINT cache_machinecache_date_461b5ee803038030_uniq UNIQUE (`date`, `start`, `end`, `machine_id`);
ALTER TABLE `cache_institutecache` ADD CONSTRAINT cache_institutecache_date_204cb3af944379a2_uniq UNIQUE (`date`, `start`, `end`, `institute_id`, `machine_category_id`);
ALTER TABLE `cpu_job` ADD COLUMN `queue_id` varchar(50) NULL;
ALTER TABLE `cpu_job` ALTER COLUMN `queue_id` DROP DEFAULT;
CREATE TABLE `cpu_job_software` (`id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY, `cpujob_id` integer NOT NULL, `softwareversion_id` integer NOT NULL, UNIQUE (`cpujob_id`, `softwareversion_id`));
CREATE INDEX cpu_job_5fc73231 ON `cpu_job` (`date`);
CREATE INDEX cpu_job_8a089c2a ON `cpu_job` (`account_id`);
ALTER TABLE `cpu_job` ADD CONSTRAINT cpu_job_account_id_1eada5cc22642a5b_fk_account_id FOREIGN KEY (`account_id`) REFERENCES `account` (`id`);
CREATE INDEX cpu_job_a9374927 ON `cpu_job` (`machine_id`);
ALTER TABLE `cpu_job` ADD CONSTRAINT cpu_job_machine_id_3a0539fe28c45657_fk_machine_id FOREIGN KEY (`machine_id`) REFERENCES `machine` (`id`);
CREATE INDEX cpu_job_b098ad43 ON `cpu_job` (`project_id`);
ALTER TABLE `cpu_job` ADD CONSTRAINT cpu_job_project_id_448e0b8265044069_fk_project_id FOREIGN KEY (`project_id`) REFERENCES `project` (`id`);
CREATE INDEX cache_institutecache_7c818b67 ON `cache_institutecache` (`institute_id`);
ALTER TABLE `cache_institutecache` ADD CONSTRAINT cache_institutecac_institute_id_6f1ba2c4a51fab86_fk_institute_id FOREIGN KEY (`institute_id`) REFERENCES `institute` (`id`);
CREATE INDEX cache_institutecache_c141adf7 ON `cache_institutecache` (`machine_category_id`);
ALTER TABLE `cache_institutecache` ADD CONSTRAINT cach_machine_category_id_3d08974bafe72ae0_fk_machine_category_id FOREIGN KEY (`machine_category_id`) REFERENCES `machine_category` (`id`);
CREATE INDEX cache_machinecache_a9374927 ON `cache_machinecache` (`machine_id`);
ALTER TABLE `cache_machinecache` ADD CONSTRAINT cache_machinecache_machine_id_34e33f6df1c9ac6e_fk_machine_id FOREIGN KEY (`machine_id`) REFERENCES `machine` (`id`);
CREATE INDEX cache_machinecategorycache_c141adf7 ON `cache_machinecategorycache` (`machine_category_id`);
ALTER TABLE `cache_machinecategorycache` ADD CONSTRAINT cach_machine_category_id_5903b325a186eb60_fk_machine_category_id FOREIGN KEY (`machine_category_id`) REFERENCES `machine_category` (`id`);
CREATE INDEX cache_personcache_c141adf7 ON `cache_personcache` (`machine_category_id`);
ALTER TABLE `cache_personcache` ADD CONSTRAINT cach_machine_category_id_271be448f4d2cab3_fk_machine_category_id FOREIGN KEY (`machine_category_id`) REFERENCES `machine_category` (`id`);
CREATE INDEX cache_personcache_a8452ca7 ON `cache_personcache` (`person_id`);
ALTER TABLE `cache_personcache` ADD CONSTRAINT cache_personcache_person_id_3b95f1b58ce21b62_fk_person_id FOREIGN KEY (`person_id`) REFERENCES `person` (`id`);
CREATE INDEX cache_personcache_b098ad43 ON `cache_personcache` (`project_id`);
ALTER TABLE `cache_personcache` ADD CONSTRAINT cache_personcache_project_id_4050581de967b3f5_fk_project_id FOREIGN KEY (`project_id`) REFERENCES `project` (`id`);
CREATE INDEX cache_projectcache_c141adf7 ON `cache_projectcache` (`machine_category_id`);
ALTER TABLE `cache_projectcache` ADD CONSTRAINT cach_machine_category_id_1e684fabf3e27e10_fk_machine_category_id FOREIGN KEY (`machine_category_id`) REFERENCES `machine_category` (`id`);
CREATE INDEX cache_projectcache_b098ad43 ON `cache_projectcache` (`project_id`);
ALTER TABLE `cache_projectcache` ADD CONSTRAINT cache_projectcache_project_id_4590f0060c3b6882_fk_project_id FOREIGN KEY (`project_id`) REFERENCES `project` (`id`);
CREATE INDEX cpu_job_75249aa1 ON `cpu_job` (`queue_id`);
ALTER TABLE `cpu_job` ADD CONSTRAINT cpu_job_queue_id_2ab579768634049b_fk_queue_name FOREIGN KEY (`queue_id`) REFERENCES `queue` (`name`);
CREATE INDEX cpu_job_software_46f2e6e9 ON `cpu_job_software` (`cpujob_id`);
ALTER TABLE `cpu_job_software` ADD CONSTRAINT cpu_job_software_cpujob_id_33edd7cbdd1b84a3_fk_cpu_job_id FOREIGN KEY (`cpujob_id`) REFERENCES `cpu_job` (`id`);
CREATE INDEX cpu_job_software_8766df73 ON `cpu_job_software` (`softwareversion_id`);
ALTER TABLE `cpu_job_software` ADD CONSTRAINT cpu_j_softwareversion_id_3477a0c114946dce_fk_software_version_id FOREIGN KEY (`softwareversion_id`) REFERENCES `software_version` (`id`);

COMMIT;

[Brian May]

I thought I had fixed it, but no luck:
​https://github.com/Karaage-Cluster/karaage-usage/commit/7f4c53a1539a52d0e85fe864c41b1db3bb8012b3

I found sqldiff, part of django-extensions. It says:

-- Detecting notnull changes not implemented for this database backend

BEGIN;
-- Application: kgusage
-- Model: CPUJob_software
ALTER TABLE `cpu_job_software`
        MODIFY `id` integer AUTO_INCREMENT;
-- Model: CPUJob
ALTER TABLE `cpu_job`
        MODIFY `id` integer AUTO_INCREMENT;
-- Model: InstituteCache
ALTER TABLE `cache_institutecache`
        MODIFY `id` integer AUTO_INCREMENT;
-- Model: ProjectCache
ALTER TABLE `cache_projectcache`
        MODIFY `id` integer AUTO_INCREMENT;
-- Model: PersonCache
ALTER TABLE `cache_personcache`
        MODIFY `id` integer AUTO_INCREMENT;
-- Model: MachineCategoryCache
ALTER TABLE `cache_machinecategorycache`
        MODIFY `id` integer AUTO_INCREMENT;
-- Model: MachineCache
ALTER TABLE `cache_machinecache`
        MODIFY `id` integer AUTO_INCREMENT;
COMMIT;

As far as I can tell, these changes are false positives (maybe as a result of int vs integer), and do not affect the migrations in anyway. All my tables have minor differences like this, including in apps where the migrations are correctly faked.

[Brian May]

I kind of feel like a dope. I made the change to the db model, but completely forgot to regenerate the migration.

Anyway, to debug this type of issue, see django/db/migrations/executor.py, in particular the detect_soft_applied function. This function returns True if the migration is already applied and should be skipped.

In my case, it was returning false because it could not find one of the tables. If I understand the code correctly, if one of the tables is missing it will try to run the entire migration. Maybe it should really error out if at least one of the tables exists but not all of them do?

Anyway, this is the code that was returning False for me.

if model._meta.db_table not in self.connection.introspection.get_table_list(self.connection.cursor()):
    return False

I believe I have solved this issue now (I still need to double check this, but my tests are now failing for unrelated reasons) so I don't particular care what happens with this ticket, however I think this is a problem others are likely to encounter, so recommend Django do something to try and make it more obvious what is going on.

[Markus Holtermann]

Thanks for the update, brian. I didn't have time to digg into the problem today, but I try to have a look at it tomorrow.

[Aymeric Augustin]

Reading through the ticket again, I have a hard time telling what improvements could be made in Django.

If someone can describe more precisely this issue, provide instructions to reproduce it, and perhaps propose a solution, please reopen. Thanks!

[Brian May]

Hello,

Just noticed I hadn't replied to this for some time.

I would suggest that some sort of verbose/debugging output would be very helpful. So you can see why Django isn't faking the migration.

e.g. "Not faking migration as expected table XYZ does not exist."

So it is possible to find out exactly what is going wrong without resorting to hacking Django, as I previously did.

(For me it is most likely undetected errors in south migrations that only occur under limited conditions that actually cause this)

Thanks