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,584
questions
0
votes
0answers
7 views
How to update MariaDB Timezone data
I know I can install timezone data to MariaDB by
mysql_tzinfo_to_sql /usr/share/zoneinfo/|mysql -u root mysql
MySQL document of the DB should be restarted. Is there any method not to?
After ...
0
votes
0answers
14 views
Select max date that corresponds with other tables
I have 2 tables. They look like this:
table a:
id datetime value
---- ------------------- -----
1 2020-05-20 07:34:46 1
2 2020-05-20 07:34:46 2
3 2020-05-20 07:34:46 3
4 ...
0
votes
1answer
20 views
How to add all combinations of two pairs of key-value to a table?
I have a database of items, as each item has a set of key-value pairs such
place => london
field => engineering
since I frequently need to find items having the same two key-value sets. I ...
1
vote
2answers
31 views
Simple query with JOIN on 60M rows using index takes forever in MariaDB/MySQL
Here's a rather simple SQL query:
select source_id
from data_sources
join sources on sources.id = data_sources.source_id
where log_time + interval sources.retention_days day < current_timestamp;
...
0
votes
1answer
12 views
Query processor ran out of internal resources and could not produce a query plan” - MariaDB
i have some query
SELECT COUNT(loan_payment) as setcolumn
FROM table1
WHERE paydate between {d '2020-05-01'} AND {d '2020-05-31'}
and period_code = 'MONTHLY'
AND company_id =...
0
votes
1answer
57 views
Designing an (almost) Read only database
I want to create a database with 3 main entities each of which will be subtyped further(by subtype I mean generalization-specialization).
This database will support a website where the users can ONLY ...
0
votes
0answers
35 views
Warning - Could not open mysql.plugin table. Some options may be missing from the help text
Upgraded to MariaDB 10.4 (from 10.3) on a new server.
Used MariaBackup to restore, and then ran mysql_upgrade.
Everything loads well, MariaDB works, but I get the following warning:
[root@server ~]# ...
1
vote
1answer
45 views
Is it Okay for mysql to hit 59% without no query running in my site?
Please I need some little help from you guys.
I have a VPS I bought since 2018 and since then one of the most difficult thing i have been facing is high load on my SQL, but i guess that was due to my ...
0
votes
0answers
23 views
What are the causes of longer SQL query execution? [closed]
I have a platform that runs on Dell PowerEdge R410 dedicated Server with 16Gb RAM and 500Gb HDD. The platform has constant active visitors/Users of over 85,000. Pages without connection to DB load at ...
0
votes
1answer
15 views
On MariaDB event scheduler doesn't run
I'm using MariaDB 10.3.22 on debian buster; I've set up a query to cleanup some expired user session from a table; this query should run every 15 minutes; the query is correct (tested and working on ...
1
vote
0answers
16 views
Slow database drop on MariaDB 10.1.44
I've got a test system which is creating and dropping databases. I'm getting pretty poor performance on a Ubuntu 18.04 machine with 26gb or RAM and many CPUs and SSD storage. It takes 15 seconds to ...
0
votes
1answer
11 views
MariaDB Maxscale REST API can't show result
hi i have already setup maxadmin on maxscale can access from rest api this is my configuration
.....
[CLI_Service]
type=service
router=cli
[CLI_Listener]
type=listener
service=CLI_Service
...
0
votes
1answer
31 views
How to fix conversion error from InnoDB to MyISAM?
How can I fix conversion error from InnoDB to MyISAM for these tables?
MariaDB [db_name]> ALTER TABLE wp_posts ENGINE = MyISAM;
ERROR 1071 (42000): Specified key was too long; max key length is ...
1
vote
1answer
30 views
Is MariaDB easier and better suited for a WordPress website? [closed]
I've been having problems with MySQL 8.0.20 on Ubuntu 18.04, and because I don't have a lot of knowledge on databases, I was thinking on switching to MariaDB as I heard it has better settings and ...
0
votes
1answer
13 views
How can I get partition name from value in MySQL/MariaDB
I have my table partitioned on day of month basis.
Now I need to archive data of the last 16th day date_add(now(), interval -16 day) to another table. For that I need to find out the partition name ...
0
votes
0answers
38 views
Cascading Triggers
I'm running MariaDB 10.2.31 on Ubuntu 18.04.4 LTS. My data consists 99% of JSON-encoded strings from a remote API.
Example (orders) very much simplified:
{
"id":123,
"referrer": "webshop",
...
1
vote
1answer
19 views
MariaDB Full Text Index - How to query old data as well
I have added new full text index to mariadb
ALTER TABLE <table name> ADD FULLTEXT INDEX records
I can query against this index only newly added and updated records but for older records (which ...
0
votes
1answer
20 views
Copy MySQL User but change HostName
I am migrating to a docker MariaDB setup and all my users were created w/ "Localhost". From what I read and tested ironically '%' excludes localhost. I want to copy my existing users adding a second ...
1
vote
0answers
48 views
Delete INFORMATION_SCHEMA
I recently created a new database and INFORMATION_SCHEMA was there. I am aware that users should not mess with backend services, but INFORMATION_SCHEMA has 0 bytes. I checked all the tables and there ...
0
votes
0answers
9 views
Adding an additional permission to my SQL user on every database they already have some permissions
Our corporate hosting server has had restricted SQL DB permissions for a very long time, but one of our end-user systems has started requiring a new DB permission, INDEX, which none of them have. I ...
0
votes
1answer
34 views
MariaDB - MySQL - ALL-DB Import - Table 'user' already exists
I am attempting to migrate from local MariaDB to Docker version which should in essence be as simple as migrating to a new SQL Server. I have setup the Docker container fine via but can't seem to ...
1
vote
1answer
24 views
How to get SHOW CREATE TABLE without table borders made of dashes?
When I use SHOW CREATE TABLE in my MariaDb command line client I get this output for my table:
+---------+----------------------------------------------------------------------------------------------...
0
votes
0answers
1 view
Is gtid_ignore_duplicates required for mesh masters?
I want to set up mesh replication with 3 masters. Only one will be active at any time, the other 2 will be at standby working as a backup. The idea is to replicate each from each, in a mesh fashion.
...
0
votes
0answers
30 views
Mariadb replication slave broken
I run Mariadb 10.3 on a Debian 10.3 server. The database was standalone until now and worked fine. Now I want to set it up as a replication slave. I use this as a guide:
https://www.linuxbabe.com/...
1
vote
2answers
23 views
deleted the /var/log/mysql folder
Due to a mistake i deleted the log folder for mysql. Is there a way to generate the folder and the error logs?
I'm using MariaDB 10.3 on Debian 9 Stretch
0
votes
1answer
15 views
How to remove wildcard character from field
I have fields that contain the character *. I've been trying to remove them but my commands fail with an error...
Example:
UPDATE name SET name = REGEXP_REPLACE (name, '*', '') WHERE category_id = ...
0
votes
0answers
15 views
Why won't node-1 restart successfully on my 4-node MariaDB Galera cluster, but the others will?
I setup a 4-node Galera cluster on the following platform:
CentOS 8.1.1911, with MariaDB 10.3.17 and Galera 3.26.
Galera config:
wsrep_cluster_address="gcomm://192.168.10.47,192.168.10.48,192.168.11....
0
votes
2answers
44 views
Cannot lower/figure out per connection mariadb memory usage
I'm running a small mariadb DB to serve some websites and monitoring tools (nexctloud, wordpress and icinga2). The machine that works as a DB server also run an elasticsearch + graylog instance so I ...
2
votes
3answers
25 views
mariadb multiline regexp_replace
I'm trying to replace multiple lines using the mariadb regex_replace function.
CREATE TABLE `test` (
`entry` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test` (`entry`) VALUES ...
0
votes
1answer
27 views
Do I need to redesign my Database and get rid of foreign keys for successful replication?
I plan to set up MariaDB replication either in master-master or master-slave mode. The main issue I have now are worries after reading this documentation.
We use foreign keys a LOT and we also use a ...
1
vote
1answer
43 views
How do I choose a Mariadb version to install, and what is the difference between mariadb.com and .org?
I have not worked on databases before but I am needing to research information about databases for work
I will be using Mariadb
What should I look for when choosing which version to download? It ...
1
vote
1answer
19 views
SLAVE off when loading data into the MASTER from another database
I have an active MASTER/SLAVE replication (Mariadb 10.2) with a single database (see scenario 1)
I decided to add in IMPORT database to populate the MASTER database as described in scenario 2. ...
0
votes
1answer
18 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 ...
0
votes
2answers
11 views
Mariadb with more than one node each node will have recent 1 year data
I am creating a saas which will then have time based data every seconds for example.
I want horizontal scalling when data reach its limit of 1 year.
after 1 year old data should be shift to another ...
1
vote
1answer
33 views
How many SELECT/UPDATE/INSERT Operations can you run on a 4 core / 8 GB RAM server? [closed]
Assume you are working on a web project with html/css/js frontend with php in backend. How many SQL Operations (executed by mysqli for example) can do do with 4 core / 8 GB RAM server ? The database ...
0
votes
1answer
31 views
Load test with databases. Best practices , tools [closed]
I need to design a way to stress a clon of my production databases.
At the moment, I'm using mysql percona xtradb with tree nodes running in google cloud.
I'm using haproxy as load database balancer.
...
0
votes
0answers
11 views
cannot create trigger without super privilege even though bin_log is off
I'm trying to import a magento 2 database dump to a MariaDB 10.1 server however it's asking me to provide SUPER privilege in order to finish the import.
It fails with the message
ERROR 1227 (42000) ...
2
votes
2answers
45 views
Mysql Database optimize suggestions
hey guys i hope you can help to tweak my database setup a lil bit.
Server
Intel Core i5-3550 4 x 3.4Ghz
16GB DDR3
2 x 1000GB
Software
nginx, php7.4, mariadb, wordpress, xenforo
30.0000 Unique ...
1
vote
1answer
20 views
How to alter tables safely to set row_format as Compressed or Dynamic in Mariadb Galera Cluster?
We have some tables with row_format=Compact. The biggest table is around 30Gb, so how safely i can alter tables and set the row format as Compressed for the big table and Dynamic for other three ...
0
votes
1answer
22 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(...
1
vote
1answer
20 views
How can I build a risk matrix in databse tables? [closed]
I'd like to build a risk matrix in a SQL database. A very basic one looks like so:
------------------------------------
| |Consequence |
|Likelihood|Low |Medium |High |
|Low ...
1
vote
2answers
117 views
Modeling a three-way association with optional relation
Business rules
I have three tables (Parties, Categories and Products) which representing the following relationships:
A product is classified by zero-one-to-many categories
A category classifies ...
0
votes
2answers
42 views
How to improve multiple case when query performance?
I have the following query which counts when the event_time is equal to the day of the month.
I also have indexes on the id of each table, template_name, from_email_address, event_time, event_status....
0
votes
2answers
20 views
Optimize MariaDB query with table that self-joins
I have a query that I think is taking way too long (as in I've not yet seen it finish!) but I can't figure out why.
The query (which is generated by an application that would be difficult but not ...
2
votes
1answer
33 views
Process was intentionally killed - now the process is still showing in “show processlist ”
We had a MariaDB long-running process (40 hours), so I killed the thread. But, the thread is still there, with:
showing Command as Killed.
a State of Unlocking tables.
Info is insert into ...
What ...
0
votes
0answers
20 views
MYSL: How to keep duplicates if they have different X?
This is my query
SELECT * FROM(
SELECT orderid, duration, is_renew, bundle_id, service_id, object_name, pkg_id_fk, disconnect_time, DATE_ADD(paid_on, INTERVAL duration HOUR) AS expires
FROM ...
0
votes
1answer
26 views
Select query on 8 million records with greater and less than columns (Slow)
I looked through the posts but cannot find an answer.
I inherited this application so the response is 5+ seconds
Server version: 10.2.25-MariaDB-log SUSE
I added an index on 'LFT' and 'RGT' which ...
0
votes
1answer
15 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
...
1
vote
0answers
21 views
How to prevent MariaDB mysqldump from prepending database name in CREATE VIEW?
I am having the exact same problem as this person here: How can I prevent mysqldump from prepending the database name in CREATE VIEW?
I am using MariaDB 10.4.7 x64 on Windows. For just ONE of my ...
0
votes
0answers
22 views
Why can mysqlfrm not read from my .frm file? See debug output
I'm working on a project at home and learning databases as I go. Because of my inexperience I've just learned the hard way that cut/pasting .ibd files is an extremely bad idea. The tables have ...
