MySQL - Error resetting ID from 1 to max

Question

I want to reset my ids from 1 to the max one because, since I need to update a table many times, I risk to have ids greater than the maximum value for the ID column.
For example, sometimes I get the ID 2842719402837 that is too big to be entered in my ID column (INTEGER, length 11).
I use this query to reset the ids from 1 to the max one :

SET @num := 0;
UPDATE riepilogo SET id = @num := (@num+1);
ALTER TABLE riepilogo AUTO_INCREMENT =1;

Here you can find the answer whence I got the SQL : Click Here

While it's working perfectly on phpMyAdmin, it's not working with PHP.
I'm using this code to execute the query :

$sql = "SET  @num := 0;
        UPDATE riepilogo SET id = @num := (@num+1);
        ALTER TABLE riepilogo AUTO_INCREMENT =1;";
$result = $conn->query($sql);
if(!$result) die("MySQL Error : ".$sql."<br>Error : ".$conn->error);

Naturally I already set the $conn variable, but I'm getting the following error :

MySQL Error : SET @num := 0; UPDATE riepilogo SET id = @num := (@num+1); ALTER TABLE riepilogo AUTO_INCREMENT =1;
Error : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE riepilogo SET id = @num := (@num+1); ALTER TABLE riepilogo AUTO_' at line 2

So, this query is working with the SQL console of phpMyAdmin, but it's not executing it with PHP.
What am I doing wrong? Thanks a lot!

Why aren't you using SERIAL DATATYPE? Int with autoincrement is too low limit. — capcj, May 26 '17 at 17:35
What php db tool are you using? I believe for PDO you can't do multiple sentences unless you do a previous set up first https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd — Lixus, May 26 '17 at 17:38
To make it easier - move this code to a stored procedure and simply call the procedure. — Nigel Ren, May 26 '17 at 17:52
@Lixus Well I'm not sure what you mean, are you asking me for my method to create the connection? — DamiToma, May 26 '17 at 17:53
@NigelRen You're solution was the only one that worked, thanks a lot! — DamiToma, May 26 '17 at 18:12

score 0 · Answer 1 · answered May 26 '17 at 17:39

0

You could use BIGINT datatyppe instead of INT

As per your requirement use the below code to reset AUTO_INCREMENT,

$sql = "ALTER TABLE riepilogo AUTO_INCREMENT = 1";
$result = $conn->query($sql);
if(!$result) die("MySQL Error : ".$sql."<br>Error : ".$conn->error);

answered May 26 '17 at 17:39

Praveen

602
4
7

Using BIGINT instead of INT will just extend my table duration, but it won't solve my problem. And your query is not working, as it just set the AUTO_INCREMENT but not from 1. – DamiToma May 26 '17 at 17:52

MySQL - Error resetting ID from 1 to max

1 Answers1