Tag: mysql

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…

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

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…

MySQL & Python- Error: 2006 mysql has gone away

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…

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