Questions tagged [mariadb]

Open source RDBMS designed to be a "drop-in" replacement for MySQL. Add a version-specific tag like mariadb-10.2 if that context is important.

Filter by
Sorted by
Tagged with
0
votes
1answer
11 views

SQL 2 Columns of Different Types in Same Table - Working With 'Uknown' Amount

I'm very new to SQL and I've been searching for an answer for a while now, can't seem to find it. I have 1 table of CustOrders which has 3 columns, Name (varchar), Number_Orders (int) and order_date. ...
0
votes
0answers
10 views

How to manage partitions in MariaDB with InnoDB tables?

I'd like to set up partitioning for the "time series" use case as in https://mariadb.com/kb/en/partition-maintenance/. However, I'd also like to control data directories. And I want the "max" or "...
0
votes
0answers
12 views

after running Galera Replication for MariaDB , performance (TPS) does huge diffrence [duplicate]

I have two VM machines (with exact hardware-software configuration on two ESXI Servers in same rack and same subnet too) that is running with nginx + PHP-FPM + and MariaDB 10.4. in each ESXI server I ...
0
votes
1answer
34 views

Approaching database design

I have 1000 sensors, and I need to hoard the value of each sensor at every second, for a month. This is just theory, the most realistically achievable unit of time will be determined when I start ...
0
votes
1answer
27 views

Table contains 4 indexes inside InnoDB, which is different from the number of indexes 3 defined in MySQL

The database is an old version of MariaDB, the equivalent of MySQL 5.5. The table contains those columns id auth old_auth device_id_fk This error is showing Table contains 4 indexes inside InnoDB, ...
0
votes
2answers
43 views

Database Design for Pokémon Review

I am trying to learn more about Relational Databases. So, I did a pokemon database gathering the stats available on the Smogon website. I have done some research thus far and would like to get some ...
1
vote
3answers
50 views

MySQL Optimization on my server

I have an active server running around 80-100 active websites. I would like to optimize the server and possibly reduce the amount of RAM and optimize my MySQL settings. I have 30 GB of RAM assigned ...
0
votes
0answers
19 views

Delete from table one row with 2 variable with limit

I'm developing a PHP script who store session of users in a Database. When an user logout from server, remove only one row ( because same user can login more than one time ) When server reboot, ...
0
votes
1answer
21 views

Exporting mysql query result to .txt file

I want to export query result into .txt file The path of .txt file and query is dynamic DELIMITER // create procedure test(filepath text , _query text) BEGIN SET @sql = CONCAT(_query, ' into ...
1
vote
4answers
49 views

Unique over 2 columns

I have 2 columns col1 and col2 INITIAL QUESTION: is it possible to make a constraint to not be able to add the same email two times in this columns, only using indexes? The Question has been edited ...
0
votes
1answer
21 views

MariaDB Can't Create New User

Fresh install of MariDB 10.4 (fedora 31). I login into mysql as root: MariaDB [mysql]> select User, Host,Password from user; +-------+-----------+-------------------------------------------+ | ...
-1
votes
1answer
19 views

-bash: PURGE: command not found

I want to delete binary log files following are my binary log files binary2.000001 binary2.000002 i want to delete binary2.000002 mysql> PURGE BINARY LOGS TO 'binary2.000002'; It shows me:...
0
votes
0answers
12 views

1 Galera node freezes, rejoin is ok but not working

We have a 5-node Galera cluster in a high-load application. One of the nodes freezes in production. Still the cluster-size=5 and Galera/WSREP writes to this node, but the node is not working. Then we ...
0
votes
1answer
20 views

How to restart MariaDB Galera cluster to change my.cnf

I have 3 dbs servers with mariadb galera cluster. I need few my.cnf change but each time I try to just restart with mysql restart command , replication gets affected. So can anybody let me know ...
1
vote
2answers
39 views

Mariadb Delete with Join query stuck sending data

I am using Mariadb (10.4.10-MariaDB-log) with Galera Cluster (3 Nodes). I am running a large data queries with joins , but No idea why delete with join is not working. DROP TEMPORARY TABLE IF ...
1
vote
1answer
57 views

How to migrate Oracle Database to MariaDB? [closed]

Currently we have a server on Oracle version 18. We are planning to migrate to MariaDB. Is there any free service provided by MariaDB or oracle that helps migrate a database from Oracle to MariaDB?
0
votes
1answer
55 views

Wrong results on queries with join for some MariaDB versions

I found that on some versions of MariaDB server some simple queries return wrong results for certain data. I have 2 tables: CREATE TABLE `tab_parent` ( `id` bigint(12) unsigned NOT NULL ...
1
vote
1answer
31 views

Rollback is not working if the storedproce call another storedproc

Is it intended behavior that if I call a stored procedure inside a stored procedure will automatically commit? example: pseudo procedure proc1 create procedure proc1(in var1 int, out var2 int) ...
0
votes
3answers
62 views

Grant MonitorAdmin / PROCESS privilege to user

I need to assign the monitoradmin privilege to a user because this is what an application called "dynatrace" needs to monitor the server (some details in a post on their site). According to https://...
1
vote
1answer
25 views

MariaDB handling composite unique indexes that are foreign keys at the same time [closed]

I have a possible issue with how MariaDB (and probably MySQL) handles composite unique indexes that are also foreign keys. Not sure if that is by design and if it is, why does it work like that. I ...
0
votes
0answers
18 views

SET PASSWORD vs ALTER USER

On a fresh MariaDB install (10.4.12) if I do mariadb-admin password ... or SET PASSWORD = PASSWORD('...'); for a root user, that doesn't force me to use password to authenticate. But when I do: ...
1
vote
1answer
24 views

Setup innodb_io_capacity

I want to configure the innodb_io_capacity option for my MariaDB server. In the documentation I found the following: Faster hard drives, RAID configurations, and solid state drives (SSDs) benefit ...
0
votes
1answer
55 views

INSERT ON DUPLICATE KEY UPDATE to UPDATE multiple rows in single query

I've come across people recommending using ON DUPLICATE KEY UPDATE to UPDATE multiple rows using a single query. Unfortunately no one seems to be using clean SQL (defining an obvious primary key of id ...
3
votes
2answers
92 views

Dropping a CHECK in mariadb?

I have a schema migration plan to add json column to mariadb 10.2. But I am required to have a reverse plan. Here's the forward plan: ALTER TABLE `mydb`.`table1` ADD COLUMN jsonf JSON DEFAULT NULL; ...
0
votes
0answers
21 views

Update mysql Database - am I doing it right?

I have about 15 tables that are linked together in my database (mariadb - parent-child with FK). If I now update something with VBA (over ADODB, mysql-connector), I update first with my code the ...
0
votes
1answer
24 views

ALTER TABLE WITH VALUES FROM SELECT

I have a table1 with fluctuating number of columns. (colname1,colname2,colname3...colnamen) I want to : ALTER TABLE table2 CHANGE COLUMN `columns` `columns` ENUM('colname1','colname2','colname3' .......
0
votes
1answer
24 views

ROUTINE TO SELECT FOM A TABLE AND UPDATE ANOTHER TABLE [closed]

I do not understand how to update a table from another table after a table has been modified. I have a table with 3 columns like id(INT), distname(VARCHAR)UNIQUE and score(INT), and I have another ...
0
votes
1answer
19 views

galera cluster with mariadb 10.3.13 is not replicating on RHEL 8.1

I'm installing a galera cluster using mariadb 10.3.13 with two nodes in redhat 8.1 server. I enabled ports 3306, 4567, 4568 y 4444 ports. selinux is disabled. firewall disabled. The first node is ...
0
votes
1answer
55 views

MariaDB Optimization with Mysql Tuner

I've read so many articles regarding MariaDB optimization for low memory servers. There are a lot of guides about that but people are always sharing their own configuration. That makes it difficult to ...
0
votes
1answer
27 views

How to store file paths, which collation to use?

I have a table that stores Windows system paths. I am scratching my head about how to store them and in particular which collation to use. The paths are unicode strings. They are case insensitive, ...
0
votes
1answer
52 views

MySQL 8.016 master with MariaDB 10.2 slave on AWS RDS, Character set '#255' is not a compiled character set

I have a MySQL 8.016 master and a MariaDB 10.2 slave, both running on AWS RDS. This was working perfectly until today, when I figured I wanted to do chain replication instead, like this MySQL 8.016 ->...
0
votes
1answer
15 views

Status show SSL is in use even though I did not set up certificate on the client side?

I want to set up SSL connections to a distant MariaDB server. I followed the instructions here. I generated the certificate for the server, restarted it, and got this: +---------------------+---------...
0
votes
1answer
38 views

How can I improve this slow query in my wordpress site?

SELECT object_id, term_taxonomy_id FROM wp_term_relationships INNER JOIN wp_posts ON object_id = ID WHERE term_taxonomy_id IN (525627,516360,525519,535782,517555,525186,517572,549564,1,517754,541497,...
0
votes
1answer
27 views

How to dedicate server to MySQL for max performance?

I am using MariaDB 10.5, I see this in MySQLTuner report Dedicate this server to your database for highest performance. How do I do this? My server is on Centos 8.
1
vote
0answers
22 views

MariaDB REGEXP_REPLACE Invalid utf8 byte sequence

I'm working with a fairly old (15 years) old database. It started as a MySQL3, was upgraded several times, the frontends had problems with proper encoding, the whole database was converted to utf8mb4 (...
0
votes
1answer
40 views

Is it okay to use MySQL Workbench with MariaDB? [closed]

Workbench 8 gives me a warning when connecting to MariaDB, but it appears to work fine. Since MariaDB is based on MySQL, would it be a problem to keep using it?
0
votes
1answer
19 views

Is there an option to turn existing global variant to constant in MySQL or MariaDB?

I'm trying to prevent changing some global variables which I set from the ini file. For example: server_audit_logging=ON I want to prevent it from changing by: SET GLOBAL server_audit_logging = ...
0
votes
1answer
46 views

Mariadb is not starting

Please help me with this issue MariaDB not starting. Its a production server so not able to reinstall and clean the data at all. (This command executed by me) systemctl -l status mariadb.service ...
0
votes
1answer
61 views

How DISCARD the tablespace on mariadb while table is not found?

I run the mysql_upgrade against my mariadb 10.3 and get the following error - see here for details. ERROR 1813 (HY000) at line 85: Tablespace for table 'mysql.transaction_registry' exists. Please ...
1
vote
0answers
137 views

InnoDB: Error: Table “mysql”.“mysql.transaction_registry” not found after upgrade to mariadb 10.3

I've upgraded the mariadb to 10.3 packages on my Debian 10, however something went wrong and I ended in the same issue which is mentioned several times Cannot open table mysql/innodb_index_stats [...
2
votes
1answer
30 views

Lost connection to MySQL server at 'handshake after truncating table

I have been using Heidi SQL's latest version through an SSH tunnel to truncate a 2.5GB table. When I clicked truncate MSQL lost the connection immediately and it's not starting up ever since then, no ...
1
vote
1answer
42 views

Can/Should rarely used columns be placed in a different table for performance?

I have a table that contains data (blobs). Data is often read, rarely inserted, and never modified. For all of this data, I compute hashes that I also store in the DB. These hashes are only checked ...
0
votes
0answers
19 views

Master slave replication - DATETIME columns not replicating the time

I have master which is mariadb 5.5 and slave mariadb 10.3, the replication works fine except for the DATETIME columns which are syncing the date part but not the time. On master: LastLogin 2020-01-15 ...
0
votes
2answers
37 views

Why is a SELECT grant needed for an UPDATE statement on a table? (Error 1143)

myuser@'%' has UPDATE and INSERT grants on table1. I tried to run this UPDATE: UPDATE table1 SET column1 = 'blahblah' WHERE id = 123; I got this error: SELECT command denied to user 'myuser'@'...
-2
votes
1answer
97 views

What is the optimal configuration for MariaDB ? (CentOS 8, MariaDB 10.5)

Hello i have MariaDB 10.5 on a Centos 8 Server configuration 18cores 32 threads and 32gb ram I have plenty of free ram but I am trying to optimize for CPU less CPU work and more ram usage How I do ...
0
votes
0answers
34 views

Restore database with .idb .frm and logfiles

I recently had an error with my SD card inside my RaspberryPi4. (I'm very inexperienced with databases as well.) So this means that I have to restore my database. What I did was copying all the files ...
0
votes
1answer
31 views

trigger to update row number

I have a MariaDB table id | key | year | .... | position 1 | key1 | 2004 | ... | 3 2 | key1 | 2011 | ... | 2 3 | key1 | 2017 | ... | 1 4 | key2 | 1994 | ... | 2 5 | key2 | ...
0
votes
0answers
15 views

Proper way to preserve data for mariadb docker container

I am running a single mariadb container as a backend for a webapp. I restarted the container, and the only data remaining was from a long time ago. None of the recent transactions were available. They ...
0
votes
1answer
50 views

I have to delete over 90 million records, do I remove or maintain the transaction's SQLWARNING?

SQL Server: mysql Ver 15.1 Distrib 5.5.65-MariaDB, for Linux (x86_64) using readline 5.1 OS: Slackware 14.1 That's 90 million old radius bank records I want to remove, leaving only those this month....
1
vote
0answers
79 views

How can I reduce the memory usage of a tiny MEMORY table in MySQL?

After running the following queries: CREATE TABLE mem_test (i int NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=MEMORY; INSERT INTO mem_test () VALUES (); SELECT table_rows,avg_row_length,data_length,...

1 2 3 4 5 30