Page MenuHomePhabricator

Error installing with SQL Server 2014 Express: Index 'oi_name_archive_name' has more than 900 bytes long
Open, Needs TriagePublic

Description

When installing MediaWiki 1.27.1 with SQL Server 2014 Express, one error occurs:

Query:

CREATE INDEX oi_name_archive_name ON [mediawiki].[oldimage] (oi_name,oi_archive_name)

Function: DatabaseBase::sourceFile( C:\inetpub\AglWiki/maintenance/mssql/tables.sql )
Error:

1945 [SQLSTATE 01000][Error Code 1945][Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Warning! The maximum key length is 900 bytes. The index 'oi_name_archive_name' has maximum length of 1020 bytes. For some combination of large values, the insert/update operation will fail.

Backtrace:

#0 C:\inetpub\AglWiki\includes\db\Database.php(901): DatabaseBase->reportQueryError('[SQLSTATE 01000...', 1945, 'CREATE INDEX oi...', 'DatabaseBase::s...', false)
#1 C:\inetpub\AglWiki\includes\db\Database.php(3054): DatabaseBase->query('CREATE INDEX oi...', 'DatabaseBase::s...')
#2 C:\inetpub\AglWiki\includes\db\Database.php(2975): DatabaseBase->sourceStream(Resource id #179, false, false, 'DatabaseBase::s...', false)
#3 C:\inetpub\AglWiki\includes\installer\DatabaseInstaller.php(195): DatabaseBase->sourceFile('C:\\inetpub\\AglW...')
#4 C:\inetpub\AglWiki\includes\installer\DatabaseInstaller.php(218): DatabaseInstaller->stepApplySourceFile('getSchemaPath', 'install', true)
#5 C:\inetpub\AglWiki\includes\installer\MssqlInstaller.php(634): DatabaseInstaller->createTables()
#6 C:\inetpub\AglWiki\includes\installer\Installer.php(1475): MssqlInstaller->createTables(Object(MssqlInstaller))
#7 C:\inetpub\AglWiki\includes\installer\WebInstallerInstall.php(44): Installer->performInstallation(Array, Array)
#8 C:\inetpub\AglWiki\includes\installer\WebInstaller.php(279): WebInstallerInstall->execute()
#9 C:\inetpub\AglWiki\mw-config\index.php(80): WebInstaller->execute(Array)
#10 C:\inetpub\AglWiki\mw-config\index.php(38): wfInstallerMain()
#11 {main}

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptSep 14 2016, 12:20 PM
Aklapper renamed this task from Error on install MediaWiki with SQL Server 2014 Express to Error installing with SQL Server 2014 Express: Index 'oi_name_archive_name' has more than 900 bytes long.Sep 15 2016, 11:30 AM

Hi @PauloSincos, thanks for taking the time to report this!
Putting the actual error into the task summary for easier finding.
Reminds me of ancient T12435 :)

sme added a subscriber: sme.Oct 5 2016, 3:14 PM

Hi all,

Has there been any progress on this issue since then?

sme added a comment.Oct 6 2016, 9:12 AM

In the file "[mediawiki source dir]\maintenance\mssql\tables.sql" Line 624 says:

  • Base filename: key to image.img_name
  • Not a FK because deleting images removes them from image oi_name nvarchar(255) NOT NULL default '',
  • Filename of the archived file.
  • This is generally a timestamp and '!' prepended to the base name. oi_archive_name nvarchar(255) NOT NULL default '',

Then, line 655 says:

CREATE INDEX /*i*/oi_name_archive_name ON /*_*/oldimage (oi_name,oi_archive_name);

Besides, as far as I know, the 900-byte limit includes all columns of a given index key which means that in this case, we should sum up the length of oi_name,oi_archive_name which is 255+255 = 510 which is less than 900.

Could you point me to the place where this 1020 comes from? It seems like (maybe by coincidence) 1020 is twice 510.

I would highly appreciate any of your advice.

sme added a comment.Oct 6 2016, 10:32 AM

I finally managed to get it to work by decreasing the size for both "oi_name" and "oi_archive_name" from 255 to 225.
Highly appreciate this nice forum.

Highly appreciate this nice forum.

The support forum is at https://www.mediawiki.org/wiki/Project:Support_desk
Phabricator is just a place to track bug reports. :)

Change 314826 had a related patch set uploaded (by Paladox):
Lower nvarcher value to 225 for oi_name and oi_archive_name

https://gerrit.wikimedia.org/r/314826

Reedy added a subscriber: Reedy.Oct 8 2016, 3:17 PM

In the file "[mediawiki source dir]\maintenance\mssql\tables.sql" Line 624 says:

  • Base filename: key to image.img_name
  • Not a FK because deleting images removes them from image oi_name nvarchar(255) NOT NULL default '',
  • Filename of the archived file.
  • This is generally a timestamp and '!' prepended to the base name. oi_archive_name nvarchar(255) NOT NULL default '',

Then, line 655 says:
CREATE INDEX /*i*/oi_name_archive_name ON /*_*/oldimage (oi_name,oi_archive_name);
Besides, as far as I know, the 900-byte limit includes all columns of a given index key which means that in this case, we should sum up the length of oi_name,oi_archive_name which is 255+255 = 510 which is less than 900.
Could you point me to the place where this 1020 comes from? It seems like (maybe by coincidence) 1020 is twice 510.
I would highly appreciate any of your advice.

I presume 1 character isn't a byte, it'll be 2 or more for unicode purposes?

Change 314826 merged by jenkins-bot:
[mediawiki/core] Drop index oi_name_archive_name on table oldimage

https://gerrit.wikimedia.org/r/314826

Change 345333 had a related patch set uploaded (by Brian Wolff; owner: Paladox):
[mediawiki/core@REL1_28] Drop index oi_name_archive_name on table oldimage in MSSQL

https://gerrit.wikimedia.org/r/345333

Change 345339 had a related patch set uploaded (by Brian Wolff; owner: Paladox):
[mediawiki/core@REL1_27] Drop index oi_name_archive_name on table oldimage in MSSQL

https://gerrit.wikimedia.org/r/345339

Change 345333 merged by jenkins-bot:
[mediawiki/core@REL1_28] Drop index oi_name_archive_name on table oldimage in MSSQL

https://gerrit.wikimedia.org/r/345333

Change 345339 merged by jenkins-bot:
[mediawiki/core@REL1_27] Drop index oi_name_archive_name on table oldimage in MSSQL

https://gerrit.wikimedia.org/r/345339

Paladox added a subscriber: Paladox.EditedMay 27 2017, 9:24 PM

Is this resolved?

Yes i think so. :) Though haven't tested as i don't use this db.

demon added a subscriber: demon.

In no universe is MSSQL support a blocker to release.

No, This is still an active problem. and demon, you must work in customer relations

demon added a comment.May 31 2017, 4:04 PM

No, This is still an active problem.

Of course it is. But it's not a blocker to release.

demon removed a subscriber: demon.Feb 19 2019, 10:33 AM