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,280
questions
0
votes
1
answer
17
views
Can I put MariaDB's tmpdir inside it's datadir on Centos 6?
We have separate disks on our Centos 6 machine
A smaller one for our / and system folders,
A larger one for /home/mainuser
Currently MariaDB has the settings
datadir=/home/mainuser/mysql
tmpdir=/tmp
...
- 123
0
votes
1
answer
40
views
MariaDB UUID datatype not orders correctly
I'm using MariaDB 10.8.3. I have a table with a PK of type UUID. I'm inserting ordered UUIDs generated in application side. When I retrive the data of the table ordering by UUID column, it is not ...
1
vote
0
answers
22
views
Resume MySQL / MariaDB replication from master logs
Master server crashed and slave was running behind. I can not boot the master to resume the replication and retrieve the data lost. Is there a way to resume the replication directly from the log files?...
- 111
1
vote
2
answers
37
views
How to Update and Manage Millions of Databases
We are looking to shard our application, so that each customer has their own database. The data contents of all databases are different, but the structure is the same for all customers.
Imagine there ...
- 111
-2
votes
1
answer
25
views
error 1292, Truncated incorrect DOUBLE value: ''
im try to insert data to table order but allows im getting error
[22007][1292] Truncated incorrect DOUBLE value: ''
INSERT INTO `order`
SET `order`.customerId = '9e1c8915-c60c-407e-8d34-9fefdffd5ebd',...
0
votes
1
answer
39
views
Approximately how many reads/writes per secons can MySql/MariaDb tolerate?
I'm running a python script that reads some historical data and inserts it into MariaDb. There's a lot of data and I consider splitting it into parts and running mulitple instances of a script.
But ...
- 27
0
votes
0
answers
38
views
How to flatten/pivot "inner join" into a single row in a certain order?
Let's say I have 2 tables in MySql/MariaDb: competisions and participants. There can be multiple participants in one competisions.
I want to
a) do an inner join
b) sort the participants by score
c) ...
- 27
1
vote
3
answers
37
views
Is having a boolean column to store whether there are values for a multivalued attribute a bad micro-optimization?
This question is more theoretical. Let's say there is an entity in a one-to-many relationship. For example, a blog post can have zero or more tags. Those tags are stored in another table (that ...
- 23
1
vote
1
answer
18
views
How to change the engline type from InnoDb to MyISAM of an existing db?
I have a MariaDb database which consists of 2 tables and which is around 10Gb of the size. One is dependendant on the other via a foreign key.
How can I change the engine type of that particular db ...
- 27
0
votes
1
answer
30
views
MariaDB memory usage much higher than innodb_buffer_pool_size
I have a backend server with 1G RAM for my HTTP server and my MariaDB.
I noticed the database keeps getting killed by OOM once or twice a day. Most of the time the OOM is triggered by the HTTP server, ...
0
votes
1
answer
35
views
How to make a total for each query and put it into one table
I have 2 queries to collect data but how to get the total like below format:
select b.number as ticket_id,
d.name as reported_by,
b.created as reported_date,
f.topic as ...
- 9
1
vote
3
answers
55
views
Forcing InnoDB to build the secondary index
When INSERTing, InnoDB defers the building of secondary indexes. For example,
CREATE TABLE item_creators
(
item_id int(11) unsigned NOT NULL,
creator_id int(11) unsigned NOT NULL,
INDEX(creator_id),
...
- 4,261
0
votes
0
answers
14
views
speeding up JOIN by LIKE or REGEXP
I have two tables of
CREATE TABLE titles
(
title_id int(11) unsigned NOT NULL AUTO_INCREMENT,
title varchar(255),
INDEX(title),
PRIMARY KEY(title_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE ...
- 4,261
0
votes
1
answer
16
views
faster INSERT INTO InnoDB table with composite PRIMARY KEY
I have a simple tag_map
CREATE TABLE tag_map
(
article_id int(11) unsigned NOT NULL,
tag_id int(11) unsigned NOT NULL,
INDEX(tag_id),
PRIMARY KEY(article_id,tag_id)
) ENGINE=InnoDB
The table is quite ...
- 4,261
0
votes
1
answer
10
views
Inserting a "date-time" into TIMESTAMP column -- "Incorrect datetime value"
I have a date-time data that comes in this format:
2016-02-10T12:00:00.000Z
I've created a MariaDb database and a column for that date of type TIMESTAMP
However, when inserting the data into the ...
- 27
0
votes
1
answer
26
views
Is it possible to select all columns but one column using SQL syntax?
Consider these tables:
Brands (Id, Name, Logo, Ratings)
Products (Id, Title, Picture, Price)
Is it possible to use star selection to select ALL but ID fields from the Brands table in a join?
create ...
- 1,287
0
votes
0
answers
21
views
Mariadb equivalent to MySQL CONNECTION_CONTROL
MySQL introduced a security plugin library in version 5.7.17 including the plugin CONNECTION_CONTROL, which checks incoming connection attempts and adds a delay to server responses as a result of ...
0
votes
1
answer
75
views
Problem with corrupted MariaDB database file
When the MariaDB database on my Docker container keeps restarting itself, I figured out that the problem is in my database (I think it's corrupted).
root@website:~# docker-compose ps
Name ...
1
vote
0
answers
19
views
Finding sub-groups by relationships within groups
In this example, two tables define the groups and the relationship of persons within each group
CREATE TABLE groups
(
group_id int(11) unsigned NOT NULL,
person_id int(11) unsigned NOT NULL,
INDEX(...
- 4,261
0
votes
0
answers
19
views
My sql does not start on Linux
I have a problem that mysql doesn't start anymore although nothing has been changed.
The following error message appears when starting mysql.
This is the Error-log:
To report this bug, see https://...
0
votes
1
answer
70
views
MariaDB 10.5 slower than mysql 5.5
Since i moved from mysql 5.5 to mariadb 10.5 queries much slower even with many different tuning config and new server is ssd but old mysql was faster did anyone has such experience ?
even with simple ...
0
votes
2
answers
63
views
What is the bottleneck in SELECT from InnoDB tables?
For an InnoDB table with 500 million rows (on a separate NVMe drive), SELECT COUNT(*) takes about 3 min.
SHOW ENGINE INNODB STATUS\G shows ROW OPERATIONS of about 2M reads/s, which is consistent with ...
- 4,261
1
vote
0
answers
46
views
MariaDB Server crashes when performing mysqlcheck
I'm checking my databases and I'm using this command :
mysqlcheck --all-databases -c -uroot -pxxxxxxx
But I'm getting this error :
mysqlcheck: Got error: 2013: Lost connection to MySQL server ...
1
vote
1
answer
20
views
Unknown charset: utf8mb4
Context: I send queries to my MariaDB server(Ver 15.1 Distrib 10.3.34-MariaDB) through a Python script and mysql.connector module.
(I don't know which one it's among those 3 in my list:
mysql-...
- 43
0
votes
0
answers
29
views
Optimized MariaDB configuration on my Server to prevent lock wait timeouts
I'm having issues recently with my galera cluster getting desync after multiple lock wait timeouts, is there any way I can improve my galera cluster configuration to prevent more lock wait timeouts ...
- 3
0
votes
0
answers
16
views
MariadDB: Hashicorp Vault Encryption Plugin fails to initiate when InnoDB Table Encryption turned on
Have integrated MariaDB 10.6.8-4 with Hashicorp Vault using the hashicorp_key_management.so plugin. With this plugin enabled, things work fine until with innodb_encrypt_log turned on. Also, altering ...
0
votes
1
answer
17
views
Date missing from timestamp in mysql log
Currently, my log entries look like:
time
message
03:53:08 PM
ERROR
05:40:32 PM
ERROR
...
...
I want the date included, like this:
time
message
06/30/2022 03:53:08 PM
ERROR
06/30/2022 05:40:32 ...
- 11
0
votes
1
answer
22
views
MariaDB Procedure run comand create trigger
Is it possible to run command to create table and trigger from a procedure?
1295 - This command is not supported in the prepared statement protocol yet
CREATE DEFINER=`username`@`%` PROCEDURE `...
- 39
-1
votes
0
answers
25
views
MariaDB convert a row to a json object
I need to transform a row into a json object dynamically.
in a trigger I need to get the NEW and OLD and transform it into a json object.
however the "json_object" function is necessary to ...
- 39
0
votes
1
answer
19
views
How come mariabackup fails to restore in ubuntu and achieves it on fedora? [closed]
I am unable to find the solution alone, so I decided to ask a question after two or three days of exeperimentation and research. I had a mysql (MariaDB) install on a server under Ubuntu 20.04 with a ...
0
votes
1
answer
16
views
wrong index in composite PRIMARY KEY in MyISAM
I use MyISAM for processing tables, as it is much faster to read large data from MyISAM as compared with InnoDB for JOINs. I create map tables for bulk INSERT,
CREATE TABLE tag_map
(
article_id int(11)...
- 4,261
0
votes
1
answer
19
views
Throw no default value error even if we give value
A user of one of my product is using MySQL 10.7.3-MariaDB.
This is the table creation:
CREATE TABLE IF NOT EXISTS negativity_accounts
(
id CHAR(36) NOT NULL PRIMARY KEY,
reports VARCHAR(1024) ...
- 105
0
votes
1
answer
20
views
MariaDB OPTIMIZE TABLE “grew” my table (as seen by the DB)
DB is MariaDB 10.3 from the “official” Docker image storing the DB in a bind mount on ext4.
MariaDB [foo]> SELECT now(), TABLE_SCHEMA as DB, TABLE_NAME AS tbl, ROUND((DATA_LENGTH + INDEX_LENGTH) / ...
- 101
0
votes
1
answer
24
views
Find records with join matching all join IDs
Suppose to have the following database setup:
Users (
id int primary key,
name varchar(255)
)
OrgUnits (
id int primary key,
name varchar(255)
)
Users_OrgUnits (
user_id int,
orgunit_id ...
- 137
0
votes
0
answers
35
views
How to check a value is UUID in MariaDB?
Since MariaDB does not have a UUID data type, based on search I realized that people usually define a column with char(36) as its data type and they can also define a default using uuid() function for ...
- 1,287
0
votes
1
answer
11
views
mysqldump won't accept combination of --socket, --all-databses, --user, --password
Linux, 2 mariadbs each with their own .sock.
Dump as root works fine:
[root@host]$ mysqldump --socket=/tmp/mysqlsecond.sock --all-databases > $sqlfile
Dump as the non-root backup user requires ...
0
votes
0
answers
16
views
move specific MariaDB database to custom folder?
How can I move a specific MariaDB database to a custom folder? I'm not talking about changing the datadir or moving all databases, just a specific one?
I have myawesomedb on a MariaDB 10.6 server on ...
- 101
0
votes
1
answer
31
views
Best column type for huge numeric data
What's the best data type for storing numeric values
(like 1656659676159881372) in MariaDB?
- 103
0
votes
1
answer
38
views
I need to improve my model
I create a smaller app that has to be used for money transactions where it focuses on favor and expense from bank accounts (later I will expand other tables for admin, workers costs, credit ...
- 1
0
votes
1
answer
33
views
How do I tell MariaDB to differ between characters with and without accents in primary key?
We have a MySQL table with utf8mb4_0900_ai_ci and want it to merge to MariaDB for performance reasons (and because in our new infrastructure we only use MariaDB). Unfortunately, MariaDB does not ...
- 101
0
votes
2
answers
25
views
MariaDB: can a SELECT query block other queries?
If I used a stored procedure having only Select queries in mariadb (NO Update OR Insert). Does this Select locks other queries (Select, Update or Insert) using the same tables?
- 11
3
votes
1
answer
183
views
MariaDB Query Optimizer ignores index
MariaDB Version 10.5.15
simple Table, two columns with an index
If I make a query on a table and use two columns in the where conndition that have an index, no index is used. Why?
CREATE TABLE `logs` ...
- 133
0
votes
1
answer
33
views
Delete all data of all tables of all user databases in MariaDB
In our development environment, we want to have a DeleteData.sql query that developers can use to delete all of the data of all tables of all databases of their MariaDB docker instance.
For example, ...
- 1,287
1
vote
2
answers
38
views
How to tell if a particular MariaDB / MySQL server supports the Barracuda storage format?
In creating some DDL-generating code for MySQL / MariaDB (inside WordPress as it happens) I need to find out whether the RDBMS server I'm talking to can support the newer Barracuda / DYNAMIC / InnoDB ...
- 499
0
votes
2
answers
23
views
Optimizing querying for any of multiple terms in string
I have been pondering what would be the most efficient way to search for any of multiple urls contained in an unstructured text field.
Suppose I want to find posts containing either https://...
- 117
3
votes
2
answers
409
views
MariaDB 10.6 Performace
I'm migrating my database more to my surprise I had a drop in the performance search, I already checked all the indexes of the tables and they are the same
Server New
MariaDB 10.6.8
CentOS 7
8 VCpu 2....
- 39
0
votes
1
answer
14
views
MariaDB: How to install a second instance using MSI-Installer
I would like to install a second instance of MariaDB with MSI-Installer (Windows 10). The first instance is on drive C. When I want to install the second instance on drive D using MSI-Installer the ...
- 257
0
votes
0
answers
35
views
Can I use a LEFT JOIN on a grouped result?
I have the following two queries, which on their own work as expected:
(SELECT
max(ID_box_connections) AS bc1_ID_box_connections,
ID_boxes_from
FROM box_connections
GROUP BY ID_boxes_from)
...
0
votes
1
answer
99
views
docker mariadb ignores to read my.cnf files from mounted volume
This problem is annoying me very much, it might be something stupidly simple to a good system admin but i've wasted many hours without success, i've tried many answer from SO and other websites and ...
- 101
2
votes
2
answers
180
views
MySQL What charset/collation for Case insensitivity and Accent sensitivity?
I am looking for a charset/collation that would make it so when I do a
SELECT * FROM table_name WHERE username = "Warrior"
It only returns me the rows where username = "Warrior", ...
- 43