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 updating to MariaDB 10.6.x, I configured the my.cnf file to represent a prior server with a similar setup: # # This group is read both both by the client and the server # use it for options that affect everything # [client-server] # # include all files from the config directory # !includedir /etc/my.cnf.d [mysqld] sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION bind-address = 127.0.0.1 # skip-name-resolve=1 query_cache_size=0 query_cache_type=0 local-infile=0 innodb_buffer_pool_size=1024M query_cache_size=64M performance_schema=ON tmp_table_size=96M max_heap_table_size=96M unix_socket=OFF table_definition_cache=2400 key_buffer_size=1024M innodb_buffer_pool_size=3G innodb_log_file_size=750M max_connections=300 !includedir /etc/mysql/releem.conf.d Several days have passed, and I have run the MySQL Tuner. I'd like some guidance about what to change/add/remove form my.cnf based on these suggestions: We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found. See MySQL: Welcome (specially the conclusions at the bottom of the page). When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Variables to adjust: skip-name-resolve=1 query_cache_size (=0) query_cache_type (=0) query_cache_size (> 64M) join_buffer_size (> 256.0K, or always use indexes with JOINs) tmp_table_size (> 96M) max_heap_table_size (> 96M) table_definition_cache (2400) > 3113 or -1 (autosizing if supported) key_buffer_size (~ 195M) innodb_buffer_pool_size (>= 3.3G) if possible. NOTE: skip-name-resolve=1 produces a database error in our backup logs, which is why it is disabled. Thanks for any further assistance. I am obviously not a database guru.

I am using MariaDB 10.6.19. Below is my.cnf settings:

[mysqld]
performance-schema=0
max_connections=1500
max_user_connections = 1200
query_cache_size = 64M
skip-external-locking
key_buffer_size = 64M
max_allowed_packet=268435456
table_open_cache = 256
sort_buffer_size = 1M
net_buffer_length = 16K
read_buffer_size = 1M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 32M
innodb_file_per_table
tmp_table_size=64M
max_heap_table_size=64M
thread_cache_size = 8
query_cache_type = on

innodb_use_native_aio = 1
default_storage_engine = MyISAM
open_files_limit=40000
unix_socket=OFF

I use the following query:

SELECT ROUND( (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Created_tmp_disk_tables') / (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME='Created_tmp_tables') * 100, 2 ) AS 'Tmp_Tables_ToDisk%

and it turns 29.97%. Based on Claude.AI suggests, I increase the following settings:

tmp_table_size=64M -> 1280M (about 1% of total memory 128GB)
max_heap_table_size=64M -> 1280M

However, after restarting MySQL and wait for 23 hours, I recalculate Tmp_Tables_ToDisk% and get 32.56%, which is not improved. Claude.AI suggests to increase the tmp_table_size/max_heap_table_size more, so I change:

tmp_table_size=1280M -> 2560M 
max_heap_table_size=1280M -> 2560M

However, after 7 hours, Tmp_Tables_ToDisk% is 33.78. Still not improved. Why? Should I increase tmp_table_size/max_heap_table_size further more? **Update:** I try to enable slow query log and restart mysql, as below:

long_query_time=2
slow_query_log=1

After 6 hours, Created_tmp_tables is 58313 and Created_tmp_disk_tables is 25066, but there are only 16 queries in the slow log. Which means most of the queries that create temp tables are fast(< 2 seconds).

I have a question, and I've found one similar addressed, which I was going to respond to with my own questions (https://dba.stackexchange.com/questions/335192/live-database-migration-mariadb-10-5-to-mysql-8-0) , however, as that post/question is over a year old, I thought it may be better for me to create a new question. My Situation: I have a Web server (Apache) hosted by a 3rd party. The server is currently running MariaDB 10.5. On this server we have several sites, running on the last version or two of WordPress, and sites running Drupal 8, 9, and 10. Thus far, we've had no issues. However, we want to begin development of a couple projects on the Drupal 11 platform. When attempting to install D11, we're told that it requires MariaDB 10.6. I've made contact with our hosting company, and they've said that while they won't be upgrading our current server, they are willing to migrate us to another server, and we've been given the option to move to a server running MariaDB 10.6, or a server running MySQL 8. Being 100% honest, I know how to create the databases and from there, I've just let WordPress or Drupal do what they need to do. I've had no reasons to delve deeper into the Databases (Though this is an area that I'd like to learn more about). But for this situation, I'm not sure what is the better option to choose. Of course, objective Number One, would be to not break any of the currently running websites. I **presume** that migrating from the MariaDB 10.5 server to a MariaDB 10.6 server would be a "clean" move, but admittedly, I'm not positive of that. I'm also not sure what (if any) issues would be caused (or oppositely, benefits would be gained) migrating a MariaDB 10.5 website to a MySQL 8 server. I come to this forum, seeking your wisdom and direction. Thank you.

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 = JSON_OBJECT('test', JSON_OBJECT('id', 'name')); SELECT @json_result_2; I receive the following results: For @json_result_1: {"test": "{\"id\": \"name\"}"} And for @json_result_2: {"test": {"id": "name"}} How does it come that I retrieve different result? Aren't those JSON string supposed to be internally all treated as TEXT?

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 includes above 2 indexes in where clause, an optimizer try to index merge, Using intersect(state,CATEGORY), that is less efficient than even table scan, takes about 20 sec. Is there any way to except a index if the cardinality of the index is lower than a specific directed number when an optimizer is about to make a execution plan? The database is MariaDB-1:10.6.11 Community version But other databases which has lower version than previous mentioned database server, 10.3.28-MariaDB, handles the same query in 5ms. Servers of older version run a table scan using high cardinality index(date). All mentioned servers are same replicas of a source database with slightly different versions. Depends on difference of their version, their optimizers create query that have a lot of difference in performance. What factors could bring that difference? --- Full explain extended in newer version having low performance

id 1
select_type SIMPLE
table that_table
type index_merge
possible_keys CATEGORY,state
key state,CATEGORY
key_len 6,152
ref NULL
rows 1778388
filtered 100
Extra
Using intersect(state,CATEGORY); Using where; Using filesort

--- Full explain extended in old version having good performance

id 1
select_type SIMPLE
table that_table
type index
possible_keys CATEGORY,state
key DATE
key_len 62
ref NULL
rows 18
filtered 100
Extra Using where

--- Below is indexes on the table

# cardinality: 24, CATEGORY VARCHAR(50) DEFAULT '' NOT 
CREATE INDEX CATEGORY ON that_table (CATEGORY) ; 


# cardinality: 4, state VARCHAR(1) DEFAULT 'Y' NULL;
CREATE INDEX state ON that_table (state);

# cardinality: 7,110,590, DATE VARCHAR(20) DEFAULT '' NOT NULL;
CREATE INDEX DATE ON that_table (DATE);

--- The problematic query:

EXPLAIN EXTENDED
SELECT *
FROM that_table
WHERE category = 'blabla'
  AND state = 'Y'
ORDER BY date DESC
LIMIT 9;

--- A definition of table

CREATE TABLE that_table (
     UID int(10) NOT NULL AUTO_INCREMENT,
     state varchar(1) DEFAULT 'Y',
     CATEGORY varchar(50) NOT NULL DEFAULT '',
     DATE varchar(20) NOT NULL DEFAULT '',

     # .... other columns
     
     PRIMARY KEY (UID),
     KEY DATE (DATE),
     KEY CATEGORY (CATEGORY),
     KEY state (state),

     # ... other indexes
);

# 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 natively. From what I've read before, it's discouraged to do that in prod, since databases do all sorts of microoptimizations with memory and storage and cpu, and the container might obscure some things and so the behavior will not be optimal. So, if after reading this question you think it can be actually caused by the DB running in a container, definitely lmk. Alright so now to the actual question: ------- There will be a detailed explanation below, but this initial problem statement is just to give you a feel of what we’re dealing with here. We have a feature called “Tasks”, which is just a generic task runner. A task is represented by a single row in an InnoDB table (using MariaDB 10.6.14); anyone can create a task, and then one of the workers will pick it up, run, potentially retry a few times if there are errors, and once the task is finished (successfully or not), delete the row from the table. The issue is that, having plenty of tasks to run (even just 200K of runnable tasks which we need to run asap is enough), the performance alternates between those two modes: - Performance is great (the query to pick the next task takes 20-30ms, and every worker is able to run about 200 tasks per second or more) - Performance is terrible (the query quickly jumps to hundreds of milliseconds, and keeps slowly growing, and we’re running 1-3 tasks per second per worker). The “great” phase usually lasts for 5-10 mins, followed by a few hours of the “terrible” phase; then it fixes itself and goes back to “great”, etc. Here’s how it looks on the charts: A few quick points to highlight: - On this chart, no new tasks are being inserted in the table; we already have a few hundred K of tasks in the table that we need to run ASAP, and as we run them (and DELETE the rows from the table), the performance alternates like that. - It doesn’t seem to have anything to do with the amount of data in the table: as mentioned, even just 200K of rows is enough, which is nothing for the modern hardware (and yeah the hardware specs used here are very good). It’s rather just the velocity of changes that seems to be causing this: those 200K of rows represent the tasks that we need to run ASAP, and we do. When we have a much lower rate of tasks that we delete/update, those performance issues don’t happen. - It doesn’t seem to be caused by a wrong index: if there wasn’t a proper index, the performance would always be bad. But here it switches back and forth on its own, without us adding any new data to the table, so it seems to be some internal mariadb issues. This was just to give you a feel of what the issue is like. I have more to say on this, but before I do that, I feel I need to share more implementation details, so let’s get to it. # Background info and implementation details As mentioned above, we have a generic task runner. A task is represented by a single row in an InnoDB table; anyone can create a task, and then one of the workers will pick it up, take care of, and eventually delete the row from the table. There are two additional features worth highlighting: - A task can be scheduled either to run ASAP or after a specific time in the future; the only time-related guarantee here is that the task will not run before its scheduled time. We say that a task is runnable if we don’t need to wait more and can already run it whenever we can; - Every task has a priority: from 0 (highest) to 4 (lowest); having multiple runnable tasks on different priorities, the higher-priority tasks will always be picked first. Btw those features are a big part of why this was implemented on top of MariaDB, and not say Kafka or similar. It’s not a textbook use case of Kafka. The usage pattern can be very bursty: most of the time we might have like 10-20 tasks per second being created and ran ASAP, and it doesn’t cause problems; but then as part of some batch job we might create a few millions of low-priority tasks, and the workers will run them at the rate of 500 tasks per second in total. This is how the table looks like (there are a bit more fields, but they are not relevant to the problem, so are omitted)

CREATE TABLE tasks (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  -- Identifies which handler to actually run for this task. Think of it
  -- as the name of a function to call.
  name varchar(127) NOT NULL,

  -- Priority of the task; the highest priority is 0, and as the number
  -- increases, priority lowers. Currently, it can be from 0 to 4.
  priority tinyint(3) unsigned NOT NULL DEFAULT 2,

  -- Status of the task, one of those:
  -- - 0: PENDING: the task is ready to run asap or after next_attempt time.
  --      It can be the initial status that the task is created with, but the
  --      task could also reenter this status again later if it failed and the
  --      scheduler decided to retry it;
  -- - 1: PROCESSING: the task is currently being executed;
  --
  -- Note: there are no states for completed or failed tasks, because such
  -- tasks are deleted from this table.
  status tinyint(3) unsigned NOT NULL DEFAULT 0,

  -- Specifies the earliest time when the task needs to run next time.
  -- Used for tasks scheduled for the future, as well as for implementing
  -- retries with backoff. If NULL, the task should run ASAP.
  next_attempt timestamp(6) NULL,

  PRIMARY KEY (id),

  -- See details on this particular index ordering below.
  INDEX tasks_next_attempt_id_idx (priority, next_attempt, id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

There are a few workers (not too many: something like 3 or 6 etc) polling this table (see the exact query below); when a worker gets no task to run, it backs off and polls it only once per second, but when it successfully gets a task to run, it tries to get the next one right away, so that having many runnable tasks, we run them as fast as we can. The query to pick the next task does the following, atomically: - Find the id of the next task to run (taking into account the scheduled time and priorities as described above) - Set its status to 1 (which means “processing”) - Set the next_attempt to the current time plus 15 seconds (which is an interval after which the task will rerun if it appears dead; it’s not really relevant to the problem, but for the context: while the task is running, every 10 seconds its next_attempt will be again updated to 15 seconds into the future, so as long as the worker is functioning and the task keeps running, next_attempt will always be in the future) - Return the task id Here’s the exact query (where 2024-04-11 08:00:00 used as the current time: we actually pass specific timestamps like that instead of using NOW() for better testability, but it’s not related to the issue).

UPDATE tasks
INNER JOIN (
    SELECT id FROM tasks
    WHERE
            priority IN (0, 1, 2, 3, 4)
        AND (next_attempt IS NULL OR next_attempt = 0 AND priority  select count(*) from tasks;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.3 sec)

mysql> SELECT id FROM tasks WHERE priority IN (0, 1, 2, 3, 4) AND (next_attempt IS NULL OR next_attempt  EXPLAIN SELECT id FROM tasks WHERE priority IN (0, 1, 2, 3, 4) AND (next_attempt IS NULL OR next_attempt <= NOW()) ORDER BY priority, next_attempt, id LIMIT 1 FOR UPDATE SKIP LOCKED;                    
+------+-------------+----------+-------+---------------------------------------+---------------------------------------+---------+------+--------+--------------------------+
| id   | select_type | table    | type  | possible_keys                         | key                                   | key_len | ref  | rows   | Extra                    |
+------+-------------+----------+-------+---------------------------------------+---------------------------------------+---------+------+--------+--------------------------+
|    1 | SIMPLE      | tasks    | range | tasks_priority_next_attempt_id_idx    | tasks_priority_next_attempt_id_idx    | 9       | NULL | 748746 | Using where; Using index |
+------+-------------+----------+-------+---------------------------------------+---------------------------------------+---------+------+--------+--------------------------+
1 row in set (0.26 sec)

I also tried to SHOW TABLE STATUS, the important bits from there are:

Data_length:    45 092 864
Index_length:    7 393 280
Data_free:     827 326 464

After a few hours of being in that degraded state, it recovered on its own (without having any data being inserted), and those stats became:

Data_length:        4 096
Index_length:       4 096
Data_free:    879 755 264

I’m not a DBA and not sure what exactly these numbers mean though, but thought it’s worth sharing them here. I’m guessing it tells me that the index is full of garbage (7 MB of index on an empty table can’t have anything but garbage), and it must also be telling me that the index is not getting rebuilt when it should have. ## 2. If we don’t DELETE the tasks, but only UPDATE them to make them non-runnable, it doesn’t help with the performance I was just suspecting that the fragmentation (which is supposedly caused by deleting a lot of data from the table) might have something to do with it, so I just changed the logic to UPDATE the task after completion (set it to lowest the priority 5 which is never runnable) instead of DELETEing the row. It didn’t change anything about the performance; the patterns remained the same. ## 3. Doing a bunch of INSERTs helps As another observation, when we not only run tasks (and therefore DELETE them from the table), but also INSERT new tasks at about the same rate, then even though mariadb still switches to poor performance periodically, it recovers very quickly. Check it out: - So while we’re inserting tasks, it doesn't go into the degraded state for too long; - Instead, there is a distinctive pattern: seemingly every 4 mins, the performance drops, and then immediately recovers. - When we stopped creating tasks, it worked for a few minutes, and again fell into a degraded state for an extended period of time. I also confirmed that by just waiting for it to degrade, and then inserting a bunch of tasks, to check if it’d help it to recover. It helped every time. Empirically found that inserting 10K or 20K is not enough, but 30K of tasks is usually enough; the performance recovers right after inserting enough tasks: I guess it tells me that frequent INSERTs cause MariaDB to do something useful with the index, while frequent DELETEs and UPDATEs unfortunately do not. ## 4. Rebuilding an index helps If we just build a new index and drop the old one:

ALTER TABLE tasks ADD INDEX tasks_next_attempt_id_idx2 (priority,next_attempt,id), ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE tasks DROP INDEX tasks_next_attempt_id_idx;

It fixes the issue immediately, and doesn’t lock the table while building an index. Actually this is the most viable workaround we’re thinking of, at the moment: if we can’t find a way to make it happen automatically, we can just add some app logic like “if the next-task queries become slower than 100ms and stay this way for 5 seconds, recreate the index manually”; or even the lazy way like “rebuild the index every few minutes”. It sucks, and is generally a weird design to do ALTER TABLE in the app code, but practically it’s still much better than letting it be in the degraded state for hours. # Question I’d appreciate any thoughts and feedback you have based on the explanation above, but the questions I'm actually asking are: ## 1. What is happening? Really curious to learn some MariaDB implementation details which would explain this behavior, since it doesn’t make sense to me and I wasn’t able to find it on my own. ## 2. How to make it work fast without having to rebuild the index manually? As mentioned before, doing a bunch of INSERTs, like 30K or more, usually helps it to recover from the degraded state, so it looks like MariaDB does some maintenance to the index behind the scenes, and this maintenance doesn’t happen for DELETEs or UPDATEs. I wonder if there is some knob in MariaDB that we can tune to enable some more aggressive index maintenance, without having to rebuild it manually.

I was trying to upgrade my local development environment (Ubuntu 22.04 WSL) from MariaDB 10.6 to 11.4. After updating the APT repo file, I run and apt update, which updated the server version. However, upon running mariadb-upgrade, I see this error:

Phase 1/8: Checking and upgrading mysql database
Processing databases
mysql
mysql.column_stats                                 OK
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.event                                        OK
mysql.func                                         OK
mysql.global_priv                                  OK
mysql.gtid_slave_pos                               OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.index_stats                                  OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.roles_mapping                                OK
mysql.servers                                      OK
mysql.spider_link_failed_log                       OK
mysql.spider_link_mon_servers                      OK
mysql.spider_table_crd                             OK
mysql.spider_table_position_for_recovery           OK
mysql.spider_table_sts                             OK
mysql.spider_tables                                OK
mysql.spider_xa                                    OK
mysql.spider_xa_failed_log                         OK
mysql.spider_xa_member                             OK
mysql.table_stats                                  OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.transaction_registry                         OK
Phase 2/8: Installing used storage engines... Skipped
Phase 3/8: Running 'mysql_fix_privilege_tables'
ERROR 1062 (23000) at line 30: Duplicate entry 'db_626suite-get_98_53_metadata-PROCEDURE' for key 'PRIMARY'
ERROR 1062 (23000) at line 256: Duplicate entry 'db_626suite-get_98_53_metadata-PROCEDURE' for key 'PRIMARY'
ERROR 1062 (23000) at line 304: Duplicate entry 'db_626suite-get_98_53_metadata-PROCEDURE' for key 'PRIMARY'
ERROR 1062 (23000) at line 331: Duplicate entry 'db_626suite-get_98_53_metadata-PROCEDURE' for key 'PRIMARY'
ERROR 1062 (23000) at line 343: Duplicate entry 'db_626suite-get_98_53_metadata-PROCEDURE' for key 'PRIMARY'
FATAL ERROR: Upgrade failed

I do not understand how it is possible that there are duplicate functions, nor how to solve the problem. Since this is just a development environment, I simply tried dropping the database, but the error persists. Maybe the new version performs some checks that the old one didn't; but, how can I check for similar inconsistencies before running mariadb-upgrade in order to get a smooth update?

Hello Community Members, We have recently migrated our DB server to Azure from Onprem, and we are struggling with memory fragmentation in MariaDB Database. DB is consuming more than 2 times of buffer pool memory allocated. We see this fragmentation only on primary master. This behaviour is only observed when app connectivity via Proxysql to MariaDB. For sure the culprit is proxysql. We have one master with 2 standby databases in DB topology and 4 proxysql nodes one being hostgroup 1 and rest 3 with hostgroup 2. We have enabled the multiplexing, We initiall have heavy memory fragmentation, which got limited after changing the following variables in proxysql : During Migration : -------------------------- mysql-max_stmts_per_connection = 100; and max_prepared_stmt_count = 500000 Memory fragmentation was quite huge and we have to perform switch over the master database and bounce the DB post sswitchover to release the memory occupied by MariaDB. Post Migration : -------------------- mysql-max_stmts_per_connection = 50; at proxysql and max_prepared_stmt_count = 250000, at mariadb this has slowed down the fragmentation, but still mariadb consuming the memory based on the prepared statements. ProxySQL doesn't automatically close prepared statements unless the client explicitly requests it ? Connection Multiplexing in ProxySQL may be preventing statements from being properly closed. The application might not be explicitly closing prepared statements, relying on ProxySQL to handle cleanup. If multiplexing is enabled, ProxySQL keeps connections open and doesn't send COM_STMT_CLOSE properly. In our case, we have enabled multiplexing. No Query rules in place. sh-4.4$ cat /var/lib/mysql/my.cnf |grep buffer_pool;free -g;top |grep mysqld innodb_buffer_pool_size = 96G total used free shared buff/cache available Mem: 660 378 269 0 13 279 Swap: 0 0 0 1 mysql 20 0 386.8g 374.2g 28312 S 313.3 56.6 13770:26 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 320.0 56.6 13770:36 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 288.7 56.6 13770:44 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 236.0 56.6 13770:51 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 336.3 56.6 13771:02 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 321.9 56.6 13771:11 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 309.3 56.6 13771:21 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 298.0 56.6 13771:30 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 279.7 56.6 13771:38 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 152.0 56.6 13771:42 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 172.1 56.6 13771:48 mysqld\ 1 mysql 20 0 386.8g 374.2g 28312 S 238.0 56.6 13771:55 mysqld\ MariaDB [(none)]> SHOW GLOBAL STATUS LIKE 'Memory_used';\ +---------------+--------------+\ | Variable_name | Value |\ +---------------+--------------+\ | Memory_used | 117572547280 |\ +---------------+--------------+\ 1 row in set (0.001 sec) MariaDB [(none)]> select 117572547280/1024/1024/1024 'Memory_used in GB';\ +-------------------+\ | Memory_used in GB |\ +-------------------+\ | 109.497967436910 |\ +-------------------+\ 1 row in set (0.000 sec)\ MariaDB [(none)]>\ MariaDB [(none)]> SELECT -> NOW() AS Query_Time,\ -> -> -- Temporary Tables Statistics\ -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Created_tmp_tables') AS Tmp_Tables_Created, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Created_tmp_disk_tables') AS Tmp_Tables_Disk, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Created_tmp_memory_tables') AS Tmp_Tables_Memory, -> -> -- Open Tables & Table Cache -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Open_tables') AS Open_Tables, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Opened_tables') AS Opened_Tables, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'table_open_cache') AS Table_Open_Cache, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_VARIABLES WHERE VARIABLE_NAME = 'table_definition_cache') AS Table_Definition_Cache, -> -> -- Prepared Statements Memory Usage -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Prepared_stmt_count') AS Active_Prepared_Statements, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Com_prepare_sql') AS Total_Prepares, -> (SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Com_deallocate_sql') AS Total_Deallocates, -> -> -- Memory Consumed by Prepared Statements (Estimation) -> ROUND((SELECT VARIABLE_VALUE FROM information_schema.GLOBAL_STATUS WHERE VARIABLE_NAME = 'Prepared_stmt_count') * 512 / 1024, 2) AS Estimated_Memory_MB -> ; +---------------------+--------------------+-----------------+-------------------+---------\----+---------------+------------------+------------------------+----------------------------+----------------+-------------------+---------------------+\ | Query_Time | Tmp_Tables_Created | Tmp_Tables_Disk | Tmp_Tables_Memory | Open_Tables | Opened_Tables | Table_Open_Cache | Table_Definition_Cache | Active_Prepared_Statements | Total_Prepares | Total_Deallocates | Estimated_Memory_MB | +---------------------+--------------------+-----------------+-------------------+---------\----+---------------+------------------+------------------------+----------------------------+----------------+-------------------+---------------------+\ | 2025-02-25 08:49:26 | 17772934 | 1985203 | NULL | 400 | 39233 | 400 | 400 | 240131 | 0 | NULL | 120065.50 |\ +---------------------+--------------------+-----------------+-------------------+-------------+---------------+------------------+------------------------+----------------------------+----------------+-------------------+---------------------+\ 1 row in set (0.003 sec) MariaDB [(none)]> Proxysql : ---------------- mysql> SELECT srv_host, srv_port, ConnUsed, ConnFree, ConnOK, Queries, -> ROUND(Queries / NULLIF(ConnOK, 0), 2) AS MultiplexingEfficiencyRatio -> FROM stats_mysql_connection_pool -> ORDER BY MultiplexingEfficiencyRatio ASC -> LIMIT 10; +-------------------------------------------------------------------------+----------+----------+----------+--------+-----------+-----------------------------+\ | srv_host | srv_port | ConnUsed | ConnFree | ConnOK | Queries | MultiplexingEfficiencyRatio |\ +-------------------------------------------------------------------------+----------+----------+----------+--------+-----------+-----------------------------+\ | 2c_oltp.mariadb | 3052 | 2 | 4 | 1555 | 55156669 | 35470.0 \ | | 2b_oltp.mariadb | 3052 | 2 | 6 | 1933 | 70648384 | 36548.0 \ | | 2a_oltp.mariadb | 3052 | 43 | 4 | 4099 | 150018800 | 36598.0 \ | | 2d_oltp.mariadb | 3052 | 0 | 4 | 2080 | 77161687 | 37096.0 | +-------------------------------------------------------------------------+----------+----------+----------+--------+-----------+-----------------------------+\ 4 rows in set (0.00 sec) mysql> SELECT * FROM stats_mysql_connection_pool;\ +-----------+-------------------------------------------------------------------------+----\------+--------+----------+----------+--------+---------+-------------+-----------+-------\------------ | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us |\ +-----------+-------------------------------------------------------------------------+----\------+--------+----------+----------+--------+---------+-------------+-----------+-------\------------+ | 1 | 2a_oltp.mariadb | 3052 | ONLINE | 45 | 0 | 4097 | 0 | 62 | 149975306 | 0 | 48544471112 | 187097398415 | 270 | | 2 | 2c_oltp.mariadb | 3052 | ONLINE | 2 | 4 | 1555 | 0 | 10 | 55133002 | 0 | 32800700913 | 20589949783 | 421 | | 2 | 2b_oltp.mariadb | 3052 | ONLINE | 2 | 6 | 1933 | 0 | 14 | 70624781 | 0 | 41584296177 | 27213051703 | 272 | | 2 | 2d_oltp.mariadb | 3052 | ONLINE | 0 | 3 | 2079 | 0 | 13 | 77137911 | 0 | 45755921665 | 31073609951 | 168 | +-----------+-------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+-----------+-------------------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM stats_mysql_global WHERE variable_name LIKE '%stmt%'; +---------------------------+----------------+\ | Variable_Name | Variable_Value |\ +---------------------------+----------------+\ | Com_backend_stmt_prepare | 63914669 |\ | Com_backend_stmt_execute | 233922211 |\ | Com_backend_stmt_close | 0 |\ | Com_frontend_stmt_prepare | 233883162 |\ | Com_frontend_stmt_execute | 233923781 |\ | Com_frontend_stmt_close | 233876578 |\ | Stmt_Client_Active_Total | 3 |\ | Stmt_Client_Active_Unique | 2 |\ | Stmt_Server_Active_Total | 93421 |\ | Stmt_Server_Active_Unique | 77596 |\ | Stmt_Max_Stmt_id | 174002 |\ | Stmt_Cached | 82713 |\ +---------------------------+----------------+\ 12 rows in set (0.01 sec) mysql> show variables like "%stmt%"; +--------------------------------+-------+\ | Variable_name | Value |\ +--------------------------------+-------+\ | mysql-max_stmts_cache | 10000 |\ | mysql-max_stmts_per_connection | 50 |\ +--------------------------------+-------+\ 2 rows in set (0.00 sec) mysql> MariaDB [(none)]> show status like 'Prepared_stmt_count';\ +---------------------+--------+\ | Variable_name | Value |\ +---------------------+--------+\ | Prepared_stmt_count | 220071 |\ +---------------------+--------+\ 1 row in set (0.000 sec) MariaDB [(none)]>\ mysql> SELECT\ -> username, schemaname, count(*)\ -> FROM -> stats_mysql_prepared_statements_info\ -> GROUP BY\ -> 1, 2\ -> ORDER BY\ -> 3 DESC;\ +----------+------------+----------+\ | username | schemaname | count(*) |\ +----------+------------+----------+\ | app | appprd | 87573 |\ +----------+------------+----------+\ 1 row in set (0.29 sec)\ mysql> Will proxysql disable multiplexing for all queries that have @ in their query_digest will disable multiplexing ??? We are using 2.5.5 version of proxysql and maridb 10.6.18. How we can stabilize this memory fragmentation permanently without any significant impact on performance?

I am managing a MariaDB 10.6 Server for a bunch of people. I also dump the databases for backup purposes. Recently, one of the databases started to make problems while dumping: > mysqldump: Couldn't execute 'show create table my_view': Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_unicode_ci,COERCIBLE) for operation '=' (1267) Everything in that database was created using the utf8mb4 character set and utf8mb4_unicode_ci collation. I checked the DB, tables, columns and view. I then found out, that when I open a regular mariadb shell, the collation_connection differs:

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE "collation_connection";
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_unicode_ci |
+----------------------+--------------------+
1 row in set (0.002 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE "collation_connection";
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_general_ci |
+----------------------+--------------------+
1 row in set (0.003 sec)

**My first question:** Why does the global scope have a different collation_connection compared to the session scope? I always thought the session scope inherits from the global scope. I was not able to find in the docs, where this gets set. Here are the relevant parts of my mariadb option file:

[mariadb]
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci

[client]
default-character-set=utf8mb4

[mariadb-dump]
default-character-set=utf8mb4

**My second question:** How to prevent such issues? Do I have to tell all of my users to use the same collation to prevent issues like that? --- Here is an example to reproduce it:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(100),
    salary DECIMAL(10, 2),
    created_at TIMESTAMP
);

INSERT INTO employees (name, department, salary, created_at)
VALUES
    ('Alice', 'HR', 50000, NOW()),
    ('Bob', 'Engineering', 70000, NOW()),
    ('Charlie', 'HR', 45000, NOW()),
    ('David', 'Engineering', 80000, NOW()),
    ('Eve', 'Marketing', 60000, NOW());

SET collation_connection = utf8mb4_unicode_ci;

CREATE VIEW hr_employees AS
SELECT id, name, salary
FROM employees
WHERE department = 'HR' AND date_format(created_at, "%Y") IN ('2024');

SET collation_connection = utf8mb4_general_ci;

SHOW CREATE VIEW hr_employees;
ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8mb4_unicode_ci,COERCIBLE) for operation '='

I am in the process of migrating some of our servers to a new operating system after CentOS went out of support; and, as a replacement, we decided to move to Oracle Linux 9; specifically, MariaDB is version 10.6.18 and the OS is Red Hat Enterprise Linux release 9.4 (Plow). However, I am now facing an issue where, if the databases service gets killed by the OOM-killer, it won't restart automatically, while it did on CentOS. (By the way, I know that the OOM-killer *shouldn't* hit the database, that is another matter and not the focus of this question...). Here is the logs of the last time this happened on one of these servers:

2024-08-08  2:40:05 0 [Note] /opt/agews64/bin/mariadbd: ready for connections.
Version: '10.6.18-MariaDB'  socket: '/opt/agews64/data/mysql/mysql.sock'  port: 3306  MariaDB Server
240808 03:18:13 mysqld_safe Number of processes running now: 0
240808 03:18:13 mysqld_safe mysqld restarted
2024-08-08  3:18:13 0 [Note] Starting MariaDB 10.6.18-MariaDB source revision 887bb3f73555ff8a50138a580ca8308b9b5c069c as process 157377
2024-08-08  3:18:13 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2024-08-08  3:18:13 0 [Note] InnoDB: Using transactional memory
2024-08-08  3:18:13 0 [Note] InnoDB: Number of pools: 1
2024-08-08  3:18:13 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2024-08-08  3:18:13 0 [Note] InnoDB: Using Linux native AIO
2024-08-08  3:18:13 0 [Note] InnoDB: Initializing buffer pool, total size = 3221225472, chunk size = 134217728
2024-08-08  3:18:13 0 [Note] InnoDB: Completed initialization of buffer pool
2024-08-08  3:18:13 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=636943881408,636943881408
2024-08-08  3:18:13 0 [Note] InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 34 row operations to undo
2024-08-08  3:18:13 0 [Note] InnoDB: Trx id counter is 44113220
2024-08-08  3:18:13 0 [Note] InnoDB: 128 rollback segments are active.
2024-08-08  3:18:14 0 [Note] InnoDB: Starting in background the rollback of recovered transactions
2024-08-08  3:18:14 0 [Note] InnoDB: Rolled back recovered transaction 44112559
2024-08-08  3:18:14 0 [Note] InnoDB: Rollback of non-prepared transactions completed
2024-08-08  3:18:14 0 [Note] InnoDB: Removed temporary tablespace data file: "/opt/agews64/data/mysql/ibtmp1"
2024-08-08  3:18:14 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2024-08-08  3:18:14 0 [Note] InnoDB: Setting file '/opt/agews64/data/mysql/ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2024-08-08  3:18:14 0 [Note] InnoDB: File '/opt/agews64/data/mysql/ibtmp1' size is now 12 MB.
2024-08-08  3:18:14 0 [Note] InnoDB: 10.6.18 started; log sequence number 636943881876; transaction id 44113222
2024-08-08  3:18:14 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-08-08  3:18:14 0 [Note] Server socket created on IP: '0.0.0.0'.
2024-08-08  3:18:14 0 [Warning] 'proxies_priv' entry '@% root@vm-626suite-template' ignored in --skip-name-resolve mode.
2024-08-08  3:18:14 0 [Note] /opt/agews64/bin/mariadbd: ready for connections.
Version: '10.6.18-MariaDB'  socket: '/opt/agews64/data/mysql/mysql.sock'  port: 3306  MariaDB Server
2024-08-08  3:18:22 0 [Note] /opt/agews64/bin/mariadbd (initiated by: unknown): Normal shutdown
2024-08-08  3:18:22 0 [Note] InnoDB: FTS optimize thread exiting.
2024-08-08  3:18:22 0 [Note] InnoDB: Starting shutdown...
2024-08-08  3:18:23 0 [Note] InnoDB: Removed temporary tablespace data file: "/opt/agews64/data/mysql/ibtmp1"
2024-08-08  3:18:23 0 [Note] InnoDB: Shutdown completed; log sequence number 636943890130; transaction id 44113962
2024-08-08  3:18:23 0 [Note] /opt/agews64/bin/mariadbd: Shutdown complete

As you can see on line 3, where it says 240808 03:18:13 mysqld_safe Number of processes running now: 0, it seems that mysqld_safe correctly detected that fact that mariadbd had died, and dutifully restarted it. However, starting from 2024-08-08 3:18:22 0 [Note] /opt/agews64/bin/mariadbd (initiated by: unknown): Normal shutdown, that is, a few seconds after mysqld_safe restarted the database, immediately a new "clean" shutdown occurs. After that, the database does not automatically restart. For reference, here is the service definition for systemd:

[Unit]
Description=AGEws - Database
PartOf=agews.service
After=agews.service

[Service]
Type=forking
ExecStart=/opt/agews64/agews __start mysql
ExecStop=/opt/agews64/agews __stop mysql
ExecReload=/opt/agews64/agews __restart mysql
Restart=on-abort
KillMode=control-group
TimeoutSec=3600
OOMScoreAdjust=-750
MemoryAccounting=yes

[Install]
WantedBy=agews.service

The unit file uses Restart=on-abort as it seemed recommended in another question I found on the network. After starting, the unit is dinamically modified based on available RAM; for this specific server, it sets MemoryHigh=5.5G and MemoryMax=6G. **What configuration change should I make so that MariaDB service automatically restart after being killed by OOM-killer?**

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 to the user 'name.name'@'10.25%'. the problem is that i deleted the user dba, so now my role lost the user who had his ADMIN OPTION.

MariaDB [(none)]> SELECT count(*),User,Role,Admin_option FROM mysql.roles_mapping group by role DESC;
+----------+----------------------+-------------+--------------+
| count(*) | User                 | Role        | Admin_option |
+----------+----------------------+-------------+--------------+
|        1 | name.name            | test        | N            |       
+----------+----------------------+-------------+--------------+

now i recreated the user dba, Is there a way to give back the ADMIN OPTION to dba using another super user like root ? what are the possible solutions to back to the normal status ? mariadb info : Server: MariaDB Server version: 10.6.8-MariaDB-1:10.6.8+maria~stretch-log mariadb.org binary distribution Protocol version: 10

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 table_open_cache instead of 1024. The interesting thing is, if we set it to anything LESS than 200 in my.cnf, it takes that value, but anything more than 200 and it's capped at 200. We can dynamically set it to a higher value using set global table_open_cache = 1024;, and that is reflected in the variable, but we can't seem to set a higher value from my.cnf. (And yes, we are restarting mariadb after making the change. Again, setting to a lower value than 200 works.) ulimit -n returns 1024. open_files_limit is 40000. We've also checked that mariadb is not loading any other cnf files besides /etc/my.cnf and ~/.my.cnf, the latter of which doesn't contain anything that would override the value. Also checked the error log, but didn't see any startup errors there. What else could be causing table_open_cache to be capped at 200?

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 cloudDB with the connector /net. the database size is 2 TB right now and sqlite. The DB provider must be in germany . Can anybody guide me in the right direction please. managed server or VServer? Hardware config? Storage space ? I want to avoid pitfalls. Thank you in advance!

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 (270MB). May be exists any third party solution?

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? MariaDB 10.6.

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, and exactly the same database contents. On my development system the ibdata1 file is 397MB. On the production system the ibdata1 file keeps growing until it has occupied every byte on the site. It is now 23GB, well over 50% of the 40GB I have as part of my contract. I cannot just keep paying for more disk space to satisfy MariaDBs design decision to dump information into this file even though I have set the separate file for each table option. I shut down the server, deleted the ibdata1 file, restarted the server, reloaded the databases and still the ibdata1 file keeps growing. How do I stop this? My entire site is down until I can fix this problem, so this is an emergency.

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 DEFAULT CHARSET=utf8 COLLATE utf8_general_ci ROW_FORMAT=DYNAMIC; INSERT INTO Data (ID,XML) VALUES ($id,LOAD_FILE('file.xml')); The process is slow, around 2-5 inserts/second. The entire database would be too large for an SDD drive, and I create the database on a separate HDD, but I move the files in batches to an SDD drive to make the reading faster. Note that disk speed is not the rate-determining step, as XML data are hugely shrunk by compression. I tried InnoDB to gain concurrent insert, but the size of InnoDB ibd is three times larger than ARIA/MyISAM, and InnoDB is much slower on HDD. I tried ROCKSDB, but it cannot be created on a separate disk, as there is one single directory for all tables. Also, the memory management of ROCKSDB is terrible for such scenarios (or I could not find the proper configuration). I did not try ARCHIVE engine performance since it needs ID to be in order. My current solution is to INSERT concurrently to a temporary InnoDB table on SSD and then INSERT INTO SELECT from the InnoDB table to the ARIA table on HDD. The problem is the integrity and delay in emptying the InnoDB and starting the concurrent INSERT process. I appreciate any possible solution.

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 slower on the new server. I enabled slow query logs with log-queries-not-using-indexes enabled and nearly every query was being logged. Using a slow query as a test case, I ran it on both databases and it was 10 times slower on the *new* server. Then I did 'explain' on both servers and found that on the new server, it wasn't using any indexes. I ran mysqlcheck --optimize and the query time improved (along with the Explain) but still 4 times slower than on the old server. Apart from more recent OS and software versions, the server configs are roughly the same, apart from the newer server having more RAM, which has been allocated to MariaDB. Here is the server.cnf file for the new server. I have tweaked it based on performance tuning recommendations, but nothing has helped speed up the queries apart from optimizing the entire database.

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#

#user                    = mysql
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
#datadir                 = /var/lib/mysql
#tmpdir                  = /tmp

# Broken reverse DNS slows down connections considerably and name resolve is
# safe to skip if there are no "host by domain name" access grants
skip-name-resolve

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 127.0.0.1

#
# * Fine Tuning
#

#key_buffer_size        = 128M
#max_allowed_packet     = 1G
#thread_stack           = 192K
#thread_cache_size      = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
#myisam_recover_options = BACKUP
#max_connections        = 100
#table_cache            = 64

#
# * Logging and Replication
#

# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# Recommend only changing this at runtime for short testing periods if needed!
#general_log_file       = /var/log/mysql/mysql.log
#general_log            = 1

# When running under systemd, error logging goes via stdout/stderr to journald
# and when running legacy init error logging goes to syslog due to
# /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf
# Enable this if you want to have error logging into a separate file
#log_error = /var/log/mysql/error.log
# Enable the slow query log to see queries with especially long duration
#slow_query_log 				 = 1
#slow_query_log_file    = /var/log/mysql/mariadb-slow.log
#long_query_time        = 3
#log_slow_verbosity    = query_plan,explain
#log-queries-not-using-indexes = 1
#min_examined_row_limit = 1000

# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
#max_binlog_size        = 100M

#
# * SSL/TLS
#

# For documentation, please read
# https://mariadb.com/kb/en/securing-connections-for-client-and-server/ 
#ssl-ca = /etc/mysql/cacert.pem
#ssl-cert = /etc/mysql/server-cert.pem
#ssl-key = /etc/mysql/server-key.pem
#require-secure-transport = on

#
# * Character sets
#

# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

#
# * InnoDB
#

# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# Most important is to give InnoDB 80 % of the system RAM for buffer use:
# https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size 
#innodb_buffer_pool_size = 8G

#
# Performance Tuning 
#

# few myisam tables, set this low
key_buffer_size		= 32M

# increased from 2000
table_open_cache	= 4000

# Note: query cache deprecated, s/b disabled acc. to perf tuner
query_cache_limit		= 0
query_cache_size		= 0
# for more write intensive setups, set to DEMAND or OFF
#query_cache_type		= OFF

# kill long running queries (30 seconds)
max_statement_time = 30

# also change aria page cache from 128M (helps w/temp tables)
aria_pagecache_buffer_size = 256M

#Note: disabled bin logs
skip-log-bin

innodb_log_file_size	= 512M
innodb_buffer_pool_size	= 3G
# This is ignored in 10.5 and above (auto calced based on pool size, so ignore perf script rec to raise it)
#innodb_buffer_pool_instances = 2

# increased both after server upgrade
tmp_table_size		= 1572M
max_heap_table_size	= 1572M
# > total num tables
table_definition_cache	= 3200

# END changes


# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.6 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.6]

Here is the Explain query without optimizing the database first. The Select query takes **0.133 seconds**:

explain SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) JOIN wp_icl_translations wpml_translations ON wp_posts.ID = wpml_translations.element_id AND wpml_translations.element_type = CONCAT('post_', wp_posts.post_type) WHERE 1=1 AND ( wp_postmeta.meta_key = 'price' AND ( ( mt1.meta_key
 = 'vebra_status' AND mt1.meta_value IN ('0','6','7','8','13','1') ) ) ) AND ((wp_posts.post_type = 'property' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'private')))
_posts.post_type NOT IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','property','rl_gallery','quadro_mods' ) ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 DESC LIMIT 0,
12;
+------+-------------+-------------------+--------+-----------------------------+------------------+---------+------------------------------------+------+-----------------------------------------------------------+
| id   | select_type | table             | type   | possible_keys               | key              | key_len | ref                                | rows | Extra                                                     |
+------+-------------+-------------------+--------+-----------------------------+------------------+---------+------------------------------------+------+-----------------------------------------------------------+
|    1 | SIMPLE      | wp_posts          | range  | PRIMARY,type_status_date    | type_status_date | 164     | NULL                               | 1    | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | wp_postmeta       | ALL    | post_id,meta_key            | NULL             | NULL    | NULL                               | 1    | Using where; Using join buffer (flat, BNL join)           |
|    1 | SIMPLE      | mt1               | ALL    | post_id,meta_key            | NULL             | NULL    | NULL                               | 1    | Using where; Using join buffer (incremental, BNL join)    |
|    1 | SIMPLE      | wpml_translations | eq_ref | el_type_id,id_type_language | el_type_id       | 251     | func,mamaisonfrancaise.wp_posts.ID | 1    | Using index condition; Using where                        |
+------+-------------+-------------------+--------+-----------------------------+------------------+---------+------------------------------------+------+-----------------------------------------------------------+

Same explain after optimization. Select query now takes **0.048 seconds**:

explain SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) JOIN wp_icl_translations wpml_translations ON wp_posts.ID = wpml_translations.element_id AND wpml_translations.element_type = CONCAT('post_', wp_posts.post_type) WHERE 1=1 AND ( wp_postmeta.meta_key = 'price' AND ( ( mt1.meta_key = 'vebra_status' AND mt1.meta_value IN ('0','6','7','8','13','1') ) ) ) AND ((wp_posts.post_type = 'property' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'private'))) AND ( ( ( wpml_translations.language_code = 'en' OR 0 ) AND wp_posts.post_type IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','property','rl_gallery','quadro_mods' ) ) OR wp_posts.post_type NOT IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','property','rl_gallery','quadro_mods' ) ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 DESC LIMIT 0,
12;
+------+-------------+-------------------+------------+-----------------------------+------------------+---------+------------------------------------+--------+-----------------------------------------------------------+
| id   | select_type | table             | type       | possible_keys               | key              | key_len | ref                                | rows   | Extra                                                     |
+------+-------------+-------------------+------------+-----------------------------+------------------+---------+------------------------------------+--------+-----------------------------------------------------------+
|    1 | SIMPLE      | wp_posts          | range      | PRIMARY,type_status_date    | type_status_date | 164     | NULL                               | 64     | Using where; Using index; Using temporary; Using filesort |
|    1 | SIMPLE      | mt1               | ref|filter | post_id,meta_key            | post_id|meta_key | 8|767   | mamaisonfrancaise.wp_posts.ID      | 3 (0%) | Using where; Using rowid filter                           |
|    1 | SIMPLE      | wp_postmeta       | ref        | post_id,meta_key            | post_id          | 8       | mamaisonfrancaise.wp_posts.ID      | 3      | Using where                                               |
|    1 | SIMPLE      | wpml_translations | ref        | el_type_id,id_type_language | id_type_language | 251     | mamaisonfrancaise.wp_posts.ID,func | 1      | Using where; Using index                                  |
+------+-------------+-------------------+------------+-----------------------------+------------------+---------+------------------------------------+--------+-----------------------------------------------------------+

Here is the same query from the old server (Mariadb 10.3). The select query takes **0.013 seconds**.

explain SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) JOIN wp_icl_translations wpml_translations ON wp_posts.ID = wpml_translations.element_id AND wpml_translations.element_type = CONCAT('post_', wp_posts.post_type) WHERE 1=1 AND ( wp_postmeta.meta_key = 'price' AND ( ( mt1.meta_key = 'vebra_status' AND mt1.meta_value IN ('0','6','7','8','13','1') ) ) ) AND ((wp_posts.post_type = 'property' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'acf-disabled' OR wp_posts.post_status = 'private'))) AND ( ( ( wpml_translations.language_code = 'en' OR 0 ) AND wp_posts.post_type IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','property','rl_gallery','quadro_mods' ) ) OR wp_posts.post_type NOT IN ('post','page','attachment','wp_block','wp_template','wp_template_part','wp_navigation','property','rl_gallery','quadro_mods' ) ) GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value+0 DESC LIMIT 0, 12;
+------+-------------+-------------------+--------+-----------------------------+------------+---------+------------------------------------------------+------+----------------------------------------------+
| id   | select_type | table             | type   | possible_keys               | key        | key_len | ref                                            | rows | Extra                                        |
+------+-------------+-------------------+--------+-----------------------------+------------+---------+------------------------------------------------+------+----------------------------------------------+
|    1 | SIMPLE      | wp_postmeta       | ref    | post_id,meta_key            | meta_key   | 767     | const                                          |   62 | Using where; Using temporary; Using filesort |
|    1 | SIMPLE      | wp_posts          | eq_ref | PRIMARY,type_status_date    | PRIMARY    | 8       | mamaisonfrancaise.wp_postmeta.post_id          |    1 | Using where                                  |
|    1 | SIMPLE      | wpml_translations | eq_ref | el_type_id,id_type_language | el_type_id | 251     | func,mamaisonfrancaise.wp_postmeta.post_id     |    1 | Using index condition; Using where           |
|    1 | SIMPLE      | mt1               | ref    | post_id,meta_key            | post_id    | 8       | mamaisonfrancaise.wpml_translations.element_id |    4 | Using index condition; Using where           |
+------+-------------+-------------------+--------+-----------------------------+------------+---------+------------------------------------------------+------+----------------------------------------------+

Finally, here are the create table statements for the three tables used above (same on both databases):

| wp_posts | CREATE TABLE wp_posts (
  ID bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  post_author bigint(20) unsigned NOT NULL DEFAULT 0,
  post_date datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  post_date_gmt datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  post_content longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  post_title text COLLATE utf8mb4_unicode_ci NOT NULL,
  post_excerpt text COLLATE utf8mb4_unicode_ci NOT NULL,
  post_status varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
  comment_status varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  ping_status varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  post_password varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  post_name varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  to_ping text COLLATE utf8mb4_unicode_ci NOT NULL,
  pinged text COLLATE utf8mb4_unicode_ci NOT NULL,
  post_modified datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  post_modified_gmt datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  post_content_filtered longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  post_parent bigint(20) unsigned NOT NULL DEFAULT 0,
  guid varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  menu_order int(11) NOT NULL DEFAULT 0,
  post_type varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
  post_mime_type varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  comment_count bigint(20) NOT NULL DEFAULT 0,
  PRIMARY KEY (ID),
  KEY post_name (post_name(191)),
  KEY type_status_date (post_type,post_status,post_date,ID),
  KEY post_parent (post_parent),
  KEY post_author (post_author)
) ENGINE=InnoDB AUTO_INCREMENT=6739 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

| wp_postmeta | CREATE TABLE wp_postmeta (
  meta_id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  post_id bigint(20) unsigned NOT NULL DEFAULT 0,
  meta_key varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  meta_value longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (meta_id),
  KEY post_id (post_id),
  KEY meta_key (meta_key(191))
) ENGINE=InnoDB AUTO_INCREMENT=86406 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

| wp_icl_translations | CREATE TABLE wp_icl_translations (
  translation_id bigint(20) NOT NULL AUTO_INCREMENT,
  element_type varchar(60) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post_post',
  element_id bigint(20) DEFAULT NULL,
  trid bigint(20) NOT NULL,
  language_code varchar(7) COLLATE utf8mb4_unicode_ci NOT NULL,
  source_language_code varchar(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (translation_id),
  UNIQUE KEY trid_lang (trid,language_code),
  UNIQUE KEY el_type_id (element_type,element_id),
  KEY trid (trid),
  KEY id_type_language (element_id,element_type,language_code)
) ENGINE=InnoDB AUTO_INCREMENT=6566 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |

There is another recent question here regarding 10.3 vs 10.6 performance, so I'm wondering if something changed between these versions? Why do I need to optimize the tables to improve performance? Are there any config changes I can make to help? Note that I can't change queries or tables themselves since they're provided by Wordpress and related plugins. Thanks in advance. **EDIT** Here is output of show engine innodb status **Old**

=====================================
2022-09-21 16:57:37 0x7facd4664700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 22 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 9256 srv_active, 0 srv_shutdown, 19604 srv_idle
srv_master_thread log flush and writes: 28853
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 618180
OS WAIT ARRAY INFO: signal count 287213
RW-shared spins 12767, rounds 300017, OS waits 8937
RW-excl spins 5573, rounds 14028, OS waits 158
RW-sx spins 95, rounds 883, OS waits 17
Spin rounds per wait: 23.50 RW-shared, 2.52 RW-excl, 9.29 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 2104026620
Purge done for trx''s n:o < 2104026620 undo n:o < 0 state: running
History list length 30
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421855290974312, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 421855290982664, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
29223 OS file reads, 462720 OS file writes, 190797 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 13.14 writes/s, 5.82 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 185, seg size 187, 219 merges
merged operations:
 insert 81, delete mark 750, delete 359
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 332147, node heap has 26 buffer(s)
Hash table size 332147, node heap has 95 buffer(s)
Hash table size 332147, node heap has 80 buffer(s)
Hash table size 332147, node heap has 81 buffer(s)
Hash table size 332147, node heap has 289 buffer(s)
Hash table size 332147, node heap has 61 buffer(s)
Hash table size 332147, node heap has 428 buffer(s)
Hash table size 332147, node heap has 18 buffer(s)
475.39 hash searches/s, 8.59 non-hash searches/s
---
LOG
---
Log sequence number 857452474234
Log flushed up to   857452474234
Pages flushed up to 857452455417
Last checkpoint at  857452429869
0 pending log flushes, 0 pending chkp writes
138348 log i/o's done, 3.27 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 1403912192
Dictionary memory allocated 1209824
Buffer pool size   81920
Free buffers       51281
Database pages     29561
Old database pages 10932
Modified db pages  7
Percent of dirty pages(LRU & free pages): 0.009
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 19, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 28619, created 942, written 306353
0.00 reads/s, 0.00 creates/s, 8.82 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 29561, unzip_LRU len: 0
I/O sum:cur[1] , unzip sum:cur
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=1063, Main thread ID=140378813626112, state: sleeping
Number of rows inserted 36282, updated 85523, deleted 13204, read 154819934
0.00 inserts/s, 1.77 updates/s, 0.00 deletes/s, 475.71 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

**New**

=====================================
2022-09-21 16:56:54 0x7f6678db4640 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 50 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 8 srv_active, 0 srv_shutdown, 28897 srv_idle
srv_master_thread log flush and writes: 28902
----------
SEMAPHORES
----------
------------
TRANSACTIONS
------------
Trx id counter 7375478
Purge done for trx''s n:o < 7375478 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION (0x7f667a556130), not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
FILE I/O
--------
Pending flushes (fsync) log: 0; buffer pool: 0
30553 OS file reads, 56567 OS file writes, 65170 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.14 writes/s, 0.14 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 4501511543
Log flushed up to   4501511543
Pages flushed up to 4263277735
Last checkpoint at  4263277723
0 pending log flushes, 0 pending chkp writes
56569 log i/o's done, 0.14 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 3254779904
Dictionary memory allocated 22318968
Buffer pool size   194688
Free buffers       151928
Database pages     42760
Old database pages 15764
Modified db pages  14896
Percent of dirty pages(LRU & free pages): 7.651
Max dirty pages percent: 90.000
Pending reads 0
Pending writes: LRU 0, flush list 0
Pages made young 9152, not young 518978
0.00 youngs/s, 0.00 non-youngs/s
Pages read 29150, created 13816, written 0
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 42760, unzip_LRU len: 0
I/O sum:cur, unzip sum:cur
--------------
ROW OPERATIONS
--------------
0 read views open inside InnoDB
Process ID=0, Main thread ID=0, state: sleeping
Number of rows inserted 275257, updated 33966, deleted 4640, read 269777358
0.04 inserts/s, 0.02 updates/s, 0.04 deletes/s, 1330.07 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

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 three nodes. On node 1 and node 2 I get the expected hostname for the user: mariadb --no-defaults -utheusername -p'thepassword' -h127.0.0.1 -e"select user()" +-----------------------+ | user() | +-----------------------+ | theusername@127.0.0.1 | +-----------------------+ However, on the third node it always ignores the request to use host 127.0.0.1 and replaces this with localhost: mariadb --no-defaults -utheusername -p'thepassword' -h127.0.0.1 -e"select user()" +-----------------------+ | user() | +-----------------------+ | theusername@localhost | +-----------------------+ If I instead log in interactively (remove the -e option) and run the status command, it shows that the connection is over TCP/IP (not socket although the user's host is still localhost) on all the nodes: Connection: 127.0.0.1 via TCP/IP What could be the reason for this?

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 lin_98_41_strumenti_categorie ( id_categoria int(10) unsigned NOT NULL AUTO_INCREMENT, id_azienda int(10) unsigned NOT NULL DEFAULT 1, id_sede int(10) unsigned NOT NULL DEFAULT 1, revisione_documento int(10) unsigned NOT NULL DEFAULT 0, id_macchina int(10) unsigned DEFAULT NULL, id_attrezzo int(10) unsigned DEFAULT NULL, id_impianto int(10) unsigned DEFAULT NULL, id_opera_provvisionale int(10) unsigned DEFAULT NULL, tipo_strumento char(1) GENERATED ALWAYS AS (concat_ws('',if(id_macchina is not null,'M',NULL),if(id_attrezzo is not null,'A',NULL),if(id_impianto is not null,'I',NULL),if(id_opera_provvisionale is not null,'O',NULL))) VIRTUAL, PRIMARY KEY (id_categoria,id_azienda,id_sede,revisione_documento), UNIQUE KEY unique_id_macchina_lin_98_41_strumenti_categorie (id_macchina,id_azienda,id_sede,revisione_documento), UNIQUE KEY unique_id_attrezzo_lin_98_41_strumenti_categorie (id_attrezzo,id_azienda,id_sede,revisione_documento), UNIQUE KEY unique_id_impianto_lin_98_41_strumenti_categorie (id_impianto,id_azienda,id_sede,revisione_documento), UNIQUE KEY unique_id_opera_provvisionale_lin_98_41_strumenti_categorie (id_opera_provvisionale,id_azienda,id_sede,revisione_documento), KEY fk_main_lin_98_41_strumenti_categorie (id_azienda,id_sede,revisione_documento), CONSTRAINT fk_id_attrezzo_lin_98_41_strumenti_categorie FOREIGN KEY (id_attrezzo) REFERENCES agews_attrezzi (id_attrezzo) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_id_impianto_lin_98_41_strumenti_categorie FOREIGN KEY (id_impianto) REFERENCES agews_impianti (id_impianto) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_id_macchina_lin_98_41_strumenti_categorie FOREIGN KEY (id_macchina) REFERENCES agews_macchine (id_macchina) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_id_opera_provvisionale_lin_98_41_strumenti_categorie FOREIGN KEY (id_opera_provvisionale) REFERENCES agews_opere_provvisionali (id_opera_provvisionale) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT fk_main_lin_98_41_strumenti_categorie FOREIGN KEY (id_azienda, id_sede, revisione_documento) REFERENCES lin_98_41 (id_azienda, id_sede, revisione_documento) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci; The error is in the last GENERATED ALWAYS field marked as STORED, if I manually change the type to VIRTUAL it works. The error just says: > ERROR 1901 (HY000) at line 420329: Function or expression 'id_macchina' cannot be used in the GENERATED ALWAYS AS clause of tipo_strumento but it gives no useful indications as to **why** now it cannot be used. However, this SQL dump worked without modifications with version 10.6.10, is it possible there was such a backward-incompatbile change in the latest 10.6 version? I have looked the release notes, but I didn't found anything which should justify suche breaking change.