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 ...
