Reset auto-increment in itop 2.7

[Rafael AINCIART]

Hi!

I created a root class 'Affair' and his childs 'opportunity', 'order' and 'project'.
In this classes, I used the new way of getting new IDs by using ItopCounter::IncClass() method.

<method id="DBInsertNoReload">
    <static>false</static>
    <access>public</access>
    <type>Overload-DBObject</type>
    <code>
        <![CDATA[
            public function DBInsertNoReload() {
                $iNextId = ItopCounter::IncClass(get_class($this));
                $sThisClass = get_class($this);
                $sRef = $this->MakeAffairRef($sThisClass, $iNextId);
                $this->SetIfNull('ref', $sRef);
                $iKey = parent::DBInsertNoReload();
                return $iKey;
            }
        ]]>
    </code>
</method>
<method id="MakeAffairRef">
    <static>false</static>
    <access>protected</access>
    <type>Overload-DBObject</type>
    <code>
        <![CDATA[
            protected function MakeAffairRef($sThisClass, $iNextId) {
                return sprintf(static::GetAffairRefFormat($sThisClass), $iNextId);
            }
        ]]>
    </code>
</method>
<method id="GetAffairRefFormat">
    <static>true</static>
    <access>public</access>
    <type>Overload-DBObject</type>
    <code>
        <![CDATA[
            public static function GetAffairRefFormat($sThisClass) {
                switch($sThisClass) {
                    case 'Opportunity':
                        $sFormat = 'PC-%06d';
                        break;
                    case 'Order':
                        $sFormat = 'CD-%06d';
                        break;
                    case 'Project':
                        $sFormat = 'PJ-%06d';
                        break;
                    default:
                        $sFormat = 'NA-%06d';
                }
                return $sFormat;
            }
        ]]>
    </code>
</method>

Now I would like to reset auto-increment :

DELETE FROM affair;
DELETE FROM opportunity;
DELETE FROM `order`;
DELETE FROM project;
ALTER TABLE affair AUTO_INCREMENT = 1;
ALTER TABLE opportunity AUTO_INCREMENT = 1;
ALTER TABLE `order` AUTO_INCREMENT = 1;
ALTER TABLE project AUTO_INCREMENT = 1;

It works and the new records start to 1 in database but new 'ref' are not reset and for the database record 1 I have a ref like 'PC-000017'. Do you have an idea to help me ?
Thanks a lot.

Rafael.

[Rafael AINCIART]

In addition :
Executing SHOW TABLE STATUS in my database, I have the following result :
Name Auto_increment
affair 1
opportunity 1
order 1
project 1
It's OK.
But when I create a new opportunity (for example), I have this :

Proposition commerciale: PC-000018

I really don't understand...
I checked if another table has an auto increment value of 18 but none.

Where do I do a mistake ?

[Rafael AINCIART]

Hi!
I share with you the results of my investigations : the issue seems to come from ItopCounter::IncClass().
In my last test, I logged the values in my code :
- $iNextId = ItopCounter::IncClass(get_class($this)); --> it returns the 25 ! After the reset of auto increment, it would be 1
- $iKey = parent::DBInsertNoReload(); --> it returns 1, the expected value.

The solution could be to use $iKey to increment my class ref but I would like to undestand if I am using this function wrong ?

EDIT : using $iKey instead of $iNextId doesn't work because it not save 'ref' in database :(
Another idea ?

[Vincent @ Combodo]

The table which contains the counters is 'key_value_store'
I am a little bit lost with your SQL command, they are not related to what is the next ID to use.

[Rafael AINCIART]

Hi Vincent !

Unless I am mistaken, 'key_value_store' is a new table that didn't exists before 2.7 release. And in older versions, I used to reset auto increment key counter of a class by executing the following commands :
DELETE FROM my_class;
ALTER TABLE my_class AUTO_INCREMENT = 1;
And it was working.
The ID of the dedicated table of my new class was the same as the counter used to generate the ref values.

In 2.7, if I understand correctly, the counter used to generate the ref values is distinct of the ID of the dedicated table of my new class and it is stored in the new 'key_value_store' table.
In order to reset my counter, I have to do :
DELETE * FROM my_class;
UPDATE key_value_store SET value = 1 WHERE key_name = 'my_class';

Did I understand correctly?

Thanks for your help!

Regards,

Rafael.

[Pierre Goiffon]

Hello,

Indeed in iTop 2.7.0 we changed the way the Ticket.ref field is generated. Some documentation can be found in the "What's new" document of this version : Ticket Ref generation

Before 2.7.* we were getting the next id (autoincrement field in the itop object's database table) value just before doing the real insert.
This doesn't work with MySQL 8 as this is cached.

A solution could have been to split the reference init : doing the insert, read iTop object id, setting ref, doing an update.
But this would have meant changing the create stack, and we weren't very eagger to do it.

So we decided instead to adopt a very generic mechanism : a key-value store mechanism that can be used anywhere, with a specific implementation handling auto incremented values. This is ItopCounter object as you find out.

As I understand, you had some objects, then removed them, and want to reset the ItopCounter value for this class ? If so, locate the key_value_store table, the line with key_name column equals to your iTop object name. The value column will contain the auto increment value.

[Rafael AINCIART]

Hi Pierre!

Your solution is a good enhancement :)
I had read the documentation and adapted my code to the new way of doing and it worked correctly. I faced an issue only because I needed to reset the reference counter before going into production (this is not the most common case) and because I didn't know this new table.
Now it's clear for me.
I did the tests and it works now.

Thank you to both of you !

Rafael.

[Pierre Goiffon]

Glad to hear your problem is solved 👌

Out of curiosity, can you give more details on :

I faced an issue only because I needed to reset the reference counter before going into production (this is not the most common case)

What procedure do you follow to go from test to production ? We might give some advice on this too O:)

[Rafael AINCIART]

This time, I'm quickly building (~3 days) a web app for managing commercial documents and processes and for 10 users. So it's a simplified procedure : I develop directly on the production instance :)

[Pierre Goiffon]

Ok thanks, understood :)

[Pierre Goiffon]

For reference, a ticket was opened but I closed it as everything was said here : [#1953]