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,121
questions
-1
votes
2answers
18 views
Dynamic table DB design opinions
i need some advice or opinions of "best practices" to clean up the mess i did (quick and dirty) a year ago (to show my idea) wich grew quite quick.
We have around 10 tools (growing by 5 ...
3
votes
2answers
50 views
Why is my database name truncated?
I'm using MariaDB v5.x and want to return a long database's name with the query. I can successfully do this with a simple query, but with a more involved query the database name comes back truncated [...
0
votes
1answer
20 views
MariaDB Maxscale caching not working
I have setup Maxscale (v6.2) and have connected to the Galera Cluster (3 nodes - MariaDB 10.5). I am trying to use the cache filter but it seems to not work. I have enabled general log for all the ...
1
vote
1answer
15 views
MariaDB/MySQL backup replication
I have currently two DB instanced with two-way replication. Each replicates other database. So both are master and slave. So every change in any of these databases is automatically replicated to the ...
0
votes
1answer
22 views
Best practise making users in Maria db database
Iam creating a basic banking database to practise MariaDB, so I wanted to know what the general guidance on what kind of users you should be making and what type of privileges?
Currently I have a read ...
0
votes
0answers
10 views
MariaDB run a new cluster while keeping an old cluster running
I currently have a 5-cluster mariadb galera set up. Two of the servers are in the cloud while three of them are locally hosted.
I kinda want to start the three on premise servers to act as a separate ...
0
votes
0answers
11 views
MariaDB Galera cluster filling disk with gcache.page files
I am running 3 node Galera cluster MariaDB-server-10.3.13-1.el7.centos.x86_64 on CentOS7 and its been 2 years no issue but suddenly recently without any change on node-2/3 disk started filling up and ...
0
votes
1answer
50 views
Pick specific column value in a group of rows based on condition
I am working with mariadb server version 10.6.4.
This is my table definition:
CREATE TABLE `tmp_dba` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`case_id` bigint(20) DEFAULT NULL,
`client_id` ...
0
votes
1answer
26 views
Rookie question for idb file
A rookie question here.
I have read that idb files in mariadb are used for restoring a table.
Does that means that they hold records of deletion too?
I 'd like to know that, if I delete a row from a ...
0
votes
1answer
13 views
Does a read-write-split on your galera cluster still make sense when running the servers on the same (private) cloud environment?
I know about the claims that a Galera cluster should/might perform better in a lot of cases whenever a read-write-split is set up, so there are dedicated read and write hosts in the cluster.
This ...
-1
votes
2answers
23 views
How to increase mariabd disk space
I am getting this error and yes i tried to edit my.ini file its empty the hosting panel is cyberpanel and I cant edit it even with "VIM" and I am facing this error
(/tmp/#sql-temptable-33e-...
0
votes
1answer
29 views
Recover 3-node Galera cluster from crash
this morning we got a power failure and all (physical) servers were abruplty shut down.
I got a problem on my MySQL Galera cluster (3 nodes).
I was able to start 1 node. On other two, MySQL cannot ...
0
votes
1answer
17 views
Extracting multiple similar tables in different schemas
I am attempting to extract tables from multiple schemas in the same database. The tables are similar enough that they are easy to identify and work with using meta data queries like this one:
SELECT ...
0
votes
1answer
24 views
Mariadb replication - Ignore users changes
I posted this question on ServerFault but was told to ask it here.
I need to set up multi-source replication (3 masters and 1 slave). I'd like to avoid replicating users' changes on the slave because ...
0
votes
1answer
18 views
How to backup from MySQL Ubuntu to Synology NAS MariaDB
I am facing difficulties when setting up MariaDB on Synology NAS as slave to replicate my server on Ubuntu. But there are missing commands and files (my.cnf) in the installation. Need help on any ...
0
votes
1answer
38 views
Unknown column in field list MariaDB
I am using PHPMyAdmin and MariaDB and get this weird error as mentioned in the title.
CALL log_total_outstanding(@message);
MySQL said: Documentation
#1054 - Unknown column 'time' in 'field list'
I ...
0
votes
1answer
41 views
Strong vs weak entity types on SQL
I have a 3NF normalized database design and was trying to understand the concept of strong vs weak entity types and my understanding is weak entity types are tables that don't have a primary key. ...
1
vote
1answer
25 views
Why store /var/lib/mysql when you can just create SQL dumps for backup purposes?
Tools like mariabackup offer the possibility to create full and incremental file system backups of /var/lib/mysql but I don't quite understand what additional safety layer this would give me if I ...
0
votes
1answer
20 views
Creating a parent table from child relationships
I have a child relationship table as
CREATE TABLE ItemRelationships
(
Item1 int(11) unsigned NOT NULL,
Item2 int(11) unsigned NOT NULL,
INDEX(Item2),
FOREIGN KEY(Item1) REFERENCES Items(ItemID) ON ...
0
votes
3answers
35 views
Delete ibddata1 file on innodb database
I have recently changed my mariadb database from innodb_file_per,_table =off to innodb_file_per_table = on .
I checked out yesterday and I saw that idbdata 1 file is extreme large 30G .
I believe it ...
0
votes
1answer
17 views
Possible to GROUP BY across different columns in mariadb/mysql
I have a table full of events similar to this:
`date` | start_time | end_time | length
--------------------------------------------
2022-01-10 | 07:00:00 | 16:00:00 | 9
2022-01-11 | 07:00:...
0
votes
2answers
42 views
Understanding thread pool and max connection in mariadb
I need some help in understanding the concept of thread pool and max connection in mariadb.
Says max_connection=500, thread_handling=pool-of-threads, thread_pool_size=50.
Assuming my database has no ...
0
votes
0answers
47 views
MariaDB Galera Cluster galera.cache file getting bigger than specified gcache.size
We have a 3 node Galera Cluster running on Kubernetes, behind 2 HAProxy PODs configured so, all queries are executed on the first POD/node of the cluster if available, and the other 2 nodes, provide ...
1
vote
0answers
54 views
Slave index corruption mariadb 10.5
We have a simple mariadb replication with a master and a bunch of slaves. without any prior alert or warning, randomly in our slaves some indexes ( some of them are not present in master) become ...
0
votes
0answers
36 views
Letting MariaDB infer the data type of the generated column
How to let MariaDB infer the data type of the generated column?
For example:
alter table Customers
add NormalizedName varchar(100)
as (lower(`Name`)) virtual
And
alter table Keywords
add `Length` ...
-1
votes
1answer
64 views
Could my MySQL connection string 'options' bring my site down? [closed]
I moved from MariaDB 10.1 to 10.6 (link to my post initially trying to get my site back) and have been having difficulties keeping my website up ever since. It crashes with ASP C0000005 errors -- link ...
0
votes
0answers
33 views
MariaDB Access denied for user 'root'@'192.168.80.4' (using password: YES) for some databases
I have a MariaDB up and running inside a docker container, and I can connect to it using adminer. I have more than 10 databases in it. I can access all data from adminer.
In my code I'm using EF Core ...
0
votes
1answer
30 views
UPDATE a table by self JOIN and GROUP BY
I have a table of team players in which the PlayerID is missing in some rows.
CREATE TABLE TeamPlayers
(
ID int(11) unsigned NOT NULL AUTO_INCREMENT,
TeamID int(11) unsigned NOT NULL,
PlayerID int(11) ...
0
votes
1answer
22 views
Replication from multiple masters to one slave
I have 2 Raspberry PI's and each run a MariaDB 10.3 server. Each has the same databases:
vennens
scheduler
On the slave I've configured the 50-server.conf file with follow contents:
master_pi1....
0
votes
0answers
42 views
mariabackup trouble between Windows and Linux
I am finally trying to migrate a database from Windows to Linux.
I am running MariaDB 10.4 (I have also tried mariabackup from 10.4.22 too)
I do this:
mariabackup --backup --target-dir=e:\transport --...
-1
votes
0answers
16 views
How can I generate a deleted ibd file from a table
i have encountered the error in which I do not have, in a table that I manage, the idb file of a specific table. Looks like someone's been able to eliminate her.
Is there any way to generate the file ...
0
votes
0answers
37 views
MySQL server control database corrupted after MariaDB migration
I migrated the MySQL in my server from MySQL 5.7 to MariaDB 10.3 and the MySQL database, the one that controls users and passwords corrupted.
It has several tables that in the column "collation&...
0
votes
1answer
22 views
Unable to create WordPress databse user
Wordpress and MariaDB are on different hosts. I'm connected to MariaDB via it's CLI command interface. I am following the DigtalOcean guide.
Here are the DB and OS info:
10.3.32-MariaDB-0ubuntu0.20....
2
votes
1answer
22 views
MariaDB index chosen differs on ARM and x64 based procesors
When I have been using mariaDB on apple M1 chip, I discovered some diference between index chosen in select statement versus x64 server linux system with this query:
explain
SELECT z.id_hlavni AS ...
0
votes
1answer
68 views
How to define advance condition in each group
I have below sample data, and I'm trying to get groups of record with below conditions:
Same prev_read_at
Different current_read_at
Only one record must have payment_id
Number of records in each ...
0
votes
0answers
18 views
Maria db move to other partition
I have a 120GB maria db sever on centos 7.5 with 4 primary partitions.
My free storage is 20G and I want to add more . I can not make another partition because I already have 4 primary partition.
I ...
0
votes
1answer
15 views
Maria db false size
I am using maria db on a centos 7.5 VM with 150G disk.
Df command shows me 30G free. I've 2 large dbs about 20G each.
That is a sum of 40G. I have 3 smaller dbs let's say 20G more, so I have a total ...
0
votes
0answers
21 views
Maria DB not letting me creating more then 1 user at once
I am trying to make a read only and a full privledge admin user to my database with the following code:
DROP USER IF EXISTS 'readonly'@'localhost';
CREATE USER 'readonly'@'localhost' IDENTIFIED BY '...
1
vote
2answers
51 views
Determine Ideal Collation Set for correct data storage
Alright so I have a MariaDB DB and I've just noticed some weird storage issues, like the following:
My JSON columns (which in MariaDB are LONGTEXT) have an automatically set collation of utf8mb4_bin. ...
0
votes
1answer
23 views
Query to find the count of all the tables in a MySQL database
We have been using the following query to find the size (i.e. row count) of all the tables in a database:
SELECT TABLE_NAME, TABLE_ROWS FROM information_schema.TABLES WHERE TABLE_SCHEMA = '...
0
votes
1answer
39 views
How to fix MariaDB commands out of sync, missing expression near ON?
I have the following procedure
DROP PROCEDURE IF EXISTS find_transactions_past_5_days;
DELIMITER $$
CREATE PROCEDURE find_transactions_past_5_days (IN input_date DATE)
BEGIN
...
-1
votes
0answers
27 views
Sum JSON_EXTRACT
is possible one help with sum values and replace other keys? i need make one output
CREATE TABLE tbl_users (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`info` text,
`gup` text
);
...
1
vote
3answers
98 views
Why does MySQL require two or more redo log files for InnoDB?
A single redo log file can also be written in a circular fashion.
In fact, MariaDB has changed its InnoDB redo log to a single file, and its developers claimed that "the log file was ...
2
votes
1answer
35 views
MariaDB error with trigger and end statement
I am getting a MariaDB server version for the right syntax to use near 'END IF; END'
I've tried to remove the semi colons sitting getting the errors, I thought I had done it in the correct syntax.
Can ...
0
votes
1answer
33 views
How to create a trigger that generate a random specific output to insert into a table in MariaDB?
I have an account table which is laid out in 3NF:
CREATE TABLE account (
account_id INT AUTO_INCREMENT,
customer_id INT NOT NULL,
account_type_id VARCHAR(15) NOT NULL,
is_active ...
1
vote
1answer
38 views
Filter out rows which the text on that current row is contained in the text of the following row
I wonder if it's possible, in a SELECT, to filter out rows which the text on that current row is contained in the text of the following row.
For example, image we log "searches" performed by ...
0
votes
0answers
17 views
MySQL MariaDB Average Cost Calculatiton based on FIFO Method (First In - First Out)
I have the following table of transactions - print screen simplified (positive Qty is a buy, while negative Qty is a sell):
Based on the above data, I would like to calculate average buying price for ...
0
votes
1answer
14 views
How do I set up one server to replicate X schemas from X different server, without resorting to mysqldump?
In other words; I'm trying to figure out how to restore multiple mariabackup dumps to one server (different databases). I assume I'm missing something obvious in the documentation, but I've been ...
0
votes
1answer
15 views
MariaDB Understanding "Event" Interval
I've created my first EVENT:
ALTER EVENT flush_logs_event
ON SCHEDULE EVERY 6 HOUR
COMMENT 'Rotate /var/log/mysql/master-bin*'
DO FLUSH LOGS;
I changed it from "12" to "6" ...
0
votes
2answers
29 views
data structure for grouping children without parents
I have a list of name
ID Name
1 John Doe
2 J. Doe
3 John Doe
4 John Doe
With different methods, I check which names are the same person by checking their actions. For example, in ...