Problem/Motivation

On Drupal 9, every time I want to create a field or something else that provides a table creation, the CMS return the following error:

There was a problem creating the field Visualizza solo in pagina: Exception thrown while performing a schema update. SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near '.'.: CREATE TABLE [dbo].[media__field_visualizza_solo_in_pagina] ( [bundle] varchar(128) NOT NULL CONSTRAINT [dbo].[media__field_visualizza_solo_in_pagina_bundle_df] DEFAULT '', [deleted] smallint NOT NULL CONSTRAINT [dbo].[media__field_visualizza_solo_in_pagina_deleted_df] DEFAULT 0, [entity_id] bigint NOT NULL CHECK ([entity_id] >= 0), [revision_id] bigint NOT NULL CHECK ([revision_id] >= 0), [langcode] varchar(32) NOT NULL CONSTRAINT [dbo].[media__field_visualizza_solo_in_pagina_langcode_df] DEFAULT '', [delta] bigint NOT NULL CHECK ([delta] >= 0), [field_visualizza_solo_in_pagina_value] smallint NOT NULL ); Array ( )

In this specific case I want to create a field that is called: field_visualizza_solo_in_pagina on a media type (Audio).

Steps to reproduce

1. Enable the last stable version of the module (3.4.2),
2. Create a new field on a content type or media type,
3. The system breaks because of the error before;

Proposed resolution

TBD

Remaining tasks

TBD

User interface changes

TBD

API changes

TBD

Data model changes

TBD

CommentFileSizeAuthor
#3 dbo-schema.png3.56 KBdrp_distruptor
audio_field_error.PNG41.82 KBdrp_distruptor

Comments

drp_distruptor created an issue. See original summary.

pstewart’s picture

pstewart commented
Status: Active » Postponed (maintainer needs more info)

I've been investigating this, and although I've not been able to recreate I can see how the problem can arises if the table name passed to
Schema::createFieldSql is prefixed with a schema, as the code uses the table name directly when creating the constraint name:

      if (isset($spec['default'])) {
        $default = $this->defaultValueExpression($sqlsrv_type, $spec['default']);
        $sql .= " CONSTRAINT {{$table}_{$name}_df} DEFAULT $default";
      }

What I'm unclear about is how $table is acquiring a schema prefix, as I don't see this happening in my dev environments either with or without the schema being defined in settings. The one thing I've not tried is adding a schema via the prefix setting.

@drp_distruptor are you seeing this problem on a pre-existing site or on a new install, and do you see the same problems with the previous release 4.3.1? Also please can you let me know what database settings you are using (without sensitive details of course).

drp_distruptor’s picture

drp_distruptor
Location Neaples
commented
StatusFileSize
new dbo-schema.png3.56 KB

With the new version 4.3.3, the issue seems solved by removing the prefix from the settings.local.php

$databases['default']['default'] = [
    'database' => '###',
    'username' => '###',
    'password' => '###',
    'prefix' => '',
    'encrypt' => '0',
    'host' => '###',
    'port' => '1433',
    'namespace' => 'Drupal\\sqlsrv\\Driver\\Database\\sqlsrv',
    'driver' => 'sqlsrv',
    'autoload' => 'modules/contrib/sqlsrv/src/Driver/Database/sqlsrv',
];

By the way, you should be sure that the default schema is dbo on your user and database.

pstewart’s picture

pstewart commented
Version: 4.3.2 » 4.4.x-dev
Priority: Major » Normal
Status: Postponed (maintainer needs more info) » Needs work