framework: Schema Builder: Error when changing a column to a tinyInteger

+1

Tried to convert FLOAT to unsigned TINYINT.

My solution was to convert it to SMALLINT first:

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

You didn’t need me to tell you that.

It was not closed without comment. The comment is here:

this is a doctrine issue not laravel

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?

That’s not your comment. How are we supposed to know you’ve chosen that comment out of the 40 in this thread, as the reason to close? Even so, this whole thread is full of people disagreeing with that notion… can you not do what @lucasvscn said and make a Doctrine plugin?

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

It’s annoying that Laravel and Doctrine are now both saying ‘it’s not our fault, won’t fix’.

Doctrine has this page on RTD if it’s any use as a starting point? http://doctrine-orm.readthedocs.io/projects/doctrine-orm/en/latest/cookbook/mysql-enums.html

Custom types can then be registered to handle tiny-ints, medium-ints, enums & more across different DB types?

GrahamCampbell wrote:

this is a doctrine issue not laravel

ganey wrote:

It’s annoying that Laravel and Doctrine are now both saying ‘it’s not our fault, won’t fix’.

The problem is certainly solvable on the Laravel side, since Doctrine provides an api to register custom types. Laravel migrations should make use of that. I don’t see a reason, why they shouldn’t. See https://blog.intelligentbee.com/2017/06/26/add-tinyint-mysql-doctrine-symfony-2-8/.

@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 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.

@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();
});

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

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

@GrahamCampbell, why was this issue closed?

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.

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

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)

Please comment on why this was closed?

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

I agree. I think it can be solved on Laravel’s side, however this seems to have been going on a long time so for now I’ve created a simple trait that maps missing Laravel data types to the (hopefully) most appropriate SQLite types. It also handles the Cannot add a NOT NULL column with default value NULL when you try to add a non-nullable column to an existing table. Let me know if it helps and whether something like this makes sense as a PR.

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;
        ");`

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

@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.

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.

How come this is still open ??? Is there not a solution to this ?

This work Schema::table(‘graphic_megapacks’, function($table) { $table->boolean(‘game_id’)->change(); });

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).

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.

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)

@GrahamCampbell Why was this closed yet again without comment?

@srmklive since Laravel Schema/Blueprint does implement a tinyInteger column type, it should provide a plugin for Doctrine/DBAL that makes framework run as expected in both situations: placing a new column, or changing it.

That’s for requests, not bugs.

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:

https://github.com/doctrine/dbal/issues/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

+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.

@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.

@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.

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