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,362
questions
0
votes
1answer
15 views
Why is a connection via an SSH tunnel presenting as 127.0.0.1 and not localhost?
I have defined a MariaDB user test@localhost and I have skip-name-resolve ON as recommended for performance reasons.
I'm able to connect with -hlocalhost -P3306 and so is my web application using the ...
0
votes
0answers
4 views
MariaDB Master / Slave failover / failback automation
Good day all, I'm hoping someone might be able to point me in the right direction.
I'm working on a new dual data center project, and I'm trying to come up with a plan for failover (for patching and ...
0
votes
0answers
17 views
Migrate Mariadb 10.3 to Mysql 8
What is failsafe way to migrate ?
Both database are on CentOS 7 different servers and latest versions of database.
0
votes
2answers
21 views
Slave error after master upgrade
I have two MySQL servers in replication (master -> slave).
I updated them with apt-get upgrade. It runs well on both except the I get some replication errors on slave now.
Last_Errno: 1005 ...
1
vote
2answers
43 views
Multiple versions of mysql / mariadb
I am running 10.3.18-MariaDB-log MariaDB Server but for one application it require I run mysql 5.7, so I am installed that to run under another port which works fine.
However for the software that ...
0
votes
0answers
28 views
mysql to mariadb: unknown collation utf8mb4_0900_ai_ci
I have a mysql 8.0 that I exported using mysqldump. I am trying to import it onto a Mariadb 10.4 database with phpmyadmin, both are the most current versions. Each time I do it though, I get:
Error: ...
0
votes
1answer
49 views
MariaDB Optimization - insert and select
I have 2-3 php files.
I am sending sms to users by sending parameter in these php files. I'm checking the 3rd and 4th table before sending sms so select. If there is an id record, I do not insert. If ...
0
votes
1answer
20 views
mariabackup from remote mariadb10.3 galera | '/var/lib/mysql/' (Errcode: 2 “No such file or directory”)
The following setup applies:
3 node galera cluster in docker
backup container that should run mariabackup to get the data out of the galera cluster
network is fine. backup container can ping galera
...
1
vote
1answer
21 views
MySQL: How to determine a value type?
I am looking for some function that will return the type of given value/result like TYPE(value). Similar as for charset/collation for which there are functions CHARSET(value)//COLLATION(VALUE).
I ...
1
vote
0answers
41 views
Linguistic corpus design: order elements of different types
I'm trying to design a database for a linguistic corpus search engine. Basically there is a manuscript which contains several songs, and every song in turn contains stanzas and proses. Since stanzas (...
1
vote
1answer
21 views
Cannot remove orphaned InnoDB Temp Table - missing .frm file
For the second time, I have encountered an issue, where our MySQL server (MariaDB v10.1) suddenly threw an error - Could not remove temporary table: './database_name/#sql-4593_791', error: 120.
...
-1
votes
1answer
23 views
Problem in mysql database!
how to create a view of this query in (mysql) database.
SET @variable = 0;
-- Without Pagination
SELECT
id,
`credit`,
`debit`,
@variable := @variable + (`credit` - `debit`) `Balance`
...
0
votes
1answer
12 views
Using to_base64 function and regexp_replace with backreferences in MariaDB
I am using MariaDB 10.3. I want to replace a string using REGEX and encode part of the result into base64.
For example, I want to first invert the "a" and "b" strings in this example:
MariaDB [(none)...
0
votes
0answers
33 views
Optimizing range join (x between a and b) in MariaDB
My query has a join using a BETWEEN clause and it causes a full table scan to occur if I understand the EXPLAIN output correctly.
Query
EXPLAIN
SELECT g.id, g.name, c.id AS child_id, c.date
...
2
votes
0answers
52 views
Can you force sets of rows to be unique?
Let's say we have a table like this with a primary key of (nr, key):
nr | key | val
---|-----|------
1 | 1 | a
2 | 1 | a
2 | 2 | b
3 | 1 | a
3 | 2 | c
Every set of rows (grouped by nr ...
0
votes
1answer
25 views
Less than operator not working on where clause in mysqldump
I am trying to backup some tables with the built in mysqldump function from MariaDb version 10.4. My tables all have a column called insertDateTime with a timestamp in the form of 2019-09-11 12:00:00
...
0
votes
0answers
13 views
Creating new Slave without copying relay-log files from existing slave
I have a already existing master slave replication setup. I want to create a new slave but I cannot ssh to the existing slave machine due to some issue with the slave so I cannot copy the relay log ...
0
votes
1answer
12 views
Can I setup several mariadb clusters, each for a schema
I'm planning to use MariaDB Cluster, as a way to sync several databases from different peripherals (raspberry pis).
The peripherals don't share data, but all data should be shared with the main ...
0
votes
0answers
12 views
What is the best way to migrate old data into a running mariadb database?
We have a need to migrate a large number of 100GB databases onto a new infrastructure.
I would like to minimise downtime as much as possible, so am toying with the idea of migrating the clients to ...
0
votes
0answers
11 views
Custom methods in MySQL / MariaDB ORDBMS
According to the book I am reading ORDBMSs come with an option to write a program (method) in Java (or other programming language), compile it into Java byte code and register via some SQL command.
...
1
vote
1answer
31 views
Get normalized representation of SET datatype
In MySQL you can SELECT SET values from a table using this syntax:
CREATE TABLE `test_set` (
`int` int(11) DEFAULT NULL,
`set_2` set('a','b','c') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=...
0
votes
0answers
19 views
Why can't I access my mariadb after installing homebrew?
I just installed homebrew and mariadb via brew.
For some reason, get an error when trying to access the database.
If leave the password as empty, which is normally the default, it gives an error, and ...
1
vote
1answer
23 views
MariaDB Parallel Replication Drifting at random times
I have recently been thrown in the deep-end to manage our Database servers since our DBA left.
The current set-up is Mariadb (mariadb Ver 15.1 Distrib 10.1.38-MariaDB, for debian-linux-gnu), InnoDB (...
1
vote
2answers
39 views
Mysql Config Suggestions - CPU too high
My server (64GB,16CPUs) has mariadb 5.5.60.
The queries are around 500 selects /second and 300 inserts/seconds.
The queries are simple select of auth token and user details.
Both are properly ...
0
votes
0answers
10 views
Access a file on diffferent drive using connect driver on Windows XP
I need to access a *.DBF file on a network drive using CONNECT database engine.
When I use a file on the C:/ drive, it works as intended, but when I move the file to a remote drive, it says "no such ...
0
votes
1answer
23 views
Set the file name in “SELECT … INTO DUMPFILE”
I am trying to write a query to dump a single LONGBLOB data into a file.
SELECT data FROM table WHERE id = 123
INTO DUMPFILE fileName
which data, fileName, and id are the fields in the table.
SQL ...
0
votes
0answers
17 views
Database synchronization 2 times a day -MariaDB
I have 2 different servers. The first one is server1 and the other is server2. On servers, one works for websites (server1) and the other for mobile applications (server2) for security purposes. I ...
0
votes
3answers
48 views
Is there a way to get the max,count, or etc for all columns without naming them individually?
Normally if you want to find something across all columns you have to
select max(col1),max(col2),max(col3)...max(col99) from blah;
Is there a way to do this without naming every column manually?
1
vote
2answers
28 views
upgrade from Mariadb 10.2.* to 10.3.* using mariabackup?
Anyone successfully done upgrade from 10.2 to 10.3 using mariabackup without any downtime to the cluster?
I have 3 node cluster. Node1 serves the traffic rest are for standby and quorum. I would like ...
2
votes
1answer
71 views
“Normal” time for query execution
I have a table in a MariaDB database with ~700,000,000 rows. I have indexed some columns of it.
My question is whether my DB behaves "normaly" or not - because I have not had previous experience with ...
0
votes
1answer
51 views
How to store a bunch of columns and where you don't know how many there will be, the columns change, and new columns are introduced
So the so source of the data is the internet, and random bots that submit values to my apache server.
Periodically new columns appear, but they are not always present.
The other problem is then you ...
0
votes
0answers
27 views
Cannot write to Galera Cluster (Lock wait timeout)
My Galera cluster has gone down / locked up, I can read from all databases, but I cannot write to any table in any database, it just locks / waits forever.
I can fix it by shutting down all 3 mariadb ...
0
votes
0answers
23 views
Amazon RDS instance recovery during mysqldump
I have a db.t2.small read replica, which crashes every time I run mysqldump.
The issues started after I made some table changes - my main suspicion is the use of InnoDB.
This replica is used as a ...
0
votes
1answer
20 views
Auto-archive MariaDB10 database table
I am trying to auto-archive active database table to archive database table using stored procedure and event in MariaDB10. These are the steps I am following:
Create new table everyday just before ...
0
votes
0answers
49 views
MariaDB failing steps to reset lost root password
I'm running a local copy of MariaDB on OS X from Homebrew. It's working fine, but I've forgotten my root password (yeah, I know) and the usual reset is failing like this:
$ mysqld_safe ‐‐skip-grant-...
2
votes
0answers
42 views
Foreign key violation when trying to insert into a table [closed]
I have a table A and B in MARIADB. Please consider A as child of B and fk is ID. I am trying to insert value 15 to ID of A but a FK violation occurs.
error is similar to given below:
ERROR 1452 (...
0
votes
1answer
17 views
Mariadb encryption
I have a requirement to encrypt certain column of DB tables. Going through the links, have followed the below steps:
In /etc/my.cnf
[mysqld]
plugin-load-add = file_key_management
...
1
vote
1answer
23 views
Upgrade from MariaDB 10.3 to MariaDB 10.4 with Galera Cluster - mysql.global_priv table
I have 3-node MariaDB 10.3 Galera Cluster (Ubuntu 14.04) and I want to upgrade it to MariaDB 10.4 Galera Cluster (Ubuntu 18.04).
According to https://mariadb.com/kb/en/library/upgrading-from-mariadb-...
0
votes
3answers
38 views
Select youngest record
I have a MariaDB table "mdata" with the following schema:
+-----+-----------+---------------------+------+
| id | room | date | temp |
+------+-----------+---------------------...
0
votes
1answer
40 views
How to install mysql/mariadb on NIXOS?
I installed mariadb via configuration.nix. For the installation I made the following entries in my configurastions.nix.
services.mysql.enable = true;
services.mysql.package = pkgs.mariadb;
...
1
vote
1answer
33 views
MariaDB/MySQL SSL Replication Failure
After searching for a solution for the last 6 hours, I have come up dry in my attempt to add SSL to the replication. I managed to get it to connect with SSL via the mysql command line tool without ...
0
votes
0answers
17 views
Possible issues dumping a legacy MySQL 5.0 database to a local MariaDB 10.2 for develpment?
I've inherited a site that uses MySQL 5.0 and want to get a development version up and running with the existing data on my local (OS X) machine where I have MariaDB 10.2 but the compatibility tables ...
0
votes
1answer
14 views
MariaDB 5.5 — how to customize password rules
I am using RHEL 7, which includes MariaDB 5.5.
I want to set up minimum requirements for passwords: minimums length 15, 1 lowercase, 1 uppercase, 1 symbol, 1 number.
I figure that I am probably ...
0
votes
1answer
26 views
which node will execute events in master-master setup
Am newbie in this database replication. Right now on server master-master setup with Galera setup.
If I have events running in the database. It means from which master its running.
If one master down ...
0
votes
0answers
15 views
Which characters are used as letters inside a fulltext index of MySQL?
I can add a fulltext index to MySQL tables. This will index all words of the related fields inside the table.
But what are "words"? Indeed they incloses letters and digits (A-Z, a-z, 0-9) and maybe ...
0
votes
1answer
22 views
Get the last row that was left outside of a timeframed SQL query
Is there a way to write a SQL query with a timeframe condition to include the latest row that is outside of the timeframe (besides solutions like counting the size of the result set and querying for ...
0
votes
1answer
29 views
REGEXP_REPLACE is matching but not replacing with empty string
Given a table with a column called files_name, and you want to match everything after the match with regex, and replace it with nothing.
I have this code but it matches everything, but it's always ...
0
votes
2answers
40 views
Finding indexes that aren't used often or at all
Is there a facility or a recommended practice for determining which indexes are being used and which ones can be "purged"? At the minimum, I'm looking for a log file that over time can be analyzed for ...
0
votes
1answer
28 views
Direct database interaction with memory - how it works?
I am reading a book on databases and a chapter in it talks about buffer manager and it's replacement strategies.
I've noticed that the DBMS interacts directly with memory, as opposed to asking OS to ...
0
votes
1answer
72 views
MariaDB slave replication stopped after slave upgrade to 10.1.41
After the recent MariaDB 10.1.41 update which happened on 01/Aug/2019, few of our slaves have stopped syncing relay logs from master which is on 10.1.40. Slaves which got automatically updated to 10.1....
