Questions tagged [mariadb-10.5]
The mariadb-10.5 tag has no usage guidance.
85
questions
0
votes
1
answer
15
views
io.debezium.DebeziumException: Client requested master to start replication from impossible position
I am using Kafka and Debezium to connect to a MariaDB
I have a problem where in the event the MariaDB loses power and shutsdown abruptly, on restart Debezium is no longer able to connect
I have this ...
- 1
1
vote
0
answers
40
views
Inconsistent result when running same query on slightly distinct MariaDB versions
I'm investigating the difference in the results given by the same SQL query on two servers running slightly different versions of MariaDB (10.5 and 10.11).
Test query is:
SELECT a.Name
FROM Artist a ...
- 111
0
votes
1
answer
73
views
Mariadb not using indexes post upgrade to 10.5.23 from 10.2
We upgraded our MariaDB server from 10.2 to 10.5.23 and have found a significant CPU spike after this and almost most of the queries landing in the slow query logs. On investigation, we found that ...
- 111
0
votes
1
answer
22
views
Odd Query Error in Mariadb
This query works without an error (I am running this query in HeidiSQL and 10.5.22-MariaDB)
SELECT concat(d.fname, ' ', d.Lname) AS Name,
d.EMail,
d1.Number
FROM orders d
LEFT OUTER ...
- 1
0
votes
1
answer
41
views
Slow CREATE TABLE AS followed by SELECT (other solutions haven't worked)
I have a problem which is similar to Slow CREATE table from subquery using SELECT .. INNER JOIN but the solutions provided there have not made any difference for me.
I'm using MariaDB 15.1 and have ...
- 111
2
votes
1
answer
143
views
Unique key allows duplicates
I have a database of members of an organization.
The data is really messy - the leader had a catastrophic medical issue, so I'm trying to make a sensible database out of Claris Filemaker Pro.
I'm ...
- 131
0
votes
0
answers
41
views
Does Incremental Mariabackup backs up a specified database or every node's databases
# Set the backup directory
backup_dir="/mnt/nas/tmp-storage"
# Set the MySQL/MariaDB backup user and password
backup_user="root"
backup_password="...
- 1
0
votes
0
answers
75
views
How to Take Two Different Full Backup From Mariadb With Restic
I have mariadb server. I take my backups with mariadb-backup tool and restic. I have a backup strategy. According to it, i need to take a full backup on every sunday at 00.00 am and need to take ...
- 1
1
vote
0
answers
261
views
SOLVED - Mariadb replication Master Slave fail with error code 1595
I've a strange problem.
A Mariadb replication start failing since this morning.
Until today everything works fine from month.
This is my scenario:
I've a server in a DC which is the master, and a ...
4
votes
1
answer
855
views
How to fix performance issue with row_number and multiple schema?
After receiving answer to How can I get a valid rank counter?, I adapted to my own system. But now, I have a performance issue. All of my requests are very fast (less than 0.0005s for most of them), ...
- 215
0
votes
1
answer
73
views
How can I get a valid rank counter?
I'm making a raking system. I want get the line in one request.
Here is a DB fiddle of my complete example: https://www.db-fiddle.com/f/3Com2wnrhaqfiTKqJSZtft/0
In the example, the UUID used is ...
- 215
0
votes
1
answer
157
views
Adding system versioning to an existing MariaDB table with partitioning by system time
I have an existing MariaDB table that I want to keep a history of changes to using the built-in system versioning functionality. I'm using MariaDB 10.5 but I have also tested everything in this ...
- 113
0
votes
1
answer
171
views
Mariadb upgrade problem
I upgraded mariadb on a Centos 7 box from 5.5 to 10.5 and after updating the tables, mariadb will no longer start. In /var/log/messages, I'm getting the following errors. Any help would be much ...
- 3
1
vote
1
answer
523
views
Why is MariaDB innodb_flush_method = O_DIRECT so much slower than fsync for SELECT count(*) as count? [closed]
I have a MariaDB 10.5.18 server with 64 GB of RAM running on an enterprise grade SSD.
I'm using a fairly generic my.cnf to try to figure out why innodb_flush_method O_DIRECT is so much slower than ...
- 11
0
votes
0
answers
123
views
What can produce slow down of mariadb
Our Mariadb database (1To) encounter slow down during some minutes but we don't find the root cause : there no CPU or IO overhead but locks OS dans lot of process taking time on sending data and ...
- 103
0
votes
0
answers
29
views
Huge number of sleep queries out of sudden makes mariadb server unresponsive
I've a master-slave-slave configuration behind a maxscale and at random times maxscale peaks with lots of connections and when I issue a show processlist I find a lots of sleep queries, I can't ...
1
vote
0
answers
221
views
Changing root and user passwords in MariaDB 10.5?
We have a few MariaDB servers (MariaDB 10.5, running on Debian 11/bullseye) where we need to change the passwords for the DB root user and for other user accounts in the DB system (by which I mean ...
- 121
0
votes
0
answers
68
views
mariadbd 10.5.18 seems to stop working
I've got a three node galera cluster (mariadb 10.5.18, ubuntu 20.04.5 LTS) with a haproxy as loadbalancer in front of it.
Normally, there are
up to 80 tcp based connection from a java application,
...
- 1
0
votes
1
answer
25
views
Ensure value is in list when inserting / updating
is it possible to ensure that a value is in a list of values defined in another table? For instance by using a check constraint?
I was thinking about a construct like this:
CREATE OR REPLACE TABLE `...
- 101
0
votes
1
answer
193
views
fixing GTID based multi source replication error on selected source
I can't find or think of a solution when ever i encounter db replication errors on affected source and repositioning the GTID in the replica.
What i do is retrieve from (four) masters gtid postion (...
- 1
1
vote
3
answers
158
views
Forcing InnoDB to build the secondary index
When INSERTing, InnoDB defers the building of secondary indexes. For example,
CREATE TABLE item_creators
(
item_id int(11) unsigned NOT NULL,
creator_id int(11) unsigned NOT NULL,
INDEX(creator_id),
...
- 4,491
0
votes
0
answers
186
views
speeding up JOIN by LIKE or REGEXP
I have two tables of
CREATE TABLE titles
(
title_id int(11) unsigned NOT NULL AUTO_INCREMENT,
title varchar(255),
INDEX(title),
PRIMARY KEY(title_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE ...
- 4,491
0
votes
1
answer
429
views
faster INSERT INTO InnoDB table with composite PRIMARY KEY
I have a simple tag_map
CREATE TABLE tag_map
(
article_id int(11) unsigned NOT NULL,
tag_id int(11) unsigned NOT NULL,
INDEX(tag_id),
PRIMARY KEY(article_id,tag_id)
) ENGINE=InnoDB
The table is quite ...
- 4,491
1
vote
0
answers
24
views
Finding sub-groups by relationships within groups
In this example, two tables define the groups and the relationship of persons within each group
CREATE TABLE groups
(
group_id int(11) unsigned NOT NULL,
person_id int(11) unsigned NOT NULL,
INDEX(...
- 4,491
1
vote
0
answers
402
views
What transaction isolation level prevents MariaDB from locking tables during a join?
I am using MariaDB 10.5.12. My query will join the tbl_cus table to a previous view, creating a new view. I am using MySQL Workbench with auto-commit enabled. During testing, the app connected to the ...
- 111
1
vote
2
answers
31
views
How do you check if two tags exists in a tag_map GROUP By article_id?
In a fairly simple tag_map,
CREATE TABLE tag_map
(
article_id int(11) unsigned NOT NULL,
tag_id int(11) unsigned NOT NULL,
INDEX(tag_id),
PRIMARY KEY(article_id,tag_id)
) ENGINE=InnoDB;
How can I ...
- 4,491
0
votes
1
answer
46
views
wrong index in composite PRIMARY KEY in MyISAM
I use MyISAM for processing tables, as it is much faster to read large data from MyISAM as compared with InnoDB for JOINs. I create map tables for bulk INSERT,
CREATE TABLE tag_map
(
article_id int(11)...
- 4,491
0
votes
1
answer
34
views
Find records with join matching all join IDs
Suppose to have the following database setup:
Users (
id int primary key,
name varchar(255)
)
OrgUnits (
id int primary key,
name varchar(255)
)
Users_OrgUnits (
user_id int,
orgunit_id ...
- 167
0
votes
0
answers
20
views
Deleting rows from a tag_map if parent tag exists
I have a simple tag_map
CREATE TABLE tag_map
(
article_id int(11) unsigned NOT NULL,
tag_id int(11) unsigned NOT NULL,
INDEX(tag_id),
PRIMARY KEY(article_id,tag_id)
) ENGINE=InnoDB
There is a table ...
- 4,491
1
vote
0
answers
688
views
Mariadb 10.5 FORCE INDEX can't be used with the PRIMARY key?
In a InnoDB table I can use any index with the FORCE INDEX or USE INDEX except for the PRIMARY one. (This is on RDS, if that matters)
ERROR 1176 (42000): Key 'id' doesn't exist in table 'mytable'
...
- 111
2
votes
2
answers
504
views
Finding gaps between the values of a column in MySQL
At a table of movie actors,
CREATE TABLE ArtistMap
(
ArtistID int(11) unsigned NOT NULL,
MovieID int(11) unsigned NOT NULL,
Year year,
INDEX(MovieID),
INDEX(Year),
PRIMARY KEY(ArtistID,MovieID)
) ...
- 4,491
0
votes
1
answer
314
views
Why is there a big gap in the actual Table space? (calculated vs information_schema vs file size)
I have a InnoDB table with the following columns:
BIGINT -> 8 bytes
BIGINT -> 8 bytes
ENUM(0,1) -> 1 byte
MEDIUMINT -> 3 bytes
INT -> 4 bytes
= 24 bytes per row
It contains 10454004 ...
- 809
-2
votes
1
answer
718
views
MariaDb performance issue
Using mysqltuner, we are facing performance issue with application using this db. We have physical memory of 128 GB. But this db is set to 16 GB. How can increase the memory and performance of this db....
- 1
-1
votes
1
answer
114
views
how to perform inner join on two table where i'am able to retrieve all data?
I need help with a MySQL query.
bar_opening_details has closing & opening stock
bar_recvd_details has received the stock
When I receive purchased stock, I insert it into bar_recvd_details. Next, ...
- 23
0
votes
1
answer
475
views
MariaDB Maxscale caching not working
I have setup Maxscale (v6.2) and have connected to the Galera Cluster (3 nodes - MariaDB 10.5). I am trying to use the cache filter but it seems to not work. I have enabled general log for all the ...
- 103
0
votes
1
answer
173
views
Replication from multiple masters to one slave
I have 2 Raspberry PI's and each run a MariaDB 10.3 server. Each has the same databases:
vennens
scheduler
On the slave I've configured the 50-server.conf file with follow contents:
master_pi1....
- 103
0
votes
0
answers
31
views
Faster JOIN using LIKE
I have a simple tag table as
CREATE TABLE Tags
(
TagID int(11) unsigned NOT NULL AUTO_INCREMENT,
Tag varchar(255),
ParentID int(11) unsigned,
Parent enum('Yes', 'No'),
UNIQUE INDEX(Tag),
INDEX(...
- 4,491
1
vote
1
answer
167
views
Filter out rows which the text on that current row is contained in the text of the following row
I wonder if it's possible, in a SELECT, to filter out rows which the text on that current row is contained in the text of the following row.
For example, image we log "searches" performed by ...
- 809
0
votes
1
answer
82
views
Why does an integer comparison result in no temporary index in MariaDB?
I am running a query on a table (transaction_items, c. 12 million rows) that has a many-to-many relationship with another table, and I wish to group transaction_items by this relationship.
To do this, ...
- 101
0
votes
1
answer
853
views
Subquery not using Index, even if forcing
Using MariaDB 10.5.12 on CentOS 7
If it makes any difference, my "optimizer_use_condition_selectivity" = 1
If I run:
EXPLAIN SELECT COUNT(*) FROM ips WHERE ip="1.2.3.4"
This query ...
- 809
0
votes
2
answers
376
views
Disabling Aria INSERT log in MariaDB [closed]
When I bulk INSERT into an Aria table, it creates a copy of the INSERT in a series of log files (aria_log.%). It can lead to full disk and crash of MySQL.
As I understand, Aria keeps a redo log for ...
- 4,491
0
votes
1
answer
29
views
Improving JOIN with sub-query
I capture Users who comment on the Articles of other Users, as
INSERT IGNORE INTO AuthorCommentators (AuthorID,CommentatorID,Freq)
SELECT AuthorID,CommentatorID,C FROM
(SELECT b.UserID ...
- 4,491
1
vote
1
answer
125
views
Speeding up SELECT FROM a large table
I have a large table (500M rows with ibd size of 1.5TB)
CREATE TABLE t1
(
ID int(11) unsigned NOT NULL AUTO_INCREMENT,
ChildID int(11) unsigned NOT NULL,
Number smallint(5) unsigned,
Title varchar(255)...
- 4,491
1
vote
1
answer
97
views
Monitoring MySQL/innoDB post-process
I did INSERT INTO SELECT of a huge table (1TB). When the process is finished, MySQL/InnoDB still does post-processing (creating updating secondary indices, etc.) for hours.
Since these are internal ...
- 4,491
1
vote
4
answers
1k
views
Tuning innoDB for a write-intensive machine
I have MariaDB 10.5 on my desktop with multiple disks (SSD and HDD) for write-intensive projects. Writing to a single table is fast and the percentage of dirty pages remains close to zero with 1000-...
- 4,491
0
votes
1
answer
2k
views
Suddenly, started getting "ERROR 2026 (HY000): Unknown SSL error"
MariaDB instance has been running well for ages, and suddenly, starting about 30 minutes ago, my apps get this error trying to connect:
# mysql
ERROR 2026 (HY000): Unknown SSL error
My certs have ...
- 809
0
votes
1
answer
45
views
How do you JOIN two tables as a combination of LEFT and RIGHT JOINs?
This is probably a stupid question. In two tables of
CREATE TABLE t1
(
id int(11) unsigned NOT NULL,
col1 int(11) unsigned NOT NULL,
PRIMARY KEY(id)
) ENGINE=InnoDB;
CREATE TABLE t2
(
id int(11) ...
- 4,491
1
vote
1
answer
549
views
MariaDB hidden disk usage for log dir
We have a simple master/slave cluster in our environment which is sets up on Ubuntu 20.04 and MariaDB 10.5:
MariaDB [(none)]> SELECT VERSION();
+--------------------------------------------+
| ...
0
votes
1
answer
1k
views
SELECTing n rows for each GROUP with WHERE condition
I SELECT n rows for each ArticleID from TagMap table
SELECT a.ArticleID,a.TagID FROM TagMap a
LEFT JOIN TagMap b ON a.ArticleID = b.ArticleID AND a.TagID < b.TagID
GROUP BY a.ArticleID,a.TagID
...
- 4,491
0
votes
1
answer
930
views
How to remove a innodb metadata lock with thread_id 0 in mariadb / mysql?
Today, suddenly our mysql database server got down. Few minutes after restarting the server went down again, and again. To see what is happening, we temporarily put our application in maintenance mode ...
