MariaDB is an open source RDBMS that is designed to be a "drop-in" replacement for MySQL.

learn more… | top users | synonyms

36
votes
5answers
5k views

When is the right time to use MariaDB instead of MySQL, and Why?

What we earn and what we lost with this migration? What should I expect as drawbacks after the migration? Is it really unnecessary to change the applications in any situation?
22
votes
2answers
7k views

How do I properly perform a MySQL bake-off?

I want to performance test (aka bake-off) MySQL server rpm against some other forks such as Percona server, MariaDB, and possibly some others. I'm hoping that by asking this question I can better ...
15
votes
3answers
7k views

Why does importing a 12 GB .sql file take more than 36 hours?

I've been waiting now for 36 hours for a 12 GB .sql file to be imported with a simple type site.sql | mysql command. I can see the ibdata1 is growing still, currently nearly 40 GB. Considering the ...
8
votes
3answers
4k views

How can I improve my SQL statement with weekly results with week starting on Thursday or any other day of the week?

I am a total newb, and I couldn't find a good way to do this anywhere. I have a database table that contains statistics that are recorded at various time throughout the week. The reporting week ...
6
votes
3answers
3k views

Is MariaDB a suitable drop in replacement for MySQL on a standard LAMP stack?

I've been wondering lately what (if any) are the improvements available in MariaDB over 'conventional' MySQL? I understand that where platform interoperability and/or backwards compatibility may be ...
6
votes
1answer
3k views

What are the drawbacks of using Galera Cluster instead of Master/Slave Replication?

What are the drawbacks of using Galera Cluster instead of regular Master/Slave Replication? Galera's 0 slave lag time, Synchronous replication and no Single point of failure seem very appealing then ...
6
votes
2answers
1k views

After I kill my clients, MySQL continues to process queries for hours

Hi I have a MySQL db with 10 million rows with lots of triggers. A php client connects to mysql and INSERT's hundreds of rows per second. When the client has been running for hours, and I terminate ...
6
votes
1answer
7k views

Do you still use MyISAM or prefer Aria storage engine?

If Aria storage engine (previously called Maria) is the "new" MyISAM, which supports transaction and automatic crash recovery: Why still use MyISAM ? Should changing storage engine from MyISAM to ...
6
votes
2answers
2k views

To what extent is MariaDB Java client a perfect replacement for MySQL JDBC (Connector/J)?

Premise: I ship a Java application which will make a JDBC connection to a MySQL database. (At the time of this writing it could be 5.1 or 5.5, but hopefully the question and answer don't rely heavily ...
5
votes
4answers
4k views

TRUNCATE TABLE statement sometimes hangs

Why does the TRUNCATE TABLE statement hang sometimes? What are the reasons for this type of issue? I am migrating from MySQL to MariaDB. This problem doesn't happen with MySQL, only with MariaDB. ...
5
votes
2answers
967 views

Datatype for phone number: VARCHAR, INT or BIGINT?

So this will be the dummy question of the year but I need to ask since is not the first time I pass through this. Take a look to the following table definition: Take a look at the column ...
5
votes
1answer
2k views

Substantial difference between database size under MySQL and actual size on disk

When I check the size of my databases under MySQL I get this: MariaDB [(none)]> SELECT table_schema "Data Base Name", sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB", ...
4
votes
1answer
97 views

How can I optimize a query with multiple derived tables containing `IN` and `GROUP BY`?

I'm collecting nmap data every five minutes and storing it in a database. Information about each scan (e.g. start and end time) is stored in the scans table: CREATE TABLE `scans` ( `scan_id` ...
4
votes
2answers
2k views

SHOW TABLE STATUS very slow on InnoDB

Recently we've been migrating from MyISAM to InnoDB and I understand that MyISAM uses meta information against each table to track information and such - however, the database is about 1.8gb with 1+ ...
4
votes
1answer
98 views

MariaDB database access using roles

I run MariaDB 10.0.23, and I use roles to grant privileges to users. I have a user john: +-------------------------------------------------------------------+ | Grants for john@% ...
4
votes
1answer
747 views

SELECT performance on MariaDB Galera cluster vs. MariaDB is terrible

We are evaluating a Galera setup and so far we have not noticed to many drawbacks except a few queries that have terrible READ performance I cannot put my finger on it. The query by itself isn't ...
4
votes
1answer
301 views

Optimizing MySQL -

I've been researching how to optimize MySQL a bit, but I still have a few questions. MySQL Primer Results http://pastie.org/private/lzjukl8wacxfjbjhge6vw Based on this, the first problem seems to be ...
4
votes
1answer
68 views

Running Triggers on Slave with Row-based Events in MySQL >=5.6

We would like to have a setup where a slave has triggers that are not present on the master for the purpose of updating some summary tables or run a specific udfs.sys If one uses statement-based ...
4
votes
2answers
146 views

Update old database with SQL dump

I had to recover a database with a some months old full backup. I have however in my possession a most recent but unfortunately incomplete SQL dump for this same database done with mysqldump. Is ...
3
votes
2answers
10k views

MySQL - Autoincrement does not increment sequentally if last row was deleted

I have a table which contains an auto incremented primary key id. If I delete the last row (highest id, for exampe id = 6) and insert a new row, the new id starts at 7. Which paramater I have to ...
3
votes
1answer
4k views

Error 1046 Mariadb: No database selected

GRANT ALL ON my-database.* TO my-user@10.0.0.1 IDENTIFIED BY 'password'; ERROR 1046 (3D000): No database selected Ok, I have created a database with a "-" on the name (did the same thing on the ...
3
votes
5answers
14k views

How to determine the optimal sort_buffer_size?

I read from a sample configuration file which says the following: # Sort buffer is used to perform sorts for some ORDER BY and GROUP BY # queries. If sorted data does not fit into the sort buffer, a ...
3
votes
2answers
56 views

What's the best way to diagram this “relation” looking for performance and|or disk space savings

I am working in a DB diagram and I have a table called cm_identifier_type. Each record of this table can have or not a downloadable file (saved a file path). I have identified two possibles ways to ...
3
votes
2answers
3k views

Install MariaDB 10 on Ubuntu without prompt and no root password

Pretty much the same as this question but I don't want to have a root password (this is just a dev machine). Here's what I've got: export DEBIAN_FRONTEND=noninteractive sudo debconf-set-selections ...
3
votes
3answers
151 views

How can I improve this query?

About a year back I introduced a query which returns a sort of "Customers Also Purchased" data-set. At the time it ran reasonably fast however, as of late it's become very slow, sometimes taking up to ...
3
votes
1answer
1k views

How to migrate from MySQL to MariaDB or Percona with minimum downtime?

I want to migrate my MySQL 5.1 database to one of these databases: MariaDB, PerconaDB or MySQL 5.6 Is there any way to do this operation with no downtime or with minimum downtime possible? How? The ...
3
votes
1answer
799 views

MySQL/MariaDB stored procedure compiles with typos

I just asked a similar question to this, but that turned out to be because MySQL column names are case insenstive and I was creating an ambiguity. I took the advice given and prefixed all my params ...
3
votes
1answer
92 views

what does !! as an operator in mysql?

I tried to use the exclamation mark as a not operator in a query SELECT !0, !!0, !!!0, !1, !!1, !!!1, !2, !!2, !!!2; | !0 | !!0 | !!!0 | !1 | !!1 | !!!1 | !2 | !!2 | !!!2 | ...
3
votes
2answers
4k views

Install MariaDB without password prompt in Ubuntu

I need to install MariaDB server in Ubuntu without password prompt. For this purpose, I execute the following commands, but it doesn't gave me any progress. It again shows password prompt. I used the ...
3
votes
1answer
326 views

why “select” work in mariadb but doesn't work in mysql

set @row_number = 0; SELECT * FROM (SELECT (@row_number:=@row_number + 1) AS num, id, tbl_user_id, title, description, length lengths, ...
3
votes
1answer
237 views

Update columns values using select from external database and table in MariaDB trigger

I have two databases pdone and veevan. pdone is a kind of master and veevan keep on continue sync against an external sources. I need to perform a internal sync between veevan and pdone so I am using ...
3
votes
2answers
111 views

Is it possible to backup stored routines and functions with Innobackupex?

I am running a few restore tests on my Dev and I've noticed that the stored routines are not being restored with the innobackupx. Is that something achievable ? Am I doing something wrong ? I am only ...
3
votes
1answer
450 views

MariaDB ALTER TABLE produced a separate .ibd file, how can I re-integrate the data into the shared table space?

We have a multi-GB InnoDB MariaDB (recently switched from MySQL). For some reason, the value of innodb_file_per_table changed to ON (we wanted only one innodb data file explicitely). So when I ran an ...
3
votes
2answers
163 views

Do MySQL optimizations hold for MariaDB also?

I am a developer who will be starting a new project soon in which database performance will be crucial. The current candidate for the backend is MariaDB. I have worked with MySQL and MariaDB before in ...
3
votes
1answer
557 views

Auto-increment ids with multiple inserts in mysql/mariadb

Suppose I have a table with an auto_increment column, and then insert multiple values in a single statement: insert into foo (bar) values ('a'), ('b'), ('c'); From the documentation, ...
3
votes
2answers
2k views

How do I determine how much data is being written per day through insert, update and delete operations?

The longevity of SSDs is largely determined by the amount of bytes written by insert, update and delete operations. What is the best way to accurately determine how much data is being written by ...
3
votes
2answers
854 views

ALTER TABLE … ROW_FORMAT=Compressed times out

I'm trying to change a table with the command: ALTER TABLE ... ROW_FORMAT=Compressed This fails after 15 minutes, the log says: InnoDB: Error: semaphore wait has lasted > 600 seconds InnoDB: We ...
3
votes
1answer
242 views

When is mariadb creating tmp tables?

I have found, that copying to tmp table is taking most of the time of a slow query, that I wanted to optimize. I watched the output of 'profiling' in a terminal. This happens on a pics table with 6000 ...
3
votes
1answer
298 views

Is the innodb_lazy_drop_table problem solved?

I'm researching database configuration for MySQL/MariaDB. I've been reading the configuration chapter in "High Performance MySQL". In there, it suggests that innodb_lazy_drop_table can be used to ...
3
votes
3answers
9k views

Purge old records from mysql ibdata1 file

Is it possible to purge old records from an ibdata1 file without taking down the database attached to it? Records that have been deleted from the database are still present when I scan ibdata1 for ...
3
votes
2answers
2k views

MySQL Full Text search increase relevance for exact matches?

I have a MySQL database of ~10,000 organisation names that I want to be able to search. I would like to use a full text search because this would enable me to find "institute of doobry" by searching ...
3
votes
1answer
155 views

MySQL (MariaDB - 10.0.16-MariaDB-1 (Debian) strange performance issues

I am running version 10.0.16-MariaDB-1 (Debian GNU/Linux 8.1 (jessie)) on Dual Xeon CPU, total 32 cores with 128 GB of RAM. This is a dedicated DB server and has been recently migrated over from ...
3
votes
1answer
162 views

MariaDB Spider Engine Out of Memory

I had almost standard MariaDB with InnoDB engine working fine but I wanted to test out the Spider Engine + Sharding/Partitioning. I had a query which was passing fine on the MariaDB InnoDB for ...
3
votes
1answer
243 views

Introduce delay between master slave setup in MariaDB

We're using a master-slave solution using MariaDB 5.5.44. Is there a way to manually introduce a delay between the master and slave? I'm thinking of something like 1' or so. Is there a mysqld option ...
3
votes
2answers
4k views

How to enable queries logs in MariaDB

I'm running mysql Ver 15.1 Distrib 5.5.46-MariaDB in CentOS 6.7 server. I'm trying to get queries logged and I'm doing by redirecting log to files as shown below: #/etc/my.cnf.d/server.cnf # this is ...
3
votes
1answer
1k views

What's the advantage of thread pool in MySQL/MariaDB?

MariaDB thread pool MySQL thread pool I've read official document of MySQL/MariaDB thread pool,but I'm still confused about it; As I understand, thread pool has two advantages: 1. limit too many ...
3
votes
1answer
137 views

Should I use utf8_unicode_ci or utf8_slovak_ci?

I was using utf8_slovak_ci collation until now, but I read that utf8_unicode_ci sorts very wide range of languages. Does it mean, I do not need to use separate collation for every language and I can ...
3
votes
1answer
3k views

Why does auto increment jumps by more than the number of rows inserted?

I am very perturbed by this weird behaviour I am seeing in the auto_increment value recorded in the bidID of a Bids table after performing bulk insertion using a stored procedure: INSERT INTO Bids ...
3
votes
2answers
238 views

Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]

I have a poorly performing query: SELECT user_id, count(item_id) as count FROM table items WHERE category = 'magazine' AND created_at > 1384754400 GROUP BY user_id ORDER BY count(item_id) desc ...
3
votes
3answers
796 views

How can I improve my table design for different types of an entity?

Consider an accounting system as an example. I have an Entity called Client. Client can be of different types, with different fields applicable to different types. I consider creating separate tables ...