Hi Joe, The AUTO_PK_SUPPORT table is only used when PK Generation Strategy = Default. Cayenne Modeler creates the AUTO_PK_SUPPORT if when generating the SQL you check the "Create Primary Key Support" checkbox. You don't need AUTO_PK_SUPPORT unless you are using the Default strategy on some of your tables.
Your create statement should look more like: CREATE TABLE pickers.product (..., oid BIGINT NOT NULL AUTO_INCREMENT, ..., PRIMARY KEY (oid)) ENGINE=InnoDB; Make sure your create statement includes the AUTO_INCREMENT option for your oid. If you don't have AUTO_INCREMENT as an option, MySQL will not auto-increment or generate your PKs. mrg On Tue, Apr 20, 2010 at 10:15 AM, Joe Baldwin <[email protected]> wrote: > Michael, > > I need a simple verification (because the webhost tech person is very > confused). > > To explain further: I am using Cayenne Modeler to design the entities, and to > create the database tables in MySQL. (i.e. Tools - Generate Database Schema). > My understanding, and my tests have proved, if you set [PK Generation > Strategy] to "Database-Generated", and [Auto Incremented] to "oid (INTEGER)" > then Cayenne Modeler will "request/instruct/ask " MySQL to create a table, > then set the oid as the primary key, and set auto_increment to "true" for > that field. > > The following code was generated by the Cayenne Modeler (I deleted everything > not having to do with the table or key) > > CREATE TABLE pickers.product (PRIMARY KEY (oid)) ENGINE=InnoDB; > CREATE TABLE AUTO_PK_SUPPORT ( TABLE_NAME CHAR(100) NOT NULL, > NEXT_ID BIGINT NOT NULL, UNIQUE (TABLE_NAME)); > DELETE FROM AUTO_PK_SUPPORT WHERE TABLE_NAME IN ('product'); > INSERT INTO AUTO_PK_SUPPORT (TABLE_NAME, NEXT_ID) VALUES ('product', > 200); > > > The question now is: Has Cayenne Modeler generated SQL that creates a table > named "product", with a primary key "oid", whose value is automatically > created by MySQL's InnoDB engine, using auto_increment, at the time of the > INSERT? Another way of asking this is: In this scenario, does Cayenne > generate the key, or does MySQL generate the key? > > I would like to forward a brief response to the webhost. > > Thanks, > Joe > > > > > On Apr 20, 2010, at 9:25 AM, Michael Gentry wrote: > >> Hi Joe, >> >> Your wording on #2 and #3 was a little curious to me. I don't know if >> you used Cayenne Modeler to generate your schema or if you are using >> an existing schema. Cayenne (at runtime) doesn't really instruct the >> database (MySQL) to auto-generate keys. Cayenne will nont provide a >> value for "oid" when inserting the record and that essentially >> instructs MySQL to auto-generate a key. You set this up when you >> create your product table, for example. The database needs to know >> that it is responsible for auto-generating the keys -- Cayenne won't >> tell it at runtime (other than leaving the key out on insert). >> >> Maybe I'm a bit off on what you were asking, but it seems like you are >> expecting Cayenne to tell MySQL to generate the keys when it actually >> works the other way around: MySQL is supposed to know to generate the >> key and Cayenne then asks it for the key it generated. >> >> mrg >> >> PS. BTW, your settings are correct to use the auto-generated key >> feature in MySQL, just be sure the table was created to auto-generate >> on your "oid" column. >> >> >> On Mon, Apr 19, 2010 at 8:03 PM, Joe Baldwin <[email protected]> wrote: >>> I apologize for this, but I am being forced to verify a configuration that >>> is pretty obvious. >>> >>> Please refer to the attached CM configuration panel for an Entity. >>> >>> 1. The Primary Key "PK Generation Strategy" is set to "Database-Generated". >>> Please verify that this means that the PK Generation Strategy is Database >>> Generated. >>> 2. The Primary Key "Auto Increment" is set to "oid (INTEGER)". Please >>> verify that the field being auto-incremented is "oid". >>> 3. Please verify that (with the DBMS set to MySQL) that the two previous >>> parameters instruct MySQL to use its auto_increment feature to create a new >>> "oid" value for inserted records. >>> >>> Thanks >>> Joe >>> >>> >>> > >
