Skip to main content
6 votes

Unable to create tables with ROW_FORMAT=COMPRESSED

If someone else stumbles across this thread (like me) looking for a solution to a 4047 InnoDB refuses to write tables with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE or similar error from the mariadb ...
Echsecutor's user avatar
3 votes

MariaDB 10.4. No traffic yet high CPU usage

Ok i found out it was caused by innodb-encrypt-tables=FORCE and innodb-encrypt-threads=4, after reducing down to =1 problem (load) disappeared.
ensim's user avatar
  • 81
3 votes
Accepted

MariaDB Aborted connection events

That means that some application connects on the TCP port to the server, and does not authenticate within a given timeout (which I think is 5 seconds by default). There is a way to suppress these ...
Vladislav Vaintroub's user avatar
3 votes

10.4.10-MariaDB Column 'authentication_string' is not updatable

use SET PASSWORD FOR user@localhost = PASSWORD('passwordUwant') ; the cause of this problem is because user is a 'view' now source: https://mariadb.com/kb/pt-br/set-password/
user201553's user avatar
3 votes

10.4.10-MariaDB Column 'authentication_string' is not updatable

You are suppose to run mysql_upgrade command for Mariadb instance. Command is as following : mysql_upgrade [--force] [--user=# --password --host=hostname --port=# --socket=# --protocol=tcp|...
JYOTI RAJAI's user avatar
3 votes

Join without indexing the entire table

You need to add these indexes: CREATE INDEX ix_forum_posts_topic_id ON forum_posts(topic_id); CREATE INDEX ix_forum_topics_last_activity ON forum_topics(last_activity); CREATE INDEX ...
NikitaSerbskiy's user avatar
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
2 votes
Accepted

SQL query with "INTO OUTFILE" does not work on MariaDB 10.4 but does on 10.0

Use SELECT * FROM ( SELECT 'Name','Address1','Address2', 'City', 'State', 'Zip', 'Phone', 'SSN', 'Credit Card', 'County', 'Comment', 'Company' UNION ALL SELECT ...
Akina's user avatar
  • 20k
2 votes
Accepted

How to model dependent categories (parent-child-relation)?

First you have to separate the nodes subordination from the nodes itself. You need a pivot table trees having exactly two columns: nodeID -- parentID. The node having parentID equal to NULL is a root ...
Kondybas's user avatar
  • 4,548
2 votes
Accepted

Extract a substring where the delimiter might appear more than once

There a few possibilities here - Solution1 uses standard MariaDB string functions, and Solution2 makes use of regular expressions (regexes - excellent site here, quick start here). You can also use ...
Vérace's user avatar
  • 30.2k
2 votes
Accepted

JSON in MySQL : get data from an object within a range of keys

Possible realization: WITH RECURSIVE cte AS ( SELECT @from num UNION ALL SELECT num+1 FROM cte WHERE num < @till ) SELECT CONCAT('key', num) `key`, ...
Akina's user avatar
  • 20k
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
1 vote

sometimes a query that runs really fast will take too long to finish in mariadb

Indexes that may help: price_index: INDEX(customer_group_id, website_id) cat_index: INDEX(category_id, store_id, product_id, position, visibility) inventory_stock_5: INDEX(is_salable, sku) links: ...
Rick James's user avatar
  • 78.6k
1 vote

Extract a substring where the delimiter might appear more than once

Do it backwards: search the substring until the hyphen from the end of the string (using the SUBSTRING_INDEX function) then TRIM the found part from the value. If the delimiter is strictly those as ...
1 vote
Accepted

what happens If I remove mysql-bin.index file?

If you erased all the binary logs, the mysql-bin.index can also go. Do you still need binary logging ? You must restart MariaDB, and the mysqld process will recreate mysql-bin.index it for you. If you ...
RolandoMySQLDBA's user avatar
1 vote

MySql suddenly started to choose different index for query, which causes it to be slow

Give these a try: at_status: INDEX(entity_id, value_id, value, attribute_id, store_id) at_status_default: INDEX(entity_id, value, attribute_id, store_id) at_visibility: INDEX(entity_id, value_id,...
Rick James's user avatar
  • 78.6k
1 vote

Can I improve this query by adding indexes or tuning mariadb config?

There's a lot going on in your query, and potentially a lot of room for improvement. As a general answer and set of advice, yes creating proper indexes on your tables involved should help improve the ...
J.D.'s user avatar
  • 38.1k
1 vote
Accepted

Select data from a table to insert/update another similar table

Here's how to update the records that exist in catalog_product_entity_varchar and relate to a record in catalog_product_entity_text: UPDATE catalog_product_entity_varchar cpev INNER JOIN ...
J.D.'s user avatar
  • 38.1k
1 vote
Accepted

an error in your SQL syntax; check the manual that corresponds to your MySQL server version

Your CREATE TABLE will fail because there is no PIN column mentioned in KEY `idxPIN` (`PIN`) line. MySQL 5.6 does not allow an expression as default value. Use trigger: CREATE TABLE `Customer` ( `ID`...
Akina's user avatar
  • 20k
1 vote
Accepted

mariadb: replication master and slave have equal MySQL server ids

Use RESET SLAVE ALL on the master to forget about is slave replication setting to itself.
danblack's user avatar
  • 7,809
1 vote
Accepted

MariaDB Optimization with Mysql Tuner

I used to use MySQL on a 256MB, non-dedicated, machine. But, thanks to OS bloat, more MySQL/MariaDB features, etc, 512MB may be the minimum these days. If you set things too high in my.cnf, there ...
Rick James's user avatar
  • 78.6k
1 vote

Covering Index Not Being Used Effectively

Using index means that an index was "covering" and was used. Or, at least, when the EXPLAIN was run, it decided that. I have looked at thousands of EXPLAINs and slowlog entries. In theory, ...
Rick James's user avatar
  • 78.6k
1 vote

Mariadb always writes tmp disk to a file

Read the manual where it mentions using MEMORY for temp tables and when it will switch to MyISAM, either because of limitations such as TEXT or because of exceeding LEAST(tmp_table_size, ...
Rick James's user avatar
  • 78.6k
1 vote
Accepted

UTF8mb4 MariaDB 10.4 - Max key length is 767 bytes error

I'd keep the utf8mb4 as the default. You can use innodb_large_prefix=1 to get past the index length limitation. Its also quite possible that symmetricDS doesn't actually need 255 length character ...
danblack's user avatar
  • 7,809
1 vote

Why doesn't MariaDB server recognise Server Audit settings in config file?

From the documentation: Although the plugin's shared library is distributed with MariaDB by default, the plugin is not actually installed by MariaDB by default. So you need to install the plugin ...
dbdemon's user avatar
  • 6,381
1 vote

Join without indexing the entire table

Turn the query inside-out. First, let's check the viability of such. Will this give you the correct 3 ids? SELECT ft.id FROM forum_topics AS ft ORDER BY ft.last_activity DESC LIMIT ...
Rick James's user avatar
  • 78.6k
1 vote
Accepted

Join without indexing the entire table

OK - I've wrestled with this and I've come up with a query which, while not perfect, should run better than your one! I have no sample data, but here is the result of EXPLAIN EXTENDED of your query (...
Vérace's user avatar
  • 30.2k
1 vote

slow UPDATE of rows by COUNTing from another table

t2 has no useful index. It needs INDEX(id) for much-improved speed. If you have watered down the table and queries, then this answer may not be sufficient.
Rick James's user avatar
  • 78.6k
1 vote
Accepted

slow UPDATE of rows by COUNTing from another table

Remove correlation: UPDATE t1 JOIN ( SELECT t2.id, COUNT(*) c1 FROM t2 GROUP BY t2.id ) t3 ON t3.id=t1.id SET t1.c1 = t3.c1;
Akina's user avatar
  • 20k
1 vote
Accepted

How to add/update data from slave server to master server in MariaDB?

You can use master-master replication which consists of two master-slave configurations: One master-slave where node 1 is master and node 2 is slave, and one where it's the other way around. There ...
dbdemon's user avatar
  • 6,381

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