mysqldump not including auto_increment statements
law...@gmail.com
I'm using mysqldump to dump an innodb database. I've looked at the
resulting output file, and the CREATE TABLE statements do not include
the auto_increment for the fields I've specified.
CREATE TABLE `CardQuestions` (
`id` int(10) unsigned NOT NULL,
...
However, phpMyAdmin creates a table the way I already specified with
its export command:
CREATE TABLE IF NOT EXISTS `CardQuestions` (
`id` int(10) unsigned NOT NULL auto_increment,
...
I've read over the mysqldump man page, and it doesn't say anything
about switches for including auto_increment statements. Similar google
searches have turned up nothing about my problem. What's going on?
Peter H. Coffin
Can't replicate this. See `comment_ID` definition.
$ mysqldump -uroot -p --opt --databases pinball
-- MySQL dump 10.11
--
-- Host: localhost Database: pinball
-- ------------------------------------------------------
-- Server version 5.0.33-log
[...]
--
-- Table structure for table `comment`
--
DROP TABLE IF EXISTS `comment`;
CREATE TABLE `comment` (
`comment_ID` int(7) NOT NULL auto_increment,
`portLoc_ID` int(7) NOT NULL default '0',
`comment` varchar(255) NOT NULL default '',
`cchangeDate` datetime NOT NULL default '0000-00-00 00:00:00',
`cchangeBy` varchar(20) NOT NULL default '',
`displayFlag` enum('0','1') NOT NULL default '0',
`ip_address` varchar(15) NOT NULL default '',
PRIMARY KEY (`comment_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;
--
89. After I capture the hero's superweapon, I will not immediately disband my
legions and relax my guard because I believe whoever holds the weapon is
unstoppable. After all, the hero held the weapon and I took it from him.
--Peter Anspach's list of things to do as an Evil Overlord
law...@gmail.com
>
> Can't replicate this. See `comment_ID` definition.
>
> $ mysqldump -uroot -p --opt --databases pinball
> -- MySQL dump 10.11
> --
Peter, thanks for taking the time to test this out on your system.
It looks like the source of my problems is the --opt switch. I set up
my backup scripts with the --skip-opt switch in order to avoid
extended inserts. ( I had some problem in the past with an extended
inserts, which I can't remember the details of ). Offhand, it's easier
to grep through a dump file and find the source of any problems when
each row is a single line.
If you run a mysqldump with --skip-opt, do you get the auto_increments
in your create table statements? The man page for mysqldump says that
--opt is shorthand for the following switches: --add-drop-table, --add-
locks, --all, --extended-insert, --quick, --lock-tables . The man page
doesn't mention anything about auto_increment for any of those
switches; in fact, it doesn't mention auto_increment at all. I'm
guessing it's the --all switch ( "Include all MySQL specific create
options." ) that includes the auto_increment statements?
Peter H. Coffin
Looks like you've got it sussed. --all on my mysqldump is
--create-options; dropping it spits out this table def for the same
database:
$ mysqldump -uroot -p --skip-opt --add-drop-table --add-locks
--disable-keys --extended-insert --lock-tables --quick --set-charset
--databases pinball
[...]
--
-- Table structure for table `comment`
--
DROP TABLE IF EXISTS `comment`;
CREATE TABLE `comment` (
`comment_ID` int(7) NOT NULL,
`portLoc_ID` int(7) NOT NULL default '0',
`comment` varchar(255) NOT NULL default '',
`cchangeDate` datetime NOT NULL default '0000-00-00 00:00:00',
`cchangeBy` varchar(20) NOT NULL default '',
`displayFlag` enum('0','1') NOT NULL default '0',
`ip_address` varchar(15) NOT NULL default '',
PRIMARY KEY (`comment_ID`)
);
No auto-increment. Looks like you'll probably want to keep --skip-opt,
and add back all (or most) of the --opt implicit options except
--extended-insert to make your project work the way you want.
--
55. The deformed mutants and odd-ball psychotics will have their place in my
Legions of Terror. However before I send them out on important covert
missions that require tact and subtlety, I will first see if there is
anyone else equally qualified who would attract less attention. --Overlord
law...@gmail.com
> On Tue, 12 Feb 2008 12:33:24 -0800 (PST), lawp...@gmail.com wrote:
>
> > If you run a mysqldump with --skip-opt, do you get the auto_increments
> > in your create table statements? The man page for mysqldump says that
> > --opt is shorthand for the following switches: --add-drop-table, --add-
> > locks, --all, --extended-insert, --quick, --lock-tables . The man page
> > doesn't mention anything about auto_increment for any of those
> > switches; in fact, it doesn't mention auto_increment at all. I'm
> > guessing it's the --all switch ( "Include all MySQL specific create
> > options." ) that includes the auto_increment statements?
>
> Looks like you've got it sussed. --all on my mysqldump is
> --create-options; dropping it spits out this table def for the same
> database:
<snip>
It would be nice if that were mentioned in the man or docs
somewhere ;) Is the auto_increment non ANSI standard SQL? I can't
imagine a scenario where you wouldn't want the auto_increments
included in your dumps, unless you were creating a universal ANSI sql
file.
> No auto-increment. Looks like you'll probably want to keep --skip-opt,
> and add back all (or most) of the --opt implicit options except
> --extended-insert to make your project work the way you want.
That sounds about right. A while ago, when I first set up the scripts,
I had mondo problems figuring between the --extended and --complete
switches ( wait, which means what? ), so I just settled for --skip-
opt. If I am remembering correctly, --opt seems to take precedence
over --complete, so that you have a single line INSERT for all table
rows. But I'll do some more testing ;)
Peter H. Coffin
> On Feb 12, 3:32 pm, "Peter H. Coffin" <hell...@ninehells.com> wrote:
>> On Tue, 12 Feb 2008 12:33:24 -0800 (PST), lawp...@gmail.com wrote:
>>
>> > If you run a mysqldump with --skip-opt, do you get the auto_increments
>> > in your create table statements? The man page for mysqldump says that
>> > --opt is shorthand for the following switches: --add-drop-table, --add-
>> > locks, --all, --extended-insert, --quick, --lock-tables . The man page
>> > doesn't mention anything about auto_increment for any of those
>> > switches; in fact, it doesn't mention auto_increment at all. I'm
>> > guessing it's the --all switch ( "Include all MySQL specific create
>> > options." ) that includes the auto_increment statements?
>>
>> Looks like you've got it sussed. --all on my mysqldump is
>> --create-options; dropping it spits out this table def for the same
>> database:
>
><snip>
>
> It would be nice if that were mentioned in the man or docs
> somewhere ;) Is the auto_increment non ANSI standard SQL? I can't
> imagine a scenario where you wouldn't want the auto_increments
> included in your dumps, unless you were creating a universal ANSI sql
> file.
Nope, it's not standard SQL at all. DB2 (the RDBMS I'm otherwise
most familiar with) has two different ways of doing kind of what
auto-increment does: SEQUENCEs and IDENTITYs. IDENTITYs work a lot like
autoincrement. SEQUENCES are magic ways of coming up with values across
the whole database, by name, and you plug them into records yourself.
Every time something anywhere on the database instance ask for the
value of a named sequence, it gets the next value in line, subject to
a whole lot of conditions (max value, min value, how many values to
skip, whether the values are allow to roll over, ascending or decending,
etc.).
--
57. Before employing any captured artifacts or machinery, I will carefully
read the owner's manual.
Adam
So, if you are doing mysqldump and using --skip-opt, be sure to include --create-options and you will not have this issue.
On Tuesday, February 12, 2008 11:35:37 AM UTC-7, (unknown) wrote:
> Hello all --
>
> I'm using mysqldump to dump an innodb database. I've looked at the
> CREATE TABLE `CardQuestions` (
> `id` int(10) unsigned NOT NULL,
> ...
>
> However, phpMyAdmin creates a table the way I already specified with
> its export command:
>
> CREATE TABLE IF NOT EXISTS `CardQuestions` (
> `id` int(10) unsigned NOT NULL auto_increment,
> ...
>