Resetting AUTO_INCREMENT on myISAM without rebuilding the table

Posted by Artem on Stack Overflow See other posts from Stack Overflow or by Artem
Published on 2010-04-21T19:38:45Z Indexed on 2010/04/21 19:43 UTC
Read the original article Hit count: 250

Filed under:

alter-table

|

auto-increment

|

mysql

|

myisam

Please help I am in major trouble with our production database. I had accidentally inserted a key with a very large value into an autoincrement column, and now I can't seem to change this value without a huge rebuild time.

"ALTER TABLE tracks_copy AUTO_INCREMENT = 661482981"

Is super-slow.

How can I fix this in production? I can't get this to work either (has no effect): myisamchk tracks.MYI --set-auto-increment=661482982

Any ideas?

Basically, no matter what I do I get an overflow: SHOW CREATE TABLE tracks CREATE TABLE tracks ( ... ) ENGINE=MYISAM AUTO_INCREMENT=2147483648 DEFAULT CHARSET=latin1

© Stack Overflow or respective owner

Related posts about alter-table

Convert VARCHAR() columns to NVARCHAR()

as seen on Geeks with Blogs - Search for 'Geeks with Blogs'
We recently underwent an upgrade that required us to change our database columns from varchar to NVarchar, to support unicode characters. Digging through the internet, I found a base script which I modified to handle reserved word table names, and maintain the NULL/NotNull constraint of the columns… >>> More
SQL Server script commands to check if object exists and drop it

as seen on Geeks with Blogs - Search for 'Geeks with Blogs'
Over the past couple years I’ve been keeping track of common SQL Server script commands that I use so I don’t have to constantly Google them. Most of them are how to check if a SQL object exists before dropping it. I thought others might find these useful to have them all in one place, so… >>> More
SQLite3, "ALTER TABLE" and durability

as seen on Stack Overflow - Search for 'Stack Overflow'
I'd like to run some ALTER TABLE statements on a sqlite3 database. What happens if the user kills the process or the power is cut while the ALTER TABLE is running? Will the database be left in a corrupt intermediate state? >>> More
query " ALTER TABLE test_posts ADD sticky boolean NOT NULL default = false" = error

as seen on Stack Overflow - Search for 'Stack Overflow'
Whats wrong with my query? ALTER TABLE test_posts ADD sticky boolean NOT NULL default = false #1064 - 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 '=false' at line 2 >>> More
'ALTER table' for all tables in a database

as seen on Server Fault - Search for 'Server Fault'
How can I run the following for every table in a database: ALTER table [table_name] type=innodb; I don't want to have to manually run it for each table, but rather run it for all tables in a database. As an aside: If your curious as to why I am running this: http://bugs.mysql.com/bug.php?id=1341… >>> More

Related posts about auto-increment

Doctrine does not export relation properly

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi, I've got a MySQL 5.1.41 database which i'm trying to fill with doctrine, but doctrine does not insert the relations correctly. My YAML is: Locatie: connection: doctrine tableName: locatie columns: loc_id: type: integer(4) fixed: false unsigned: false primary: true … >>> More
How To Disable Subsonic's Primary Key Autoincrement?

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi everybody, I'm using Subsonic (simplerepository) and SQLite, and I have a class with an Int64 property marked as [SubSonicPrimaryKey]: [SubSonicPrimaryKey] public Int64 MyID; which is transformed into: [MyID] integer NOT NULL PRIMARY KEY AUTOINCREMENT Is it possible to disable the AUTOINCREMENT… >>> More
Linq To Objects Auto Increment Number

as seen on Stack Overflow - Search for 'Stack Overflow'
This feels like a completely basic question, but, for the life of me, I can't seem to work out an elegant solution. Basically, I am doing a Linq Query creating a new object from the query. In the new object, I want to generate a auto-incremented number to allow me to keep a selection order for… >>> More
Why can't Doctrine retrieve my model data?

as seen on Stack Overflow - Search for 'Stack Overflow'
So, I'm trying to use Doctrine to retrieve some data. I have some basic code like this: $conn = Doctrine_Manager::connection(CONNECTION_STRING); $site = Doctrine_Core::getTable('Site')->find('00024'); echo $site->SiteName; However, this keeps throwing a SQL error that 'column siteid does… >>> More
Symfony: embedRelation() controlling options for nesting multiple levels of relations

as seen on Stack Overflow - Search for 'Stack Overflow'
Hey all, I'm trying to set some conditional statements for nested embedRelation() instances, and can't find a way to get any kind of option through to the second embedRelation. I've got a "Measure-Page-Question" table relationship, and I'd like to be able to choose whether or not to display the… >>> More