I wasn't sure which component to put this under.

I have been creating many access control rules, to deny certain usernames being used. I currently have 125 rules in the list (but I have aids up to 127 in my database with 1 or 2 aids missing - which were rules I deleted)

I now get the following error every time I try to add any additional rules


user warning: Duplicate entry '127' for key 1 query: INSERT INTO access (aid, mask, type, status) VALUES ('133', 'examplerule', 'user', 0) in /home/.jaunt/diversity/woo.sh/includes/database.mysql.inc on line 120.

Is there a known limit on the number of rules that can be applied?

Thanks,

Alex

CommentFileSizeAuthor
#12 aid_bid.patch2.66 KBsime
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

sime’s picture

Sounds like...
the access table key has been incremented ahead of the value in the sequences table. I don't know all the ways this can happen, so I won't hypothesize.

If you look at the structure of access table in phpmyadmin, the current autoincrement is shown next to "Next Autoindex". So make the value in sequences (WHERE name='access_aid') higher than that.

HTH
.s

ALT83’s picture

Thanks for your quick response.

I changed the aids for the last two entries in my list, plugging up gaps I had at aids 17 and 27 and then successfully added another entry.

The next autoindex value is 128 and I have 126 rows.

I then got virtually the same error. Tried entering values manually in phpMyAdmin, above 127, but received the following error


Error

SQL query: Edit

INSERT INTO `access` ( `aid` , `mask` , `type` , `status` )
VALUES (
'129', 'example', 'user', '0'
)

MySQL said: Documentation
#1062 - Duplicate entry '127' for key 1 

So I guess that highlights, that it is not strictly a Drupal error...

Might it be the autoindex value blocking this? Can it be raised? (Using phpMyAdmin - 2.8.0.3, MySQL client version: 5.0.16)

Thanks,

Alex

sime’s picture

I don't really understand how you're getting a key error manually inserting a new row with phpMyAdmin, if you're explicitly defining an`aid` that is not in use.

So, with a disclaimer about uncertainly, you can change the autoincrement like this:
ALTER TABLE `access` AUTO_INCREMENT =N where N is the new value.

ALT83’s picture

I don't understand it either, the aids I tried to add manually were certainly not already taken.

Thanks for the MySQL script, worked perfectly (redefined autoindex as 300)

One thing I have noticed though is that the number after VALUES (in this case 140), increases by 1 everytime I get this error, i.e. everytime I unsuccessfully add a rule... not sure how relevant that is...


user warning: Duplicate entry '127' for key 1 query: INSERT INTO access (aid, mask, type, status) VALUES ('140', 'example1', 'user', 0) in /home/.jaunt/diversity/woo.sh/includes/database.mysql.inc on line 120.

But the duplicate entry continually references 127, which I know for a fact is not duplicated in any other row...

Very puzzled...

I don't mind carrying out any more experimentative scripts you offer as this is only a test installation...

Cheers,

Alex

sime’s picture

Status: Active » Closed (fixed)

From what I know, the sequence table tells drupal what the next `aid` will be. Drupal ignores the auto-increment so as to support older versions of mysql.

I won't comment further about the cause. I've certainly not used mysql 5.x.
If you are testing, yes, maybe try a new installation and see if it happens again.

sime’s picture

Sorry, I should explain that I've closed the ticket, but if you think this might be a conflict with mysql 5.0.16, or something, by all means open it again and update the title.

ALT83’s picture

Title: Access Control Rules Error » Access Control Rules Error (MySQL 5.x Compatibility Problem?)
Component: base system » database system
Status: Closed (fixed) » Active

Thanks for your help sime.

If anyone has any thoughts on this topic, that would be most appreciated as it is important for my purposes that I can create more than 127 access rules.

agentrickard’s picture

This is not a MYSQL issue, nor is it a sequences issue.

The aid column in the database is tinyint(10), which only allows 128 records.

Reset this column to be an INT.

See http://drupal.org/node/66961 for a similar bug.

This is caused by a design assumption that certain tables will never grow beyond X records.

agentrickard’s picture

agentrickard’s picture

Priority: Critical » Normal
Status: Active » Closed (works as designed)

I'm setting this to 'by design,' with the caveat that if you need more than 128 records, you alter the table.

ALTER TABLE `access` CHANGE `aid` `aid` INT( 10 ) 

-or-

ALTER TABLE `access` CHANGE `aid` `aid` MEDIUMINT( 10 )
killes@www.drop.org’s picture

Version: 4.7.2 » x.y.z
Status: Closed (works as designed) » Active

well, it is by design, but the design seems to be a bit dated. We should patch this for 4.8.

sime’s picture

Title: Access Control Rules Error (MySQL 5.x Compatibility Problem?) » Access Control Rules Error - Bump up access.aid and boxes.bid
Version: x.y.z » 4.7.2
Priority: Normal » Critical
FileSize
2.66 KB

That was stupid of me. The number 127 should have given it away. And to think I had 3 fingers added to each hand just last week. ;-)

Here is a patch for access and boxes. Includes updates.inc, but I doubt I'll get that right first go (ie. the postgre stuff.)

ALT83’s picture

Cheers agentrickard, the first line of code you gave me sorted the problem perfectly!

sime’s picture

Version: 4.7.2 » x.y.z
Priority: Critical » Normal
Status: Active » Needs review

marking "needs review"

agentrickard’s picture

I also wonder why some tables autoincrement and some use 'sequences.' Not sure the patch addresses this.

Shouldn't we be uniform?

And sime, trust me, I spent an hour looking at this problem a few weeks ago when we mysteriously couldn't add blocks anyomre.

agentrickard’s picture

One more note:

I went through the 4.7.2 files for database.4.0.mysql and database.4.1.mysql and confirmed that only the 'access' and 'boxes' tables use tinyint for their primary numeric keys.

Dries’s picture

I tested this patch and it works as advertised. The PostgreSQL update in updates.inc looks wrong though.

Cvbge’s picture

Hi,

I've checked database.pgsql for 4.7 and HEAD and tables 'access' and 'boxes' use SERIAL data type, which can hold really big numbers. There is no need to increase or change it.

Thuse, please remove any postgresql parts from the patch (namely: both db_add_column() calls from system_update_187()).

Dries’s picture

Status: Needs review » Fixed

Modified the patch and committed it to CVS HEAD. Thanks guys! Good job.

Anonymous’s picture

Status: Fixed » Closed (fixed)