Public bug reported:
Binary package hint: mysql-client-5.0
I have a database on a Dapper system with a table with the following structure:
mysql> show columns from grmn;
+-----------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+----------+------+-----+---------+----------------+
| serial | int(11) | NO | PRI | NULL | auto_increment |
(only the first column is shown)
The system has the latest mysql for Ubuntu Dapper 6.06:
mysql Ver 14.12 Distrib 5.0.22, for pc-linux-gnu (i486) using readline 5.1
I have another system which is still running Breezy 5.10 with mysql Ver
12.22 Distrib 4.0.24. If I want to use mysqldump to transfer this
database to the Breezy system, I use mysqldump as follows, indicating
that the output should be compatible with a 4.0 version of MySQL:
mysqldump --opt --compatible=mysql40 -u peter -p$passw german >
german_breezy.sql
The beginning of the dump file is as follows. Note that the
auto_increment attribute is left off:
-- MySQL dump 10.10
--
-- Host: localhost Database: german
-- ------------------------------------------------------
-- Server version 5.0.22-Debian_0ubuntu6.06.2-log
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!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,MYSQL40'
*/;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `grmn`
--
DROP TABLE IF EXISTS `grmn`;
CREATE TABLE `grmn` (
`serial` int(11) NOT NULL,
The effect of this was that I could search and read the resulting
database on the destination system, after running the german_breezy.sql
file, but could not make any insertions. After investigating for a
while, I saw that the serial field restored database did not have the
auto_increment property. At that point, I could use ALTER TABLE to
change the properties of the serial column, making it an auto_increment
column, and that worked. Or I could change the column properties in the
.sql file and update from that.
Running mysqldump without the --compatible option resulted in a dump
file that had the auto_increment property, but the dump file was not
compatible with the older version of mysql without some other editing.
** Affects: mysql-dfsg-5.0 (Ubuntu)
Importance: Undecided
Status: Unconfirmed
--
mysqldump --opt --compatible=mysql40 fails to preserve auto_increment
https://launchpad.net/bugs/65217
--
ubuntu-bugs mailing list
[email protected]
https://lists.ubuntu.com/mailman/listinfo/ubuntu-bugs
