Fredy  Larson

Fredy Larson

1595209620

How to alter tables in production when records are in millions

As a developer, I have experienced changes in app when it is in production and the records have grown up to millions. In this specific case if you want to alter a column using simple migrations that will not work because of the following reasons:

It is not so easy if your production servers are under heavy load and the database tables have 100 million rows. Because such a migration will run for some seconds or even minutes and the database table can be locked for this time period – a no-go on a zero-downtime environment.

In this specific case you can use MySQL’s algorithms: Online DDL operations. That’s how you can do it in Laravel.

First of all create migration. For example I want to modify a column’s name the traditional migration will be:

Schema::table('users', function (Blueprint $table) {
            $table->renameColumn('name', 'first_name');
        });

Run the following command php artisan migrate –pretend this command will not run the migration rather it will print out it’s raw sql:

ALTER TABLE users CHANGE name first_name VARCHAR(191) NOT NULL

Copy that raw sql, remove following code:

Schema::table('users', function (Blueprint $table) {
            $table->renameColumn('name', 'first_name');
        });

Replace it with following in migrations up method:

\DB::statement('ALTER TABLE users CHANGE name first_name VARCHAR(191) NOT NULL');

Add desired algorithm, in my case query will look like this:

\DB::statement('ALTER TABLE users CHANGE name first_name VARCHAR(191) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;');

#laravel #mysql #php #alter heavy tables in production laravel #alter table in production laravel #alter tables with million of records in laravel #how to alter heavy table in production laravel #how to alter table in production larave #mysql online ddl operations

What is GEEK

Buddha Community

How to alter tables in production when records are in millions

Olivia Green

1618925097

But such a seemingly insignificant increase in awareness and positive perception can lead to a future increase in sales by several percentage points, start your free trial at Paymo and this is often a multimillion-dollar increase in profits.

Fredy  Larson

Fredy Larson

1595209620

How to alter tables in production when records are in millions

As a developer, I have experienced changes in app when it is in production and the records have grown up to millions. In this specific case if you want to alter a column using simple migrations that will not work because of the following reasons:

It is not so easy if your production servers are under heavy load and the database tables have 100 million rows. Because such a migration will run for some seconds or even minutes and the database table can be locked for this time period – a no-go on a zero-downtime environment.

In this specific case you can use MySQL’s algorithms: Online DDL operations. That’s how you can do it in Laravel.

First of all create migration. For example I want to modify a column’s name the traditional migration will be:

Schema::table('users', function (Blueprint $table) {
            $table->renameColumn('name', 'first_name');
        });

Run the following command php artisan migrate –pretend this command will not run the migration rather it will print out it’s raw sql:

ALTER TABLE users CHANGE name first_name VARCHAR(191) NOT NULL

Copy that raw sql, remove following code:

Schema::table('users', function (Blueprint $table) {
            $table->renameColumn('name', 'first_name');
        });

Replace it with following in migrations up method:

\DB::statement('ALTER TABLE users CHANGE name first_name VARCHAR(191) NOT NULL');

Add desired algorithm, in my case query will look like this:

\DB::statement('ALTER TABLE users CHANGE name first_name VARCHAR(191) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE;');

#laravel #mysql #php #alter heavy tables in production laravel #alter table in production laravel #alter tables with million of records in laravel #how to alter heavy table in production laravel #how to alter table in production larave #mysql online ddl operations

Jerod  Mante

Jerod Mante

1602242460

SQL ALTER TABLE Example | How To Alter Table in SQL

SQL ALTER TABLE query is used to add, modify, or delete columns in the existing table. The ALTER TABLE query is also used to add and drop various constraints on a current table. ALTER TABLE statement in SQL is used to add, modify, or drop columns in a table. You can also use an ALTER TABLE command to add and drop various constraints on an existing table.

SQL ALTER TABLE Statement Example

SQL ALTER TABLE modifies the table definition by altering, adding, or dropping columns and constraints. ALTER TABLE also reassigns and rebuilds the partitions, or disables and enables the constraints and  triggers.

Once a table is created in the database, there are many occasions where one may wish to change the structure of the table.

#sql #sql alter table #alter table

studio52 dubai

studio52 dubai

1621769539

How to find the best video production company in Dubai?

How to find the best video production company in Dubai?We are the best video production company in Dubai, UAE. We offer Corporate Video, event video, animation video, safety video and timelapse video in most engaging and creative ways.

#video production company #video production dubai #video production services #video production services dubai #video production #video production house

studio52 dubai

studio52 dubai

1621857375

Top Video Production Companies in Dubai 2021

Looking for the top video production companies in Dubai in 2021? Choose the right video production company to enhance your product and service with the best video services.

#dubai video production company #video production company #video production house #top video production companies in dubai 2021 #video production #video production companies in dubai

Ajay Kapoor

1626148470

Software Product Development Company, SaaS Development Services

PixelCrayons: Being a top software product development company, we are known for providing robust, secure, feature-packed, and scalable Software product engineering solutions as per the specific needs of businesses.

Get SaaS-based software product development services from initial strategy & planning to final deployment and after delivery support.

Being a trusted SaaS product development company, we cover the entire array of software product engineering services from consulting to development, testing and devops.

We turn your software product idea into reality by putting custom skillsets in place. With 16+ years of domain expertise, we have created 13800+ successful projects and garnered 6800+ happy customers from 38+ countries.

#software product development companies in india #software product development services #software product development company #software product development india #product development companies in india #software product development companies