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.
50
questions
0
votes
0answers
39 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
5 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
14 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
29 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
38 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 ...
1
vote
2answers
86 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
97 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
44 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 | ...
0
votes
1answer
102 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
23 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
106 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
365 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
19 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
32 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 ([...
2
votes
2answers
220 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
44 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
154 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
21 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
196 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
50 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
45 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
60 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
53 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
18 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
18 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
50 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
31 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
39 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
30 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
30 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 ...
0
votes
1answer
37 views
How to use mysql_plugin utility?
I tried to follow this official documentation to install unix_socket plugin using mysql_plugin utility but getting an error that
Cannot read plugin config file unix_socket. Bad format in plugin
...
0
votes
0answers
21 views
How to execute multiple concatenated queries?
I wanted to grant privileges on some selected tables only. So far I could only gather the necessary queries but unable to execute those.
SELECT
CONCAT('GRANT SELECT ON drupal.', TABLE_NAME, ' to ...
0
votes
1answer
19 views
How to lock all tables except some specific table?
I am trying to set a stage server which replicate some tables like articles, user etc from master. If I start the slave server as read_only I am not able to my stage site up because there are some ...
0
votes
1answer
46 views
MariaDB replication from master to slave
After doing some research around this topic, I should be attempting this task soon. However, I have two questions going around my head - could do with an experienced professional answering them.
I am ...
0
votes
1answer
70 views
MySQL vs. MariaDB Binary Logging Difference
I've been doing some testing with both MySQL and MariaDB and I've noticed a big difference with regards to the growth of binary logs - I can't find out much about this online.
I have a test database, ...
0
votes
1answer
2k views
ERROR 1130 (HY000) not allowed to connect to this MariaDB server facing issue in granting permission
I am trying to connect to mariadb running different container.Getting below error
$ mysql -u root -h test-mysql --port 3306
ERROR 1130 (HY000): Host 'test_1_73f6dacd1d23.test_bluenet' is not allowed ...
1
vote
1answer
87 views
MariaDB uses “wrong”/irrelevant Keys on large table
We have migrated our database from a local hosting company to AWS.
Now when we run a particular query on AWS it finishes in about a minute (processlist says "sending data") where on the old server the ...
0
votes
0answers
101 views
Resolving “there is unknown charset/collation names” warnings on views
I recently moved our MySQL 5.6 server over to MariaDB 10.3.10 and, for the most part, have not had any problems. We have a couple of databases that have close to 1000 views which are now filling the ...
0
votes
2answers
74 views
Log full of [Note] InnoDB: ft_end() after upgrade to MariaDB 10.3.10
My upgrade path was MariaDB 10.2.18 to MariaDB 10.3.10.
There are no bugs reported on mariadb or mysql bug tracker (at least that I could find).
...
Oct 24 17:45:33 host1 mysqld[12196]: 2018-10-24 ...
1
vote
0answers
334 views
MariaDB 10.3 slave fails to replicate from a MariaDB 10.2 master
We have a 10.2.18 master and 4 slaves with 10.2.18. Because of this bug that I've reported last week (https://jira.mariadb.org/browse/MDEV-17420) we tried to upgrade one of the replicas to 10.3.10 to ...
1
vote
1answer
16 views
How to update MariaDB if the current version is not in default directory?
I will try to update my MariaDB from 5.5.56-MariaDB to 10.3.9-MariaDB. However my current version was configured or located into another directory. How should i do it?
0
votes
1answer
56 views
MariaDB - how to drop partition on master but not on slave
I've read about filtering replication and I only run into information I know already, like you can select only a database or a table to do replication on.
My use case is that I want to have a slim ...
3
votes
1answer
440 views
MySQL Workbench Visual Explain not working with MariaDB 10.3.9
I recently installed MySQL Workbench 8.0.12 and MariaDB 10.3.9. I created my database and want to start fine tuning indexes and views. I can run a query with the EXPLAIN button and get the results in ...
4
votes
2answers
213 views
What are “Invisible Columns” and how are they used?
I saw a new feature Invisible Columns in MariaDB 10.3.x. What are practical use cases for DBA and web developer? When to use this feature?
Columns can be given an INVISIBLE attribute in a CREATE ...
0
votes
0answers
81 views
MariaDB galera cluster with Mroonga in wrapper mode doesn't synch
MariaDB Galera Cluster states that the storage engine needs to be InnoDB.
I am using Mroonga on some tables in wrapper mode {which uses InnoDB under the hood as storage engine}.
The problem I am ...
1
vote
1answer
323 views
MariaDB doesn't set authentication_string during installation
I'm setting up a new server using MariaDB (current stable version of 10.3) and I notice that all of my users - mainly root and debian-sys-maint - do not have an authentication_string after the ...
2
votes
1answer
2k views
Can upgrade from MariaDB 5.5 to MariaDB 10.3 directly?
We have a database running in MariaDB 5.5.60 and we want to upgrade it to the last 10.3.7 version.
However I'm looking at MariaDB knowledge base ( https://mariadb.com/kb/en/library/upgrading/ ) and I'...
0
votes
2answers
279 views
How to filter for consecuting timeline events in pure SQL?
I run a LPR system on a parking lot, and I record every plate along with the camera that recorded it.
I have 2 cameras, camera 1 is on the entry and camera 2 on the exit. So for every reading I ...
2
votes
1answer
786 views
Does MariaDB or MySQL implement the VALUES (expression) table value constructor?
I am just wondering if MariaDB or MySQL implements the <table value constructor> in the SQL Spec. In SQL Server and PostgreSQL this is done with standardized VALUES (expression)?
SELECT *
FROM (...
