Mysql alter table AUTO_INCREMENT = 0

Question

0.00/5 (No votes)

See more:

At each Directory change, I store the filenames in a table.
Reset table and set AUTO_INCRTEMENT = 0

The first time the counter is = 0
id = 0
id = 1
id = 2
...

The following times I empty the table and set AUTO_INCREMENT = 0 the value of the first id is always = 3

id = 3
id = 4
id = 5
....

tb_temporary - InnoDb - utf8mb4 - utf8mb4_900_ai_ci
id = INT(11) - PK - NN - AI

What I have tried:

int Contatore = 0;
            Query = "ALTER TABLE tb_temporary AUTO_INCREMENT = @contatore";
            gesdb.AlterCounter(Query, conn, Contatore);

public void AlterCounter(string Query, MySqlConnection MyConn2, int contatore)
       {
           try
           {
               MyConn2 = Connection;

               if (MyConn2.State != ConnectionState.Open)
               {
                   MyConn2.Open();
               }

               MySqlCommand cmd = new MySqlCommand(Query, MyConn2); //assegna MySqlCommand

               cmd.Parameters.AddWithValue("@contatore", contatore);

               //trs = MyConn2.BeginTransaction();
               //cmd.Transaction = trs;
               try
               {
                   cmd.ExecuteNonQuery();
                   //trs.Commit();
               }
               catch
               {
                   //trs.Rollback();
               }
           }
           catch
           {

           }
       }

Posted 19-Aug-20 5:34am

Member 13174280

Updated 19-Aug-20 5:47am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-08-19T05:44:00

You can only set an auto increment value to greater than the largest value already in the table - if you think about it that makes total sense: if you didn;t then duplicate index values become a high probability and that's a very bad idea.
So the second time you try, the largest value in the table is 2, and your attempt to set it to zero is ignored. You can set it to 3 or greater, but not below that.

Sandeep Mewara · Answer 2 · 2020-08-19T05:47:00

SQL

ALTER TABLE some_table AUTO_INCREMENT = 0

Quote:
As of MySQL 5.6, documentation[^] now states: For both InnoDB and MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum AUTO_INCREMENT column value plus one.

Quote:
How To Reset MySQL Autoincrement Column. Autoincrement Number Reseting[^] : Note that you cannot reset the counter to a value less than or equal to any that have already been used

Will reset the auto_increment value to be the next value based on the highest existing value in the auto_increment column. It will not just default reset to 0 as you are expecting.

Mysql alter table AUTO_INCREMENT = 0

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0