Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I was looking around for a quick way to remove an auto increment from the definition of a primary key. As best I can tell the only way to do it is w/ an alter table or dumping all the data into a new schema sans auto_increment.

Just for fun I tried the following to see if it would work.

  1. Create table test1 (innodb) w/ an auto increment PK
  2. Insert a couple rows w/o specifying PK values, letting auto inc do its job
  3. Create table test2 like test1;
  4. Alter table test2 modify pk_col int(10) unsigned not null; -- no auto_inc
  5. Shutdown mysql
  6. backup test1.frm; cp test2.frm test1.frm
  7. Restart mysql
  8. select * shows all rows as expected, w/ previously auto_inc created PKs 1,2 and 3
  9. Insert a row w/o a PK specified, gets created w/ PK value as 0 (the default value).
  10. Insert a row specifying PK of 5
  11. Shutdown mysql
  12. restore original auto_increment test1.frm
  13. Restart; show create table lists auto_inc
  14. Test insert a row w/o specifying PK, generated auto_inc value of 6 (+1 the highest value even though the last one created by virtue of auto_inc was 3

Seems like that works!

For curiosity I ran both .frms through od and diffed that output. All I got

9c9
< 0010020 000001 015000 002000 000400 003400 001400 000000 001400
---
> 0010020 000001 015000 002100 000400 003400 001400 000000 001400
45c45
< 0020760 002000 005003 000012 000001 015000 007400 000000 020403
---
> 0020760 002000 005003 000012 000001 015000 000100 000000 020403

Thoughts, comments?

share|improve this question
    
I have no idea whether it would work with innodb as well, but at least with MyISAM you can do it without stopping and restarting MySQL. See this question: serverfault.com/questions/340823/… –  Jannes Dec 17 '11 at 16:50
    
The trick works perfectly with innodb! Thanks for that. According to the first answer: I think myisam could be the problem here! And: why and when do you do the repair command? You must switch off mysql completely (of course)!!! –  user34131 Feb 11 at 21:10
add comment

1 Answer

I tried something similar just now

Here is MySQL for My PC

mysql> select * from information_schema.global_variables where variable_name='datadir' or variable_name like 'versio%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION                 | 5.5.12-log                   |
| VERSION_COMPILE_MACHINE | x86                          |
| DATADIR                 | C:\MySQL_5.5.12\data\        |
| VERSION_COMPILE_OS      | Win64                        |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)

I will run this using MyISAM

  • Step 01) create a table called 'rolando'
  • Step 02) insert 'dominique' and 'diamond'
  • Step 03) copy the table structure to 'pamela'
  • Step 04) alter 'pamela' to not have auto_increment
  • Step 05) In DOS, copy rolando.MYD to pamela.MYD
  • Step 06) run REPAIR TABLE pamela; (Rebuild pamela.MYI)
  • Step 07) run SELECT COUNT(1) FROM pamela;
  • Step 08) run SHOW CREATE TABLE pamela\G
  • Step 09) run SELECT * FROM pamela;
  • Step 10) insert 'carlik' into pamela
  • Step 11) run SELECT * FROM pamela;

Let's see if these steps are kosher.

Here are Steps 1-4

mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists pamela;
Query OK, 0 rows affected (0.00 sec)

mysql> create table rolando
    -> (
    ->     name varchar(20),
    ->     id int not null auto_increment,
    ->     primary key (id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into rolando (name) values ('dominique'),('diamond');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from rolando;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
+-----------+----+
2 rows in set (0.00 sec)

mysql> create table pamela like rolando;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table rolando\G
*************************** 1. row ***************************
       Table: rolando
Create Table: CREATE TABLE `rolando` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql> alter table pamela modify id int(11) unsigned not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql>

Here is Step 6

C:\>copy C:\MySQL_5.5.12\data\test\rolando.MYD C:\MySQL_5.5.12\data\test\pamela.MYD
        1 file(s) copied.

C:\>

Here are the rest of the Steps starting at Step 7

mysql> repair table pamela;
+-------------+--------+----------+------------------------------------+
| Table       | Op     | Msg_type | Msg_text                           |
+-------------+--------+----------+------------------------------------+
| test.pamela | repair | warning  | Number of rows changed from 0 to 2 |
| test.pamela | repair | status   | OK                                 |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.03 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> insert into pamela (name,id) values ('carlik',3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from pamela;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
| carlik    |  3 |
+-----------+----+
3 rows in set (0.00 sec)

mysql>

Dangerous game, isn't it ???

Guess what? Stuff like this is actually published in "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:

The technique we are about to demonstrate is unsupported, undocumented, and may not work. Use it at your risk. We advise you to back up you data first!

I also had an earlier post when someone ask a similar question : Can I rename the values in a MySQL ENUM column in one query?

You got guts, @atxdba !!!

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.