Questions tagged [mariadb-10.4]
The mariadb-10.4 tag has no usage guidance.
54
questions
0
votes
1
answer
34
views
innodb_encrypt_tables = ON Convert table Collation
With Encryption at REST enabled on a MariaDB (10.4) database, can you change the Collation of all tables without unencrypting the database e.g:
alter table <some_table> convert to character set ...
0
votes
1
answer
369
views
sometimes a query that runs really fast will take too long to finish in mariadb
I'm in a situation where some types of queries which run always fast when I execute them will (when ran by themeselves) hang for an undefined amount of time.
What particularly puzzles me in this case ...
- 149
0
votes
0
answers
65
views
how can I add a unique restriction on a column with json data for a specific key
I have this database table
create table db_etennisnlive.bss_custom_option_template
(
template_id int unsigned auto_increment comment 'Template ID'
primary key,
title ...
- 149
1
vote
0
answers
229
views
Mariadb large live database dump/restore fails when using recommended options
I have a 58Gb database that is live, using InnoDB engine and I want to dump restore it to a development machine for test purposes. I'm using mariadb 10.4 client and servers on Debian 11 Buster.
I've ...
- 111
0
votes
0
answers
81
views
MariaDB replication works via MASTER_LOG_FILE, almost instantly fails using MASTER_USE_GTID
We have a weird issue with MariaDB version 10.4.28+maria~ubu2004 (the official binary release for Ubuntu 20.04 LTS).
We dumped the master data using mariabackup (we have a single database), and ...
0
votes
0
answers
179
views
Making backups with mariabackup from slave MariaDB. Best Way possible
I am trying to determine the best way to create backups from a MariaDB 1.4 slave, and I have the following questions:
Will creating backups affect the MariaDB slave replication and performance?
Is it ...
- 1
1
vote
1
answer
230
views
Mariabackup in production queries are very slow
I'm using mariabackup to create a new slave from our master server and after I ran the command my database is getting stuck or queries are processed very low.
VM 64GB 32 core
I'm using NFS storage for ...
1
vote
0
answers
216
views
MySQL Checksum differs but cannot find difference in table
I have two tables which are expected to be the same
CHECKSUM TABLE robot, tbd_robot #Doesn't match
All the column types (storage format) are the same, the engine and collation are also the same. The ...
- 111
5
votes
1
answer
869
views
MariaDB 10.4. No traffic yet high CPU usage
I have a problem with which i fail to win. Have new server set up with Debian 10 (64) and MariaDB 10.4. There's no traffic on this server yet. So no slow queries or queries in total however, MariaDB ...
- 81
3
votes
1
answer
3k
views
MariaDB Aborted connection events
A windows server (2022) has MariaDB 10.4 running on it. In the Event Viewer for Windows logs > Application there are two events that happen every minute:
Aborted connection ### to db: 'unconnected' ...
- 55
1
vote
2
answers
982
views
MariaDB replication lagging up to a dozen times a day for no obvious reasons
We first set up MariaDB replication using less powerful servers but considering our increased database load we had to rent more powerful servers and that's when the troubles began.
Right now the ...
0
votes
0
answers
36
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, ...
- 1
0
votes
0
answers
571
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
3
answers
1k
views
Extract a substring where the delimiter might appear more than once
I have a column of strings with this pattern <email> - <id>. Email is always the first string.
I would like to extract only the email address but the problem here is that an email address ...
- 149
0
votes
1
answer
173
views
Maxscale R/W split - SELECTS hitting master?
Using Maxscale 6.3.0 in Read-Write Split, with 1 master and 2 slaves( all MariaDB 10.4), MASTER not configured to accept reads, is it normal that Maxscale redirect these to master? If yes, why?
...
- 111
0
votes
1
answer
20
views
What underlying table does SHOW PROFILES get the Query column from?
I found that SHOW PROFILE gets data from the information_schema.PROFILING table. I'm trying to figure out what table SHOW PPROFILES is getting the Query column from.
- 101
2
votes
1
answer
138
views
MySql suddenly started to choose different index for query, which causes it to be slow
Database is from Magento 2's site. Issue suddenly started to occur on our live site, dev site has the same database, but just with older data and same query is running completely fine there. I've also ...
- 21
0
votes
0
answers
177
views
Galera Cluster in read-write split and query cache
I currently have 3 MariaDB 10.4 servers, configured as a Galera cluster. In front of it, a Maxscale router to split writes (1 master) and reads (2 slaves). QC is disabled in all 3 servers.
My question ...
- 111
0
votes
1
answer
68
views
How do I set up one server to replicate X schemas from X different server, without resorting to mysqldump?
In other words; I'm trying to figure out how to restore multiple mariabackup dumps to one server (different databases). I assume I'm missing something obvious in the documentation, but I've been ...
- 101
0
votes
0
answers
136
views
Log files are still created even when general_log is OFF - MySQL (MariaDB)
I have a MySQL server (10.4.21-MariaDB) installed by the XAMPP stack on ubuntu.
I notice in the mysql folder (/opt/lampp/var/mysql) several files:
ip-172-31-14-21.err
ip-172-31-14-21.log
ip-172-31-14-...
- 111
1
vote
1
answer
92
views
Can I improve this query by adding indexes or tuning mariadb config?
We're having a problem with a query that keeps executing very very slowly
SELECT `e`.*,
IF(at_news_from_date.value_id > 0, at_news_from_date.value, at_news_from_date_default.value) AS `...
- 149
0
votes
1
answer
31
views
Identify duplicates data in combined columns
In a table there is Lang1 (First Language ID) and Lang2 (Second Language ID) that is repeating logically and I would like to keep one record and delete the other. I have encircled the entries in ...
0
votes
0
answers
123
views
select which algorithm a view is using
I have a view in a MariaDB 10.4.13 database that feeds into a database documentation process and gathers data on tables and views used in the database.
Amongst other fields it selects which algorithm ...
- 209
1
vote
1
answer
33
views
query left join error
I have two queries that get me a correct result, but when I try to join they give me an incorrect result.
I want to make something like:
SELECT directoscount.parte,
SUM(directoscount....
- 13
0
votes
0
answers
285
views
MariaDb 10.4 not starting in a freebsd jail after update
ran a pkg update and mariadb seems to be broken.
Cant find out what the reason might be after 2h our googling and digging.
Help to figure out would be highly appreciated. Thanks in advance.
The ...
- 45
0
votes
1
answer
56
views
Select data from a table to insert/update another similar table
I have two tables (in two databases):
create table catalog_product_entity_text
(
value_id int auto_increment comment 'Value ID'
primary key,
attribute_id smallint unsigned default 0 ...
- 149
0
votes
1
answer
2k
views
an error in your SQL syntax; check the manual that corresponds to your MySQL server version [closed]
I am restore a MariaDB 10.4.17 Database to Amazon aurora MySQL 5.6
while creating a table it is showing me this error
ERROR 1064 (42000) at line 25 in file: 'name.sql': You have an error in your SQL ...
- 69
0
votes
1
answer
308
views
mysql.gtid_executed table is empty
I want to keep track of GTIDs and the corresponding mysqlbin log somewhere. I see this mysql.gtid_executed table, however on my mariadb DB, there are no rows in this table. Any idea how this gets ...
- 21
0
votes
1
answer
1k
views
JSON in MySQL : get data from an object within a range of keys
What I have:
A mysql table, say table1
table1 contains two columns viz. id and data
id is int but the twist is data is JSON type and its keys are comparable
the table1 contains only one row (for the ...
- 105
2
votes
1
answer
732
views
what happens If I remove mysql-bin.index file?
Because of the file storage full, we are removing mysql bin log files.
Accidentally, we removed mysql-bin.index file.
What happens if I remove mysql-bin.index file?
These below are my DBMS info. Thank ...
- 45
1
vote
1
answer
2k
views
How to model dependent categories (parent-child-relation)?
Idea / Goal
I have two tables: Article (represents a Product) and Category. The relation is like this: An Article can be assigned to only one Category but a Category may have at least one Articles, ...
- 115
0
votes
2
answers
114
views
How should I model my database - cannot decide on multiple options
I apologize if this might be a rather beginner-level-question.
For my database design, I have worked out multiple options but cannot decide on which one I should implement. I am using 10.4.17-MariaDB.
...
- 115
0
votes
1
answer
1k
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 ...
- 135
1
vote
0
answers
2k
views
Table 'my_table' doesn't exist in engine
If I try
select * from my_table
I get:
"Table 'my_table' doesn't exist in engine"
but
show tables
contains this table in the list.
I tried:
mysqlcheck -u root -p --check --all-databases
...
- 111
0
votes
1
answer
119
views
MariaDB (RDS) going temporarily offline (with memory drop)
We have an issue with a database of ours on RDS running MariaDB 10.4.8 on a t3-large instance type.
For the past week or so, it's been sporadically inaccessible with no errors I can find. During the ...
- 113
1
vote
2
answers
905
views
Can I upgrade MariaDB replication infrastructure gradually?
I have 2 masters and 1 slave (replicating form both masters).
M1- MariaDB 10.4.11
M2- MariaDB 10.4.16
S1- MariaDB 10.4.12
I want to upgrade this LIVE system to 10.5 with possibly 0 downtime. The idea ...
- 135
0
votes
1
answer
131
views
Covering Index Not Being Used Effectively
I have a unique composite index on three fields (hash, is_customer, user_id) in a table a.
And queries that go like this:
select `a`.`id`, `hash`, `is_customer`, `user_id`
from `a`
where `hash` = ...
- 111
1
vote
0
answers
567
views
Bad write log efficiency, strange broken relationships
I recently switched galera synchronization method from rsync to mariabackup which greatly reduced CPU usage on our DB servers and also increased the innodb_log_file_size to 2G from about 600M or so. ...
- 111
1
vote
0
answers
228
views
Unable to grant privileges with admin account
I'm running MariaDB version 10.4.8 on Amazon RDS. Once the server is up and running, I need to create some databases, users and grant privileges to those users.
Creating the databases and users works ...
- 111
1
vote
2
answers
166
views
Problem with complex queries after migrating from MySQL 5.1 to MariaDB 10.4
I completed a migration from MySQL 5.1 master-slave system to a MariaDB 10.4 Galera Cluster. All the databases where copied over since they were on InnoDB per-file and they are working fine. However ...
0
votes
1
answer
295
views
MariaDB Out-of-Order Parallel Replication and OPTIMIZE TABLE
I am using MariaDB 10.4 and have question about Out-of-Order parallel replication.
I am optimizing databases(about 400GB of data) on Master every week. As optimizing DB on master do not have any ...
- 3
0
votes
1
answer
934
views
Mariadb always writes tmp disk to a file
I'm trying to understand why Mariadb (v10.4.12) is writing most of the temp tables to disk.
Created_tmp_disk_tables 196,8k
Created_tmp_tables 367.8k
There's 188G of RAM in the system with ~50% of it ...
- 1
0
votes
1
answer
666
views
UTF8mb4 MariaDB 10.4 - Max key length is 767 bytes error
I am running MariaDB 10.4.
I am trying to setup symmetricDS for database syncing. Some of the tables it creates have fields that are are VARCHAR(255). I know UTF8mb4 can only support up to VARCHAR(...
- 395
0
votes
1
answer
1k
views
Why doesn't MariaDB server recognise Server Audit settings in config file?
I'm trying to implement the Server Audit plugin that is built-in with MariaDB 10.4. I've added the settings to the my.ini config file, running on Windows 10.
[server]
...
#Audit Settings
...
- 11
0
votes
3
answers
66
views
Join without indexing the entire table
I have a query that pulls the 3 most recently commented posts on our forum. The issue is is that COUNT(forum_posts.id) as posts, is indexing the entire database of 600,000 posts and the query takes ...
- 3
0
votes
2
answers
50
views
slow UPDATE of rows by COUNTing from another table
I try to update a table by counting rows from another table as
UPDATE t1 SET c1 = (
SELECT COUNT(*) FROM t2 WHERE t2.id=t1.id GROUP BY id
)
strangely, this query stuck at Sending data state ...
- 4,491
0
votes
1
answer
458
views
How to add/update data from slave server to master server in MariaDB?
I am experimenting on whether I can tweak the functionality of master-slave replication. I can write data in my master server which gets updated in the slave server but it cannot happen the other way. ...
- 42
3
votes
1
answer
5k
views
SQL query with "INTO OUTFILE" does not work on MariaDB 10.4 but does on 10.0
I have an SQL query that executes perfectly on MariaDB 10.0.35 but fails with a syntax error on MariaDB 10.4.12. I attempted to contact them, but they told me to check forums and compare the ...
- 33
0
votes
1
answer
35
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.
Table CustOrders
Name (varchar)
Number_Orders (...
1
vote
1
answer
1k
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 ...
- 13