By obisun on

Hi,

Can anyone help me out with this issue. I've been working on a site and suddenly I began to see this message on the site pages. What could it mean? And how can I resolve it?

Warning: Table 'watchdog' was not locked with LOCK TABLES query: INSERT INTO watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', 'Got a packet bigger than 'max_allowed_packet' bytes\nquery: UPDATE cache_views SET data = 'a:4:{s:6:\\"tables\\";a:203:{s:17:\\"nodefamily_parent\\";a:3:{s:4:\\"name\\";s:10:\\"nodefamily\\";s:4:\\"join\\";a:3:{s:4:\\"left\\";a:2:{s:5:\\"table\\";s:4:\\"node\\";s:5:\\"field\\";s:3:\\"nid\\";}s:5:\\"right\\";a:1:{s:5:\\"field\\";s:10:\\"parent_nid\\";}s:4:\\"type\\";s:5:\\"inner\\";}s: in /home/masked/public_html/includes/database.mysql.inc on line 172

Thanks.

Comments

jazzitup’s picture

jazzitup commented

Try to make a repair action on every single table in your Drupal database. I hope that will help!

--
www.jazzit.hr

--
www.jazzit.hr

obisun’s picture

obisun commented

Hi,

I eventually got to resolve this issue, but not before trying so many other avenues that failed.

First I thought it was the module in question, so I tried to disable the modules, but that didnt work. Then I tried to delete the watchdog table in the database and then re-install it, but that made matters worse. So I made a search and discovered that the issue had to do with the max_allowed_packet setting for the mysql on my server. So I sent a mail to the Tech Support of my web host as follows;

    Can I Increase memory in the php.ini file? And where is it located? How will that affect my server performance. I'm running a drupal based site on my server and I've been receiving the following error message after updating one of the modules and was adviced to increase the max_allowed_packet setting for the mysql. Kindly advice.

And then they replied as follows;

    There is no max_allowed_packet setting in php.ini file, nor in my.cnf file. The max_allowed_packet file should be located in my.cnf (mySQL configuration file) rather then php.ini. I've set the value to 32M, let us know if this suits your needs.

And that just did it. I hope this helps some one else.

Cheers.

I delight in giving Light!

okellhammer’s picture

okellhammer commented

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:

Unfortunately newer versions of MySQL does not allow you to lock MyISAM tables. If you need transaction support kindly use the InnoDB table type which is transaction safe.

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?

TheFazz’s picture

TheFazz commented

i am having a similar problem.

i checked my error log file from my host, and found this error:

[Tue Mar 25 23:03:09 2008] [error] PHP Warning:  Table 'main_watchdog' was not locked with LOCK TABLES\nquery: INSERT INTO main_watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (foo, ;main_sessions' was not locked with LOCK TABLES\\nquery: SELECT sid FROM main_sessions WHERE sid = foo; in foo;/home/public_html/drupal5/includes/database.mysql.inc&foo foo) in /home/public_html/drupal5/includes/database.mysql.inc on line 172

[Tue Mar 25 23:03:09 2008] [error] PHP Fatal error:  Allowed memory size of 20971520 bytes exhausted (tried to allocate 320405 bytes) in /home/public_html/drupal5/includes/database.mysql.inc on line 380

from what i can see, I have a memory limit issue (which results in the white screen of death) as well as a LOCK TABLE issue.

I am using drupal 5.7.
PHP version 5.2.5
MySQL version 5.0.45-community
Apache version 1.3.41 (Unix)

i do not have access to the host's configuration so I cannot change the 20MB limit.

I removed some of the unused modules. i did a phpadmin table repair, and i setup a site specific (under "sites/my.site.org/modules" module directory using only the core modules - and still does not work. i need help urgently.

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

Servage is lying to you because they don't want to upgrade. My host is on MySql 5.something and I don't see this problem even in 16M. And I do get "LOCK TABLES".

NancyDru

jazzitup’s picture

jazzitup commented

I could only say that in my case, my hosting provider told me that they had some serious problems with a hard disk (a hardware problem!) on the server where my MySQL database was located. I had quite the same problems and I was going crazy with my client. As soon as they moved my database to another server, everything was just great! I hope my story will help someone to solve the same issue.

--
www.jazzit.hr

--
www.jazzit.hr

firfin’s picture

firfin commented

Thank you obisun, you have shown me the light :-)

/etc/mysql/my.cnf
max_allowed_packet = 16M
changed it to 32M , no more problem.

Encarte’s picture

Encarte commented

+1

Encarte’s picture

Encarte commented

In my case, this problem seems to be a result of another problem (that only becomes clear after increasing the php memory max). The original problem seems to be this one: http://drupal.org/node/121390, causing a general failure, multiple subsequent errors and making almost impossible to understand where it started.

reed.richards’s picture

reed.richards commented

Things like this started happening when installing the views module on my system. First it was a sql gone away error which I solved by increasing the allowed packet size and then the lock tables error occurred which is a strange error since I run a local server where the user of the database has all rights i.e. the root user. Anyways what I did was set the memory limit to 256M instead of 128M and then restart the server. Voila it worked.

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

I solved this problem by going from 24M to 64M. No more errors.

NancyDru

axel’s picture

axel commented

Seems like a table crashed. Use REPAIR TABLE tablename USE_FRM from mysql client.

--
Axel,
JabberID: laika@jabber.org
Drupal in Russia | Darcs repo of my projects

Alan Oliveira’s picture

Alan Oliveira commented

Warning: Table 'watchdog' was not locked with LOCK TABLES query: INSERT INTO watchdog (uid, type, message, variables, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', '%message in %file on line %line.', 'a:4:{s:6:\"%error\";s:12:\"user warning\";s:8:\"%message\";s:293:\"Unknown column 'email' in 'field list'\nquery: INSERT INTO webform_component (nid, cid, pid, form_key, name, type, value, extra, mandatory, weight, email) VALUES (152, 1, 0, 'obs', 'obs', 'textarea', '', 'a:0:{}', 0, -10, 0)\";s:5:\"%file\";s:64:\"C:\\xampp\\htdocs\\drupaltek\\modules\\webform\\webform_components.inc\";s:5:\"%line\";i:478;}', 3, '', 'http://localhost/drupaltek/?q=node/152/edit/components/new/textarea&name... in ...\includes\database.mysqli.inc on line 128

this happen when i try to add a field using WebForm module

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented

In my case this was caused by being out of memory. When I increased it, this problem went away.

NancyDru

WeRockYourWeb.com’s picture

WeRockYourWeb.com commented

I'm getting the same exact error when trying to add/ submit a webform component on a Drupal 5.x site. Increase the memory, which allowed us to repair the table, but the LOCK TABLES Warning still shows up. Will troubleshoot further and post back.

WeRockYourWeb.com’s picture

WeRockYourWeb.com commented

Completely uninstalling webform and re-installing it fixed the issue. I also increased the name varchar for the variable table from 128 to 255, but that was earlier and didn't seem to affect anything.