Issues

Issue #6 resolved

"has_bulk_insert = True" causes ADO client to right pad inserts to same width

Michael Manfre
repo owner created an issue

Change {{{ has_bulk_insert = True }}} and then run the following. {{{ python runtests.py --settings=settings --noinput -v 2 bulk_create }}}

It will fail with

{{{ Creating tables ... Creating table django_content_type Creating table auth_permission Creating table auth_group_permissions Creating table auth_group Creating table auth_user_user_permissions Creating table auth_user_groups Creating table auth_user Creating table django_site Creating table django_flatpage_sites Creating table django_flatpage Creating table django_redirect Creating table django_session Creating table django_comments Creating table django_comment_flags Creating table django_admin_log Creating table bulk_create_country Creating table bulk_create_restaurant Creating table bulk_create_pizzeria Creating table bulk_create_state Traceback (most recent call last): File "runtests.py", line 326, in <module> options.failfast, args) File "runtests.py", line 169, in django_tests failures = test_runner.run_tests(test_labels, extra_tests=extra_tests) File "C:\projects\web-site\django\django\django\test\simple.py", line 368, in run_tests old_config = self.setup_databases() File "C:\projects\web-site\django\django\django\test\simple.py", line 315, in setup_databases self.verbosity, autoclobber=not self.interactive) File "C:\projects\web-site\django\django\django\db\backends\creation.py", line 277, in create_test_db load_initial_data=False) File "C:\projects\web-site\django\django\django\core\management__init.py", line 150, in call_command return klass.execute(args, defaults) File "C:\projects\web-site\django\django\django\core\management\base.py", line 232, in execute output = self.handle(args, options) File "C:\projects\web-site\django\django\django\core\management\base.py", line 368, in handle return self.handle_noargs(options) File "C:\projects\web-site\django\django\django\core\management\commands\syncdb.py", line 109, in handle_noargs emit_post_sync_signal(created_models, verbosity, interactive, db) File "C:\projects\web-site\django\django\django\core\management\sql.py", line 178, in emit_post_sync_signal interactive=interactive, db=db) File "C:\projects\web-site\django\django\django\dispatch\dispatcher.py", line 172, in send response = receiver(signal=self, sender=sender, **named) File "C:\projects\web-site\django\django\django\contrib\auth\management__init.py", line 35, in create_permissions ctype = ContentType.objects.get_for_model(klass) File "C:\projects\web-site\django\django\django\contrib\contenttypes\models.py", line 44, in get_for_model self._add_to_cache(self.db, ct) File "C:\projects\web-site\django\django\django\contrib\contenttypes\models.py", line 116, in _add_to_cache key = (model._meta.app_label, model._meta.object_name.lower()) AttributeError: 'NoneType' object has no attribute '_meta' }}}

Underlying problem is that the auth contenttypes ( "user", "group", and "permission") are inserted as a single bulk insert. "user" and "group" are right padded with blanks to len("permission"). This behavior seems to happen with all bulk inserts.

Need to find a connection setting or ADO option to disable this behavior.

Comments (9)

  1. João Pedro Francese

    I'd like to use bulk insert to speed up my queries (I often have to insert thousands of lines at once). Vernon, did you do any progress? Michael, I'm trying to debug this error as well... can you point me where ADO options are used in code, please?

  2. João Pedro Francese

    Changing the ADO type for strings to adVarWChar instead of adBSTR seems to have gotten it working.

    in sqlserver_ado/dbapi.py:

    # Mapping Python data types to ADO type codes
    def _ado_type(data):
        if isinstance(data, six.string_types):
            return adVarWChar 
        return _map_to_adotype[type(data)]
    
  3. João Pedro Francese

    Ran it now.

    test_large_batch (bulk_create.tests.BulkCreateTests) fails because of excess of parameters in query.

    ======================================================================
    ERROR: test_large_batch (bulk_create.tests.BulkCreateTests)
    ----------------------------------------------------------------------
    Traceback (most recent call last):
      File "D:\Dev\Libs\django-mssql\django-1.6.5\tests\bulk_create\tests.py", line 97, in test_large_batch
        TwoFields(f1=i, f2=i+1) for i in range(0, 1001)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\models\manager.py", line 160, in bulk_create
        return self.get_queryset().bulk_create(*args, **kwargs)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\models\query.py", line 359, in bulk_create
        self._batched_insert(objs_without_pk, fields, batch_size)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\models\query.py", line 838, in _batched_insert
        using=self.db)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\models\manager.py", line 232, in _insert
        return insert_query(self.model, objs, fields, **kwargs)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\models\query.py", line 1514, in insert_query
        return query.get_compiler(using=using).execute_sql(return_id)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\models\sql\compiler.py", line 903, in execute_sql
        cursor.execute(sql, params)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\backends\util.py", line 69, in execute
        return super(CursorDebugWrapper, self).execute(sql, params)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\backends\util.py", line 53, in execute
        return self.cursor.execute(sql, params)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\utils.py", line 99, in __exit__
        six.reraise(dj_exc_type, dj_exc_value, traceback)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\backends\util.py", line 53, in execute
        return self.cursor.execute(sql, params)
      File "D:\Dev\Libs\django-mssql\hg\sqlserver_ado\dbapi.py", line 618, in execute
        self._execute_command()
      File "D:\Dev\Libs\django-mssql\hg\sqlserver_ado\dbapi.py", line 534, in _execute_command
        self._raiseCursorError(klass, _message)
      File "D:\Dev\Libs\django-mssql\hg\sqlserver_ado\dbapi.py", line 466, in _raiseCursorError
        eh(self.connection, self, errorclass, errorvalue)
      File "D:\Dev\Libs\django-mssql\hg\sqlserver_ado\dbapi.py", line 91, in standardErrorHandler
        raise errorclass(errorvalue)
    DatabaseError: (-2147352567, 'Exce\xe7\xe3o.', (0, u'Microsoft SQL Server Native Client 10.0', u'O n\xfamero de express\xf5es de valor de linha na instru\xe7\xe3o INSERT excede o n\xfamero m\xe1ximo permitido de 1000 valores de linha. ', None, 0, -2147217900), None)
    Command:
    INSERT INTO [bulk_create_twofields] ([f1], [f2]) VALUES (?, ?),  ....... (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?)
    Parameters:
    [Name: p0, Dir.: Input, Type: adInteger, Size: -1, Value: "0", Precision: 0, NumericScale: 0, Name: p1, Dir.: Input, Type: adInteger, Size: -1, Value: "1", Precision: 0, NumericScale: 0, Name: p2, Dir.: Input, Type: adInteger, Size: -1, Value: "1", Precision: 0, NumericScale: 0, Name: p3, Dir.: Input, Type: adInteger, Size: -1, Value: "2", Precision: 0, NumericScale: 0, Name: p4, Dir.: Input, Type: adInteger, Size: -1, ........ NumericScale: 0, Name: p1997, Dir.: Input, Type: adInteger, Size: -1, Value: "999", Precision: 0, NumericScale: 0, Name: p1998, Dir.: Input, Type: adInteger, Size: -1, Value: "999", Precision: 0, NumericScale: 0, Name: p1999, Dir.: Input, Type: adInteger, Size: -1, Value: "1000", Precision: 0, NumericScale: 0, Name: p2000, Dir.: Input, Type: adInteger, Size: -1, Value: "1000", Precision: 0, NumericScale: 0, Name: p2001, Dir.: Input, Type: adInteger, Size: -1, Value: "1001", Precision: 0, NumericScale: 0]
    
    ----------------------------------------------------------------------
    Ran 14 tests in 9.602s
    
    FAILED (errors=2, skipped=1)
    

    This happened in my project as well, and to avoid it I'm using and explicit batch_size whenever I use a bulk insert in my code. Ideally django-mssql would detect the number of used parameters and break into as many queries as necessary, but I haven't tried to implement that yet. Do you think it's a good idea?

    I'm going to submit a pull request so you can check it out if you want.

  4. João Pedro Francese

    In addition to the 1000 rows limit, there is a 2100 parameters limit in any query. In my previous comment, I didn't realize that the exception I posted was the 1000-rows exception, and wrote about the 2100-parameters one (because I bump into it so much more frequently). I wrote a test to catch this problem and I am working on a solution for it. When it's done I'll send a pull request.

    Here's the output of the thest I wrote, which uses bulk_create() to add 800 items of a 5-attribute class:

    E
    ======================================================================
    ERROR: test_too_many_parameters (mssql_bulk_create.tests.BulkCreateTests)
    ----------------------------------------------------------------------
    Traceback (most recent call last):
      File "D:\Dev\Libs\django-mssql\hg\tests\mssql_bulk_create\tests.py", line 18, in test_too_many_parameters
        FiveFields(f1=i+1, f2=i+2, f3=i+3, f4=i+4, f5=i+5) for i in range(0, 800)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\models\manager.py", line 160, in bulk_create
        return self.get_queryset().bulk_create(*args, **kwargs)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\models\query.py", line 359, in bulk_create
        self._batched_insert(objs_without_pk, fields, batch_size)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\models\query.py", line 838, in _batched_insert
        using=self.db)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\models\manager.py", line 232, in _insert
        return insert_query(self.model, objs, fields, **kwargs)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\models\query.py", line 1514, in insert_query
        return query.get_compiler(using=using).execute_sql(return_id)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\models\sql\compiler.py", line 903, in execute_sql
        cursor.execute(sql, params)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\backends\util.py", line 69, in execute
        return super(CursorDebugWrapper, self).execute(sql, params)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\backends\util.py", line 53, in execute
        return self.cursor.execute(sql, params)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\utils.py", line 99, in __exit__
        six.reraise(dj_exc_type, dj_exc_value, traceback)
      File "D:\Dev\Libs\django-mssql\django-1.6.5\django\db\backends\util.py", line 53, in execute
        return self.cursor.execute(sql, params)
      File "D:\Dev\Libs\django-mssql\hg\sqlserver_ado\dbapi.py", line 618, in execute
        self._execute_command()
      File "D:\Dev\Libs\django-mssql\hg\sqlserver_ado\dbapi.py", line 534, in _execute_command
        self._raiseCursorError(klass, _message)
      File "D:\Dev\Libs\django-mssql\hg\sqlserver_ado\dbapi.py", line 466, in _raiseCursorError
        eh(self.connection, self, errorclass, errorvalue)
      File "D:\Dev\Libs\django-mssql\hg\sqlserver_ado\dbapi.py", line 91, in standardErrorHandler
        raise errorclass(errorvalue)
    DatabaseError: (-2147352567, 'Exce\xe7\xe3o.', (0, u'Microsoft SQL Server Native Client 10.0', u'O fluxo de entrada do protocolo TDS RPC (chamada de procedimento remoto) est\xe1 incorreto. Foram informados par\xe2metros demais para esta solicita\xe7\xe3o de RPC. O m\xe1ximo \xe9 2100.', None, 0, -2147217900), None)
    Command:
    INSERT INTO [mssql_bulk_create_fivefields] ([f1], [f2], [f3], [f4], [f5]) VALUES (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), ..., (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?), (?, ?, ?, ?, ?)
    Parameters:
    [Name: p0, Dir.: Input, Type: adInteger, Size: -1, Value: "1", Precision: 0, NumericScale: 0, Name: p1, Dir.: Input, Type: adInteger, Size: -1, Value: "2", Precision: 0, NumericScale: 0, Name: p2, Dir.: Input, Type: adInteger, Size: -1, Value: "3", Precision: 0, NumericScale: 0, Name: p3, Dir.: Input, Type: adInteger, Size: -1, Value: "4", Precision: 0, NumericScale: 0, Name: p4, Dir.: Input, Type: adInteger, Size: -1, Value: "5", Precision: 0, NumericScale: 0, Name: p5, Dir.: Input, Type: adInteger, Size: -1, Value: "2", Precision: 0, NumericScale: 0, Name: p6, Dir.: Input, Type: adInteger, Size: -1, Value: "3", Precision: 0, NumericScale: 0, Name: p7, Dir.: Input, Type: adInteger, Size: -1, Value: "4", Precision: 0, NumericScale: 0, ..., Name: p3998, Dir.: Input, Type: adInteger, Size: -1, Value: "803", Precision: 0, NumericScale: 0, Name: p3999, Dir.: Input, Type: adInteger, Size: -1, Value: "804", Precision: 0, NumericScale: 0]
    
    ----------------------------------------------------------------------
    Ran 1 test in 4.908s
    
    FAILED (errors=1)
    
  5. Log in to comment