Questions tagged [mariadb]

Open source RDBMS that forked from MySQL. Add a version-specific tag like mariadb-10.5 if that context is important.

Filter by
Sorted by
Tagged with
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 ...
user avatar
  • 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 ...
user avatar
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?...
user avatar
  • 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 ...
user avatar
-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',...
user avatar
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 ...
user avatar
  • 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) ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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, ...
user avatar
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 ...
user avatar
  • 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), ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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(...
user avatar
  • 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://...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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-...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 `...
user avatar
-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 ...
user avatar
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 ...
user avatar
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)...
user avatar
  • 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) ...
user avatar
  • 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) / ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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?
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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?
user avatar
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` ...
user avatar
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, ...
user avatar
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 ...
user avatar
  • 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://...
user avatar
  • 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....
user avatar
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 ...
user avatar
  • 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) ...
user avatar
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 ...
user avatar
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", ...
user avatar
  • 43

1
2 3 4 5
46