Page MenuHomePhabricator

Error installing MediaWiki on MSSQL: "Implicit conversion from data type varchar to varbinary is not allowed" (externallinks table)
Closed, DeclinedPublic


Helping a user on freenode today, we came across the following error:

Error: 257 [SQLSTATE 42000][Error Code 257][Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Implicit conversion from data type varchar to varbinary is not allowed. Use the CONVERT function to run this query.

The cause was the default line here:

Query: CREATE TABLE [mediawiki].[externallinks] (
el_from int NOT NULL REFERENCES [mediawiki].[page](page_id) ON DELETE CASCADE,
el_to nvarchar(max) NOT NULL,
el_index nvarchar(450) NOT NULL,
el_index_60 varbinary(60) NOT NULL default ''

Removing default '' allowed the installation to work.

MW: 1.30
MSSQL: Version 10.50.1600.1 (Microsoft SQL Server Data Center Edition (64-bit))

Event Timeline

User tried MW 1.27 and didn't have the same problem. Looks like the problem was introduced in 1.28 or 1.29 or 1.30.

Apparently, on SQLServer the default for varbinary should be (0x)

This has arised also in Topic:Ud2z88i3y6yxmlft, but for another table (l10n_cache)

Simply removing the default can cause errors on insert statements that do not specify the default column.

The core problem is the implicit conversion from varchar to varbinary is indeterminate as it is not known if the intention is to convert to 8 bit character codes, or to 16 bit Unicode. I am unclear why the base type was changed to varbinary as the core problem of whether Unicode is the default coding does not seem to be resolved. It is true that removing or changing the default allows the install to continue, but does not allow setup to complete, so it really does not fix the problem. Would a valid solution be to change the type coding back to varchar till MediaWiki (and/or PHP) make Unicode the default character coding?

There seems to be a related problem that isbinary is not being set for this column which seems to be a clear error.

I can confirm the problem exists on 1.29.2 and 1.30
I can confirm that 1.27 LTS works and uses varbinary for lc_value in l10n_cache.

Krinkle renamed this task from Errors installing on MsSQL server to Error installing MediaWiki on MSSQL: "Implicit conversion from data type varchar to varbinary is not allowed" (externallinks table).Jul 28 2018, 10:26 PM
Krinkle removed a project: Wikimedia-Rdbms.
Krinkle added a subscriber: Krinkle.

Heads-up: As per RFC discussion in August 2019, the previously experimental support for using Oracle or MSSQL as database backends in MediaWiki core has been removed in MediaWiki 1.34, so this task might end up as declined in the future.