Parallel Replication In MariaDB

Parallel Replication In MariaDB

Tags :

Category : How-to

Get Social!

Parallel replication has been available in MariaDB since Version 10.0.5, however requires at least version 10.0.5 on both the Master and Slave for parallel replication to work.

Parallel replication can help speed up applying changes to a MariaDB slave server by applying several changes at once.

What is Parallel Replication?

MariaDB replicates data from a master to a slave by shipping all changes that have been applied to the master to the slave in a serialised file. The file is then read by the slave and each change is applied one at a time. A change may be a single row change, such as an INSERT, a DDL change or statement that is applied in it’s entirety such as INSERT INTO… SELECT. The bottle neck to this process is that the changes which need to be applied are read in serial – that is, one at a time.

Parallel replication tries to overcome this by applying DML statements in parallel by reading ahead in the relay log (the log on the slave with changes waiting to be applied) and giving work to each parallel worker to apply, in parallel! Each parallel worker has a cache that allows it to read ahead in the log and apply statements that can be applied in parallel – these are usually statements applied in a single transaction, or statements that have been committed in the same group.

The above diagrams show the differences between the different replication mode. Up to the SQL Thread things work in much the same way, however in parallel replication mode the SQL Thread behaves differently in that it moves work to the Worker threads rather than applying it directly itself.

Enable Parallel Replication

You will need MariaDB 10.0.5 or later running on both the master and the slave for parallel replication to be available.

Edit your MariaDB config file, my.cnf on some installations and edit or add the following parameter. 

slave-parallel-threads=12

This will enable 12 parallel workers on the database Slave which will be started when your slave server is next restarted and replication is enabled.

You can see if the required number of workers has been started by running show processlist which will show 12 processes running as system user with various State information.

You can see further information by running show slave status which will show you the replication type, how up to date the replication is and if there are any errors.

List of Bank Holidays For England in SQL Format

Tags :

Category : How-to

Get Social!

First off let’s create a table to store the bank holiday values. You may need to adjust this slightly depending on your SQL server technology being used (this was tested on MySQL/ MariaDB Server) but this is standard SQL dialect and should work on any RDBMS that respects the current SQL standards.

DROP TABLE IF EXISTS bank_holidays;
CREATE TABLE bank_holidays (
  holiday_date date NULL
, holiday_description INT NULL
, PRIMARY KEY (holiday_date)
);

The next step is to insert the bank holiday values below. This table is currently for 2012 up to 2019 for England and Wales.

INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20190101', 'New Year’s Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20190419', 'Good Friday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20190422', 'Easter Monday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20190506', 'Early May bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20190527', 'Spring bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20190826', 'Summer bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20191225', 'Christmas Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20191226', 'Boxing Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20181226', 'Boxing Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20181225', 'Christmas Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20180827', 'Summer bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20180528', 'Spring bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20180507', 'Early May bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20180402', 'Easter Monday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20180330', 'Good Friday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20180101', 'New Year’s Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20171226', 'Boxing Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20171225', 'Christmas Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20170828', 'Summer bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20170529', 'Spring bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20170501', 'Early May bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20170417', 'Easter Monday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20170414', 'Good Friday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20170102', 'New Year’s Day (substitute day)');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20161227', 'Christmas Day (substitute day)');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20161226', 'Boxing Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20160829', 'Summer bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20160530', 'Spring bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20160502', 'Early May bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20160328', 'Easter Monday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20160325', 'Good Friday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20160101', 'New Year’s Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20151228', 'Boxing Day (substitute day)');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20151225', 'Christmas Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20150831', 'Summer bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20150525', 'Spring bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20150504', 'Early May bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20150406', 'Easter Monday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20150403', 'Good Friday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20150101', 'New Year’s Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20141226', 'Boxing Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20141225', 'Christmas Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20140825', 'Summer bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20140526', 'Spring bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20140505', 'Early May bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20140421', 'Easter Monday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20140418', 'Good Friday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20140101', 'New Year’s Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20131226', 'Boxing Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20131225', 'Christmas Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20130826', 'Summer bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20130527', 'Spring bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20130506', 'Early May bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20130401', 'Easter Monday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20130329', 'Good Friday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20130101', 'New Year’s Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20121226', 'Boxing Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20121225', 'Christmas Day');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20120827', 'Summer bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20120605', 'Queen’s Diamond Jubilee (extra bank holiday)');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20120604', 'Spring bank holiday (substitute day)');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20120507', 'Early May bank holiday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20120409', 'Easter Monday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20120406', 'Good Friday');
INSERT INTO bank_holidays(holiday_date, holiday_description) VALUES('20120102', 'New Year’s Day (substitute day)');
Github/JAC2703

For a more up to date list, please see my github page.

Please submit a PR if you have collated a list yourself, or have any updates to an existing list. The repository currently covers the UK, but I’d be more than happy to accept a PR for other countries! 

MySQL/ MariaDB Error Code: 1329. No data – zero rows fetched, selected, or processed

Tags :

Category : How-to

Get Social!

The above error can occur when calling a cursor results in no rows, or no more rows if called in a loop. Whilst the error message is descriptive about what has happened physically, you may wish to catch the error so that you can do something else, or simply replace the generic database error with something more meaningful. 

If you’re not sure what I’m talking about, run the following code on a MySQL or MariaDB database:

DROP PROCEDURE IF EXISTS test_error;

DELIMITER $$
CREATE PROCEDURE test_error() 
BEGIN 
    DECLARE temp_column_name VARCHAR(100);
        
	DECLARE c_example CURSOR FOR 
        SELECT   column_name
        FROM     information_schema.columns
        WHERE    column_name != column_name;
    -- Get data from example cursor
    OPEN c_example;
    FETCH c_example INTO temp_column_name;
    CLOSE c_example;
  
END$$
DELIMITER ;

CALL test_error();

The response is:

Error Code: 1329. No data - zero rows fetched, selected, or processed

In order to trap the error we need to define a CONTINUE HANDLER and DECLARE a variable for the CONTINUE HANDLER to set. We can then manage the No data exception simply by checking the variable.

Following on from the above example, we’ve introduced a variable ch_done. When this variable is set to 1 then the last cursor to be FETCHed returned No data. If it returns a zero then data was returned and all is well.

DROP PROCEDURE IF EXISTS test_error;

DELIMITER $$
CREATE PROCEDURE test_error() 
BEGIN 
    DECLARE ch_done INT DEFAULT 0;
    DECLARE temp_column_name VARCHAR(100);
        
	DECLARE c_example CURSOR FOR 
        SELECT   column_name
        FROM     information_schema.columns
        WHERE    column_name != column_name;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET ch_done = 1;
    
    -- Get data from example cursor
    OPEN c_example;
    FETCH c_example INTO temp_column_name;
    CLOSE c_example;
    
    IF(ch_done = 1) THEN 
        -- handle the No data error!
        SELECT 'Oh no!';
    END IF;
  
END$$
DELIMITER ;

CALL test_error();

Remember, if you call multiple cursors in a row, you may need to reset the ch_done back to 0.

How to exit from a MySQL/ MariaDB Stored Procedure/ Function Prematurely

Tags :

Category : How-to

Get Social!

MySQL and MariaDB enable you to define your own error conditions and to report back to the SQL client both a return code and an error message. As soon as you raise the condition then MySQL/ MariaDB will halt any further execution of the code and report the error back to the client. This can help the user calling the function understand what went wrong, rather than seeing a generic database error message.

DECLARE error_flag INT DEFAULT 0;
DECLARE REF_MISSING CONDITION FOR SQLSTATE '45000';

-- Your code, set the error_flag in the event of an error

IF (error_flag) THEN
    SIGNAL REF_MISSING
    SET MESSAGE_TEXT = 'An error occurred!';
END IF;

The above code defines a custom condition with an error code of 45000 which is the suggested user defined error code. Other error codes are available, which you may have seen, but are reserved by the database server to use for specific database error events – it’s best not to mix your user defined messages with these. The function then checks if the error_flag has been set and, if it has, halts further code execution and returns the error “An error occurred!” to the client.

View from MySQL Workbench

You could simplify this by just calling the below code at the point the error is detected, if you are already catching an error event in your stored procedure or function by simply using the below code without the error_flag declaration.

SIGNAL REF_MISSING
SET MESSAGE_TEXT = 'An error occurred!';

View Table Sizes in MySQL/ MariaDB Databases

Tags :

Category : How-to

Get Social!

MySQL and MariaDB present a bunch of queryable objects that give you all sorts of insights into what’s happening with the database. 

The size of data stored in tabels is one such thing that can be easily queried directly in SQL, providing you have SELECT access to the information_schema. 

Create the two below views in a schema of your choice:

CREATE  OR REPLACE VIEW schema_size AS
SELECT   table_schema
,        round(SUM(((data_length + index_length) / 1024 / 1024)), 2) table_size_mb
FROM     information_schema.tables  
WHERE table_schema IN ('dv', 'da', 'hue')  
GROUP BY table_schema
ORDER BY table_size_mb DESC;

CREATE  OR REPLACE VIEW table_size AS
SELECT   table_schema
,        table_name 
,        round(SUM(((data_length + index_length) / 1024 / 1024)), 2) table_size_mb
FROM     information_schema.tables  
WHERE table_schema IN ('dv', 'da', 'hue')  
GROUP BY table_schema
,        table_name 
ORDER BY table_size_mb DESC;

See schema_size and table_size for more info.

You can now query each view to get the size, in megabytes, of a whole schema or individual table respectivley. 

SELECT * 
FROM   schema_size;

SELECT * 
FROM   table_size;
  • schema_size – displays the aggregated size of each schema in the database.
  • table_size – lists all tables in the database. You can add a filter to the table_schema column to limit the output.

MySQL/ MariaDB Table Size

Tags :

Category : Supporting Scripts

Get Social!

Create a MySQL or MariaDB view to show the size of each table in the database:

See database size for more information.

CREATE  OR REPLACE VIEW table_size AS
SELECT   table_schema
,        table_name 
,        round(SUM(((data_length + index_length) / 1024 / 1024)), 2) table_size_mb
FROM     information_schema.tables  
WHERE table_schema IN ('dv', 'da', 'hue')  
GROUP BY table_schema
,        table_name 
ORDER BY table_size_mb DESC
Next Page »

Visit our advertisers

Search

Quick Poll

What type of VPN protocol do you use?
Results

Recent Posts

Visit our advertisers

Site Links
Blog
Rated Sites

JamesCoyle.net Limited