Problem/Motivation

Although the driver has means of autodetecting when statements have > 2100 parameters in order to enable EMULATE_PREPARES (as a workaround to deal with the different parameter limit in MySQL and SQL Server), this mechanism seems not to be working under some circumstances.

This autodetection method is in connection.php:

public function query($query, array $args = [], $options = []) {
    // Use default values if not already set.
    $options += $this->defaultOptions();
    $stmt = NULL;
    try {
      // We allow either a pre-bound statement object or a literal string.
      // In either case, we want to end up with an executed statement object,
      // which we pass to PDOStatement::execute.
      if ($query instanceof StatementInterface) {
        $stmt = $query;
        $stmt->execute(NULL, $options);
      }
      else {
        $this->expandArguments($query, $args);
        $insecure = isset($options['insecure']) ? $options['insecure'] : FALSE;
        // Try to detect duplicate place holders, this check's performance
        // is not a good addition to the driver, but does a good job preventing
        // duplicate placeholder errors.
        $argcount = count($args);
        if ($insecure === TRUE || $argcount >= 2100 || ($argcount != substr_count($query, ':'))) {
          $insecure = TRUE;
        }
        $stmt = $this->prepareQuery($query, array('insecure' => $insecure));
        $stmt->execute($args, $options);
      }

Obviously, when query is called with an already bound statement (i.e. the ones that come from other parts of the database abstraction layer such as MERGE, INSERT, etc.) it is unable to figure out the number of arguments in the statement and thus failing to turn on EMULATE_PREPARES.

Proposed resolution

The PDOStatement we are using is an external turbocharged implementation that keeps internal track of bound parameters and values. It *should* be easy (maybe even at a deeper level) to figure out in a more reliable way the number of arguments/params bound to a statement using the internal counter and turn on EMULATE_PREPARES accordingly.

Remaining tasks

Do it and test it.

User interface changes

None.

API changes

None.

Data model changes

None.

Original report

After saving a node with a large amount of field values I got the following error:
SQLSTATE[IMSSP]: Tried to bind parameter number 2101. SQL Server supports a maximum of 2100 parameters.: INSERT INTO
Complete error is attached in file SQLSRV-error-bind-parameter.txt.

This error is generated in the execute()method in the class InsertQuery_sqlsrv.
The $batch_size doesn't check the maximum amount of 2100 parameters against the number of columns in the rows.

CommentFileSizeAuthor
#17 mssql_patch.txt2.19 KBeugis
#16 mssql_patch.txt2.17 KBeugis
#4 creating_node_sql_error-2779047-4.patch909 bytesfabianvdbor
#2 creating_node_sql_error-2779047-2.patch798 bytesfabianvdbor
SQLSRV-error-bind-parameter.txt40.43 KBfabianvdbor
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

fabianvdbor created an issue. See original summary.

fabianvdbor’s picture

fabianvdbor CreditAttribution: fabianvdbor as a volunteer and at Emble commented
FileSize
creating_node_sql_error-2779047-2.patch798 bytes

The attached patch calculate the maximum size of the batch.

fabianvdbor’s picture

fabianvdbor CreditAttribution: fabianvdbor as a volunteer and at Emble commented
Status: Active » Needs review
fabianvdbor’s picture

fabianvdbor CreditAttribution: fabianvdbor as a volunteer and at Emble commented
FileSize
creating_node_sql_error-2779047-4.patch909 bytes

During the testing of my website I got this error with the bind parameters.

My code has calculated one batch size too much.

The attached patch fixes this.

david_garcia’s picture

david_garcia CreditAttribution: david_garcia commented

Thanks for your patch. This is an interesting and useful approach.

I'll take care of this first in 8.x and then backport.

david_garcia’s picture

david_garcia CreditAttribution: david_garcia commented

Thinking about this again... the driver has an automatic fallback to prevent the 2,100 parameter issues, where it will use EMULATE_PREPARES to trick the PDO driver. I'm not sure why this fallback is not triggering when doing inserts, needs to be investigated.

david_garcia’s picture

david_garcia CreditAttribution: david_garcia commented
Title: Creating node SQL error 'Tried to bind parameter number 2101' » 2100 parameter limit issue
Version: 7.x-2.x-dev » 8.x-2.x-dev
Issue summary: View changes
Status: Needs review » Needs work
david_garcia’s picture

david_garcia CreditAttribution: david_garcia commented
Issue summary: View changes

  • david_garcia committed 41b21ca on 8.x-2.x 
    Issue #2779047: Merge queries should use emulate_prepares to avoid the...
david_garcia’s picture

david_garcia CreditAttribution: david_garcia commented

My original intention was to use EMULATE_PREPARES at a lower layer level relying on internal bound parameter count of the statement.

Unfortunately, statement is prepared before binding parameters so we need upstream code to decide to use emulate_prepares when needed, or to batch whatever it is doing.

As an experimental approach, I've added support for this in the MERGE statement implementation, that seems to be the most abused one.

eugis’s picture

eugis CreditAttribution: eugis commented

Hi,

I'm also facing a similar issue when trying to import configs (drush cim).

Here's shortened error message:

Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[IMSSP]: Tried to bind parameter number 2101. SQL Server supports a maximum of 2100 parameters.: MERGE INTO [cache_config] _target USING (SELECT T.* FROM (values(:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6),
<...>
(:db_insert_placeholder_2184, :db_insert_placeholder_2185, :db_insert_placeholder_2186, :db_insert_placeholder_2187, :db_insert_placeholder_2188, :db_insert_placeholder_2189, :db_insert_placeholder_2190))
 as T([cid], [expire], [created], [tags], [checksum], [data], [serialized])) _source ON _target.[cid] = _source.[cid] WHEN MATCHED THEN UPDATE SET _target.[expire] = _source.[expire], _target.[created] = _source.[created], _target.[tags] = _source.[tags], _target.[checksum] = _source.[checksum], _target.[data] = _source.[data], _target.[serialized] = _source.[serialized] WHEN NOT MATCHED THEN INSERT ([cid], [expire], [created], [tags], [checksum], [data], [serialized]) 
 VALUES (_source.[cid], _source.[expire], _source.[created], _source.[tags], _source.[checksum], _source.[data], _source.[serialized]) OUTPUT $action;; Array ( [:db_insert_placeholder_0] => Array ( [value] => backup_migrate.backup_migrate_destination.private_files [datatype] => 2 ) [:db_insert_placeholder_1] => Array ( [value] => -1 [datatype] => 2 ) [:db_insert_placeholder_2] => Array ( [value] => 1510033960.723 [datatype] => 2 ) [:db_insert_placeholder_3] => Array ( [value] => [datatype] => 2 ) [:db_insert_placeholder_4] => Array ( [value] => 0 [datatype] => 2 ) [:db_insert_placeholder_5] => Array ( [value] => Resource id #747 [datatype] => 3 ) [:db_insert_placeholder_6] => Array ( [value] => 1 [datatype] => 2 ) 
 <...>
 [:db_insert_placeholder_2184] => Array ( [value] => webform.webform_options.titles [datatype] => 2 ) [:db_insert_placeholder_2185] => Array ( [value] => -1 [datatype] => 2 ) [:db_insert_placeholder_2186] => Array ( [value] => 1510033960.728 [datatype] => 2 ) [:db_insert_placeholder_2187] => Array ( [value] => [datatype] => 2 ) [:db_insert_placeholder_2188] => Array ( [value] => 0 [datatype] => 2 ) [:db_insert_placeholder_2189] => Array ( [value] => Resource id #1059 [datatype] => 3 ) [:db_insert_placeholder_2190] => Array ( [value] => 1 [datatype] => 2 ) ) in Drupal\Core\Config\CachedStorage->readMultiple() (line 105 of C:\sites\ey_test\core\lib\Drupal\Core\Config\CachedStorage.php).

Any possible solutions?

david_garcia’s picture

david_garcia CreditAttribution: david_garcia commented

@eugis Is this with 8.x-1.x or 8.x-2.x?

eugis’s picture

eugis CreditAttribution: eugis commented

@david_garcia

It's on 8.x-2.x. I've solved this by enabling wincache.

rajeevgole’s picture

rajeevgole
He/Him
Primary language English
CreditAttribution: rajeevgole as a volunteer and at Axelerant for Axelerant commented

I am also getting the same error, I tried the above solutions but none worked.

I am using Drupal 8.7.3 version.

In file docroot/drivers/lib/Drupal/Driver/Database/sqlsrv/Connection.php, I tried changing insecure options to true. 

// Merge default statement options. These options are
    // only specific for this preparation and will only override
    // the global configuration if set to different than NULL.
    $options = array_merge(array(
        'insecure' => TRUE,
        'statement_caching' => $this->driver_settings->GetStatementCachingMode(),
        'direct_query' => $this->driver_settings->GetDefaultDirectQueries(),
        'prefix_tables' => TRUE,
        'integrityretry' => FALSE,
        'resilientretry' => TRUE,
      ), $options);

But then I started getting below error on every Drush command.

The error is "An error occurred translating the query string to UTF-16: No mapping for the Unicode character exists in the target multi-byte code page." in docroot/drivers/lib/Drupal/Driver/Database/sqlsrv/mssql/src/Statement.php:312

Any workaround for this?

rajeevgole’s picture

rajeevgole
He/Him
Primary language English
CreditAttribution: rajeevgole as a volunteer and at Axelerant for Axelerant commented

In my case, I implemented the workaround by disable cache config. This is not a proper solution but at least I got unblocked.

In sites/default/services.yml

services:
  cache.backend.null:
    class: Drupal\Core\Cache\NullBackendFactory

In settings.php

 $settings['cache']['bins']['config'] = 'cache.backend.null';
eugis’s picture

eugis CreditAttribution: eugis commented
FileSize
mssql_patch.txt2.17 KB

Regarding the database caching - what about splitting cache array into chunks?

eugis’s picture

eugis CreditAttribution: eugis commented
FileSize
mssql_patch.txt2.19 KB

#16 is a bit wrong - here's another one.

david_garcia’s picture

david_garcia CreditAttribution: david_garcia commented

I made a new release that works around the parepared statement limit issue in Upsert statements also:

https://www.drupal.org/project/sqlsrv/releases/8.x-2.14

david_garcia’s picture

david_garcia CreditAttribution: david_garcia commented
Status: Needs work » Fixed

I consider this fixed, please open a new issue relating to this one if any prepare limit issue shows somewhere else...

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.