MYSQL Question - jos_virtuemart_product_categories ID column value is 2293744177

[d_a_w_g_y]

Hi All,

Not sure if this one has come up before, apologies if it has , I looked through the forum and didn't find anything, also please redirect me if I'm posting in the wrong place,

SOA2
Vm2

My friends shop is using SOA2 and communicates to VM2 using a C# application,  part of the process is to add new products which includes categories, all has been working fine, until a particular child product at about 1548 virtuemart product id, perhaps an error occurred at the time, not entirely sure yet, also we have no control it seems over the category ID insert (the ID column for jos_virtuemart_product_categories ), unless perhaps instead of using AddProducts (provided by the SOA2 API) we write it ourselves as a series of SQL statements, which might be time consuming but is an option neverless.

So i'm not 'that' worried about how it happened, the c# client code doesn't tell me this (perhaps i will look closer at the WSDL implementation) and its happened on a few different occassions and now the MAX ID value is hitting 2293744177! which is greater than an Unsigned Int 32, which threw an exception when attempting to handle this number in another part of the application code, where we remove products that are in a particular category and then we add back new products to the same particular category and to play it safe (as this is a manual process) we add it to the MAX(ID) + 1, also the ID column structure is int(11) so you can see what might eventually happen

So we can work around it and use a ulong unsigned int 64bit until eventually the enevitable happens, so I will try and find out how this problem can occur and then perhaps rewrite the code using SQL or report a bug to SOA2 / attempt to fix it.

Therefore my question is...  Is it possible to re-order the ID column? (e.g. 'something like' http://stackoverflow.com/questions/10987400/mysql-query-to-reorder-field-values) and if I did are there any unforseen consequences, I will of course do a DB backup before attempting anything,

Any help or advice would be greatly appreciated,

cheers

d_a_w_g_y

[d_a_w_g_y]

OK I think i've found the problem and its in the c# application code

looks like a string value was having + 1 ,  where the increment should be performed against an integer lol

Therefore the question is about re-jigging the ID column,

Cheers

d_a_w_g_y

[d_a_w_g_y]

OK lol

So I backedup the DB ... then I did some research and noticed the table was MyISAM which i believe may not have foerign keys,

No one spoke to me about this so oeerr.,...  I did the following:

SET @count = 0;
UPDATE `jos_virtuemart_product_categories` SET `jos_virtuemart_product_categories`.`id` = @count:= @count + 1;
ALTER TABLE `jos_virtuemart_product_categories` AUTO_INCREMENT = 1;

Which has sorted out the problem and reordered all values sequentially, all products seem to be in the right place so nothing 'looks' broken hehe

Can anyone confirm this was OK to do? just wondering

cheers

d_a_w_g_y

[Milbo]

Hehe, well done.

A lot people wonder why we have for a reference table like virtuemart_product_categories, 3 columns and not just 2. There you see one of the reasons. Great you sorted it