Page MenuHomePhabricator
Create Task
Maniphest T145635

Error installing with SQL Server 2014 Express: Index 'oi_name_archive_name' has more than 900 bytes long
Closed, DeclinedPublic
Actions

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}

Details

SubjectRepoBranchLines +/-
Drop index oi_name_archive_name on table oldimage in MSSQLmediawiki/coreREL1_27+6 -2
Drop index oi_name_archive_name on table oldimage in MSSQLmediawiki/coreREL1_28+5 -1
Drop index oi_name_archive_name on table oldimagemediawiki/coremaster+3 -1
Customize query in gerrit

Related Objects

Event Timeline

PauloSincos created this task.Sep 14 2016, 12:20 PM
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
Aklapper added a parent task: T11767: [DO NOT USE] Microsoft SQL Server/MSSQL support (tracking).
Aklapper added a comment.Sep 15 2016, 11:32 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 subscribed.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.

Aklapper added a comment.Oct 8 2016, 2:04 PM
In T145635#2696202, @sme wrote:

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. :)

gerritbot subscribed.Oct 8 2016, 3:10 PM

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

gerritbot added a project: Patch-For-Review.Oct 8 2016, 3:10 PM
Reedy subscribed.Oct 8 2016, 3:17 PM
In T145635#2696091, @sme wrote:

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?

Jhuff05 mentioned this in T155188: When installing MediaWiki 1.27.1 with SQL Server 2008 R2 Error Occures:.Jan 12 2017, 8:07 PM
Aklapper merged a task: T155188: When installing MediaWiki 1.27.1 with SQL Server 2008 R2 Error Occures:.Jan 13 2017, 10:04 PM
Aklapper added a subscriber: Jhuff05.
Aklapper mentioned this in T158530: Mediawiki 1.27.1 could not do Full text searching and failed to give correct searching result.Feb 20 2017, 8:55 AM
gerritbot added a comment.Mar 13 2017, 5:20 AM

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

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

ReleaseTaggerBot added projects: MW-1.29-release (WMF-deploy-2017-03-14_(1.29.0-wmf.16)), MW-1.29-release-notes.Mar 13 2017, 6:00 AM
gerritbot added a comment.Mar 29 2017, 1:26 PM

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

gerritbot added a comment.Mar 29 2017, 1:51 PM

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

gerritbot added a comment.Mar 29 2017, 4:35 PM

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

ReleaseTaggerBot added a project: MW-1.28-release-notes.Mar 29 2017, 5:00 PM
gerritbot added a comment.Apr 1 2017, 4:26 AM

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

ReleaseTaggerBot added a project: MW-1.27-release-notes.Apr 1 2017, 5:00 AM
Krinkle moved this task from Untriaged to Usage problem on the MediaWiki-libs-Rdbms board.May 8 2017, 1:27 AM
• tstarling added a project: MSSQL.May 19 2017, 10:11 AM
• tstarling removed a parent task: T11767: [DO NOT USE] Microsoft SQL Server/MSSQL support (tracking).May 19 2017, 10:15 AM
PauloSincos unsubscribed.May 19 2017, 11:11 AM
Krinkle edited projects, added MW-1.27-release, MW-1.29-release; removed MW-1.27-release-notes, MW-1.28-release-notes, MW-1.29-release-notes, MW-1.29-release (WMF-deploy-2017-03-14_(1.29.0-wmf.16)), Patch-For-Review.May 25 2017, 1:53 PM
Krinkle subscribed.

Is this resolved?

Paladox subscribed.EditedMay 27 2017, 9:24 PM
In T145635#3291882, @Krinkle wrote:

Is this resolved?

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

• demon removed projects: MW-1.29-release, MW-1.27-release.May 30 2017, 7:57 PM
• demon subscribed.

In no universe is MSSQL support a blocker to release.

Jhuff05 added a comment.May 31 2017, 10:48 AM

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

Aklapper added a comment.May 31 2017, 11:05 AM

@Jhuff05: Please stick to technical aspects, not people. See mw:Compatibility#Database for the status of MSSQL 'support'.

• demon added a comment.May 31 2017, 4:04 PM
In T145635#3303309, @Jhuff05 wrote:

No, This is still an active problem.

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

• demon unsubscribed.Feb 19 2019, 10:33 AM
Krinkle edited projects, added MediaWiki-Installer, Schema-change; removed MediaWiki-libs-Rdbms.Jul 18 2019, 7:44 PM
Aklapper added a comment.Sep 26 2019, 8:27 AM

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.

Reedy closed this task as Declined.Feb 24 2020, 6:42 AM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits