Questions tagged [mariadb-10.5]
The mariadb-10.5 tag has no usage guidance.
70
questions
1
vote
3
answers
56
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,271
0
votes
0
answers
21
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,271
0
votes
1
answer
22
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,271
1
vote
0
answers
19
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,271
1
vote
0
answers
49
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 ...
1
vote
2
answers
30
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,271
0
votes
1
answer
18
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,271
0
votes
1
answer
26
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 ...
- 137
0
votes
0
answers
26
views
Can I mix Galera-3 and Galera-4 nodes?
Say I have three servers
node1 192.168.100.201
node2 192.168.100.202
node3 192.168.100.203
on node1 and node2 is running mariadb 10.5 and on node3 still 10.1 and I need that version there still, but ...
- 101
0
votes
0
answers
18
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,271
1
vote
0
answers
137
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
134
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,271
0
votes
0
answers
19
views
How do I write MariaDB query results to several different outfiles on client side?
I have MariaDB server and client 10.5.12 both running on the same Debian 11 PC.
I am running several SQL queries from a file that I pass to the MariaDB shell client:
mysql < autoreport.sql
I would ...
- 1
0
votes
1
answer
54
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 ...
- 634
-2
votes
1
answer
264
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
-2
votes
1
answer
85
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
186
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
55
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
30
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,271
1
vote
1
answer
52
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 ...
- 634
0
votes
1
answer
55
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
268
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 ...
- 634
0
votes
2
answers
112
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,271
0
votes
1
answer
28
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,271
1
vote
1
answer
67
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,271
1
vote
1
answer
52
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,271
1
vote
4
answers
215
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,271
0
votes
0
answers
27
views
MariaDB: User password gets reset even after ALTER USER command fails
On MariaDB 10.5.10.
I create a DB user with a complex password. Then I change the password to say 1 char, it throws an Error 1396 but resets the password to NULL.
So now I can login as the user ...
- 21
0
votes
1
answer
397
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 ...
- 634
0
votes
1
answer
32
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,271
1
vote
1
answer
220
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
253
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,271
0
votes
1
answer
253
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 ...
1
vote
0
answers
48
views
JOINing two mapping tables
I have two mapping tables as
CREATE TABLE ProjectDevelopers
(
DeveloperID int(11) unsigned NOT NULL,
ProjectID int(11) unsigned NOT NULL,
INDEX(ProjectID),
PRIMARY KEY(DeveloperID,ProjectID)
) ENGINE=...
- 4,271
1
vote
1
answer
120
views
Creating MEMORY table with varchar in MySQL
I need to create TEMPORARY tables for processing (via JOIN) a large set of data (1M rows), which have varchar columns. If I use ENGINE=MEMORY, it will change varchar to char. Then, the data will not ...
- 4,271
0
votes
2
answers
45
views
How to determine if I have an undersized innodb_buffer_pool for MySQL
We have several MySQL 5.7/Mariadb databases running on Ec2 instances on CentOS. Our current logic for allocating memory to the DB is based off total memory available on the OS and a percentage is ...
- 21
0
votes
1
answer
85
views
Histograms in MySQL
We have several Linux based On-prem Mariadb 10.5.8 DB. Size is about 1 TB each.
Currently we don't have any script that does analyze table to gather statistics on our DB. It's done automatically based ...
- 21
0
votes
1
answer
239
views
Wait state: Statistics
We have 200 databases in one instance of MariaDB with a total of 370.000 tables, since we upgraded to MariaDB 10.5.11, we see most of time passed by mysql is in wait states: Statistics.
According ...
0
votes
1
answer
33
views
how do I restrict a specific row's columns to have only a specific value set?
I have the following table
CREATE TABLE `mview_state` (
`state_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'View State ID',
`view_id` varchar(255) DEFAULT NULL COMMENT 'View ID',
`mode` ...
- 149
0
votes
1
answer
23
views
List sorted Posts on Group and children Groups efficiently
Say I have a system that has Groups and Posts on those Groups.
A Group can have multiple "child Groups"
Group 1 > Group 2 > Group 3
Group 1 > Group 2 > Group 4
Group 1 > ...
- 634
2
votes
1
answer
275
views
MariaDB Galera Arbitrator failing to sync with SSL
So the scenario is this. I have already setup a 4 node MariaDB Galera cluster (10.5). I'm also adding on top a 5th Galera Arbitrator and everything syncs and connects just fine. However when I enable ...
- 147
0
votes
1
answer
425
views
How to Prevent High MariaDB Memory Usage on Windows
I am running MariaDB 10.5 and I am having trouble with it using too much memory on my Windows 10 machine. I have 16GB of RAM, and MariaDB ends up using around 8GB.
SHOW VARIABLES; https://pastebin.com/...
- 1
1
vote
1
answer
184
views
slow disk with InnoDB page compression
I have a write-intensive MariaDB with both NVMe SSD and HDD disks. I recently enabled page compression (innodb_compression_default=ON). I encountered two problems:
The database gets slow after a ...
- 4,271
0
votes
0
answers
394
views
HeidiSQL, how to export user definition
I am using MariaDB version 10.5.9.0 on Windows version 10.0.19041.928 with HeidiSQL version 11.0.0.5919
I have exported the database structure which includes the tables, stored procedures and data. I ...
- 149
0
votes
2
answers
292
views
Stored procedure prepared statement no error and not working [closed]
Here is my stored procedure:
BEGIN
#Procedure: insertMetaTagIfNotPresent
#Parameters: bkID ID to find or NULL to create automatically
# vcTag Tag name to assign, must be ...
- 149
0
votes
1
answer
92
views
MariaDB optimizing geo IP lookup with Maxmind GeoLite2 database
I have the following two tables that I ingest with data from Maxmind's GeoLite2 Country database:
CREATE TABLE `country_location` (
`geoname_id` int(11) UNSIGNED NOT NULL,
`locale_code` varchar(...
- 1
0
votes
1
answer
314
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 ...
- 21
0
votes
1
answer
24
views
need help building a query to detect duplicates on multiple columns
I have the following table
create table catalog_product_entity_varchar
(
value_id int auto_increment comment 'Value ID'
primary key,
attribute_id smallint unsigned default 0 not ...
- 149
3
votes
2
answers
2k
views
Having charset "utf8mb4" - does it mean every character takes 4 bytes, or only those that need 4 bytes?
I have a VARCHAR(80) utf8mb4 column, and I'm testing adding some ASCII & Emoji characters, and use LENGTH() and CHAR_LENGTH() to understand the differences.
From reading in different places, my ...
- 634
0
votes
1
answer
76
views
Does the collation of ENUM columns impact their size?
What does it mean when we say that an ENUM has a charset of utf8mb4, versus latin1?
My understanding is that the ENUM holds a numeric index (1, 2, 3, 4...) that identifies which of the ENUM values (...
- 634