MySQL will only try to auto-generate keys when you don't supply one on your own. If you have Cayenne generating the keys, it'll push them to MySQL. However, I would suggest not doing both strategies on the same table as it will probably bite you in the long run somehow.
mrg On Tue, Apr 20, 2010 at 12:31 PM, Joe Baldwin <[email protected]> wrote: > Michael, > > Good catch. I did not intentionally select the checkbox "Create Primary Key > Support" (I have deselected this). > > However, I did verify that all the tables in the database have Auto-Increment > for the oid field. > > So, if I had "PK Generation Strategy" set to "Database-Generated", and each > table had "auto-increment" set to true for the oid field (which I just > verified by inspecting each of the tables), which would have created the > value for the oid field? (I am assuming this would have been generated by > MySQL InnoDB.) > > Thanks, > Joe > > > > > > On Apr 20, 2010, at 11:17 AM, Michael Gentry wrote: > >> 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 >>>>> >>>>> >>>>> >>> >>> > >
