Controlling output of mysqldump 10.9

[Todd Cary]

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

[Markus Popp]

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

[Gordon Burditt]

>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

[Bill Karwin]

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.

[Todd Cary]

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

[Markus Popp]

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

[Gordon Burditt]

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