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,469
questions
1
vote
1
answer
24
views
How to detect a trigger set up on database INFORMATION_SCHEMA?
Some time ago I developed a utility Python app which does certain actions if it finds that any data in a database has been updated recently. To do this, for each database I created a table ...
- 111
1
vote
1
answer
18
views
MySQL upgrade 5.7 to 8.0 with Keyring encrytption failed to complete upgrade
I ran the mysql community installer on Red Hat 7.9 over a 5.7.36 installation.
First, I ran xtrabackup for all dbs
Then I stopped mysqld
Then I ran yum install
Everything ended ok
I got the below ...
- 476
0
votes
0
answers
6
views
NetSuite drivers aren't working with MariaDB on Ubuntu 22.04: can't open lib 'opt/netsuite/odbcclient/lib64/ivoa27.so'
I'm trying to use the CONNECT engine in MariaDB to access NetSuite. I have the ODBC drivers installed and working with everything except MariaDB. Every time I try, I get this error:
ERROR 1296 (HY000):...
- 1
0
votes
0
answers
16
views
MariaDB: Syntax error in SET variable statement
I have a MariaDB transaction in which for the life of me I cannot see the syntax error:
START TRANSACTION;
SET @config_name='test';
SET @config_version = (SELECT IFNULL(max(`Version`)+1,0) FROM `DEV`.`...
- 3
1
vote
1
answer
19
views
Can't get MariaDB docker to work without password
I want to add MariaDB to my docker compose file, like this:
version: '3'
services:
mariadb:
image: 'mariadb:10.3'
ports:
- "3306:3306"
volumes:
...
- 123
3
votes
1
answer
59
views
Does simple key value storage on MySQL/MariaDB requires index on key?
I was exchanging with another developer regarding the performance of SELECT queries on a simple key/value table.
CREATE TABLE cache (
id BIGINT unsigned not null auto_increment,
...
- 133
0
votes
1
answer
28
views
This query gives error. right syntax to use near 'on vehicles.school_id = schools.id left join drivers on drivers.school_id = ...' at line 8
select drc_users.name,
vehicles.total_vehicles,
COUNT(distinct drivers.id) as total_drivers
from drc_users
LEFT join brc_users on drc_users.id = brc_users.district_id
left join crc_users on crc_users....
- 113
0
votes
0
answers
38
views
RAM recommedation for Mysql DB server
We are dealing with large dataset and currently database size is 400 GB. Lately logs are showing error "Out of memory" and we are trying to fix the same.
Largest table size exists in ...
- 101
0
votes
0
answers
6
views
MariaDB replication works via MASTER_LOG_FILE, almost instantly fails using MASTER_USE_GTID
We have a weird issue with MariaDB version 10.4.28+maria~ubu2004 (the official binary release for Ubuntu 20.04 LTS).
We dumped the master data using mariabackup (we have a single database), and ...
0
votes
0
answers
26
views
MariaDB: Using composite primary key and index on the same columns gives strange results
Environment information:
Windows 11
10.6.8-MariaDB
In SO https://stackoverflow.com/questions/20055644/mysql-counter-within-group
I asked how can I add a counter within a group. It works quite well ...
- 257
1
vote
1
answer
31
views
Mysql/Mariadb: can you format a date+time to show the time zone at a given location?
I want to get the time zone from a date+time plus location. Some cities in the world exist in different time zones, depending on the time of the year.
For example:
2022-01-01 00:00:00 in Europe/...
- 111
0
votes
0
answers
23
views
What cons should I consider if I put index on every UUID column in my database
I'm creating a system that is not entirely microservices, but it's getting close to microservices and each module has its own database.
And based on what I have seen in many online services (Azure, ...
- 1,327
0
votes
1
answer
34
views
Optimize MariaDB 10.6
I am not a database guru.
But here's the matter with which I'd like some help.
I have a relatively freshly-minted server running AlmaLinux 8.7, with 64GB RAM and a pair of terabyte SSDs.
After ...
0
votes
1
answer
31
views
MariaDB: defective MyISAM and ARIA Indexes after errorless import of big data file
I have a long suffering with this issue after moving forward from MySQL 5.6 and then MariaDB 10.0.
Working now on Debian 11 (Bullseye) with MariaDB "Ver 15.1 Distrib 10.9.5-MariaDB, for debian-...
- 101
0
votes
0
answers
12
views
MySQL Server fails to start after removal of MariaDB in MySQL Workbench
A couple of years ago I was following a tutorial that had me install MariaDB.
Now, I am trying to use MySQL Workbench to manage a MySQL database. I initially tried to add a user and got an error ...
- 13
0
votes
1
answer
37
views
Pulling the postcode area from the full postcode
If I have a Postcode EH1 2GB I am looking for a function which will pull only the EH
Ideally this would work for postcodes which may only contain 1 leading letter such as G2 1AB and 2 numbers after ...
- 139
1
vote
1
answer
35
views
Avoid writing on a slave MariaDB server
I have a replication in place with a Master server and a slave server.
From time to time I connect to the slave server mostly to get info or generate statistics on my user's usage.
But I'm afraid that ...
- 113
2
votes
2
answers
218
views
Data retention in Mariadb
I want to retain data for 90 days only for all 15 tables in my Mariadb database.
Current db size is 60 GB.
What are the options available for performing the same.
0
votes
1
answer
35
views
Bringing up galera cluster
Because of a mismanagement I have a 3 node cluster down. Before doing the due diligence disregarding seqno and recovery processes, the node that used to be the primary was brought up doing ...
0
votes
1
answer
26
views
Why is there no performance_schema.threads?
I am trying to diagnose an issue where a phpBB message board hangs (becomes unresponsive) for about five minutes about once every hour. (It is not exactly on the hour every hour, but it is a little ...
- 13
-1
votes
1
answer
65
views
Why MariaDB server is consuming SWAP when there is available memory?
I have a dedicated server for MariaDB 10.8.3 and the features as follows:
248GB free disk space.
innodb_buffer_pool_size = 12286766080
binlog_cache_size=8M
Memory results:
[root@server user]# free -h
...
- 119
1
vote
1
answer
84
views
mysqldump: Error: Index for table is corrupt; try to repair it when dumping table - does this mean repair was done?
I ran into the same database trouble that another user described here and I managed to get mysqld started by setting innodb_force_recovery = 6, which allowed me to perform a backup using mysqldump -u&...
- 13
0
votes
0
answers
19
views
Mariadb / Mysql "Authentication switch request" slows down PHP connections to mariadb server
We switched our on premises MariaDB server to the Azure MariaDB managed service.
It runs on MariaDB 10.3.23 in which we cannot tune all server values.
While stress testing our PHP API application, we ...
- 1
1
vote
0
answers
36
views
Create PIVOT table with MariaDB's CONNECT engine
Given the following base table, which implements a sort of Entity-Attribute-Value table
CREATE OR REPLACE TABLE dipendenti_detail (
id_dipendente int(10) unsigned DEFAULT 0,
id_azienda int(10) ...
- 149
1
vote
1
answer
63
views
Why is MariaDB innodb_flush_method = O_DIRECT so much slower than fsync for SELECT count(*) as count? [closed]
I have a MariaDB 10.5.18 server with 64 GB of RAM running on an enterprise grade SSD.
I'm using a fairly generic my.cnf to try to figure out why innodb_flush_method O_DIRECT is so much slower than ...
- 11
0
votes
1
answer
17
views
How do I add 2 missing nodes to MariaDB Galera Cluster using IST and not SST
I inherited an issue where a MariaDB Galera Cluster has only one out of 3 nodes working.
Node1: up and running in production and is accepting reads and writes
Node2: fresh install it just need to sync ...
0
votes
2
answers
20
views
Calculating a percentage based on Group
In order to get a percentage, you divide X by the Total and multiply by 100..
I am trying to translate this into a query and group by type to get a percentage of each type.
I have tried different ...
- 43
-1
votes
1
answer
27
views
SQL to show character encoding of a database/installation in MariaDB
With only very litte MariaDB experience I need to inspect charater encoding settings on a large scale of MariaDB installations. I want to do this via Ansible and believe it would be easiest to issue ...
- 153
0
votes
1
answer
25
views
Can I Use Having Keyword With 2 Columns?
I have 2 tables for keeping stocks of several products. 1 table for current stock of a product per warehouse and the other table is to create low stock warnings.
My stocks table is like this;
id | ...
0
votes
2
answers
92
views
SQL Query to filter from an Array
I wasnt sure how to title this question.
I have a database of suppliers, a simplified version is shown below.
Company
Vehicles
X
["Van", "Luton", "Artic"]
Y
["Van&...
- 43
1
vote
1
answer
36
views
Metastore(Mysql) bottleneck for Hive
We have a hive installation that has MariaDB as metastore database. MariaDB has around ~250 GB metadata with ~100GB indexes. It becomes terribly slow during the peak load of 40-60K QPS.
Looking from ...
- 111
0
votes
1
answer
24
views
Custom Function as paratemer in MariaDB query
I have the following code in Maria DB ,
I want to use my function in a query to create a sequence that starts with the count(*) +1 of a my TABLE1
It gives me an error in the CREATE SEQUENCE query :
...
- 101
0
votes
0
answers
42
views
Missing information_schema_stats_expiry in MariaDB server system variables
Using the Server version: 10.3.38-MariaDB - MariaDB Server.
Running the command
SET SESSION information_schema_stats_expiry=0;
always results in this error:
#1193 - Unknown system variable '...
- 1
0
votes
0
answers
27
views
Backup Maria DB data that is already down in docker but the data is persistent
MariaDB (running in Docker), whose storage is mounted on a drive of the host machine is currently down. I can navigate to the storage in Linux and can see the data. How do I back it up as the usual ...
- 1
0
votes
1
answer
66
views
Do prepared statements give a performance advantage when running multiple times with similar parameters?
I've used prepared statements (MySQL & PHP) for many years, but only with an eye to being safe against SQL injection. I've never re-used a prepared statement, I've always built them up every ...
- 225
0
votes
1
answer
50
views
'lc_time_names' can't be set to the value of 'de_DE'
Works:
SET lc_time_names='en_US';
Fails with "Variable 'lc_time_names' can't be set to the value of 'de_DE'":
SET lc_time_names='de_DE';
This seemed simple to me. I guess, I have to ...
- 105
1
vote
0
answers
34
views
How this performance graph may be interpreted?
My configuration is:
Compute
AWS ECS cluster with a Django based API (Using ORM). 1vCPU and 4GB of memory per container.
Database
RDS MariaDB db.t3.medium instance (2 vCPU, 4GB of memory)
gp3 type ...
- 111
0
votes
2
answers
115
views
MariaDB - Mysterious Delay at Each Query
I have 2 tables on MariaDB (10.5.16-MariaDB-log) where I query table A, and then loop over the roughly 4000 results and for each one do a query on table B. (This is inherited code and this will be ...
- 11
0
votes
1
answer
26
views
MariaDB constraint is incorrectly formed although columns are of the same type
I've got a problem while creating (altering) foreign key.
I have two tables in my DB (created via flyway migrations):
connector (migration)
create table if not exists connector
(
id char(36) not ...
- 103
0
votes
1
answer
26
views
Remote MYSQL Performance
please note that I'm working on a problem for 2 weeks and I didnt find its solution. I had a running website on Joomla and the Joomla + MYSQL are installed on the same server.
So, I separated the app ...
- 1
3
votes
1
answer
57
views
using ON UPDATE for non-timestamp columns
Timestamp columns may have DEFAULT NOW() ON UPDATE NOW() which is awesome to keep track of when the row was updated.
I want to have similar functionality with user id, such that when an update ...
- 31
1
vote
2
answers
30
views
Combining Queries into 1 Query
I have 3 queries...
Query 1 Gives the Opening Balance at the Start of the Month
SELECT AccountBalance As 'OpenBalance', MONTHNAME(DateTime) as 'Month', MIN(DateTime) as 'Open'
FROM Table1
GROUP BY ...
- 43
0
votes
1
answer
69
views
MariaDB privileges for "SHOW DATABASES" vs "SELECT * FROM information_schema"
I have a user with an access to several databases.
In MariaDB 10.6.11 I cannot use the following command to list all accessible databases:
SHOW DATABASES;
/* SQL Error (1227): Access denied; you need (...
- 23
0
votes
2
answers
31
views
Selecting last value to be entered each month
I am looking to pull a closing balance from the database for each month.
I have tried
SELECT CloseBal As 'Balance', MONTHNAME(DateTime) as 'Month', DateTime
FROM Table1
WHERE MAX(DateTime)
Group By ...
- 43
1
vote
1
answer
30
views
Combining Multiple queries from the same table inrto 1 query
I am looking to find a way to achieve the below...
I have tried using Joins and SubQueries but no joy as yet...
I have a table that keeps track of wins/loses/draws as so
Date
WLD
01/01/23
Win
05/01/...
- 43
0
votes
2
answers
34
views
Allowing negative values up to a specific value
I have a situation where we store the value of credits a user has in the database:
`credits` DOUBLE(22,2) UNSIGNED NOT NULL DEFAULT '0.00',
The credits are numbers going from 1 to any positive value. ...
- 190
1
vote
1
answer
34
views
Is there any configuration not to use index merge in mariadb, if a cardinality of an index is below than that?
There is a table have 2 indexes, called state and CATEGORY, which has a very low cardinality respectively 4 and 24 within 7,110,590 rows in the table.
When running query of select statements that ...
- 111
0
votes
0
answers
45
views
What can produce slow down of mariadb
Our Mariadb database (1To) encounter slow down during some minutes but we don't find the root cause : there no CPU or IO overhead but locks OS dans lot of process taking time on sending data and ...
- 103
0
votes
0
answers
304
views
ERROR 1558 (HY000): Column count of mysql.proc is wrong. How do I solve this?
I am on linux mint using XAMPP 8.2.0 (linux version), that has MariaDB 10.4.27. Ran into a problem. I type the following MySQL code:
DELIMITER $$
CREATE PROCEDURE employee_info(IN p_em_id INT)
BEGIN
...
- 1
0
votes
0
answers
28
views
MariaDb OPTIMIZE TABLE progress jumps from 99% to 0%
I have a very large table that I am normalizing, so a whole range of url varchar(1024) fields have been replaced by url_id bigint(20) that lookup the url in another table. Now I've dropped the url ...
- 101