MariaDB Interview Questions

Prepare better with the best interview questions and answers, and walk away with top interview tips. These interview questions and answers will boost your core interview skills and help you perform better. Be smarter with every interview.

  • 4.8 Rating
  • 30 Question(s)
  • 25 Mins of Read
  • 8521 Reader(s)

Beginner

If you've got a solution that currently works with MySQL, it will work with MariaDB. Do we have specific solutions that we recommend?

No. There's a wide array out there, and I'd encourage you to take your pick (the right one, for the right solution).

There are many GUI tools that work with MariaDB, such as Webyog/SQLyog, HeidiSQL, dbForge Studio for MySQL, and of course, MySQL Workbench.

We know some tools have received requests and/or are working to add support for MariaDB and Aria-specific features (SQLyog, for example).

Until your preferred GUI tool supports MariaDB and Aria-specific features directly, most of the tools provide a mechanism for editing and executing custom SQL code.

The data directory location is controlled by the datadir variable. Look at your /etc/mysql/my.cnf file to see where your installation of MariaDB is configured to store data.

The default is /var/lib/mysql but it is often changed, like for example if you are using a RAID array.

No need for a fresh install.

Just export your data, move or delete the system database (ibdata1) and the log files (ib_logfile0 & ib_logfile1), set innodb_page_size to either 4k or 8k, and restart MariaDB.

A new XtraDB instance will be created with the smaller page size. Then you can import your data and run your tests.

or MariaDB 5.3.x then you can uninstall MariaDB with:

sudo make uninstall

If you compiled and installed MariaDB 5.5.x or higher then there is no 'make uninstall'.

However, after doing a 'sudo make install' to install MariaDB there will be an 'install_manifest.txt' file at the top level of the source dir. Remove all of the files listed in it and everything you installed will be gone.

On Linux, the default is to place everything under '/usr/local/mysql/' so removing that directory is enough to uninstall MariaDB.

If you customized the install directory, or if MacOS has a different default then it will be different. In any case, the 'install_manifest.txt' file will have the locations.

InnoDB/XtraDB tables can be up to 64TB (terabytes) in size.

On top of this, you can have multiple tables per database and multiple databases per server.

Usually, the size and space limits of your underlying storage and operating system are reached long before MariaDB's internal limits are reached.

To provide a community developed, stable, and always Free DBMS that is, on the user level, broadly compatible with MySQL.

We strive for interoperability with both our own and our upstream, communities.

Until MariaDB 5.5, MariaDB was kept up to date with the latest MySQL release from the same branch. For example, MariaDB 5.1.47 was up to date with MySQL 5.1.47, and so on.

We did a merge from the main MySQL branch for every new MySQL release or when there was some critical bugfix applied to the main branch.

Since MariaDB 10.0, MariaDB includes backported features from MySQL as well as entirely new features not found anywhere else but does not necessarily include all MySQL features.

We strive to keep our main trees as free from bugs as possible. It should be reasonably safe to pull from our trees at any time.

The MariaDB project is the brainchild of Michael "Monty" Widenius, the founder of MySQL®, Monty Program Ab (now MariaDB Corporation), and a founding member of the MariaDB Foundation.

The core team consists of developers sponsored by the MariaDB Foundation, companies and individuals who are members of the Foundation, and people in the MariaDB community. MariaDB is a technocracy, and we want our core membership to reflect this.

The MariaDB Foundation's role is to be a steward of the MariaDB project.

The Foundation also takes the responsibility of keeping the quality of the

MariaDB project high. The MariaDB Foundation has assumed this stewardship from Monty Program Ab, which was the original driver until a foundation could be created.

A current list of active core contributors (MariaDB captains) can be found on Launchpad.

Anyone with enough technical skill level who actively participates in the development of MariaDB can be part of this team.

You don't have to work at the MariaDB Foundation, MariaDB Corporation, or at any other Foundation member company to be able to participate or commit code!

There is an active community around MariaDB that you can be part of.

Their developer contributions can be found on the log of MariaDB contributors.

There have been some questions if MariaDB is a company or a community open source project. MariaDB is a community open source project because:

  • It's driven by a nonprofit foundation.
  • All code in MariaDB is open source. The MariaDB Foundation does not and does not intend to release any closed source modules to MariaDB. The same goes for companies which are members of the Foundation.
  • Anyone who matches the criteria of a MariaDB captain can get commit rights to the MariaDB code base. There are many different companies and individuals among them.
  • Anyone can contribute articles to the MariaDB Knowledge Base with very few restrictions.
  • The MariaDB Foundation manages the MariaDB trademark but gives broad rights to it to others.

If you can't find help in the MariaDB documentation, in many cases the documentation for MySQL can be used.

New features of MariaDB are mentioned on the MariaDB versus MySQL page and in greater detail under the MariaDB category.

If you have an about a feature that is not properly documented or something that is not working as expected, go to the corresponding Knowledge Base page and ask you there.

You can report and check on bugs which are unique to MariaDB in JIRA. MySQL bugs that also affect MariaDB should also be reported to MySQL.

You can also subscribe to the mailing lists and or join the IRC channel to communicate with MariaDB users and developers.

The MariaDB Corporation offers commercial support for MariaDB and all major MySQL versions (starting from MySQL 3.23).

If you want to contribute to or participate in the development of MariaDB, there are many ways to do so.

You don't have to be a developer (but we always welcome more of those), you just have to have the willingness to help make MariaDB better. For example, we are looking for writers or translators of KB articles and people helping setting up MariaDB discussions groups.

Ready to begin?

Contributing to the MariaDB Project is the page that gathers together everything you need to get started.

The community category contains a lot of interesting links about how to participate.

You can also consider sponsoring a feature.

It has a lot of unique features that make it more useful and wonderful to work over it than MySQL. Some features are: -

MariaDB is licensed under GPL, LGPL, or BSD.

  • It is authorized under the BSD, LGPL, GPL MariaDB.
  • It is suitable to work with the data sources like RDBMS.
  • It provides a great number of storage engines.
  • It provides some great extraordinary performance engines.
  • It has a good paradigm inquiring language.
  • It supports a lot of operating systems and can work on it.
  • It supports a large number of programming language.
  • It even supports the most used web development language that is PHP.

Galera cluster technology is also supported by MariaDB.

As it is the modification of MySQL, it supports many functions that are absent in MySQL.

It contributes commands and many operations.
It removes some unnecessary features present in MySQL.

Before the client has established a connection to the server.

Please have a look at below illustration.

zeolearn:~ byte$ mysql -uroot -p -hlocalhost
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.2.8-MariaDB Source distribution

This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> \q
Bye


zeolearn:~ byte$ mysql.server stop
Shutting down MySQL
. SUCCESS!

zeolearn:~ byte$ mysql -uroot -p -hlocalhost
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

MariaDB provides the same features of MySQL with some extensions. It is relatively new and advance.

A list of the features of MariaDB:

  • MariaDB can run on different operating systems and support a wide variety of programming languages.
  • MariaDB is licensed under GPL, LGPL, or BSD.
  • MariaDB follows a standard and popular query language.
  • MariaDB provides Galera cluster technology.
  • MariaDB provides supports for PHP which is the most popular web development language.
  • MariaDB includes a wide selection of storage engines, including high-performance storage engines for working with other RDBMS data sources.
  • MariaDB also offers many operations and commands unavailable in MySQL and eliminates/replaces features impacting performance negatively.
  • MariaDB's speed is one of its prominent features. It is remarkably scalable and can handle tens of thousands of tables and billions of rows of data.

Join is data retrieval operation that combines multiple query outputs of the same structure into a single output.

By default the MySQL UNION removes all duplicate rows from the result set even if you don’t explicit using DISTINCT after the keyword UNION.

SELECT customerNumber id, contactLastname name
FROM customers
UNION

SELECT employeeNurrber id, firstname name
FROM employees

id name
103 Schmitt
112 King
114 Ferguson
119 Labrune
121 Bergulfsen

Any RDBMS Transaction is a logical unit of work requested by a user to be applied on the database objects. MariaDB server introduces the transaction concept to allow users to group one or many SQL statements into a single transaction, so that the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).

  • Written in C and C++.
  • Tested with a broad range of different compilers.
  • Works on many different platforms.
  • Designed to be fully multi-threaded using kernel threads, to easily use multiple CPUs if they are available
  • Provides transactional and nontransactional storage engines.
  • Implements in-memory hash tables, which are used as temporary tables.
  • Works in cross platform.

Any RDBMS SQL Queries/commands can be divided into three major subgroups.

DDL (Data Definition Language ) which deals with database schemas and descriptions of how the data should reside in the database, thus the language statements like CREATE TABLE / ALTER TABLE belong to DDL

DML (Data Manipulation Language ) which deals with data manipulation, and therefore includes most common SQL statements such as SELECT, INSERT, etc.

DCL (Data Control Language ) which includes commands such as GRANT, and mostly concerns with rights, permissions, privileges and other controls of the database system.

Before executing any update/delete on the table data, it’s always best practice to check the number of rows that are going to be affected i.e.,  updated / removed from table with COUNT() operation with the given WHERE condition to cross check whether we’re doing correct update or not

SELECT COUNT(user_id) FROM users WHERE <>;  

will only return the number of user_id s going to effect.

Use DISTINCT in the query, such as

SELECT DISTINCT fname FROM users;

You can also ask for a number of distinct values by saying SELECT COUNT (DISTINCT fname) FROM users;

To do that we can use the concatenate function as shown in the below

CONCAT (string1, string2, string3) .

Ex., SELECT CONCAT(fname, “ ”, lname) from users;

The first is, naturally, the character count.

The second is byte count.

For the Latin characters the numbers are the same, but they’re not the same for Unicode and other encodings.

Advanced

The 'MySQL' name is trademarked by Oracle, and they have chosen to keep that trademark to themselves.

The name MySQL (just like the MyISAM storage engine) comes from Monty's first daughter "My".

MariaDB continues this tradition by being named after his younger daughter, Maria.

Currently, MONyog does not monitor the audit_log file to fetch the events running on the server. Instead, MONyog runs few queries on the server for fetching the same. We have added your request to our issue list and the priority is not yet set. Sibin (Webyog)

The data directory location is controlled by the datadir variable.

If look at your /etc/mysql/my.cnf file to see where your installation of MariaDB is configured to store data.

The default is /var/lib/mysql but it is often changed, like for example if you are using a RAID array.

mysql> DELIMITER //
mysql> CREATE PROCEDURE
OfficesByCounties (IN countryName
VARCHAR(255))
-> BEGIN
-> SELECT *
-> FROM offices
-> WHERE country = countryName;
-> END //
Query OK, 0 rows affected (0.04 sec)
mysql> DELIMITER ;
mysql> create table offices (name
varchar(10), country varchar(50));
Query OK, 0 rows affected (0.06 sec)
mysql> insert into offices values
('here'
,
'india'),('mongo'
,
'usa'),
('citi'
,
'canada'),('JPM'
,
'India');
Query OK, 4 rows affected (0.06 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> CALL
GetOfficeByCountry('India');
+------+---------+
| name | country |
+------+---------+
| here | india |
| JPM | India |
+------+---------+
2 rows in set (0.00 sec)

As per this error it denotes that the data that we’re trying to delete is still present in another table.

Imagine if you have a table for universities and a table for students, which contains the ID of the university they go to, running a delete on a university table will fail if the students table still has people enrolled for that university.

Right way to do it would be to delete the offending data first, and then delete the university in query.

Simple workaround would be running SET foreign_key_checks=0 before the DELETE command, and setting the parameter back to 1 after the DELETE is done.

If your foreign key constraint has ON DELETE CASCADE, the data in dependent tables will be deleted automatically.

When you’re not deleting by row ID.

Such as in “DELETE FROM zeolearn_tbl ORDER BY timestamp LIMIT 1; “

This will delete the most recently posted question in the table zeolearn_tbl.

The disk space management - each MyISAM table is stored in a separate file, which can be compressed then with myisamchk if needed.

With InnoDB storage engine the tables are stored in tablespace, and not much further optimization is possible.

All data except for TEXT and BLOB can occupy 8000 bytes at most. No full text indexing is available for InnoDB.

The COUNT(*) will be executed slower than in MyISAM due to tablespace complexity.

HEAP tables are in-memory.

They are usually used for high-speed temporary storage. No TEXT or BLOB fields are allowed within HEAP tables.

You can only use the comparison operators = and <=>. HEAP tables do not support AUTO_INCREMENT. Indexes must be NOT NULL.

MariaDB’s Heap table space can be controlled with a variable max_heap_table_size, which is a configurable variable my.cnf or command line.

Description

Prepare better with the best interview questions and answers, and walk away with top interview tips. These interview questions and answers will boost your core interview skills and help you perform better. Be smarter with every interview.
Levels