Problems with Vanilla migration to new server on two tables

Andy K · November 2007

Hi all: I was migrating two DBs from MySQL 5 to a new server using 4. I'm using phpMyAdmin to backup, and CLI to restore.

One DB I was able to migrate in one chunk, and it was a dream.

The other, the BIG one, I'm having problems with.

I broke the DB restore into chunks to isolate the tables with problems. Those tables are LUM_CssMedia and LUM_FilesToPack

Here's what I get:

xxx@cl02:~/domains/xxx.com/html$ mysql -u db29139_sg -p db29139_sg -h internal-db.xxxx.xxxx.com < sg5.sql
Enter password:
ERROR 1170 (42000) at line 22: BLOB/TEXT column 'FilePath' used in key specification without a key length

xxx@cl02:~/domains/xxx.com/html$ mysql -u db29139_sg -p db29139_sg -h internal-db.xxxx.xxxx.com < sg6.sql
Enter password:
ERROR 1170 (42000) at line 22: BLOB/TEXT column 'MediaName' used in key specification without a key length

I'm not sure what that means.

Here's the MySQL output from those VERY small tables:

LUM_CssMedia
==================

-- phpMyAdmin SQL Dump
-- version 2.11.0
-- http://www.phpmyadmin.net
--
-- Host: mysql.story-games.com
-- Generation Time: Nov 01, 2007 at 07:36 AM
-- Server version: 5.0.24
-- PHP Version: 4.4.7

SET FOREIGN_KEY_CHECKS=0;

--
-- Database: `storygames`
--

-- --------------------------------------------------------

--
-- Table structure for table `LUM_CssMedia`
--

CREATE TABLE IF NOT EXISTS `LUM_CssMedia` (
`MediaID` int(11) NOT NULL auto_increment,
`MediaName` varchar(256) NOT NULL,
PRIMARY KEY (`MediaID`),
UNIQUE KEY `MediaName` (`MediaName`)
) TYPE=MyISAM AUTO_INCREMENT=4 ;

--
-- Dumping data for table `LUM_CssMedia`
--

INSERT INTO `LUM_CssMedia` (`MediaID`, `MediaName`) VALUES
(1, 'screen'),
(2, 'print'),
(3, '');

SET FOREIGN_KEY_CHECKS=1;

Andy K · November 2007

Here's the output from LUM_FilesToPack
===================================

-- phpMyAdmin SQL Dump
-- version 2.11.0
-- http://www.phpmyadmin.net
--
-- Host: mysql.story-games.com
-- Generation Time: Nov 01, 2007 at 07:35 AM
-- Server version: 5.0.24
-- PHP Version: 4.4.7

SET FOREIGN_KEY_CHECKS=0;

--
-- Database: `storygames`
--

-- --------------------------------------------------------

--
-- Table structure for table `LUM_FilesToPack`
--

CREATE TABLE IF NOT EXISTS `LUM_FilesToPack` (
`FileID` int(11) NOT NULL auto_increment,
`FilePath` varchar(1000) NOT NULL,
PRIMARY KEY (`FileID`),
UNIQUE KEY `FilePath` (`FilePath`)
) TYPE=MyISAM AUTO_INCREMENT=31 ;

--
-- Dumping data for table `LUM_FilesToPack`
--

INSERT INTO `LUM_FilesToPack` (`FileID`, `FilePath`) VALUES
(1, 'themes/vanilla/styles/default/vanilla.css'),
(2, 'themes/vanilla/styles/default/vanilla.print.css'),
(3, 'extensions/Quotations/quote.css'),
(4, 'extensions/PreviewPost/preview.css'),
(5, 'http://www.story-games.com/forums/extensions/BBInsertBar/style.css'),
(6, 'extensions/CategoryJumper/style.css'),
(7, 'js/global.js'),
(8, 'js/vanilla.js'),
(9, 'js/ajax.js'),
(10, 'js/ac.js'),
(11, 'extensions/Quotations/quote.js'),
(12, 'extensions/Style/functions.js'),
(13, 'extensions/PreviewPost/preview.js'),
(14, 'http://www.story-games.com/forums/extensions/BBInsertBar/functions.js'),
(15, 'extensions/Notify/functions.js'),
(16, './extensions/SmoothPageJump/prototype.lite.js'),
(17, './extensions/SmoothPageJump/moo.fx.js'),
(18, './extensions/SmoothPageJump/functions.js'),
(19, 'extensions/GuestWelcome/style.css'),
(20, 'themes/vanilla/styles/default/people.css'),
(21, 'themes/vanilla/styles/jazzman/vanilla.css'),
(22, 'themes/vanilla/styles/jazzman/vanilla.print.css'),
(23, 'themes/vanilla/styles/soulscape/vanilla.css'),
(24, 'themes/vanilla/styles/soulscape/vanilla.print.css'),
(25, 'extensions/Flickrizer/style.css'),
(26, 'extensions/Notify/style.css'),
(27, 'extensions/Style/style.css'),
(28, 'extensions/WindowLinks/functions.js'),
(29, 'themes/vanilla/styles/blackandwhite/vanilla.css'),
(30, 'themes/vanilla/styles/blackandwhite/vanilla.print.css');

SET FOREIGN_KEY_CHECKS=1;

Can anyone tell me how I can restore these tables successfully?

I looked on my other install, and it appears that LUM_CssMedia isn't even present. That might be related to some extension?

Andy K · November 2007

(deleted a followup question)

Dinoboff · November 2007

varchar(1000) is wrong I think. You can't have more than 255 characters.

Andy K · November 2007

UPDATE: I tested anyway, recreating the DB without using those two tables, and everything seems to work OK. Does anyone know what these tables are related to? I looked on my other working Vanilla install (which has different extensions) and they are not on that DB. Hmmm.

-Andy

Dinoboff · November 2007

The packer extension. If you are still using it, just remove $Configuration['PACKER_VERSION'] from conf/settings.php

Andy K · November 2007

Ah, ok. I've long since removed that.

Thanks for your help! I killed those tables and was able to restore fine.

Problems with Vanilla migration to new server on two tables

Comments