Questions tagged [mariadb]
Open source RDBMS designed to be a "drop-in" replacement for MySQL. Add a version-specific tag like mariadb-10.2 if that context is important.
1,843
questions
2
votes
1answer
893 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
1k 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.
4
votes
1answer
2k views
Best backup strategy for MySQL (MariaDB) replication set
My main concern is getting a proper backup, even more so than a temporary outage. The data is golden. I've been doing reading through the documentation and a lot of the backup strategies seem to be.
...
0
votes
2answers
660 views
Percona XtraDB Cluster/MariaDB Galera Cluster - replication suddenly stops working, cluster thinks it is in “Synced” state
We are trying to populate an empty Galera cluster using myloader. We use the latest version of myloader from repo trunk, the command is as follows:
/root/mydumper/myloader -d /root/dump/export-...
1
vote
1answer
4k views
Is it safe to use mysqldump to backup and restore a master-master cluster
Not sure if the specifics matter but just to get it out of the way I am talking about a Galera cluster on XtraDB (MariaDB).
I have a master-master setup and am interested in backing up and restoring ...
0
votes
1answer
1k views
MySQL pivot table via a view
Suppose the following schema
CREATE TABLE `log_apache` (
`date` datetime NOT NULL,
`count` int(10) unsigned NOT NULL,
`input` bigint(20) unsigned NOT NULL,
`output` bigint(20) unsigned NOT ...
2
votes
1answer
1k 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
0answers
1k views
mariadb out of memory crash
I have recently migrated from mysql (5.5.36 MySQL Community Server (GPL)) to MariaDB (10.0.10-MariaDB-1~wheezy), along with switching my cloud hosting provider.
Both machines have 2GB RAM and 1GB swap....
0
votes
0answers
1k views
TokuDB fails to load in MariaDB, even when transparent hugepages are disabled
Earlier, I upgraded my website server to Ubuntu 14.04 LTS. I had a very nice setup with MariaDB and the TokuDB engine. However, the setup was kind of messed up when TokuDB refused to load properly, ...
2
votes
0answers
753 views
Any way to speed up my queries in MariaDB with tables less than a 4 GB in size?
I'm doing some text mining work and a bit frustrated that queries are taking 6 to 10 minutes to run.
My workstation has a 3.5ghz Xeon, 32GB of RAM, and 2 SSD as RAID 0.
Is it possible to do ...
1
vote
1answer
334 views
Fan-In replication 4 Master -> 1 slave
I am trying to merge 4 source db's (one for each region) into a global db. We use ID ranges as primary key on the tables in each region and so the merge should happen nicely. I have a fairly high ...
2
votes
1answer
989 views
MariaDB Galera setup with drastically different amounts of memory used on cluster nodes - why?
We have a 3 node MariaDB Galera cluster.
All nodes are identical and the config on each is identical. They have 64GB of RAM each.
The output of the following query is 28:
SELECT CEILING(...
2
votes
2answers
826 views
MySQL - prevent OS cache for query
On a high load production server I have many SQL selects which are unique or re-use will be in hours. I use SQL_NO_CACHE but these queries are still cached with OS cache. The server has 64G ram and ...
0
votes
2answers
104 views
Optimize query with indexes
The following query is looking for all towns that are between 5 and 10 kilometers from a hard-coded latitude/logitude (=another town actually). I have 37010 towns.
I'm using Symfony 2 which "creates" ...
1
vote
1answer
693 views
update variable from multiple tables in one row
I have two databases, and I need to UPDATE variable 'birthday' from one row to another checking 'name' (from db1 to db2).
The problem is, that 'name' variable in second db is located in another table ...
0
votes
1answer
2k views
MySQL will not start
Please help, I really need to recover my databases.
service mysql start start: Job failed to start
mysqld --verbose 140401 20:16:11 [Warning] Can't create test file /var/lib/mysql/developer.lower-...
0
votes
1answer
555 views
make MariaDB Auto-Increment-Increment dynamic
In a certain replication setup I have created multi-master replication in STAR topology using MariaDB. I have an unknown number of 'client-masters', so I have set the auto_increment_increment to 30 ...
0
votes
0answers
819 views
Low Query cache efficiency after mariadb 5.5 migration
We change from Percona MySQL server 5.5 to MariaDB but performance is not as good as we think.
This graph is from a day in MariaDB. You can see the low performance in terms of cache_hits:
We have ...
1
vote
1answer
1k views
mariadb 5.5.36 with innodb plugin
I installed mariadb 5.5.36 on a win32 system. Everything runs fine except innodb requests are 2 x slower than an identical MySQL installation. So I want to switch back to original innodb plugin to ...
4
votes
1answer
835 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 ...
13
votes
2answers
20k 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 (...
1
vote
2answers
5k views
Is there a way to enable MariaDB MEMORY storage engine for every table?
I am new to MariaDB and interested in using it because I want to use MEMORY storage engine for every table. I read MEMORY storage engine on mariadb.com and it seems like I had to specify storage ...
1
vote
0answers
343 views
MariaDB Galera Cluster
I have two mariadb servers in cluster that is working galera and xtradb.
it is my primary server configuration:
[mysqld]
datadir=/var/lib/mysql
query_cache_size=0
binlog_format=ROW
...
12
votes
5answers
64k 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 ...
1
vote
1answer
1k views
Connecting to a MySQL database on Mac from a VMWare fusion Virtual Machine
I have MariaDB (5.5.34-MariaDB) installed on a Mac (OS 10.8.5) I can connect to the database fine locally (127.0.0.1) on port 3306. But when I try to connect to the database from windows 7 64bit ...
1
vote
1answer
3k views
MariaDB not caching any queries
I tested several simple queries like such:
SELECT COUNT(*) FROM Users WHERE courses='a';
But none are being cached. The following are the cache related variables:
show variables like "%cache%";
+---...
0
votes
0answers
5k views
MariaDB MySQL server does not start
My MariaDB mysql server doesn't start with error 1067
This is the error log:
140222 0:43:44 InnoDB: The InnoDB memory heap is disabled
140222 0:43:44 InnoDB: Mutexes and rw_locks use ...
12
votes
2answers
11k 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 &...
1
vote
1answer
1k views
Configure MariaDB PAM authentication, and enabling remote login from Toad
I am currently trying to integrate Linux services into my Windows environment and have installed MariaDB with PAM authentication following the guidance from MariaDB.
I have tested successfully on the ...
2
votes
1answer
2k 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 ...
1
vote
1answer
5k views
What does “WSREP Aborter Idle” mean in a MariaDB Galera cluster?
I've tried to Google WSREP Aborter Idle but there are literally 3 results. Does anyone know what this means?
Should I be concerned that the these processes have been opened for so long or is this ...
1
vote
1answer
118 views
Count/Statistic for number of SHOW INDEX queries in MySQL/MariaDB 5.5 [closed]
We have a lot of SHOW INDEX FROM queries coming from our ORM layer.
Usually there is a mechanism to prevent this but it doesn't seem to work
at the moment. To better analyze the problem it would be ...
0
votes
2answers
189 views
Show Users That Connect To DB
We are using Maria DB (v5.3 I believe) in our production environment.
One issue that we have, is that historically, the dev user (which has full rights and privileges) is used in all applications ...
4
votes
2answers
472 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
1answer
995 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 ...
1
vote
1answer
1k views
MySQL/MariaDB stored procedure inserts even with syntax error
createdAt has not been defined, but the query still runs when I call this proc and inserts a "0" date. Why, shouldn't it fail?
DELIMITER $$
USE `MobiFit_Dev` $$
DROP PROCEDURE IF EXISTS `...
4
votes
1answer
14k views
Incorrect definition of table mysql.event after migrating database to mariadb
I did a mysqldump to dump all databases into a .sql file, then installed a mariadb server. There are no errors after the installation. But after restoring the databases, I started to observe the ...
2
votes
2answers
2k 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 ...
0
votes
1answer
575 views
Aria engine - changing block size
It's possible to change aria_block_size from default 8192 without recreating tables? I want try 1024 or 2048 but after change in my.cnf and restart mariadb I got this errors on aria tables:
#138 - ...
0
votes
1answer
84 views
Indexes on XtraDB tables
I have old db with myisam tables, using mariadb and want try move to xtradb for row-level locking. Each table have 27 columns, primary index and other 11 indexes. Columns with indexes have many ...
2
votes
0answers
548 views
mysql/mariadb: ifnull changes collation
It seems that 'ifnull' changes the default collation that is set to be used.
I have tried on both mysql and mariadb:
Server version: 5.5.34-MariaDB-1~precise-log mariadb.org binary distribution
...
2
votes
2answers
5k views
Setting default CHARSET and DB Engine
I recently updated my server's database system from MySQL to MariaDB.
After update, everything works well.
But, when I went to phpMyAdmin, the default charset and types are changed:
At the end of ...
3
votes
3answers
11k 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 ...
14
votes
2answers
7k 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 ...
2
votes
2answers
35k views
node unable to join a galera cluster; it fails during state transfer
It looks like the node is joining the cluster and then it fails… I have tried with both rsync and xtrabackup and it fails during state transfer. I seems to me that I am missing something real simple ...
6
votes
3answers
2k 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 ...
1
vote
1answer
907 views
mariadb-galera-server with Aria?
With Galera now somewhat supporting MyISAM replication, does that also mean that it will replicate Aria databases with storage engine (which is more or less feature complete to MyISAM, and a drop in ...
2
votes
2answers
694 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 (...
0
votes
2answers
5k views
How to enable the Federated/FederatedX storage engine on MariaDB 5.5?
I upgraded from MySQL 5.1 to MariaDB 5.3 and then to 5.5 on a Windows machine. Everything works well, except I can't enable the federated storage engine.
With MySQL 5.1 and MariaDB 5.3, I just added "...
8
votes
2answers
17k 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 ...
