Skip to main content
Share Your Experience: Take the 2024 Developer Survey

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.

Filter by
Sorted by
Tagged with
2 votes
4 answers
122 views

Performance issues of a queue implemented on top of InnoDB

Brief problem statement First thing first: after posting this question originally and working more with our DBAs, I actually learned that our DB runs in a container, instead of being installed ...
Dmitry Frank's user avatar
0 votes
1 answer
22 views

Change MariaDB "ADMIN_OPTION" of a ROLE after deleting the user who created it

I have a problem in the prod : I have a user named dba who has all privileges with grant option, i used it to create a role named test, i granted some rights to the role test and granted the role test ...
Firas Megrahi's user avatar
0 votes
1 answer
19 views

MariaDB in the cloud....need some guidance

my plan is to move a mariadb into the cloud in germany. the clients will connect to it from 2 different office locations with 1gig/50mb up/dowload cable internet. Approx 10 clients will use the ...
kongootto's user avatar
0 votes
1 answer
19 views

MariaDB 10.6: How much undo does my query generate?

For reasons, I'm trying to work out just how much undo any single query generates. I know how to get this in Oracle DB, but don't know if it's possible in MariaDB/MySQL. Is it possible, and how? ...
Phil Sumner's user avatar
  • 1,877
0 votes
1 answer
40 views

How do I prevent ibdata1 from continually growing by over 1GB per hour?

Like most DBAs I have two copies of my entire system, one running on a commercial website provider and one on my development system. Both are identical: the same options, the same software level, ...
James Cobban's user avatar
1 vote
1 answer
63 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 = ...
Andreas Hinderberger's user avatar
0 votes
2 answers
57 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 ...
Googlebot's user avatar
  • 4,531
0 votes
1 answer
428 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 ...
gabtzi's user avatar
  • 161
1 vote
0 answers
19 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 ...
dbdemon's user avatar
  • 6,361
1 vote
1 answer
229 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 `...
Matteo Tassinari's user avatar
0 votes
1 answer
749 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 ...
Wiki wanda's user avatar
0 votes
0 answers
362 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 ...
alexlii's user avatar
2 votes
1 answer
61 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 ...
Tom Tom's user avatar
  • 21
1 vote
1 answer
863 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 ...
stackuser's user avatar
0 votes
3 answers
293 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 ...
Nathan Stretch's user avatar
1 vote
0 answers
160 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 ...
user avatar
0 votes
2 answers
62 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=...
Elikill58's user avatar
  • 221
1 vote
2 answers
663 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 ...
Marcello Picchi's user avatar
0 votes
0 answers
122 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`.`...
JRB's user avatar
  • 3
0 votes
1 answer
461 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 ...
Gene Steinberg's user avatar
1 vote
0 answers
190 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) ...
Matteo Tassinari's user avatar
1 vote
1 answer
167 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 ...
soredive's user avatar
  • 111
0 votes
1 answer
1k 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 ...
JRB's user avatar
  • 3
1 vote
1 answer
247 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....
Marshall C's user avatar
0 votes
1 answer
231 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 ...
Dmitriy Gorbenko's user avatar
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 ...
James Cross's user avatar
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 ...
mattf10's user avatar
0 votes
0 answers
120 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....
NeDark's user avatar
  • 103
3 votes
1 answer
341 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 ...
Elikill58's user avatar
  • 221
0 votes
1 answer
273 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 --...
Tomáš Kafka's user avatar
0 votes
2 answers
2k 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 ...
Hosni Mansour's user avatar
0 votes
2 answers
266 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 ...
nulll's user avatar
  • 151
0 votes
1 answer
63 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 ...
sgc's user avatar
  • 1
0 votes
0 answers
351 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 ...
Johan de Vries's user avatar
0 votes
1 answer
57 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
Mastax's user avatar
  • 21
0 votes
2 answers
726 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 (...
M-A-X's user avatar
  • 151
-1 votes
1 answer
82 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 ...
FeLoN's user avatar
  • 3