Questions tagged [mariadb]
Open source RDBMS that forked from MySQL. Add a version-specific tag like mariadb-10.5 if that context is important.
1,898
questions
0
votes
0answers
13 views
Mysqld draining memory, using way more then assigned and not giving any back
This is a production server.
Whenever mysqld is restarted the memory consumption slowly builds up to about 75 to 90% of my total memory (while only 8 out of 32GB is allocated to mysqld).
Sometimes ...
-1
votes
1answer
8 views
Upgrade to 10.3 resulted in deleting configuration from old version
We've recently upgraded MariaDB from 10.1 to 10.3 from Cpanel.
After the upgrade we started to having issues in the INSERT clauses where we use
INSERT into table values ('','one')
where the first ...
0
votes
0answers
19 views
How do I write a query that gives me 2 results from the same table?
I wonder if anyone can help me with this at all, I'm a complete novice here! The call center I manage uses FreePBX as a phone system and try as I might I have not been able to find a solution for a ...
0
votes
0answers
4 views
How to select more than 20 rows in SPHINX engine?
I use SphinxSE instead of FULLTEXT. Sphinx settings are for end-users of a search engine. According to the official documentation,
LIMIT clause. Both LIMIT N and LIMIT M,N forms are supported. Unlike
...
0
votes
0answers
13 views
How to setup replication of 150Gb mysql database
I have 150Gb database on running project.
How to create replication for it without minimum downtime?
All manuals say: lock database, export data, import data at new server, configure and start ...
0
votes
1answer
7 views
Monitor network traffic per client
I'm investigating intermittent performance issues on a database running MariaDB 10.4 and I notice extraordinarily high network traffic. Bytes_sent is about 20TB after running for 58 hours (379GB per ...
-1
votes
2answers
39 views
A quick way to ORDER all IDs randomly in MySQL [closed]
I need to assign random IDs to a table. Thus, I create a mapping table as
CREATE TABLE t2
(
ID int(11) unsigned NOT NULL AUTO_INCREMENT,
SourceID int(11) unsigned NOT NULL,
UNIQUE INDEX(SourceID),
...
1
vote
1answer
42 views
Can CTE simplify repeated and possibly recursive joins?
I have three tables organized as depicted below, showing foreign keys and unique fields (green):
That represents email messages received by a server. (Sorry I couldn't simplify more. Full version ...
-1
votes
0answers
17 views
Join MariaDB table on JSON array to text field
Table crons has a JSON field called surveys which contains a JSON array of UUIDs like:
[
"65f42c322ea442099612cad251be1dac",
"43788fe207b143dc9eac4da38c036af6"
]
Table1 (crons)...
0
votes
1answer
12 views
MariaDB why do I get error “Unknown table x in field list” when it does exist (yes, another one!)
Using mariadb 10.4.13-MariaDB but I get the same error in a copy of the database running under 10.3.
The important part of the schema shows
/*Database: mamlocal*/
----------------------
/*Table ...
0
votes
1answer
20 views
Database connection lost whenever SQL query string is too long
I recently switched from running my Rails app on a single VM to running the database — MariaDB 10.3 — on a separate (Debian Buster) VM. Now that the database is on a separate server, Rails immediately ...
1
vote
2answers
25 views
What is the correct way to declare and use MarisDB user defined variables?
I'm using MariaDB version 10.3.28-MariaDB-cll-lve
This site https://mariadb.com/kb/en/user-defined-variables/ says
User-defined variables names must be preceded by a single at character (@)
That's ...
0
votes
0answers
27 views
What to use **MariaDB** or **Mysql** [closed]
So, I want to upgrade my server database to either MariaDB or to Mysql8 and I want to know what is the best option to go for.
TBC: my server is Unix based system and my websites are developed in PHP.
...
0
votes
2answers
39 views
Delete from GROUP BY with multiple GROUP columns
I have a simple table consisting of id, channel_id, value and timestamp:
CREATE TABLE `data` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`channel_id` int(11) DEFAULT NULL,
`timestamp` bigint(20) NOT NULL,
`...
1
vote
2answers
118 views
how to change “localhost” in MariaDB
I wonder if there is any way to change localhost in MariaDB.
For example, while creating a user in database, it shows
'user'@'localhost'
I would like to know how to change localhost to any other ...
0
votes
1answer
37 views
MariaDB innodb bufferpool load taking long time
When I am restarting my MariaDB database engine it takes a long time to show 'innodb bufferpool load completed' depending on data volume. Is there a way to make it faster?
+----------------------------...
2
votes
1answer
33 views
How to code something like 'Update on duplicate key update to something else'?
As part of dealing with the UK's GDPA 'right to be forgotten' in a small database I want to update the (autoinc) PK of a row in table 'member' to an obfuscated value and let it cascade down through ...
0
votes
1answer
27 views
Mariadb concurrent insert not working for MYISAM tables
I am using mariadb 10.1.13. I have a large MYISAM table ( > 2000000 rows ) which used to have frequent 'INSERT' operations from multiple scripts.
Recently I had to add a new 'SELECT' query to the ...
0
votes
0answers
13 views
MariaDB default user accounts created during installation?
I have installed MariaDB 5.5.57 and phpMyAdmin 5.1.0 on my QNAP NAS.
I notice that 7 user accounts were created during installation;
What's the purpose of the three "Any" accounts, and do I ...
0
votes
0answers
26 views
importing tablespace showing error 2013 (HY000): Lost connection to MySQL server during query
trying to recover innodb table from ibd file (576kb). this is the exact steps i do:
getting create table query using mysqlfrm command
create the table
Alter Table discard tablespace
moving the new ...
1
vote
2answers
11 views
MariaDB - Changing explicit_defaults_to_timestamp
I am trying to check from MariaDB docs online how I can turn ON explicit_defaults_to_timestamp global variable. I encountered this as part of Apache Airflow db setup.
Is there any solution to do this? ...
0
votes
2answers
18 views
MariaDb mysqldump configurations
We have extremely large database (mariaDB 10.3) and mysqldump taking 2-3 hours to take the full database dump and also causing downgrade in application performance during the dump process.
After some ...
0
votes
0answers
39 views
MariaDB System Versioned Tables, Update queries causing duplicates
I have just enabled system versioned tables on an existing system and am experiencing an oddity, not sure if it is a bug or if I am misunderstanding something.
I have a table called assets, in this ...
1
vote
0answers
45 views
MariaDB service fails to start with InnoDB Space id error
I've been running MariaDB on a Raspberry Pi 4 (Raspbian 10 / buster) as the database server for my Webtrees and Owncloud sites. Recently, I noticed that the server wasn't looking right, and after a ...
0
votes
0answers
16 views
galera_new_cluster hangs with galera_recovery
My cluster crashed and i cannot restart the first node with galera_new_cluster.
The nodes are distributed geographically in France with medium latency of 12ms.
It remains on /usr/bin/galera_recovery ...
0
votes
1answer
8 views
mysql.gtid_executed table is empty
I want to keep track of GTIDs and the corresponding mysqlbin log somewhere. I see this mysql.gtid_executed table, however on my mariadb DB, there are no rows in this table. Any idea how this gets ...
-1
votes
2answers
13 views
Preferential Select Query
The issue that we are trying to tackle is best shown with the following illustrative example:
CREATE TABLE table_1
(
id INT UNSIGNED AUTO_INCREMENT,
colA INT,
colB VARCHAR(10),
PRIMARY ...
1
vote
0answers
17 views
physical backup in mariadb 10.5 InnoDB: Unsupported redo log format
I have mariadb 10.5 version 3 node galera cluster, on node 1 physical backup is running/working but on node 2 and 3 physical backup script is not working and getting error.....
mariabackup --backup --...
0
votes
1answer
41 views
MariaDB indices do not start at 1 after removing database and re-creating it
I am currently experimenting with a DB-Driven app. It automatically sets up its database-tables if its database does not exist, so it is very easy for me to drop the database and just start from ...
1
vote
1answer
41 views
How do I recover from Mariadb(10.4.18) binlog corruption
I have a 3 node Mariadb Galera cluster running Mariadb 10.4.18 on ubuntu 18.4.
I applied updates to one of the nodes this morning and now Mariadb doesn't start on that node with the following error.
[...
0
votes
2answers
26 views
I have a correct subquery that returns results but when I use it as an “IN” clause I get error 1064
This must be a very simple problem, the subquery returns results alone but the compound query reports:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to ...
0
votes
1answer
21 views
LEFT and RIGHT JOIN multiple table, show all null data on mariadb 10.3
i want to join 3 table with null value,
here i have 3 table folowing
tbl_reporting tbl_criteria tbl_student
|===============================| |...
0
votes
0answers
22 views
What is the SELECT statement equivalent of SHOW ALL SLAVES STATUS?
The following SQL query in MySQL outputs, in my case just a single row, but with many fields/columns.
SHOW ALL SLAVES STATUS;
However I need a query that will output just one of the fields.
Is there ...
0
votes
2answers
34 views
Eliminating effectively-redundant entries with Inner Join [closed]
The problem that we are trying to solve can be explained with the following illustrative example.
We have the following two tables:
CREATE table moniker_tbl
(
id INT UNSIGNED AUTO_INCREMENT,
name ...
0
votes
1answer
11 views
Variation on greatest-n-per-group where whole table is needed with indicator, not just greatest rows per group
I have a query that returns some rows having a grouping on a column and dates. I would like to have that query indicate in a column which of each group has the greatest date.
There are a vast number ...
0
votes
2answers
52 views
LEFT JOIN subquery fails in the presence of an EXISTS clause in the main query
I have this query:
SELECT c.id contact_id, engagement.sent
FROM contact c
LEFT JOIN (
/* ➊ Inner query */
SELECT eq.contact_id contact_id, COUNT(*) sent
FROM mailing_job j
INNER JOIN ...
0
votes
1answer
25 views
Galera - why can't I use port 4567 for replication?
I have two Galera clusters in two different datacenters.
I want to replicate cluster c1(using c1-1 node as replication master) to cluster c2 (using c2-1 node as replication slave). Everything works ...
0
votes
2answers
63 views
Comparing Query Performance Across DBMSs
I have a list of queries that I want to compare the execution time of across 3 different DBMSs: SQLite, MariaDB and postgreSQL.
I have been looking into command line tools for each system to get the ...
1
vote
1answer
34 views
Can large data deletion affect Innodb buffer pool performance? [duplicate]
At the beginning of this year we lunched a lot of massive data deletions (millions of rows per week) in our production database. Also, there is an automatic integration of new data (insert/updates) of ...
0
votes
1answer
42 views
backup and restore app for databases [closed]
I'm looking for an open source app to backup sql server / mariadb / postgres.
Do you know which app can do that ?
I want to schedule backups with the app.
yes it's possible with only one app . I used ...
0
votes
0answers
24 views
Exact meaning of “[ERROR] InnoDB: The age of the last checkpoint is NNN, which exceeds the log capacity NNN”
My understanding is that InnoDB will write transactions to the redo logs, and will run out of space in the redo logs if dirty pages exist in the buffer pool with LSNs lower than the lowest LSN ...
0
votes
0answers
16 views
How to query with maximum number of rows with same value?
In Mariadb 10.5.8
I have a table with some repeating value e.g.
Item , qty
shoes 3
pants 2
pants 2
pants 4
shoes 3
pants 2
shoes 6
pants 2
shoes 3
pants 2
I want to query up to 3 occurrences of each ...
0
votes
1answer
15 views
How can I disable swappiness just for Mariadb and not for other applications on the same EC2 Instance?
I have an EC2 instance where DB(Mariadb) runs on the same EC2 instance as the other applications.
MySQL documentation suggests to disable swappiness since DB doesn't like swap. How can I disable ...
0
votes
2answers
52 views
MariaDB: Insert records (list of tuples) into table if not exists
Currently I have several sql queries that look like this:
INSERT INTO `test` (col1, col2)
SELECT * FROM (SELECT '1','2') AS tmp
WHERE NOT EXISTS (
SELECT * FROM `test`
WHERE col1='1' AND col2='...
0
votes
1answer
26 views
Do MySQL limitations apply to MariaDB as well on AWS RDS
I am thinking of migrating MariaDB 10.3.13 to RDS, I want to do an analysis of what limitation can be caused by migrating to RDS, I could find no documentation by AWS for MariaDB for issues and ...
0
votes
1answer
26 views
mysqldump: Got error: 2002: “Can't connect to MySQL server on 'x' (code)” when trying to connect
I am trying to create a back up from a server by using XAMPP (via command prompt).
Unfortunately the error is: mysqldump: Got error: 2002: "Can't connect to MySQL server on 'x' (10060)" when ...
0
votes
2answers
26 views
How to find unused columns in MariaDB tables
I have a lot of tables in my DB that have a number of columns which might not be getting used as they slowly lost their relevance with time, I need to find such columns and delete those from the ...
0
votes
3answers
33 views
Determine theoretical max connections for MariaDB
I want to upgrade the ram our MariaDB server (10.1.25-MariaDB-1~xenial) to handle more connections.
I have been determining the max connections mostly based on this article which suggests to use this ...
0
votes
1answer
19 views
How can change the default authentication method in MariaDB / MySQL
I'm trying to set the default authentication method to unix_socket.
Following the instructions here:
https://lindevs.com/change-default-authentication-plugin-in-mysql/
I added to my.cnf :
[mysqld]
...
0
votes
0answers
19 views
How to use a query to get the definition of (MariaDB 10.3.27) database objects (specifically the variables they use)
I have written a query to get most of the useful information about the routines in a MariaDB database for a database documenter that I am developing.
The code below will return routine name, routine ...
