Early Bird Registration for DrupalCon Portland 2024 is open! Register by 23:59 PST on 31 March 2024, to get $100 off your ticket.
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
Comment | File | Size | Author |
---|---|---|---|
#12 | aid_bid.patch | 2.66 KB | sime |
Comments
Comment #1
simeSounds 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
Comment #2
ALT83 CreditAttribution: ALT83 commentedThanks 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
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
Comment #3
simeI 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.Comment #4
ALT83 CreditAttribution: ALT83 commentedI 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...
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
Comment #5
simeFrom 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.
Comment #6
simeSorry, 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.
Comment #7
ALT83 CreditAttribution: ALT83 commentedThanks 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.
Comment #8
agentrickardThis 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.
Comment #9
agentrickardFor details, see: http://dev.mysql.com/tech-resources/articles/visual-basic-datatypes.html
Comment #10
agentrickardI'm setting this to 'by design,' with the caveat that if you need more than 128 records, you alter the table.
-or-
Comment #11
killes@www.drop.org CreditAttribution: killes@www.drop.org commentedwell, it is by design, but the design seems to be a bit dated. We should patch this for 4.8.
Comment #12
simeThat 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.)
Comment #13
ALT83 CreditAttribution: ALT83 commentedCheers agentrickard, the first line of code you gave me sorted the problem perfectly!
Comment #14
simemarking "needs review"
Comment #15
agentrickardI 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.
Comment #16
agentrickardOne 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.
Comment #17
Dries CreditAttribution: Dries commentedI tested this patch and it works as advertised. The PostgreSQL update in updates.inc looks wrong though.
Comment #18
Cvbge CreditAttribution: Cvbge commentedHi,
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()).
Comment #19
Dries CreditAttribution: Dries commentedModified the patch and committed it to CVS HEAD. Thanks guys! Good job.
Comment #20
(not verified) CreditAttribution: commented