mysql | DBA Diaries

Using SQL COALESCE to Find the First Non-NULL Value

Posted on May 10, 2017 Written by Andy Hayes Leave a Comment

You are looking to find a way to find the first non-null value from a list of fields. In this post, we look at SQL COALESCE – a wonderfully useful tool that helps to solve that problem.

To help demonstrate this, we will talk about a hypothetical scenario where a number of customer leads have been added to a table from different sources and the consistency of data found in some of the key fields is sparse.

Here is the data, the table is called “Leads”

For the purposes of this example, the task is to pull out a single column list of phone numbers for the leads in the table and that involves finding the first non-null value.

Here is what you do:

SELECT 
  COALESCE(HomePhone, BusinessPhone, MobilePhone) AS Phone 
FROM Leads;

This produces a list:

You can see that it has taken the first phone number (non-null value) found based on the order of columns passed into COALESCE.

It is possible to write this in another way using SELECT CASE

SELECT 
 CASE WHEN HomePhone IS NOT NULL THEN HomePhone
      WHEN BusinessPhone IS NOT NULL THEN BusinessPhone
      WHEN MobilePhone IS NOT NULL THEN MobilePhone
   END AS Phone
FROM Leads;

The result is the same but it takes longer to write the code so using COALESCE in your SQL statement is certainly more efficient in this case – less is more! 🙂

How to Take a Backup of a Table in MySQL

Posted on May 9, 2017 Written by Andy Hayes Leave a Comment

Sometimes, there is a requirement to make a backup of a table in MySQL. For instance, there could be a data update planned, schema change needed or that it is necessary to work with a copy of a table, perhaps for some report.

A possible approach to this is to run a full backup of the database to call upon if something goes wrong. This is feasible but sometimes not practical. The database dump could take some time to complete and if something goes wrong and you need to restore, restoring the dump could equally take some time to complete.

In this post, I will show a couple of ways to take backups of tables in MySQL.

Method 1 – Taking a Backup of a MySQL Table Using INSERT INTO

CREATE TABLE table_backup; 
INSERT INTO table_backup SELECT * FROM table;

This method creates the structure of the table including indexes and then loading the data in via one statement.

The advantages of this method are that makes a backup of the table immediately available with the database. It is however to all extents and purposes just like any other SQL statement by way of consuming disk space on the data drive. So disk space should be a consideration when performing this method.

Method 2 – Taking a Backup of a MySQL Table Using mysqldump

mysqldump -u{backup_user} -p{backup_password} from_db_name table_to_backup > backup_file.sql

Instead of backing up the entire database, a backup of the table can be made using mysqldump.

This is my personal preference if the requirement is just that a backup is needed and that there is no requirement to have the table online like in the first example. The backup is taken using mysqldump and can be directed to a location of choice. Disk space is therefore not a consideration for the data drive, rather it is necessary just for the location being backed up to.

How to Determine Which Port MySQL is Running On

Posted on May 9, 2016 Written by Andy Hayes Leave a Comment

MySQL defaults to port 3306 when installed but sometimes for another reason, it may be listening on a different port. In this post, I list some ways in which you can find out which port your MySQL instance is running on.

Using the MySQL configuration file to determine which port it is running on

If you are running linux, then this is an easy one liner. On my test machine, my config is stored in /etc/mysql:

cat /etc/mysql/my.cnf | grep port

On my test machine this returns

port            = 3306

If you are running Windows, then find your my.ini file and open it in a text editor such as notepad. Perform a search for the term – “port”

Using the MySQL client to determine the MySQL port

MySQL can tell you which port it is running on. Log into it and use the “show variables like ‘port{3a76cbff1b5ce5ace3bd34bbdd68f71285d81fb9af60114c7a6db80d3a8688de}'”;

[email protected]:~# mysql -uroot -p;
mysql> show global variables like 'port{96f5cbaad583fb885ff5d18ecce5734bf1e8596949521e20c290401929518f75}';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)

If you are using a client such as MySQL Workbench, you use the above syntax or click on “Server Status” over on the left hand side, for example in Windows, it looks like this:

Using the netstat command to check which port MySQL is running on

If you’re wanting to know how to check if mysql is running then there are some useful one line commands that can be run.

This command is very useful as a mysql port check either on a unix or windows operation system – “netstat” which is short for “network statistics”

netstat -tln | grep mysql

In the results below (unix OS), the port is listed in this section as “0 0.0.0.0:3306”

tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      1115/mysqld

If running on windows, you could do

netstat -anob

It’s a little hard then to find the one which is relevant as the list can be quite large, scrolling past quickly. It’s possible to pipe it to make the output page or dump it to file and then look for mysqld.exe in the list.

Paging example

netstat -anob | more

Dumping to file example

netstat -anob > output.txt

In each case find the mysqld.exe lines where the status is LISTENING

MySQL Replication Events – Statement versus Row-Based Formats

Posted on May 8, 2016 Written by Andy Hayes Leave a Comment

In a recent post, I briefly touched upon mysql replication events. It is important to know the advantages and disadvantages of each type.

There are two types of replication event:

Statement based – these are the write operations. They written to the binary log as SQL statements.

Row based – these are the row changes and they are written to the binary log as complete copies of the rows.

It’s also possible to configure the server as MIXED which is a combination of both statement and row based replication. We’ll talk about that in a minute…

When setting up replication, in order to specify a particular replication event format, the binlog_format setting is configured in my.cnf (my.ini if using Windows)

Is there a best option ?

Let’s expand first on the differences between statement based and row based replication in MySQL.

Statement based

With this format, every SQL statement that could perform a modify operation is logged to the binary log. Those same statements are then executed on the slaves by the SQL thread.

If the application makes five update operations to a table on the master, those same five statements are executed on the slave against the same dataset and in the same order.

Row based

These are complete copies of the rows that are written to the binary log and subsequently applied to the slaves.

If an application modified the same row five times, then five copies of that row would be written to the binary log and applied to the slaves. A modification might only be an update to a single field on the row but in row based replication, this operation is replicated as the entire row and not a single field update.

What are the advantages of statement based replication in MySQL?

Binary logs are smaller so less disk space is needed for them
Less data is transferred between the master and slaves
Auditing changes made to a database could be done by looking in the binary logs using a tool like mysqlbinlog

What are the disadvantages of statement based replication in MySQL?

Non-deterministic queries can produce results on the slave than are different to what were returned on the master

This points deserves padding out as will cause data consistency problems.

There are many non-deterministic operations which are considered as unsafe when used with replication.

An example of a non-deterministic operation would be where the function UUID() is used to a universal unique identifier. This will return a different result upon each execution. Having this function inside an insert or update statement for example would produce different results on both the master and the slave because the SQL statements generated on the master and executed on the slave. This behaviour applies to not only ad-hoc SQL statements but also to stored procedures and UDF’s.

Greater numbers of row level locks may be necessary, for example if the operation was a INSERT…SELECT. This can introduce a performance penalty.
There is a lot of extra context work needed to ensure that update operations are executed in the correct order

What are the advantages of row based replication in MySQL?

The entire row is replicated. This avoids all of the issues with non-deterministic operations found in ad-hoc SQL, stored procedures and UDF’s
No context information is needed to help guarantee operations are executed in the correct order
Fewer row level locks are needed

What are the disadvantages of row based replication in MySQL?

More data can be generated, increasing disk space requirements, network traffic and I/O
The data is logged in binary format, making it harder to audit the changes to the database using the binary logs

Conclusion – statement based, row based replication or mixed replication

In my view it really depends 🙂 Data consistency should be the number one priority and this has to factor heavily in any decision.

Assess requirements based on the application. If you know for sure that the application is not using non-deterministic functions, then statement based replication would be sufficient but how can you guarantee this as a DBA?

As a DBA you might be responsible for introducing stored procedures and udf’s into production. This gives you a chance to review things before they are live and non-deterministic functions may raise a red flag. However ad-hoc SQL inside the application would be harder to review. In a statement based configuration, data consistency cannot be guaranteed between the master and the slave in this scenario.

So let’s go with row based – no wait, our application is write heavy, that’s going to cost more in hardware storage and potentially affect I/O performance!

Given the above, I would choose MIXED which switches between statement and row based replication depending on the operations performed. This helps keep binary logs in check whilst helping to guarantee data consistency between the master and the slave.

If I had to choose between statement and row based and storage, I/O etc was not an issue, I would stick to row based replication given the extra peace of mind it provides.