New answers tagged mariadb
0
The data type you choose to store this key isn't going to make much of a difference for your performance. Rather, you should store the pieces of the key that you'll need to search on, in their own columns as well that way you can avoid wildcard searches (contains, ends-with, etc) which are inefficient and do exact searches instead. You can achieve this ...
0
You should use the ROW_NUMBER() or LAG() and LEAD() window functions to get the previous and next N number of rows relative to the current row.
For example:
WITH leaderboard AS
(
SELECT student_no, SUM(points) AS points_total, ROW_NUMBER() OVER (ORDER BY SUM(points) DESC, timestamp ASC) AS leaderboard_rank
FROM student_points
GROUP BY student_no
)...
0
SELECT z.id, z.level, z.score, z.`timestamp`
FROM ( SELECT w.*
, @num := @num+1 AS num
-- just to get the 'num' for middle line:
, IF( w.id = 5, @hit := @num, NULL ) AS unused
FROM table AS w
WHERE @num := 1 -- UDV initialization
AND w.level = 1
ORDER BY w.score DESC
...
0
You can do this by setting long_query_time as follows:
If you want it done permanently set it in mysql.cnf in the [server] section.
long_query_time=123.123 (down to millisecond precision)
From here - you can also set it dynamically.
Issue the command (from the mysql CLI (Command Line Interface) client):
SHOW VARIABLES LIKE '%long%'
or similar and set it. ...
0
After some digging, I managed to successfully copy history and trends from one item to another.
The sort of syntax I used is this:
History table:
INSERT INTO history (`itemid`, `clock`, `value`, `ns`)
SELECT '<new_itemid>', `clock`, `value`, `ns`
FROM history
WHERE itemid = <old_itemid>;
Trends table:
INSERT INTO trends (`itemid`, `clock`, `num`,...
0
The values for id 5 and LIMIT must be calculate beforehand and passed on to the query, because LIMIT must have fixed values or you have to use a prepared statement
CREATE TABLE table1
(`id` int, `level` int, `score` int, `timestamp` varchar(19))
;
INSERT INTO table1
(`id`, `level`, `score`, `timestamp`)
VALUES
(4, 1, 70, '2021-01-14 21:50:...
0
What MySQL calls a database is in fact a schema. They say as much in their documentation:
CREATE SCHEMA is a synonym for CREATE DATABASE
Schema is a logical grouping of database objects (tables etc.); multiple schemas can be defined in a single MySQL instance and you use the same connection properties (except the "database"/schema name) to access ...
0
Analysis of GLOBAL STATUS and VARIABLES:
Observations:
Version: 10.5.8-MariaDB-log
32 GB of RAM
Uptime = 07:15:44; some GLOBAL STATUS values may not be meaningful yet.
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.
The More Important Issues:
Since you are not using MyISAM, you could decrease ...
0
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 differently than MySQL:
In MySQL the number of FD (open_files_limit) used is: table_open_cache x 2 + ...
In MariaDB the number of DD used is table_open_cache x ...
1
DDL changes implicitly commit only if they are in the same connection. As they are in a different connection from the mysqldump they lock wait until the dump is complete and then they will complete.
As such hopefully DDL changes aren't as time critical and --single-transaction will suit your needs without any addition fiddling.
Alternate would be to create a ...
0
I specify table_open_cache_instances = 8
That must be changed in the configuration [which it seems to be??], else it is lost in the next restart. (This applies to most settings.)
Meanwhile, this is much too high:
table_open_cache = 207000
Lower that to 10000 for starters.
Then provide SHOW GLOBAL STATUS LIKE 'Table_open_cache%'; so we discuss whether it ...
2
As you can see underscore is a wildcard in a LIKE comparison.
If you want the underscore recognizes you need to escape it with \ ´
But this is basic sql and nothing strange.
Finally it is good practise not to use wildcards reseved words in table names or column names, so you don't get confused and of course all others
SELECT table_name FROM ...
1
depending on Version utf8 is a synonym for utf8mb3 or utf8mb4.
so you are always compatible with the version and you can restore a backup without problems, if the version has no utf8mb4 support
2
The reason for that messages is the client's inactivity. If no actions from the client for wait_timeout seconds then connection will be closed with a message.
That is the normal behaviour and that message was hidden by default log_warnings = 1 before mariadb 10.2.24. All newer releases has log_warnings = 2 and logfile now is filled by "(Got timeout ...
0
It is hard to explain where you go wrong, because your logic doesn't fit in mysql.
AS you can see in my sample, you have to add a column that describes, from which table you get the data
This you can use to SUM the amount
CREATE TABLE table1
(`date` varchar(10), `amount` int)
;
INSERT INTO table1
(`date`, `amount`)
VALUES
('2018-02-17', 10....
2
You try to add to the string 'foo' 5 days.
Use backticks for column names
SELECT * FROM table WHERE DATE_ADD(`foo`, INTERVAL 5 DAY) < '2021-01-09 00:00:00'
1
Your query is not valid SQL, it is only allowed if ONLY_FULL_GROUP_BY is missing from @@sql_mode. I moved your example to Fiddle1 and tried. Note that I set
set @@sql_mode=concat(@@sql_mode, ',ONLY_FULL_GROUP_BY');
and as a consequence 'tmp.bid' isn't in GROUP BY is the error message you get from your query.
But, let's enable partial GROUP BY and see where ...
3
Per create user % has a wildcard meaning. _ also matches a single character like the like expression.
* has no meaning and was probably put there by someone not familiar with what they are doing. Recommend dropping this use.
2
Column-oriented database engines, not just MariaDB ColumnStore, are specifically optimized for read operations on large volumes of data. For this they use auxiliary data structures (like ColumnStore extent maps), query parallelism, compression etc. This comes at the cost of increased overhead for standard DML operations. This is often explicitly stated in ...
0
767 Limit in InnoDB Indexes lists 5 work arounds for the 767 problem.
But, these days, the preferred solution is to upgrade:
This problem exists before the limit was raised in 5.7.7 (MariaDB 10.2.2?).
0
It appears as if the user you are logged in as does not have the privileges needed to edit access. The edit links are only shown if your user is able to modify permissions.
1
I'm not an expert in Mariadb but I think you cannot do it. From the documentation on generated columns
Generated columns can be referenced in the INSERT, UPDATE, and DELETE statements.
However, VIRTUAL or PERSISTENT generated columns cannot be explicitly set to any other values than NULL or DEFAULT. If a generated column is explicitly set to any other value,...
1
What is the client? Does it have "auto reconnect" turned on? Turn it off.
What I think is happening:
The UPDATE croaks (in any way) and gets rolled back.
The connection is reestablished, perhaps with autocommit.
The INSERT runs successfully and autocommits.
The COMMIT is silently ignored.
Put error checks after every SQL statement.
0
In wsrep_node_address on Amazon EC2, you must use the global DNS name, instead of the internal IP address.
0
The following day I got the same errors at exactly the same time (2am and 2:05am), so looked into it more.
The :q felt like a bit of a clue to me. I couldn't really understand how I could have accidentally affected MariaDB when (failing) to quit vim... but I went looking in my /var/lib/mysql directory, and sure enough there was an empty directory d:q. ...
0
I'll add an answer with some tips that hopefully will make it a bit easier for you to construct and maintain your queries. First, I will suggest that you use aliases for your tables. Your query then becomes:
SELECT *
FROM hyung as h, masterdata_hyung_description as mhd
JOIN masterdata_hyung_event as mhe
ON mhd.eventId = mhe.eventId
WHERE h.Taket = '...
0
After full days my noob brain tried to work hard, I've found my solution.
SELECT * FROM hyung, masterdata_hyung_description #this
JOIN masterdata_hyung_event ON masterdata_hyung_description.eventId = masterdata_hyung_event.eventId #and this
WHERE hyung.Taket = '1822171' AND masterdata_hyung_description.hyungDescriptionId = hyung.descriptionId
Not tested ...
3
A simple solution is using IN clause in the following way:
DELETE FROM base_tbl
WHERE (f1, f2) in (select f3, f4 from del_criteria_tbl)
1
You can use a MYSQL specific construction like:
DELETE a.*
FROM base_tbl a
JOIN del_criteria_tbl b
ON a.f1 = b.f3
AND a.f2 = b.f4
or use a standard variant:
DELETE
FROM base_tbl a
WHERE EXISTS (
SELECT 1
FROM del_criteria_tbl b
WHERE a.f1 = b.f3
AND a.f2 = b.f4
)
0
There's no need to change the config file as bind-address is commented out.
The issue is that you need a user to be authorized to a host that's not localhost, root is authorized to localhost only. Creating a new user from inside the container solved the problem.
mysql -u root -p
CREATE USER 'user1'@'%' IDENTIFIED BY 'xxxxxxx';
GRANT ALL PRIVILEGES ON *.* TO '...
0
This is how you do it:
docker run -d -p 3306:3306 --name cont_mariadb
-v vol_mariadb:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=my-pwd mariadb/columnstore
0
Connection aborts is happening in MariaDB 10.2 but not in 10.1 even with log_warnings level set to 2
According to the general consensus that answers shouldn't be posted as comments, following my short answer.
You have set a timeout of 7 minutes and your database instance is aborting the connection after 7 minutes. Works as designed.
General information is logged in the General query log and contains:
Established client connections and statements received ...
0
Rate Per Second = RPS
Suggestions to consider for your my.cnf [mysqld] section
log_error=master-server-name-error.log # to document startup confirmation and later errors
REMOVE thread_cache_size to allow MariaDB default to be used and reduce threads_created
join_buffer_size=1M # from 16M for this per connection RAM request
key_cache_age_threshold=7200 # ...
1
MariaDB's memory usage grows over time. It stops growing when certain configurable settings are hit. In practice, it mostly stops growing long before the 'theoretical' limit is hit.
The buffer_pool is allocated only in RAM. It is the main memory user that grows over time, stopping when it hits innodb_buffer_pool_size.
Since the buffer_pool is a "cache&...
1
You are out of RAM.
Plan A: innodb_buffer_pool_size = 150M until you figure out what else to do.
Plan B: Get more RAM.
Plan C: Downgrade WP.
Plan D: Get a second server; put some 'users' on each.
Plan E: Decrease the number of client threads your web server is allocating.
Plan F: Decrease max_connections in MySQL.
How much RAM do you have? Keep in ...
0
I was playing around with my query and came up with this. It is much quicker than what I had before. So much so, that I think this might be the best way to do stuff like this.
with recursive array_values as (
select
json_extract(pr.record_data, '$.*.A.B.C[*].D.*') val
, pr.id
, json_length(json_extract(pr.record_data, '$.*.A.B.C[*...
Top 50 recent answers are included
