Mysql – Drop auto increment hack w/o alter table

MySQLschema

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?

Best 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 !!!

Related Question