5.7 > 6.0 SQL “Unknown Column” Prob

By jeffsensat on 16 Feb 2008 at 15:55 UTC

Followed all upgrade rules including de-installing pre-6 status update module, turned off ALL modules except core, reverted to Garland theme, took site offline, ran update.php on Drupal 5.7 database, exported 5.7 database then imported it back into a fresh mysql database for v 6.0, altered Drupal 6 .htaccess and settings.php files appropriately, uploaded Drupal 6 via FTP, access new Drupal installer, started 6.0 install process, got to step where you define Drupal 6 database including database name with user ID and password ... then get this:

——————————

Warning: Unknown column 'serialized' in 'field list' query: SELECT data, created, headers, expire, serialized FROM cache WHERE cid = 'variables' in [drupal 6 "/includes/database.mysqli.inc" file] on line 128

Warning: Unknown column 'serialized' in 'field list' query: UPDATE cache SET data = 'a:405:{s:15:\"user_import_max\";i:250;s:13:\"filter_html_1\";i:1;s:18:\"node_options_forum\";a:1:{i:0;s:6:\"status\";}s:15:\"install_profile\";s:7:\"default\";s:17:\"node_options_page\";a:1:{i:0;s:6:\"status\";}s:12:\"comment_page\";i:0;s:14:\"theme_settings\";a:24:{s:11:\"toggle_logo\";i:1;s:11:\"toggle_name\";i:1;s:13:\"toggle_slogan\";i:1;s:14:\"toggle_mission\";i:1;s:24:\"toggle_node_user_picture\";i:1;s:27:\"toggle_comment_user_picture\";i:1;s:13:\"toggle_search\";i:1;s:14:\"toggle_favicon\";i:1;s:21:\"toggle_node_info_blog\";i:1;s:22:\"toggle_node_info_event\";i:1;s:22:\"toggle_node_info_forum\";i:1;s:21:\"toggle_node_info_page\";i:0;s:21:\"toggle_node_info_poll\";i:1;s:24:\&quo in [drupal 6 "/includes/database.mysqli.inc" file] on line 128

Warning: Unknown column 'serialized' in 'field list' query: SELECT data, created, headers, expire, serialized FROM cache_page WHERE cid = 'http://slightlyoffcenterplayers.com/d6/' in [drupal 6 "/includes/database.mysqli.inc" file] on line 128

——————————

Without starting over from scratch (chucking my original 5.7 database and re-entering EVERYTHING again into 6.0), are there possible solutions? Looks like pre-release Drupal 6.0 candidates also had this issue. Very disappointed, but still hopeful ...

Thanks to all developers for the Drupal labor of love.

Comments

it looks like

rernst commented 16 February 2008 at 16:15

all of the problems are with the cache tables... perhaps they weren't updated somehow. Have you searched the bug reports for the drupal 6 upgrade process for similar problems?

What you could do for now is make a fresh install of d6 elsewhere, and copy the cache table definitions to your existing database, to see if that stops the errors

Cache Tables

jeffsensat commented 16 February 2008 at 16:53

Thanks for the help, Ryan.

Fresh d6 install: can do. Can you be more specific about procedure with copying cache table defs over? Do you mean ... create a NEW and FRESH blank d6 mySQL database, install a separate d6 using that dbase, then copy all “CACHE” table entries from the NEWEST d6 mySQL database into my 5.7>6.0 converted PROBLEM SQL database?

(I’m an SQL noob, so I’m kinda flying by the seat of my pants here.)

yes

rernst commented 16 February 2008 at 17:41

that's exactly what I mean. Sorry if I wasn't clear. You should probably first blow away all of the existing cache tables.

of course, make a backup before you do so ;)

Same error

a_calder commented 3 September 2008 at 04:59

Just wondering if the cache suggestion worked. I just upgraded from 5.1 to 6.4 and I am getting the exact same error. Specifically, after I installed the Views module:

# user warning: Unknown column 'serialized' in 'field list' query: SELECT data, created, headers, expire, serialized FROM cache_views WHERE cid = 'views_default_views:en' in /home-dir/public_html/includes/cache.inc on line 26. # user warning: Unknown column 'serialized' in 'field list' query: UPDATE cache_views SET data = 'a:5:{s:15:\"comments_recent\";O:4:\"view\":21:{s:8:\"db_table\";s:10:\"views_view\";s:10:\"base_table\";s:8:\"comments\";s:5:\"built\";b:0;s:8:\"executed\";b:0;s:4:\"args\";a:0:{}s:10:\"build_info\";a:0:{}s:8:\"use_ajax\";b:0;s:5:\"pager\";a:5:{s:9:\"use_pager\";b:0;s:14:\"items_per_page\";i:10;s:7:\"element\";i:0;s:6:\"offset\";i:0;s:12:\"current_page\";i:0;}s:17:\"attachment_before\";s:0:\"\";s:16:\"attachment_after\";s:0:\"\";s:12:\"exposed_data\";a:0:{}s:13:\"exposed_input\";a:0:{}s:3:\"vid\";s:0:\"\";s:4:\"name\";s:15:\"comments_recent\";s:11:\"description\";s:162:\"Contains a block and a page to list recent comments; in /home-dir/public_html/includes/cache.inc on line 109.

Did that work?

misterlawrence commented 29 August 2008 at 23:54

Did the cache suggestion work?

yes

najibx commented 22 September 2008 at 07:52

There's a problem when upgrading to D6, this table was not upgraded. Delete the old table and do SQl query below :


CREATE TABLE IF NOT EXISTS `cache_location` (
  `cid` varchar(255) NOT NULL default '',
  `data` longblob,
  `expire` int(11) NOT NULL default '0',
  `created` int(11) NOT NULL default '0',
  `headers` text,
  `serialized` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`cid`),
  KEY `expire` (`expire`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Worked for me - I.e. Most

pingers commented 28 November 2008 at 06:16

Worked for me - I.e. Most cache tables have the same definition, so you can just export the definition and create the correct table name from that definition.

Cheers!

Which table?

bob.salsburg commented 18 December 2008 at 21:44

I love how this stuff just rolls off your tongue... or off the keyboard.

Upgraded fron 5.14 to 6.8 and attempted to login to database. My error collection is below. From all of that, can someone tell me what needs to be deleted in all this goop? And then, I suppose you run the query? Please be specific. I need to get this site back up.

=============================================

Warning: Unknown column 'serialized' in 'field list' query: SELECT data, created, headers, expire, serialized FROM cache WHERE cid = 'variables' in /home/christch/public_html/includes/database.mysqli.inc on line 128

Warning: Unknown column 'serialized' in 'field list' query: UPDATE cache SET data = 'a:172:{s:13:\"filter_html_1\";i:1;s:18:\"node_options_forum\";a:1:{i:0;s:6:\"status\";}s:17:\"menu_primary_menu\";i:2;s:19:\"menu_secondary_menu\";i:2;s:15:\"install_profile\";s:7:\"default\";s:17:\"node_options_page\";a:1:{i:0;s:6:\"status\";}s:12:\"comment_page\";i:0;s:14:\"theme_settings\";a:18:{s:11:\"toggle_logo\";i:1;s:11:\"toggle_name\";i:1;s:13:\"toggle_slogan\";i:0;s:14:\"toggle_mission\";i:1;s:24:\"toggle_node_user_picture\";i:0;s:27:\"toggle_comment_user_picture\";i:0;s:13:\"toggle_search\";i:0;s:14:\"toggle_favicon\";i:1;s:21:\"toggle_node_info_page\";i:0;s:22:\"toggle_node_info_story\";i:0;s:12:\"default_logo\";i:1;s:9:\"logo_path\";s:0:\"\";s:11:\"logo_upl in /home/christch/public_html/includes/database.mysqli.inc on line 128

Warning: Cannot modify header information - headers already sent by (output started at /home/christch/public_html/includes/database.mysqli.inc:128) in /home/christch/public_html/includes/bootstrap.inc on line 610

Warning: Unknown column 'language' in 'where clause' query: SELECT src FROM url_alias WHERE dst = 'node' AND language IN('en', '') ORDER BY language DESC in /home/christch/public_html/includes/database.mysqli.inc on line 128

This worked for me ...

jeffsensat commented 30 November 2008 at 00:11

http://forum.civicrm.org/index.php/topic,5026.0.html

I used the step to delete

parrottvision commented 1 January 2009 at 17:25

I used the step from najibx to delete the block and rebuild it. Only one of the cache tables (cache_block) didn't update to include the serialized column. May have been something to do with a collision with the old block_cache module. Who knows, it's fixed now which is what matters.

CREATE TABLE IF NOT EXISTS `cache_block` (
`cid` varchar(255) NOT NULL default '',
`data` longblob,
`expire` int(11) NOT NULL default '0',
`created` int(11) NOT NULL default '0',
`headers` text,
`serialized` smallint(6) NOT NULL default '0',
PRIMARY KEY (`cid`),
KEY `expire` (`expire`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Thanks a lot for posting that

EnekoAlonso-1 commented 28 February 2009 at 07:34

Thanks a lot for posting that code here...... that saved my night :)

off-on caching

cardell commented 19 February 2009 at 07:59

I was getting the same error messages after upgrading from 5 to 6. I got the errors when I tried to go to the modules or themes admin pages. I went to Site Configuration -> Performance, and disabled everything. Then I went back to the modules and themes admin pages...no errors. After going to each of those pages, I went back to Performance and turned caching back to Normal...went back to modules and themes admin...no errors.

Just something else to try.

Subscribing, same issue.

summit commented 26 March 2009 at 22:47

Subscribing, same issue. Shouldn't this be patched somewhere to help others not having this issue anymore?
Greetings, Martijn

same issue

dugh commented 13 September 2009 at 22:56

I see the same error constantly:

Unknown column &#039;serialized&#039; in &#039;field list&#039; query: UPDATE cache_similarterms SET .....

I ran this and hopefully the error will not appear again:

ALTER TABLE `cache_similarterms`
ADD `serialized` smallint(6) NOT NULL default '0';