# migrate AUTO_INCREMENT to TiDB using AUTO_RANDOM

## Summary

------------------------------
`AUTO_RANDOM_BASE` ([CREATE TABLE](https://docs.pingcap.com/tidb/stable/sql-statement-create-table)) sets the incremental part of the AutoRandom column to a specified value, thus it guarantees the all values allocated will be larger than that. 

Theoretically, you can write to an AUTO\_RANDOM column that contains values from an originally AUTO\_INCREMENT column. As long as the existing values are not too large (so that they occupy the bits used for sharding), the rebase of auto\_random happens automatically to make sure that overlap doesn't occur for implicit allocations.

There’s one counter across all shards, and that counter should only go up, so if you set a high value, it will re-base the auto-random counter, which you can see in the output of `show create table.`

```{.sql .copyable .custom-prompt .prompt-MySQL}
create table t1 (id bigint unsigned not null auto\_random primary key);  
```

```output
Query OK, 0 rows affected, 1 warning (0.00 sec)  
```

```{.sql .copyable .custom-prompt .prompt-MySQL}
show warnings;  
```

```output
+-------+------+---------------------------------------------------------+  
| Level | Code | Message                                                 |  
+-------+------+---------------------------------------------------------+  
| Note  | 1105 | Available implicit allocation times: 576460752303423487 |  
+-------+------+---------------------------------------------------------+  
1 row in set (0.00 sec)  
```

```{.sql .copyable .custom-prompt .prompt-MySQL}
select log(576460752303423487)/log(2); 
```

```output
+--------------------------------+  
| log(576460752303423487)/log(2) |  
+--------------------------------+  
|              59.00000000000001 |  
+--------------------------------+  
1 row in set (0.00 sec)  
```

```{.sql .copyable .custom-prompt .prompt-MySQL}
insert into t1 (id) values (null);  
```

```output
Query OK, 1 row affected (0.00 sec)  
```

```{.sql .copyable .custom-prompt .prompt-MySQL}
show create table t1\\G  
```

```output
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
       Table: t1  
Create Table: CREATE TABLE \`t1\` (  
  \`id\` bigint(20) unsigned NOT NULL /\*T!\[auto\_rand\] AUTO\_RANDOM(5) \*/,  
  PRIMARY KEY (\`id\`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_bin   
  /\*T!\[auto\_rand\_base\] AUTO\_RANDOM\_BASE=30001 \*/  
1 row in set (0.00 sec)  
```

```{.sql .copyable .custom-prompt .prompt-MySQL}
insert into t1 (id) values (2<<32);  
```

```output
Query OK, 1 row affected (0.00 sec)  
```

```{.sql .copyable .custom-prompt .prompt-MySQL}
show create table t1\\G  
```

```output
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
       Table: t1  
Create Table: CREATE TABLE \`t1\` (  
  \`id\` bigint(20) unsigned NOT NULL /\*T!\[auto\_rand\] AUTO\_RANDOM(5) \*/,  
  PRIMARY KEY (\`id\`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_bin   
  /\*T!\[auto\_rand\_base\] AUTO\_RANDOM\_BASE=8589964593 \*/  
1 row in set (0.00 sec)mysql> insert into t1 (id) values ((2<<59)-1);  
Query OK, 1 row affected (0.00 sec)  
```

```{.sql .copyable .custom-prompt .prompt-MySQL}
show create table t1\\G
```

```output
\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 1. row \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
       Table: t1  
Create Table: CREATE TABLE \`t1\` (  
  \`id\` bigint(20) unsigned NOT NULL /\*T!\[auto\_rand\] AUTO\_RANDOM(5) \*/,  
  PRIMARY KEY (\`id\`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4\_bin  
  /\*T!\[auto\_rand\_base\] AUTO\_RANDOM\_BASE=576460752303453488 \*/  
1 row in set (0.00 sec)  
```

```{.sql .copyable .custom-prompt .prompt-MySQL}
insert into t1 (id) values (null);  
```

```output
ERROR 8059 (HY000): Failed to read auto-random value from storage engine
```