Skip to main content
7 votes

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

Actually, my answer is in the Documentation: https://mariadb.com/kb/en/unicode/ UTF-8 encoding using one to three bytes per character. Basic Latin letters, numbers and punctuation use one byte. ...
Nuno's user avatar
  • 829
4 votes

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

It appears that in 10.3 there was a new privilege introduced, DELETE HISTORY, which means ALL will not work anymore if the user doesn't have it. It still works. However, in 10.3 there is a new ...
Elijah Lynn's user avatar
3 votes
Accepted

Is CHARSET important for tables with INT only?

The character set and collation of a table affects the newly created columns in that table. It also affects char/text columns without a specified character set/collation. So your int only tables don't ...
danblack's user avatar
  • 7,809
3 votes

Can I upgrade MariaDB replication infrastructure gradually?

A MariaDB replica can replicate from an older master. The other way around won't work. You should add a step 2.5: stop replicating from M1 to M2 (even if it's just a standby master). After step 4, M1 ...
Federico Razzoli's user avatar
3 votes
Accepted

SELECT with where between not working as expected

A shorter answer: ... WHERE MID(server, 2) BETWEEN 89 AND 110 Or, if you need to limit to the "C" set of servers: ... WHERE MID(server, 2) BETWEEN 89 AND 110 AND server LIKE "C%&...
Rick James's user avatar
  • 78.5k
3 votes
Accepted

How to COUNT co-tags from a tag_map table in mysql?

SELECT t1.TagID AS TagID1 ,t2.TagID AS TagID2 ,COUNT(1) FROM TagMap AS t1 JOIN TagMap AS t2 ON t1.ArticleID = t2.ArticleID AND t1.TagID <> t2.TagID GROUP BY t1.TagID, t2.TagID UPDATED: ...
NikitaSerbskiy's user avatar
3 votes

High CPU usage, low memory usage on Mariadb 10.5.8 -- SQL tuner complaining about memory, performance seems fine

Increase innodb_buffer_pool_size to be about 70% of available RAM. High CPU implies poor indexing and/or poor query formulation. Let's see some queries and the matching SHOW CREATE TABLE. INDEX(`...
Rick James's user avatar
  • 78.5k
3 votes
Accepted

Disabling Aria INSERT log in MariaDB

After lots of experimentations, I found out that the problem was not TRANSACTIONAL, but PAGE_CHECKSUM. The default behaviour of Aria is PAGE_CHECKSUM=1 for which Aria keeps a copy of the inserting ...
Googlebot's user avatar
  • 4,531
3 votes

Forcing InnoDB to build the secondary index

I think you have some misunderstandings. When you insert a row, InnoDB doesn't rebuild the whole index. It just adds an entry to the existing index. It would be incredibly bad for performance if it ...
Bill Karwin's user avatar
  • 15.1k
2 votes
Accepted

Troubleshooting high memory usage on MariaDB 10.5.4

POSSIBLE MEMORY LEAK ISSUES There have been three releases since version 10.5.4 03 Nov 2020 : MariaDB 10.5.7 07 Oct 2020 : MariaDB 10.5.6 10 Aug 2020 : MariaDB 10.5.5 There have been some memory ...
RolandoMySQLDBA's user avatar
2 votes

Server with mariadb sometimes dies under high load

I assume you hit the open_files_limit. This should be indicated somewhere further up of your warning about the instances in your MariaDB Error Log. The problem here is, that MariaDB behaves ...
shinguz's user avatar
  • 59
2 votes
Accepted

Can I upgrade MariaDB replication infrastructure gradually?

Fast forward few months and I want to give warning to using different major versions for replication. Even when slave is newer than master, and hence should keep compatibility, there are possible SQL ...
michnovka's user avatar
  • 145
2 votes

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

Plan A (if possible): Use "huge pages" for the buffer_pool. They can't be swapped, thereby helping keep from swapping MariaDB. Plan B: Use swappiness = 1 -- This, in theory, discourages ...
Rick James's user avatar
  • 78.5k
2 votes

mysqldump select queries appearing in slow query log after upgrade

By default, MariaDB's mysqldump will log queries (--log-queries enabled by default) to the slow log when executing the reload of a dump. The MariaDB doc says: When restoring the dump, the server will,...
RolandoMySQLDBA's user avatar
2 votes

Histograms in MySQL

Histograms can help the query optimizer to better estimate how many rows will pass a range condition (such as a > 10 and a < 100 or a in (1, 2, 5)). Histograms will be useful if the optimizer ...
Vicențiu Ciorbaru's user avatar
2 votes

How to determine if I have an undersized innodb_buffer_pool for MySQL

Run SHOW ENGINE INNODB STATUS\G in the mysql client. Eventually you see a section on the buffer pool: ... ---------------------- BUFFER POOL AND MEMORY ---------------------- ... Buffer pool hit rate ...
Bill Karwin's user avatar
  • 15.1k
2 votes
Accepted

MariaDB hidden disk usage for log dir

OK, I've find the problem and it's what I added as "Update 1" to the question. The main issue was about logrotate; lets see the original content: # - I put everything in one block and added ...
RoohAllah Godazgar's user avatar
2 votes
Accepted

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

You'll want to use a window function, specifically the LEAD() function would make great use here like so: -- Gets the following row's search text and compare it to the current row's search text to see ...
J.D.'s user avatar
  • 38.1k
2 votes

Replication from multiple masters to one slave

Replication maintains synchronization of the data. It does not initialize the Repicas. That is, you must first copy the data from the Primaries to the Replica, then establish the "multi-source&...
Rick James's user avatar
  • 78.5k
2 votes
Accepted

MariaDB Maxscale caching not working

Edit: You forgot to add filters=Cache to the service definition. This is why it looks like it's not working. Filter by default apply only to the services that use them. To take them into use and ...
markusjm's user avatar
  • 283
2 votes
Accepted

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

I want to join the tables on item_id You're successfully doing this. You're justing using the wrong type of join. I also want data from bar_recvd_details, even if recvd_value IS NULL You can't do ...
Brendan McCaffrey's user avatar
2 votes

MariaDb performance issue

Once you have twice as much RAM as the dataset size, adding more RAM will not help performance. Most of the 128GB is unused now. A slow query can, in many cases, be sped up by having a better index ...
Rick James's user avatar
  • 78.5k
2 votes

Finding gaps between the values of a column in MySQL

Use LEAD(year) or LAG(year) to discover a gap of 10+ years. Then use ROW_NUMBER() to keep rows before that gap. (See "windowing functions", available since MariaDB 10.2 or MySQL 8.0.)
Rick James's user avatar
  • 78.5k
2 votes
Accepted

How to fix performance issue with row_number and multiple schema?

When running SELECT count(*) FROM arkbans.litebans_bans WHERE uuid = p.uuid (and by replacing p.uuid by a value), I never go more than 0.0002s. Yea running it for a single uuid is only 0.0002s but ...
J.D.'s user avatar
  • 38.1k
1 vote

slow disk with InnoDB page compression

I face a similar issue with MariaDB when using page compression. My inserts would be fast, but after a few hours, inserts would 'freeze' for many seconds/minutes at a time. I tried tweaking the ...
arashb31's user avatar
  • 121
1 vote
Accepted

Stored procedure prepared statement no error and not working

Doh, I looked at numerous examples online, and these were pretty much the same as I used in my example, however I did think I was missing something, then I found another example which proved that I ...
SPlatten's user avatar
  • 149
1 vote

MariaDB optimizing geo IP lookup with Maxmind GeoLite2 database

OR is inefficient. It can be turned into UNION. An example: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#or Why use INET6_ATON on IPV4 values? Finding something in a range is poorly ...
Rick James's user avatar
  • 78.5k
1 vote
Accepted

need help building a query to detect duplicates on multiple columns

You can accomplish this with grouping on your store_id and value in a CTE or subquery to get the dupes and then re-joining the dupes to your catalog_product_entity_varchar table like so: WITH ...
J.D.'s user avatar
  • 38.1k
1 vote
Accepted

Does the collation of ENUM columns impact their size?

No difference. ENUM is stored as a 1- or 2-byte number, where 0 maps to the first item in your list. You deal with the column as a string, but the disk only sees a number. 2-bytes is used only when ...
Rick James's user avatar
  • 78.5k
1 vote
Accepted

Update Column from Recursive CTE (MySQL/MariaDB)

For this, I changed the recursive CTE to have both the anchor and child in the same CTE as that is standard. With that, I was able to show how the original table gets updated. The answer consists of 3 ...
Chuma's user avatar
  • 206

Only top scored, non community-wiki answers of a minimum length are eligible