Questions tagged [mariadb-10.6]
This question is related to mariadb-10.6. Also include the mariadb tag to get the attention of general mariadb helpers.
33
questions
1
vote
1
answer
27
views
MariaDB: inconsistent SELECT results involving JSON
When executing the following statments on MariaDB 10.6.14
SET @json_1 = JSON_OBJECT('id', 'name');
SET @json_result_1 = JSON_OBJECT('test', @json_1);
SELECT @json_result_1;
SET @json_result_2 = ...
0
votes
2
answers
49
views
INSERTing millions of files into a table
I archive millions of XML files (1-100MB each) in a table with the structure of
CREATE TABLE Data
(
ID int(11) unsigned NOT NULL,
XML longtext COMPRESSED,
PRIMARY KEY(ID)
) ENGINE=Aria ...
- 4,491
0
votes
1
answer
396
views
how can I view deleted records in an ibd file?
I'm in a situation with a wordpress site where a table wp_options keeps increasing in filesystem size really fast even though it only has 10000 records and we have to constantly optimize it to reduce ...
- 149
1
vote
0
answers
15
views
MariaDB node replaces client request for host 127.0.0.1 with localhost
This seems very strange:
I have a MariaDB Galera cluster with three nodes, and each node has virtually the same configuration.
Since it's a Galera cluster, the database users are the same across all ...
- 6,261
1
vote
1
answer
79
views
Cannot restore MariaDB dump after upgrade from 10.6.10 to 10.6.15
I have just updated MariaDB server from 10.6.10 to 10.6.15 on my development machine, and trying to restore a previously working SQL dump now fails.
Here is an example of failing query:
CREATE TABLE `...
- 167
0
votes
1
answer
462
views
Tips for improving MariaDB performance and tuning queries
I'm currently working on optimizing the performance of my MariaDB database and have come across a tool called mysqltuner. I've installed and run mysqltuner on my server, and it provided me with some ...
0
votes
0
answers
243
views
Please guide upon optimize my.cnf of MariaDB 10.6.14 on Wordpress Woocommerce site
Before post, I read post at InnoDB tables in an 8GB RAM server: Are these settings optimized?, but it seems not fully relevant to my case. It would be great if any MariaDB expert could guide me based ...
- 1
2
votes
1
answer
51
views
Inefficiency of index in SELECT requests after large INSERT requests on myisam tables on MariaDB 10.6
This is my first post on this website. Please don’t hesitate to indicate me if there is any problem in my post.
I have a new issue when migrating from MariaDB 10.1 to MariaDB 10.6 on indexes with ...
- 21
1
vote
1
answer
516
views
MariaDB replication server's SQL Thread stuck at 'Waiting for prior transaction to commit'
Replication hangs often at a one Master - One Replica setup.
From processlist the SQL worker threads seems to wait indefinitely with 'Waiting for prior transaction to commit' status.
Once this happens ...
- 13
0
votes
3
answers
152
views
Can't set MariaDB table_open_cache greater than 200 in my.cnf
We have MariaDB 10.6.13 running on an Almalinux 8 server. In /etc/my.cnf, we have specified table_open_cache = 1024. However, when we run show variables like 'table_open%'; it shows 200 for ...
- 167
0
votes
0
answers
60
views
(How) can I recover a lost .par file if data files are still there?
Main Problem
After some critical maintenance work of one of our dev environments, we somehow lost both the .frm file and the .par file for a data-critical partitioned table (of over 1TB).
However, all ...
- 101
1
vote
0
answers
130
views
Replication is slow after upgrading from mysql 5.6.17 to mariadb 10.6.12
I was using mysql 5.6.17 and upgraded to mariadb 10.6.12 due to EOS.
After installing mariadb on the same server, change the basedir to run mariadb.
After that, I ran the mariadb-upgrade tool to ...
insim
0
votes
2
answers
58
views
Why making temporary table is faster than multiple WHERE conditions?
I have a table like that:
CREATE TABLE Sessions (
SessionId int(11) NOT NULL AUTO_INCREMENT,
ExternalId int(11) NOT NULL,
Active bit(1) NOT NULL DEFAULT b'0'
) ENGINE=InnoDB DEFAULT CHARSET=...
- 215
1
vote
2
answers
459
views
Error with SQL DELETE statement with MariaDB
I have a multi-database application that has a query that is giving me execution problems with MariaDB 10.6.12 (Windows).
The same query works perfectly when run against MYSQL 8.0.27
However, when it ...
- 115
0
votes
0
answers
108
views
MariaDB: Syntax error in SET variable statement
I have a MariaDB transaction in which for the life of me I cannot see the syntax error:
START TRANSACTION;
SET @config_name='test';
SET @config_version = (SELECT IFNULL(max(`Version`)+1,0) FROM `DEV`.`...
- 3
0
votes
1
answer
296
views
Optimize MariaDB 10.6
I am not a database guru.
But here's the matter with which I'd like some help.
I have a relatively freshly-minted server running AlmaLinux 8.7, with 64GB RAM and a pair of terabyte SSDs.
After ...
1
vote
0
answers
141
views
Create PIVOT table with MariaDB's CONNECT engine
Given the following base table, which implements a sort of Entity-Attribute-Value table
CREATE OR REPLACE TABLE dipendenti_detail (
id_dipendente int(10) unsigned DEFAULT 0,
id_azienda int(10) ...
- 167
1
vote
1
answer
114
views
Is there any configuration not to use index merge in mariadb, if a cardinality of an index is below than that?
There is a table have 2 indexes, called state and CATEGORY, which has a very low cardinality respectively 4 and 24 within 7,110,590 rows in the table.
When running query of select statements that ...
- 111
0
votes
1
answer
904
views
MariaDB writing transaction and rollback - novice
Currently I am learning how to write transactions for MariaDB (I got a version 10.6 running).
I attempt to execute several commands and based on their success to commit or rollback.
So far I have ...
- 3
1
vote
1
answer
183
views
Occasionally Need to OPTIMIZE TABLE after table creation -- MariaDB 10.6.11
I transfer tables from one server to another by running the CREATE TABLE statement without any indexes. Then I batch insert all of the data into the table. Then I run an ALTER TABLE to add the indexes....
- 131
0
votes
1
answer
144
views
MariaDB: Limit changes query plan and makes query very slow
It's counter-intuitive and weird to see how adding "LIMIT 2" changes whole plan for MariaDB (version 10.6.7) from using one index and having total time of 2 seconds into another index with ...
1
vote
1
answer
2k
views
When do you need to run Analyze Table on MariaDB?
We have migrated a few large databases from earlier version of 10.1.22 to 10.6.7 - the latest provided by ubuntu repo for 22.04. All was well for a few weeks and then we started seeing very slow ...
- 11
0
votes
1
answer
3k
views
MariaDB 10.6 join query 10 times slower than 10.3
I have two VPS servers (LEMP) running Wordpress sites. Specs:
Old
4GB RAM
Ubuntu 18.04
MariaDB 10.3
New
8GB RAM
Ubuntu 22.04
MariaDB 10.6
I migrated a site and found that it was around 10 times ...
- 1
0
votes
0
answers
109
views
MariaDB SELECT query keeps stuck in one system but not in another
This is the reduced query showing problem:
SELECT
t.*
FROM
transactions t
LEFT JOIN assignments a ON a.transaction_id = t.id
LEFT JOIN users u2 ON u2.id = a.user_id
WHERE (
u2....
- 103
3
votes
1
answer
237
views
How to allow window function in window specification?
I'm trying to run this query:
SELECT uuid,
brainffa - LAG(brainffa, 1) OVER (ORDER BY brainffa) as brainffa
FROM time_played
WHERE automatic = 1 AND name LIKE 'all%'
ORDER BY uuid DESC ...
- 215
0
votes
1
answer
199
views
Mariadb: Sometimes inserts made during mysqldump are stuck forever in Updating state, and then service mariadb restart never finishes - how to debug?
I have a simple innodb Mariadb database used for logging (only inserts with insert ignore into a single large table).
I back up the database nightly with mysqldump --no-create-info --hex-blob --...
- 101
0
votes
2
answers
1k
views
MariaDB 10.6 issue with blocked queries that can't be killed
We have AWS RDS database, MariaDB 10.6.8, and we are facing some issues with queries being blocked, the main issue is that queries would lock the table and this would cause more blocked queries:
show ...
0
votes
2
answers
207
views
MariaDB 10.6 InnoDB setttings for unit-tests: maximize performance at the expense of persistence
I have a MariaDB 10.6 the db is used for phpunit tests.
The database is dropped and recreated each time I run the unit tests.
I would like to maximize the speed of the interactions (read/write) with ...
- 151
0
votes
1
answer
59
views
MariaDB - IODKU, but only if condition met
I have an often offline dev db, and would like to be able to push updates to my live server in the most efficient way possible. I would prefer not to update tens of thousands of rows that have not ...
- 1
0
votes
0
answers
295
views
INSERT .. ON DUPLICATE KEY UPDATE and INSERT IGNORE transaction issue
I have a reproduction where INSERTing a row doesn't actually seem to insert them, if you follow a transaction.
Reproduction using docker (I've reproduced on 10.5 and 10.6):
docker run -d -e ...
0
votes
1
answer
47
views
How to convert a string year to a DATE
STR_TO_DATE doesn't work like I expect.
STR_TO_DATE('2007', '%Y')
STR_TO_DATE('2007', '%y')
2006-11-30
2019-11-30
10.6.7-MariaDB-1:10.6.7+maria~bullseye-log
- 21
0
votes
1
answer
617
views
How to reduce the size of file in innodb_data_file_path (ibdata1) in mariadb without recreate db
I have db with innodb tables and use innodb_file_per_table
After
OPTIMIZE TABLE `db`.`table`
of all tables the size of db on disk reduced (from 3.3G to 2.8G)
But size of file ibdata1 is the same (...
- 141
-1
votes
1
answer
81
views
mysql vs mariadb query [closed]
Hope i don't make any mistakes posting here :)
Hi everyone,
i'm using the following query on mysql and works perfect. But when i use the same query (same database) on a mariadb i'm getting wrong ...
- 3