Gustavo Picon is sharing code with you

Bitbucket is a code hosting site. Unlimited public and private repositories. Free for small teams.

Don't show this again

tabo / django-treebeard

Efficient tree implementations for Django 1.0+ :: https://tabo.pe/projects/django-treebeard/

Clone this repository (size: 602.0 KB): HTTPS / SSH
hg clone https://bitbucket.org/tabo/django-treebeard
hg clone ssh://hg@bitbucket.org/tabo/django-treebeard

Issues

#25 Strange behavior in Django 1.2

Reported by Daniel Ostrowski (last edited )

Hello.

I am running official Django 1.2 and treebeard from the Mercurial repository. I also have the templates from treebeard listed in my templates directories.

I have the following model:

from treebeard.mp_tree import MP_Node

class ItemCategory(MP_Node):
    title = models.CharField(max_length=100)
    slug = models.SlugField(max_length=100, unique=True)
    is_active = models.BooleanField(default=True)

    node_order_by = ['title']

    def __unicode__(self):
        return self.title

When adding things in the interface, I will add like 2 categories. The first one is a child of root, and the 2nd one will be a child of the first.

However, when I do that, I notice that the display in the admin section only lists the first one. So I decided to see how it behaves on the command line. You will see the odd behavior here. I deleted all the objects from the command line then....

>>> ItemCategory.objects.all()
[]
>>> ItemCategory.add_root(title='CDs', slug='cds')
<ItemCategory: CDs>
>>> r = ItemCategory.objects.get(slug='cds')
>>> r
<ItemCategory: CDs>
>>> r.add_child(title='Music', slug='music')
<ItemCategory: Music>
>>> r = ItemCategory.objects.get(slug='cds')
>>> r.get_children()
[<ItemCategory: Music>]
# HERE I ADD A NEW ITEM "Instructional" IN THE ADMIN INTERFACE as a CHILD of "CDs"
>>> r = ItemCategory.objects.get(slug='cds')
>>> r.get_children()
[<ItemCategory: Music>]

So the item I add from the admin interface doesn't get properly added.

The database ends up looking like this.

}}}

#!SQL

mysql> select * from store_itemcategory;
+----+----------+-------+----------+---------------+---------------+-----------+
| id | path     | depth | numchild | title         | slug          | is_active |
+----+----------+-------+----------+---------------+---------------+-----------+
|  8 | 00010001 |     2 |        1 | Music         | music         |         1 |
|  7 | 0001     |     1 |        1 | CDs           | cds           |         1 |
|  9 | 1        |     3 |        0 | Instructional | instructional |         1 |
+----+----------+-------+----------+---------------+---------------+-----------+
3 rows in set (0.00 sec)


Any idea why this happens? It seems like a bug. My models are simple and I'm on an official Django release.

Status: resolved Responsible: Gustavo Picon Type: bug Priority: major
Milestone: none Component: none Version: none

Attachments

No attachments added for this issue yet.

Comments and changes

  1. #1 Daniel Ostrowski

    written

    • Changed content.
  2. #2 Daniel Ostrowski

    written

    • Changed content.
  3. #3 Daniel Ostrowski

    written

    • Changed content.
  4. #4 Daniel Ostrowski

    written

    • Changed content.
  5. #5 Gustavo Picon

    written

    • Changed status from new to open.

    This looks odd, will try to reproduce the problem here. What python version are you using?

  6. #6 Daniel Ostrowski

    written

    2.6.x

  7. #7 Gustavo Picon

    written

    Daniel,

    I just tried to reproduce this bug by copy&pasting your model, and doing _EXACTLY_ what you did and I had no problems. I tested sqlite, mysql innodb and postgresql. Used the latest release of Django (1.2.1). In all cases I had the correct results:

    >>> r.get_children()
    [<ItemCategory: Instructional>, <ItemCategory: Music>]
    
    mysql> SELECT * FROM tbexample_itemcategory;
    +----+----------+-------+----------+---------------+---------------+-----------+
    | id | path     | depth | numchild | title         | slug          | is_active |
    +----+----------+-------+----------+---------------+---------------+-----------+
    |  1 | 0001     |     1 |        2 | CDs           | cds           |         1 |
    |  2 | 00010003 |     2 |        0 | Music         | music         |         1 |
    |  3 | 00010002 |     2 |        0 | Instructional | instructional |         1 |
    +----+----------+-------+----------+---------------+---------------+-----------+
    3 rows in set (0.00 sec)
    

    Can you give me some more details? Do you have the latest version of treebeard from the repo? Did you update? Do you have local patches? Maybe extra methods in your model? (save, etc). What database are you using? Driver versions? Any detail can help :)

  8. #8 Daniel Ostrowski

    written , last edited

    • Changed status from open to on hold.

    Gustavo,

    Thanks for checking this out!

    I am using Django 1.2.1 (just double checked) and and tip, though I was using tip from about 8 days ago (specifically: http://bitbucket.org/tabo/django-treebeard/changeset/0a23f5cf9c9e).

    I will try some more experiments to see what I can find, so for now I will put this bug on hold until I can reproduce it consistently.

  9. #9 Daniel Ostrowski

    written

    • Changed status from on hold to invalid.

    Gustavo,

    I logged some SQL on my MySQL server and it seemed some of the operations that were performed through the admin resulted in some code which was concatenating paths using "||"

    So, in my case the problem was that "PIPES_AS_CONCAT" (here: http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html#sqlmode_pipes_as_concat) is usually turned off by default in my version of MySQL (5.1.37-1ubuntu5.1).

    Setting the "sql_mode" to "PIPES_AS_CONCAT" fixed the problem. I'm not sure if this is maybe something that could be mentioned prominently somewhere on the treebeard documentation? Or maybe I missed it somewhere?

    Anyhow, this explains the differences in behavior between command line "add_child" and the admin.

    I will mark this as closed, but please give consideration to the documentation if there's no mention of this issue, yet. :)

    Thanks,

    Dan

  10. #10 Gustavo Picon

    written , last edited

    • Changed status from invalid to open.

    If I had the graphic skills, I'd make one of those FFFFFFFUUUUUUUUUUUUUUU comics now, titled mysql rage.

    I was aware of PIPES_AS_CONCAT being unset by default, that's why I wrote this:

    http://www.bitbucket.org/tabo/django-treebeard/src/9364e9504bc2/treebeard/mp_tree.py#cl-857

            
            elif settings.DATABASE_ENGINE == 'mysql':
                # hooray for mysql ignoring standards in their default
                # configuration!
                # to make || work as it should, enable ansi mode
                # http://dev.mysql.com/doc/refman/5.0/en/ansi-mode.html
                sqlpath = "CONCAT(%s, SUBSTR(path, %s))"
            else:
                sqlpath = "%s||SUBSTR(path, %s)"
    

    So it seems that the problem is with the way I'm getting the database engine (settings.DATABASE_ENGINE). I'm testing with django 1.2.1 but sharing the same settings with django 1.0 and 1.1 (old-style-single-db settings). So maybe the problem lies with the way the DB is configured.

    Could you please share the database settings in your settings.py file? Mine are:

    (d12)tabo@mango:django-treebeard$ django-admin.py diffsettings | grep DATABASE
    DATABASE_ENGINE = 'mysql'
    DATABASE_NAME = 'treebeard_innodb'
    DATABASE_OPTIONS = {'init_command': 'SET storage_engine=INNODB,character_set_connection=utf8,collation_connection=utf8_unicode_ci'}
    DATABASE_USER = 'root'
    

    I'm reopening the ticket because it seems like this may be a treebeard bug.

  11. #11 Daniel Ostrowski

    written , last edited

    Well, I changed my MySQL installation's my.cnf to put the default storage engine and PIPES_AS_CONCAT in, so now it looks like this:

    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.mysql',
            'NAME': 'watershed_main',
            'USER': 'xxxx',
            'PASSWORD': 'xxxx',
            'HOST': '127.0.0.1', 
            'PORT': '', 
        }
    }
    

    ... and that's it. I don't have any options. But for Django 1.2.x the recommended 'ENGINE' entry is a longer qualifier than just 'mysql' so that's probably the issue.

    (On a stock Django 1.2 project, the db settings start with:

    'ENGINE' : 'django.db.backends.' # Add 'postgresql_psycopg2', 'postgresql', 'mysql', 'sqlite3' or 'oracle'.)

  12. #12 Gustavo Picon

    written , last edited

    Finally got time to work on this, quick update:

    Using django 1.2.1, running the tests with these settings:

    DATABASES = {
      'default': {
        'ENGINE': 'django.db.backends.mysql',
        'TEST_MIRROR': None,
        'NAME': 'treebeard_innodb',
        'TEST_CHARSET': None,
        'TIME_ZONE': 'America/Lima',
        'TEST_COLLATION': None,
        'OPTIONS': {
          'init_command': 'SET storage_engine=INNODB,character_set_connection=utf8,collation_connection=utf8_unicode_ci'
        },
        'HOST': '',
        'USER': 'root',
        'TEST_NAME': None,
        'PASSWORD': '',
        'PORT': ''
      }
    }
    

    got:

    #!
    
    Ran 651 tests in 59.756s
    
    FAILED (failures=14, errors=62)
    
    

    I'm checking south's code for ideas on how to handle introspection in 1.2 correctly (suggested in #django).

  13. #13 Gustavo Picon

    written

    • Changed status from open to resolved.

    Fixed in cbcb1e035117 . All tests now pass with new style settings.

    Closing ticket.

  14. #14 Anonymous

    written

    Hello,

    I found this (already closed ticket) while searching for a solution to the same problem, but with a different error message:

    Creating group WJAX10
    found parent: WJAX
    Traceback (most recent call last):
      File "X:\sites\spec\data\import-graph.py", line 39, in <module>
        group = parent.add_child(key=rec['key'])
      File "C:\opt\lang\Python25\lib\site-packages\treebeard\mp_tree.py", line 508, in add_child
        **kwargs)
      File "C:\opt\lang\Python25\lib\site-packages\treebeard\mp_tree.py", line 580, in add_sibling
        cursor.execute(sql, vals)
      File "c:\opt\lang\python25\Lib\site-packages\django\db\backends\util.py", line 15, in execute
        return self.cursor.execute(sql, params)
      File "c:\opt\lang\python25\Lib\site-packages\django\db\backends\mysql\base.py", line 86, in execute
        return self.cursor.execute(query, args)
      File "C:\opt\lang\Python25\lib\site-packages\MySQLdb\cursors.py", line 166, in execute
        self.errorhandler(self, exc, value)
      File "C:\opt\lang\Python25\lib\site-packages\MySQLdb\connections.py", line 35, in defaulterrorhandler
        raise errorclass, errorvalue
    _mysql_exceptions.OperationalError: (1292, "Truncated incorrect DOUBLE value: '0001000100020002000A'")
    
    

    In my case, I am scripting the addition of a number of graph items to preload my database. the error "Truncated incorrect DOUBLE value: '0001000100020002000A'" is happening because my the generated SQL

    UPDATE `graph_group` SET path='0001000100020002000B'||SUBSTR(path, 21) WHERE path LIKE '0001000100020002000A%'
    
    

    is failing -- the || is being used as OR instead of CONCAT. The exciting wrong behavior here is that || works fine for this statement until the path contains an alpha character. Once you get ten children nodes, it blows up as above.

    The error is actually saying that MySQL cannot make sense of the string '0001000100020002000A' as a DOUBLE which it can then use with OR.

    I know you already closed this ticket, but I'm including my error text since I didn't find this solution very easily via Google.

    As recommended above, the solution was to run MySQL in ANSI mode, which fixes PIPES_AS_CONCAT.

    Thanks for a great set of tools.

    Greg Peddle gpeddle@mettasoft.com

  15. #15 Greg Peddle

    written

    Hello,

    I found this (already closed ticket) while searching for a solution to the same problem, but with a different error message:

    Creating group WJAX10
    found parent: WJAX
    Traceback (most recent call last):
      File "X:\sites\spec\data\import-graph.py", line 39, in <module>
        group = parent.add_child(key=rec['key'])
      File "C:\opt\lang\Python25\lib\site-packages\treebeard\mp_tree.py", line 508, in add_child
        **kwargs)
      File "C:\opt\lang\Python25\lib\site-packages\treebeard\mp_tree.py", line 580, in add_sibling
        cursor.execute(sql, vals)
      File "c:\opt\lang\python25\Lib\site-packages\django\db\backends\util.py", line 15, in execute
        return self.cursor.execute(sql, params)
      File "c:\opt\lang\python25\Lib\site-packages\django\db\backends\mysql\base.py", line 86, in execute
        return self.cursor.execute(query, args)
      File "C:\opt\lang\Python25\lib\site-packages\MySQLdb\cursors.py", line 166, in execute
        self.errorhandler(self, exc, value)
      File "C:\opt\lang\Python25\lib\site-packages\MySQLdb\connections.py", line 35, in defaulterrorhandler
        raise errorclass, errorvalue
    _mysql_exceptions.OperationalError: (1292, "Truncated incorrect DOUBLE value: '0001000100020002000A'")
    

    In my case, I am scripting the addition of a number of graph items to preload my database. the error "Truncated incorrect DOUBLE value: '0001000100020002000A'" is happening because my the generated SQL

    UPDATE `graph_group` SET path='0001000100020002000B'||SUBSTR(path, 21) WHERE path LIKE '0001000100020002000A%'
    

    is failing -- the || is being used as OR instead of CONCAT. The exciting wrong behavior here is that || works fine for this statement until the path contains an alpha character. Once you get ten children nodes, it blows up as above.

    The error is actually saying that MySQL cannot make sense of the string '0001000100020002000A' as a DOUBLE which it can then use with OR.

    I know you already closed this ticket, but I'm including my error text since I didn't find this solution very easily via Google.

    As recommended above, the solution was to run MySQL in ANSI mode, which fixes PIPES_AS_CONCAT.

    Thanks for a great set of tools.

    Greg

  16. #16 Gustavo Picon

    written

    Greg,

    Could you please paste your DB settings and django version? By default, treebeard should be using CONCAT instead of ||

  17. #17 Gustavo Picon

    written

    In my previous comment, I meant: _IN MYSQL_, treebeard should be using CONCAT instead of ||

Add comment / attachment

Show/hide preview

Verification: Please write the text from the image in the box (letters only)

captcha

Is that you, Humanoid? Is this me?