Wikimedia Bugzilla is closed!

What version of the extension are you running?

I note both of those files are in different locations in the version in git master.

The latter index issue looks fixed explicitly in master.

CREATE UNIQUE INDEX /*i*/acr_email ON /*_*/account_requests (acr_email(255));

The first issue may be fixed, I guess it depends on the updater code.

Confirm Accounts does not specify versions (see e.g. http://terms.gbif.org/wiki/Special:Version)

We use the master version as of 2012-12-12. I believe this change https://gerrit.wikimedia.org/r/gitweb?p=mediawiki/extensions/ConfirmAccount.git;a=shortlog did not fix it, but for safety I have just updated confirmAccount to todays git head and tried again, the error persists. 

Updating (tables already existing):

Adding acr_agent field to table account_requests ...A database query syntax error has occurred.
The last attempted database query was:
"ALTER TABLE `account_requests`
 ADD acr_xff VARCHAR(255) NULL default '',
 ADD acr_agent VARCHAR(255) NULL default ''
"
from within function "DatabaseBase::sourceFile( /usr/share/mediawiki20/extensions/ConfirmAccount/backend/schema/mysql/patch-acr_agent.sql )".
Database returned error "1170: BLOB/TEXT column 'acr_email' used in key specification without a key length (localhost)"

Both Account_xxx tables created, all other tables are fully on 1.20.2 successfully update (with ConfirmAccount disabled), when reenbling Confirm account it tries to create new tables but fails with:

Creating account_requests table ...A database query syntax error has occurred.
The last attempted database query was:
"CREATE INDEX acr_email_token ON `account_requests` (acr_email_token)
"
from within function "DatabaseBase::sourceFile( /usr/share/mediawiki20/extensions/ConfirmAccount/backend/schema/mysql/Co  nfirmAccount.sql )".
Database returned error "1170: BLOB/TEXT column 'acr_email' used in key specification without a key length (localhost)"


Thanks for looking into this!

Works fine for me with git master of both core and ConfirmAccount:

Creating account_requests table ...done.
...have acr_filename field in account_requests table.
...account_credentials table already exists.
...have acr_areas field in account_requests table.
...index acr_email already set on account_requests table.


MediaWiki	1.21alpha (327df4f)
PHP	5.4.6-1ubuntu1.1 (apache2handler)
MySQL	5.5.28-0ubuntu0.12.10.2

vs your

MediaWiki	1.20.2
PHP	5.4.10-1~dotdeb.0 (fpm-fcgi)
MySQL	5.1.66-0+squeeze1

I wonder why it's complaining about that there when the patch doesn't touch it.

It would seem it's an "issue" with your mysql install in one way or another - whether config, or an actual bug.

Can you post full copies of what schema your site is running with?

show create table account_requests\G
show indexes from account_requests\G
show create table account_credentials\G
show indexes from account_credentials\G

Created attachment 11630 [details]
gzip file containing an empty version of the mediawiki database to upgrade, where confirmaccount causes errors

user table without entries, a user perhaps has to be added before working

Retested with mediawiki 1.20 and ConfirmAccount git-Head versions as of this morning, the error on update.php is: Creating account_credentials table ...A database query syntax error has occurred.
The last attempted database query was:
"ALTER TABLE `account_requests`
 ADD acr_type tinyint(255) unsigned NOT NULL default 0,
 DROP INDEX acr_deleted_reg,
 ADD INDEX acr_type_del_reg (acr_type,acr_deleted,acr_registration)
"
from within function "DatabaseBase::sourceFile( /usr/share/mediawiki20/extensions/ConfirmAccount/backend/schema/mysql/patch-account_credentials.sql )".
Database returned error "1091: Can't DROP 'acr_deleted_reg'; check that column/key exists (localhost)"

Some Software versions:
MediaWiki	1.20.2
PHP	5.4.10-1~dotdeb.0 (fpm-fcgi)
MySQL	5.1.66-0+squeeze1

Forgot to mention: first, thanks for investigating this, second: retested with the db schema provided in attachment 11630 [details] (a 1.18 version we want to upgrade, and which we can upgrade without errors if ConfirmAccount is commented out).

(In reply to comment #5)
> Retested with mediawiki 1.20 and ConfirmAccount git-Head versions

Is there a reason why you didn't use the REL1_20 branch of ConfirmAccount together with MW 1.20?

Bad habits? Back in svn-times the branches for extensions created together with core were useless because most authors did not update the branch (e.g. 1.18 branch), asking to use head instead or apply the bug fixes yourself.

But also, I was not aware of it under git at all, although we did use the specific version tags for the SMW extensions. I wonder whether the extension branches are new? This morning on git update ("cd ../extensions; sudo git pull; sudo git submodule update --init --recursive;") we received dozens of new REL1_19 and REL1_20 branches for extensions, which were not yet present a couple of days ago at the last update.

I will gladly test the REL1_20 branch as well.

(In reply to comment #8)
> I will gladly test the REL1_20 branch as well.

Did so, no change. Installed origin/REL1_20 with "cd /usr/share/mw-wmf-clone/extensions/ConfirmAccount; sudo git archive --prefix=ConfirmAccount/   origin/REL1_20 | sudo tar --extract --overwrite --directory=/usr/share/mediawiki20/extensions". DB error is the: ...  from within function "DatabaseBase::sourceFile( /usr/share/mediawiki20/extensions/ConfirmAccount/backend/schema/mysql/patch-account_credentials.sql )".
Database returned error "1091: Can't DROP 'acr_deleted_reg'; check that column/key exists (localhost)" one.

(Note: both head and REL1_20 contain unfixed bug 43886)

Created attachment 11634 [details]
table structure of account_credentials account_requests (MW 1.18) before MW 1.20.2-upgrade

(In reply to comment #9)
> Database returned error "1091: Can't DROP 'acr_deleted_reg'; check that
> column/key exists (localhost)" one.

I work on the same data base: the index 'acr_deleted_reg' does not exist in our DB to be dropped. I don't know what the patch-account_credentials.sql is intended to DROP for what ConfirmAccount-Version by the SQL:
---
ALTER TABLE /*$wgDBprefix*/account_requests
  ADD acr_type tinyint(255) unsigned NOT NULL default 0,
  DROP INDEX acr_deleted_reg,
  ADD INDEX acr_type_del_reg (acr_type,acr_deleted,acr_registration);
---

So I attach our table structure we had to upgrade. I did this manually and now the upgrade (php ./maintenance/update.php --quick --conf ./LocalSettings.php) works, but this manual repair is not a general solution

Andreas

*** Bug 44059 has been marked as a duplicate of this bug. ***

Several times in this bug and in the bug marked as a duplicate of this bug this error popped up that I think is creating the problem. 

Database returned error "1170: BLOB/TEXT column 'acr_email' used in key specification without a key length (localhost)"

I am not sure how to specify a key length manually for that key.

(In reply to comment #12)
> Several times in this bug and in the bug marked as a duplicate of this bug
> this
> error popped up that I think is creating the problem. 
> 
> Database returned error "1170: BLOB/TEXT column 'acr_email' used in key
> specification without a key length (localhost)"
> 
> I am not sure how to specify a key length manually for that key.

In https://github.com/wikimedia/mediawiki-extensions-ConfirmAccount/blob/master/backend/schema/mysql/ConfirmAccount.sql the statement "CREATE UNIQUE INDEX /*i*/acr_email ON /*_*/account_requests (acr_email(255));" creates a prefix index (only on the first 255 chars, which defines the key length). It's on old problem that this doesn't work on some mysql versions (giving the same error as if no key length was given).

So this extensions's code is no longer compatible some some versions of MySQL? What if our hosting provider does not support a newer MySQL? Which versions of MySQL do not support a limit on TEXT or BLOB so that "TEXT(88) simply won’t work"?

http://iderror.com/errors/mysql/mysql-server/error-1170-sqlstate-42000-er_blob_key_without_length/

if the mysql version is the root cause, then a very widely used version (the current debian lastest stable distributed MySQL 5.1.66-0+squeeze1) is causing it.

(In reply to comment #15)
> if the mysql version is the root cause, then a very widely used version (the
> current debian lastest stable distributed MySQL 5.1.66-0+squeeze1) is causing
> it.

Not sure, I've never had the problem myself for years. Does the core user_email column/index give you trouble (they and the one for this extension are both "tinytext NOT NULL" with a prefix index (though CA has the prefix at 255 vs 50). phpadmin shows:
Keyname	Type	Unique	Packed	Column	Cardinality	Collation	Null	Comment
acr_email	BTREE	Yes	No	acr_email (255)	7	A	No

No, no problems with core or with any other extension. We are running mediawiki since 1.12 and went through several updates through the years. It is just ConfirmAccounts. :-(

My suspicion was that one of the reasons might be that we are upgrading 1.18 -> 1.20, without 1.19. But you cannot reproduce the error on your setup with the full mediawiki db in attachment 1 [details]? (A note on that version: core is 1.18, confirm account is likely 1.18, but may be slightly older, since we use ConfirmAccount in production as shared from another db, only testing uses (and upgrades) locally. In any event: with these dbs update.php works smooth if ConfirmAccount is inactive, and fails if active).

(In reply to comment #10)
> I work on the same data base: the index 'acr_deleted_reg' does not exist in
> our
> DB to be dropped. I don't know what the patch-account_credentials.sql is
> intended to DROP for what ConfirmAccount-Version by the SQL:

There was 'acr_deleted_reg ON account_requests (acr_deleted,acr_registration);' for a while before it was replaced by one that included acr_type. I split out the removal change in https://gerrit.wikimedia.org/r/#/c/45070/.

I ran into this problem (on 1.20.2 and mysql-server-5.1.54p9 on OpenBSD).   A manual hack seemed to fix it for me:

mysql> alter table account_requests modify acr_email varchar(255) not null;

Then I re-ran update.php.

The column was already declared as TINYTEXT, which has a length of 255 and is otherwise similar to VARCHAR(255).  But not exact, as you cannot specify an index length on a TINYTEXT.