mysqldump ignores auto_increment

------=_Part_18058_29662660.1144918191541
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi,
I got confused looking to see if this was a known problem - so thought I'd
try again.....

mysql_standard 4.1.16 on Linux...
Synopsis:
Create table with auto_increment
mysqldump database
output doesn't create table with auto_increment.

Example:
Create a table in a junk database using,

create table tbl ( id int(11) not null auto_increment, primary key(id));

Backup the database using,

mysqldump -u ... -p.... junk > out.sql

Look at out.sql, (snipped for brevity),

--
-- Table structure for table `tbl`
--

DROP TABLE IF EXISTS `tbl`;
CREATE TABLE `tbl` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) TYPE=3DMyISAM;

wheres the auto_increment ???

Help!
Regards,
Ian.

------=_Part_18058_29662660.1144918191541--

Re: mysqldump ignores auto_increment

------=_Part_26842_17664579.1144957192775
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Imran,
Thanks for your reply.
I tried what you asked, and it did the same.
I then went onto a couple of other boxes with EXACTLY the same MySQL instal=
l
and they worked (added the auto_increment).

The box that fails is running Redhat 7.3 (for customer backward
compatibilty). I dont have any other 7.3 boxes to try - everything else is
newer.

The my.cnf configuration files are pretty much the same on all installs.
The version we are using is (tarball install)...
mysql-standard-4.1.16-pc-linux-gnu-i686

The box where it doesnt work is the one on which it is most needed!

Many Regards,
Ian.



On 4/13/06, Imran Chaudhry wrote:
>
> Strange, I created the same table using your definition in my test
> database:
>
> MySQL admin@localhost test> show create table tbl;
>
> +-------+--------------------------------------------------- -------------=
----------------------------------+
> | Table | Create Table |
>
> +-------+--------------------------------------------------- -------------=
----------------------------------+
> | tbl | CREATE TABLE `tbl` (
> `id` int(11) NOT NULL auto_increment,
> PRIMARY KEY (`id`)
> ) TYPE=3DMyISAM |
>
> +-------+--------------------------------------------------- -------------=
----------------------------------+
> 1 row in set (0.00 sec)
>
> And then with mysqldump:
>
> imran@workstation:/var/www/partner.smoothwall.net/db$ mysqldump test
> tbl --add-drop-table -Q
>
> -- MySQL dump 9.11
> --
> -- Host: localhost Database: test
> -- ------------------------------------------------------
> -- Server version 4.0.24_Debian-10ubuntu2-log
>
> --
> -- Table structure for table `tbl`
> --
>
> DROP TABLE IF EXISTS `tbl`;
> CREATE TABLE `tbl` (
> `id` int(11) NOT NULL auto_increment,
> PRIMARY KEY (`id`)
> ) TYPE=3DMyISAM;
>
> Can you try again in one step? Create the table and then immediately
> exit the client and mysqldump it.
>
> --
> http://www.ImranChaudhry.info
> MySQL Database Management & Design Services
>

------=_Part_26842_17664579.1144957192775--

Re: mysqldump ignores auto_increment

------=_Part_40020_21482255.1145040697602
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Figured out what was causing it....
The /etc/my.cnf had a line in it, in the [mysqldump] section...
compatible=3Dmysql40

Comment that line out and it works - we need that when sending data to
customers who are still running mysql40.
Now I know whats causing it, I can work around it.

Many thanks,
Ian Collins.


On 4/14/06, Imran Chaudhry wrote:
>
> > The box that fails is running Redhat 7.3 (for customer backward
> > compatibilty). I dont have any other 7.3 boxes to try - everything else
> is
> > newer.
>
> Was there a MySQL installation that came with 7.3 by default? If this
> wasn't properly taken out of service, then you may be using an old
> mysqldump with a newer server (or vice versa) and this may cause the
> issue with autoincrement that you're describing.
>
> On the RH7.3 box can you issue these commands and tell me the results:
>
> mysqld -V
> mysqldump -V
> echo $PATH
>
> If these options are not taken, then can you confirm the mysqlserver
> version by starting mysql client and typing status;
>
> I'm just reaching here, by I suspect that even though you have
> upgraded to 4.1 over a default mysql install, your PATH still points
> to the old mysql progs.
>
> --
> http://www.ImranChaudhry.info
> MySQL Database Management & Design Services
>

------=_Part_40020_21482255.1145040697602--