Skip to main content
17 votes
Accepted

How to take mysqldump with Generated column?

This is a problem when using mysqldump from MariaDB with virtual generated columns. MariaDB's mysqldump apparently dumps the generated values, but MySQL only accepts DEFAULT as value for a virtual ...
Dario Seidl's user avatar
13 votes
Accepted

Does MariaDB or MySQL implement the VALUES (expression) table value constructor?

MariaDB 10.3+ Yes, speaking of MariaDB 10.3.3 and later it seems to support table value constructors. SELECT 1, t.7 FROM (VALUES (7),(42)) AS t; 1 | 7 -------- 1 | 7 1 | 42 You'll notice that ...
Vladislav Vaintroub's user avatar
6 votes
Accepted

Change default collation for character set utf8mb4 to utf8mb4_unicode_ci

I don't think there is a way to change that DEFAULT. Anyway, it would be better to use utf8mb4_unicode_520_ci, which is based on a later Unicode standard. Just get into the habit of specifying ...
Rick James's user avatar
  • 78.5k
5 votes

Query joining four tables resulting in one row

You need to join the tables from both queries and then use GROUP_CONCAT() to concatenate the labels: (the query assumes that files (id) is UNIQUE or the PRIMARY KEY`) select files.id, files.name,...
ypercubeᵀᴹ's user avatar
5 votes

Delete data before INSERT

Yes, you can issue a DELETE statement before your INSERT. If you do it inside a transaction, they will be essentially one operation, they will either fail or succeed together (assuming that the table ...
ypercubeᵀᴹ's user avatar
5 votes

How to get more connections for mariaDB or how to reduce RAM usage per thread?

You can increase max_connections without increasing RAM. BUT--Let's discuss whether 175 might actually be too big. If Max_used_connections has not reached 175, then max_connections is not the real ...
Rick James's user avatar
  • 78.5k
4 votes
Accepted

Random row selection but with distinct values for a subset of columns

MariaDB 10.3 supports window functions, so something like this would work: select * from ( select t.*, -- all columns row_number() -- assign sequential numbers over ( -- within a "...
mustaccio's user avatar
  • 26.2k
4 votes
Accepted

MariaDB 10.3: InnoDB much slower than MyISAM

TLDR; The problem turned out to be the file system. Don't use BTRFS for database storage. I did an analysis of MariaDB timings for different file systems using some of the data that I have to work ...
Raul Laasner's user avatar
4 votes
Accepted

Unexpected results while using HAVING without GROUP BY

This is not valid SQL - even though MariaDB and MySQL (until version 5.6) allow it: having budget > avg(budget) So, if you get unexpected results, don't worry much. Just do not use invalid SQL ...
ypercubeᵀᴹ's user avatar
4 votes
Accepted

Can upgrade from MariaDB 5.5 to MariaDB 10.3 directly?

The whole jump it is possible (depends on your data structure) but is not recommended and based on official documentation is not supported. The best option is to perform and step-by-step IN-PLACE ...
Jesus Uzcanga's user avatar
4 votes
Accepted

ERROR 1130 (HY000) not allowed to connect to this MariaDB server facing issue in granting permission

The GRANT statement doesn't work because the user@host combination root@'%' doesn't exist (yet). You can create it and then re-run the GRANT: CREATE USER root@'%' IDENTIFIED BY '...
dbdemon's user avatar
  • 6,381
4 votes

What are "Invisible Columns" and how are they used?

Here's an actual use-case that came up just days after I learned about the invisible columns feature in MariaDB 10.3: Following a new requirement, I had added a new column in the middle of one of our ...
dbdemon's user avatar
  • 6,381
3 votes
Accepted

What are "Invisible Columns" and how are they used?

This is of course not standard SQL, and generally goofy. Instead follow best practices, Never use INVISIBLE columns. Never use SELECT * in production code. Always secure columns that you don't want ...
Evan Carroll's user avatar
  • 63.6k
3 votes
Accepted

JSON field saves as longtext datatype

MariaDB does not have a datatype of JSON. Instead it gives you LONGTEXT. Be aware that there are a lot of differences between MySQL and MariaDB in the area of JSON. Furthermore, different versions ...
Rick James's user avatar
  • 78.5k
3 votes
Accepted

Authentication relying on mysql_native_password seems to also check for Linux user?

Correct. Only the root account on most Linux-based systems can connect to MariaDB (and MySQL) as the root account. This is to prevent poor authentication practices as have been witnessed time and ...
matigo's user avatar
  • 2,028
3 votes

How do I find out what's causing processes to wait for table flush?

I am going to hazard a guess that the first query at the top, as Rick said, is the cause of it, especially if it is an ANALYZE TABLE query. This is a rather typical cause of the queries piling up in ...
Gordan Bobić's user avatar
2 votes
Accepted

MariaDB - how to drop partition on master but not on slave

You could run SET SESSION sql_log_bin=0 in the session before running the ALTER TABLE...
danblack's user avatar
  • 7,799
2 votes
Accepted

MySQL Workbench Visual Explain not working with MariaDB 10.3.9

MySQL Workbench is a MySQL product for MySQL servers only. There are no guarantees that it works with other servers and you you may encounter non-working areas (like you described) or may not even be ...
Mike Lischke's user avatar
  • 1,553
2 votes

How to filter for consecuting timeline events in pure SQL?

You don't need cursor for that. If your id are automatically incrementing, you also don't need ordering by the timestamp, the id is enough. If not, then use timestamps. Note, timestamps have some ...
peterh's user avatar
  • 2,089
2 votes

Get slave status using sql query inside if-else condition

Why not simply do STOP SLAVE? If not "slave_running", it will have no effect.
Rick James's user avatar
  • 78.5k
2 votes
Accepted

Subset of Unordered Pairs of Distinct Primary Keys With Normalization

You can enforce, that valueone < valuetwo. Unfortunately MySQL doesn't support check constraints with which this would be easy (You can define them, but they have no effect). You'd have to use ...
sticky bit's user avatar
  • 4,894
2 votes
Accepted

Insert query result into database row

You need the AUTO_INCRMENT default for your id field, so: ALTER TABLE `payment_transactions_daily_facts` CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT FIRST; Then, next time you run your ...
2 votes
Accepted

MySQL vs. MariaDB Binary Logging Difference

There are many differences in default values for system variables between MariaDB 10.3 and MySQL 5.7, so that can explain both some of the performance and binlog size differences. Additionally, ...
dbdemon's user avatar
  • 6,381
2 votes

Change default collation for character set utf8mb4 to utf8mb4_unicode_ci

Option 1 IF you are using MySQL 8.0.11 or newer (not sure how that equates to MariaDB 10.3.15), and IF you are ok using utf8mb4_0900_ai_ci instead of utf8mb4_unicode_ci then it seems that a server ...
Solomon Rutzky's user avatar
2 votes
Accepted

MariaDB database migration - hangs infinitely while MySQL works no problem

WordPress has an inherent performance problem due to using the EAV schema pattern. Then it fails to adequately index its meta tables. I discuss that here , but since you have an extra column, I will ...
Rick James's user avatar
  • 78.5k
2 votes

How to take mysqldump with Generated column?

We were able workaround by creating the dump not with mysqldump but https://github.com/Smile-SA/gdpr-dump which is based on a PHP base dump tool.
Alex's user avatar
  • 1,203
2 votes

Group_concat on IF Select MYSQL

To alias the expression computed inside a Correlated Subquery, you will need to specify it outside the complete subquery. In this case, it will be outside the If(..) block: SELECT o.order_id, ...
Madhur Bhaiya's user avatar
2 votes
Accepted

MySQL / MariaDB Get last 7 days summarized and with avarages calculated

First, a very nice first post. Both ddl and sample data makes it easy to recreate the setup. Second, all you have to do with your query is to add the date dimension: SELECT plant_id, date(created_at) ...
Lennart - Slava Ukraini's user avatar
2 votes

Get all rows where specific column value occurs more than once (filter out single occurences)

The first approach is to create an index by sessionId. But its datatype is MEDIUMTEXT... So the approach is: normalize your data, move sessionId values into separate table and refer to it by foreign ...
Akina's user avatar
  • 20k
2 votes
Accepted

Get all rows where specific column value occurs more than once (filter out single occurences)

If I understand your requirement correctly, this is the kind of problem where window aggregation shines. Use the window version of the COUNT(*) function on the filtered dataset to obtain the counts ...
Andriy M's user avatar
  • 23.1k

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