MARKS TUTORIAL

In this article I want to share my investigation, where I face this issue in GCP (why specially mention, you will know at the end), and hope that it might save someone's else time. The MySQL GCP version is:  5.7.33-google-log . mysql> ALTER TABLE t_user CHANGE COLUMN UserPassword UserPassword varchar(255) NOT null, ALGORITHM=INPLACE, LOCK=NONE; ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.   mysql> ALTER TABLE t_user CHANGE COLUMN UserPassword UserPassword varchar(70) NOT null, ALGORITHM=INPLACE, LOCK=NONE; Query OK, 0 rows affected (0.01 sec) Records: 0  Duplicates: 0  Warnings: 0 In MySQL, utf8 corresponds to 3 byte length per character, hence according to MySQL utf8 will change byte length around 85 characters. According to documentation: Extending VARCHAR column size ALT...

In this article we will look into a very important concept of distributed computing and that is we need to prevent happening of transaction on failed master in a HA cluster of MySQL, being other nodes has elected a new master (earlier slave) due to Network partitioning. Here we will assume the setup of HA cluster as follows: Client/s first connect to Service Discovery module to know the current Master , they cache the information for a specific interval. They then talk to the Master to proceed their transaction/s. The transaction are then replicated to connected replicas. The client who has the Master information will cache it and do not change it until told to do so. We will tell you, what will happen if we allow transactions on a failed master, using a real world use case, and so you will know why it is important to prevent it. The setup is as follows:  3 Clients. 1 Service Discovery Module. 3 MySQL server, 1 Master/Primary and 2 Replicas/Secondary. Please see the diagram repr...

In this article I have tried to explain how flow of information is seen from a resource point of view. I know this is very basic, but remember good knowledge of basic things help you understand more complex things easily. This knowledge will also help you to create or understand monitoring graph provided by various applications like Percona Monitoring and Management. PS: I am not in their marketing team, I just have done hands on it and I love it that's why mentioned here. 😄 I believe the image is self explanatory but defining the terms here for completeness. CPU No Description. Memory Pages read from disk and written to Memory. Pages read from Memory and written to disk. Disk Pages read from Disk and written to memory. Pages read from memory and written to Disk. Network Interface When information is send to the Network interface, it is called Request/Bytes Read/Inbound and when it is retrieved from Network Interface then it is called Response/Bytes Written/Outbound.

This article defines a method for extracting schema from a particular MySQL database, without dumping the data from the tables. This procedure can be helpful in cases of migration where you want schema but not the data of target database. For example, checking if Application DB for version X can be migrated to Application DB version Y. Dump Table Structure mysqldump -h localhost -u root -proot@123 -P3306 --single-transaction --skip-comments --no-data --skip-routines --skip-triggers --skip-events --add-drop-database --set-gtid-purged=OFF --hex-blob --databases emsent_dplicense | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | sed -e 's/DEFINER[ ]*=[ ]*[^*]*PROCEDURE/PROCEDURE/' | sed -e 's/DEFINER[ ]*=[ ]*[^*]*FUNCTION/FUNCTION/' > ems52_schema_tables.sql  Dump Routines, Triggers and Events Structure mysqldump -h localhost -u root -proot@123 -P3306 --single-transaction --skip-comments --no-create-db --no-data --no-create-info --routines --triggers --events --...

In this article I will show case a deadlock example encountered in a API call in our application. Quick Links 1. Deadlock Output 2. Understanding MySQL deadlock 3. How to avoid a MySQL deadlock NOTE:  The blog I referred is: How to deal with MySQL deadlocks A deadlock in MySQL happens when two or more transactions mutually hold and request for locks, creating a cycle of dependencies. In a transaction system, deadlocks are a fact of life and not completely avoidable. InnoDB automatically detects transaction deadlocks, rollbacks a transaction immediately and returns an error. It uses a metric to pick the easiest transaction to rollback. Though an occasional deadlock is not something to worry about, frequent occurrences call for attention. Before MySQL 5.6, only the latest deadlock can be reviewed using SHOW ENGINE INNODB STATUS command. But with Percona Toolkit’s pt-deadlock-logger you can have deadlock information retrieved from SHOW EN...

There’s no common blueprint for database management system design. Every database is built slightly differently, and component boundaries are somewhat hard to see and define. Even if these boundaries exist on paper (e.g., in project documentation), in code seemingly independent components may be coupled because of performance optimizations, handling edge cases, or architectural decisions. Hence in this article I will be defining a common architecture (component + interfaces between them) of DBMS, that I believe every DB vendor thrive for. Quick Links 1. Architecture 2. Components and their Interaction 2.1. Transport Subsystem 2.2. Query Processor 2.3. Execution Engine 2.4. Storage Engine DBMS Architecture Database management systems use a client/server model, where database system instances (nodes) take the role of servers, and application instances take the role of client...

Quick Links 1. Error 2. Explanation 3. Solution One day I was restoring the mysqldump, shared by one of my team mate, into my local machine. I was using HeidiSQL as the client, but believes that same has happened if I were using command line utility. All was going well, the dump is being restored. But after a while, as the database is being restored, I got the following error: Error SQL Error (2006): MySQL server has gone away Explanation Now, let us see why we got this error. Since it happens due to the MySQL configuration system variable max_allowed_packet setting , hence the timing of getting the error will depend upon when you hit that limit in your database restore. max_allowed_packet  is basically the maximum size (in bytes) of one packet or any generated/intermediate string exchanged between client and MySQL server. Straight from the documentation A communication packet is a single SQL stat...

MySQL is a well-established relational database management system. It is fully compatible with a Windows computer system. By using the MySQL Installer, an application designed to simplify the setup of MySQL products, MySQL can be installed and deployed within minutes. Quick Links 1. Prerequisites 2. Installation 2.1. Download MySQL Installer for Windows 2.2. Set Up MySQL Installer for Windows 2.3. Configure MySQL Server on Windows 2.3.1. High Availability 2.3.2. Type and Networking 2.3.3. Authentication Method 2.3.4. Accounts and Roles 2.3.5. Windows Service 2.3.6. Logging Options (Optional) 2.3.7. Advanced Options (Optional) 2.3.8. Apply Configuration 2.4. Complete MySQL Installation on Windows Server 3. Conclusion The article is comprehensive, rich with images, and focuses on the individual steps nee...

Quick Links 1. Create Procedure: usp_max_blob_in_db 2. Execute Procedure: usp_max_blob_in_db This article will mention a stored procedure that will help you to find maximum size from all LOB columns in a MySQL database. At the time of this writing, the LOB column in MySQL are any of the data type: 'blob', 'mediumblob', 'longblob', 'text', 'mediumtext' and 'longtext'. You can execute the below SQL procedure to find the maximum size in bytes. The stored procedure calling part contains a parameter called @p_raw_format, which acts as follows: -- Format Parameter: @p_raw_format -- Values: (0) or Any -> Result in human readable form [KB, MB, GB], (1) -> Result in raw bytes. Create Procedure Create the stored procedure usp_max_blob_in_db in the database where you need to find the maximum bytes in any LOB column. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16...