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.
86
questions
0
votes
0answers
44 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
13 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
13 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 ...
0
votes
1answer
86 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
23 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
25 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
13 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
286 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
91 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
648 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
35 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
12 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
87 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
38 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
50 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
101 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
1k 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
1k 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
125 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
75 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
33 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
217 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
138 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
221 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
774 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
200 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 ...
1
vote
1answer
162 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
826 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
233 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
188 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
665 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
206 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
122 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 ...
0
votes
0answers
846 views
Unpivot in MYSQL?
Please I need help on how I can transform the data that appears in the attached photo. Maybe Unpivot SQL?
1
vote
1answer
197 views
MariaDB Inner join is slow
Here is my SQL (with explain):
EXPLAIN
SELECT * FROM
GBI gbi
INNER JOIN VF_KRED kred ON
gbi.vendor = kred.vendor;
Giving below the outcome:
Now, the select SQL takes ~10 seconds to produce the ...
2
votes
1answer
944 views
How to take mysqldump with Generated column?
I am getting below error while importing the dump,
ERROR 3105 (HY000) at line 82: The value specified for generated
column 'column_name' in table 'table_name' is not allowed.
When I describe the ...
1
vote
1answer
380 views
Can we use MariaDB mysqldump CLI to take dump from MySQL Community version? [duplicate]
MySQL server version:
mysqld Ver 5.7.26 for Linux on x86_64 (MySQL Community Server (GPL))
MariaDB mysqldump CLI version:
mysqldump Ver 10.17 Distrib 10.3.12-MariaDB, for Linux (x86_64)
I am ...
1
vote
1answer
48 views
Random selection with a “complex” subset structure
This is my first question in the whole StackExchange world.
I'm not sure if this kind of question fit perfectly here or in DBA Meta section so feel free to "flag" or move this question.
In my ...
5
votes
2answers
6k views
Change default collation for character set utf8mb4 to utf8mb4_unicode_ci
When creating a database without specifying a character set or collation the servers defaults are used (as expected).
MariaDB [(none)]> SHOW VARIABLES LIKE '%_server' ;
+----------------------+----...
0
votes
0answers
692 views
buffer overflow detected in mariadb 10.3.11
We are getting Assertion failure every time memory usage of MySQL spikes up to the overall system's memory.
Example:
InnoDB: Assertion failure in file
/home/buildbot/buildbot/build/mariadb-10.3....
2
votes
1answer
88 views
Random row selection but with distinct values for a subset of columns
I have a table that looks like this :
Key1 | Key2 | Key3 | Data |
-----|------|------|-------|
1 | 1 | 1 | a |
1 | 1 | 2 | b |
1 | 2 | 1 | c |
1 | 2 | ...
-1
votes
1answer
360 views
MySQL 5.7 to MariaDB 10.3.13 configuration overwritten - correct way
I have earlier MySQL 5.7 over Ubuntu 16.04 with following configuration in /etc/mysql/my.cnf.
[mysqld]
innodb_buffer_pool_size = 1228M
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M
...
0
votes
1answer
128 views
MySQL logging max_questions resource
How would I know in MySQL if the user has exceeded his max_queries per hour?
I know that I can see this error on the client side, but I just want to know that before client is reporting to me, ...
0
votes
1answer
595 views
MariaDB Encryption - unknown variable 'plugin-load-add=file_key_management.dll'
I'm really new to MariaDB and I need to set up encryption at rest. I'm running MariaDB 10.3 on Windows Svr 2012R2.
I have created a key and added the following to my config file (my.ini)
plugin-load-...
0
votes
1answer
2k views
Error: “A slave with the same server_uuid/server_id as this slave has connected to the master”
I am testing a node.js client which connects to a mysql DB (mariadb). It works fine on its own, but if I launch this client simultaneously on another host, the first instance gets disconnected with ...