Welcome to Geeklog Monday, November 20 2017 @ 09:38 pm EST


Status: offline

Dirk

Site Admin
Admin
Registered: 12/01/2002
Posts: 13073
Location:Stuttgart, Germany
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):
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

Status: offline

doopisdotnet

Forum User
Chatty
Registered: 31/01/2003
Posts: 38
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?

Driadan

Anonymous
I've looked at phpmyadmin, and it says that the version is

MySQL 4.0.16

Don't know if this helps

Driadan

Status: offline

Dirk

Site Admin
Admin
Registered: 12/01/2002
Posts: 13073
Location:Stuttgart, Germany
Quote by Driadan: MySQL 4.0.16

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

Status: offline

vinny

Site Admin
Admin
Registered: 24/06/2002
Posts: 352
Location:Colorado, USA
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?


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

Status: offline

vinny

Site Admin
Admin
Registered: 24/06/2002
Posts: 352
Location:Colorado, USA
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?

Status: offline

Dirk

Site Admin
Admin
Registered: 12/01/2002
Posts: 13073
Location:Stuttgart, Germany
Quote by vinny: P.S. Dirk, do you want to this to the FAQ?

Sounds like a good idea given the length of this thread ...

bye, Dirk

Status: offline

doopisdotnet

Forum User
Chatty
Registered: 31/01/2003
Posts: 38
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?


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


i was on 1.3.8 then straight to 1.3.11

Driadan

Anonymous
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.


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

Status: offline

vinny

Site Admin
Admin
Registered: 24/06/2002
Posts: 352
Location:Colorado, USA
Quote by Driadan:
Also, as soon as I make the changes, would you like a copy of the modified files?


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

Chris

Anonymous
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.


Status: offline

vinny

Site Admin
Admin
Registered: 24/06/2002
Posts: 352
Location:Colorado, USA
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

Chris

Anonymous
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

Status: offline

vinny

Site Admin
Admin
Registered: 24/06/2002
Posts: 352
Location:Colorado, USA
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


You want to replace "LOCK TABLE gl_comments WRITE" with "START TRANSACTION" (no "gl_comments WRITE" with the START TRANSACTION query).

-Vinny

It works!

Anonymous
So for anyone looking for an alternative to locking, here it is.

Old Code
PHP Formatted Code

        DB_query("LOCK TABLES {$_TABLES['comments']} WRITE");
        DB_query('UNLOCK TABLES');

 


New Code
PHP Formatted 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.

Status: offline

ChrisHood

Forum User
Newbie
Registered: 30/01/2005
Posts: 2
Location:Virginia
Figure I'd at least get an account so I can help more. Thanks again for the great work and support.
- Chris

Astrogen

Anonymous
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.

Astrogen

Anonymous
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

dan Mc

Anonymous
grumpy
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

Status: offline

vinny

Site Admin
Admin
Registered: 24/06/2002
Posts: 352
Location:Colorado, USA
It might be easier to collect information like that in the Geeklog Wiki then in the forum.

-Vinny

All times are EST. The time is now 09:38 pm.

  • Normal Topic
  • Sticky Topic
  • Locked Topic
  • New Post
  • Sticky Topic W/ New Post
  • Locked Topic W/ New Post
  •  View Anonymous Posts
  •  Able to post
  •  Filtered HTML Allowed
  •  Censored Content