Questions tagged [mariadb-10.5]
The mariadb-10.5 tag has no usage guidance.
88 questions
0
votes
0
answers
11
views
mariadb: why does altering a table column from unsigned to signed int (or the reverse) require the COPY algorithm?
I have database table with a tens of millions of records. For some reason the auto increment id column is a signed int and I tried to change it to an unsigned int. Goes without saying but I checked ...
- 1
0
votes
1
answer
91
views
Migrating mariaDB database to new server and new mariaDB version, and with raw innoDB partitions
I have a linux server running mariaDB-5.5; the tables are all (I think) innoDB tables and the data are stored on a raw partition; actually on two raw partitions because some years ago at the last ...
- 101
0
votes
1
answer
81
views
Mariadb, installed via brew, does not run on startup
I have installed MariaDB via brew and each time I restart my computer I need to start it manually by
brew services start [email protected]
Also I cannot find mariadb listed in the "Login Items" ...
1
vote
2
answers
495
views
ERROR InnoDB: MySQL-8.0 tablespace in ./ibdata1
I am currently unable to restart [what I thought was] a previously working version of mariadb. Rocky Linux 8.6 server crashed during yum update and became unbootable. I installed Rocky Linux 9.3 and ...
- 21
1
vote
1
answer
189
views
Optimize MariaDB for Drupal 10 to handle more users [closed]
I have problems with my Drupal 10/MariaDB 10.5/PHP 8.1 website when they are 20 users simultaniously.
The website goes down and the response time is really big (up to 30seconds sometimes) because php-...
- 11
0
votes
0
answers
25
views
Does MariaDB-Galera cluster have error tolerance function on sst_user credential?
We have done the testing and confirmed the following system behavior of MariaDB-Galera clustering:
The first-time bootstrap always needs to get everything correct, including the sst_user credentials ...
- 23
0
votes
1
answer
327
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
68
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
188
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
25
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
1
vote
1
answer
74
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 ...
- 121
2
votes
1
answer
496
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
1
vote
0
answers
1k
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
3k
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), ...
- 181
0
votes
1
answer
102
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 ...
- 181
0
votes
1
answer
424
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
321
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
992
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
214
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
1
vote
0
answers
349
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
88
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
30
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
306
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
227
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,531
0
votes
0
answers
295
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,531
0
votes
1
answer
533
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,531
1
vote
0
answers
27
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,531
1
vote
0
answers
503
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,531
0
votes
1
answer
69
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,531
0
votes
1
answer
38
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,531
1
vote
0
answers
905
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
653
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,531
0
votes
1
answer
455
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 ...
- 839
-2
votes
1
answer
850
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
124
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
636
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
251
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,531
1
vote
1
answer
218
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 ...
- 839
0
votes
1
answer
96
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
1
vote
1
answer
1k
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 ...
- 839
0
votes
2
answers
493
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,531
0
votes
1
answer
31
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,531
1
vote
1
answer
171
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,531
1
vote
1
answer
109
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,531
2
votes
4
answers
2k
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,531
0
votes
1
answer
3k
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 ...
- 839
0
votes
1
answer
49
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,531