Questions tagged [mariadb-10.3]
MariaDB 10.3 was released as stable (GA) with version 10.3.7 on 25 May 2018. Please also tag with mariadb for search purposes.
118
questions
0
votes
0
answers
14
views
How can replication break from a crashed replica?
I have a master-master relationship between two database servers, server A and B. Server B acts as a failover in case server A were to go down. Server B recently went down unexpectedly due to ...
- 167
0
votes
1
answer
19
views
Query with EXISTS-subquery taking about 280 ms in 80 % of cases, and less than 1 ms in 20 % of cases
I noticed a query that seemed to take way too long most of the time, so I investigated it with the Laravel query logger (to get the time and the actual SQL from the query builder).
Excerpt:
>>&...
- 103
0
votes
1
answer
34
views
MariaDB database has no 'max_used_connections'
I have a running MariaDB which has no max_used_connections variable. How can I see it?
I got the list of all variables using the following command from the shell:
SHOW VARIABLES;
but it was not there....
0
votes
1
answer
78
views
How do I find out what's causing processes to wait for table flush?
I'm currently using MariaDB 10.3 and am troubleshooting this case where threads will start to build up until the MySQL service eventually has to be restarted. I was able to capture the process list ...
- 99
2
votes
3
answers
200
views
How to get more connections for mariaDB or how to reduce RAM usage per thread?
We have an MariaDB 10.3 database server running on an 8 core and 64GB RAM machine. The database engine is innoDB. Our current max_connections = 175 and our DB admin tells me that it is not possible to ...
- 125
0
votes
0
answers
121
views
Can't start MariaDB suddenly
● mariadb.service - MariaDB 10.3.32 database server
Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
Active: activating (auto-restart) (Result: signal) ...
- 1
0
votes
1
answer
47
views
Replication from multiple masters to one slave
I have 2 Raspberry PI's and each run a MariaDB 10.3 server. Each has the same databases:
vennens
scheduler
On the slave I've configured the 50-server.conf file with follow contents:
master_pi1....
- 103
0
votes
1
answer
321
views
fix InnoDB: corrupted TRX_NO after upgrading to 10.3.31
After upgrading mariadb from 10.3.25 to 10.3.31 it fails to start like described in this bug report
I temporary fixed it by downgrading mariadb to 10.3.25 again like I described here.
To fix this ...
- 798
0
votes
1
answer
67
views
MariaDB: Periodic copy of data from local database to remote database
I need to periodically copy the outcome of a view on our local database to another remote database.
I've tried using mysqldump which works fine when copying data from tables to tables, but i need to ...
0
votes
0
answers
132
views
Recover data from .ibd file with corrupted page
I'm seeing "incorrect information in file" errors when opening my INNODB tables.
I followed the general recommendation to extract the CREATE TABLE statements from the .frm files using ...
- 133
0
votes
1
answer
117
views
Mariadb can not find system tables
I am running mariadb 10.3.27 and after a erroneus long query maria db stoped working.
I restart it and check systemctl status.
Mariadb was working but also have the bellow errors:
[ERROR] mariadbd: ...
- 139
0
votes
1
answer
110
views
service mysql reload results 'Access denied', but service mysql start works
Using Debian 10.10.
I have a MySQL problem.
service mysql reload
results 'Access denied'
service mysql start
executes correctly.
All databases are working correctly after starting mysql. Strange, ...
- 155
0
votes
1
answer
37
views
SELECT from table without blocking INSERTs into it?
I have an application where each page load inserts information about the visit into a MariaDB table. There is an analytics hub where statistics from this data can be viewed.
In the beginning, this ...
- 141
0
votes
1
answer
161
views
MariaDB on Ubuntu mysteriously goes away after 15 minutes [closed]
I have an issue I've never seen before...my MariaDB goes away after 1800 seconds, despite having the default configuration for wait_timeout of 28800 seconds.
I checked all my my.cnf files, and there ...
- 581
0
votes
1
answer
390
views
Authentication relying on mysql_native_password seems to also check for Linux user?
I have a mariaDB 10.3 install. The mysql.table states that 'root' login relies on mysql_native_password plugin, with no password set.
If my linux user is root, I am able to connect to mysql using;
...
- 3
0
votes
1
answer
92
views
ALTER TABLE statements not replicating in all tested scenarios - MariaDB
Summary
Tech: mariadb 10.3
Issue: some attempts to ALTER TABLE to change engine is not replicating.
Scenarios tested:
command line PHP script running ALTER statements using PDO - did not replicate
...
- 101
0
votes
1
answer
67
views
unknown variable 'file-key-management-filename'
New installation of Debian 10, with MariaDB 10.3.29. Trying to set up encryption-at-rest, but MariaDB won't start due to an error...
[ERROR] /usr/sbin/mysqld: unknown variable 'file-key-management-...
- 1
0
votes
0
answers
35
views
Performant way to limit selected rows but make sure the sum of the rows is large enough
Here's a simplified version of the problem. You have a marketplace where users can publish how many items they have for sale and the price. You would like to buy 1 000 000 items, and you want to ...
- 53
0
votes
1
answer
45
views
Get the next datetime value
I have a MySQL table:
SELECT
sh.id,
sh.DG_ID,
sh.START_DATE
FROM
STATUS_HISTORIES sh
order by DG_ID, START_DATE
id
DG_ID
START_DATE
22
212
2021-04-20 14:59:58
66
212
2021-04-21 05:...
- 3
0
votes
0
answers
302
views
MariaDB mysql.user table shows as a VIEW. Hence, MySQL is not starting
We have a MySQL installation that has mysql.user table a VIEW. So, MySQL is not starting properly.
We are now running it with skip grant tables. Is there any option to convert the view to BASE TABLE ...
- 1
2
votes
1
answer
59
views
MaridDB 10.3.29 prevents me from deleting a foreign key
This is a minimal example that triggers the error
CREATE TABLE A (
id bigint NOT NULL AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE B (
what tinyint NOT NULL,
a bigint NOT NULL,
CONSTRAINT x ...
- 131
1
vote
2
answers
94
views
Get all rows where specific column value occurs more than once (filter out single occurences)
I am trying to filter all sessionIds that occur once out of an existing result set.
This query is being used in a web application and runs on a big dataset (~ 35 million rows), so I want to prevent ...
- 125
0
votes
2
answers
177
views
Does a NULL DATE use as much storage as a value?
I need to store a DATETIME value that may or may not have a date.
My options are:
| starts DATETIME |
|---------------------|
| 0000-00-00 12:38:06 |
| starts_date DATE | starts_time TIME |
|----...
- 121
0
votes
1
answer
23
views
LEFT and RIGHT JOIN multiple table, show all null data on mariadb 10.3
i want to join 3 table with null value,
here i have 3 table folowing
tbl_reporting tbl_criteria tbl_student
|===============================| |...
0
votes
2
answers
56
views
LEFT JOIN subquery fails in the presence of an EXISTS clause in the main query
I have this query:
SELECT c.id contact_id, engagement.sent
FROM contact c
LEFT JOIN (
/* ➊ Inner query */
SELECT eq.contact_id contact_id, COUNT(*) sent
FROM mailing_job j
INNER JOIN ...
- 407
0
votes
2
answers
158
views
How to find unused columns in MariaDB tables
I have a lot of tables in my DB that have a number of columns which might not be getting used as they slowly lost their relevance with time, I need to find such columns and delete those from the ...
- 69
0
votes
0
answers
46
views
How to use a query to get the definition of (MariaDB 10.3.27) database objects (specifically the variables they use)
I have written a query to get most of the useful information about the routines in a MariaDB database for a database documenter that I am developing.
The code below will return routine name, routine ...
- 199
0
votes
1
answer
129
views
Please help tune MySQL config
Anyone can help me to check this MySQL configuration? I have a VPS 2GB RAM - 2 vcpu, installed cyberpanel from litespeed. Any help would be appreciated!
my.cnf settings are:
[client]
port ...
3
votes
0
answers
172
views
MariaDB - Query to find unused indexes
I need to find unused index on a MariaDB database and do not have 'userstat' enabled, I found this query which is supposed to find unused indexes, but I am unable to get a full understanding of this ...
- 69
0
votes
2
answers
287
views
Remove unused Indexes MariaDB
I have a big database with 50 tables and each table can contain between 100,000 to 100 million records. On these tables are there are 1000s of indexes, but many of the indexes are now not in use.
How ...
- 69
0
votes
1
answer
153
views
MariaDB 10.3.27 Memory usage problem
Greetings!
I tried to optimize MariaDB configuration with my.cnf. Everything was OK, but available memory falling down each hour. I'm using MariaDB with Zabbix server on the same server. CPU: 4 Cores, ...
- 3
1
vote
2
answers
339
views
How to limit impact of a bad application on MariaDB Galera Cluster with MaxScale?
I have a Galera Replication cluster with three MariaDB nodes where a Maxscale Active-Passive cluster in front provides a single node image to tis clients.
I have a bad behaving client, which opens ...
- 111
-1
votes
1
answer
67
views
Can't update database table's column using trigger or from php page but works after using update sql query in phpMyAdmin
After inserting into my audit log table called userUpdateLogand it calls an before insert trigger to insert into userProfiles and users based off the columns insert into their respective table.
but ...
- 1
1
vote
0
answers
179
views
Should a mysql slave answer queries much slower then the master?
I am seeing a slight issue on a mysql slave server, or at least on the surface it appears there might be an issue.
Both mysql's are virtual machines
The master and slave are the same OS (debian10) and ...
- 11
-1
votes
1
answer
59
views
MariaDB CONNECT engine to read external mongodb collections
I want to access MongoDB collections through MariaDB 10.4.17. Both are installed on my local machine running Windows 10.
Software
listening port
MongoDB
27017
MariaDB
15501
I installed the MariaDB ...
- 69
1
vote
1
answer
917
views
How to enable use of SSL in MySQL server?
I have 2 devices on my local network: a remote MySQL server (using MariaDB 10.3) and a laptop (acting as a client with Python). I need to connect to a remote MySQL server via Python using SSL ...
- 125
1
vote
0
answers
263
views
Why setting up new galera cluster with mariabackup as sst starts but all other nodes failed with same error?
I did a fresh reinstallation of mariadb-server on all the nodes
(I removed using sudo apt purge mariadb-*)
I started the first node using sudo galera_new_cluster it went fine and is still running. but ...
- 131
0
votes
0
answers
31
views
MARIADB-10.3.14 VM OOM KILLED AND TUNING [duplicate]
DEDICATED VM FOR MARIADB -
CENTOS 7.7 \\ 16VCPU/250G - JBOD DISKS/NO SSD.
OS PARTITION AND DATABASE ARE ON DIFFERENT FILESYSTEM.
Span of every two weeks, Kernel OOM happens.
Reducing ...
- 1
2
votes
1
answer
1k
views
Very slow query on MariaDB 10.3 compared to MySQL 5.5.50
I already discussed how much slower performance I have experienced on MariaDB 10.3 compared to MySQL 5.5 for some queries in my previously posted thread, but I am now struggling with a specific kind ...
- 143
0
votes
1
answer
23
views
Why does the query plan start with a not-filtered-for table when the filtered-for table's entries exceed a certain threshold?
We process data for our customers. Each customerrequest and is associated with one or more items (less than two on average) and falls into one of <100 categories, foreign keys to which are also ...
- 101
1
vote
0
answers
115
views
How can I fix mariaDB in debian server?
I'm already too tired of trying so many different things and I can't even bypass an error which is running
# mysql -u root -p
when it asks for password, no matter what I type it shows
Welcome to the ...
- 111
0
votes
1
answer
92
views
InnoDB engine getting disabled after decreasing innodb_page_size to 4K
I'm trying to optimize my database (mariadb) for high write (insert & update).
Database is running fine with default innodb_page_size (16K), but when I decrease the value of innodb_page_size=4K.
...
- 1,857
1
vote
0
answers
32
views
why timezone like '+00:00' ignored with select but error with create table?
I am wondering, why does mariadb ignore timezone like '+00:00' with select statement however throw the error with create table statement?
SELECT CAST('2020-07-09 09:55:36.933554+00:00' AS DATE) dt;
# ...
- 111
0
votes
1
answer
2k
views
Extract all objects from json array using MariaDB 10.3
How can I extract every object from this json array using MariaDB 10.3? I think the JSON_TABLE function in MySQL does what I need, but I'm currently constrained to MariaDB.
SET @json_doc='[
{
...
- 133
1
vote
1
answer
271
views
Why is disk IO higher on Debian 10 (MariaDB 10.3) with MySQL replication?
I have a MySQL/MariaDB master-master replication setup that has been working well for several years, the db and tables are not very large (under 200MB for 18 tables). These were on 2 servers running ...
- 61
1
vote
1
answer
3k
views
ERROR 1067 (42000): Invalid default value for 'uploadedDate'
Hi everyone i am getting an error trying to run the below query ERROR 1067 (42000): Invalid default value for 'uploadedDate'
CREATE TABLE `filenew` (
`id` int(11) NOT NULL AUTO_INCREMENT /* ...
- 13
2
votes
1
answer
115
views
Process was intentionally killed - now the process is still showing in "show processlist "
We had a MariaDB long-running process (40 hours), so I killed the thread. But, the thread is still there, with:
showing Command as Killed.
a State of Unlocking tables.
Info is insert into ...
What ...
- 123
0
votes
2
answers
40
views
convert_unixtime output wrong date
Behind the scene: Basically I operate an app that heavily relies on time calculation. We store unixtime in the database according to the user's timezone. Now to perform specific operations we need to ...
- 1
0
votes
1
answer
263
views
MariaDB/MySQL: Import from dump (.sql) file remotely via network
After I set my.ini file following the answer by Rolando here : https://dba.stackexchange.com/a/83385/191579 , today I managed to import about 40GB+ sqldump file in around 2-3 hours only. While on a ...
- 103
0
votes
2
answers
74
views
Using subquery within IF condition [closed]
Hello i am having a weird problem currently i have the following query
SELECT *
FROM `txt_campaign_message` `cm`
LEFT JOIN `txt_campaign` `c` ON c.id = cm.campaign_id
LEFT JOIN `...
