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,798
questions
0
votes
1answer
9 views
Get rows above and below (neighbouring rows) a certain row, based on two criteria SQL
Say I have a table like so:
+---+-------+------+---------------------+
|id | level |score | timestamp |
+---+-------+------+---------------------+
| 4 | 1 | 70 | 2021-01-14 21:50:38 |
|...
0
votes
1answer
18 views
Connecting to “foreign” database on the same DBMS
I am somewhat surprised to see that JDBC allows me to transparently SELECT from a table in a "foreign" database, on the same DBMS, for which I have the requisite privileges, without ...
0
votes
0answers
8 views
Zabbix MariaDB Copy item data to another item
My current DB:
MariaDB [zabbix]> SELECT VERSION();
+----------------------+
| VERSION() |
+----------------------+
| 10.0.28-MariaDB-2+b1 |
+----------------------+
There are some ...
0
votes
1answer
15 views
Consistent InnoDB dumps with mysqldump
The MySQL documentation recommends that when dumping an InnoDB with mysqldump to use --single-transaction to get a transactionally consistent snapshot of the database with minimal blocking of other ...
-1
votes
1answer
17 views
Context of MySQL wildcards
I am trying to get a better understanding of the context in which MySQL interprets wildcards, especially the _ underscore wildcard, which matches exactly one character. For example, it is a common ...
0
votes
1answer
10 views
MariaDB default utf8
The installation puts character-set-server=utf8 in my.ini by default. Is it better to use utf8mb4 instead of clean utf8 and if Yes, why utf8mb4 is not set by default?
1
vote
0answers
27 views
Difference between setting of a shared lock when using INSERT or UPDATE/DELETE statements
I would like to know why a shared lock is set when I use a foreign key on an INSERT statement, but not when I use a foreign key on an UPDATE or DELETE statement.
Specifications:
version: 10.1.47-...
0
votes
3answers
27 views
Server with mariadb sometimes dies under high load
I have a dedicated server i7-4770 with 32GB RAM. On server installed CentOs 7.8, MariaDB 10.5.8, PHP 7.4.14, Nginx 16.1. All tables in the database are InnoDB.
When the load on the database increases ...
1
vote
1answer
26 views
mariadb: Aborted connection .. Got timeout reading communication packets
What is the typical cause of warnings such as this? They appear periodically, sometimes multiple times per day then not for a day or so.
2021-01-08 13:20:46 203939 [Warning] Aborted connection 203939 ...
0
votes
1answer
16 views
Get monthly sums from several tables with same (date,amount) columns
I have records stored in "date,amount" columns from several tables: table1, table2, table3, table4. Amongst tables, field names are same.
Table 1:
date
amount
2018-02-17
10.00
2018-06-25
...
1
vote
0answers
14 views
MariaDB views: I want to replace repeated multi-table joins in my queries with a view - are there any issues to watch out for?
Rather than
SELECT a.pk, b.pk, c.pk, d.name
FROM a
JOIN b on b.pk = a.fk
JOIN c on c.pk = b.fk
JOIN d on d.pk = c.fk
I can do
SELECT a_pk, b_pk, c_pk, d_name
FROM view_a_b_c_d
I have a lot of this ...
0
votes
1answer
21 views
Warning: #1292 Incorrect datetime error when using DATE_ADD()
Doing this works fine:
SELECT * FROM table WHERE foo < '2021-01-09 00:00:00'
But this one:
SELECT * FROM table WHERE DATE_ADD('foo', INTERVAL 5 DAY) < '2021-01-09 00:00:00'
results in
Warning: #...
0
votes
0answers
21 views
MySQL Group Replication versus MariaDB Galera for replication [closed]
I have a laravel based website and as the website grows
I need to have multiple servers to share traffic. I'm
currently using MariaDB on a single server.
So there are multiple tools to create database ...
0
votes
1answer
30 views
Using multiple GROUP BYs to get fewer records, not more
I feel like this is a simple thing to do, but I've been playing around with trying to achieve the desired result and the only way I'm coming up is with 2 nested subqueries, which doesn't seem great ...
0
votes
1answer
17 views
Difference between host '*' and host '%'
When I run the following command:
SELECT user, host, password, plugin FROM mysql.user WHERE user='user_123';
this is what I get:
+----------+---------------------------------------+-------------------...
0
votes
1answer
16 views
Slow inserts in mariadb columnstore
I have just started on researching the feasibility of using MariaDB's columnstore for OLAP, and I find inserts are very slow. This is MariaDB 10.5 on a debian 10 system, just an elderly desktop with ...
0
votes
0answers
14 views
Make slave has same “general_log” table like master server
Apologize for my English. I'm very new to database administration. I've been trying to set up MariaDB replication server by following this document.
The only data that I need to replicate is in a ...
0
votes
1answer
24 views
Specified key was too long creating primary key
Simple question: what is wrong here?
Using utf8 (CHARSET=utf8mb4) makes the internal fields larger.
What is the maximum field size for UTF8 keys?
We need to guess?
create table repos(repo varchar (200)...
1
vote
0answers
15 views
Automatically and robustly restarting a Galera cluster
Galera clusters, somewhat famously, have a requirement that nodes need to be shut down and brought back up again in a certain order.
There are a few questions here and on other sites about recovering ...
1
vote
1answer
19 views
Missing Edit Privileges Option In PhpMyAdmin [closed]
The Action column of the PhpMyAdmin is not displaying the Edit Privileges. I did not get the solution for this problem. I even tried to upgrade and degrade the PhpMyAdmin version, but nothing worked. ...
0
votes
0answers
21 views
Galera Manager web gui for mariadb
I want to create a MariaDB 10.4 multi-master cluster on four Ubuntu 20.04 nodes. As you know Galera manager provides a web GUI interface for the cluster and it's handy for monitoring and add or remove ...
0
votes
1answer
60 views
ERROR 1064 (42000): You have an error in your SQL syntax - Cannot understand what the error means
I'm trying to import an SQL db, exported from another place. I have the SQL create script. I am not sure what might be going wrong...
My mariadb version is
select version();
+----------------+
| ...
0
votes
0answers
44 views
How to enable use of SSL in MySQL server?
I have 2 devices on my local network: a remote MySQL server (using MariaDB 10.3) and a laptop (acting as a client with Python). I need to connect to a remote MySQL server via Python using SSL ...
2
votes
0answers
19 views
mysqlimport vs Loading the SQL Format Dump
Isn't mysqlimport rumoured to be much faster than loading the SQL format dump?
We have a database with nearly 60 million entries on the Windows platform, running MariaDB 10.1.21. We created the tab ...
1
vote
1answer
17 views
MariaDB InnoDB does not rollback on timeout
I have the ff. sample queries in this sequence:
START TRANSACTION
UPDATE
INSERT
COMMIT
Query #2 is timing out but I noticed that query #3 is not rolled back.
I am also trying to manually rollback by ...
-2
votes
0answers
15 views
How can I insert multiple data from table to one column in another table?
from this
to this
thank you for your answer, it's kinda hard to understand my teacher at the university
0
votes
0answers
7 views
how can I recover windows xampp data in ubuntu xampp by file transfer
My windows got crashed but i was able to retrieve the xampp folder in which i want to recover my data in ubuntu xampp.
I have tried to solve this problem by copying the following files from the ...
2
votes
0answers
25 views
MariaDB root password change using Ansible
I had tried to change MariaDB root password using following Ansible script.
- name: Dump MySQL root Password
debug:
msg: "MySQL new Password : {{ mysql_pass }}"
...
0
votes
1answer
19 views
What's causing [ERROR] Invalid (old?) table or database name 'd:q'?
I've two entries in my MariaDB 10.5 error log file last night:
[ERROR] Invalid (old?) table or database name 'd:q'
I've looked at my nginx access log around that time and can't see any unusual ...
1
vote
0answers
54 views
MariaDB 10.3.27 - 100% Temporary tables created on disk despite/because of InnoDB?
I am using Mariadb 10.3 with InnoDB with a fairly high load. For some reason, I have 100% of my temporary tables created on disk, despite using Innodb and a large tmp_table_size (2G) (see Mysqltuner....
0
votes
2answers
20 views
MySQL #1054 - Unknown column 'masterdata_hyung_event.eventId' in 'on clause' on deep join tables
So, I have this query
SELECT * FROM hyung
LEFT JOIN masterdata_hyung_description ON hyung.descriptionId = masterdata_hyung_description.hyungDescriptionId
CROSS JOIN masterdata_hyung_description ON ...
1
vote
0answers
11 views
Deploying MariaDB Columnstore Docker image in Azure
I deployed the MariaDB Columnstore Docker container in Windows with the following command:
docker container run --name cont_mariadb -e MYSQL_ROOT_PASSWORD=xxxxxxx
-p 3306:3306 -v ...
0
votes
2answers
17 views
Deleting table entries based on criteria in another table
We want to be able to delete entries from a MySQL table, based on deletion criteria set in another table. Let me explain with an example.
I have two tables defined as follows:
CREATE TABLE base_tbl
(
...
-1
votes
1answer
22 views
Cannot connect to MariaDB Columnstore database on Docker
I'm running MariaDB 10.5 Columnstore on Docker as described here. This works fine when I connect to the database starting a command line in the container itself.
But when I try to connect with an SQL ...
1
vote
0answers
27 views
What is the default my.cnf on debian's mariadb server?
I'm trying to set default-time-zone on mariadb server
Server version: 10.3.25-MariaDB-0+deb10u1 Debian 10
To do such things, in mysql, I used to simply modify /etc/mysql/my.cnf but here I have a ...
-1
votes
1answer
18 views
Implement MariaDB/ColumnStore on Docker with volumes
I'm trying to implement MariaDB ColumnStore on Docker, as explained here.
But I only see two options to store the data, either locally in the container or in S3. What I need is to have an external/...
0
votes
0answers
47 views
MariaDB failed to start
I have to fix an issue on a VM for a website that use Mariadb technology. When I type "systemctl start mariadb", it shows :
Job for mariadb.service failed because the control process exited ...
0
votes
1answer
25 views
Connection aborts is happening in MariaDB 10.2 but not in 10.1 even with log_warnings level set to 2
I am noticing different behavior in mariadb 10.1 & 10.2 reg.connection aborts.
I noticed connection getting aborted after 7 minutes(wait_timeout) of any query execution. There are few random ...
0
votes
1answer
18 views
MariaDB RAM usage does not reflect configuration
I've got 256GB RAM. InnoDB pool buffer size is 128GB, and InnoDB data + indexes in all databases are about 105GB. Most of that data is frequently queried so, correct me if I am wrong, it should reside ...
0
votes
0answers
31 views
After installing MariaDB, mysql_secure_installation produces error, cannot connect mysqld.sock
After installing MariaDB, mysql_secure_installation produces the error:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
A review of /var/run/...
0
votes
1answer
16 views
How do I efficiently emulate JSON_TABLE in Mariadb?
I don't have MySql readily available, but from what I understand, the best way to achieve what I want in MySql is to make use of JSON_TABLE. Also, from what I understand, JSON_TABLE is being very ...
0
votes
1answer
53 views
Error: MariaDB server is down randomly [duplicate]
Hope someone can provide some ideas...
I have 4 Wordpress websites running on the server. 2 are live sites and 2 are staging sites. Since last week I updated one of Wordpress site to the latest ...
1
vote
2answers
67 views
Best Way to Optimize MySQL / MariaDB for 250M Row Table on a 64GB 16 core Machine [closed]
I have an extremely large dataset (250M Rows in one table, 170G on disk) on a very powerful desktop computer that I'm utilizing to clean-up the data and do analysis on it.
As part of this analysis:
I ...
0
votes
1answer
26 views
mariadb: replication master and slave have equal MySQL server ids
I'm a novice mariadb user trying to set up a master with multiple slaves using mariadb-10.4.17 on fedora33.
I believe at one point I inadvertently ran the "change master" command on the ...
0
votes
0answers
10 views
mariadb automate replication resync using windows task scheduler
Built Master - to multi-slave for different locations(servers)replication setup.
Need batch file script to be run under windows task scheduler to automate replication status.
if Replication breaks ...
1
vote
2answers
67 views
MariaDB 10.5.8 memory leak (MyISAM) on master but not slave
We've recently migrated a legacy application from MySQL 5.5 on Amazon Linux to MariaDB 10.5.8 (latest, from the official repos) on Amazon Linux 2. The "hardware" went from an m3.large ...
0
votes
0answers
9 views
connection getting lost to mysql
if I update 10 posts in a website simultaneously and connection gets lost after one or two post updates, does this has anything to do with connect_timeout value?
currently it is set to 10.
After the ...
0
votes
1answer
17 views
Retaining earliest instances of duplicate entries from a table
We have a situation where duplicate entries have crept into our table with more than 60 million entries (duplicate here implies that all fields, except the AUTO_INCREMENT index field have the same ...
0
votes
1answer
20 views
How to use FIRST_VALUE window function in MySQL/MariaDB to get one row per group?
I'm pleased to see window functions landed in MariaDB 10.2. I thought they would be good for first-of-group problems, but I'm struggling to see how they're efficient. I have something like this:
...
0
votes
0answers
20 views
mysqldump won't dump tables or data
I'm trying to back up a mediawiki database prior to moving the instance to a new server, but I'm getting stuck on mysqldump, which has never given me trouble like this before. I'm clearly doing ...
