CODEWITHSUNDEEP
mysqlauto-increment
Dawn Green
Dawn Green

Reputation: 493

MySQL Auto-Increment query is returning two rows

I have an inno-db table with one primary key which is the auto-increment column. I am using the following query to find the next auto-increment id:

SELECT Auto_increment FROM information_schema.tables WHERE table_name='mytable';

However, when I view the results, I get two rows returned with two values (1352, 123841). I've tried resetting the auto-increment value and even deleted the rows below 2000. How can reset the table to return a single result?

Upvotes: 2

Views: 606

Answers (2)

Rahul Tripathi
Rahul Tripathi

Reputation: 172578

You probably have two tables in your db. Try to check the schema and you will find the reason:-

SELECT table_schema, Auto_increment
FROM information_schema.tables 
WHERE table_name='mytable';

Upvotes: 0

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

You just might have two databases in the MySQL Instance with the same table name.

SELECT table_schema, Auto_increment
FROM information_schema.tables WHERE table_name='mytable';

If you are asking about the database you are standing in, run this

SELECT Auto_increment
FROM information_schema.tables
WHERE table_name='mytable'
AND table_schema=DATABASE();

Give it a Try !!!

Upvotes: 6

Related Questions