Some of the features have become obsolete and have been removed from MySQL 8.0. When alternatives to these removed items are shown, they need to be used to avoid further complications.The ‘innodb_locks_unsafe_for_binlog’ system variable has been removed.The ‘READ COMMITTED’ isolation level can be used since it behaves in a similar way.After upgrading the system to MySQL version 8.0.3 or later, scripts that reference previous InnoDB INFORMATION_SCHEMA view names have to be upgraded.Some of the account management attributes have been removed. A few have been listed below:Instead of using ‘GRANT’ to create users, use ‘CREATE USER’.The query cache has been removed.The deprecated ... Read More

Some of the features that have been deprecated may be removed in the upcoming versions of MySQL. If applications use the features that have been deprecated in that specific version, that feature should be revised and alternatives should be used wherever possible.Let us understand in brief, the features that have been deprecated in MySQL 8.0:The ‘utf8mb3’ character set is deprecated, use ‘utf8mb4’ instead.The ‘sha256_password’ password authentication is deprecated, may be removed in future updates. Use ‘caching_sha2_password’ instead.Some implementation changes have been made to ‘validate_password’ plugin, may be removed in future versions. Use this plugin by ensuring that component infrastructure is ... Read More

Let us understand the features that were added to MySQL 8.0Security Levels EnhancedThe security levels have been improved, and DBA (Database administrator) has been given greater flexibility for account management.Resource GroupsResource groups can be created and managed, and the server has the ability to assign threads to resources of specific groups, that are running within the server. Group attributes can be used to control the resources, restrict or provide permission to the threads to consume the resource, and so on.Transactional Data DictionaryA transactional data dictionary is used to store information about objects, which was previously a non-transactional table.Upgrade ProcedureThe upgrade ... Read More

MySQL is a very powerful program in its own right. It handles a large subset of the functionality of the most expensive and powerful database packages. It uses a standard form of the well-known SQL data language. MySQL 8.0 released on 19 April 2018 and the current version is 8.0.23.The new features in MySQL 8.0 have been briefly listed below:Atomic DDLAn atomic data definition language (DDL) statement to combine updates made to data dictionary, storage engine operations and so on.Encryption DefaultsThe encryption defaults have been defined and implemented globally for table encryption.  The ‘default_table_encryption’ variable is used to define an ... Read More

MySQL is an open source SQL (structured query language) database management system.  It is a system that helps store and manage data efficiently. Database generally stores data in a structured fashion.Timeline of MySQLUnireg, which is the code base of MySQL, was started in 1981.MySQL was founded in 1995 in Sweden.In 2000, MySQL went open source, so it could be accessed and used by all.In the year 2001, Marten Mickos was elected as the CEO of MySQL.In the year 2002, MySQL launched its headquarters in USA, in addition to Sweden headquarters.In 2003, MySQL entered into a partnership with SAP, and many features ... Read More

MySQL is an open source SQL (structured query language) database management system. Let us see some of its characteristics:ConsistentMySQL server is quick, and reliable. It stores data efficiently in the memory ensuring that data is consistent, and not redundant.ScalableMySQL server is scalable and easy to use. Scalability refers to the ability of systems to work easily with small amounts of data, large amounts of data, clusters of machines, and so on.  It is also used in production environment due to its scalability and ease of use.Databases over InternetIt provides high security, improved connectivity, and speed thereby making it suitable to ... Read More

Although quite infrequent, you may come across situations wherein you need to define the primary key on an existing table. This can be achieved using the ALTER TABLE statement.The syntax is −ALTER TABLE table_name ADD PRIMARY KEY (column_name1, column_name2, …., columns_nameN)As can be seen from the above syntax, you can define PRIMARY KEY on multiple columns. When you have defined the PRIMARY KEY on multiple columns, the condition is that the column pairs should have unique and non-null values. Thus, if the PRIMARY KEY is defined on (column1, column2), the values (value1, value2), (value3, value2), and (value1, value4) are allowed. ... Read More

Let us create a new table containing a single timestamp column −CREATE TABLE timestamp_test(    ts timestamp );Now let us populate it with some data −INSERT INTO timestamp_test(ts) VALUES(current_timestamp), (current_timestamp+interval '5 days'), (current_timestamp-interval '18 hours'), (current_timestamp+interval '1 year'), (current_timestamp+interval '3 minutes'), (current_timestamp-interval '6 years');If you query the table (SELECT * from timestamp_test), you will see the following output −ts2021-01-30 19:23:24.0080872021-02-04 19:23:24.0080872021-01-30 01:23:24.0080872022-01-30 19:23:24.0080872021-01-30 19:26:24.0080872015-01-30 19:23:24.008087Now, in order to extract hour, minute, etc. from the timestamp column, we use the EXTRACT function. Some examples are shown below −SELECT EXTRACT(HOUR from ts) as hour from timestamp_testOutput −hour19191191919Similarly −SELECT EXTRACT(MONTH from ts) as ... Read More

Often, we have some very long table names, and writing the table name every time is troublesome. We can use aliasing to help us there, thanks to which, we will need to write the long table name only once.The table aliases are generally written in the FROM part of the statement, or the JOIN part.For example, consider that we have two tables, marks, and student_info, defined respectively below −marksnameroll_noperc_marksAniket1224Siddhi4565Yash2642Isha5687student_infonameroll_noagegenderAniket1226MIsha5625FSiddhi4523FYash2625MNow, if you want to see the name, roll_no, perc_marks, and age of the student in one query, your query will look like this −SELECT marks.name, marks.roll_no, marks.perc_marks, student_info.age FROM marks LEFT ... Read More

Suppose you have a table user_info that contains the state and district of different users. An example is given below −namedistrictstateAnilMumbaiMaharashtraJoyJhalawarRajasthanRonPuneMaharashtraReenaMeerutUttar PradeshNow, if you want to combine the state and district in a single field called location, this is how you should be able to do it −SELECT name, district || ', ' || state as location from user_infoThe || operator is the string concatenation operator. The output will be −namelocationAnilMumbai, MaharashtraJoyJhalawar, RajasthanRonPune, MaharashtraReenaMeerut, Uttar PradeshSimilar operations can also be performed on numerical values. Suppose you have a table marks containing the total marks scored by students and the maximum ... Read More