Open Source Database

 Understanding MySQL Index Scan Types MySQL uses various scan types to efficiently retrieve data from indexed tables. Understanding these scan types helps optimize query performance. 1. Full Table Scan Definition: Reads all rows from a table. Use Case: When no index is available or the query requires all rows.      select * from emp; 2. Index Scan (Range Scan) Definition: Reads rows within a specified range. Use Case: Queries using indexed columns with range conditions (e.g., BETWEEN, <, >); select * from emp where age between 30 and 40; 3. Full Index Scan Definition: Reads all entries in an index. Use Case: When a query can be resolved by reading only the index. select empid from emp; 4. Index Range Scan Definition: Scans a range of index values. Use Case: Common for range queries and partial key lookups. select * from emp where last_name like 'singh%'; 5. Index Only Scan Definition: Retrieves data directly from the index without accessing the table. Use Case:...

MySQL 8.0: InnoDB now supports Instant ADD COLUMN  Instant DDL has been one of the most requested InnoDB features for a very long time. With ever larger and rapidly growing datasets the ability to do DDL instantly is a must have feature in any web scale database.  Developers constantly need to add new columns to meet the constantly changing business requirements.  The ability to add ADD COLUMNs instantly is the first in a series of DDL statements that we plan to do instantly. The move to  a new transactional data dictionary in MySQL 8.0 has made this task a lot easier for us.  Prior to MySQL 8.0 the meta-data (data dictionary) was stored in flat files called .frm files. The .frm files are in  an arcane format that is long past its use by date. Background:- MySQL 5.6 was the first release to support  INPLACE DDL. Prior to MySQL 5.6 the only way to do DDL was to COPY the rows one by one.  INPLACE DDL  is mostly handled by InnoDB while COPY row ...

 How to resolve MySQL ERROR 1290 running with read-only option When you run a MySQL data manipulation query — INSERT, UPDATE, or DELETE — you may encounter an error saying that the statement can’t be executed because of the read-only option. mysql> SET GLOBAL super_read_only=0; Query OK, 0 rows affected (0.00 sec) mysql> SET GLOBAL read_only=0; Query OK, 0 rows affected (0.00 sec) mysql> create database PITR; Query OK, 1 row affected (0.11 sec)   Now finally we are able to read/write operation.

 MySQL 8.0: From JSON Documents  Tables to  SQL  Here we use the JSON table function called JSON_TABLE(). This function creates an SQL view on the JSON Document, i.e. a mapping between JSON and SQL. We then wrap a CREATE TABLE employees AS around it like this: given below command;-  CREATE TABLE employees2 AS SELECT * FROM JSON_TABLE(@jsonempl, "$[*]" COLUMNS (id INT PATH "$.id", name VARCHAR(45) PATH "$.name", age INT PATH "$.age")) emps;

 MySQL 8.0: From SQL Tables to JSON Documents One of the nice things about MySQL 8.0 is the ability to combine the relational and document models. As a developer, you can choose to work with traditional SQL tables, with schemaless JSON documents, or with both types at the same time. Here we show how to bridge SQL tables and JSON documents by using JSON functions. Enjoy! Step.1.Create sample database. step.2.create table insert record. step.3.From an SQL Table to a JSON Document Here we use two JSON aggregation functions called JSON_ARRAYAGG() and JSON_OBJECT(). We simply select from employees and convert the result set into JSON like this: step.4.We now have a JSON Document referenced by the variable @jsonempl. Let us look at it using the JSON_PRETTY() function: step.5. Now sql also.

 MySQL Document Store MySQL Document Store allows developers to work with SQL relational tables and schema-less JSON collections. To make that possible MySQL has created the X Dev API which puts a strong focus on CRUD by providing a fluent API allowing you to work with JSON documents in a natural way. The X Protocol is a highly extensible and is optimized for CRUD as well as SQL API operations. NoSQL + SQL = MySQL MySQL Document store gives users maximum flexibility developing traditional SQL relational applications and NoSQL schema-free document database applications. This eliminates the need for a separate NoSQL document database. Developers can mix and match relational data and JSON documents in the same database as well as the same application. For example, both data models can be queried in the same application and results can be in table, tabular or JSON formats. A Guide to Transforming Documents into Data:- MySQL Document Store enables you to work with JSON documents using M...

  How to Upgrade Mysql 5.7 To Mysql 8 Step.1.First we need to mysql8.0 repoon centos yum install https://dev.mysql.com/get/mysql80-community-release-el6-11.noarch.rpm. step.2.check yum list  step.3. Install mysql shell for checking upgrade step.4.login to mysqlshell step.5. check upgrade for mysql using mysql-shell https://dev.mysql.com/get/mysql80-community-release-el6-11.noarch.rpm MySQL  JS > util.checkForServerUpgrade("root@localhost:3306"); The MySQL server at localhost:3306, version 5.7.40 - MySQL Community Server (GPL), will now be checked for compatibility issues for upgrade to MySQL 8.0.37... 1) Usage of old temporal type   No issues found 2) MySQL 8.0 syntax check for routine-like objects   No issues found 3) Usage of db objects with names conflicting with new reserved keywords   No issues found 4) Usage of utf8mb3 charset   No issues found 5) Table names in the mysql schema conflicting with new tables in 8.0   No issues found 6) Part...

 CREATE DEFINER=`db_user`@`%` PROCEDURE `COUNT_ALL_RECORDS_BY_TABLE`() LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT '' BEGIN DECLARE done INT DEFAULT 0; DECLARE TNAME CHAR(255); DECLARE table_names CURSOR for      SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_ROWS>0 and TABLE_SCHEMA = DATABASE(); DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; OPEN table_names;    DROP TABLE IF EXISTS TCOUNTS; CREATE TEMPORARY TABLE TCOUNTS    (     TABLE_NAME CHAR(255),     RECORD_COUNT INT   ) ENGINE = MEMORY;  WHILE done = 0 DO   FETCH NEXT FROM table_names INTO TNAME;    IF done = 0 THEN     SET @SQL_TXT = CONCAT("INSERT INTO TCOUNTS(SELECT '" , TNAME  , "' AS TABLE_NAME, COUNT(*) AS RECORD_COUNT FROM ", TNAME, ")");     PREPARE stmt_name FROM @SQL_TXT;     EXECUTE stmt_name;     DEALLOCATE PREPARE stmt_name;   ...

 How to find aborted connection day wise One important metric to monitor is the aborted connection ration,which indicated number of faild connectionsattempts relative to the total number of connection attempts. Step.1.Now we are going to create table. CREATE TABLE `daily_aborted_connections` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `log_date` DATE NULL DEFAULT NULL, `aborted_connection_total` INT(11) NULL DEFAULT NULL, `aborted_connection_yesterday` INT(11) NULL DEFAULT NULL, `aborted_connection_today` INT(11) NULL DEFAULT NULL, `connection_total` INT(11) NULL DEFAULT NULL, `connection_yesterday` INT(11) NULL DEFAULT NULL, `connection_today` INT(11) NULL DEFAULT NULL, `aborted_connections_ration_daywise` DECIMAL(10,2) NULL DEFAULT NULL, `log_inserted_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, `log_time_ist` DATETIME NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) USING BTREE ) COLLATE='latin1_swedish_ci' ENGINE=InnoDB AUTO_INCREMENT=9 ; Step.2....

How to Send UDP Packet Using Mysql Databse  Step 1. Copy send_udp.so file to the following path using root user: [root@localhost ~]#  /usr/lib64/mysql/plugin/ Step2. Change the file permission using the command:  [root@localhost plugin]#  chmod 755 send_udp.so Step3. Manual  Installation To add this function to your mysql execute this SQL command as root in mysql server mysql> CREATE FUNCTION send_udp RETURNS INTEGER SONAME "send_udp.so"; Note:-  Here send_udp is the function name we have created and send_udp.so is the file that we have copied in above steps. Step4. Example of use: Here 127.0.0.1 is the ip address  which must be in network and 1234 is the port. mysql> select send_udp('127.0.0.1',1234,'Test Message'); +-------------------------------------------+ | send_udp('127.0.0.1',1234,'Test Message') | +-------------------------------------------+ |                          ...

 How To Install and configure MySQL Enterprise Thread Pool plugin MySQL Enterprise Edition includes MySQL Enterprise Thread Pool, implemented using a server plugin. The default thread-handling model in MySQL Server executes statements using one thread per client connection. As more clients connect to the server and execute statements, overall performance degrades. The thread pool plugin provides an alternative thread-handling model designed to reduce overhead and improve performance. The plugin implements a thread pool that increases server performance by efficiently managing statement execution threads for large numbers of client connections. The thread pool addresses several problems of the model that uses one thread per connection: Too many thread stacks make CPU caches almost useless in highly parallel execution workloads. The thread pool promotes thread stack reuse to minimize the CPU cache footprint. With too many threads executing in parallel, context switching overhead is h...

  Learn what you should look out for when upgrading an existing database from MySQL 5.7 to 8 and how to change your database to be compatible with the new version. Although MySQL 8 was released back in 2018, a significant share of MySQL servers out there are still running MySQL 5.x. MySQL 5 had a lengthy run from its release in 2005, and thus many organizations still have databases that were built on 5.x. But Oracle has been phasing out MySQL 5.7 support for various platforms over the past few years and end of life for MySQL 5.7 is slated for October 2023. If you’re still running a database on MySQL 5.7, it’s time to seriously consider upgrading. You'll get several new features that give you performance improvements and security enhancements, so it is important that you do this soon — especially with the imminent end-of-life of MySQL 5.7, which means there will be no further security updates. Fortunately, this process is usually pretty straightforward, but there are several changes...

Database Health Categories Cache Performance Metrics These metrics focus on the efficiency of various cache systems within the database, helping to identify potential bottlenecks and areas for optimization. They measure the hit rate and fragmentation of different cache types, such as thread, table, MyISAM, and InnoDB caches, to ensure that frequently accessed data is readily available and cache usage is optimized. Thread Cache Hit Rate Thread Cache Ratio MyISAM Cache Hit Rate InnoDB Cache Rate Hit Table Cache Hit Rate QCache Fragmentation Database Efficiency Metrics This category encompasses metrics that monitor the overall efficiency and performance of the database system. They track key write ratios, log file sizes, and the number of sort merge passes to determine how well the database is managing data writes, storage, and sorting operations. These metrics can help identify areas where optimization efforts can be targeted to improve database performance. MyISAM Key Write Ratio InnoDB...