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.
69
questions
0
votes
1answer
29 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 ...
1
vote
0answers
56 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
45 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
30 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 ...
0
votes
0answers
10 views
Occasional “access denied” on a persistent SSL connection, to MariaDB, from PHP
When using SSL to connect to a MariaDB instance on Amazon RDS, I'm able to setup a persistent connection, and login almost every time.
But I will occasionally get:
[Warning] Access denied for user '...
1
vote
1answer
31 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
28 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
44 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
23 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
89 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 ...
0
votes
1answer
167 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
63 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
43 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
192 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
97 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
54 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
152 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
60 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
62 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
380 views
Unpivot in MYSQL?
Please I need help on how I can transform the data that appears in the attached photo. Maybe Unpivot SQL?
0
votes
0answers
65 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 ...
0
votes
0answers
312 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 ...
0
votes
1answer
77 views
Can we use MariaDB mysqldump CLI to take dump from MySQL Community version?
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
45 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 ...
3
votes
2answers
2k 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
344 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
60 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
254 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
69 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
297 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
829 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 ...
0
votes
1answer
26 views
How to use MariaDB's new “Invisible” column type
I have been playing around with this new column type. The use case being that I want to hide this column from a client issuing a "Select *" query.
But the column seems to behave like any normal ...
0
votes
0answers
113 views
Returning a set datatype from MariaDB function
Given below is the syntax for the MariaDB's CREATE FUNCTION:
CREATE [OR REPLACE]
[DEFINER = {user | CURRENT_USER | role | CURRENT_ROLE }]
[AGGREGATE] FUNCTION [IF NOT EXISTS] func_name ([...
3
votes
2answers
658 views
MariaDB 10.3: InnoDB much slower than MyISAM
I'm using MariaDB 10.3.12 on Arch Linux and haven't modified any of the default settings under /etc/mysql. The following script takes 0.75 s to run on average using the default InnoDB engine:
drop ...
1
vote
1answer
177 views
Unexpected results while using HAVING without GROUP BY
Given below is the snapshot of the SQL queries:
MariaDB [university]> select * from department;
+------------+----------+-----------+
| dept_name | building | budget |
+------------+----------+...
0
votes
0answers
312 views
SELECT … INTO OUTFILE “Read-only file system” error
I'm using MariaDB 10.3.12 on CentOS 7, using the MariaDB-server RPM package from MariaDB's yum repository.
I'm trying to do a simple SELECT ... INTO OUTFILE into a different directory than /var/lib/...
0
votes
1answer
35 views
10.3.9-MariaDB can not query equal operator with special character
I have a MariaDB database that has a filed TagName with value ~!@#$%^&*()_+|}{":?><./';[]\=-`
I had set mode SET @@SQL_MODE = CONCAT(@@SQL_MODE, ',NO_BACKSLASH_ESCAPES');
When I try to ...
0
votes
0answers
354 views
“Resource deadlock avoided during COMMIT” when a node leaves the group
I believe it's a bug in MariaDB Galera (10.3) but wanted to confirm. I have 3 nodes in the cluster (A, B, C) and a sysbench running the OLTP write-only test connecting only to node "A". When the node "...
-1
votes
1answer
54 views
Query joining four tables resulting in one row
I have these two queries which pull data from four tables:
select files.id, files.name, dirs.dir
from files
inner join dirs on dirs.id = files.idDir
where files.id = 45
select label
from labels
...
0
votes
5answers
77 views
Mysql: Unable to drop a columns NULL default
Running mariadb 10.3
I have a bunch of columns that were created by default as NULL defaults. I was asked by a third party integrator to remove the NULL defaults, but so far have been unsuccessful.
...
0
votes
2answers
616 views
Delete data before INSERT
I have this table which is used to generate second table with the result:
INSERT INTO payment_transactions_daily_facts (count, volume, date, year, month, week, day, transaction_type, contract_id, ...
0
votes
1answer
28 views
Insert query result into database row
I have this table which is used to generate second table with the result:
INSERT INTO payment_transactions_daily_facts (count, volume, date, year, month, week, day, transaction_type, contract_id, ...
0
votes
1answer
103 views
Execute a sql query inside IF() function
Wherever I find an example of IF () function I only see something like below.
SELECT IF(500<1000, "YES", "NO");
+---------------------------+
| IF(500<1000, "YES", "NO") |
+--------------------...
0
votes
2answers
19 views
Get slave status using sql query inside if-else condition
How can I stop the slave if it is already running using sql query statements only?
So far I tried to do something like below but getting sql syntax error. I think it is due to the borders of result ...
0
votes
0answers
19 views
MySQL: Ignore a Row in a table of a particular Database
I had replication in place between 3 databases, I am trying to ignore a Row in a table of a particular Database. I tried many ways but i couldn't, and also i couldn't find anything in online that ...
0
votes
1answer
68 views
Do the type-specific WKT- constructors function differently from ST_GeomFromText?
From the docs on MariaDB, for ST_LineStringFromText
Constructs a LINESTRING value using its WKT representation and SRID.
ST_LineFromText(), ST_LineStringFromText(), ST_LineFromText() and ...
1
vote
1answer
47 views
Subset of Unordered Pairs of Distinct Primary Keys With Normalization
I am currently in the process of designing a database. I have one table as follows:
CREATE TABLE Set (
id INT PRIMARY KEY AUTO_INCREMENT,
-- other values
);
Now I wish to create another ...
1
vote
1answer
49 views
Can you update invisible columns
I'm trying to update the row_start column on a table with Versioning set. The row_start column is invisible.
UPDATE pgwebp.t_prov a
INNER JOIN pgwebp.t_prov b ON a.prov_id_a = b.prov_id
SET a....
0
votes
1answer
33 views
How can I add another .ibd file to my existing table when the memory space is full?
I am working on an enterprise solution on MariaDB. The biggest challenge we are facing is to find a solution if our tablespace is full (.ibd file) and how can we add another .ibd file which will work ...
0
votes
1answer
37 views
Unable to create an user using mysql_embedded
I am creating a docker mariadb image and wanted to create a database and user name before bootstrapping mysqld daemon. So as per the documentation I tried below queries.
mysql_embedded -e "CREATE ...
