Posted on: 01/02/05 11:33pm
By: trenchteam
Checked my error log and it says I can access my database due to locked table???
Okay, 2 years and first time.
Posted on: 01/03/05 12:28am
By: trenchteam
Okay, I found it. Heres the ERROR. No one can comment in my BLOG. Heres the error in the log.
*** dummy entry ***
1/1/2005 7:14:00 PM - [pear_error: message="mail() returned failure" code=0 mode=return level=notice prefix="" info=""]
1/2/2005 11:02:45 AM - 1044: Access denied for user: 'trench@%' to database 'xxx'. SQL in question: LOCK TABLES gl_comments WRITE
1/2/2005 8:31:05 PM - 1044: Access denied for user: 'trench@%' to database 'xxx'. SQL in question: LOCK TABLES gl_comments WRITE
1/2/2005 9:19:00 PM - 1044: Access denied for user: 'trench@%' to database 'xxx'. SQL in question: LOCK TABLES gl_comments WRITE
*** dummy entry ***
1/1/2005 7:14:00 PM - [pear_error: message="mail() returned failure" code=0 mode=return level=notice prefix="" info=""]
1/2/2005 11:02:45 AM - 1044: Access denied for user: 'trench@%' to database 'xxx'. SQL in question: LOCK TABLES gl_comments WRITE
1/2/2005 8:31:05 PM - 1044: Access denied for user: 'trench@%' to database 'xxx'. SQL in question: LOCK TABLES gl_comments WRITE
1/2/2005 9:19:00 PM - 1044: Access denied for user: 'trench@%' to database 'xxx'. SQL in question: LOCK TABLES gl_comments WRITE
1/3/2005 12:55:47 AM - 1044: Access denied for user: 'trench@%' to database 'xxx'. SQL in question: LOCK TABLES gl_comments WRITE
1/3/2005 1:10:07 AM - 1044: Access denied for user: 'trench@%' to database 'xxx'. SQL in question: LOCK TABLES gl_comments WRITE
1/3/2005 1:30:08 AM - 1044: Access denied for user: 'trench@%' to database 'xxx'. SQL in question: LOCK TABLES gl_comments WRITE
1/3/2005 2:25:31 AM - 1044: Access denied for user: 'trench@%' to database 'xxx'. SQL in question: LOCK TABLES gl_comments WRITE
I was trying to comment myself, since others told me they couldnt comment. Not sure how to fix this problem.
Okay, 2 years and first time.
Posted on: 01/03/05 12:38am
By: Anonymous (tokyoahead)
Check your permissions with phpMyAdmin if you are allowed to do LOCK
try to execute the command manually with phpMyAdmin and check the error you get (if any)
Okay, 2 years and first time.
Posted on: 01/03/05 03:37am
By: trenchteam
allowed to do lock? Im not understanding?
What do you mean by executing the command?
BTW, my comments were working prior to the upgrade from 1.3.10 to the newest one. Not sure what happened.
I noticed that the file in the SQL folder for the upgrade from 1.3.10 to 1.3.11 includes this:
<?php
// bugfix: allow up to 40 characters for the story ID
$_SQL[] = "ALTER TABLE {$_TABLES['comments']} CHANGE sid sid varchar(40) NOT NULL default ''";
?>
It seems to have to do with my comments? Im wondering if something may have went wrong during the upgrade? Steps I can take? Possibly reload the COMMENTS Table? How can I execute this? I was thinking that I could drop the COMMENTS table, then reload it? Can someone tell me how I can do this???
This is all coming out of the top on the head based on the error and the upgrade I made. Sorry, Im ranting! Just disappointed that the comments are not working.
Comment.php ERROR!!
Posted on: 01/03/05 01:53pm
By: Dirk
As usual, a quick
search[*1] would have brought up the answer. See the latter half of
this thread[*2] for example.
The
FAQ[*3] also has some information (although only as a side note - I guess we need a separate entry for this ...).
bye, Dirk
Comment.php ERROR!!
Posted on: 01/04/05 12:11am
By: trenchteam
I tried a seach and came up with nothing. I actually searched a couple days before asking this question. Guess Im not a good searcher! anyways, thanks Dirk. I guess i have to solve this with my provider.
Comment.php ERROR!!
Posted on: 01/04/05 06:02am
By: trenchteam
My hosting service (godaddy) said they can't grant me those permissions because it is a global flag. Why would comments in 1.3.9 work but fail in version 1.3.10rc3?
http://www.geeklog.net/forum/viewtopic.php?showtopic=43804
jeez, this sux if I've lost my comment function.
Seems like Im in the same boat as this poor guy. My hosting service Webhost4life said they have to check with a manager before doing this change due to a GLOBAL FLAG. If they cant do this for me, is there any way around it? I dont want to change from Geeklog because of this, but comments are the lifeline of my site, if they cant change those permissions, I may have no choice.
Comment.php ERROR!!
Posted on: 01/05/05 12:06am
By: trenchteam
Grrr. Didnt work. Gonna make the switch I guess to wordpress. Dang. Lots of stories to move!
Comment.php ERROR!!
Posted on: 01/05/05 01:19pm
By: Dirk
I would be interested in the response from your hosting service about why exactly they can't grant you that permission.
Obivously, if this becomes a widespread problem, we will have to do something about it.
But for now, I fail to see how the permission to lock tables is any different from the other MySQL permissions, such as to drop, create, or modify tables ...
You could comment out the SQL queries that lock/unlock the comments table in comments.php, but you'll risk a corruption of that table when two users are posting a comment at the same time.
bye, Dirk
Comment.php ERROR!!
Posted on: 01/06/05 12:32am
By: enigmah
I am also having the same problem. I am using Ipowerweb.com
Comment.php ERROR!!
Posted on: 01/06/05 04:20am
By: Dirk
[QUOTE BY= enigmah] I am also having the same problem. I am using Ipowerweb.com[/QUOTE]
And what was their explanation why they can't give you that permission?
bye, Dirk
Comment.php ERROR!!
Posted on: 01/06/05 01:00pm
By: enigmah
They were able to give mysql users fully 'Lock Tables' privileges, now the commenting works..
Comment.php ERROR!!
Posted on: 01/08/05 09:54am
By: Anonymous (Driadan)
I have the same problem, I use the services from
servage.net[*4] and the response was:
You are fully able to update tables without first locking them. If you would like a transaction safe enviroment we recommend using the InnoDB tables type.
Since I don't know anything of databases, i was unnable to response to this.
I hope, there can be a workarround to this, I really like geeklog system. But, if I can't solve it, i'm afraid i'll have to change to another software
Driadan
P.S.:btw does anyone know what is a InnoDB table type?
Comment.php ERROR!!
Posted on: 01/08/05 04:18pm
By: Dirk
[QUOTE BY= Driadan] You are fully able to update tables without first locking them. If you would like a transaction safe enviroment we recommend using the InnoDB tables type.[/QUOTE]
Well, that doesn't explain why they're not able to give you table lock permissons ...
Using InnoDB tables isn't an option, as that would raise the minimum requirements to MySQL 4.x.
[QUOTE BY= Driadan] P.S.:btw does anyone know what is a InnoDB table type? [/QUOTE]
MySQL supports several types of tables. Geeklog currently uses MyISAM tables. InnoDB tables have additional features, but are not available everywhere (and they have disadvantages in other areas, e.g. it's difficult to create a consistent backup).
bye, Dirk
Comment.php ERROR!!
Posted on: 01/08/05 06:06pm
By: Anonymous (Driadan)
thanks for the info, now I'll ask them again...
As soon as I receive an answer i'll post here
Driadan
Comment.php ERROR!!
Posted on: 01/12/05 05:45pm
By: Anonymous (DeCEiVeR)
[QUOTE BY= Driadan] thanks for the info, now I'll ask them again...
As soon as I receive an answer i'll post here
Driadan[/QUOTE]
Well its been a while since I've tried to do anything about this but my hosting said they couldn't change it, thought they said it was a global flag and not just something on my account.
If anyone gets any headway let me know!!
Comment.php ERROR!!
Posted on: 01/12/05 05:51pm
By: Anonymous (DeCEiVeR)
[QUOTE BY= Dirk] I would be interested in the response from your hosting service about why exactly they can't grant you that permission.
Obivously, if this becomes a widespread problem, we will have to do something about it.
But for now, I fail to see how the permission to lock tables is any different from the other MySQL permissions, such as to drop, create, or modify tables ...
You could comment out the SQL queries that lock/unlock the comments table in comments.php, but you'll risk a corruption of that table when two users are posting a comment at the same time.
bye, Dirk[/QUOTE]
Well I commented out ""//DB_query("LOCK TABLES {$_TABLES['comments']} WRITE");"" and ""//DB_query('UNLOCK TABLES');"" in the comment.php file to get aroaund this problem for now, but it looks like after talking to about 3 different people at my hosting company that i'm not going to be able to get them to do anything about this. So if a fix becomes available please post.
Comment.php ERROR!!
Posted on: 01/12/05 06:03pm
By: Turias
Have you thought about switching providers? If they can't do something as simple as give your mySQL user LOCK privs, I would be worried about what else they can't do.
Comment.php ERROR!!
Posted on: 01/13/05 02:30am
By: Dirk
I have to agree with Turias. This talk about a "global flag" doesn't seem to make sense. As I said above, all that should be required is to grant you one more permission (that to lock tables). Surely that shouldn't be a problem?
Or if it is, I'd like to hear a more detailed explanation, please.
bye, Dirk
Comment.php ERROR!!
Posted on: 01/22/05 02:30am
By: Anonymous (Driadan)
well, they answered this:
The problem is that newer versions of mysql does not allow users to lock tables when using myisam tables. As we do not want to want to downgrade we will need to find another solution - are you sure you need to lock a table (and why would you do that?).
regards,
Driadan
Comment.php ERROR!!
Posted on: 01/22/05 03:32am
By: Dirk
The problem is that newer versions of mysql does not allow users to lock tables when using myisam tables.
This is getting weirder with every response ...
Do you happen to know which MySQL version you're on? If you have phpMyAdmin installed, it should quote the version number right on its front page. Otherwise, have a look at one of your backup files, they should have it in their header.
Here's a few quotes from the MySQL manual (
here[*5] ):
If you are going to run many operations on a set of MyISAM tables, it's much faster to lock the tables you are going to use. Locking MyISAM tables speeds up inserting, updating, or deleting on them.
If you are using a storage engine in MySQL that doesn't support transactions, you must use LOCK TABLES if you want to ensure that no other thread comes between a SELECT and an UPDATE.
(Where "storage engine" refers to the different tables types that MySQL supports - MyISAM tables don't support transactions.)
So, in other words, MySQL AB (the makers of MySQL)
recommend using table locks on MyISAM tables, at least for certain operations.
I could not find any mention of table locks on MyISAM tables not being supported on later versions of MySQL, but I'm willing to dig through the release notes (hence the question which version you're on).
bye, Dirk
Comment.php ERROR!!
Posted on: 01/22/05 07:44am
By: doopisdotnet
i got erros with psek.com
they told me they cannot edit any shareds files and had me upgrade to a dedicated server
can i go back to an older version of geeklog?
Comment.php ERROR!!
Posted on: 01/22/05 08:19am
By: Anonymous (Driadan)
I've looked at phpmyadmin, and it says that the version is
MySQL 4.0.16
Don't know if this helps
Driadan
Comment.php ERROR!!
Posted on: 01/22/05 08:42am
By: Dirk
[QUOTE BY= Driadan] MySQL 4.0.16[/QUOTE]
FWIW, I'm running MySQL 4.1.8 locally and it still allows me to lock MyISAM tables. So much for that excuse ...
bye, Dirk
Comment.php ERROR!!
Posted on: 01/22/05 09:38am
By: vinny
[QUOTE BY= doopisdotnet] i got erros with psek.com
they told me they cannot edit any shareds files and had me upgrade to a dedicated server
can i go back to an older version of geeklog?[/QUOTE]
Wow, the only change they need to make to fix this problem is to give you LOCK TABLE prvileges on your database. There are no "shared files" to edit. I'm not sure what their talking about, but the fact that they had you upgrade to a dedicated server (for only this problem!?!) makes me think they were more interested in getting your money...
As to using an older version of Geeklog, backing out database changes isn't easy but it is doable. I don't think 1.3.10 or 1.3.11 made any changes that would be especially difficult to back out, but it won't be simple.
-Vinny
Comment.php ERROR!!
Posted on: 01/22/05 09:51am
By: vinny
Assuming your hosting service is making up a lame excuse not to give you LOCK TABLES privileges there are a couple things you can do:
0. Tell your host that you want "LOCK TABLE" priveleges or you'll take your business else where, all the excuses I've seen on this thread seem very lame.
1. Remove all the "LOCK TABLE" "UNLOCK TABLE" SQL calls from comment.php. This should be okay if you have a low traffice site, but opens up the possiblity of corruption if two or more users try to save or delete comments at the same time.
2. Upgrade to InnoDB tables. There is a script in the admin directory to do this. Then change every instance of "LOCK TABLE XXX" to "START TRANSACTION" and every instance of "UNLOCK TABLE XXX" to "COMMIT". The only downside to this is that it is untested and will only work with InnoDB tables. If anyone tries this let me know how it works.
I appologize to everyone for the problems that the LOCK statements are causing. They were implemented to improve comment display performance (which was misserable).
Good Luck,
Vinny
P.S. Dirk, do you want to this to the FAQ?
Comment.php ERROR!!
Posted on: 01/22/05 09:58am
By: Dirk
[QUOTE BY= vinny] P.S. Dirk, do you want to this to the FAQ?[/QUOTE]
Sounds like a good idea given the length of this thread ...
bye, Dirk
Comment.php ERROR!!
Posted on: 01/22/05 02:10pm
By: doopisdotnet
[QUOTE BY= vinny] [QUOTE BY= doopisdotnet] i got erros with psek.com
they told me they cannot edit any shareds files and had me upgrade to a dedicated server
can i go back to an older version of geeklog?[/QUOTE]
Wow, the only change they need to make to fix this problem is to give you LOCK TABLE prvileges on your database. There are no "shared files" to edit. I'm not sure what their talking about, but the fact that they had you upgrade to a dedicated server (for only this problem!?!) makes me think they were more interested in getting your money...
As to using an older version of Geeklog, backing out database changes isn't easy but it is doable. I don't think 1.3.10 or 1.3.11 made any changes that would be especially difficult to back out, but it won't be simple.
-Vinny[/QUOTE]
i was on 1.3.8 then straight to 1.3.11
Comment.php ERROR!!
Posted on: 01/22/05 07:59pm
By: Anonymous (Driadan)
[QUOTE BY= vinny]
2. Upgrade to InnoDB tables. There is a script in the admin directory to do this. Then change every instance of "LOCK TABLE XXX" to "START TRANSACTION" and every instance of "UNLOCK TABLE XXX" to "COMMIT". The only downside to this is that it is untested and will only work with InnoDB tables. If anyone tries this let me know how it works.
[/QUOTE]
Well, that's another option they told me to do, but I didn't know how to do it, or if it would have any possibilities to work. I'll try to do it tomorrow.
Also, as soon as I make the changes, would you like a copy of the modified files?
regards,
Driadan
Comment.php ERROR!!
Posted on: 01/22/05 09:17pm
By: vinny
[QUOTE BY= Driadan]
Also, as soon as I make the changes, would you like a copy of the modified files? [/QUOTE]
No thanks, the changes are pretty simple (only four lines I think) and won't be incorporated into Geeklog (see Dirk's previous comments for why). Please do let me know if it works though.
Thanks,
Vinny
Comment.php ERROR!!
Posted on: 01/29/05 05:57pm
By: Anonymous (Chris)
I'm having same problem it would appear. I started another thread but think this section might be better place to communicate.
I am using MySQL 4.0.16
My tables are set as InnoDB
As for changing the instances of "Lock Table xxx" which file would I find these statements in? I have just started publishing geeklog and I'm in a testing mode and would be more than happy to see if it works.
Comment.php ERROR!!
Posted on: 01/29/05 06:04pm
By: vinny
The changes will need to be in comment.php. There should be two pairs of LOCK/UNLOCK queries you'll have to change. Please, let me know how the fix works for you.
-Vinny
Comment.php ERROR!!
Posted on: 01/29/05 06:08pm
By: Anonymous (Chris)
Nevermind, I realized it was in the comment.php file.
So I made the changes, to the code using InnoDB tables and the Start Transaction and Commit statements instead. Here is the error I get.
01/29/2005 06:03:46 PM - 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'gl_comments WRITE' at line 1. SQL in question: START TRANSACTION gl_comments WRITE
I'm Using MySQL 4.0.16
Comment.php ERROR!!
Posted on: 01/29/05 08:56pm
By: vinny
[QUOTE BY= Chris]
01/29/2005 06:03:46 PM - 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'gl_comments WRITE' at line 1. SQL in question: START TRANSACTION gl_comments WRITE
[/QUOTE]
You want to replace "LOCK TABLE gl_comments WRITE" with "START TRANSACTION" (no "gl_comments WRITE" with the START TRANSACTION query).
-Vinny
Comment.php ERROR!!
Posted on: 01/30/05 12:38am
By: Anonymous (It works!)
So for anyone looking for an alternative to locking, here it is.
Old Code
DB_query("LOCK TABLES {$_TABLES['comments']} WRITE");
DB_query('UNLOCK TABLES');
New Code
DB_query("START TRANSACTION");
DB_query('COMMIT');
With InnoDb tables in MySQL 4.0.16
It works, the only area I will have to wait to see is how it responds to a significant load of visitors.
Thanks for the help.
Comment.php ERROR!!
Posted on: 01/30/05 12:42am
By: ChrisHood
Figure I'd at least get an account so I can help more. Thanks again for the great work and support.
Comment.php ERROR!!
Posted on: 02/06/05 09:49pm
By: Anonymous (Astrogen)
So I just switched hosting providers, and ran into the same Problem.
Little bit of explanation that others could not do here.
the global flag is a global lock FLAG, that is required in MySQL 4, it doesn't mean a user can LOCK all databases though, only databases where they have both a SELECT privilege and a global LOCK privilege.
I am with shieldhost.com though, and they are running MySQL 3.23 which is more the problem here. If there was a patch, or an option, or a requirement for 4.0 we could find an alternative to these Locks.
In both MySQL 4, and MySQL 3.23 however, the LOCK is implemented by writing to the mysql database. This is a problem for hosting providers that only grant access to to your database for instance foo.*
Logically having write access to the mysql database is undesired by many hosting providers. For now I will comment out the LOCK code.
Perhaps an option in config.php to exclude the lock code?
Comments appreciated.
Comment.php ERROR!!
Posted on: 02/06/05 09:52pm
By: Anonymous (Astrogen)
I should add that granting all privileges to mysql.* fixes this problem (if you have full control of your mysql install (which I do not)).
-Astrogen
Comment.php ERROR!!
Posted on: 03/03/05 11:04am
By: Anonymous (dan Mc)
ipowerweb frustration.
Would it be possible have a forum title that lists host specific problems - and their possible fixes?
For instance, ipowerweb:
can't use mysqldump for backups, must use the phpmyadmin.
can't post comments, restrictive mysql permissions settings.
and I'm sure I'll find more, as I just was switched to vdeck server and previous cpanel server worked fine. They do not have any documentation on these mysql permissions changes. They are not capable of answering direct questions. My tech support questions/answers are sickening.
I would not have chose them for hosting had I known the problems I would have to discover trying to use geeklog!
dan Mc
Comment.php ERROR!!
Posted on: 03/05/05 10:32pm
By: vinny
It might be easier to collect information like that in the
Geeklog Wiki[*6] then in the forum.
-Vinny