Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

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.

0
votes
1answer
14 views

Return results of two MySQL query in only one

I am running two seperate queries to get statistics from 3 different tables: contacts campaigns sent_messages The first query counts all contacts that are marked as "stop" in the contacts table (...
0
votes
0answers
43 views

Cannot get a user function to co-operate with a subquery in mariadb

The following is a demo of an issue we are having with a function and a subquery. I'm interested to know if it is a bug or if there is a reasonable explaination why it is failing. Notes: Problem ...
1
vote
0answers
22 views

Levenshtein distance on MariaDB

is there any official or "de-facto" official implementation of Levenshtein distance on MariaDB 10.2 or 10.3? I found this https://github.com/juanmirocks/Levenshtein-MySQL-UDF, but I want to be sure ...
1
vote
0answers
20 views

Get a list of categories per row using a MariaDb Select Subquery

I am new to MariaDB, I have a list os stores that are categorized by one or more categories. I need to show a Json representation of this structure. How can i, using Maria DB, get a list of ...
0
votes
0answers
16 views

Recursive triggers on update MariaDB

I have two virtual servers. On both servers, I have a table "users". I have an update trigger on the table "users" on both servers so when one server table is updated, it should be updated on the ...
0
votes
1answer
14 views

MariaDB - So many threads?

I'm on a test VM based on VMWare with MariaDB + nginx installed; when looking with htop this is the situation: So we have 26 threads under the main process. I know that inactive threads doesn't ...
1
vote
0answers
23 views

Mariadb master master replication supported by HAPROXY for automatic failover

We have implemented master-master setup in maria-db. Master A is being used for read\write and Master B is used for backups and MIS operations. We have built this setup so that we do not need to ...
0
votes
1answer
56 views

MariaDB 10.4.4 “exists” problem

I have 2 similar queries: 1st with 0.002s execution time SELECT * FROM `table1` WHERE (`order_id` = 1234 OR (EXISTS (SELECT * FROM `accounts` WHERE `...
1
vote
1answer
13 views

How can I check a version of installed ODBC MariaDB connector?

Several months ago I built MariaDB Connector/ODBC from Source following official instructions: git clone https://github.com/MariaDB/mariadb-connector-odbc.git cd mariadb-connector-odbc cmake -...
1
vote
1answer
13 views

Change mariadb's log directory

MariaDB was installed using yum on Centos7 using repository http://yum.mariadb.org/10.3/centos7-amd64. MariaDB's log files are sent to directory /var/lib/mysql. How can I change the location to ...
0
votes
1answer
16 views

galera cluster how to run an ALTER without using RSU/TOI

I need to run an alter statement but would like to prevent locking/stall the cluster. The Galera docs advised using: SET wsrep_OSU_method='RSU'; Then Run the ALTER statement, once finich: SET ...
0
votes
0answers
15 views

How do I properly copy BIN_logs to a another mysql server for testing an “undo”?

About a week ago a user, meaning to delete one category from an app database, deleted all/most of them thru the web app that runs the database. I thought we did not have backups so we revived an old ...
0
votes
2answers
35 views

Database stalls on innodb_buffer_pool_wait_free

I have 4 identical MariaDB 10.0.33 databases that are regularly experiencing database stalls in different situations. I'm trying to understand how to tune the innodb parameters to prevent these stalls....
0
votes
1answer
35 views

Large table random select with some peculiarity

I have a table with 800k rows and I need to select 20 random rows that not are in other 2 tables (tickets already purchased). I already tried a SQL with NOT IN syntax, but it's still (sometimes 1s in ...
0
votes
1answer
48 views

Mariadb won't start (can't change dir, errcode 13: permission denied)

I moved my MariaDB database to /media/user/extdrive1/mysql, updated the conf files, and changed chown for the drive to 7777 for user mysql.mysql. Despite all this, sudo systemctl start mysqld fails, ...
0
votes
1answer
32 views

AWS MariaDB reports via CloudWatch that disk space free storage is 0 but 40% unaccounted for

I have 3 AWS MariaDB instances - one for production, one for user testing and one development - and I set up a script to clone the production data across to the testing and dev DBs using mysqldump and ...
0
votes
1answer
33 views

Connect to another host from inside mysql stored procedure

I have two virtual servers on one physical machine. There are MySQL users and passwords the same for both of them. I want to write something like this: SELECT * FROM [host_name].[db_name].[...
0
votes
0answers
45 views

MySQL (MariaDB) - “Data too long for column” for a very short value, not reproducible elsewhere

I have a table logs with a column info - the type is text. I get an error running the following query: insert into logs (date_time, info, log_code, company_id, updated_at, created_at) values ('...
1
vote
0answers
19 views

Preserving some data on rollback in InnoDB?

I want to keep log of attempted, sucessful, and failed operations (both for troubleshooting, and also for detecting malicious users trying things). I want to keep that log in InnoDB MySQL table. Now, ...
1
vote
1answer
39 views

Storing numbers that will be also used as digits

I'm new to databases, please help me to figure out the best design to store 8-digit numbers that are going to be used for two purposes: Statistical analyses of separate digits. For example all 2nd ...
1
vote
2answers
49 views

Vertical / Horizontal Scaling Disk Size MySQL or MariaDB

I have been looking into this for a while and cannot find a suitable solution so would like to know if someone here can please help. I am trying to set up database back-end that will assist with ...
1
vote
0answers
8 views

how wsrep_local_index is defined or could be changed?

I have a cluster of 3 nodes using Galera with MariaDB, every time I want to backup I check the node with the highest wsrep_local_index and then do: SET GLOBAL wsrep_desync = ON; Take the backup, ...
0
votes
0answers
42 views

How is data accessed with unique index in MariaDB? (NULL r_rows on ANALYZE)

I'm using MariaDB 13.3.14 I have a very simple test table with an email column. And I am using ANALYZE to see the behavior of the indexes. If I understand correctly the output of ANALYZE, rows ...
0
votes
0answers
41 views

MariaDB table is full, causing AWS alarms because of high IOPS and / or through-put on loading of dump

I am running a migration script which copies a large amount of data from one table into another and the process is now running for more than 24 hours and failing, as well as generating alarms from the ...
1
vote
2answers
25 views

opened_tables count increasing rapidly

number of opened tables is increasing rapidly after sometime. MariaDB [(none)]> show global status like 'open%'; +--------------------------+--------+ | Variable_name | Value | +------...
2
votes
1answer
31 views

Random row selection but with distinct values for a subset of columns

I have a table that looks like this : Key1 | Key2 | Key3 | Data | -----|------|------|-------| 1 | 1 | 1 | a | 1 | 1 | 2 | b | 1 | 2 | 1 | c | 1 | 2 | ...
1
vote
1answer
21 views

Why do information_schema queries scan all databases?

I'm building a PHP application that looks up the last modified date of all databases using information_schema. A simplified version is like this: $sql = "SELECT update_time FROM information_schema....
0
votes
2answers
44 views

How can I resolve a database name to a directory?

I am connected to a database foo, and I want to resolve where the underlying directory is for that database, what are my options? Obviously I can reason from datadir, and the database name but there ...
1
vote
1answer
61 views

Calculating disk usage with INFORMATION_SCHEMA.TABLE versus file system?

MySQL's database size can be calculated with SELECT table_schema AS db_name, SUM(data_length + index_length) AS size FROM INFORMATION_SCHEMA.TABLES WHERE table_schema != 'INFORMATION_SCHEMA' GROUP BY ...
3
votes
1answer
35 views

MariaDB configuration for many updates, writes

Based on few tutorials that I show I created the following MariaDB configuration for my server. Server has 32 GB ram, SSD, 8 cores and is dedicated to run MySQL. The biggest table has about 1,5 ...
1
vote
0answers
41 views

MariaDB Galera slave thinks it's replicating, but it isn't

I have a MariaDB 10.3 Galera slave node with general logging turned on. It's consuming binlogs generated by a MySQL 5.5 NDB cluster. The binlog format is row-based in both databases. SHOW ALL ...
0
votes
0answers
43 views

mariabackup of database running in docker

I have MariaDB running in a Docker container with the container's data directory /var/lib/mysql/ mapped to the host directory /mnt/data/. I want to run mariabackup from the host to create a physical ...
0
votes
0answers
16 views

MariaDB Semi-Sync Slave connection lost during inserting of rows into Master

I have been using MariaDB (10.3) Semi-Sync [Master/Slave] Replication. As a part of testsystem, I am inserting 6000 rows into Master database and approximately it is processing 1000 rows/second. ...
0
votes
0answers
81 views

MariaDB corrupted after attempting full backup with mariabackup

I have Mariadb installed on a Raspberry Pi with a simple star schema for logging data that comes in on a Modbus server. Everything was running fine for about a month and a half until one day I was ...
0
votes
2answers
65 views

MySQL performance tuning related connection

I am running openstack cloud and it use mysql database which is MariaDB 10.1.30 I am sometime seeing connection reset from multiple clients, to trying to find out what is going on in database, I am ...
0
votes
1answer
117 views

mysqld: Can't create/write to file '/home/mysqltmp/ibXXX' Permission denied

My main volume is a 20GB SSD disk mounted on /. I have a 4TB HDD mounted in /home. So to save space I want to set the mysql temporary directory to /home/mysqltmp. However when I set to this directory ...
0
votes
1answer
23 views

MySQL Conditional Counter Based Other Columns?

I have a query that returns purchases for all customers in a store across a date range. It works fine, but now I've been asked to modify the results so only the first purchase per customer per day is ...
0
votes
1answer
23 views

Table metadata lock MariaDB

I'm having serious problems in a DB, where a "Waiting for table level lock" box is loitering and locks the whole system: Based on some research, when the problem occurred to get the command "SELECT * ...
2
votes
1answer
94 views

How to optimize the execution of query that takes too long

I am working with a query that is taking around 53 minutes to execute, which I feel is excessive. Maria DB version: Server version: 10.1.38-MariaDB-1~stretch mariadb.org The declaration of the ...
0
votes
1answer
33 views

Making a field greater than the limit unique

Firstly, just a quick note. I have searched for an answer but I can only people asking why rather than how to overcome. I am currently designing a set of database tables for a project I am working on....
0
votes
0answers
22 views

Innodb errors - considering changing to aria

On my local machine, using XAMPP, my MySQL would intermittently not start. I have followed lots of solutions for this, and the innodb_force_recovery=1 usually fixes it, at least for a while. It is ...
0
votes
2answers
38 views

How to optimize SQL query which involves GROUP_CONCAT function with ORDER BY

I wrote the following SQL query to group results and then sort results in each group by date: SELECT origin, provider, SUBSTRING_INDEX(GROUP_CONCAT(price ORDER BY departure_date ...
-2
votes
1answer
38 views

How would you do this simple query in the most optimized way MYSQL and also MARIADB

pkid clientname location 1 Jones NY 2 Jones NJ 3 Jones TX 4 Jones FL 5 Jones IA 6 Jones NC 7 Jones SC 8 Jones VA 9 Smith FL 10 Smith CO ...
2
votes
2answers
40 views

First order by importance then group by section

In this table scheme http://sqlfiddle.com/#!9/101a9c/9/0 id section rev importance 2 1 b 6 1 1 a 5 3 2 c 9 5 3 e 6 4 ...
1
vote
1answer
14 views

Why would a file generated by mysqldump be substantially larger than the data directory

I have a MariaDB server - 5.5.60 I have a database xyz that has both MyISAM and InnoDB tables The majority of the tables are MyISAM but in this database in particular, the data is mainly in InnoDB ...
0
votes
0answers
26 views

Unable to call PASSWORD() with VARCHAR() parameter inside a procedure

DELIMITER // CREATE PROCEDURE custUpdateInfo(IN customer_id varchar(20), IN passwordd VARCHAR(20), IN named varchar(20), IN addressd VARCHAR(60), IN phone_number DECIMAL(10) UNSIGNED, IN email_id ...
0
votes
1answer
44 views

How do I debug/set up MariaDB SSL connection?

[ I originally posted this on ServerFault but then was pointed at this community ] Since moving from MySQL to a newer MariaDB installation on Debian 9, I a struggling with SSL connections. I have ...
0
votes
0answers
43 views

init_connect not executing

From this question I was able to make my init_connect = "CALL mydb.Warnings()", but a user login in does not triggers the SP. the SP is like: CREATE DEFINER=`root`@`localhost` PROCEDURE `Warnings`...
0
votes
1answer
37 views

How to use 'mysql -uroot -pXXXX' on Debain 9.8?

My apologies for this lame question. Debian 9.8 changed some things and mysql is broke but I can't seem to fix it. I installed MySQL on Debian 9.8, which now installs MariaDB 10.1. After install I ...
0
votes
1answer
440 views

Access denied for user 'root'@'localhost' What proces is causing this?

I got this connections constantly in logs, how to trace what app is trying to connect? root@localhost is allowed to connect and working for some apps. I think there is some app without changed ...