Description:
Create database db1.
Create tables t1 and t2 in db1.
Insert some data contents in table t1
Set auto increment option to specific value using myisamchk for table t1 and t2.
Insert some contents in table t1 and t2.
Verify that table values begins from the number specified by auto increment value.
Table t1 numbering will fail whereas t2 will have numbers beginning from what is specified in auto inc value.
create database db1;
use db1;
create table t1(id int auto_increment primary key, a char(20));
create table t2(id int auto_increment primary key, a char(20));
insert into t1(a) values('pp'),('er');
select * from t1;
select * from t2;
insert into t1(a) values('l');
insert into t2(a) values('l');
select * from t1;
select * from t2;
How to repeat:
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> use db1;
Database changed
mysql> create table t1(id int auto_increment primary key, a char(20));
Query OK, 0 rows affected (0.01 sec)
mysql> create table t2(id int auto_increment primary key, a char(20));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t1(a) values('pp'),('er');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from t1;
+----+------+
| id | a |
+----+------+
| 1 | pp |
| 2 | er |
+----+------+
2 rows in set (0.00 sec)
Note: Go to the path where myisamchk utility programs is located and perform the following,
./myisamchk --set-auto-increment=500 /export/home/tmp/review/mysql-6.0-backup/mysql-test/var/mysqld.1/data/db1/t1
Updating MyISAM file: /export/home/tmp/review/mysql-6.0-backup/mysql-test/var/mysqld.1/data/db1/t1
./myisamchk --set-auto-increment=500 /export/home/tmp/review/mysql-6.0-backup/mysql-test/var/mysqld.1/data/db1/t2
Updating MyISAM file: /export/home/tmp/review/mysql-6.0-backup/mysql-test/var/mysqld.1/data/db1/t2
mysql> use db1;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into t1(a) values('l');
Query OK, 1 row affected (0.00 sec)
mysql> insert into t2(a) values('l');
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+----+------+
| id | a |
+----+------+
| 1 | pp |
| 2 | er |
| 3 | l |
+----+------+
3 rows in set (0.00 sec)
mysql> select * from t2;
+-----+------+
| id | a |
+-----+------+
| 501 | l |
+-----+------+
1 row in set (0.00 sec)
From the above table we can notice that, auto inc option works only for records that are first inserted in the table(table t2).
The reference manuals says "Force AUTO_INCREMENT numbering for new records to start at the given value" . This statement is ambiguous.
Myisamchk auto increment option should work even when there are some already existing values in tables.
Description: Create database db1. Create tables t1 and t2 in db1. Insert some data contents in table t1 Set auto increment option to specific value using myisamchk for table t1 and t2. Insert some contents in table t1 and t2. Verify that table values begins from the number specified by auto increment value. Table t1 numbering will fail whereas t2 will have numbers beginning from what is specified in auto inc value. create database db1; use db1; create table t1(id int auto_increment primary key, a char(20)); create table t2(id int auto_increment primary key, a char(20)); insert into t1(a) values('pp'),('er'); select * from t1; select * from t2; insert into t1(a) values('l'); insert into t2(a) values('l'); select * from t1; select * from t2; How to repeat: mysql> create database db1; Query OK, 1 row affected (0.00 sec) mysql> use db1; Database changed mysql> create table t1(id int auto_increment primary key, a char(20)); Query OK, 0 rows affected (0.01 sec) mysql> create table t2(id int auto_increment primary key, a char(20)); Query OK, 0 rows affected (0.00 sec) mysql> insert into t1(a) values('pp'),('er'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t1; +----+------+ | id | a | +----+------+ | 1 | pp | | 2 | er | +----+------+ 2 rows in set (0.00 sec) Note: Go to the path where myisamchk utility programs is located and perform the following, ./myisamchk --set-auto-increment=500 /export/home/tmp/review/mysql-6.0-backup/mysql-test/var/mysqld.1/data/db1/t1 Updating MyISAM file: /export/home/tmp/review/mysql-6.0-backup/mysql-test/var/mysqld.1/data/db1/t1 ./myisamchk --set-auto-increment=500 /export/home/tmp/review/mysql-6.0-backup/mysql-test/var/mysqld.1/data/db1/t2 Updating MyISAM file: /export/home/tmp/review/mysql-6.0-backup/mysql-test/var/mysqld.1/data/db1/t2 mysql> use db1; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> insert into t1(a) values('l'); Query OK, 1 row affected (0.00 sec) mysql> insert into t2(a) values('l'); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +----+------+ | id | a | +----+------+ | 1 | pp | | 2 | er | | 3 | l | +----+------+ 3 rows in set (0.00 sec) mysql> select * from t2; +-----+------+ | id | a | +-----+------+ | 501 | l | +-----+------+ 1 row in set (0.00 sec) From the above table we can notice that, auto inc option works only for records that are first inserted in the table(table t2). The reference manuals says "Force AUTO_INCREMENT numbering for new records to start at the given value" . This statement is ambiguous. Myisamchk auto increment option should work even when there are some already existing values in tables.