Problem/Motivation

Today we upgraded to Drupal 9.2.0 and received a WSOD with the errors below. We are running sqlsrv 4.1.3.

In Connection.php line 968:

SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]A MERGE statement must be terminated by a se
mi-colon (;).: MERGE [cache_config] AS tgt USING(VALUES (:db_upsert_placeholder_0, :db_upsert_placeholder_1, :db_up
sert_placeholder_2, :db_upsert_placeholder_3, :db_upsert_placeholder_4, :db_upsert_placeholder_5, :db_upsert_placeh
older_6), (:db_upsert_placeholder_7, :db_upsert_placeholder_8, :db_upsert_placeholder_9, :db_upsert_placeholder_10,
:db_upsert_placeholder_11, :db_upsert_placeholder_12, :db_upsert_placeholder_13), (:db_upsert_placeholder_14, :db_
upsert_placeholder_15, :db_upsert_placeholder_16, :db_upsert_placeholder_17, :db_upsert_placeholder_18, :db_upsert_
placeholder_19, :db_upsert_placeholder_20), (:db_upsert_placeholder_21, :db_upsert_placeholder_22, :db_upsert_place
holder_23, :db_upsert_placeholder_24, :db_upsert_placeholder_25, :db_upsert_placeholder_26, :db_upsert_placeholder_
27), (:db_upsert_placeholder_28, :db_upsert_placeholder_29, :db_upsert_placeholder_30, :db_upsert_placeholder_31, :
db_upsert_placeholder_32, :db_upsert_placeholder_33, :db_upsert_placeholder_34), (:db_upsert_placeholder_35, :db_up
sert_placeholder_36, :db_upsert_placeholder_37, :db_upsert_placeholder_38, :db_upsert_placeholder_39, :db_upsert_pl
aceholder_40, :db_upsert_placeholder_41), (:db_upsert_placeholder_42, :db_upsert_placeholder_43, :db_upsert_placeho
lder_44, :db_upsert_placeholder_45, :db_upsert_placeholder_46, :db_upsert_placeholder_47, :db_upsert_placeholder_48
), (:db_upsert_placeholder_49, :db_upsert_placeholder_50, :db_upsert_placeholder_51, :db_upsert_placeholder_52, :db
_upsert_placeholder_53, :db_upsert_placeholder_54, :db_upsert_placeholder_55)) AS src ([cid], [expire], [created],
[tags], [checksum], [data], [serialized]) ON tgt.[cid]=src.[cid] WHEN MATCHED THEN UPDATE SET [cid]=src.[cid], [exp
ire]=src.[expire], [created]=src.[created], [tags]=src.[tags], [checksum]=src.[checksum], [data]=src.[data], [seria
lized]=src.[serialized] WHEN NOT MATCHED THEN INSERT ([cid], [expire], [created], [tags], [checksum], [data], [seri
alized]) VALUES (src.[cid], src.[expire], src.[created], src.[tags], src.[checksum], src.[data], src.[serialized]);
Array
(
)

In StatementWrapper.php line 116:

SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]A MERGE statement must be terminated by a se
mi-colon (;)

Drupal 9.2.0
sqlsrv 4.1.3
Microsoft IIS 10.0
SQLServer 13.00.4001
PHP 7.4.8

Looks like something changed in core 9.2.0 that may have caused this issue?

Connection.php line 968
StatementWrapper.php line 116

Issue fork sqlsrv-3219201

Command icon Show commands

Start within a Git clone of the project using the version control instructions.

Or, if you do not have SSH keys set up on git.drupalcode.org:

About issue forks

Comments

chrisck created an issue. See original summary.

beakerboy’s picture

beakerboy
Primary language English
Location Kentucky
commented

Thanks for the report. Is this query created by core or by a custom module? If the sqlsrv driver creates the merge statement using the Upsert object, it should be appending a semicolon and setting $options['allow_delimiter_in_query'] to true. Is this a static or dynamic query?

Feel free to post on the #sql-server channel of the Drupal slack as well.

I don’t know if I’ve tested the driver on D9.2 yet.

beakerboy’s picture

beakerboy
Primary language English
Location Kentucky
commented
Related issues: +#3133798: Semicolon removed from query even when it is allowed
beakerboy’s picture

beakerboy
Primary language English
Location Kentucky
commented
Related issues: +#3160199: deadlocked on lock resources with another process and has been chosen as the deadlock victim

It looks like this may have already been reported a year ago.

beakerboy’s picture

beakerboy
Primary language English
Location Kentucky
commented
Related issues: -#3160199: deadlocked on lock resources with another process and has been chosen as the deadlock victim
KenKodz’s picture

KenKodz commented

I have the exact same issue. It appears to occur in the cache rebuild.

KenLewis opened merge request !10

Beakerboy opened merge request !11

Beakerboy closed merge request !10

beakerboy’s picture

beakerboy
Primary language English
Location Kentucky
commented
Status: Active » Fixed

Updated Connection::prepareStatement() and Connection::query() to match the latest core expectations.

Status: Fixed » Closed (fixed)

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

ARRC-Drupal-Chick’s picture

ARRC-Drupal-Chick commented

I've run into this error when trying to update to 9.2 from 9.1.

It appears that you have the fix in the sqlsrv 4.2.x-dev.

When might we expect this to be promoted out of development?

We really, really don't like using development versions of things on our production servers.

But we need to update to 9.2 before 9.1 goes out of security coverage later this year.

Our stack may be unique:

  • Windows
  • MS SQL Server - 2017 Enterprise
  • Apache 2.4
  • PHP 7.3 and PHP 7.4
  • Drupal 9.2.3
  • Drupal driver for SQL Server and SQL Azure 4.1.3

--------------------
And yes, you can run Apache on Windows. I've been doing it for almost 20 years.

Thank you.
-=-==--=-===-=-=-=-==-=--=-==-==-===-=-
Edit: 16:40 AK time: I tried installing the sqlsrv:^4.2.x-dev with composer and updating to 9.2.3 (also with composer) and ran into the same error. So, something still not quite right for me. Or, the fix didn't make it into the downloadable version.

chrisck’s picture

chrisck
he/him
Location BC, Canada
commented

@ARRC-Drupal-Chick there have been reports that Drupal 9.2 is working with 4.2.x-dev after applying the fix in #4 here. Issue #3220722 needs to be resolved before it'll work with 9.2. Just waiting on the maintainer to review KenLewis' MR and commit it. We are in the same place and have rolled back to 9.1.11 for now.

ARRC-Drupal-Chick’s picture

ARRC-Drupal-Chick commented

@chrisck, thank you.

Sometimes it just helps to know that something is working even if it is waiting for someone else to do something else. (I've seen that way too many times in the last 40 something years in the computer/hardware/software/satellite/web worlds.)

chrisck’s picture

chrisck
he/him
Location BC, Canada
commented

@ARRC-Drupal-Chick can you please try patching 4.2.x-dev with my patch in #3220722 and let me know if it works for you on Drupal 9.2?

beakerboy’s picture

beakerboy
Primary language English
Location Kentucky
commented

The 4.2.x-dev branch has been promoted to 4.2.0.

ARRC-Drupal-Chick’s picture

ARRC-Drupal-Chick commented

@chrisck, I apologize for not getting to this sooner. I missed seeing your request.

I have used the 4.2.x-dev install with updating Drupal to 9.2.5 and the combination seems to have resolved the MERGE issue.

Thank you very much.

(Now if I can just figure out why enabling AdvAgg breaks using intlTelInput-query.js, I'll be a very happy site admin/all around geek.)

ARRC-Drupal-Chick’s picture

ARRC-Drupal-Chick commented

When I did the Drupal core security update (upgrading from 9.1 to 9.2 as well), I also updated to 4.2.x-dev for Drupal driver for SQL Server and everything is working beautifully.

The interesting thing is that Drupal driver for SQL Server no longer appears on the "Available updates" page.

chrisck’s picture

chrisck
he/him
Location BC, Canada
commented

@ARRC-Drupal-Chick when on the dev branch it will not show any updates. You should downgrade to sqlsrv 4.2.1 to see available updates. 4.2.1 is now compatible with Drupal 9.2.

In sqlsrv.info.yml it should show the package information. I believe project and version is required to show on available updates.

# Information added by Drupal.org packaging script on 2021-08-30
version: '4.2.1'
project: 'sqlsrv'
datestamp: 1630341852