4
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. European and Middle
East letters mostly fit into 2 bytes. Korean, Chinese, and Japanese
ideographs use 3-bytes. No supplementary characters are stored.
utf8mb4 -- ...
3
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 privilege DELETE HISTORY which your sys_dbcreator is missing, so it doesn't have ALL on the database level anymore, and thus cannot grant it.
The only way to do this ...
3
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%"
Or (to avoid trouble with "24H1"),
... WHERE IF(server LIKE "C%",
MID(server, 2) BETWEEN 89 AND 110,
FALSE)...
2
(Aside from JD's Answer, ...)
SQL syntax is picky; WHERE comes after all the JOINs.
The ON clauses should say how the table is 'related' to the preceding table. Otherwise, it works identically to WHERE.
I'll quibble with the "notoriously inefficient". This depends on the ONs and WHERE. In particular, if WHERE filters on more than one table, the ...
2
Doing smaller transactions and keeping connections not open (Open Transactions) for long time will probably do the job.
And:
The undo log is usually part of the physical system tablespace, but from MariaDB 10.0, the innodb_undo_directory and innodb_undo_tablespaces system variables can be used to split into different tablespaces and store in a different ...
2
Ubuntu-18.04 has systemd v229.
It wasn't until v236 that systemd gained the ability to extend timeout at request of MariaDB
As such the large rollback is exceeding the default 90 second allowed service startup and systemd is oblivious as to why.
If upgrading distribution isn't an option you can increase the timeout in the systemd service for MariaDB
2
WITH
cte_hi AS ( SELECT DISTINCT name, hi_priority.col2, 1 priority
FROM hi_priority
ORDER BY col2, name LIMIT 7 ),
cte_lo AS ( SELECT DISTINCT lo_priority.name, lo_priority.col2, 2 priority
FROM lo_priority
LEFT JOIN cte_hi USING (name)
WHERE cte_hi.name IS NULL
ORDER BY col2, name ...
1
UUIDs are not nice when it comes to performance. In particular, there is no relationship between the 'next' row you fetch via a UUID and all the other rows that are currently cached.
Docker possibly has a "small" value for innodb_buffer_pool_size, thereby leading to I/O when random accesses into the table happen.
UUID v1 can have its bits shuffled ...
1
Short answer
ZFS, etc, are not the main problems. (See Comments for a rebuttal on ZFS.)
Use InnoDB, not MyISAM.
Fix the indexes in postmeta, as discussed here: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta
Avoid index prefixing. (The above link discusses that, too.)
(If you must use MyISAM, then the indexes recommended ...
1
When mysql/mariadb creates columns for UTF8 data they are created big enough to fit any string having given encoding.
VARCHAR(80) COLLATE 'utf8_general_ci' column will be exactly 80x3=240 bytes long.
VARCHAR(80) COLLATE 'utf8mb4_general_ci' column will be exactly 80x4=320 bytes long.
P.S.
InnoDB engine has so called "overflow pages" for table ...
1
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 there are more than 255 options. There are some who shun the use of ENUM altogether; there are some who frown on more than a few options. I say use ENUM for a ...
1
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 parts as it pertains to the query:
A query to show the original contents of the table
The update based on the recursive CTE
A query of the original table ...
1
You can use any query typo with the created (with statements
The update can be de by joining the original with the cnstructed
CREATE tABLE tbl_1min(id int, close int,ema9 varchar(30))
INSERT INTO tbl_1min VALUES (1,1,'test')
SET @NDays := 9;
WITH RECURSIVE anchor AS
(
SELECT
a1.id,
a1.close,
a1.ema9
...
1
The first thought that came to mind was "Wow !!! You must have rather big transactions."
According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace, these are the main issues that cause ibdata1 to grow:
Lots of Transactional Changes
Very Long Transactions
Lagging Purge Thread
The undo logs inside ibdata1 will hold lots of ...
1
Set sync_binlog = 1
When sync_binlog is set to 0 (default), the OS caches all writes to disk and writes in 4K batches. This is for speed. If set to 1, every database update will be immediately written to the binary log. This may cause a higher iops consumption.
1
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, if logging is turned on, log the queries to the general and slow query log. Defaults to on; use --skip-log-queries to disable. Added in MariaDB 10.1.1.
This ...
1
As mentioned in the comments, when comparing strings, they are compared lexicographically not numerically (even when they contain numerical values). What this means is each character is compared to the comparison string's character of the same index. For example, comparing the strings "10" and "3" would result in "10" preceding &...
1
You can use WINDOW functions they exist since 10.2
CREATE TABLE student_points
(`student_no` int, `level_id` int, `points` int)
;
INSERT INTO student_points
(`student_no`, `level_id`, `points`)
VALUES
(12345678, 1, 70),
(10983023, 2, 90),
(24829039, 1, 20),
(12345678, 1, 70),
(10983023, 2, 20),
(24829039, 1, 20),
...
1
The following query would accomplish what you're looking for:
SELECT name, category, percentage
FROM expense_profile
WHERE name = 'Bob'
UNION ALL
SELECT name, 'Other' AS category, 100.0 - SUM(percentage) AS percentage
FROM expense_profile
WHERE name = 'Bob'
GROUP BY name
1
Within the same single query, the WHERE clause always syntactically proceeds all JOIN clauses. Your observations are correct that filtering can occur both in the JOIN and WHERE clauses and that functional commonality between them is called a predicate.
A predicate is a logical expression / condition that evaluates to a boolean value, and hence is how ...
1
No. Keep everything separate, including my.cnf, log files, port, socket, data, etc. Be sure to have a different server_id for each.
Better yet, put them in different VMs (or use Docker). There will still be things that you must be sure to keep separate: data, port numbers, and possibly other things.
1
As someone already mentioned, this has to do with the mysql.user table being changed to a view in 10.4. The problem and solution is documented on the MariaDB website in MDEV-22127.
The solution is to simply add the following two lines to the top of your all-dbs.sql dump file:
DROP TABLE IF EXISTS `mysql`.`global_priv`;
DROP VIEW IF EXISTS `mysql`.`user`;
Only top voted, non community-wiki answers of a minimum length are eligible
