SQL: MySQL commit

by Mark Nielsen
Copyright August 2021

The purpose of this document is to show to commit data (schema or data) in production environment for one MySQL server. Each company has its own procedure. We will cover generic procedures.

No Links
General Procedure
The MySQL setup.
Commit Techniques
The steps

No Links

No links, this article is stuff I have used in production before.

General steps.

These are high level steps. We aren't going to go into details with each one.

There is a written procedure and policy for data or schema changes to databases.
Ticket changes in the system capture all below.
DEV: Development proposes changes that work.
QA tests the changes
A 2nd pair of eyes approves the changes from a technical point of view.
A manager or business or other person accepts the changes.
Changes have to be scheduled and approved. All dangers and backup plans must be presented.
Follow procedures to execute changes in prod.
Make sure all changes are recorded.
Post error logs to ticket and report in ticket the outcome.
If successful, close ticket.
If not, follow procedures or rollback based on policy.
Database changes and application software upgrade should happen on different days if possible, and not on Fridays or weekends if possible.

Setup

Save to a file "my_stuff.sql".

  create database if not exists temp;
  use temp;
  drop database if exists MY_STUPID_DATABASE;
  
  create database if not exists MY_STUPID_DATABASE;
  use MY_STUPID_DATABASE;
  
  create table if not exists people (id int,  name varchar(64));
  create table if not exists charges (id int, people_id int, amount int);
  insert into people values (1, 'mark'), (2, 'john'), (3, 'you');
  insert into charges (people_id, amount) values (1, 10), (2,10), (1, 3), (1, 4), (2,5);

  select count(1) from people;
  select count(1) from charges;  
  create user mark@localhost;
  ALTER USER mark@localhost IDENTIFIED BY 'mark';
  grant all privileges on *.* to mark@localhost;

  show grants for mark@localhost;

Start mysql as: mysql -u root -p
or if not password : mysql -u root

Execute the commands:

use MY_STUPID_DATABASE;
	source my_stuff.sql

Commit techniques

Source sql file

You can do it one of two ways:

In the mysql prompt "source FILE.sql" will execute commands in the file and keep going even if there are errors.
Or "cat FILE.sql | mysql" with mysql options will submit commands to mysql. It should stop on the first error. I never use this, but you might be able to override this behavior.

You must be careful with either solution. Lets assume all the commands work. Should log all commands. In general

mysql should be executed as "mysql -v -v -v"

The first commands should be

      tee error.log
      use database DB;
      select now(), @@hostname, USER();
      begin;

      # Your commands, not schema changes.

      commit;

      # Now do your schema changes OUTSIDE of any commit block.
      # You may have several commit blocks, schema changes should be outside of them.

Analyze the error log with "grep -i err error.log" and look for any errors. NOTE: you may get false errors.
Feel free is to discuss your own requirements.

Doing it this way, you need to be careful of:

Any schema changes. They can commit changes and also cause locks.
Data or schema changes may lock queries.
Data or schema changes can use of diskspace.
It might be wise to break up sql files to commit to data and schema separately and to do a different sql file for each ticket.

Online DDL Operations

Online DDL Operations

Some schema changes can be made without locking, without creating a new table, and does not block queries. Different versions of MySQL allow different DDL operations without hassle. However, the operations my slow down the system. Online DDL Operations is used a lot and very beneficial and can be combined with the sql change files.

Percona online schema changes

pt-online-schema-change Percona online schema change creates a temporary new table, populates the new table, use triggers to make the new identical the same as the old with the changes, and then swaps the tables, and usually deletes the now old table.

This technique can be used for almost all schema changes. There are some exceptions, like if triggers exist. Also, some versions make only work with some versions of MySQL.

This is used a lot and be used with sql change files.

Sample Steps

We will do the following:

Create a new table.
Add data in a commit block.
Use online schema change to change table.
Lock a table with a schema change.
Use pt-schema-change to all auto increment and indexes.

Save to a file called: "my_stuff2.sql"

    
    tee error2.log
    use database MY_STUPID_DATABASE ### Yes I did use a second time.;
    select now(), @@hostname, USER();

    create table if not exists BLAH (i int);
    alter table BLAH add column i2 int;
    
    ALTER TABLE charges ADD COLUMN ignore_me1 int, ALGORITHM=INSTANT; 
    ALTER TABLE charges ADD COLUMN ignore_me2 int;

    begin;
    insert into charges (people_id, amount) values (1,1), (2,2), (1,3), (2,4);
    commit;

    alter table people CHANGE id id INT AUTO_INCREMENT PRIMARY KEY;
    alter table charges CHANGE id id INT AUTO_INCREMENT PRIMARY KEY;
    
    show create table charges;
    select count(1) from charges;
    show tables;

Start mysql: In mysql shell: mysql -v -v -v -u mark -pmark MY_STUPID_DATABASE

Execute commands in mysql:

    use MY_STUPID_DATABASE
    source my_stuff2.sql

Look at error log with linux command: grep -vi err error2.log

Save to a file called "stuff.bash ".

    date
    whoami
    set PTDEBUG =1;
    pt-online-schema-change --dry-run --alter "ADD COLUMN ignore_me_pt1 INT" D=MY_STUPID_DATABASE,t=charges  -u mark -pmark;
    date

    pt-online-schema-change --dry-run --alter "ADD index name (name)" D=MY_STUPID_DATABASE,t=people -u mark -pmark;
    date
    pt-online-schema-change --dry-run --alter "ADD index id_amount (id, amount)" D=MY_STUPID_DATABASE,t=charges -u mark -pmark;
    date

    # you should really check the dry runs and then do the commits. 

    pt-online-schema-change --execute --alter "ADD COLUMN ignore_me_pt1 INT" D=MY_STUPID_DATABASE,t=charges  -u mark -pmark;
    date

    pt-online-schema-change --execute --alter "ADD index name (name)" D=MY_STUPID_DATABASE,t=people -u mark -pmark;
    date
    pt-online-schema-change --execute --alter "ADD index id_amount (id, amount)" D=MY_STUPID_DATABASE,t=charges -u mark -pmark;
    date
    
    mysql -v -v -v -u mark -pmark MY_STUPID_DATABASE -e "show create table people;"
    mysql -v -v -v -u mark -pmark MY_STUPID_DATABASE -e "show create table charges;"

Execute file as: " bash stuff.bash > stuff.log 2>&1 "
Look at error file: "more stuff.log"