2

I have a database which must maintain a strict primary key numerical sequence in its id column. i.e. 1, 2, 3, 4, 5, etc.

Now when I delete a row I then need to do a recount and an increment reset using:

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

This works fine when typed into the MySQL command line and is exactly what I want however I need to execute it from a PHP script command. I use:

$sql="SET @count = 0"; 
$result=$database->query($sql);
$sql="UPDATE Main SET id = @count:= @count + 1"; 
$result=$database->query($sql);
$sql="ALTER TABLE Main AUTO_INCREMENT =1"; 
$result=$database->query($sql);

Now the first two recount lines work fine but the AUTO_INCREMENT line will not reset the auto increment counter. Again "ALTER TABLE Main AUTO_INCREMENT =1;" works fine directly from the MySQL command line but not when executed through the PHP script.

Note: the database uses the mysqli_query command.

Emile Bergeron
  • 17,074
  • 5
  • 83
  • 129
Greg
  • 21
  • 5
  • 2
    Possible duplicate of [Auto Increment after delete in MySQL](http://stackoverflow.com/questions/2214141/auto-increment-after-delete-in-mysql) – Jigar Dec 18 '16 at 06:04
  • Try running them all in the same line: ```$sql="SET @count = 0; UPDATE `Main` SET `id` = @count:= @count + 1; ALTER TABLE Main AUTO_INCREMENT =1;"``` – Bubble Hacker Dec 18 '16 at 06:10
  • No, I did try this before and again just now but it doesn't even do the recount of the primary key. I've also tried replacing semicolons with commas and different punctuations etc... – Greg Dec 18 '16 at 06:31
  • I've tried setting AUTO_INCREMENT to 0 as well as other numbers (even high numbers) and this command just does not affect the actual increment counter using PHP but it does using MySQL command line. – Greg Dec 18 '16 at 06:37

1 Answers1

0

Ok I found it. AUTO_INCREMENT resides in the "information_schema.tables" database and my user had "USAGE" only privileges for this table. With the "ALTER" privilege granted it works fine. Thanks.

Greg
  • 21
  • 5