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,243
questions
0
votes
2
answers
16
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
381
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....
- 41
0
votes
1
answer
11
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
1
answer
33
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
0
answers
18
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
1
vote
2
answers
65
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", ...
- 13
0
votes
1
answer
19
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 ...
- 147
0
votes
1
answer
22
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 "...
- 111
0
votes
0
answers
24
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
71
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
22
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
44
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 ...
user5372615
0
votes
1
answer
30
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
42
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;
+-------+------------+----------+--...
- 113
0
votes
3
answers
69
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
27
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
11
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
0
votes
0
answers
11
views
InnoDB Deadlock when transaction waiting for lock already acquired
Transaction (2) holds the following lock:
RECORD LOCKS space id 11404 page no 1144152 n bits 72 index PRIMARY of table [tableName] /* Partition [tableName]_p59 */ trx id 28648068046 lock_mode X Record ...
0
votes
1
answer
37
views
#1064 - SQL syntax Error while trying to import
DROP TABLE IF EXISTS `migration_versions`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `migration_versions` (
`version` ...
- 103
0
votes
1
answer
25
views
MariaDB is using more memory than the buffer allows - 4x more
Help me out here brain trust, why is my MariaDB 10.2.27 using 4x more resident memory over the buffer?
MariaDB [(none) 10.2.27]>SELECT MAX_USED_MEMORY_GB, INNODB_BUFFER FROM
(SELECT ROUND((@@...
- 1
0
votes
1
answer
20
views
Covert from literal HEX value to INT MariaDB
I have a situation where i need to get the integer value from a literal hex.
Example: "ff" equals 255 in decimal, but if i use the HEX("ff") function it returns 6666.
Is it ...
2
votes
1
answer
38
views
Previous Row based on double where condition and group max sum
I have such a table and I would like to get these values:
+───────────────────+────────+──────────+──────────────────────────────────────────────────────+
| TS | Value | ValueA | ...
- 23
-1
votes
0
answers
23
views
MariaDB Delete query with JOIN and RETURNING gives syntax error
The query
DELETE mvmaec FROM malfunction_version_machine_assignment_error_codes mvmaec
INNER JOIN malfunction_version_machine_assignments mvma on mvmaec.assignment_id = mvma.assignment_id
WHERE
...
- 99
2
votes
1
answer
33
views
MYSQL - Migration challenge: same name schemas
We have 7 Linux servers (Redhat) and every linux server has a Mysql DB with 1 database (PROD_1) in it. (Every project has own server)
We want to migrate them all to one database server (redhat/mariadb)...
- 155
7
votes
4
answers
778
views
MariaDb - How to ensure a (LEFT) JOIN only if a single right side record exists
I have two tables I need to join together. However, I only want to join records when there are unqiue matches rather than picking one of several for the join.
Versioning:
Using MariaDB version 10.3.34
...
- 173
-1
votes
0
answers
31
views
How to run dynamic query in MariaDB inside try catch?
In SQL Server, we can run this query:
declare @dynamicQuery nvarchar(max)
set @dynamicQuery = '
select [name]
from sys.databases
'
begin try
execute sp_executesql @dynamicQuery
end try
...
- 1,275
3
votes
1
answer
63
views
Storing column for data that doesn't exist for majority of rows
I'm using mariadb.
I have a single table that keeps track of video_count and image_count for a few different sites. I.e.
type | enum('SITE1','SITE2','SITE3')
...
- 211
0
votes
0
answers
27
views
Why is restoring a fresh database from a backup failing due to a duplicate key error?
I'm trying to build a replica from an active database with no downtime for replication purposes.
I run the following statement to get a backup, avoid locking the tables, and flush the logs so that the ...
- 167
0
votes
0
answers
14
views
INSERT .. ON DUPLICATE KEY UPDATE and INSERT IGNORE transaction issue
I have a reproduction where INSERTing a row doesn't actually seem to insert them, if you follow a transaction.
Reproduction using docker (I've reproduced on 10.5 and 10.6):
docker run -d -e ...
0
votes
0
answers
64
views
Slow update query in mysql with left join
I ran into an performance issue when I use left join in an update query. When I pass one argument to IN my query is slow, but when I pass several arguments my query is much faster.
UPDATE deliverables
...
- 101
0
votes
2
answers
49
views
How can I recover the database?
I have a database that is around 5.8 GB in size. The system I was using originally was Ubuntu 14, and the database MySQL 5.5 with MyISAM, with Apache 2 and PHP 5. It had incredible number of locks on ...
2
votes
0
answers
23
views
Slow create/truncate tables
I have a problem with the performance of my MariaDB server in osx arm64 (M1):
I installed MySQL server via Homebrew, after a while I reinstalled it on MariaDB 10.7.3 via Homebrew.
after that, the ...
- 21
1
vote
1
answer
18
views
Auto assign value-message to rows in a table with MySQL trigger
Im testing a trigger for an assignment where i need to set an specific value to each row on a table.
I have 2 tables tbl_cursos and tbl_modalidad. After i insert an entry on tbl_cursos the trigger ...
- 13
0
votes
0
answers
15
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
0
answers
23
views
Generate MySQL/MariaDB SSL certificates from my wildcard certificate (*.mycompany.com)
I'm configuring a series of MariaDB servers with SSL protection. I was able to do it creating different self-signed certificates, as everybody can do just following some of the multiple tutorials you ...
- 111
0
votes
1
answer
21
views
How to convert a string year to a DATE
STR_TO_DATE doesn't work like I expect.
STR_TO_DATE('2007', '%Y')
STR_TO_DATE('2007', '%y')
2006-11-30
2019-11-30
10.6.7-MariaDB-1:10.6.7+maria~bullseye-log
- 1
2
votes
2
answers
70
views
Database becoming unresponsive after time (4 to 24 hours)
Since a couple of days my mariadb database randomly becomes unresponsive after random intervals (which have been between 4 and 20 hours).
The database comes back to life when rebooting the ct.
And ...
- 41
1
vote
0
answers
23
views
Why on the VPS, uuid() is wrapped in a single quote?
On my local machine, running show create table Projects returns this:
CREATE TABLE `Projects` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`Guid` char(36) NOT NULL DEFAULT uuid(),
`Name` varchar(...
- 1,275
3
votes
1
answer
66
views
Is an SSL Certificate Authority Chain "Secret"
We've recently signed up to use MariaDB's SkySQL package, however are new to the use of SSL Certificate Chains when connecting (having only really used SSH tunnels in the past).
We have the key which ...
- 33
1
vote
1
answer
51
views
Inconsistent results with MySQL, query never ends on MariaDB
We have some really strange behaviour on MySQL (8.0.29) that we can't explain.
We have 2 tables + 1 link table in-between, illustrated by this schema :
We run a query whereby we need to fetch the id ...
- 107
1
vote
1
answer
52
views
Junction table or denormalize?
I'm using MariaDB.
I'm designing a simple application that can send notifications to subscribers for when certain websites they've subscribed to have been updated.
A user can be subscribed to several ...
- 211
2
votes
1
answer
31
views
Change a value (just a flag 0/1) depending on an entry in another table and - important - leave everything else unchanged
I'm trying to replace a taxonomy entry with a flag. The fields are kept in different tables. Here's what I did so far on db<>fiddle:
CREATE TABLE `job_postmeta` (
`meta_id` bigint(20) UNSIGNED ...
- 21
0
votes
1
answer
111
views
Mysql service down with [ERROR] InnoDB: Page log sequence number is in the future
I have a Centos 7.9 server with MariaDB version 10.2.43. All of a sudden on May 2 2022, I have started receiving mysql down alerts. On checking I could see below log information in logs.
Error 2:- ...
- 130
0
votes
1
answer
12
views
Create a event inside a procedure
Using Mariadb 10.5.13 is it possible to create a event inside a procedure ?
The reason is because I would like to have a procedure I can manually trigger, and then run a one time event 3-4 hours later....
- 188
1
vote
0
answers
41
views
Which MariaDB-Galera Node Should I Start After Cluster Crushing or Failing?
Sometimes my 3-Node GaleraCluster fails and doesn't serve. When I check I see all of them are activated but two of them are not working, so I check log files I see one of them could not operate an ...
0
votes
2
answers
50
views
mariadb: innodb_file_per_table=On and still have a huge ibdata1
I have a mariadb server for TEST on a Linux OS.
I set innodb_file_per_table=1 in config file and restarted the database. I checked in Database and it was activated. I checked the ibdata1 file and it ...
- 155
0
votes
0
answers
27
views
How do I connect to mysql database with password
Using PHP 7.4.27 (now using 7.4.29) with MariaDB in xampp
I can access everything using phpmyadmin. I am using the following on my localhost
$con = new mysqli($host, $user, $pwd, $db);
I can connect ...
- 445
1
vote
1
answer
30
views
Create new MySql user with same grants as existing user
To create a new user usr_B with same grants as an existing user usr_A we can always SHOW GRANTS for usr_A and then GRANT those privileges to usr_B, but is there a built-in command for this purpose?
- 245
0
votes
0
answers
24
views
Joining table to MariaDB OQGRAPH table returns no rows
I am having an issue with the OQGRAPH storage engine in MariaDB. When I select from it, specifying a hard-coded destid and latch = 'dijkstras', it returns the expected data; however, when I try to use ...
0
votes
2
answers
33
views
Limiting and queueing the database connections
In mariaDB, is it possible to do something like this?
limit the number of connections (like in mysql, as explained in this SO answer)
set up a queue for all the other connection attempts, and avoid ...
- 125