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.

Filter by
Sorted by
Tagged with
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 ...

1
2 3 4 5
32