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

learn more… | top users | synonyms

3
votes
3answers
819 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
37 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
595 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
3answers
594 views

mariadb ignoring force index

I'm trying to tune a relatively simple query: select bt.col1,bt.col2 from bigtable bt join smalltable st on bt.smalltable_id = st.smalltable_id where st.name = 'some name occuring only once in st' ...
2
votes
1answer
42 views

Keywords and reserved words: What's a poor boy to do?

We have been using MySQL since its beginnings. Back then we thought that descriptive column names were a good idea. So we created columns like date, open, high, low, close, volume, signal and so on ...
2
votes
1answer
312 views

Does PostgreSQL support multi-threaded replication?

Is PostgreSQL replication single-threaded ? Are there any tools to achieve multi-threaded replication ? I'm asking this since Mariadb support this and currently I'm learning both these dbs.
2
votes
2answers
567 views

Slow query responce on different mysql versions, percona, MariaDB

LEFT JOIN is supposed to be faster than a NOT IN query. This is what I've read online. But practically, this seems to be opposite. I have the following query here, INSERT IGNORE INTO callrequests (...
2
votes
1answer
887 views

UTF8 vs ASCII or custom binary format: high-performance optimization on very big tables

The summary of my question is whether there are advantages, even if minimal, to use ASCII, or even a smaller format designed yet created specifically for the situation, instead of UTF-8 for strings. ...
2
votes
2answers
122 views

Master/Slave desyncronisation

Some manual operations (writes) have been made on SLAVE instead of MASTER resulting in the replication being stopped. Could not execute Delete_rows_v1 event on table xxx.newsletter; Can't find ...
2
votes
2answers
49 views

Improve query “JOIN … WHERE NOT EXISTS (SELECT 1 FROM … WHERE … )”

I'm trying to optimize a query because the plan generates a full table scan for a subquery. Data The (simplified) table structure: CREATE TABLE RECORD ( ID INTEGER PRIMARY KEY AUTO_INCREMENT ); ...
2
votes
1answer
60 views

Hybrid Synchronous / Asynchronous Replication?

We have a situation where we would like to have synchronous replication in our main data center, but replicate asynchronously to another region. Our second data center is 3,000 miles away (Site B) and ...
2
votes
1answer
198 views

Corrupt InnoDB: Start mysqld only innodb_force_recovery=6

I am working with version 10.0.19-MariaDB-1~trusty-log and I'm only able to restart mysqld with innodb_force_recovery=6 and I don't know why. The output of the /usr/sbin/mysqld is the following: ...
2
votes
2answers
162 views

ORDER BY indexedColumn ridiculously slow when used with LIMIT on MySQL

The MyISAM table has 4.5m rows. Query with just LIMIT returns fast; query with just ORDER BY returns fast; combining both it never finishes. I've also tried USE INDEX(indexedColumn, Latitude, ...
2
votes
1answer
169 views

Is a “Ranking” query's ORDER BY in FROM subquery is preserved?

I've read in the following links that the ORDER BY in FROM subquery can be ignored. Quoted from MariaDB knowledge base A query such as SELECT field1, field2 FROM (SELECT field1, field2 FROM table1 ...
2
votes
2answers
378 views

Slow “CREATE TABLE” and “TRUNCATE” on MySQL / MariaDB

I am fighting with performance issues on a local MariaDB instance. Fun thing: I previously had problems with all my INSERT and UPDATE and DELETE queries. This got signigicantly faster (~40ms -> less ...
2
votes
3answers
3k views

Could not start mysql or MariaDB. Permissions were ok, but mysql-bin.index permission error

Using MariaDB 5.5.36. Here's what I got while trying to start MariaDB: # syslog Dec 14 09:40:52 vps1497 mysqld_safe: Starting mysqld daemon with databases from /var/lib/mysql Dec 14 09:40:52 vps1497 ...
2
votes
1answer
175 views

How can I solve this error InnoDB: is in the future?

InnoDB: is in the future! Current system log sequence number 412018685584. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See ...
2
votes
2answers
1k views

InnoDB + TokuDB fills up RAM then start swapping

I'm trying to face difficulties with my database lately. I've migrated from MyISAM to TokuDB v7.0.4 and InnoDB. I'm using MariaDB - 5.5.34-MariaDB-1~wheezy-log. My problem is that MariaDB fills up RAM ...
2
votes
2answers
4k views

Memcached plugin on MariaDB?

I'd like to try new NoSQL feature in MySQL 5.6 but I am using MariaDB 10.0.2 on Debian 6 and don't fancy coming back to MySQL. I'm wondering whether the memcached plugin has been added to MariaDB? ...
2
votes
1answer
174 views

The right way of starting up a Galera Cluster

I am confused about the bootstrap process of MariaDB Galera Cluster. Let's say we have 3 nodes of a MariaDB Galera Cluster, working and functioning normally on CentOS6/7. However, at some point we ...
2
votes
1answer
382 views

grastate.dat with seqno -1 on a healthy cluster. Why?

We use those versions: # rpm -qa | egrep '(galera|maria)' mariadb-libs-5.5.41-2.el7_0.x86_64 mariadb-galera-common-5.5.40-6.el7ost.x86_64 mariadb-galera-server-5.5.40-6.el7ost.x86_64 mariadb-5.5.41-2....
2
votes
1answer
309 views

unknown variable 'log_slow_verbosity=query_plan'

What is the use of log_slow_verbosity = query_plan parameter in my.cnf ? I got an error when installing MySQL in my machine because of this parameter.
2
votes
1answer
163 views

3 minutes to import 327Mo database

We do database import a lot of times per day on a server with those specs : Xeon E5-1620v2 4c/8t @ 3,8Ghz 32 Go DDR3 ECC 1600MHz Hard Drive 2x 3 To SATA3 MariaDB 10.14 with XtraDB Given a 327Mb ...
2
votes
1answer
129 views

Making MySQL use primary index without hinting or forcing

I have the following db structure: CREATE TABLE `question` ( `id` int(11) NOT NULL AUTO_INCREMENT, `level_id` int(11) NOT NULL, `video_id` int(11) NOT NULL, `is_active` tinyint(1) NOT NULL, ...
2
votes
1answer
2k views

MariaDB, Triggers and last_insert_id

I have a situation where I have three tables: sample, sample_name and run (extra columns removed to be only relevant information). CREATE TABLE `sample` ( `sample_id` int unsigned NOT NULL ...
2
votes
1answer
3k views

MariaDB and XtraDB/InnoDB plugins

In my vps, I thought of switching from MySQL to MariaDB (using Monty's repository for Debian Squeeze). Copying over my.cnf from MySQL to MariaDB, I saw that the new config file (the one from the ...
2
votes
2answers
2k views

Is there any performance difference among MariaDB (xtradb plugin) and XtraDB Server for InnoDB?

Title says it all. However i have to enter at least 30 characters.
2
votes
1answer
4k views

MariaDB XtraDB Performance Tuning

I believe that innodb has a single read thread. However, with MariaDB you can set multiple read threads. I'm unsure what the recommended value would be. Questions Does anyone have any experience ...
2
votes
3answers
64 views

2 fast SQL queries get slow when bunched as subquery on a very large table

I'm trying to use a subquery to speed up a query on a very large table right now 30 million row it most likely going to end up close to 1 billion rows. My table lnmntr is as follow Lnmntr_ndx: ...
2
votes
1answer
208 views

ERROR WSREP`: gcs/src/gcs.cpp:gcs_open():1379:` Failed to open channel 'test_cluster'

I have three server that I want to Install Galera clustering on them, I followed this link to install and config it. but when I want to start the first server I got this error : root@node107:/home/...
2
votes
1answer
100 views

MariaDB subquery cache scope and settings

I am migrating an application from MySQL 5.6 to MariaDB 10. One major new feature of MariaDB is the subquery cache. I am trying to determine the expected effect this cache will have on our ...
2
votes
1answer
265 views

Two updates cause a deadlock

I have a mysql queue with the following sheme: CREATE TABLE IF NOT EXISTS `media` ( `mid` int(11) NOT NULL AUTO_INCREMENT, `order_type` int(11) NOT NULL, `media_id` varchar(50) COLLATE ...
2
votes
1answer
92 views

SQLyog and MariaDB engine

I am quite new in MariaDB and I use SQLyog Community Edition as the frontend GUI. Whenever I create a new table, on the Engine dropdown, there is no XtraDB option. Is there any additional steps I am ...
2
votes
1answer
8k views

The user specified as a definer ('root'@'%') does not exist

I have a trigger defined as follow: USE `veeva_bi`; DELIMITER $$ DROP TRIGGER IF EXISTS veeva_bi.account_ai$$ USE `veeva_bi`$$ CREATE DEFINER = CURRENT_USER TRIGGER `veeva_bi`.`account_ai` AFTER ...
2
votes
1answer
381 views

MariaDB 10 InnoDB Partition with Data Directory

mysql --version mysql Ver 15.1 Distrib 10.0.20-MariaDB, for Linux (x86_64) using readline 5.1 I have a MariaDB 10 installed, though I tried with the 5.5 release in the CentOS repos. I'm ...
2
votes
2answers
133 views

INET6_ATON returns NULL for input parameter

I'm making a MySQL Stored Procedure which takes a string representation of an IPv4 or v6 address and converts it to binary using INET6_ATON. I know that my version of MySQL supports this function ...
2
votes
1answer
73 views

How should a numeric value that is an external key be stored?

This is something I was pondering recently. trying to determine how to store certain values. The best wide spread example I can think of is Federal Employee Identification Number - more commonly knows ...
2
votes
1answer
4k views

what is the right way to restart a mariadb galera cluster?

I am trying to restart cluster. I have two problems in stopping cluster and starting cluster In stopping cluster, I follow the instruction https://software.mirantis.com/reference-documentation-on-...
2
votes
1answer
420 views

Threads_cached 0 while thread_cache_size > 0

I'm trying to little bit understand with some MySQL (in my case - MariaDB) performance. One point I found - "threads caching". So - I set: thread_cache_size = 4 in my.cnf and restart server. In ...
2
votes
1answer
700 views

How to force MySQL/MariaDB to show numbers in scientific notation?

I have a local MariaDB server 5.5.37 where I have some numeric data in it stored as DOUBLE. I need to perform some queries on this data (things like AVG(...) or STD(...)), and I also have a MySQL ...
2
votes
2answers
94 views

I need help optimizing a not that complicated query that returns unexpedcted performance problems

I have a table that stores details about incoming and outgoing phone calls such as when a call was made, or when a call was received, the phone number of the caller, and the phone number of the ...
2
votes
1answer
963 views

MySQL (or MariaDB) multi-master without MMM

I've been reading blogs on MySQL multi-master setup. The way I've used MySQL replication in the past was master-slave where if the master died (or was being taken down for maintenance) we would shut ...
2
votes
1answer
414 views

MariaDB 5.5 vs MySQL 5.7 query optimizers

I have a query that optimizes differently in MariaDB 5.5.37 and MySQL 5.0 and MySQL 5.7. When poorly optimized it takes about 400 seconds to run, but on MySQL 5.7 which optimizes it differently, it ...
2
votes
1answer
616 views

MariaDB Cluster - data consistency for updates and auto_increment

I have two different MariaDB servers and plan to merge data into one database and create MariaDB Cluster with 3 nodes. I read about Galera and multi-master replication - which MariaDB Cluster use and ...
2
votes
1answer
665 views

MariaDB: Created_tmp_disk_tables high after start

today i looked in the status of my MySQL server which is based on MariaDB and i noticed that Created_tmp_disk_tables is very high (over 168k). I think this is the reason for my high I/O usage. I try ...
2
votes
1answer
50 views

Possible causes that a MySQL/MariaDB query is slow under some table conditions

I am having a strange issue with a slow query under certain conditions: I have made a few tests and was able to isolate the issue to one single table called products_description (all the tables are ...
2
votes
1answer
61 views

MySQL is too slow

I have a project with PHP/Laravel/MariaDB, the DB is getting bigger every day now some tables has 4kk+ records already Server is 4GB RAM + Ubuntu 14.04 + Apache (DigitalOcean) when I only start the ...
2
votes
0answers
86 views

Expanding MariaDB cluster (sharding)

I have an expanding MariaDB 3 node cluster and need to expand the available storage. Each node has (to make the maths slightly easier) 120Gb of database storage. As this is replicated, the total DB ...
2
votes
0answers
33 views

Database does not send any data to application when ran for a larger data set

I have a query that runs fine when returning a smaller dataset (~1 million rows), but when I increase the number of rows it returns (~15-20 million) it will send nothing to the application. My query ...
2
votes
1answer
36 views

Which Table Will Get Locked With This Query

INSERT INTO stats.cps (time_stamp,entries,cust_id) select start_time, count(*) , c_id from gb.str where start_time between '$DATE10' and '$DATE' group by start_time, src_id order by start_time ASC ...