Planet MySQL

Planet MySQL - https://planet.mysql.com
  1. SQL Aggregate Functions Example | Min, Max, Count, Avg, Sum Tutorial is today’s topic. SQL provides many inbuilt functions that are used for performing various operations in data.  Aggregate Functions are used for performing operations on multiple rows of a particular column and result in a single value. An aggregate function allows you to perform the calculation on a set of values to return the single scalar value. We often use the aggregate functions with the GROUP BY and HAVING clauses of the SELECT statement. Aggregate Functions are: AVG() COUNT() MAX() MIN() SUM() STDDEV() VARIANCE() Let’s understand all this function with examples. #Avg() in SQL The avg() function is used to return an average value after the calculation performed in a numeric column. #Syntax Select avg (column_name) from table_name; Consider the following table. Consider Table: (Customers) CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000   #Query select avg(amount) from customers; #Output   #Explanation The above query resulted in the average salary of customers in a table customer. #Count() in SQL The count() function is used to count a total number of records in a table with a condition or without a condition. #Syntax Select count(column_name) from table_name; Consider Table: (CUSTOMERS) CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000   #Query: (Without a condition) Select count(cust_code) from customers; See the following output.   #Explanation The above query resulted in the total number of customers in a table. #Query: (With a condition) Select count(cust_code) from customers where amount > 10000; #Output   #Explanation The above query returned a total number of customers whose amount where more than 1000. #Max() in SQL The max() function is used to return the maximum value in a column. #Syntax Select max(column_name) from table_name; Consider Table: (CUSTOMERS) CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000   #Query Select max(amount) from customers; #Output   #Explanation The max() function resulted in the max amount present in the customer table. #Min() in SQL The min() function is used to return the minimum value in a column. #Syntax Select MIN(column_name) from table_name; Consider Table: (CUSTOMERS) CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000   #Query Select min(amount) from customers; #Output   #Explanation The min() function query resulted in the min amount present in the customer table. #Sum() in SQL The sum() function is used to return the submission of all numeric value in a column. #Syntax Select SUM(column_name) from table_name; Consider Table: (CUSTOMERS) CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000   #Query Select sum(amount) from customers; #Output   #Explanation The sum() function query resulted in the total sum of the amount column. #STDDEV() in SQL The stddev() function is used to return the standard deviation of a selected column. #Syntax Select STDDEV(column_name) from table_name; Consider Table: (CUSTOMERS) CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000   #Query Select STDDEV(amount) from customers; #Output   #Explanation The STDDEV query resulted in the standard deviation of an amount column. #Variance() in SQL The variance() function is used to return the Variance of a selected column. #Syntax Select VARIANCE(column_name) from table_name; Consider Table: (CUSTOMERS) CUST_CODE CUST_NAME CITY GRADE AMOUNT 101 SHUBH KOLKATA 1 10000 201 SOURAV KOLKATA 1 10000 301 KISHAN PATNA 2 20000 401 DILEEP ALLAHABAD 2 30000 501 SHRUTI PATNA 3 40000   #Query Select variance(amount) from customers; #Output   #Explanation The above query resulted in the variance of the amount column. Finally, SQL Aggregate Functions Example | Min, Max, Count, Avg, Sum Tutorial is over. The post SQL Aggregate Functions Example | Min, Max, Count, Avg, Sum Tutorial appeared first on AppDividend.
  2. Percona is glad to announce the release of Percona XtraDB Cluster 5.6.44-28.34 on June 19, 2019. Binaries are available from the downloads section or from our software repositories. Percona XtraDB Cluster 5.6.44-28.34 is now the current release, based on the following: Percona Server 5.6.44-86.0 Codership WSREP API release 5.6.43 Codership Galera library 3.26 All Percona software is open-source and free. Bugs Fixed PXC-2480: In some cases, Percona XtraDB Cluster could not replicate CURRENT_USER() used in the ALTER statement. USER() and CURRENT_USER() are no longer allowed in any ALTER statement since they fail when replicated. PXC-2487: The case when a DDL or DML action was in progress from one client and the provider was updated from another client could result in a race condition. PXC-2490: Percona XtraDB Cluster could crash when binlog_space_limit was set to a value other than zero during wsrep_recover mode. PXC-2497: The user can set the preferred donor by setting the wsrep_sst_donor variable. An IP address is not valid as the value of this variable. If the user still used an IP address, an error message was produced that did not provide sufficient information. The error message has been improved to suggest that the user check the value of the wsrep_sst_donor for an IP address. Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!
  3. While it shares the same heritage with MySQL, MariaDB is a different database. Over the years as new versions of MySQL and MariaDB were released, both projects have differed into two different RDBMS platforms. MariaDB becomes the main database distribution on many Linux platforms and it’s getting high popularity these days. At the same time, it becomes a very attractive database system for many corporations. It’s getting features that are close to the enterprise needs like encryption, hot backups or compatibility with proprietary databases. But how do new features affect MariaDB compatibility with MySQL? Is it still drop replacement for MySQL? How do the latest changes amplify the migration process? We will try to answer that in this article. What You Need to Know Before Upgrade MariaDB and MySQL differ from each other significantly in the last two years, especially with the arrival of their most recent versions: MySQL 8.0, MariaDB 10.3 and MariaDB 10.4 RC (we discussed new features of MariaDB 10.4 RC quite recently so If you would like to read more about what's upcoming in 10.4 please check two blogs of my colleague Krzysztof, What's New in MariaDB 10.4 and second about What's New in MariaDB Cluster 10.4). With the release MariaDB 10.3, MariaDB surprised many since it is no longer a drop-in replacement for MySQL. MariaDB is no longer merging new MySQL features with MariaDB noir solving MySQL bugs. Nevertheless version 10.3 is now becoming a real alternative to Oracle MySQL Enterprise as well as other enterprise proprietary databases such as Oracle 12c (MSSQL in version 10.4). Preliminary Check and limitations Migration is a complex process no matter which version you are upgrading to. There are a few things you need to keep in mind when planning this, such as essential changes between RDBMS versions as well as detailed testing that needs to lead any upgrade process. This is especially critical if you would like to maintain availability for the duration of the upgrade. Upgrading to a new major version involves risk, and it is important to plan the whole process thoughtfully. In this document, we’ll look at the important new changes in the 10.3 (and upcoming 10.4) version and show you how to plan the test process. To minimize the risk, let’s take a look on platform differences and limitations. Starting with the configuration there are some parameters that have different default values. MariaDB provides a matrix of parameter differences. It can be found here. In MySQL 8.0, caching_sha2_password is the default authentication plugin. This enhancement should improve security by using the SHA-256 algorithm. MySQL has this plugin enabled by default, while MariaDB doesn’t. Although there is already a feature request opened with MariaDB MDEV-9804. MariaDB offers ed25519 plugin instead which seems to be a good alternative to the old authentication method. MariaDB's support for encryption on tables and tablespaces was added in version 10.1.3. With your tables being encrypted, your data is almost impossible for someone to steal. This type of encryption also allows your organization to be compliant with government regulations like GDPR. MariaDB supports connection thread pools, which are most effective in situations where queries are relatively short and the load is CPU bound. On MySQL’s community edition, the number of threads is static, which limits the flexibility in these situations. The enterprise plan of MySQL includes threadpool capabilities. MySQL 8.0 includes the sys schema, a set of objects that helps database administrators and software engineers interpret data collected by the Performance Schema. Sys schema objects can be used for optimization and diagnosis use cases. MariaDB doesn’t have this enhancement included. Another one is invisible columns. Invisible columns give the flexibility of adding columns to existing tables without the fear of breaking an application. This feature is not available in MySQL. It allows creating columns which aren’t listed in the results of a SELECT * statement, nor do they need to be assigned a value in an INSERT statement when their name isn’t mentioned in the statement. MariaDB decided not to implement native JSON support (one of the major features of MySQL 5.7 and 8.0) as they claim it’s not part of the SQL standard. Instead, to support replication from MySQL, they only defined an alias for JSON, which is actually a LONGTEXT column. In order to ensure that a valid JSON document is inserted, the JSON_VALID function can be used as a CHECK constraint (default for MariaDB 10.4.3). MariaDB can't directly access MySQL JSON format. Oracle automates a lot of tasks with MySQL Shell. In addition to SQL, MySQL Shell also offers scripting capabilities for JavaScript and Python. Migration Process Using mysqldump Once we know our limitations the installation process is fairly simple. It’s pretty much related to standard installation and import using mysqldump. MySQL Enterprise backup tool is not compatible with MariaDB so the recommended way is to use mysqldump. Here is the example process is done on Centos 7 and MariaDB 10.3. Create dump on MySQL Enterprise server $ mysqldump --routines --events --triggers --single-transaction db1 > export_db1.sql Clean yum cache index sudo yum makecache fast Install MariaDB 10.3 sudo yum -y install MariaDB-server MariaDB-client Start MariaDB service. sudo systemctl start mariadb sudo systemctl enable mariadb Secure MariaDB by running mysql_secure_installation. # mysql_secure_installation NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY! In order to log into MariaDB to secure it, we'll need the current password for the root user. If you've just installed MariaDB, and you haven't set the root password yet, the password will be blank, so you should just press enter here. Enter current password for root (enter for none): OK, successfully used password, moving on... Setting the root password ensures that nobody can log into the MariaDB root user without the proper authorisation. Set root password? [Y/n] y New password: Re-enter new password: Password updated successfully! Reloading privilege tables.. ... Success! By default, a MariaDB installation has an anonymous user, allowing anyone to log into MariaDB without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment. Remove anonymous users? [Y/n] y ... Success! Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. Disallow root login remotely? [Y/n] y ... Success! By default, MariaDB comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. Remove test database and access to it? [Y/n] y - Dropping test database... ... Success! - Removing privileges on test database... ... Success! Reloading the privilege tables will ensure that all changes made so far will take effect immediately. Reload privilege tables now? [Y/n] y ... Success! Cleaning up... All done! If you've completed all of the above steps, your MariaDB installation should now be secure. Thanks for using MariaDB! Import dump Mysql -uroot -p > tee import.log > source export_db1.sql Review the import log. $vi import.log To deploy an environment you can also use ClusterControl which has an option to deploy from scratch. ClusterControl Deploy MariaDB ClusterControl can be also used to set up replication or to import a backup from MySQL Enterprise Edition. Migration Process Using Replication The other approach for migration between MySQL Enterprise and MariaDB is to use replication process. MariaDB versions allow replicating to them, from MySQL databases - which means you can easily migrate MySQL databases to MariaDB. MySQL Enterprise versions won’t allow replication from MariaDB servers so this is one-way route. Based on MariaDB documentation: https://mariadb.com/kb/en/library/mariadb-vs-mysql-compatibility/. X refers to MySQL documentation. Related resources  ClusterControl for MariaDB  What's New in MariaDB 10.4  How to Manage MySQL - for Oracle DBAs Here are some general rules pointed by the MariaDB. Replicating from MySQL 5.5 to MariaDB 5.5+ should just work. You’ll want MariaDB to be the same or higher version than your MySQL server. When using a MariaDB 10.2+ as a slave, it may be necessary to set binlog_checksum to NONE. Replicating from MySQL 5.6 without GTID to MariaDB 10+ should work. Replication from MySQL 5.6 with GTID, binlog_rows_query_log_events and ignorable events works starting from MariaDB 10.0.22 and MariaDB 10.1.8. In this case, MariaDB will remove the MySQL GTIDs and other unneeded events and instead adds its own GTIDs. Even if you don’t plan to use replication in the migration/cutover process having one is a good confidence-builder is to replicate your production server on a testing sandbox, and then practice on it. We hope this introductory blog post helped you to understand the assessment and implementation process of MySQL Enterprise Migration to MariaDB. Tags:  MySQL MariaDB database migration migration
  4. After upgrading the server many users start it with an unchanged config file only to find some deprecated options that they were using are no longer supported by the later server version, which causes the upgraded server to shutdown. In other cases modifying the server configuration file results in the server refusing to start when an invalid name is mistakenly entered in the configuration file.… Facebook Twitter Google+ LinkedIn
  5. How To Insert Multiple Rows in SQL With Example is today’s topic. In SQL, inserting values in the rows is a tedious task when a table comes with a lot of attributes. Previously, we were habituated to add values in row one at a time which is very time-taking and which also results in a lot of error. If we want to get rid of this, we are going to learn how to insert the multiple rows in a table at a time. How To Insert Multiple Rows in SQL With Example There are different methods to insert values in a table: 1. Using INSERT statement. 2. Using INSERT INTO SELECT statement. 3. Using UNION ALL keyword. #Syntax INSERT INTO table_name(column1, column2, ……., column n) VALUES (values in row 1), (values in row 2), ……… (values in row n); #Parameters 1) Table_name is the name of the table.2) Columns are the name of the columns.3) Values in rows are the set of the values to be inserted in the table. #Note1) At a time, you can insert 1000 rows using INSERT statements. To insert more rows than that used multiple insert keywords or use BULK INSERT.2) Only SQL SERVER 2008 allows adding multiple rows in the table. #Examples Suppose a table is created named as STUDENT. ID NAME CITY 101 Shubh Kolkata 102 Sandeep Asansol 103 Shouvik Patna   #QUERY INSERT INTO STUDENT (ID, NAME, CITY) VALUES (101,’Shubh’,’Kolkata’), (102,’Sandeep’,’Asansol’), (103,’Shouvik’,’Patna’); See the output.   Using INSERT INTO SELECT statement. #Syntax INSERT into table_1 (column1, column2, …., column n) SELECT column1, column2, …., column n from table_2 ; #Parameters table_1 is the name of the newly created table table_2 is the name of the previously created table. Columns are the respective columns in which you want to insert values. #Examples Consider table STUDENT having attributes like ID, NAME, CITY, as discussed in the previous example. Now to add values to it, we are going to use the table. new_student ID First_name City 1 Anshuman Asansol 2 Karthik Allahabad 3 Karan Malda   #Query INSERT INTO STUDENT (ID, name, city) SELECT * from new_student; See the following output.   #Explanation In the parentheses of table student, we have given the column name in the same order which was discussed in the table in the previous example. Now when we have executed a select * statement, then the values from new_student table gets copied to student table. The order should be the same as that of the student table if you are affecting the whole table. #NOTE It is not necessary that all rows should be executed, you can also insert specific rows using where condition and also specific columns which you want. #Using UNION ALL keyword #Syntax Insert into table (column1, column2, …., column n) SELECT values UNION ALL SELECT values_1 UNION ALL ….; #Parameters The table is the name of the table. Columns are the name of the columns. Values are the set of data you want to insert. UNION ALL is the keyword. #Example Consider table STUDENT having attributes like ID, NAME. Now to add values use the following query. #Query Insert into student (ID, Name) Select 1,'Shubh' UNION ALL Select 2,'Sandeep' UNION ALL Select 3,'Shouvik'; #Output   #Explanation Here values are inserted in the same order as mentioned in parentheses. UNION ALL is used for merging all the values in rows. Finally, How To Insert Multiple Rows in SQL With Example is over. The post How To Insert Multiple Rows in SQL With Example appeared first on AppDividend.