Questions tagged [mariadb]
Open source RDBMS that forked from MySQL. Add a version-specific tag like mariadb-10.5 if that context is important.
2,642
questions
0
votes
1
answer
8
views
io.debezium.DebeziumException: Client requested master to start replication from impossible position
I am using Kafka and Debezium to connect to a MariaDB
I have a problem where in the event the MariaDB loses power and shutsdown abruptly, on restart Debezium is no longer able to connect
I have this ...
- 1
0
votes
0
answers
25
views
MariaDB Error - Access denied for user 'root'@'%' (using password: YES)
I am trying to grant file privilege and grant a role to the user but unsuccessful:
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES);
I am able to assign select on . directly ...
- 1
1
vote
1
answer
25
views
Are results stored at server if a sql-query statement executes a Stored Procedure returning multiple results/rows?
Background:
We are using mariadb server with a mariadb-connector-cpp for establishing connection between clinet&server. At max we have 8000 rows of data (total num chars is 250 per row).
Exploring ...
- 11
0
votes
1
answer
33
views
cpulimit/iolimit per mysql/mariadb user
I can set cpulimit, iolimit, etc for mysql process by moving its pid into cgroup.procs file of a cgroup.
But single mysql process handles all the queries(threads) and I cant set cpulimit to the entire ...
- 1
0
votes
0
answers
17
views
Mariadb Triggers after multiple insert, update one table one one time
CREATE TRIGGER after_composers_insert AFTER INSERT ON composers
FOR EACH ROW UPDATE listings SET composer_name = NEW.composer_name
WHERE composer_id = NEW.composer_id;
CREATE TRIGGER ...
0
votes
1
answer
28
views
Migrated to dedicated MariaDB makes app slower
I'm new to dba stackexchange, hope you can help me :-)
I do have a Hetzner root server (EX52-NVMe Intel Core i7-8700 Hexa Core 64 GB DDR4 2× 1024 GB NVMe SSD) and Docker on it. At first, it was ...
1
vote
0
answers
39
views
Inconsistent result when running same query on slightly distinct MariaDB versions
I'm investigating the difference in the results given by the same SQL query on two servers running slightly different versions of MariaDB (10.5 and 10.11).
Test query is:
SELECT a.Name
FROM Artist a ...
- 111
0
votes
0
answers
8
views
MariaDB could not add nodes to cluster
could someone know how to solve below issue ?
i have allow all user to all member node
all node can communication as well with port 3306
the issue as below :
WSREP: GCache history reset: 39163b37-...
- 1
0
votes
1
answer
21
views
Myloader error during Restorin MariadDB Slave: Duplicate entry 'ТОБАР' for key 'UX_B_SEARCH_STEM'
I have lost my slave. To restore it I have locked tables on the master host and then have created a dump using mydumper tool. After that I have tried to restore my slave using myloader tool but during ...
Skuld
0
votes
0
answers
27
views
Why queries in cache cleaned every 30 mins?
I try to figure out, why my mariadb instance clean queries in cache. It happens every 30 minutes. It doesn't happen by cron or event scheduller. What i'am missing? Some mariadb settings?
MariaDB [(...
0
votes
1
answer
73
views
Mariadb not using indexes post upgrade to 10.5.23 from 10.2
We upgraded our MariaDB server from 10.2 to 10.5.23 and have found a significant CPU spike after this and almost most of the queries landing in the slow query logs. On investigation, we found that ...
- 111
1
vote
0
answers
85
views
Moving from MySQL to MariaDB, slower performance with some query's
recenty we've have moved all of our server to new hardware and to MariaDB . I've installed a new server with VirtualMin for managing all of our company's applications. at a first glance most of the ...
- 11
0
votes
1
answer
36
views
Sum of Round vs Round of Sum
This is a very short sample of what I have in my database.
CREATE TEMPORARY TABLE temp_ledger(
book_entry INT,
credit DOUBLE(24, 8),
debit DOUBLE(24, 8)
);
INSERT INTO
temp_ledger(...
- 103
0
votes
0
answers
27
views
Replication between two galera clusters
I currently have two 3 node mariadb (10.5.9) Galera clusters set up in different locations, I'm using standard replication to copy data between the primary and secondary.
What I've recently noticed is ...
1
vote
1
answer
27
views
MariaDB: inconsistent SELECT results involving JSON
When executing the following statments on MariaDB 10.6.14
SET @json_1 = JSON_OBJECT('id', 'name');
SET @json_result_1 = JSON_OBJECT('test', @json_1);
SELECT @json_result_1;
SET @json_result_2 = ...
2
votes
1
answer
58
views
Insert gives error code 1366 even though charset is utf8mb4
I am trying to manually copy a table from one server to another.
I used mysqldump to export the table. The import fails with the following error:
ERROR 1366 (22007) at line 6: Incorrect string value: '...
- 171
0
votes
0
answers
25
views
When is INDEX included in Row-Based Binary Log using "full-image"
MariaDB Config
version:
> mysql -V
mysql Ver 15.1 Distrib 10.4.27-MariaDB, for Win64 (AMD64), source revision 0946c99e7d6f7ac9dfcf3e60dae6ae85161d5ef2
my.ini:
[mysqld]
log-bin=bin.log
log-bin-index=...
- 1
2
votes
1
answer
77
views
Should I use this partitioning/indexing strategy for a table that grows by about a billion rows per year?
I'm a software engineer with about 10 years of experience building Laravel web applications for small businesses. I'm pretty comfortable with database design, but it's not my greatest strength.
One of ...
- 23
0
votes
0
answers
31
views
Upgrade from MySQL 5.6 and 5.7 to MariaDB
We have several servers with MySQL 5.6.51 and several with MySQL 5.7.39. What are the right steps to upgrade to latest MariaDB LTS 10.11? I think we can't go directly to 10.11 or?
MySQL 5.6.51 > ...
- 69
0
votes
0
answers
26
views
Structure for table to record events in other tables?
Say I have three tables:
people
groups
memberships
with the following primary keys:
uid
gid
uid+gid
I would like to record events such the creation/deletion off people/groups/memberships. I could ...
- 103
0
votes
2
answers
51
views
mariadb table partition and index tables
I have been reading about the table partitioning in mariadb.
I know it will not give any direct performance gains. But what about the index tables? Are they also partitioned?
Let's say a table is ...
- 147
0
votes
1
answer
20
views
Is commit of first insert necessary if second insert has constraint depedent on first insert?
Using MariaDB I am running an insert on one table and then trying to run another insert on second table. Due to a constraint the second insert should only succeed if the first one was successful.
If I ...
- 103
0
votes
2
answers
54
views
How do I optimize this query that gets the newest entry of each group?
I have a query, that finds the newest item of each group based on the ID returned by a subquery. The problem is, that the table has a bit over 5000 entries and even with only this amount of entries ...
- 3
0
votes
1
answer
27
views
MariaDB Galera Cluster (10.6.12) and Laravel WSREP issue
I'm administrating a galera cluster with 3 nodes, serving a Laravel application and HAProxy is the gateway to the cluster. We had an interesting issue, and I'm unable to figure out why was there an ...
- 105
2
votes
1
answer
23
views
Lost Root privelages in MariaDB 10.6
So I can't even remember how this happened, I believe I was worried about how running
mysql -u root
Was allowing the root user to login without a password or even asking for one...
So I went down a ...
- 31
0
votes
0
answers
38
views
MariaDB locking
I have a table W with a primary key, and a table T with a status column and an FK to W.
I am observing some random locking which I don't know where it originated:
T1 does the following:
select where ...
- 141
1
vote
0
answers
39
views
MariaDB, MySQL - aggregated count in group
Suppose there is a table:
first
second
third
1
1
1
1
1
1
1
2
2
1
1
2
2
3
1
And by query:
SELECT first, count(*) AS cnt
FROM table
GROUP BY first
We get result:
first
cnt
1
4
2
1
How to ...
- 111
0
votes
2
answers
48
views
INSERTing millions of files into a table
I archive millions of XML files (1-100MB each) in a table with the structure of
CREATE TABLE Data
(
ID int(11) unsigned NOT NULL,
XML longtext COMPRESSED,
PRIMARY KEY(ID)
) ENGINE=Aria ...
- 4,491
0
votes
0
answers
55
views
Installation and initial startup with mariadb in docker container in unraid
I'm hoping someone can help me. I'm very new with server-side stuff, so if you find yourself thinking, "This guy doesn't know what he's doing," you're right, I don't. :)
I have a brand-new ...
- 101
0
votes
1
answer
34
views
innodb_encrypt_tables = ON Convert table Collation
With Encryption at REST enabled on a MariaDB (10.4) database, can you change the Collation of all tables without unencrypting the database e.g:
alter table <some_table> convert to character set ...
0
votes
1
answer
395
views
how can I view deleted records in an ibd file?
I'm in a situation with a wordpress site where a table wp_options keeps increasing in filesystem size really fast even though it only has 10000 records and we have to constantly optimize it to reduce ...
- 149
3
votes
3
answers
91
views
Do you need to explicitly include the primary key in a composite index?
I recently read that when creating a composite index in MariaDB that the primary key is appended to the end of the index "under the covers". I access my data many times in order by these ...
- 131
1
vote
0
answers
73
views
is it possible to set specific timezone for session in mariadb connector/j
I 'm trying to use timezone setting with mariadb connector/J version 3.x.
In the documentation,in Timezone consideration, it says:
'a timezone': connector will set connection variable to value. ...
- 11
1
vote
2
answers
105
views
How to get local MariaDB to be as fast as local PostgreSQL?
Our scientific application needs to store and query fundamental parameters for a number of molecules. There are 2 to 28 million rows per molecule, but the number of molecules is expected to stay small ...
- 123
0
votes
0
answers
10
views
How can I enable server certificate verification when connecting to a remote host, but not when connecting via localhost?
When I add ssl-verify-server-cert to a [client-mariadb] section in the configuration files, the client correctly verifies the server certificate when connecting to a remote MariaDB instance.
When ...
0
votes
0
answers
16
views
How can I get more verbose TLS certificate errors with MariaDB?
When attempting to connect to MariaDB from a remote client (command line on another server), I get the rather unhelpful:
ERROR 1045 (28000): Access denied for user 'myuser'@'mydomain.com' (using ...
0
votes
0
answers
28
views
ERROR 4078 (HY000) at line 167956: Illegal parameter data types uuid and int for operation '='
I'm using this command to backup all of my MariaDB databases and gzip them all:
mariadb-dump -pPassword --all-databases --routines --events --single-transaction | gzip -c > dump.sql.gz
Then I copy ...
- 1,417
0
votes
0
answers
33
views
MariaDB 11.1.2-GA on Windows 11 Pro crashes every couple of days, sometimes couple of hours
Is there any current (October 2023) solution for MariaDB 11.1.2-GA on Windows 11 Pro crashing every couple of days or even sometimes every couple of hours with the following error in .err log:
InnoDB: ...
- 101
0
votes
0
answers
22
views
Mariadb 10 doesnt start after server crashed
I hope you can help me here with my problem to get my DB back up and running.
Yesterday, Strato crashed the node on which our VM is running. And now the DB is not running anymore. :-(
The mysqld does ...
0
votes
1
answer
24
views
Mariadb using IF in trigger to fill null value on insert
Trying to use a trigger to a) fill in a new column top_category with values left of a colon, found in category, then b) If there is no colon, then put entire insert value into top_category. I can't ...
- 115
1
vote
2
answers
48
views
Mariadb update column during load data of CSV
I am loading .csv files into Mariadb 10.6 using LOAD DATA LOCAL INFILE.
There is a DATE column called posted_on in the inbound data, and I need store the month name in another field monther, ideally ...
- 115
0
votes
2
answers
52
views
Foreign key constraint
Table A: Author Information
CREATE TABLE TableA (
AuthorLastName VARCHAR(255),
AuthorFirstName VARCHAR(255),
BookTitle VARCHAR(255),
PRIMARY KEY (AuthorLastName, AuthorFirstName, ...
- 1
0
votes
1
answer
29
views
Galera Cluster Upgrade
I have 3 node Galera Cluster on Ubuntu 18.04. As part of Ubuntu LCM, I'm upgrading my Setup to 22.04 along with this Galera MariaDB cluster will also upgrade.
I have designed below plan to upgrade my ...
0
votes
0
answers
46
views
MariaDB "select user, host from mysql.user" frozen
database version: 10.6.15-MariaDB
os version: centos 7
I can run select * from mysql.user without any problem, it's very fast to response.
However, select user, host from mysql.user frozen forever; ...
- 111
0
votes
0
answers
34
views
In mariadb can you dump the default mysql database with mysqldump? Error 1045. Access denied!
mysqldump --host=xx.xx.xx.xx --port=xx --user=xxx -p mysql
Enter password:
mysqldump: Got error: 1045: Access denied for user 'xxx'@'xx.xx.xx.xx' (using password: YES) when trying to connect
My user ...
- 111
0
votes
0
answers
70
views
MySQL failing to start
I am using MySQL installed as a service in XAMPP, which is having trouble starting up after a computer restart. It starts up fine when manually triggered. This is the error log after the event:
2023-...
calabacita
0
votes
0
answers
53
views
Intermittent database connection issues and errors following MariaDB upgrade in cPanel on Cloudlinux 7
I am experiencing intermittent database connection issues on my cPanel server running on CentOS 7, following an upgrade of MariaDB from version 10.5 to 10.6. Despite the upgrade process completing ...
Ric Rey Rey
1
vote
0
answers
69
views
How to Optimize SQL Restore Performance with MySQL Dump
I'm currently working on a cron job that involves masking and modifying specific columns in a MariaDB 10.6 database. The process includes taking a dump of the production database using mysqldump and ...
- 11
1
vote
0
answers
15
views
MariaDB node replaces client request for host 127.0.0.1 with localhost
This seems very strange:
I have a MariaDB Galera cluster with three nodes, and each node has virtually the same configuration.
Since it's a Galera cluster, the database users are the same across all ...
- 6,261
0
votes
0
answers
31
views
Why, MariaDB user allowed host IP address also allows a virtual machine on the host with NAT network adapter
The MariaDB user specifies an allowed IP address in the Host column of the mysql.user table. So, it only allows logging in from the host with the specified IP address. And MySQL has the same design.
...
- 139