I get a lot of database errors while tempting to update to the latest drupal 5 version. apparently (i do not know anything about MySQL) there are 2 colums that cannot be created and cause the website to report a stream of php errors in the watchdog.

when I ran the data base upgrade script I got the following errors

Warning: MySQL server has gone away query: INSERT INTO bns_watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', 'Lost connection to MySQL server during query\nquery: ALTER TABLE bns_weblinks ADD INDEX url (url (2048)) in /.../.../..../bodynsoulphotography.com/includes/database.mysql.inc on line 172.', 2, '', 'http://bodynsoulphotography.com/update.php?op=do_update', 'http://bodynsoulphotography.com/update.php?op=selection', '190.98.9.15', 1221934254) in /.../.../.../bodynsoulphotography.com/includes/database.mysql.inc on line 172

Warning: MySQL server has gone away query: INSERT INTO bns_watchdog (uid, type, message, severity, link, location, referer, hostname, timestamp) VALUES (1, 'php', 'MySQL server has gone away\nquery: UPDATE bns_system SET schema_version = 5102 WHERE name = 'weblinks' in /.../..../.../bodynsoulphotography.com/includes/database.mysql.inc on line 172.', 2, '', 'http://bodynsoulphotography.com/update.php?op=do_update', 'http://bodynsoulphotography.com/update.php?op=selection', '190.98.9.15', 1221934254) in /..../.../.../bodynsoulphotography.com/includes/database.mysql.inc on line 172

etc etc etc

Comments

nancydru’s picture

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

Those errors have nothing to do with WebLinks - that's a core problem. I would be more interested in the underlying

there are 2 colums that cannot be created

What columns are they and why did it say they couldn't be created? Can you give me more information, please?

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented
Status: Active » Postponed (maintainer needs more info)
nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented
Status: Postponed (maintainer needs more info) » Closed (fixed)

No further information. If you are ready to help us solve the problem, re-open the issue.

Cybergarou’s picture

Cybergarou commented

I recently encountered this problem myself. I managed to track it down to a SQL statement in the 5102 update. Some versions of MySQL had a bug that caused the server to crash when a statement contained both ALTER TABLE and ADD INDEX. This leads to the "server has gone away" messages.

The solution is to update MySQL to version 5.0.33 or higher.

nancydru’s picture

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

Hmm. I'm on 5.0.27 and didn't have the problem. How else would you add an index without an ALTER TABLE?

Cybergarou’s picture

Cybergarou commented

I guess there must have been some specific aspect of my configuration that contributed to the problem, such as Windows vs. Linux or MyISAM vs. InnoDB. I was using 5.0.27 as well. MySQL crashed when I ran the update and when I entered the query manually.

The only thing that changed between when the update crashed the server and when it worked was the version of MySQL. About a dozen ALTER TABLE bugs were fixed so any one of those could have been the real issue.

Chalk it up to unusual conditions that most people won't meet.

nancydru’s picture

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

Well, I'm on Windows and use MyIsam under 5.0.27.

pjek’s picture

pjek commented

Sorry for not giving feedback, I have been taken away from my website for a while. But the comment #4. Closely assembles my problem. My MySQL server would go away. I will check with my webhost if they have upgraded MySQL since then. Will post again if problem remains.

Cybergarou’s picture

Cybergarou commented

I've been using Windows with mixed table types. The weblinks table was using InnoDB.

pjek’s picture

pjek commented

Just updated my website to Drupal Core 5.12 and tried Weblinks 2.1 again, same problem. But then indeed my host apperas to be using MySQL 5.0.16. So I guess that explains the problem, I'll have to wait for the host to upgrade in order to update.

Oh yes I am on Dreamhost Private Server

nancydru’s picture

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

If someone can give me some idea on how to rewrite the queries, I will be happy to.

Cybergarou’s picture

Cybergarou commented

I'm still not sure that the problem is caused by the database engine, but it seems to be the most likely cause to me at the moment. If it is, the InnoDB engine is causing the problem. Converting the weblinks table to MyIsam before running the queries in the 5102 and 5106 updates should get around the issue. After the updates are complete, the table could be converted back into InnoDB.

Just an example query.
ALTER TABLE weblinks ENGINE = MyIsam

This of course depends on the MyIsam engine being available on the server.

pjek’s picture

pjek commented

Should I as the end user change my database format and then apply the update and then return to the previous format?

Can I use phpMyAdmin for that? Do i enter the query you mentioned in the SQL tab and run it? And to return to normal do I use then InnoDb instead of MyIsam in a similar query?

I am not a programmer, but find it interesting to learn, just want to make sure I do not do dumb things

nancydru’s picture

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

This is something I am unqualified to answer.

Cybergarou’s picture

Cybergarou commented

Changing the database format, running the update, and then changing the format back should work.

I don't use phpMyAdmin, but anything that lets you run queries should work with the example query I gave. If you are using a table prefix, be sure to add that to the query. When you switch back to the original format, replace MyIsam with InnoDB.

Just be sure to backup the database before you do this, there is a very slight chance of corrupting your data when converting the table. Let us know if it works.

pjek’s picture

pjek commented

will prepare the backup for next weekend

rmiddle’s picture

rmiddle commented
Status: Closed (fixed) » Active
Bilalx’s picture

Bilalx commented
Version: 5.x-2.0 » 6.x-1.1
Priority: Critical » Normal

Hi,

I had the same problem when trying to add a weblink in the 6.x-1.1 version

Warning: MySQL server has gone away query: INSERT INTO Drupal_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:147:\"Lost connection to MySQL server during query\nquery: INSERT INTO Drupal_weblinks (vid, nid, url, weight) VALUES (41, 103, 'http://hhh', 0)\";s:5:\"%file\";s:91:\"[removed]\\htdocs\\drupal\\sites\\all\\modules\\weblinks\\weblinks.module\";s:5:\"%line\";i:289;}', 3, '', 'http://[removed]/node/add/weblinks', 'http://[removed]/node/add/weblinks', '127.0.0.1', 1227468672) in [removed]\htdocs\drupal\includes\database.mysqli.inc on line 128

I'm using MySql 5.0.37

I applied Cybergarou suggestion, I switched to MyIsam and everything worked fine when adding a link.

I switched back to InnoDB, and i was able to successfully add new links.

Thanks Cybergarou and everyone else :)

I found this if it can help someone: http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

nancydru’s picture

nancydru
she/her/hers
Primary language English
Location Boston
commented
Status: Active » Fixed

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for two weeks with no activity.