Questions tagged [mariadb-10.5]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
0 answers
15 views

Deleting rows from a tag_map if parent tag exists

I have a simple tag_map CREATE TABLE tag_map ( article_id int(11) unsigned NOT NULL, tag_id int(11) unsigned NOT NULL, INDEX(tag_id), PRIMARY KEY(article_id,tag_id) ) ENGINE=InnoDB There is a table ...
user avatar
  • 4,179
1 vote
0 answers
15 views

Mariadb 10.5 FORCE INDEX can't be used with the PRIMARY key?

In a InnoDB table I can use any index with the FORCE INDEX or USE INDEX except for the PRIMARY one. (This is on RDS, if that matters) ERROR 1176 (42000): Key 'id' doesn't exist in table 'mytable' ...
user avatar
2 votes
2 answers
38 views

Finding gaps between the values of a column in MySQL

At a table of movie actors, CREATE TABLE ArtistMap ( ArtistID int(11) unsigned NOT NULL, MovieID int(11) unsigned NOT NULL, Year year, INDEX(MovieID), INDEX(Year), PRIMARY KEY(ArtistID,MovieID) ) ...
user avatar
  • 4,179
0 votes
0 answers
9 views

How do I write MariaDB query results to several different outfiles on client side?

I have MariaDB server and client 10.5.12 both running on the same Debian 11 PC. I am running several SQL queries from a file that I pass to the MariaDB shell client: mysql < autoreport.sql I would ...
user avatar
  • 1
0 votes
1 answer
22 views

Why is there a big gap in the actual Table space? (calculated vs information_schema vs file size)

I have a InnoDB table with the following columns: BIGINT -> 8 bytes BIGINT -> 8 bytes ENUM(0,1) -> 1 byte MEDIUMINT -> 3 bytes INT -> 4 bytes = 24 bytes per row It contains 10454004 ...
user avatar
  • 599
-2 votes
2 answers
107 views

MariaDb performance issue

Using mysqltuner, we are facing performance issue with application using this db. We have physical memory of 128 GB. But this db is set to 16 GB. How can increase the memory and performance of this db....
user avatar
  • 1
-2 votes
1 answer
79 views

how to perform inner join on two table where i'am able to retrieve all data?

I need help with a MySQL query. bar_opening_details has closing & opening stock bar_recvd_details has received the stock When I receive purchased stock, I insert it into bar_recvd_details. Next, ...
user avatar
  • 23
0 votes
1 answer
86 views

MariaDB Maxscale caching not working

I have setup Maxscale (v6.2) and have connected to the Galera Cluster (3 nodes - MariaDB 10.5). I am trying to use the cache filter but it seems to not work. I have enabled general log for all the ...
user avatar
  • 103
0 votes
1 answer
39 views

Replication from multiple masters to one slave

I have 2 Raspberry PI's and each run a MariaDB 10.3 server. Each has the same databases: vennens scheduler On the slave I've configured the 50-server.conf file with follow contents: master_pi1....
user avatar
0 votes
0 answers
25 views

Faster JOIN using LIKE

I have a simple tag table as CREATE TABLE Tags ( TagID int(11) unsigned NOT NULL AUTO_INCREMENT, Tag varchar(255), ParentID int(11) unsigned, Parent enum('Yes', 'No'), UNIQUE INDEX(Tag), INDEX(...
user avatar
  • 4,179
1 vote
1 answer
42 views

Filter out rows which the text on that current row is contained in the text of the following row

I wonder if it's possible, in a SELECT, to filter out rows which the text on that current row is contained in the text of the following row. For example, image we log "searches" performed by ...
user avatar
  • 599
0 votes
1 answer
49 views

Why does an integer comparison result in no temporary index in MariaDB?

I am running a query on a table (transaction_items, c. 12 million rows) that has a many-to-many relationship with another table, and I wish to group transaction_items by this relationship. To do this, ...
user avatar
  • 101
0 votes
1 answer
117 views

Subquery not using Index, even if forcing

Using MariaDB 10.5.12 on CentOS 7 If it makes any difference, my "optimizer_use_condition_selectivity" = 1 If I run: EXPLAIN SELECT COUNT(*) FROM ips WHERE ip="1.2.3.4" This query ...
user avatar
  • 599
0 votes
2 answers
58 views

Disabling Aria INSERT log in MariaDB [closed]

When I bulk INSERT into an Aria table, it creates a copy of the INSERT in a series of log files (aria_log.%). It can lead to full disk and crash of MySQL. As I understand, Aria keeps a redo log for ...
user avatar
  • 4,179
0 votes
1 answer
26 views

Improving JOIN with sub-query

I capture Users who comment on the Articles of other Users, as INSERT IGNORE INTO AuthorCommentators (AuthorID,CommentatorID,Freq) SELECT AuthorID,CommentatorID,C FROM (SELECT b.UserID ...
user avatar
  • 4,179
1 vote
1 answer
57 views

Speeding up SELECT FROM a large table

I have a large table (500M rows with ibd size of 1.5TB) CREATE TABLE t1 ( ID int(11) unsigned NOT NULL AUTO_INCREMENT, ChildID int(11) unsigned NOT NULL, Number smallint(5) unsigned, Title varchar(255)...
user avatar
  • 4,179
1 vote
1 answer
42 views

Monitoring MySQL/innoDB post-process

I did INSERT INTO SELECT of a huge table (1TB). When the process is finished, MySQL/InnoDB still does post-processing (creating updating secondary indices, etc.) for hours. Since these are internal ...
user avatar
  • 4,179
1 vote
4 answers
91 views

Tuning innoDB for a write-intensive machine

I have MariaDB 10.5 on my desktop with multiple disks (SSD and HDD) for write-intensive projects. Writing to a single table is fast and the percentage of dirty pages remains close to zero with 1000-...
user avatar
  • 4,179
0 votes
0 answers
25 views

MariaDB: User password gets reset even after ALTER USER command fails

On MariaDB 10.5.10. I create a DB user with a complex password. Then I change the password to say 1 char, it throws an Error 1396 but resets the password to NULL. So now I can login as the user ...
user avatar
  • 21
0 votes
1 answer
88 views

Suddenly, started getting "ERROR 2026 (HY000): Unknown SSL error"

MariaDB instance has been running well for ages, and suddenly, starting about 30 minutes ago, my apps get this error trying to connect: # mysql ERROR 2026 (HY000): Unknown SSL error My certs have ...
user avatar
  • 599
0 votes
1 answer
30 views

How do you JOIN two tables as a combination of LEFT and RIGHT JOINs?

This is probably a stupid question. In two tables of CREATE TABLE t1 ( id int(11) unsigned NOT NULL, col1 int(11) unsigned NOT NULL, PRIMARY KEY(id) ) ENGINE=InnoDB; CREATE TABLE t2 ( id int(11) ...
user avatar
  • 4,179
1 vote
1 answer
112 views

MariaDB hidden disk usage for log dir

We have a simple master/slave cluster in our environment which is sets up on Ubuntu 20.04 and MariaDB 10.5: MariaDB [(none)]> SELECT VERSION(); +--------------------------------------------+ | ...
user avatar
0 votes
0 answers
17 views

Galera: I want to add "Node X" to my cluster but use "Node Y" to transfer the data

I'm having quite a problem with adding a node to my current Galera cluster. Right now I have 4 nodes on my cluster: 3 of them are on my local and 1 currently on the cloud. I successfully synced my ...
user avatar
  • 3
0 votes
1 answer
82 views

SELECTing n rows for each GROUP with WHERE condition

I SELECT n rows for each ArticleID from TagMap table SELECT a.ArticleID,a.TagID FROM TagMap a LEFT JOIN TagMap b ON a.ArticleID = b.ArticleID AND a.TagID < b.TagID GROUP BY a.ArticleID,a.TagID ...
user avatar
  • 4,179
0 votes
1 answer
116 views

How to remove a innodb metadata lock with thread_id 0 in mariadb / mysql?

Today, suddenly our mysql database server got down. Few minutes after restarting the server went down again, and again. To see what is happening, we temporarily put our application in maintenance mode ...
user avatar
1 vote
0 answers
43 views

JOINing two mapping tables

I have two mapping tables as CREATE TABLE ProjectDevelopers ( DeveloperID int(11) unsigned NOT NULL, ProjectID int(11) unsigned NOT NULL, INDEX(ProjectID), PRIMARY KEY(DeveloperID,ProjectID) ) ENGINE=...
user avatar
  • 4,179
1 vote
1 answer
84 views

Creating MEMORY table with varchar in MySQL

I need to create TEMPORARY tables for processing (via JOIN) a large set of data (1M rows), which have varchar columns. If I use ENGINE=MEMORY, it will change varchar to char. Then, the data will not ...
user avatar
  • 4,179
0 votes
0 answers
27 views

Migrate Db2 blob data to Mariadb

We plan to move a data set from Db2 to MariaDB. Can anybody suggest an approach on how to migrate the BLOB column data exported from db2? Initially, I managed to import it into the MariaDB database ...
user avatar
0 votes
2 answers
28 views

How to determine if I have an undersized innodb_buffer_pool for MySQL

We have several MySQL 5.7/Mariadb databases running on Ec2 instances on CentOS. Our current logic for allocating memory to the DB is based off total memory available on the OS and a percentage is ...
user avatar
  • 21
0 votes
1 answer
69 views

Histograms in MySQL

We have several Linux based On-prem Mariadb 10.5.8 DB. Size is about 1 TB each. Currently we don't have any script that does analyze table to gather statistics on our DB. It's done automatically based ...
user avatar
  • 21
0 votes
1 answer
157 views

Wait state: Statistics

We have 200 databases in one instance of MariaDB with a total of 370.000 tables, since we upgraded to MariaDB 10.5.11, we see most of time passed by mysql is in wait states: Statistics. According ...
user avatar
0 votes
1 answer
24 views

how do I restrict a specific row's columns to have only a specific value set?

I have the following table CREATE TABLE `mview_state` ( `state_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'View State ID', `view_id` varchar(255) DEFAULT NULL COMMENT 'View ID', `mode` ...
user avatar
  • 147
0 votes
1 answer
20 views

List sorted Posts on Group and children Groups efficiently

Say I have a system that has Groups and Posts on those Groups. A Group can have multiple "child Groups" Group 1 > Group 2 > Group 3 Group 1 > Group 2 > Group 4 Group 1 > ...
user avatar
  • 599
0 votes
0 answers
35 views

Procedure that used to work with 0 lock timeout doesn't work anymore

I reinstalled my database, and it seems something changed in mariadb 10.5.11 that this settings are not applied anymore in the configuration file? innodb_table_locks = 0 innodb_lock_wait_timeout = 0 ...
user avatar
  • 188
2 votes
1 answer
215 views

MariaDB Galera Arbitrator failing to sync with SSL

So the scenario is this. I have already setup a 4 node MariaDB Galera cluster (10.5). I'm also adding on top a 5th Galera Arbitrator and everything syncs and connects just fine. However when I enable ...
user avatar
  • 147
0 votes
1 answer
284 views

How to Prevent High MariaDB Memory Usage on Windows

I am running MariaDB 10.5 and I am having trouble with it using too much memory on my Windows 10 machine. I have 16GB of RAM, and MariaDB ends up using around 8GB. SHOW VARIABLES; https://pastebin.com/...
user avatar
  • 1
1 vote
1 answer
144 views

slow disk with InnoDB page compression

I have a write-intensive MariaDB with both NVMe SSD and HDD disks. I recently enabled page compression (innodb_compression_default=ON). I encountered two problems: The database gets slow after a ...
user avatar
  • 4,179
0 votes
0 answers
263 views

HeidiSQL, how to export user definition

I am using MariaDB version 10.5.9.0 on Windows version 10.0.19041.928 with HeidiSQL version 11.0.0.5919 I have exported the database structure which includes the tables, stored procedures and data. I ...
user avatar
  • 149
0 votes
2 answers
151 views

Stored procedure prepared statement no error and not working [closed]

Here is my stored procedure: BEGIN #Procedure: insertMetaTagIfNotPresent #Parameters: bkID ID to find or NULL to create automatically # vcTag Tag name to assign, must be ...
user avatar
  • 149
0 votes
1 answer
65 views

MariaDB optimizing geo IP lookup with Maxmind GeoLite2 database

I have the following two tables that I ingest with data from Maxmind's GeoLite2 Country database: CREATE TABLE `country_location` ( `geoname_id` int(11) UNSIGNED NOT NULL, `locale_code` varchar(...
user avatar
  • 1
0 votes
1 answer
225 views

How can I disable swappiness just for Mariadb and not for other applications on the same EC2 Instance?

I have an EC2 instance where DB(Mariadb) runs on the same EC2 instance as the other applications. MySQL documentation suggests to disable swappiness since DB doesn't like swap. How can I disable ...
user avatar
  • 21
0 votes
1 answer
23 views

need help building a query to detect duplicates on multiple columns

I have the following table create table catalog_product_entity_varchar ( value_id int auto_increment comment 'Value ID' primary key, attribute_id smallint unsigned default 0 not ...
user avatar
  • 147
3 votes
2 answers
1k views

Having charset "utf8mb4" - does it mean every character takes 4 bytes, or only those that need 4 bytes?

I have a VARCHAR(80) utf8mb4 column, and I'm testing adding some ASCII & Emoji characters, and use LENGTH() and CHAR_LENGTH() to understand the differences. From reading in different places, my ...
user avatar
  • 599
0 votes
1 answer
39 views

Does the collation of ENUM columns impact their size?

What does it mean when we say that an ENUM has a charset of utf8mb4, versus latin1? My understanding is that the ENUM holds a numeric index (1, 2, 3, 4...) that identifies which of the ENUM values (...
user avatar
  • 599
0 votes
1 answer
730 views

Cannot use GRANT ALL anymore on RDS MariaDB 10.5 but can on 10.2

Using the default admin user I created on a new RDS instance with MariaDB engine 10.5 I cannot use the GRANT ALL anymore and instead get "access denied". This works fine on the 10.2 instance ...
user avatar
1 vote
2 answers
742 views

Update Column from Recursive CTE (MySQL/MariaDB) [closed]

I need to update the same table I'm using to create a recursive cte. The cte works as a select statement, but I can't get it to actually insert those values back into the table. My code is below- ...
user avatar
1 vote
1 answer
35 views

Is CHARSET important for tables with INT only?

My server default CHARSET is UTF-8 (set in my.cnf). I create tables like CREATE TABLE t1 ( ID int(11) unsigned NOT NULL AUTO_INCREMENT, Title varchar(255), PRIMARY KEY(ID) ) ENGINE=InnoDB DEFAULT ...
user avatar
  • 4,179
1 vote
1 answer
351 views

mysqldump select queries appearing in slow query log after upgrade

We recently upgraded from MariaDB 5.5 to MariaDB 10.5 Since doing so, we have a couple of select queries appearing in the slow query log when we backup databases using mysqldump. One of the tables is ...
user avatar
  • 113
0 votes
2 answers
59 views

SELECT with where between not working as expected

I'm trying to use this query, to filter between the names C89 to C110. SELECT id FROM servers WHERE server_name BETWEEN 'C89' AND 'C110' But this doesn't work. If I change c89 to c90, it at least ...
user avatar
  • 188
0 votes
0 answers
315 views

MariaDB low CPU usage, high load average, spiky load

I have been trying to figure out, what could possibly cause very strange spiky load on a DB server. I am running Ubuntu 20.04LTS and mariaDB 10.5.7. 8CPUs, 16Gb RAM. There are 2 separate APIs ...
user avatar
  • 101