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,317
questions
0
votes
1
answer
810
views
Mariadb (MySQL) On Windows- problem entering non-ASCII characters in a query
MariaDB: mysql Ver 15.1 Distrib 10.3.8-MariaDB
OS: Windows 7 Pro 64 bit
I have my server database and client all set up (I believe) to support utf8 end to end-
(**my.cnf**)
#
# Set everything to ...
CommunityBot
- 1
0
votes
0
answers
16
views
Backups cannot be restored on AWS RDS - View creation problem. Access denied; you need (at least one of) the SUPER, SET USER privilege(s) for this
The environment is MariaDB 10.6.8, via Amazon RDS. We have all 'admin' rights but not SUPER on AWS databases.
I have a view in an application that has it's data stored in a single schema. We use non-...
- 101
0
votes
3
answers
161
views
Mysql query that is very quick (but sometimes very slow) versus an alternative version of the same query that is consistently a *bit* slow
I have a query that is part of a larger query. I have tried two versions of the query to try to improve the page run time. One version runs almost instantaneously.. but sometimes (approx one in 50) ...
CommunityBot
- 1
1
vote
1
answer
742
views
Load Balancing multiple databases in a multi-tenant setup
We have a multi-tenant setup where each client of ours runs a DB that is identical to every other client. We were using Mysql sofar and with CentOS 7.x ,switched to MariaDB.
If we want to distribute ...
CommunityBot
- 1
1
vote
2
answers
995
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 ...
CommunityBot
- 1
0
votes
1
answer
1k
views
Mariadb/MyISAM: Incompatible key or row definition between the MariaDB .frm file and the information in the storage engine. You have to dump an
We have a db with around 150 tables in, of which about 30 or so are partitioned as such :
ALTER TABLE `myTable`
REORGANIZE PARTITION PFUTURE INTO (PARTITION P201910 VALUES LESS THAN ('2019-11-01'),
...
CommunityBot
- 1
4
votes
2
answers
5k
views
How to fix high memory usage of MariaDB?
I am using MariaDB(10.1.21) with following storage engines:
InnoDB
TokuDB
Spider Engine
System Configuration is :
3GB RAM
Dual Core Processor
I have tried pt-mysql-summary tool to identify the ...
CommunityBot
- 1
1
vote
1
answer
25
views
When do you need to run Analyze Table on MariaDB?
We have migrated a few large databases from earlier version of 10.1.22 to 10.6.7 - the latest provided by ubuntu repo for 22.04. All was well for a few weeks and then we started seeing very slow ...
- 70.5k
1
vote
1
answer
29
views
When I use group_concat on two fields, can I count on the two concatenations having the same order?
If I have a table like this:
bagid fruit color
----- ----- -----
1 apple red
1 banana yellow
and I select bagid,GROUP_CONCAT(fruit),GROUP_CONCAT(color) grouped by bagid, can I ...
- 22.1k
0
votes
0
answers
48
views
How do I connect to mysql database with password
Using PHP 7.4.27 (now using 7.4.29) with MariaDB in xampp
I can access everything using phpmyadmin. I am using the following on my localhost
$con = new mysqli($host, $user, $pwd, $db);
I can connect ...
- 476
0
votes
0
answers
24
views
After upgrade Node not joining galera cluster
I have upgraded one of the 3 debian node server and it was upgraded successfully except on icinga it shows that node is disconnected with other node and the mariadb got upgraded from 10.6.8 to 10.6.10....
- 1
1
vote
1
answer
606
views
MariaDB: Duplicate entry for key on OPTIMIZE with no duplications
There is a table named contracts with following structure:
MariaDB [db_name]> DESCRIBE `contracts`;
+-----------------------------+--------------+------+-----+---------+----------------+
| Field ...
CommunityBot
- 1
0
votes
1
answer
3k
views
InnoDB: Error: log sequence number is in the future!
Hi I have 2 node cluster where recently went offline not sure why. Node1 started fine and all the traffic is on node 1 but node 2 still down due to below error. I tried set the inodb recovery to 3 and ...
CommunityBot
- 1
0
votes
2
answers
177
views
Multiple DB restore in sequence
I am restoring several MariaDB databases into a Amazon AWS RDS MariaDB instance from an AWS EC2 machine in the same VPC / region etc.
I am using a shell script like so:
mysql -u root -h hostname....
CommunityBot
- 1
0
votes
1
answer
14
views
Turn page compression off in MariaDB
I have a table in a MariaDB database with:
`PAGE_COMPRESSED`='ON'
I'm looking for a way to make it off without recreating it with an "ALTER TABLE". I read the official documentation but ...
- 70.5k
0
votes
2
answers
5k
views
MariaDB Galera Cluster initial rsync replication failing
I'm trying to install a new galera cluster. The primary host started fine, but the secondaries are failing during the state transfer with rsync, and are not starting. I haven't been able to fix the ...
CommunityBot
- 1
0
votes
0
answers
25
views
MySQL Error Code 1175 even though I am using a primary key
First time posting here although I am fairly sure this specific situation hasn't already been addressed. The closest one I have found has been where someone was using their primary keys in a JOIN ...
- 1
0
votes
0
answers
19
views
Can't edit a value in a table with triggers
So I'm trying to make a simple scheme of order-> payment-> shipping. Here is the structure of the main tables:
cust_order (order_id, customer_id, catalog_id, items_ordered, order_date, ...
- 26.7k
1
vote
1
answer
2k
views
MariaDB crashes on mismatched tablespace ID for InnoDB
I'm having an issue with some corrupted InnoDB tables on a stand-alone MariaDB 10.1 server running on Debian Stretch. I've encountered this issue in the past, but never found a working solution, so I ...
CommunityBot
- 1
1
vote
3
answers
347
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 ...
CommunityBot
- 1
0
votes
0
answers
10
views
fixing GTID based multi source replication error on selected source
I can't find or think of a solution when ever i encounter db replication errors on affected source and repositioning the GTID in the replica.
What i do is retrieve from (four) masters gtid postion (...
- 5,861
0
votes
1
answer
15
views
MariaDB: Missing FILE_CHECKPOINT
Running MariaDB v10.9.3 on Arch Linux. I've hosted a file storage cloud with it for years. Recently decided to create a database for another project, and am very new to MySQL interactions, so it's ...
- 5,861
1
vote
2
answers
3k
views
MariaDB uses all /tmp space
I'm running a Galera cluster with MariaDB 10.
One of my nodes reports 100% usage disk for the /tmp table.
/dev/mapper/vg0-LV--Tmp 1,8G 1,7G 228K 100% /tmp
I can't see files in /tmp with ...
5
votes
2
answers
571
views
How (if at all) does Galera enforce authentication for SST via rsync when adding a node?
I have to be missing something here.
It just hit me as added a new node to my cluster in order to prepare for the removal of a different node: "How does the cluster know that it is okay to send the ...
- 5,861
0
votes
1
answer
19
views
MariaDB Multi level replication (replicate from slave)
I am looking for a way to set up MariaDB replication from slave.
My configuration looks as following:
First datacenter:
Master + 3 slaves connected via private switch
Second datacenter:
Master + 3 ...
- 70.5k
0
votes
0
answers
13
views
Square Brackets used to enclose a string being randomly removed
Quite a strange enquiry here...
I am using a low-code app builder and one of the widgets used to pupulate the table is a multi-select dropdown...
The multi-select dropdown saves entries to the DB in ...
- 111
1
vote
1
answer
175
views
How do I recover from Mariadb(10.4.18) binlog corruption
I have a 3 node Mariadb Galera cluster running Mariadb 10.4.18 on ubuntu 18.4.
I applied updates to one of the nodes this morning and now Mariadb doesn't start on that node with the following error.
[...
CommunityBot
- 1
0
votes
2
answers
32
views
High Load MariaDB Cluster (Galera Multi Master Replication) - Same hardware needed for Slaves?
My Galera Cluster is on very high load.
To run it fine, I´m using strong hardware and for sure a lot of custom MariaDB settings and custom OS tweaks.
Now, I want to add Slaves to get "realtime&...
- 70.5k
3
votes
1
answer
4k
views
InnoDB: Could not find a valid tablespace file for 'database/table'. Mariadb 10.1.40
Generally server works fine without any issues. But recently I enabled error logs to test few things and I noticed an error
InnoDB: Could not find a valid tablespace file for 'database/table'.
This ...
CommunityBot
- 1
0
votes
0
answers
17
views
Writing SQL with selected value as title and calculated sub-total
I need help writing my SQL query. I've got 3 tables.
Here's a simplified example.
Items
Name
Description
First
desc 1
Second
desc 2
Item Detail
Name
Supplier
Reorder Level
First
Amazon
1
Second
...
- 448
0
votes
1
answer
395
views
MySQL Conditional Counter Based Other Columns?
I have a query that returns purchases for all customers in a store across a date range. It works fine, but now I've been asked to modify the results so only the first purchase per customer per day is ...
CommunityBot
- 1
2
votes
2
answers
71
views
Can a redundant index improve SELECT performance?
Is it possible that adding a redundant index improves the performance of a query? I encountered a case like that several days ago and since then I've been scratching my head: how?
The MariaDB 10.4 ...
- 143
0
votes
1
answer
187
views
show detail processlist like at mariadb on maxscale
on mariadb when i need check what are query is running or sleep on mariadb instance.
i can see with SHOW PROCESSLIST or select * information_schema.processlist.
But after mariadb join and client ...
CommunityBot
- 1
1
vote
1
answer
915
views
deadlock in mariadb: with non-index & fk table
when i try to do an operation concurrently, there exists an deadlock from mariadb show engine innodb status, the deadlock log is as follows
------------------------
LATEST DETECTED DEADLOCK
----------...
CommunityBot
- 1
0
votes
1
answer
3k
views
Mysql_upgrade fails, table is corrupted
I have a mariaDB with a table that is corrupted. It's useless data to me, so I've tried to drop the schema but it won't drop. Instead, it gives me this error when trying to drop it:
"Error Code: 1805....
CommunityBot
- 1
1
vote
2
answers
259
views
How do I find out what's causing processes to wait for table flush?
I'm currently using MariaDB 10.3 and am troubleshooting this case where threads will start to build up until the MySQL service eventually has to be restarted. I was able to capture the process list ...
- 964
0
votes
0
answers
18
views
Behavior Transactions PDO::beginTransaction() and "BEGIN"
I have a strange behavior in a php script (PHP 8.1) using MariaDB 10.7.5 database.
This one with native SQL works:
$pdo->query("BEGIN");
[ ... some database stuff ... ]
$pdo->query(&...
- 10.5k
3
votes
1
answer
2k
views
(Got an error reading communication packets) but no error on client
i've search a lot about this thread without results.
we don't know how to resolve this:
DB : mariaDB
SERVER: Microsoft Azure
enviroment: Nodejs with Knex (pool connections, min 0 max 300)
our client ...
CommunityBot
- 1
0
votes
3
answers
819
views
MySQL dump import into MariaDB syntax error to use near '*/'
This is the result from mysqldump 10.13 and MySQL 8.0.28 Server.
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` int(11) DEFAULT ...
CommunityBot
- 1
0
votes
1
answer
40
views
Mysql/Mariadb - Speed up query with varchar columns already indexed but index its first 2 characters (USxxxxxxxx, CAxxxxxxxx, etc)
On my project (MariaDB 10.3), I use a table for storing data per ISIN code (a financial asset identifier). For instance, "US3696041033" for General Electric.
I have 4 Millions rows in my ...
- 70.5k
0
votes
0
answers
22
views
Backup with mariabackup error Can't open dir ./System Volume Information
I attempt to create a backup of Mariadb on a Windows installation. My commando C:\wamp\bin\mariadb\mariadb10.7.3\bin\mariabackup.exe --backup --target-dir=E:\mariadb-backup\ --user=root --password=
...
- 101
0
votes
1
answer
180
views
optimizer uses all the wrong indexes instead of one right
This is another stupid question about optimizer. Synopsis is exactly as the subject is: the optimizer does it wrong. Seems like is has an absolute desire to use any index that contains REALM_ID field: ...
CommunityBot
- 1
0
votes
1
answer
495
views
Deadlocks appeared after moving to MariaDB10.2 from MySQL5.5
After moving to mariadb-server-10.2 from mysql5.5 I am facing deadlock issues. Please see the output of 'show full processlist'. Can someone give some suggestion?
| 162506 | catalog | ...
CommunityBot
- 1
1
vote
1
answer
584
views
MariaDB: granting access to a DB before creating the DB
I've just discovered that I can create a user and grant right to it on a given database even if that DB doesn't exist yet (it will be created in a second step).
MariaDB [(none)]> CREATE USER foo2@...
CommunityBot
- 1
0
votes
1
answer
36
views
I can not install mariadb through dnf
I use "sudo dnf install mariadb" to install mariadb on fedora36 but it showed me this error:
" file /etc/my.cnf from install of MariaDB-common-10.9.2-1.fc36.x86_64 conflicts with file ...
- 5,861
0
votes
0
answers
7
views
Galera Cluster MariaDB down due too high CPU
We have 3 nodes of MariaDB Galera Cluster runing with 2 HAproxy for loadbalancing. Recently, all 3 nodes of MariaDB crashed . I noticed that CPU was really high like 99% or 100% on the 3 nodes before ...
1
vote
2
answers
174
views
Wordpress UPDATE queries on MySQL database stuck
I have an Amazon 24XL server
96 Cores
378 GB RAM
Database size 5.7G
Debian GNU/Linux 9 (stretch)
PHP 7.3.16
mysql Ver 15.1 Distrib 10.3.22-MariaDB
I have only one WordPress site where users read ...
- 93.9k
0
votes
0
answers
34
views
How to prevent insertion of row which differs only in primary key from existing row?
If my table contains the following
+----+-----+------------+-----------+
| id | uid | date | operation |
+----+-----+------------+-----------+
| 1 | abc | 2022-09-21 | create |
| 2 | xyz | ...
- 103
0
votes
1
answer
227
views
MySQL / MariaDB - Start multi-source replication with mariabackup (xtrabackup)
Many times I successfully performed procedure described in the https://mariadb.com/kb/en/setting-up-a-replication-slave-with-mariabackup/ - it's fastest and easiest because replica is restored by ...
CommunityBot
- 1
2
votes
1
answer
271
views
Safe values for InnoDB variables when using FULLTEXT searches
I'm using Maria DB, version 10.2.22, where one database column uses FULLTEXT for a broad document searching. However, I've ran into a "Table handler out of memory" on some searches. The ...
CommunityBot
- 1