Solved

move mysql 3.23 database to mysql 5.0

Posted on 2006-06-11
8
291 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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
 
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

734 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