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.
1,490
questions
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,...
