I just installed Drupal. Most stuff seems to be working - I can add new stories, comments, etc. But on certain updates involved in that process, I get a database error like those below.
I ran the GRANT command on the drupal database in MySql. Is there some other part of MySql that I need to grant access to the drupal user for? Like systems tables? Thanks.
query: LOCK TABLES sequences WRITE in c:htdocsp0drupalincludesdatabase.mysql.inc on line 69
Or
user error: Access denied for user: 'zzzz@127.0.0.1' to database 'drupal'
query: LOCK TABLES sequences WRITE in c:htdocsp0drupalincludesdatabase.mysql.inc on line 69.
warning: Cannot modify header information - headers already sent by (output started at c:htdocsp0drupalincludescommon.inc:32) in c:htdocsp0drupalincludescommon.inc on line 324.
Comments
version # -- forgot
Drupal version # is 4.1.0.
And, the full text of the first error was:
user error: Access denied for user: 'zzzz@127.0.0.1' to database 'drupal'
query: LOCK TABLES sequences WRITE in c:htdocsp0drupalincludesdatabase.mysql.inc on line 69.
LOCK TABLES
I have the same problem with a pay host.
If anyone have a solution you are wellcome...
Drupal version # is 4.6.3.
Solution 1: Ask your hoster
Solution 1: Ask your hoster to grant your database user the LOCK TABLE permission.
Solution 2: Switch hosters (and make sure they support locking tables).
--
Drupal services
My Drupal services
--
Drupal services
My Drupal services
maybe
http://www.mysql.com/doc/en/Access_denied.html#c968?
That was it -- thanks ax
Ax,
I granted all to mysql.* as was suggested by the link you provided. After restarting mysql, that cleared it up.
I can make the problem appear again by revoking all from mysql.*. Then to get it going again -- grant all to mysql.* and restart mysql. So, I'll post this as a (possible) documentation bug.
Thank you ;->
And it's only MySql 4.x
Just tried with MySql 3.23.55-max-nt and did not have the problem.
LOCK and MySQL4
RTFM :) Here is the important bit from the documentation of LOCK:
--
Kjartan
a global permission is problematic
A general web host is unlikely to grant a global privilege like this. At least that is my experience. This is likely to continue to frustrate admins as most web hosts switch to mysql4
Any web host is problematic
There are better odds of getting a grant to LOCK TABLES than a grant to everything. You should still require usage access on the database and table to do this, so there should be no security risk (unless there are bugs in MySQL).
On smaller sites the lock shouldn't be necessary, it only exists to prevent the same id being used twice in case of multiple users posting more than once.
--
Kjartan
Per-database GRANT appears to work fine
I tried just granting to drupaldbname.* and it worked just fine. No mysql.* permissions required here. (Debian 3.1, mysql-4.0.24-10)
http://dev.mysql.com/doc/mysql/en/lock-tables.html
workaround for mysql4
can anyone recommend a workaround when your web host will not grant permissions to the system tables. i am in this situation with a client.
thanks.
If you can't get the access from your host...
(See the link to mysql.com above.)
I wouldn't advise this unless you are running a sole-proprietor site without a lot of commentors.
You can go to the section in database.mysql.inc that says:
Remove the top and bottom lines, so it just says:
Runs fine.
Again, I would not advise it if you have lots of users at once; from what I could find on the LOCK/UNLOCK query, not using it could slow things down or cause problems on a big and busy site.
A safe workaround
Here is the way I solved the problem
in database.mysql.inc, replace
by
As far as I am concerned, this is concurrent update safe.
Thanks! Found a typo...
Thanks for the fix Gilles - worked great! Also found a typo, replace:
with
Difference is the $name.
-------
Unlimited FREE Job Posting
http://www.uberjobs.com
eek
I don't suggest this. It caused my site to go into infinite loops sometimes, specifically when trying to add a new vocabulary.
I guess the only solution is to just remove the locks and hope that I don't get too busy and switch hosts as soon as possible.
That's because...
I think I found why this happened. The code above doesn't give a way to create a new sequence if it doesn't exist. I had the same problem. But using the following code seems to work:
I think I may have a logical problem where it starts with 2 instead of 1, but otherwise it seems to be working.
Peter
DONT USE THIS !!!!
I struggled for two days after upgrading
http://drupal.org/node/25649
http://www.econsultant.co.za
IMHO it is *NOT* safe!
Hi,
i think the result of the function mysql_affected_rows() is not for shure from the same thread - another instance of db_query(...) may be responsible for the current result....
Arne
where is 'database.mysql.inc"?
under which directory? tks
I think /database/database.mysql is meant
I think /database/database.mysql is meant without the .inc file extension.
Paddy.
http://deburca.org, and http://amadain.net
Paddy.
http://deburca.org
Database.mysql.inc is in....
Look in your /includes folder and you've find it.
/includes/database.mysq.inc
And about line 152 you'll see the code in question. Make sure you replace the right lines (codes) and make change to the typo error: $name instead of name
Sometimes doing the right thing isn't doing the right thing
It works!
Hey man, thanks for posting the fix for this problem. It's been driving nuts for the last 3 days....
Thanks,
Rusty
Sometimes doing the right thing isn't doing the right thing
Bump
GoDaddy.com, one of the larger web hosts, doesn't allow LOCK table rights. I do have an email into them.
Personally, I think it's in a web hosts interests to allow for LOCKing of tables. Working around the solution can introduce problems, as documented below with commenting-out/working-around the Lock Tables issue.
Let's see what GoDaddy says.... If enough people start griping to them....
KnowProSE.com
eAsylum.net
Anyone?
Has anyone found a SAFE workaround for the table locking problem when hosting Drupal at GoDaddy?
There is no safe workaround.
There is no safe workaround. Drupal uses lockign to ensure the uniqueness of database IDs. if you don't use lockign, you will lose this safety measure.
--
Drupal services
My Drupal services
--
Drupal services
My Drupal services
Would updating db_next_id like this solve your problem ?
I have, or I should say had, the same problem until I noticed that a proposed change to db_next_id solved my problem.
I have
The (UN)LOCK statements are removed - and I no longer get errors when creating content.
I hope this helps.
Paddy.
http://deburca.org, and http://amadain.net
Paddy.
http://deburca.org
As I tried to explain, those
As I tried to explain, those LOCK statements are there for good measure. On a low traffic site you might get away with this change, on a high traffic site, you might run into trouble.
--
Drupal services
My Drupal services
--
Drupal services
My Drupal services
But for us, a solution is Required
No all of us have the luxury of having GRANT ALL on our mysql databases.
I, on my low traffic website, need a solution and this works for me. I am more than willing to test other solutions that do not require a LOCK.
Paddy.
http://deburca.org, and http://amadain.net
Paddy.
http://deburca.org
You don't need GRANT ALL.
You don't need GRANT ALL. GRANT TABLES LOCK should be sufficient.
http://dev.mysql.com/doc/mysql/en/grant.html
Change hosters if the current one doesn't offer what you need to properly run Drupal. Or change software.
--
Drupal services
My Drupal services
--
Drupal services
My Drupal services
My apologies - GRANT TABLES LOCK
My provider, unfortunately, does not alow me to do a GRANT - the db user id does not have the permission. This is something I have to live with. My provider, unfortunately, does not provide mod_rewrite support - this is something I also have to live with.
Why? Because it took me a while to find a provider that is helpful, responds quickly to queries and that I trust with my websites.
As can be seen by this thread I am not the only case - and as much as I don't want to change provider, I don't want to change software.
Paddy.
http://deburca.org, and http://amadain.net
Paddy.
http://deburca.org
What about just UPDATE ?
The MySQL reference manual states: "there are two good ways to implement the reading and incrementing of the counter: (1) update the counter first by incrementing it by 1 and only after that read it, or (2) read the counter first with a lock mode FOR UPDATE, and increment after that. The latter approach can be implemented as follows:
A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus it sets the same locks a searched SQL UPDATE would set on the rows.
Please note that the above is merely an example of how SELECT ... FOR UPDATE works. In MySQL, the specific task of generating a unique identifier actually can be accomplished using only a single access to the table:
The SELECT statement merely retrieves the identifier information (specific to the current connection). It does not access any table. "
Paddy.
http://deburca.org, and http://amadain.net
Paddy.
http://deburca.org
I've raised the issue on the
I've raised the issue on the developer's list and was made aware of this old issue:
http://drupal.org/node/21439
Also, SELECT FOR UPDATE is only available from mysql 4 while we still support mysql 3.
--
Drupal services
My Drupal services
--
Drupal services
My Drupal services
Thank you
Thank you very much - now, I better understand the reasons behind the current code.
Can something be formalised somewhere somehow to help those who are not masters of their mysql databases, to implement a working alternative to the 'LOCK' statement ?
Paddy.
http://deburca.org, and http://amadain.net
Paddy.
http://deburca.org
There is a possible
There is a possible aternative which is dicussed on the devel list, but it will only be available in future releases if deemed worthwhile.
--
Drupal services
My Drupal services
--
Drupal services
My Drupal services
Great Solution: 'or change software'
'or change software'
Yes this solution seemed to work for me. Not only did I not have to deal with LOCK TABLE privilege errors anymore, but it also solved the problem of having to deal with arrogant developers and administrators who disregard the needs of users. I recommend!
Changing software may work for you - but I like where I am
I like drupal - and from my experience with submitting bugs reports - I find that the Drupal developers are a very good hardworking bunch - and I would like to thank them for their time and effort in producing an excellent system.
This system, in my opinion, has it's short commings - all do. The whole excercise of finding an ISP, CMS and DB is one of compromise. I am not willing to compromise on either my ISP or my CMS - so I have to compromise on this 'LOCK TABLES' issue.
Yes - this issue is a pain in the perverbial! - everytime I do an upgrade I have to re-engineer the code.
One fine day, I will get fed up and tired of re-engineering the code - I will then have to examine why on earth there is a sequence table - and then I will have to produce a patch that removes the sequence table completely and make drupal rely on the unique ID's of each table instead.
I hope that that fine day will come soon.
Paddy.
http://deburca.org, and http://amadain.net
Paddy.
http://deburca.org
I solved the problem - moved from GoDaddy to Brinkster
Brinkster gave me full permissions to the MySQL server, and the problem is GONE...
They are a bit more expensive than GoDaddy, but they got a great review from Cnet recently.
I couldn't get why GoDaddy doesn't want to set those permissions...
Brinkster Web Hosting
My host solved the problem too - digitalspace.net
Hello all, I initially experienced this issue with the LOCK TABLES, and submitted it as a support ticket with my host, digitalspace.net.
They responded positively, and gave my database user the priviliges needed. I wanted to give them props, since they have proven themselves to me to be quite flexible, cost-effective and proficient in their service offerings. I didn't check GoDaddy vs Brinkster as far as being 'a bit more expensive' but I wouldn't hesitate to suggest that digitalspace is much less expensive for the results provided. EDIT: Checked Brinkster's prices, and they're cometitively priced, so go for whichever!
http://www.digitalspace.net/
I believe it is worthwhile to account for hosts which support resolving this issue, not so much as a marketing lip service, as a useful resource of supporting hosts.
Peace~mdc
Same problem with MySQL 5 ?
I Have a dubt if with MySQL 5 we wil have the same problem.
Anybody knows this answer????
There is/was a discussion among developers
There is/was a discussion among developers about this topic.
The last suggestion (September 27) involved using the DB's builting auto-increment mechanisms. If adopted, this could/would eventually do away with the sequences table completely.
Remembering, of course, that if a solution is devised within Drupal that solution may need to work on mysql 3, 4 and 5.
Paddy.
http://deburca.org, and http://amadain.net
Paddy.
http://deburca.org
"LOCK Table" issue still
"LOCK Table" issue still exist in Drupal 4.7 beta 4 . I tested it in my site hosted at Godaddy.com.
The patch also doesn't work for beta.
two nodes that may be of interest
Paddy.
http://deburca.org, and http://amadain.net
Paddy.
http://deburca.org
godaddy fix
can someone please confirm and post the correct method/code for fixing this problem when using godaddy's hosting service?
Sorry
Sorry, but to my knowledge there's no fix.
--
Tips for posting to the forums.
When your problem is solved, please post a follow-up to the thread you started.
An un-spupported, un-recommended thing to do could be..
For 4.7 beta 6 sites that are not busy and don't have multiples of users adding content you could try the following (it works for me - but then it is for a single user site..)
/includes/database.mysql.inc, replace db_next_id with
/includes/bootstrap.inc, replace variable_set with
and replace cache_set with
Paddy.
http://deburca.org, and http://amadain.net
Paddy.
http://deburca.org
LOCK TABLE permission
I use a free hot, hence I get the LOCK TABLE problems. According to your advice, I have replaced "db_next_id" in database.mysql.inc, "variable_set" and "cache_set" bootstrap.inc. The error messages related to LOCK TABLE doesn't appear, but I get an other problem when I try to configure my site.
For example, I want to change the 'Slogan' from 'drupal' to 'myname'. When I click to button 'Save Configurations', it doesn't work. I get a new page with an error message.
"Impossible d'afficher la page
La page que vous recherchez est actuellement indisponible. Le site Web rencontre peut-être des difficultés techniques, ou vous devez modifier les paramètres de votre navigateur.
...
"
s it only system updates?
Is it only system updates or do you get the same problem when adding normal content?
Paddy.
http://deburca.org, and http://amadain.net
Paddy.
http://deburca.org
im using 4.6...civicspace
im using 4.6...civicspace actually
the users above claim that the code works...i'm just not sure which code to use and if anyone confirmed that this worked...
is this patch the best way
is this patch the best way to solve the issue with godaddy?
http://drupal.org/node/21439
The short answer is - No!
The _BEST_ solution is to get GRANT LOCK on TABLES permission - in fact this is the _only_ correct and supported solution.
One of the many work arounds is updating the code as described above. With on great big caveat - _if_ you have a site that is updated by multiple users at the same time you _may_ get data corruption.
This particular fix happens to work but only for low traffic web sites. There are other similar solutions - but the majority will only work for low traffic web sites.
Paddy.
http://deburca.org, and http://amadain.net
Paddy.
http://deburca.org
Can I fix via phpMyAdmin?
Using:
Drupal 4.6.6
PHP Version 4.3.2
MySQL 4.1.9
Newbie on all of the above...
In a post way above this one, it says to run this:
My host has phpMyAdmin installed, and I am trying to figure out if I can run the above code in it.
There is an SQL tab in phpMyAdmin that says "Run SQL query/queries on database richardblakehoodcom" (richardblakehoodcom is the database name). Is that where I can run this code?
Then it also has the ability to import (upload) a text file and run that code, which is how I created the Drupal tables to begin with (using database/database.mysql). Maybe I can put the code in a file and do it that way?
Finally, if I can do this using phpMyAdmin, I do not entirely understand the code above.
drupaldbname - is database name, understand but do you need the brackets
< >
around it? I think not, just making sure.drupaldbuser - is this the username for the database, or the admin (first) username for Drupal I set up?
drupalsitelocation - is this the URL to the location of my drupal files? (e.g. www.mystite.com/drupalfiles/ - with or without the last slash?)
Thanks,
Rick
PS: just FYI, the exact error I got when creating the first user was this:
user error: Access denied for user 'user1168837'@'%' to database 'richardblakehoodcom'
query: LOCK TABLES sequences WRITE in /home/cust2/user1168837/html/drupal/includes/database.mysql.inc on line 66.
It did however, seem to create the user (it emailed me the password) but when I login, it won't let me into the admin section.
GRANT LOCK TABLES ON
database is in your case richardblakehoodcom
user is the username you have in $db_url (sites/default/settings.php); it's the username for the database, not the first account.
password is obvious.
host is the server on which the database is located; usually localhost.
You can indeed run this query at the SQL tab.
If you keep getting the error; you need to contact your host and ask for global table lock privileges; if your host is godaddy, move to another host (or package?) or another CMS.
--
Tips for posting to the forums.
When your problem is solved, please post a follow-up to the thread you started.
Will this be a problem with other sites using this database?
Thanks for the above.
Just before I try that - this database has other tables in it besides Drupal tables. Will it "hurt" those tables to do this to the whole database?
Also, I am a little confused on what the host is.
In phgpMyAdmin I see something like this:
MySQL 4.1.9-standard-log running on abcde1234atl2 as user123456@64.000.00.000
Woudl host be the IP address "64.000.00.000" or what they are calling the server name "abcde1234atl2" ?
Host
It's the same as the host part of $db_url in sites/default/settings.php (mysql://user:password@host/database). It won't hurt ;-) the other tables.
--
Tips for posting to the forums.
When your problem is solved, please post a follow-up to the thread you started.
OK I tried it - error - but could be my mistake - please look
This is what I got back when I ran the SQL in phpMyAdmin:
SQL-query :
GRANT LOCK TABLES ON richardblakehoodcom . * TO user1234567@64.000.00.000 IDENTIFIED BY 'abcdefghi'
MySQL said:
#1044 - Access denied for user 'user1234567'@'%' to database 'richardblakehoodcom'
I wonder why the '%' after the @ sign?
Did I do something wrong or does this look like the server won't allow it?
Was it correct to have ' ' around the password?
--- Other info ----
The info given to me about my database is like this:
SQL Server name: abcdefg0002jki
SQL IP address: 216.000.000.000
Database Name: richardblakehoodcom
User name: user1234567
Password: abcdef4h7s
I am not using this: SQL Server name: abcdefg0002jki
anywhere, and wonder if that is a problem?
This is in my settings.php file:
It could be that your don't have GRANT permission
Without the GRANT permission, I think that you can't GRANT permissions to databases.
Paddy.
http://deburca.org, and http://amadain.net
Paddy.
http://deburca.org
Most likely
@rick; this means you need to contact youir host.
--
Tips for posting to the forums.
When your problem is solved, please post a follow-up to the thread you started.
My host won't do it - thanks for your help
Ok I contacted my host (Interland, now Web.com) and got this:
"Thank you for contacting Web.com Technical Support regarding your database privileges.
Unfortunately, you will not be able to grant your user Lock_tables_priv due to the security constraints of a shared server. I apologize for any inconvenience."
So...can't use Drupal on Interland (Web.com) I guess (I mean use it the "right" way without the hacks mention above). Too bad.
Thanks again for all the help above.
Grant permissions for LOCK TABLES and godaddy.com ...
Just wanted to add in that I have received a definitive answer from godaddy.com that the LOCK TABLES function is not allowed on their shared hosting accounts. Lucky for me, they did offer as an option to upgrade my hosting to a virtual dedicated server for $34.95 a month. I think I'll just move elsewhere. :)
This also means that running the GRANT query in the SQL prompt of myPHPadmin will not fix the problem either.
I'm turning to the hacks presented above so I can at least experiment with Drupal. Will likely migrate site soon to get full functionality.
Just for ease of those searching an answer (and who may not have read this entire page), this response is in regards to the error:
Thanks again to those who help post the hacks and the warnings.
Workaround discussion
Hi,
I ran into both this and the CREATE TEMP TABLES problems with my new host (startlogic.com). Luckily they released rights for both of these after I submitted a support ticket.
A brief discussion of why table locking is done is probably a good idea for those non-programmers. Basically, when a new node (or user, or whatever) is created it needs to get a unique number. It does this by checking the sequences table (which holds the node id of the last node created), increases it by one, remembers it and writes it back to the sequences table. If only one user at a time was using the system, table locking wouldn't be needed. In a multi-threaded enviornment, table locking is used to prevent the following:
User 1 and User 2 post at almost the same time.
User 1's thread checks the sequence table and sees 17 was the last one used.
User 2's thread checks the sequence table and sees 17 was the last one used.
User 1's thread writes 18 to the database and remembers that the new node should be 18.
User 2's thread writes 18 to the database and remembers that the new node should be 18.
User 1's thread writes the new node to the database using nid 18.
User 2's thread trys to write the new node to the database using nid 18, but nid 18 is already in use so the INSERT query fails (or User 1's content is overwritten).
With table locking the following occurs.
User 1's thread locks the table.
User 2's thread trys to lock the table, but it's already locked, so it waits.
User 1's thread does it's stuff then releases the lock.
User 2's thread then continues and gets a correct new nid.
This ensures everybody is happy and gets a valid nid.
It sounds like the devs are strongly considering dropping the internal sequence table of drupal in a future release in favor of using the databases built in autoincrement features. This will fix these problems because the DB would assign the new nid instead of drupal. Since I can't see any good reason for having Drupal do it's own sequence tracking, this seems like the best route.
Since I don't know when (or if) this will be done, I'm considering writing a patch to help those who are stuck w/ an uncooperative host. The best solution for the moment would be one that:
My idea is to create a [sequence]_sequencing table for each sequence stored in the sequence table. Code would be something like this (this is just an idea, please don't use this code as it probably won't work as written):
Anyone have any suggestions on how this can be improved? One point I wish someone would look at is how to add the prefix (if enabled) to the sequencing table name. I'm not comfortable enough with the prefixing code to have played with it. It might already be done in the above code at $name = db_prefix_tables($name), but I'm not sure.
I haven't actually tested this at all, I don't have a drupal install that I can break at the moment to test it out :) Please feel free to tell me if this is a really dumb idea, or if there's any issues I'm not seeing.
Thanks,
Niosop
Why not change the ID column on the node tables to be auto inc?
I know that if it were easy it would already be done.
Would it not be possible to have the respective ID columns of the node, user, etc... tables as auto increment. I imagine that the simple act of inserting a new record into these tables would give the unique ID.
Also, after such an insert, how do you get the new ID back?
This, of course, would mean that the ID would only be available _after_ the entry is created and so hooks that would require the node ID could only be processed, again, after the node is sumbitted.
I must have a look at the development thread(s) and see what is being proposed.
Paddy.
http://deburca.org, and http://amadain.net
Paddy.
http://deburca.org
That's the idea
Yes, changing the tables to use auto_inc would be the best way to do it, and I think that's what may be done in the future. I just think that modifying the schema should be left to the core dev team, and removing/modifying calls to db_next_id would affect several modules.
In the end, using auto_inc would be alot cleaner than the above code, but I wanted to do it in such a way that it could just be dropped in w/o modifying schema or any modules.
As for how you get the new ID back, you just insert a uniq value into the table w/ the INSERT statement (like sid, or timestamp, or combination), then query for that value.
If you would like, another method of doing the above w/o sequencing tables would be to modify each of the tables to use auto_increment, and have db_next_id just create a new record, using a placeholder value, then query for that value to get the id, then delete that record and hand the id back to the requesting function. This is much cleaner, but requires modification of existing database schema.
This would mean that the id is available before the actual entry is created, and it should be transparent to the calling functions.
Niosop
Current Status and solution to LOCK Tables
I see this thread going back to 2003, but never encountered it till I upgraded a site to Drupal 4.7.
--------------
Error: user warning: Access denied for user 'username'@'localhost.localdomain' to database 'database-name' query: LOCK TABLES variable WRITE
Version MySQL 4.1.12
-----------
I have full access to the server but am not clear on the best 'fix' to this issue.
IWhile there may be more it seemed like these were the two leading contenders in this thread:
One suggestion:
GRANT LOCK TABLES ON database.* TO user@host IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
To use LOCK TABLES in MySQL 4.0.2 you need the global LOCK TABLES privilege and a SELECT privilege on the involved tables. In MySQL 3.23 you need to have SELECT, insert, DELETE and UPDATE privileges for the tables.
Second suggestion:
Per-database GRANT appears to work fine
Jacob Lee Anawalt - July 7, 2005 - 00:24
I tried just granting to drupaldbname.* and it worked just fine. No mysql.* permissions required here. (Debian 3.1, mysql-4.0.24-10)
GRANT LOCK TABLES ON .* TO ''@'';
FLUSH PRIVILEGES;
http://dev.mysql.com/doc/mysql/en/lock-tables.html
---------
Despite having full access to the server I am unclear about exactly where to run these 'commands.' I went to some of the mysql links but those links end up in explanations that are TMI for my level of experience and knowledge.
I haven't installed drupal in over a year because of frustrations with it, but thought I'd come back and see how it was working now. Is this issue because I updated or do all new installs go through this database level error process?
Thanks, GZ
Use eith the command line or phpmyadmin
If you have full access, you should be able to use the command line
and, then
Another solution, is to install phpMyAdmin and run your commands from a SQL window.
Paddy.
http://deburca.org, and http://amadain.net
Paddy.
http://deburca.org
oops duplicate post
sorry, this was alredy mentioned.
start transaction?
Forgive my terrible ignorance, but why not just use InnoDB and 'START TRANSACTION' instead of 'LOCK TABLES', then 'COMMIT' after you're done.
Is the only reason compatibility with MySQL 3.x?
I'm just beginning in MySQL and PHP, so I'd be forever thankful for any clue you'd care to bestow upon me.
By the way.. I was directed to this node by a GoDaddy support email.
-Barry
I do not think that Drupal is 100% innodb compatible
From the search I have just made on innodb, I can se that there are a lot of unanswered questions and unresolved problems.
With INNODB, the COMMIT is not really necessary. What would be needed is a sequence number that is auto-incremented and unique. The 'FOR UPDATE' clause would then lock a record (or sequence of records) for update - enforcing data integrity.
Life would be a whole lot simpler.
But on a negative note, Oracle bought Innobase (the company behind INNODB). If so, it remains to be seen how mySQL develops, and if it continues to develop with INNODB.
Paddy.
http://deburca.org, and http://amadain.net
Paddy.
http://deburca.org
A bit off-topic, but for
A bit off-topic, but for those looking for lower-cost webhosts that permit you to execute "GRANT LOCK TABLES . . ." I've haven't run into trouble at lunarpages. Hopefully that won't change, as, judging from the comments so far, this seems to be a trend . . . (and no, I'm not affiliated with lp). I'm thinking bluehost might allow this too, as they seem to offer a pretty robust package with lots of user control (unlike godaddy). Not sure how good either of these hosts are for very high-volume activity.
Heres something thats
Heres something thats driving me up the wall. Im running into a problem like this where it says I don't have access to the lock tables. However, I do. I grant lock tables everything works for a random period of time (a day or a week, its random) and then for whatever reason all users on my server that had all the permissions lose the create temporary tables and lock tables. I can then regrant them and they are ok for a period of time. It goes back and forth like this. Any ideas?
Contact your provider, they
Contact your provider, they may have set up a cronjob to take away these permissions.
I am the provider (tho its a
I am the provider (tho its a dedicated server hosted by someone else, but I have root)
That would make sense if it wasn't sporadic and I dont see any crons running that would cause that.
subscribed
subscribed
Lock Tables still issue with Drupal 5.5 and MySql 4 ?
Much of this discussion is several years old and I haven't seen mention of Drupal 5 or Drupal 6.
I am about to deploy a new Drupal site (5.5) to a host that does not support table locks (MySql 4.0.17).
giles and Theo posted a work around at http://drupal.org/node/1190#comment-26434. I write to ask if this work around remains relevant to Drupal 5 (or 6) and MySql 4?
Happy New Year!
Lock tables crashes
This still seems to be a problem: (for a site using drupal 5.3 and MySQL 5)
Here is a recent posting:
This problem has been driving me and my client absolutely nuts!! We had a fine working site with lots of modules (Drupal 5.3, mySQL 5) and now the site is crashing twice a week with some variation of the error:
Warning: Table ' (pretty much any drupal table but usually 'system', 'watchdog' or 'cache')' was not locked with LOCK TABLES query: . . . . .
The site can be (temporarily) repaired by using the 'repair table' command in phpMyAdmin, but then the site inevitably crashes again a week or so later. (Very aggravating). I'd like to be spending as little time in phpMyAdmin as possible because this isn't my area of expertise, but drupal seems to be very unstable in its relationship to the database. I really just want to be worrying about updating content in the CMS.
My web host, 'Servage' tells me in their support ticket that:
and sure enough, I can't grant 'LOCK TABLES' privileges through phpMyAdmin.
I have several other drupal sites of various versions and none of them have experienced these problems. I understand that I am running 5.3. Will these issues resolve if I upgrade to 5.7? I would upgrade to 6 but I am not sure that all of my modules have been ported over yet. This site is the only one which uses MySQL 5. My others all use MySQL 4. Could that be the problem? I have installed the DB maintenance module but that hasn't helped either.
I am kind of at my wits' end with this one. Can anyone give me any advice?
select query
Hi i am new in this type project. I need help , i have to fatch the data from database and print it .
So i need help. Please solve this problem. Its urgents.
Privileges reverting back
I've successfully changed global privileges to allow LOCK TABLES but every month the privileges revert back for all users.
The privileges usually revert back as I am working on a drupal site on the server. Unfortunately, I don't remember exactly what I was doing on the site when the privileges reverted.
Anyone have any idea why the privileges would be reverting back?
Thanks.