bryanlloydharris
asked on
move mysql 3.23 database to mysql 5.0
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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.
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.
ASKER
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);
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);
You could try this to get a more detailed list of errord
mysql
source bryanh.sql
show errors limit 20;
show warnings limit 20;
mysql
source bryanh.sql
show errors limit 20;
show warnings limit 20;
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'
'decimal(6,0) NOT NULL default '0',
payee varchar(20) NOT NULL,
amount decima'
ASKER
Hi, I think I'm just going to close this. Thanks for the help though.
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.