From ddlutils-dev-return-1375-apmail-db-ddlutils-dev-archive=db.apache.org@db.apache.org Tue Mar 06 17:27:50 2007 Return-Path: Delivered-To: apmail-db-ddlutils-dev-archive@www.apache.org Received: (qmail 18448 invoked from network); 6 Mar 2007 17:27:50 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 6 Mar 2007 17:27:50 -0000 Received: (qmail 79893 invoked by uid 500); 6 Mar 2007 17:27:58 -0000 Delivered-To: apmail-db-ddlutils-dev-archive@db.apache.org Received: (qmail 79866 invoked by uid 500); 6 Mar 2007 17:27:58 -0000 Mailing-List: contact ddlutils-dev-help@db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: ddlutils-dev@db.apache.org Delivered-To: mailing list ddlutils-dev@db.apache.org Received: (qmail 79855 invoked by uid 99); 6 Mar 2007 17:27:58 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 06 Mar 2007 09:27:58 -0800 X-ASF-Spam-Status: No, hits=-0.0 required=10.0 tests=SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: domain of tomdzk@gmail.com designates 66.249.92.168 as permitted sender) Received: from [66.249.92.168] (HELO ug-out-1314.google.com) (66.249.92.168) by apache.org (qpsmtpd/0.29) with ESMTP; Tue, 06 Mar 2007 09:27:47 -0800 Received: by ug-out-1314.google.com with SMTP id l31so263904ugc for ; Tue, 06 Mar 2007 09:27:24 -0800 (PST) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=YNcdHwjfe9aZ/f+nd1oBoxwiganWArHDlM2I2tD4UaLeihJTHKRDiwCOtZkVXzCo0D0VwBBoVPWXMxxcLzWG+8xkEkRlZkENDa0pSj7AVb7nQwlX+RTl2ch3/oXfLY2FaBHUsHgziE7kMMyJiTNOld4hrcTe7L9MPR4CplGbqKw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=A5D86ea8Cj8KhmIpvVQewYAjdB+5OwEc7B1AtXk2RUl1hxou/WN/EOCjFLU/MNtdY6as5XVWlA3L6mbSPkSkNDUIbgkK/RRGCf9/lMRawaSKr5JzLZVwTaLddPLPj94WIRv2ThHnSGHmnt0IJU0vuQOBPpoXiqPMX93K/B/y4+M= Received: by 10.114.209.1 with SMTP id h1mr1805680wag.1173202042768; Tue, 06 Mar 2007 09:27:22 -0800 (PST) Received: by 10.115.14.18 with HTTP; Tue, 6 Mar 2007 09:27:22 -0800 (PST) Message-ID: <224f32340703060927w5165c7c0p27258009c7f7dcc8@mail.gmail.com> Date: Tue, 6 Mar 2007 09:27:22 -0800 From: "Thomas Dudziak" To: ddlutils-dev@db.apache.org Subject: Re: Mysql PK Problems on table creation In-Reply-To: <687d63ce0703050754q625c1c8agd3b08dcd89c15340@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <687d63ce0703050754q625c1c8agd3b08dcd89c15340@mail.gmail.com> X-Virus-Checked: Checked by ClamAV on apache.org On 3/5/07, Paul Balomiri wrote: > I encountered a problem using ddlutils with mysql. > Starting point was an export done from a life table created with this Script: > ------------------------------------ > CREATE TABLE A (a INTEGER(11) , b1 VARCHAR(255), b2 VARCHAR(255), > PRIMARY KEY (a) ) ; > > ------------------------------------- > > A flat xml export from the life database yielded this xml output for table A > > ----------------------------------------- > > type="INTEGER" size="11" default="0" autoIncrement="false"/> > type="VARCHAR" size="255" autoIncrement="false"/> > type="VARCHAR" size="255" autoIncrement="false"/> > > > >
> ---------------------------------------------- > > The SQL generated by the SqlBuilder class contained an extra line > CREATE INDEX PRIMARY on A(a); which was rejected by the Mysql Engine > because PKs cannot be created using CREATE statements since mysql 4.1 > (see http://bugs.mysql.com/bug.php?id=6062 ). > > thus the problem is twohand: > > 1) When creating a Table the and using PRIMARY KEY(a) within the > CREATE TABLE statement, > the external statement CREATE INDEX PRIMARY... is invalid. > > 2) If a primary Index is to be added after table creation, one cannot > use the CREATE INDEX PRIMARY ... statement. One must instead use the > form ALTER TABLE A ADD PRIMARY KEY (a). PRIMARY, on the other hand > is not a valid INDEX name, thus it is true that all indexes called > PRIMARY must be PKs. There seems to be a difference in mysql between > PK and indexes in general. > Concluding from 2 I think it is safe to assume that all indexes called > 'PRIMARY' from DdlUtils are in fact Primary Keys. Based on this > assumption an overloaded version of > writeExternalIndicesCreateStmt replaces the SQL output for indexes > called 'PRIMARY' with the Alter Table form. > > Even using this form the index creation is not valid, as the key is > duplicate. So I also overloaded the createTable statement, to call a > version of writeExternalIndicesCreateStmt which omits the Indexes > called 'Index' What is odd is that AFAICS the code does use an CREATE INDEX PRIMARY statement at all, it uses a ALTER TABLE ADD CONSTRAINT statement. And also, all unit tests pass on both MySQL 4.1 and 5.0. Could you please check whether you run the latest version of the code ? If yes, then please create an issue in JIRA and attach the patch there. thanks, Tom