How Golang DB migration tool work?

migrate

Database migrations written in Go. Use as CLI or import as library.

• Migrate reads migrations from sources and applies them in correct order to a database.

---

The migration tool uses a schema_migrations table. One table exists for one DB.

mysql> select * from schema_migrations;
+---------+-------+
| version | dirty |
+---------+-------+
|      33 |     0 |
+---------+-------+
1 row in set (0.00 sec)

The command to apply all migrations to a DB would check-in schema_migrations table and pick migrations after the version (for the current case – 34 and onwards).

migration up

migration down 33

The down migration executes the down file for version 33 and updates the schema_migrations table.

Code

Migration files are read from 1..N if no version is specified.

migrate/migrate.go

// read reads either up or down migrations from source `from` to `to`.
// Each migration is then written to the ret channel.
// If an error occurs during reading, that error is written to the ret channel, too.
// Once read is done reading it will close the ret channel.
func (m *Migrate) read(from int, to int, ret chan<- interface{})

Troubleshooting

1. no migration found for version
   Explanation: The migration version was updated in schema_registry marked dirty but the migration files are deleted/ missing. So maybe the previous migration was done with N and then Nth files were deleted. Now up or down would not work as the schema_registry has version N.
   So the solution is either to bring back Nth migrations or force the schema_regsitry version to N-1.

   migrate force N-1

2. error: Dirty database version 32. Fix and force version.
   Explanation: The 33rd migration file was corrupted or had an error.

   mysql> select * from schema_migrations;
   +---------+-------+
   | version | dirty |
   +---------+-------+
   |      32 |     1 |
   +---------+-------+
   1 row in set (0.01 sec)
   

Manually undo the changes of version 33 (refer to 33 down migration file) and run migrate force 32

References

• https://github.com/golang-migrate/migrate
• https://stackoverflow.com/questions/59616263/dirty-database-version-error-when-using-golang-migrate

Written with StackEdit.

MySQL: Convert Character Set and Collation

The character set and collation changes for a DB have important changes on the storage requirements and query result behavior. The character set for a table can be changed in two ways:
a. Using CONVERT TO query
b. Using MODIFY <column name>

Methods

The CONVERT TO method makes sure that each column fits the new character set range after conversion. So a column type TEXT of character set Latin would not accommodate the character set of UTF8MB4. Since the latter needs 4 bytes for a character, while in Latin one byte is required for a character.

So CONVERT TO rounds of the column type to the nearest next size.

The next method of MODIFY does not make any change to the column type.

Conclusion

I prefer the first method because it’s assuring that the table column sizes are technically large enough and also it eliminates any manual changes for a column.

Examples

ALTER DATABASE <db name> CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

ALTER TABLE `test_table` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci;

ALTER TABLE `test_table` MODIFY `column1` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL;

ALTER TABLE `test_table` MODIFY `column2` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci NOT NULL;

References

• https://dev.mysql.com/doc/refman/8.0/en/alter-table.html
• https://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8
• https://confluence.atlassian.com/kb/how-to-fix-the-collation-and-character-set-of-a-mysql-database-744326173.html

How to: MySQL Get Database Name and Use in Another Query

How to: MySQL Get Database Name and Use in Another Query

The following SQL query get the database name and use the name to run another query.

SET @dbname = DATABASE();

ALTER DATABASE @dbname CHARACTER SET utf8 COLLATE utf8_unicode_ci; 

References

• https://stackoverflow.com/questions/8096550/mysql-determine-which-database-is-selected
• https://stackoverflow.com/questions/6115612/how-to-convert-an-entire-mysql-database-characterset-and-collation-to-utf-8
• https://stackoverflow.com/questions/49735318/mysql-how-to-find-current-database-assign-it-into-a-string-variable
• https://dev.mysql.com/doc/refman/5.7/en/set-variable.html

Design Problems of PostGres- Part II

The problems in PostGres DB are solved in MySQL to an extent. The design merits of MySQL are the following:

• The primary index has a mapping of key to an offset in the disk. But all secondary index tables have a mapping of key to the primary index’s key.

Primary index  
--------------------  
| key | Disk offset|  
--------------------  

Secondary index  
---------------------  
| key | primary key |  
---------------------  

• The drawback is that a lookup of the secondary table needs two hops.
• On the other hand, a change in a row only needs modification of the primary index. It avoids changing the secondary index for every change in row’s data
• MySQL replication uses logical information instead of data with physical information used in PostGres.
• Logs are compact compared to PG.
• MySQL manages its own cache, unlike PG that uses buffer cache.
• PG buffer cache use is inefficient since a read operation would have to land in the buffer cache and hence a context switch.
• PG uses a process per connection. It is expensive. MySQL uses threads for a connection and scales without too much resource consumption.

MySQL & Python- Error: 2006 mysql has gone away

This problem occurs for multiple reasons such as DB connection problem. In our code, we hit this issue due to a subtle problem with DB cursor.

The code was as following:

with conn as cur:
    try:
        print "hello"
    except:
        print "sorry"
    finally: 
        conn.close()

The above code would throw the error 2016 mysql has gone away exception. The problem lies in with conn as cur. This statement creates a cursor on the DB and the cursor object autmatically gets destroyed.
Here, we are closing the DB connection before the automatic destruction happened.

So since connection was invalid(closed), cursor deletion hit an exception.

The solution is to close the connection after cursor object deletion.

try:
    with conn as cur:
        print "hello"
except:
    print "sorry"
finally: 
    conn.close()

Written with StackEdit.

Part I MySQL for beginners: Frequent commands

I am dabbling my feet in SQL after a long time. I studied RDBMS in academics and worked briefly on SQLite.

Following is a list of commands, errors and suggestion, I have come across. This information is collection from various independent sources.

• SQL Server Table Column Types:

bigint    Integer value (-9,223,372,036,854,775,808 - 9,223,372,036,854,775,807)    2^63
int    smaller Integer value (-2,147,483,648) - (2,147,483,647)    2^31
smallint    smaller Integer value (-32,768) - (32,767)    2^15
tinyint    smaller Integer values 0 - 255    2^8
bit    Integer data value (either 1 or 0 value)    1 or 0
decimal    Decimal values from -10^38 - 10^38    10^38
numeric    Decimal values from -10^38 - 10^38    10^38
money    Money values (-922,337,203,685,477.5808) - (922,337,203,685,477.5807)    2^63
smallmoney    Smaller Money Values (-214,748.3648) - (214,748.3647)    2^31
datetime    Date value (January 1, 1753) - (December 31, 9999)    
smalldatetime    Smaller Date Value (January 1, 1900) - (June 6, 2079)    
timestamp    Unique Number Value (updates when row is updated)    
char    Character String Value (max 8,000 characters)    
varchar    Character String Value maximum of 8,000 characters, unless otherwise noted)    
nchar    Character String Value (max 4,000 characters)    
nvarchar    Character String Value (max 4,000 characters)    
text    Character String Value (max 2,147,483,647 characters)    2^31
ntext    Character String Value (max 1,073,741,823 characters)    2^30
binary    Binary Value (max 8,000 bytes)    
varbinary    Binary Value (max 8,000 bytes)    
image    Binary Value (max 2,147,483,647 bytes)    2^31
uniqueidentifier    Global Unique ID (GUID)

• Deleting a foreign key from a table

mysql> alter table address drop foreign KEY id;

• View a table in detail

mysql> show create table address;

• Adding primary key to a table

mysql> alter table address add primary KEY(addr_id);

• adding foreign key to a table

mysql> alter table address ADD FOREIGN KEY(id) REFERENCES entity(id) ;

• Change a column name

mysql> alter table entity change name fname varchar(128);

• Adding auto-increment

mysql> alter table meta_entity modify id int not null AUTO_INCREMENT;