Order numbers too high

I somehow have set my current order number to the largest possible number allowed in an interger field. As the number reset in Admin>Tools>Store Manager claims it can only set the number forward, how can I resest the number back to zero and start again. I have tried to reset the counter by leaving both fields blank but this didn't work.

Any thoughts?

Thanks

To state that a little clearer I think.

1. I was trying to implement a reference system into the order number and subsequently changed the New ID Order number to a number greater than 2147483647 which is apparently the largest number you can hold in an integer, of which the primary key is one.
2. As the number reset in Admin>Tools> Store Manager only allows for increases to the id number , is there any way I can
   a) change the database field to a bigint or
   b) reset the product orders to 0

All of the orders on the site are test anyway so it does not matter if they have to be deleted.

Thanks again.

While it's not advised for novices, the order number can be re-set using phpMyAdmin (the dbase editing tool).

This is an auto-increment number, so not only will you have to manually edit this down, but you will also need to manually edit each order that has been placed using the larger integers - and they need to be incremented in sequence.

Further, these numbers probably have a relational function as well - such as in the customer order history - so any changes to the order record will need to be identically reflected in all other tables/fields where these numbers are used to cross-reference data.

To do this carefully, go to phpMyAdmin and browse the orders table in order by orders_id ...

Find the high orders_id ... this is probably just 1 of them, but check for others ...

Next, go to the Store Manager and change the High orders_id that you found to the next proper orders_id ...

If you have other High orders_id fix each one in the Store Manager with a proper orders_id ...

Once all these are fixed, browse the orders table in phpMyAdmin again by orders_id and see what the new High orders_id is ...

Now run, in phpMyAdmin this command with the value of the High orders_id plus 1, so if you see 12576 use 12577:

ALTER TABLE orders AUTO_INCREMENT = 12577

where 12577 is you next orders_id that would be valid ...

NOTE: backup your database before trying this ...

After this is done, you will want to make a test order to make sure all is working correctly ...

Be careful in doing this, and it would be a good idea to set the site Down for Maintenance while you fix it ...

Apologies for the delay in thanks chaps. The db of products was still in the development stage so I just truncated the table and started again. Thanks for the responses.