1

I want to set the auto_increment value for each column by first finding the max id value.

I am referencing the code from this SO question.

The mysql docs for prepared statements show a similar format, so I am confused.

When I try running the prepare statement I get a failure. Why?

Below is the output when I try to prepare a regular statement and then when I try to prepare the auto_increment statement with a '?' for binding later.

mysql> PREPARE stmt1 FROM 'ALTER TABLE user AUTO_INCREMENT=2';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> PREPARE stmt1 FROM 'ALTER TABLE user AUTO_INCREMENT=?';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1
Community
  • 1
  • 1
Klik
  • 1,757
  • 1
  • 21
  • 38
  • possible duplicate of [How to Reset an MySQL AutoIncrement using a MAX value from another table?](http://stackoverflow.com/questions/2410689/how-to-reset-an-mysql-autoincrement-using-a-max-value-from-another-table) –  Mar 13 '15 at 14:37
  • The question you linked to contains a number of comments on exactly this problem, and at least one answer has a viable work-around. If your question is 'why' rather then 'how' then it's probably off-topic as opinion-based given that the documentation doesn't cover this use case. –  Mar 13 '15 at 14:40
  • I would like to know `why` as in, is it due to mysql version or OS etc. I need to make sure my code can run on multiple servers. – Klik Mar 13 '15 at 14:44
  • If you're concerned about portability take the pragmatic approach and don't use a prepared statement for this task. I can't see why you'd want to use user input here, and anyway it's not difficult to ensure that the value you're using in a concatenation is an integer. –  Mar 13 '15 at 14:49

1 Answers1

1

An alternative would be

set @alter_statement = concat('alter table user auto_increment = ', @value);
prepare stmt1 from @alter_statement;
execute stmt1;
deallocate prepare stmt1;

For some reasons it seems many people are experiencing a syntax error when using prepare with ?.

My guess is that it fail because the given value will be replaced with the value, between two single quotes (this is just a guess though).

Jean-François Savard
  • 20,626
  • 7
  • 49
  • 76