Advancing variable INT

[Inkfast]

I have an interesting little glitch. I'm advancing a number by 1 to create a unique in a MySql db and cfset worked great until it hit 1000, now I'm getting duplicate entry errors on insert query because my cfset seems to be stuck at that 1000 number. Any thoughts are appreciated!

<cfset variable = (GetQuery.variable + 1)>

[-__cfSearching__-]

>> to create a unique in a MySql db

Is there a reason you are not using MySQL's auto incrementing type for this job?

<cfset variable = (GetQuery.variable + 1)>

We need to see more code.

[Inkfast]

I didn't build it. It was designed for manual entries so, no auto-increment. The cfset number 1 higher was working great until I got to 1000. I figured the number is the problem for some reason.... This is a CartWeaver site.

<cfquery name="GetMerchSku" datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
        select Max(SKU_MerchSKUID) as NewMerchSku
        from tbl_skus
        </cfquery>
       
        <cfset NewMerchSku = (GetMerchSku.NewMerchSku + 1)>
       
        <!-- Insert new sku -->
        <cfquery datasource="#request.dsn#" username="#request.dsnUsername#" password="#request.dsnPassword#">
        INSERT INTO tbl_skus(SKU_MerchSKUID,SKU_ProductID,SKU_Price)
        VALUES('#NewMerchSku#','#GetProducts.Product_ID#','#makeSQLSafeNumber(GetSkus.SKU_Price)#')
        </cfquery>

[-__cfSearching__-]

Since this seems to be a Cartweaver issue. Have you tried their forums? They seem active and you might get a better answer there..

great until I got to 1000. I figured the number is the

problem for some reason....

The number 1000 is not a threshold value for any of the CF or mySQL data types. So it is more likely something else is at play.

http://dev.mysql.com/doc/refman/4.1/en/numeric-types.html

select Max(SKU_MerchSKUID)

Start with the obvious. Check the initial SELECT MAX() query and the value after being incremented. Are they even returning the expected values ..?

I didn't build it. It was designed for manual entries so,

no auto-increment.

Well there are still other ways of handling it. Granted few as simple as auto_increment. Not to mention some are database dependent. I suspect that may be a factor in why it was designed this way.

>> select Max(SKU_MerchSKUID)

Plus, unless there is some other locking going on that SELECT MAX(..) can be problematic. As there is nothing to prevent two threads from generating the same value under load. It may not be an issue for most applications, but it is something to be aware of ...

http://mysecretbase.com/get_the_last_id.cfm