Schema Builder: Error when changing a column to a tinyInteger

[robjbrain]

When modifying a column to be a tinyint like so (previously a VARCHAR(10) )

Schema::table('graphic_megapacks', function($table) {
        $table->tinyInteger('game_id')->change();
    });

The following error is given:

DBALException in DBALException.php line 228:
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 with \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 AbstractPlatform#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 some mapping information.

"getTypesMap()" returns the following:

array:19 [▼
"array" => "Doctrine\DBAL\Types\ArrayType"
"simple_array" => "Doctrine\DBAL\Types\SimpleArrayType"
"json_array" => "Doctrine\DBAL\Types\JsonArrayType"
"object" => "Doctrine\DBAL\Types\ObjectType"
"boolean" => "Doctrine\DBAL\Types\BooleanType"
"integer" => "Doctrine\DBAL\Types\IntegerType"
"smallint" => "Doctrine\DBAL\Types\SmallIntType"
"bigint" => "Doctrine\DBAL\Types\BigIntType"
"string" => "Doctrine\DBAL\Types\StringType"
"text" => "Doctrine\DBAL\Types\TextType"
"datetime" => "Doctrine\DBAL\Types\DateTimeType"
"datetimetz" => "Doctrine\DBAL\Types\DateTimeTzType"
"date" => "Doctrine\DBAL\Types\DateType"
"time" => "Doctrine\DBAL\Types\TimeType"
"decimal" => "Doctrine\DBAL\Types\DecimalType"
"float" => "Doctrine\DBAL\Types\FloatType"
"binary" => "Doctrine\DBAL\Types\BinaryType"
"blob" => "Doctrine\DBAL\Types\BlobType"
"guid" => "Doctrine\DBAL\Types\GuidType"
]

Creating a tinyInteger column works fine, it's only when modifying a column that the error occurs

//This works fine
Schema::table('graphic_megapacks', function($table) {
        $table->tinyInteger('test');
    });

[christophersk]

As a workaround you may be able to use boolean, which at least in MySQL seems to be functionally identical to tinyint.

see: http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

BOOL, BOOLEAN
These types are synonyms for TINYINT(1).

[grischka]

Have the same issue here (w. lumen). Thanks for the fix! tinyint is still makes sense in a few cases though...

[bioteck]

I have the same problem with tinyInteger

[EventideGlow]

+1. I ran into this today while writing a migration to convert a boolean column into a tinyint.

@GrahamCampbell, why was this issue closed?

[leek]

@GrahamCampbell I'd like to know why this issue was closed as well. Simply using boolean() instead does not work if you want the TINYINT to be UNSIGNED.

[osteel]

+1

[m1r0]

+1

Tried to convert FLOAT to unsigned TINYINT.

My solution was to convert it to SMALLINT first:

$table->smallInteger('tax')->tinyInteger('tax')->unsigned()->change();

[rufhausen]

I'm having the same issue with mediumInteger().

[silentred]

+1
boolean('name') did the trick.

[patrickarnold79]

+1 having the same issue. Why has the issues being closed?

[ryanwohara]

@GrahamCampbell this is a reproducible bug. I hit it with mediumInteger(). Can you clarify why this issue was closed? Are you seeing something we are not?

Edit: The workaround did not work for me. I did not get an error, but after the migrations run the database does not show a mediumInt.

[leek]

@taylorotwell We have a legitimate bug here; but I believe the worst problem is the fact that it was closed by @GrahamCampbell without any comment whatsoever.

[LMCom]

Similar problem here. I tried to change an existing tinyInteger to unsigned(). So the boolean workaround is not applicable in my case.

[davidsivocha]

Running into same issue when we are trying to change tiny int column to not allow nulls using this statement

$table->tinyInteger('bedrooms')->nullable(false)->change();

[anakadote]

+1

[sergey-rud]

+1

Laravel 5.2.19
My column NOT NULL, now I want to make it nullable:

$table->unsignedTinyInteger('application_status')->nullable()->change();

Doesn't work, throws 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 with \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 AbstractPlatform#registerDoctrineTypeMapping() or have your custom types implement Type#getMappedDatabaseTypes(). If the type name is empty you might h
ave a problem with the cache or forgot some mapping information.

[sergey-rud]

Solution:
public function up() { DB::statement('ALTER TABLE application_activities CHANGE application_status application_status TINYINT(3) UNSIGNED NULL DEFAULT NULL;'); }

[davidsivocha]

@sergey-rud While that does work, it's not really a solution to the problem at hand. It's just a workaround.

[intrepidws]

@GrahamCampbell Please comment on why this was closed? At least give us an idea here so people can figure out how to fix the problem otherwise. Closing an issue with this many active people still commenting on it, without at least an explanation, isn't helpful at all.

[GrahamCampbell]

Please comment on why this was closed?

It is not a laravel bug. Please take it up with doctrine.

[intrepidws]

See that's helpful. This could've been done three months ago.

[GrahamCampbell]

You didn't need me to tell you that.

[intrepidws]

Was just coming here to let the thread know that I've opened an issue in DBAL - but it turns out GitHub is wicked smaht.

[GrahamCampbell]

Going to re-open this here to prevent duplicate issues coming up (if possible).

[EliasZ]

Take a look at this comprehensive list of types available in doctrine/dbal: Types reference.

Tinyint is only mentioned there as an implementation of the boolean type in the mapping matrix. Quickly skimming through the source code doesn't show doctrine/dbal working with tinyint other than as tinyint(1) for a boolean implementation.

It's hard to read through their issues because they are being imported via/from JIRA, but this looks closest to describing the issue at hand but has been closed by a bot for whatever reason:

doctrine/dbal#2011 - notably the last comment

Ideally, a programmer would go I am using the MySQL database engine, no boolean, let me use a TINY INT. So instead of making the programmer think you dumbed down a field that can store 255unsigned valyes into one that stores TRUE OR FALSE.

So summarized: doctrine/dbal doesn't seem to have support for tinyint other than tinyint(1).

I do think that currently this is a flaw in the Laravel Schema component as it erroneously assumes that doctrine/dbal can create correctly sized tinyint fields (and other *int fields too, according to comments here). So options are replacing doctrine/dbal with a proper dbal, waiting for doctrine/dbal to be fixed, band-aid fixing it by disabling tinyInteger() methods and the like or add ad hoc fixes to the schema component generating the correct database platform queries.

^2c

[jlsjonas]

While I agree that this is a Doctrine issue/inconsistency; they just confirmed that they won't be fixing it.

Could this please be reconsidered to be added to Laravel to avoid a mess in migrations just to set an initial (non-default) value to new, non-nullable columns. (docs & implementation example)

[driesvints]

A pr was merged that would allow you to register a custom DBAL type if you want. This is probably the best alternative we can offer. So gonna close this.

#8840

[jlsjonas]

Just want to clarify that #28214 actually already implements tinyInteger as a custom class... for MySQL (notably SQLite & SQL Server are still missing at this point)

[driesvints]

@jlsjonas thanks for clarifying! :)

[robjbrain]

Wow Google a problem and its my own issue from 4 years ago!

The above conversation is all very confusing and at times contradictory, so here's documentation on what to actually do...

The TinyInteger class is not bundled and you must write your own.

Here is an example from a test https://github.com/laravel/framework/blob/841a28067b03979603e41dd80729cb8581a91e95/tests/Integration/Database/Fixtures/TinyInteger.php

I placed it in App\Doctrine\TinyInteger.php but you can place it anywhere.

namespace App\Doctrine;

use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Platforms\AbstractPlatform;

class TinyInteger extends Type
{
    /**
     * The name of the custom type.
     *
     * @var string
     */
    const NAME = 'tinyinteger';

    /**
     * Gets the SQL declaration snippet for a field of this type.
     *
     * @param  array  $fieldDeclaration
     * @param  \Doctrine\DBAL\Platforms\AbstractPlatform  $platform
     * @return string
     */
    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        return 'TINYINT';
    }

    /**
     * The name of the custom type.
     *
     * @return string
     */
    public function getName()
    {
        return self::NAME;
    }
}

Then use this is in your migration file.

use App\Doctrine\TinyInteger;
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class NullifyColumns extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::registerCustomDoctrineType(TinyInteger::class, TinyInteger::NAME, 'TINYINT');

        Schema::table('nation_tax_rules', function(Blueprint $table) {
            $table->unsignedTinyInteger('type_id')->nullable()->change();
        });
    }

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

@JacksonIV what is the thinking behind not making the TinyInteger class available within the Laravel Framework itself?

[agm1984]

Confirmed the above solution works great.

I come from JavaScript land, so PHP isn't my strong suit. For others like me:

1. In Laravel 5.8, the folder is app, so you can place the file in app\Doctrine\TinyInteger.php, and you will have to create the Doctrine folder because it likely won't exist.

2. Don't forget to put <? php at the top of that TinyInteger.php file or you won't have any syntax highlighting in the file, and you will also experience some haywire behaviour when you try to run the migration (lol)

3. Make sure you do composer require doctrine/dbal to install DBAL before attempting to run the migration.

Besides those points, you should be able to copy/paste the TinyInteger.php code and use it as is, including the import declarations. I am currently uncertain how those two extra methods are being used: getSQLDeclaration, and getName, but I assume DBAL is using them. Otherwise they are cruft in the context of this example code.

[amansinghvaid]

As per the Laravel docs only the following column types can be "changed": bigInteger, binary, boolean, date, dateTime, dateTimeTz, decimal, integer, json, longText, mediumText, smallInteger, string, text, time, unsignedBigInteger, unsignedInteger and unsignedSmallInteger.

https://laravel.com/docs/5.8/migrations

[MinaZakaria]

@robjbrain @agm1984 following this steps works in case of
$table->tinyInteger('type_id')->nullable()->change();
but if I'm trying to change unsignedTinyInteger It says
Cannot change column 'type_id': used in a foreign key constraint 'customer_addresses_type_id_foreign' (SQL: ALTER TABLE customer_addresses CHANGE type_id type_id TINYINTNOT NULL)
He removes UNSIGNED from the SQL statement So He feels like I'm trying to change column type which not true
any help !

[fannyfan414]

@robjbrain @agm1984 following this steps works in case of
$table->tinyInteger('type_id')->nullable()->change();
but if I'm trying to change unsignedTinyInteger It says
Cannot change column 'type_id': used in a foreign key constraint 'customer_addresses_type_id_foreign' (SQL: ALTER TABLE customer_addresses CHANGE type_id type_id TINYINTNOT NULL)
He removes UNSIGNED from the SQL statement So He feels like I'm trying to change column type which not true
any help !

To solve problem with foreign key you need to create UnsignedTinyInteger class

class UnsignedTinyInteger extends Type
{

    /**
     * The name of the custom type.
     *
     * @var string
     */
    const NAME = 'tinyinteger';

    /**
     * Gets the SQL declaration snippet for a field of this type.
     *
     * @param mixed[] $fieldDeclaration The field declaration.
     * @param AbstractPlatform $platform The currently used database platform.
     *
     * @return string
     */
    public function getSQLDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
    {
        return 'TINYINT UNSIGNED';
    }

    /**
     * Gets the name of this type.
     *
     * @return string
     *
     * @todo Needed?
     */
    public function getName()
    {
        return self::NAME;
    }
}

And add it to your migration

Schema::registerCustomDoctrineType(
            UnsignedTinyInteger::class,
            UnsignedTinyInteger::NAME,
            'TINYINT UNSIGNED'
);

Schema::table('chat_message_triggers', function (Blueprint $table) {
           $table->unsignedTinyInteger('type_id')->nullable()->change();
});

[violetice]

@sergey-rud Comment Link

Solution:
public function up() { DB::statement('ALTER TABLE application_activities CHANGE application_status application_status TINYINT(3) UNSIGNED NULL DEFAULT NULL;'); }

public function up()
{
    $database = \Illuminate\Support\Env::get('DB_DATABASE');
    DB::statement("ALTER TABLE `$database`.`game_prizes` ADD COLUMN `status` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态' AFTER `remark`;");
}

public function down()
{
    Schema::table('game_prizes', function (Blueprint $table) {
        $table->dropColumn('status');
    });
}

Perfect. It hasn't been fixed for years.This is the best way.

[beatriznbarroso]

Hey guys, I'm having the exact same problem, when trying to convert a field from string to json . Is anyone having a similar problem?

[koushik355]

After so much, still, it didn't work. So, I used raw query.

Schema::table('user_preferences', function (Blueprint $table) {
            /*$table->mediumInteger('lower_height')->nullable()->change();
            $table->mediumInteger('upper_height')->nullable()->change();
            $table->smallInteger('lower_age')->nullable()->change();
            $table->smallInteger('upper_age')->nullable()->change();*/
        });
```

        DB::statement("
    alter table user_preferences
    modify column lower_age smallint null,
    modify column upper_age smallint null,
    modify column upper_height mediumint null,
    modify column lower_height mediumint null;
        ");`

[dakujem]

So after 7 years this still hasn't been fixed? I've just run into the issue.

When I add both TINYINT and TINYINT UNSIGNED custom mapping, only the first one is ever used.

[dmitry-kulikov]

As others, I think it is Laravel issue. DBAL does not want to support TINYINT. Laravel uses DBAL but still tries to support TINYINT but this support is incomplete, this issue is the result of inconsistency.

However, I have to say that this behavior of Laravel is documented, so technically not a bug
https://laravel.com/docs/10.x/migrations#modifying-columns-on-sqlite

[dmitry-kulikov]

Secondly, Laravel provides means to solve this problem, below is complete solution for MySQL for both TINYINT and TINYINT UNSIGNED. I'm using Laravel 10 and PHP 8.1 here, probably works for other versions.

app/Doctrine/TinyInteger.php:

<?php

namespace App\Doctrine;

use Doctrine\DBAL\ParameterType;
use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Types\Type;

class TinyInteger extends Type
{
    /**
     * The name of the custom type.
     */
    public const NAME = 'tinyinteger';

    /**
     * {@inheritdoc}
     */
    public function convertToPHPValue($value, AbstractPlatform $platform)
    {
        return $value === null ? null : (int)$value;
    }

    /**
     * {@inheritdoc}
     */
    public function getSQLDeclaration(array $column, AbstractPlatform $platform): string
    {
        $unsigned = !empty($column['unsigned']) ? ' UNSIGNED' : '';
        $autoincrement = !empty($column['autoincrement']) ? ' AUTO_INCREMENT' : '';

        return 'TINYINT' . $unsigned . $autoincrement;
    }

    /**
     * {@inheritdoc}
     */
    public function getName(): string
    {
        return static::NAME;
    }

    /**
     * {@inheritdoc}
     */
    public function getBindingType(): int
    {
        return ParameterType::INTEGER;
    }
}

config/database.php:

<?php

use App\Doctrine\TinyInteger;

return [
    // ...

    'dbal' => [
        'types' => [
            TinyInteger::NAME => TinyInteger::class,
        ],
    ],

    // ...
];

That is enough, now your Laravel project knows TINYINT, no special code needed in migrations in contradistinction to other solutions above.
This approach is tested and should be good enough for single type of database. You may rewrite method getSQLDeclaration for your database of choice. If you want to support multiple types of databases, you will need to refactor getSQLDeclaration: it is better to move all real logic to separate Grammar classes (MysqlGrammar, PostgresqlGrammar, etc.) and choose appropriate one using condition $platform instanceof.

Inspired by Doctrine\DBAL\Types\SmallIntType and Laravel documentation https://laravel.com/docs/10.x/migrations#modifying-columns-on-sqlite

Hope it helps to someone.

[srmklive]

I think the fix suggested by @dmitry-kulikov should be added to the framework, if it can be done for all supported databases by Laravel.

[dakujem]

@dmitry-kulikov This might be the first example where one can use both tinyint and tinyint unsigned at the same time. I have not tested it yet, but this might be the missing piece of information.

[linaspasv]

Here is a simple example of how to add enum support when you want to perform ->change().

<?php

namespace App\Foundation\Database\DBAL;

use Doctrine\DBAL\Platforms\AbstractPlatform;
use Doctrine\DBAL\Types\Type;

class EnumType extends Type
{
    /**
     * {@inheritdoc}
     */
    public function getName(): string
    {
        return 'enum';
    }

    /**
     * {@inheritdoc}
     */
    public function getSQLDeclaration(array $column, AbstractPlatform $platform): string
    {
        $values = array_map(fn ($string) => $platform->quoteStringLiteral($string), $column['allowed']);

        return 'ENUM(' . join(', ', $values) . ')';
    }

    /**
     * {@inheritdoc}
     */
    public function convertToPHPValue($value, AbstractPlatform $platform)
    {
        return $value;
    }
}

Simply register this class in database.php like this:

    'dbal' => [
        'types' => [
            'enum' => EnumType::class,
        ],
    ]

Also, if Laravel gods will accept, I have submitted a PR (#47037) so we could easily extend / modify any existing DBAL type by registering in database.php

[hafezdivandari]

This is already fixed on Laravel 10.x #45487

If you are not utilizing SQLite, You may remove doctrine/dbal package or call Schema::useNativeSchemaOperationsIfPossible() method within the boot method of your App\Providers\AppServiceProvider class.

[linaspasv]

This is already fixed on Laravel 10.x #45487

If you are not utilizing SQLite, You may remove doctrine/dbal package or call Schema::useNativeSchemaOperationsIfPossible() method within the boot method of your App\Providers\AppServiceProvider class.

Oh wow, so we can change any of types now? This is gold but why this is not documented. I would not know if you did not tell me. :-)

[hafezdivandari]

@linaspasv Yes you can modify any type, please check PR #45487 for more detail.

It's already documented. You would need doctrine/dbal only for SQLite, but the Schema::useNativeSchemaOperationsIfPossible() is not documented.

[linaspasv]

Hm, then maybe Laravel installs doctrine/dbal by default because enum never worked for me on Laravel 10, and you PR is quite old too. I will try with Schema::useNativeSchemaOperationsIfPossible() and this of course needs to be documented too.

update - yeh, indeed, you need to have Schema::useNativeSchemaOperationsIfPossible() in your AppServiceProvider for things to work. :-)