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
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://...
user avatar
  • 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....
user avatar
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 ...
user avatar
  • 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) ...
user avatar
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 ...
user avatar
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", ...
user avatar
  • 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 ...
user avatar
  • 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 "...
user avatar
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'@'%' ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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; +-------+------------+----------+--...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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` ...
user avatar
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((@@...
user avatar
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 ...
user avatar
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 | ...
user avatar
  • 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 ...
user avatar
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)...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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') ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
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
user avatar
  • 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 ...
user avatar
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(...
user avatar
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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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:- ...
user avatar
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....
user avatar
  • 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 ...
user avatar
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 ...
user avatar
  • 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 ...
user avatar
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?
user avatar
  • 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 ...
user avatar
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 ...
user avatar

1
2 3 4 5
45