Table has no partition for value 735568

[gotlium]

from dbparti.admin import PartitionableAdmin
from dbparti.models import Partitionable
from django.contrib.auth.models import User
from django.contrib.admin import site

class Some(Partitionable):
    date = models.DateField()
    user = models.ForeignKey(User)

    class Meta(Partitionable.Meta):
        partition_type = 'range'
        partition_subtype = 'date'
        partition_range = 'month'
        partition_column = 'date'

class SomeAdmin(PartitionableAdmin):
    partition_show = 'all'

site.register(Some, SomeAdmin)

I'm got the following error: Table has no partition for value 735568 What does it mean?

Generated SQL:

ALTER TABLE app_some PARTITION BY RANGE (TO_DAYS(`date`))(
    PARTITION app_some_y0000m00 VALUES LESS THAN (0)
);

Env: MySQL 5.6.15, OS X

I try create partition manually. And all working fine. SQL:

ALTER TABLE app_some PARTITION BY RANGE COLUMNS (`date`) (
    PARTITION p2013 VALUES LESS THAN ('2013-01-01'),
    PARTITION p2014 VALUES LESS THAN ('2014-01-01')
);

What do you think about this?

[maxtepkeev]

What version of Django are you using ?

[gotlium]

1.4.x. But what's the difference? Python code working correctly. We are talking about sql errors.

[maxtepkeev]

Please try the 2be5522ac323747f77ed7ac6527d340c90a2a91a version. This should fix the problem you experienced.

[maxtepkeev]

Released v0.3.1 to PyPI with the fix. Please reopen if problem remains. Thanks.

[gotlium]

Fixes is not working. I get following message: django.db.utils.IntegrityError: (1217, 'Cannot delete or update a parent row: a foreign key constraint fails')

Ok, when field was changed to char, I get first message, on this issue: django.db.utils.OperationalError: (1526, 'Table has no partition for value 735602')

Any ideas? Can you install 5.6.x dist, and check it. Maybe that problem related for specific mysql version? Or give me a raw SQL to check, and after test, I will return my results.

PS - I can't reopen this issue. Can you do it by your self.

Additional information:

pip freeze

Django==1.6.1
MySQL-python==1.2.5
django-db-parti==0.3.1
wsgiref==0.1.2

[maxtepkeev]

The first error:

django.db.utils.IntegrityError: (1217, 'Cannot delete or update a parent row: a foreign key constraint fails')

tells you that MySQL doesn't support foreign keys in partitioned tables. This is a known limitation and we can do nothing about it. This limitation exists only for InnoDB database engine, so if you really need a FK in your table you can create it using MyISAM engine and it will work.

The second error:

django.db.utils.OperationalError: (1526, 'Table has no partition for value 735602')

means that MySQL can't find a partition for the value you've given to it, that also means that django-db-parti wasn't able to create the partition on the fly for some reason.

The first time you've reported the problem I was able to reproduce and fix it. Now I can't reproduce it, everything is working fine for me, this is the environment I currently have:

Python 2.7.6 MySQL 5.6.15 Django 1.6.1 mysql-python 1.2.5

I also have separate environments for Django 1.4.10 and Django 1.5.5, I also tried tables with MyISAM and InnoDB engines and I just can't reproduce this problem anymore.

So I need a few more details from you: what is your exact Python version and what database engine are you using (seems that it is InnoDB). Also give me your exact django model code and I will try to reproduce the problem again. Thanks.

[gotlium]

InnoDB, Python 2.7.5. That my test model:

from django.contrib.admin import site
from django.db import models
from dbparti.admin import PartitionableAdmin
from dbparti.models import Partitionable

class Users(Partitionable):
    date = models.DateField()
    user = models.CharField(max_length=100)

    class Meta(Partitionable.Meta):
        partition_type = 'range'
        partition_subtype = 'date'
        partition_range = 'month'
        partition_column = 'date'

class UsersAdmin(PartitionableAdmin):
    partition_show = 'all'

site.register(Users, UsersAdmin)

[maxtepkeev]

So, this is the setup I currently have (should be a copy of yours):

Python 2.7.5 MySQL 5.6.15 Django 1.6.1 mysql-python 1.2.5

Here are the steps I did:

1) created an empty database 2) created a new app named dbparti_test and put it into INSTALLED_APPS 3) created a file named models.py and put the code you provided inside 4) ran command python manage.py syncdb to create tables 5) ran command python manage.py partition dbparti_test to prepare table for partitioning 6) logged-in into django admin interface 7) successfully created a new record 8) checked database and the new partition was successfully created with the record inside it

Does your workflow differs from mine ? Also please try to completely reinstall django-db-parti and install again (pip uninstall django-db-parti, pip install django-db-parti). After reinstalling please restart Apache, uwsgi or whatever web server you are using, btw which one do you use ? If the problem still remains let me know and I'll tell you where to put print statements to see what's going on here. Thanks.

[gotlium]

Thx for quick response. Today I can't do it. But I'll do it tomorrow, and send report. But all steps is equal to your.

[gotlium]

Very strange. I recreate my model, after dropping table, and partition successfully created. Thanks. All is working. Good job:)

[maxtepkeev]

No problems, I'm glad you got it working. Strange things happen sometimes. Happy partitioning ;-)

[gotlium]

Thanks. About foreign key constraint. I remove all constraints and now partitioning working with InnoDB:)

[chand3040]

Hi @maxtepkeev I was just able to replicate the issue that I think @gotlium missed. When I try to create a partition on a table that already has data it says "django.db.utils.DatabaseError: (1526, 'Table has no partition for value 735831')" but again when we try the same thing in the same table with no data it works perfectly fine. I am using Django 1.4.12, Mysql 5.5.35 and Python 2.7.3.

Do let me know if I need to send you the model Details that I have implemented with.

Hoping for the reply at the earliest possible.

[maxtepkeev]

Hi @chand3040!

I don't think this is connected with @gotlium's issue.

Anyway, it is currently not possible to partition an existing table which already has data. You need to use the following workaround:

1) Create an empty table 2) Prepare it for partitioning as the manual says 3) Write a django command-line script which will transfer all the data from your existing non-partitioned table to your new partitioned table.

Also, please consider migrating to architect module, because django-db-parti is deprecated and won't be developed anymore. Architect is fully backwards compatible with django-db-parti.

Please let me know if you have more questions.