List:General Discussion« Previous MessageNext Message »
From:Alex Davies Date:December 24 2005 4:00pm
Subject:NOT NULL and default: using mysqldump to upgrade from 4.0 to 5.0
View as plain text  
Hi,

I have two servers and am trying to move the database from one to the other.

The data is currently residing in a MySQL 4.0 database. The new server is
running MySQL 5.0.

Trying to import a table such as

CREATE DATABASE /*!32312 IF NOT EXISTS*/ db17058c;
USE db17058c;
DROP TABLE IF EXISTS adidas;
CREATE TABLE adidas (
  manufacturer char(1) NOT NULL default '',
  brand char(1) NOT NULL default '',
  product varchar(100) NOT NULL default '',
  short text NOT NULL,
  long text NOT NULL,
  id int(11) NOT NULL default '0',
  image varchar(124) NOT NULL default '',
  link mediumtext NOT NULL,
  buylink varchar(124) NOT NULL default '',
  track varchar(124) NOT NULL default '',
and so on...

Throws all sorts of errors, for example

ERROR 1064 (42000): 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 'long text NOT NULL,
  id int(11) NOT NULL default '0',
  image varchar(124) NOT ' at line 6

If I remove this line then another line a few lines along hits a problem.

This appears to be some sort of conflict between NOT NULL and default
values; I would be very grateful if someone could confirm what the issue is
and suggest a way of solving it (the dump is 500mb, so any manual fix is not
a lot of use!)

With many thanks,

Alex Davies

Thread
NOT NULL and default: using mysqldump to upgrade from 4.0 to 5.0Alex Davies24 Dec
  • Re: NOT NULL and default: using mysqldump to upgrade from 4.0 to 5.0Gleb Paharenko24 Dec