We help IT Professionals succeed at work.

move mysql 3.23 database to mysql 5.0

bryanlloydharris
on
328 Views
Last Modified: 2008-03-06
Hi,

I am having trouble moving a database from mysql 3.23 to version 5.0.  The old computer is linux and the new computer is Windows XP.  Can anyone help or offer advice?

old computer> mysqldump bryanh > bryanh.sql

then I use the "scp" linux command to copy the file.  Then on the new computer I have the below.

E:\temp>mysql -u root -ppassword bryanh < bryanh.sql
ERROR 1064 (42000) at line 84: You have an error in your SQL syntax; check the m
anual that corresponds to your MySQL server version for the right syntax to use
near 'decimal(6,0) NOT NULL default '0',
  payee varchar(20) NOT NULL,
  amount decima' at line 2
Comment
Watch Question

Top Expert 2006
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Kim RyanIT Consultant

Commented:
You could add this flag to the dump to make it compatible with version 3.23
mysqldump --compatible=mysql323 bryanh > bryanh.sql

Also if there are reseved words as todd suggests, you can add this flag --allow-keywords and they will get prepended with the table name. It is better to rename though if that option is available to you.
Top Expert 2006

Commented:
I thought about the --compatible flag as well, but I'm not sure that works here (going from 3.23 -> 5.0).  But it's absolutely valid the other way around.

Author

Commented:
It is trivial to recreate the tables --- I am more curious of how to get it to work correctly, and why didn't it work.

The next post will contain the creation of the tables.  It is quite long, and I did not use the Oracle statements only the MySQL statements --- I used copy/paste into mysql monitor.

Author

Commented:
MySQL
create table checks
(check          numeric(6)       not null,
 payee          varchar(20)      not null,
 amount         decimal(6,2)     not null,
 remarks        varchar(20)     not null);

MySQL
create table deposits
(deposit          numeric(8)       not null,
 whopaid          varchar(25)      not null,
 amount           decimal(6,2)     not null,
 remarks          varchar(20)      not null);

MySQL
create table bikes
(name          varchar(15)      not null,
 framesize     decimal(10,2)    not null,
 composition   varchar(12)      not null,
 milesridden   numeric(10)      not null,
 type          varchar(8)       not null);

MySQL
create table price
(item          varchar(15)     not null,
 wholesale     decimal(4,2)    not null);

MySQL
create table hilow
(state         varchar(10)     not null,
 lows          numeric(8)      not null,
 highs         numeric(8)      not null,
 difference    numeric(10)         null);

MySQL
create table remains
(numerator     numeric(10)     not null,
 denominator   numeric(12)     not null);

MySQL
create table precedence
(n1          numeric(10)      not null,
 n2          numeric(10)     not null,
 n3          numeric(10)     not null,
 n4          numeric(10)     not null);

MySQL
create table friends
(lastname      varchar(15)     not null,
 firstname     varchar(15)     not null,
 areacode      numeric(9)      null,
 phone         varchar(10)     null,
 st            char(2)         not null,
 zip           varchar(5)      not null);

MySQL
create table parts
(name          varchar(15)     not null,
 location      varchar(15)     not null,
 partnumber    numeric(10)     not null);

MySQL
create table vacation
(lastname        varchar(15)    not null,
 employeenum     numeric(11)    not null,
 years           numeric(8)     not null,
 leavetaken      numeric(11)    null);

MySQL
create table football
(name          varchar(20)     not null);

MySQL
create table softball
(name          varchar(20)     not null);

MySQL
create table teamstats
(name        varchar(10)    not null,
 pos         varchar(3)     not null,
 ab          numeric(3)     not null,
 hits        numeric(4)     not null,
 walks       varchar(5)     not null,
 singles     varchar(7)     not null,
 doubles     varchar(7)     not null,
 triples     varchar(7)     not null,
 hr          numeric(2)     not null,
 so          varchar(2)     not null);

MySQL
create table project
(task          varchar(14)  not null,
 startdate     date,
 enddate       date);

MySQL
create table numbers
(a          decimal(10,4)     not null,
 b          decimal(10,4)     not null);

MySQL
create table characters
(lastname      varchar(15)     not null,
 firstname     varchar(15)     not null,
 m             char(1)         null,
 code          numeric(10)     not null);

MySQL
create table convert
(name          varchar(15)   not null,
 testnum       numeric(9)    not null);
Oracle
create table puzzle
(name          varchar(15)     not null,
 location      varchar(14)     not null);

MySQL
create table puzzle
(name          varchar(15)     not null,
 location      varchar(14)     not null);

MySQL
create table orgchart
(name          varchar(15)     not null,
 team          varchar(11)     not null,
 salary        decimal(10,2)   not null,
 sickleave     numeric(10)     not null,
 annualleave   numeric(11)     not null);

MySQL
create table part
(partnum         numeric(10)      not null,
 description     varchar(20)      not null,
 price           decimal(10,2)    not null);

MySQL
create table customer
(name          varchar(10)     not null,
 address       varchar(10)     not null,
 state         varchar(6)      not null,
 zip           varchar(10)     not null,
 phone         varchar(10)     null,
 remarks       varchar(30)     not null);

MySQL
create table orders
(orderedon     date,
 name          varchar(10)      not null,
 partnum       numeric(10)      not null,
 quantity      numeric(10)      not null,
 remarks       varchar(30)      not null);

MySQL
create table bills
(name          varchar(25)      not null,
 amount        numeric(6)       not null,
 account_id    numeric(10)      not null);

MySQL
create table salaries
(name          varchar(30)     not null,
 salary        numeric(9)      not null,
 age           numeric(10)     not null);

MySQL
create table products
(pro            numeric(3)       not null,
 product_name   varchar(30)      not null,
 unit_cost      decimal(9,2)     not null);

MySQL
create table states
(st          char(2)          not null);

MySQL
create table ssn_table
(ssn             varchar(9)      not null);

Oracle
create table job_tbl
(name          varchar(30)     not null,
 job           varchar(15)     not null,
 department    varchar(10)     not null);

MySQL
create table job_tbl
(name          varchar(30)     not null,
 job           varchar(15)     not null,
 department    varchar(10)     not null);
Kim RyanIT Consultant

Commented:
You could try this to get a more detailed list of errord
mysql
source bryanh.sql
show errors limit 20;
show warnings limit 20;
 
CERTIFIED EXPERT

Commented:
Could you post the precise structure that contains the text indicated as close to the error:

'decimal(6,0) NOT NULL default '0',
  payee varchar(20) NOT NULL,
  amount decima'

Author

Commented:
Hi, I think I'm just going to close this.  Thanks for the help though.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.