By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,197 Members | 1,670 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,197 IT Pros & Developers. It's quick & easy.

Controlling output of mysqldump 10.9

P: n/a
My client is using a shared server running MySQL 2.4, however the in
house server is 4.1.12 with mysqldump 10.9. Is there a way to have the
10.9 version output a format that is fully compatible with 2.4?

Some of the CREATE TABLE statements in 10.9 are not accepted by 2.4.

Currently, I am not using any of the switches in creating the output
file other than the -u, -h and -p.

Todd
Nov 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
You're running MySQL 2.4? Are you really sure? I've never heared anything
about MySQL versions prior to 3.x.x - I don't even know anything about the
history (and you could call that history) of MySQL < 3.

That would really astonish me a lot.

Markus
Nov 23 '05 #2

P: n/a
>My client is using a shared server running MySQL 2.4, however the in

*WHAT* is running MySQL 2.4 (client or server), and what is
that the version of? If that's the version of mysqld, that's
ancient, ancient software. If it's the version of the client,
well, the client version is rarely related to what SQL syntax
is accepted.

It is probably better to refer to the distribution version that
it came with (mysqldump --version or mysql --version will tell
you that, at least recent ones will).
house server is 4.1.12 with mysqldump 10.9. Is there a way to have the
10.9 version output a format that is fully compatible with 2.4?

Some of the CREATE TABLE statements in 10.9 are not accepted by 2.4.
mysqldump 10.10 (distribution version 5.0.15), which I'm running
currently, has the option --compatible which can limit the features
in created SQL for the dump. Consider --compatible no_table_options
or --compatible mysql323 . I believe the --compatible option is
not new but to use it you need mysqld 4.1.0 or later, which you have.
Currently, I am not using any of the switches in creating the output
file other than the -u, -h and -p.


Gordon L. Burditt
Nov 23 '05 #3

P: n/a
Todd Cary wrote:
My client is using a shared server running MySQL 2.4, however the in
house server is 4.1.12 with mysqldump 10.9. Is there a way to have the
10.9 version output a format that is fully compatible with 2.4?
Are you sure you've got that right? I've never seen MySQL version 2.4.
I can't find any release announcement for MySQL 2.4. The
announcements archive on mysql.com goes back to the beginning of 2000,
and that's for MySQL version 3.22. If your client is running MySQL 2.4,
that's incredibly old, and there are probably no guarantees of
compatibility.
Some of the CREATE TABLE statements in 10.9 are not accepted by 2.4.


The specific statements, and error messages, would be required for a
diagnosis and recommended solution.

I'd recommend that you read the options to mysqldump at
http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html
You might try "--compatible=ansi" for instance.

Any other solution is likely to require a custom text-processing script
to modify the mysqldump output and remove incompatible elements. Perl
is a good language for tasks like this.

Regards,
Bill K.
Nov 23 '05 #4

P: n/a
My mistake! That is the version of phpMyAdmin.

This is the destination server.
# phpMyAdmin MySQL-Dump
# version 2.4.0
# http://www.phpmyadmin.net/ (download page)
#
# Host: 10.0.11.23
# Generation Time: Nov 13, 2005 at 09:09 AM
# Server version: 4.0.24 <-----------------<<<
# PHP Version: 4.3.2
# Database : `sfyc`
# --------------------------------------------------------

The other header is

This is the source server.

-- MySQL dump 10.9
--
-- Host: localhost Database: sfyc
-- ------------------------------------------------------
-- Server version 4.1.12 <--------<<<

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Which lines should I uncomment?

Todd
Todd Cary wrote:
My client is using a shared server running MySQL 2.4, however the in
house server is 4.1.12 with mysqldump 10.9. Is there a way to have the
10.9 version output a format that is fully compatible with 2.4?

Some of the CREATE TABLE statements in 10.9 are not accepted by 2.4.

Currently, I am not using any of the switches in creating the output
file other than the -u, -h and -p.

Todd

Nov 23 '05 #5

P: n/a
This syntax:
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;


usually takes care that the code is compatible with any server version. For
example, this line of code is valid for MySQL > 4.1.1, so it is marked with
/*!40101 ... */ that means that the line will be ignored for MySQL versions
below 4.1.1.

As far as I have experienced, mysqldump manages that absolutely great that
you can use a dump file from any version for any other version.

Markus
Nov 23 '05 #6

P: n/a
So, you've got source server: 4.1.12, destination server: 4.0.24

** /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
** /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
** /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
** /!40101 SET NAMES utf8 */;
!! /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
!! /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS,
FOREIGN_KEY_CHECKS=0 */; ** /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
** /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
Which lines should I uncomment?


The destination server should ignore anything with a version over
40024 (since it's version 4.0.24). The lines marked above with **
should be ignored, and the lines marked with !! should be executed.
You can leave all of these lines alone.

But you said it was the CREATE TABLE statements that were being
rejected, but didn't show one that was. You should run mysqldump
with --compatible mysql40 for transferring from 4.1.12 to 4.0.24 .

Gordon L. Burditt
Nov 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.