Questions tagged [mariadb-10.5]
The mariadb-10.5 tag has no usage guidance.
35
questions
0
votes
1answer
57 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
1answer
19 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` ...
0
votes
0answers
10 views
Procedure that used to work with 0 lock timeout doesn't work anymore
I reinstalled my database, and it seems something changed in mariadb 10.5.11 that this settings are not applied anymore in the configuration file?
innodb_table_locks = 0
innodb_lock_wait_timeout = 0
...
1
vote
1answer
43 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 ...
1
vote
1answer
45 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 ...
0
votes
0answers
54 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 ...
0
votes
2answers
30 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 ...
0
votes
1answer
41 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(...
0
votes
0answers
41 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
1answer
48 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
1answer
22 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 ...
0
votes
0answers
19 views
master-master broke because error code=1451 ;
I have a master-master replication setup using MariaDB 10.5.8 regarding replication I have these common settings in both servers:
log_slave_updates = 1
gtid_strict_mode = 1
...
2
votes
2answers
562 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 ...
0
votes
1answer
21 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 (...
0
votes
1answer
189 views
Cannot use GRANT ALL anymore on RDS MariaDB 10.5 but can on 10.2
Using the default admin user I created on a new RDS instance with MariaDB engine 10.5 I cannot use the GRANT ALL anymore and instead get "access denied". This works fine on the 10.2 instance ...
1
vote
2answers
141 views
Update Column from Recursive CTE (MySQL/MariaDB) [closed]
I need to update the same table I'm using to create a recursive cte. The cte works as a select statement, but I can't get it to actually insert those values back into the table.
My code is below- ...
1
vote
1answer
29 views
Is CHARSET important for tables with INT only?
My server default CHARSET is UTF-8 (set in my.cnf). I create tables like
CREATE TABLE t1
(
ID int(11) unsigned NOT NULL AUTO_INCREMENT,
Title varchar(255),
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT ...
1
vote
1answer
83 views
mysqldump select queries appearing in slow query log after upgrade
We recently upgraded from MariaDB 5.5 to MariaDB 10.5
Since doing so, we have a couple of select queries appearing in the slow query log when we backup databases using mysqldump.
One of the tables is ...
0
votes
2answers
54 views
SELECT with where between not working as expected
I'm trying to use this query, to filter between the names C89 to C110.
SELECT id FROM servers WHERE server_name BETWEEN 'C89' AND 'C110'
But this doesn't work. If I change c89 to c90, it at least ...
0
votes
0answers
63 views
MariaDB low CPU usage, high load average, spiky load
I have been trying to figure out, what could possibly cause very strange spiky load on a DB server. I am running Ubuntu 20.04LTS and mariaDB 10.5.7. 8CPUs, 16Gb RAM. There are 2 separate APIs ...
0
votes
3answers
269 views
Server with mariadb sometimes dies under high load
I have a dedicated server i7-4770 with 32GB RAM. On server installed CentOs 7.8, MariaDB 10.5.8, PHP 7.4.14, Nginx 16.1. All tables in the database are InnoDB.
When the load on the database increases ...
1
vote
2answers
1k views
High CPU usage, low memory usage on Mariadb 10.5.8 — SQL tuner complaining about memory, performance seems fine
I'm hoping to get some friendly advice how to proceed with optimizing this server.
VM:
8vCPU - 2.6GHz
32GB mem
Disk speed
~3Gbps (380MBps)
Application is Librenms
my.cnf.d/server.cnf -- Built from ...
0
votes
0answers
40 views
MariaDB doesn't read all options from /etc/mysql/conf.d/ after upgrade
I am using Ubuntu 20.04LTS. I upgraded Mariadb 10.5.5 to 10.5.8 using mariadb's own repositories. After upgrade I noticed API server was unable to connect to DB server through private network. After ...
1
vote
2answers
489 views
Troubleshooting high memory usage on MariaDB 10.5.4
I am experiencing very high memory usage on MariaDB server. I am not sure if it is due to misconfiguration or a bug.
Server: MariaDB
Server version: 10.5.4-MariaDB MariaDB ...
1
vote
1answer
160 views
Can I upgrade MariaDB replication infrastructure gradually?
I have 2 masters and 1 slave (replicating form both masters).
M1- MariaDB 10.4.11
M2- MariaDB 10.4.16
S1- MariaDB 10.4.12
I want to upgrade this LIVE system to 10.5 with possibly 0 downtime. The idea ...
1
vote
2answers
47 views
How to order by COUNT(*) of unique entries in GROUP BY?
I search through a tag_map as
SELECT ArticleID,COUNT(*) AS C FROM WordMap
WHERE WordID IN(
SELECT WordID FROM Words WHERE Word IN ('word1', 'word2')
)
GROUP BY ArticleID
ORDER BY C DESC
I want to ...
0
votes
1answer
19 views
Cannot find aria_s3_copy utility in mariadb 10.5.5
I am not able to find the Aria utility - aria_s3_copy under bin. I use mariadb official docker image and I do find aria_chk but not aria_s3_copy.
Can you tell me where I can find it?
0
votes
0answers
72 views
Can Aria table be changed back to InnoDb with foreign key constraints enabled?
Question is, I am aware that Aria Engine is non-transactional and does not support foreign key, but imagine there is a scenario where I copy in data from S3. Copying in two different tables into ...
2
votes
1answer
141 views
MariaDB - Replication - Unable to login remotely
So I've been trying to configure MariaDB replication for our server. Unfortunately I cannot get it replicating correctly.
So I've followed the MariaDB guide to the letter to get replication set up. ...
1
vote
1answer
40 views
Why is it not a good idea to use MariaDB's extra_port for replication?
MariaDB has an extra_port for administrative access that helps in cases the thread pool or main port is locked up.
MariaDB's documentation says it's use case is primarily administrative access and it ...
1
vote
2answers
898 views
How can I use LOAD DATA LOCAL INFILE for specific columns?
I have a directory of text files, as the filename is the id, and the content text for this table:
CREATE TABLE texts
(
ID int(11) unsigned,
Added date,
Text MEDIUMTEXT,
PRIMARY KEY(ID)
) ENGINE=InnoDB ...
1
vote
1answer
361 views
InnoDB Buffer pool not showing as much I configured in MariaDB 10.5
I'm using MariaDB 10.5 on Centos7 with 4G RAM and 2 CPU.
Here's my.cnf configuration
[mysqld]
log-error=/var/lib/mysql/mysql-3.kannel.com.err
max_allowed_packet=1G
event_scheduler = ON
...
1
vote
1answer
19 views
How to JOIN a table with itself while using a sub-query?
I JOIN a table to itself as
SELECT t1.TagID AS TagID1
,t2.TagID AS TagID2
,COUNT(1)
FROM TagMap AS t1
JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID
GROUP BY ...
0
votes
1answer
27 views
How to COUNT co-tags from a tag_map table in mysql?
I have a simple tag_map table
CREATE TABLE TagMap
(
TagID mediumint(7) unsigned,
ArticleID int(11) unsigned,
FOREIGN KEY(TagID) REFERENCES Tags(TagID) ON DELETE CASCADE,
FOREIGN KEY(ArticleID) ...
0
votes
1answer
30 views
How to add all combinations of two pairs of key-value to a table?
I have a database of items, as each item has a set of key-value pairs such
place => london
field => engineering
since I frequently need to find items having the same two key-value sets. I ...
