Solved

move mysql 3.23 database to mysql 5.0

Posted on 2006-06-11
8
284 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
0
Comment
Question by:bryanlloydharris
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 30

Accepted Solution

by:
todd_farmer earned 125 total points
ID: 16883018
Can you post the actual SQL for that particular table definition?  It sounds like you might have a reserved word as a column name or something.  The section that you posted (from the MySQL error text) doesn't have a syntax error in it that I find.  It seems likely that there is a reserved word that you are using as a column name.
0
 
LVL 19

Expert Comment

by:Kim Ryan
ID: 16883069
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.
0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 16883141
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.
0
 
LVL 3

Author Comment

by:bryanlloydharris
ID: 16883530
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.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Author Comment

by:bryanlloydharris
ID: 16883533
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);
0
 
LVL 19

Expert Comment

by:Kim Ryan
ID: 16883541
You could try this to get a more detailed list of errord
mysql
source bryanh.sql
show errors limit 20;
show warnings limit 20;
 
0
 
LVL 29

Expert Comment

by:fibo
ID: 16883948
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'
0
 
LVL 3

Author Comment

by:bryanlloydharris
ID: 16913893
Hi, I think I'm just going to close this.  Thanks for the help though.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Creating and Managing Databases with phpMyAdmin in cPanel.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now