Questions tagged [mariadb-10.3]
MariaDB 10.3 was released as stable (GA) with version 10.3.7 on 25 May 2018. Please also tag with mariadb for search purposes.
98
questions
0
votes
1answer
21 views
LEFT and RIGHT JOIN multiple table, show all null data on mariadb 10.3
i want to join 3 table with null value,
here i have 3 table folowing
tbl_reporting tbl_criteria tbl_student
|===============================| |...
0
votes
2answers
52 views
LEFT JOIN subquery fails in the presence of an EXISTS clause in the main query
I have this query:
SELECT c.id contact_id, engagement.sent
FROM contact c
LEFT JOIN (
/* ➊ Inner query */
SELECT eq.contact_id contact_id, COUNT(*) sent
FROM mailing_job j
INNER JOIN ...
0
votes
2answers
27 views
How to find unused columns in MariaDB tables
I have a lot of tables in my DB that have a number of columns which might not be getting used as they slowly lost their relevance with time, I need to find such columns and delete those from the ...
0
votes
0answers
20 views
How to use a query to get the definition of (MariaDB 10.3.27) database objects (specifically the variables they use)
I have written a query to get most of the useful information about the routines in a MariaDB database for a database documenter that I am developing.
The code below will return routine name, routine ...
0
votes
1answer
34 views
Please help tune MySQL config
Anyone can help me to check this MySQL configuration? I have a VPS 2GB RAM - 2 vcpu, installed cyberpanel from litespeed. Any help would be appreciated!
my.cnf settings are:
[client]
port ...
2
votes
0answers
21 views
MariaDB - Query to find unused indexes
I need to find unused index on a MariaDB database and do not have 'userstat' enabled, I found this query which is supposed to find unused indexes, but I am unable to get a full understanding of this ...
0
votes
2answers
41 views
Remove unused Indexes MariaDB
I have a big database with 50 tables and each table can contain between 100,000 to 100 million records. On these tables are there are 1000s of indexes, but many of the indexes are now not in use.
How ...
0
votes
1answer
20 views
MariaDB 10.3.27 Memory usage problem
Greetings!
I tried to optimize MariaDB configuration with my.cnf. Everything was OK, but available memory falling down each hour. I'm using MariaDB with Zabbix server on the same server. CPU: 4 Cores, ...
1
vote
1answer
20 views
How to limit impact of a bad application on MariaDB Galera Cluster with MaxScale?
I have a Galera Replication cluster with three MariaDB nodes where a Maxscale Active-Passive cluster in front provides a single node image to tis clients.
I have a bad behaving client, which opens ...
-1
votes
1answer
16 views
Can't update database table's column using trigger or from php page but works after using update sql query in phpMyAdmin
After inserting into my audit log table called userUpdateLogand it calls an before insert trigger to insert into userProfiles and users based off the columns insert into their respective table.
but ...
0
votes
0answers
29 views
Should a mysql slave answer queries much slower then the master?
I am seeing a slight issue on a mysql slave server, or at least on the surface it appears there might be an issue.
Both mysql's are virtual machines
The master and slave are the same OS (debian10) and ...
-1
votes
1answer
24 views
MariaDB CONNECT engine to read external mongodb collections
I want to access MongoDB collections through MariaDB 10.4.17. Both are installed on my local machine running Windows 10.
Software
listening port
MongoDB
27017
MariaDB
15501
I installed the MariaDB ...
0
votes
0answers
140 views
How to enable use of SSL in MySQL server?
I have 2 devices on my local network: a remote MySQL server (using MariaDB 10.3) and a laptop (acting as a client with Python). I need to connect to a remote MySQL server via Python using SSL ...
1
vote
0answers
62 views
Why setting up new galera cluster with mariabackup as sst starts but all other nodes failed with same error?
I did a fresh reinstallation of mariadb-server on all the nodes
(I removed using sudo apt purge mariadb-*)
I started the first node using sudo galera_new_cluster it went fine and is still running. but ...
0
votes
0answers
16 views
How to setup Galera Cluster when nodes are located multiple sites?
I am setting up a web app that runs on 2 sites and shares db, Initially I setup 1 Galera Cluster with all nodes in it,
but sometimes SST on bringing node up takes lot of time and service start times ...
0
votes
0answers
25 views
MARIADB-10.3.14 VM OOM KILLED AND TUNING [duplicate]
DEDICATED VM FOR MARIADB -
CENTOS 7.7 \\ 16VCPU/250G - JBOD DISKS/NO SSD.
OS PARTITION AND DATABASE ARE ON DIFFERENT FILESYSTEM.
Span of every two weeks, Kernel OOM happens.
Reducing ...
1
vote
1answer
199 views
Very slow query on MariaDB 10.3 compared to MySQL 5.5.50
I already discussed how much slower performance I have experienced on MariaDB 10.3 compared to MySQL 5.5 for some queries in my previously posted thread, but I am now struggling with a specific kind ...
0
votes
0answers
20 views
InnoDB engine lock tables higher than tables used
I am running the SQL statement
CREATE OR REPLACE TEMPORARY TABLE my_schema.tmpx_test AS
SELECT col1
, SUBSTR(col2, 1, 5) AS col3
FROM my_schema.my_table
WHERE col4 = 'my_string'
AND POSITION('...
0
votes
0answers
16 views
Query execution time increased after changing server
I have changed the test database server from windows to synology. After this change, there is one query which takes 9-10 sec to get a result on synology. On the Windows server , it took 0.18sec to get ...
0
votes
1answer
18 views
Why does the query plan start with a not-filtered-for table when the filtered-for table's entries exceed a certain threshold?
We process data for our customers. Each customerrequest and is associated with one or more items (less than two on average) and falls into one of <100 categories, foreign keys to which are also ...
1
vote
0answers
28 views
How can I fix mariaDB in debian server?
I'm already too tired of trying so many different things and I can't even bypass an error which is running
# mysql -u root -p
when it asks for password, no matter what I type it shows
Welcome to the ...
0
votes
1answer
29 views
InnoDB engine getting disabled after decreasing innodb_page_size to 4K
I'm trying to optimize my database (mariadb) for high write (insert & update).
Database is running fine with default innodb_page_size (16K), but when I decrease the value of innodb_page_size=4K.
...
1
vote
0answers
14 views
why timezone like '+00:00' ignored with select but error with create table?
I am wondering, why does mariadb ignore timezone like '+00:00' with select statement however throw the error with create table statement?
SELECT CAST('2020-07-09 09:55:36.933554+00:00' AS DATE) dt;
# ...
0
votes
1answer
605 views
Extract all objects from json array using MariaDB 10.3
How can I extract every object from this json array using MariaDB 10.3? I think the JSON_TABLE function in MySQL does what I need, but I'm currently constrained to MariaDB.
SET @json_doc='[
{
...
1
vote
1answer
161 views
Why is disk IO higher on Debian 10 (MariaDB 10.3) with MySQL replication?
I have a MySQL/MariaDB master-master replication setup that has been working well for several years, the db and tables are not very large (under 200MB for 18 tables). These were on 2 servers running ...
1
vote
1answer
1k views
ERROR 1067 (42000): Invalid default value for 'uploadedDate'
Hi everyone i am getting an error trying to run the below query ERROR 1067 (42000): Invalid default value for 'uploadedDate'
CREATE TABLE `filenew` (
`id` int(11) NOT NULL AUTO_INCREMENT /* ...
2
votes
1answer
43 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
2answers
13 views
convert_unixtime output wrong date
Behind the scene: Basically I operate an app that heavily relies on time calculation. We store unixtime in the database according to the user's timezone. Now to perform specific operations we need to ...
0
votes
1answer
108 views
MariaDB/MySQL: Import from dump (.sql) file remotely via network
After I set my.ini file following the answer by Rolando here : https://dba.stackexchange.com/a/83385/191579 , today I managed to import about 40GB+ sqldump file in around 2-3 hours only. While on a ...
0
votes
2answers
39 views
Using subquery within IF condition [closed]
Hello i am having a weird problem currently i have the following query
SELECT *
FROM `txt_campaign_message` `cm`
LEFT JOIN `txt_campaign` `c` ON c.id = cm.campaign_id
LEFT JOIN `...
2
votes
1answer
63 views
MySQL / MariaDB Get last 7 days summarized and with avarages calculated
currently, I have this table:
CREATE TABLE `plant_data` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`plant_id` BIGINT(20) UNSIGNED NOT NULL,
`temperature` DECIMAL(5,1) UNSIGNED ...
0
votes
3answers
116 views
Grant MonitorAdmin / PROCESS privilege to user
I need to assign the monitoradmin privilege to a user because this is what an application called "dynatrace" needs to monitor the server (some details in a post on their site).
According to https://...
0
votes
1answer
2k views
How DISCARD the tablespace on mariadb while table is not found?
I run the mysql_upgrade against my mariadb 10.3 and get the following error - see here for details.
ERROR 1813 (HY000) at line 85: Tablespace for table 'mysql.transaction_registry' exists. Please ...
3
votes
0answers
2k views
InnoDB: Error: Table “mysql”.“mysql.transaction_registry” not found after upgrade to mariadb 10.3
I've upgraded the mariadb to 10.3 packages on my Debian 10, however something went wrong and I ended in the same issue which is mentioned several times
Cannot open table mysql/innodb_index_stats [...
0
votes
1answer
176 views
mariadb takes too much load though having sufficient RAM
I am facing more CPU usage for my app server. Server configuration is as below.
1> CPU: Intel(R) Xeon(R) CPU E5-2650 v4 @ 2.20GHz 8 core
2> RAM: 32 GB
3> 600 GB SSD NVRAM
4> Mariadb ...
0
votes
1answer
98 views
mysqldump generating inconsistent dumps
I cannot get mysqldump to generate a dump that will properly restore. The dump is being generated to setup a new slave, but whenever the slave starts up, it gets foreign key constraint failures.
I'm ...
1
vote
1answer
38 views
SQL Query GROUP BY JOIN
I have the following tables:
CREATE TABLE `Channels` (
`id` INT(11) AUTO_INCREMENT PRIMARY KEY,
`title` VARCHAR(255) NOT NULL,
`views` INT(11) NOT NULL DEFAULT 0,
) Engine = InnoDB;
INSERT ...
0
votes
0answers
40 views
MariaDB maintain key order in COLUMN_CREATE
I am using a 10.3.11 table with the following structure
MariaDB> desc MetaData;
+--------------+------------------+------+-----+---------+----------------+
| Field | Type | Null ...
0
votes
1answer
325 views
Group_concat on IF Select MYSQL
I have a query as below:
SELECT o.order_id,
c.customer_name,
Group_concat(p.product_name, ' x', p.product_quantity ORDER BY
receipt_order ASC
SEPARATOR
'<br>')...
0
votes
1answer
188 views
Mysql binary logs are not deleted after expiration
I have 3 Mysql 5.6 nodes in one galera cluster in the first DC and 3 MariaDB 10.3 nodes in another galera cluster in the DR site. One node on the 1st DC is the master for one slave node on the DR site....
0
votes
0answers
256 views
mariabackup slave info and multi-master replication
I have several master databases using mariadb 10.3 and replicate then all to one replica using multi-master replication. All is working fine.
I want to backup that replica server, so i can restore it ...
1
vote
1answer
952 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
...
0
votes
0answers
245 views
index corrupt error while trying to index a large table
I am trying to index a large table, approximately 12 million rows and 33GB in size. There is currently no indexing on the table. When I am trying to index one of the columns in a table, I get the ...
0
votes
1answer
219 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
1k views
JSON field saves as longtext datatype
I'm trying to create JSON column in 10.3.16-MariaDB but every time I save the configuration in PHPMyAdmin, the column saves as longtext datatype.
No errors shown :|
Where could be the problem?
...
1
vote
1answer
256 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
2answers
297 views
Update column value based on other column with manage duplicate value MySQL query
I need to update column value based on other column with adding separator and with manage duplicates values using sql query.
For example :
I have one database table A.
Here in it two columns : ...
0
votes
2answers
778 views
`command not found` when trying to stop MySQL
On OS X I need to reset the password of MySQL. It was installed with Homebrew.
I found this article which explains how reset my password: https://dev.to/oneearedmusic/access-denied-reset-mysql-root-...
0
votes
1answer
246 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 ...
2
votes
1answer
157 views
MariaDB database migration - hangs infinitely while MySQL works no problem
I've migrated a website from a server running MySQL 5. to MariaDB 10.3, I was expecting if anything for the MariaDB to outperform MySQL and on lots of other sites on the platform it has. However, this ...
