28

Trying to change data column type to tinyInteger in a Laravel 5.2 migration:

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class AlterTableNameTableChangeNotificationSentTinyint extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('table_name', function ($table) {
            $table->tinyInteger('column_name')->default(0)->change();
        });    
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        //
    }
}

I'm getting an error:

Doctrine\DBAL\DBALException]                                                                                                                                                              
  Unknown column type "tinyinteger" requested. Any Doctrine type that you use has to be registered with \Doctrine\DBAL\Types\Type::addType().         You can get a list of all the known types wit  
  h \Doctrine\DBAL\Types\Type::getTypesMap(). If this error occurs during database introspection then you might have forgot to register all database types for a Doctrine Type. Use Abstrac  
  tPlatform#registerDoctrineTypeMapping() or have your custom types     implement Type#getMappedDatabaseTypes(). If the type name is empty you might have a problem with the cache or forgot so  
  me mapping information.

Am I doing something wrong?

Improve this question
2
  • 1
    Did you include doctrine/dbal in your composer.json? Commented Aug 9, 2017 at 13:08
  • 1
    @Jerodev yes, "doctrine/dbal": "^2.5" in composer.json Commented Aug 10, 2017 at 17:57

9 Answers 9

Reset to default
34

Indeed Doctrine Dbal does not support tinyint you can read from their doc here

Unfortunately as well, laravel stated that tinyint cannot be changed. Check here

I need someone to prove this as wrong, because I had to use smallInteger because of this issue for one of my projects. I am thinking maybe boolean() might be the solution. I have not tried this though.

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

20

i hope that this will solve your issue 

DB::statement("ALTER TABLE table_name CHANGE COLUMN column_name column_name TINYINT UNSIGNED NOT NULL");
Improve this answer

2 Comments

This might be the accepted answer! The boolean solution is not proper when you want, for example, to use numbers from 0 to 9 (or up to 255).
I agree with @LucianDex while Doctrine Dbal still does not support tinyint.
4

Do This

Change tinyInteger to smallInteger

use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Types\SmallIntType;


if (!Type::hasType('integer')) {
     Type::addType('integer', SmallIntType::class);
  }
Improve this answer

Comments

1

Can you use boolean?

or

$table->smallInteger('column_name')->tinyInteger('column_name')->unsigned()->change();
Improve this answer

4 Comments

The later does not work. Will not convert int to tinyint column type (laravel 8).
@LucianDex Laravel 8 != Laravel 5.2
you're right! That's the reason that I mentioned Laravel version at the end.
@LucianDex that is not the version in the original post. might need to start a new question or update the answer to include laravel 8
1

This should work for you, Just change from tinyInteger to smallInteger as below;

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class AlterTableNameTableChangeNotificationSentTinyint extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::table('table_name', function ($table) {
            $table->smallInteger('column_name')->default(0)->change();
        });    
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        //
    }
}
Improve this answer

Comments

0

If you are trying to convert a non-numeric column to an int column, you will get this error. The values cannot be converted.

You might run into this when converting an old string value to an id reference to a parent table.

Instead of trying to change the existing column, create a new column and delete the old:

// Add new int column
Schema::table('children', function (Blueprint $table) {
    $table->unsignedTinyInteger('parent_id')->after('parent_slug');
});

// Convert old values to new
// Only runs on environments that already have data in db, by virtue of pulling all records from the parents table
foreach (\App\Parents::all() as $parent) {
    \App\Child::where('parent_slug', $parent->slug)->each(function ($child) use ($parent) {
        $child->update([ 'parent_id' => $parent->id ]);
    });
}

// Drop old string column
Schema::table('children', function (Blueprint $table) {
    $table->dropColumn('parent_slug');
});
Improve this answer

Comments

0

!!! This solution is only for empty tables. Not if already populated.

Just drop and recreate the column with same name.

    public function up()
    {
        // Drop and recreate because laravel don't allow to change to the tinyInteger type 
        Schema::table('your_table_name', function (Blueprint $table) {
            $table->dropColumn(['rating']);
        });

        Schema::table('your_table_name', function (Blueprint $table) {
            $table->tinyInteger('rating')->nullable()->after('some_column_name');
        });
    }
Improve this answer

Comments

-2

According to this https://github.com/laravel/framework/issues/8840 "BOOL" and "BOOLEAN" are both synonymous to "TINYINT" therefor just use "boolean" method instead of "tinyInteger", its the same in Laravel.

Improve this answer

6 Comments

If we use boolean, can we insert other numbers, than 0 and 1? Tinyint has a range of (unsigned) 0 - 255 in number value.
In PostgreSQL it will have a different approch them this I explain and probably it will not work, in this scenários the better is to use DB::statement. This I say will work only for MySQL and AFAIR SQLite will convert tinyint and boolean to integer.
Yup. What happens is that Eloquent when executing the migration will result in a tinyint column in the repository (ie, the database) and as the model has no direct relationship with the migration we can then use the integer type in the entity. The problem is that Laravel will not limit the integer in the model so when trying to save a value like 200 (considering a signed number) in the database it is possible to have some kind of error (depending on the database server error configuration), therefore I recommend making specific getters and setters whenever using tinyint with Eloquent.
PS: Just for illustration, in mysql database to language boolean conversion columns containing any value other than null and 0 is considered true, that is, if the column has a value of 10 and the attribute in the entity is of boolean type, the value 10 will be converted to true.
When I gave the original reply I was considering create tinyint on older Laravel without the need to building no class/method to it, but considering a column change (as first asked) another important consideration is that vals diff of null 0 and 1 in a col change may be lost (it may depend on db config) therefore to guarantee no data lost the better is to create another column with a temporary name, copy values to this new column, drop the original column and change the newer column name to the desired one doing those operations in different Schema::table blocks. Hope to have helped.
|
-6

try this Schema::table('table_name', function (Blueprint $table) { $table->tinyInteger('column_name')->default(0)->change();

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.