SQLite Migrate Exception: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY

[larryli]

What steps will reproduce the problem?

1. Use 'dsn' => 'sqlite:' . __DIR__ . '/../runtime/database.sqlite', in config/db.php on yii2-app-basic.

2. Create a migration as:

   use yii\db\Migration;
   
   class m160509_093358_foobar extends Migration
   {
   public function up()
   {
      $this->createTable('foobar', [
          'id' => $this->bigPrimaryKey(),
      ]);
   }
   
   public function down()
   {
      $this->dropTable('foobar');
   }
   }

3. Run ./yii migrate

   What is the expected result?

*** applying m160509_093358_foobar
    > create table foobar ...Exception 'yii\db\Exception' with message 'SQLSTATE[HY000]: General error: 1 AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
Failed to prepare SQL: CREATE TABLE `foobar` (
        `id` bigint PRIMARY KEY AUTOINCREMENT NOT NULL
)'

in C:\Users\larry\foobar\vendor\yiisoft\yii2\db\Command.php:230

Error Info:
Array
(
    [0] => HY000
    [1] => 1
    [2] => AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
)

What do you get instead?

Revert af75262541e83f353a36baf888b3392c9d34f201 changes

Additional info

Q	A
Yii version	2.0.8
PHP version	7.0.6
Operating system	Windows 10

[samdark]

@larryli how would you solve this issue?

[larryli]

@samdark Revert af75262

[cebe]

need to add a comment that this is intentionally the same as normal integer. also need a test that breaks in case someone will change it,

[SamMousa]

@cebe Doesn't it make more sense to throw an exception?

If I make a migration I specify big integer for a reason; I wouldn't be too happy if you silently went ahead and created a normal integer. That could cause me to get into trouble later when I use the application.

I think failing early is better; if needed the implementor could then fallback to normal int primary key by catching the exception.

[rob006]

If I make a migration I specify big integer for a reason; I wouldn't be too happy if you silently went ahead and created a normal integer. That could cause me to get into trouble later when I use the application.

What kind of trouble? SQLite INTEGER can store signed 8-bytes integer, same as MySQL BIGINT.

[SamMousa]

Ah, my bad. I thought it was about implicitly using another type that has a smaller domain. :8ball:

[dynasource]

My 2 suggestions:

• Mysqlite doesnt allow autoincrements on BIGINT, so the 'autoincrement' should be removed out of the statement.
• Force bigPrimarykey to be an INT so that people using Mysqlite for testing wont have issues on autoincrements. For those using Mysqlite in production and need BIG INT, let them specify this requirement themselves (as the usecase is much more rare )

[samdark]

https://github.com/yiisoft/yii2/pull/11653