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
40 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
57 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,281
0 votes
0 answers
23 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,281
0 votes
1 answer
24 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,281
0 votes
1 answer
54 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
30 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 ...
0 votes
0 answers
31 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 ...
1 vote
1 answer
387 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
20 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,281
0 votes
0 answers
45 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
0 answers
128 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
69 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,281
1 vote
0 answers
75 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
63 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
45 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
26 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
28 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
33 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 `...
0 votes
1 answer
68 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
19 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,281
0 votes
1 answer
25 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) ...
  • 145
0 votes
1 answer
37 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) / ...
0 votes
1 answer
26 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 ...
0 votes
0 answers
69 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 ...
0 votes
1 answer
19 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
23 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 ...
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
39 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 ...
0 votes
1 answer
43 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
38 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?
3 votes
1 answer
226 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` ...
0 votes
1 answer
61 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 vote
2 answers
59 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
28 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
507 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....
0 votes
1 answer
15 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
346 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 ...
2 votes
2 answers
321 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
0 votes
1 answer
67 views

Best approach for basic PHP DB session data storage

I've decided to switch from PHP's native session management based on $_SESSION to use my own, due to many different issues I've had so far with PHP's possibilities, and also because I'm building an ...
  • 149
0 votes
1 answer
40 views

Mysql DB viewer user (only for views) should access to information_schema and test?

I've created a user in our mysql/mariaDB engine so our customers can read some views. User has been created as follows and can query the views with no problem. CREATE USER IF NOT EXISTS "...
0 votes
0 answers
31 views

can't select view tables after make a role on mariadb

I made a role with the name app_reader and gave it to the specific user, below the command: CREATE ROLE app_reader; GRANT SELECT, SHOW VIEW ON dbname.* TO 'app_reader'; CREATE USER 'testuser'@'%' ...
  • 9
3 votes
1 answer
133 views

Converting large database with many tables from latin1 to utf8mb4

So this is not a question about "how do I convert a table from latin1 to utf8?" I totally know that and get it. The question I'm trying to ask is, "How can I make this conversion have ...
  • 133
1 vote
0 answers
36 views

Slow Query Log Only Logs Few Queries

Slow query is enabled but only logs few queries when you retrieve the slow log. It’s been working ok all last week, suddenly this week, it only logs just 2 or 3 queries. Long query time = 0.01 and I ...
  • 11
0 votes
0 answers
87 views

Replicate MySQL table to MariaDB for System Versioning

We have some tables in an operational and busy MySQL database (AWS RDS) for which we require system versioning for some of the tables. (point-in-time queries) Is it possible to somehow replicate the ...
user avatar
0 votes
1 answer
32 views

Procedure with params

How to write the correct WHERE clause in PROCEDURE which depends on the parameters come in arrays and their default value is NULL my attempt DELIMITER // CREATE PROCEDURE `task`(IN param1 TEXT, IN ...
  • 1
0 votes
2 answers
45 views

Why does MariaDB create another key for this column when it is already part of the primary key?

create table foo ( id int primary key ); create table bar ( a int references foo(id), b int references foo(id), primary key (a, b) ); show index from bar; +-------+------------+----------+--...
0 votes
3 answers
219 views

Extract a substring where the delimiter might appear more than once

I have a column of strings with this pattern <email> - <id>. Email is always the first string. I would like to extract only the email address but the problem here is that an email address ...
  • 149
0 votes
1 answer
89 views

How to monitor slow queries time on Mariadb

I have multiple MariaDB instances that I monitor with mysql exporter on grafana. I have spotted some slow queries recently which took more than 300 seconds by looking at the process list of the ...
  • 1
0 votes
0 answers
17 views

Restrict access from IP when using Galera Cluster shared for pre-production and production webapplication servers

At the moment we use a MariaDB + Galera Cluster for a production and pre-production environment. All DB users on the pre-production and on the production application Servers have a wild card set for ...
  • 101

1
2
3 4 5
47