Solved

move mysql 3.23 database to mysql 5.0

Posted on 2006-06-11
8
286 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

803 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