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

learn more… | top users | synonyms

35
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
6k 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
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
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
3k 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
746 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
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 ...
5
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 ...
5
votes
1answer
1k 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
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
89 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
699 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
299 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
67 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
138 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
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
150 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
782 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
88 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
3k 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
275 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
205 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
105 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
399 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
161 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
526 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
758 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
238 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
288 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
149 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
155 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
217 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
985 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
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
233 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
766 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 ...
3
votes
1answer
22 views

Performance schema: 'stage/sql/Sending data'

Background I am using MariaDB 10.0 but MySQL 5.6 answers are also of interest. Two machines cloned from the same image, same MariaDB configuration as far as I could tell, same dataset. One machine ...
3
votes
0answers
530 views

Which is the right way to fix “Error: 1205 Lock wait timeout exceeded; try restarting transaction”

I have a PHP script that sync a local DB against external datasource. Each time the script runs I perform some triggers on the DB where INSERT or UPDATE happen. These are the the triggers for that ...
2
votes
4answers
13k 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 ...